1. Samir Patel
  2. PowerBuilder
  3. Thursday, 19 April 2018 14:01 PM UTC

I am currently using the 30 free trial of Powerbuilder 2017 R2
I just migrated some old pbl files and testing the intial connection to MS SQL server
I changed my DB parameters from MSS to SNC (i have the native clients installed successfully)
when i run my powerbuilder code, it comes up with "MSSQL state Error 42S02 Object not identified"
when its trying to dynamically select from a table.
If i prefix the table name with "mydatabase.myrole." it then works
How do i bypass this, as i have thousands of lines of code referencing tables, functions, views and procs?
Yesterday, a fellow colleague pointed me to:
https://www.appeon.com/support/documents/appeon_online_help/pb2017r2/connecting_to_your_database/ch10s06.html
which doesn't help much

any help would be great

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 16:31 PM UTC
  2. PowerBuilder
  3. # 1

BTW: What version & build of SS are you using (  Select @@Version; )?

Comment
  1. Samir Patel
  2. Thursday, 19 April 2018 16:55 PM UTC
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4339.0 (X64)   Mar 19 2015 13:13:39   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)



and yes i have the correct

  1. Helpful
  1. Samir Patel
  2. Thursday, 19 April 2018 16:55 PM UTC
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4339.0 (X64)   Mar 19 2015 13:13:39   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)



and yes i have the correct provider and database name

  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 20 April 2018 13:57 PM UTC
Note that the provider for SS2008 should be ... Provider='SQLNCLI10'

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 14:56 PM UTC
  2. PowerBuilder
  3. # 2

Hi Samir;

   Typically, this means that the DB User ID you are using to connect to the DB instance does not have DBO authority by default. When that happens to me, then SS requires me to use the Table Name qualifier. Check with your DBA about the privileges that your UID has in the DB schema you are trying to use.

HTH

Regards ... Chris

Comment
  1. Samir Patel
  2. Thursday, 19 April 2018 15:00 PM UTC
Chris,



Thanks for the response, but I am connecting to the same database using the same ID and role when i run my old powerbuilder 7.0 app and it works for MSS?



Nothing has changed on the DB side?



 

  1. Helpful
  1. Samir Patel
  2. Thursday, 19 April 2018 15:00 PM UTC
Chris,



Thanks for the response, but I am connecting to the same database using the same ID and role when i run my old powerbuilder 7.0 app and it works for MSS?



Nothing has changed on the DB side?



 

  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 20 April 2018 13:56 PM UTC
Make sure that you selected the correct provider as well for SS2008, as follows ...



    SQLCA.DBParm = "Database='Chris',Provider='SQLNCLI10'"

  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Thursday, 19 April 2018 14:39 PM UTC
  2. PowerBuilder
  3. # 3

did you specify the database name in the provider string?

SQLCA.DBParm = "ProviderString='database=yourdatabasename' "

 

Comment
  1. Samir Patel
  2. Thursday, 19 April 2018 14:46 PM UTC
yes, i have



sqlca.dbparm = "Database='wgctest1',Provider='SQLNCLI11'"

  1. Helpful
  1. Samir Patel
  2. Thursday, 19 April 2018 14:55 PM UTC
i also tried your way and doesn't work...



if i do this.... it works....



  sqlca.DBParm = "ProviderString='database=mydatabase"





  integer li_count

  Select isnull(count(*),0) into:li_count from mydatabase.dbo.mytable using sqlca;

  Messagebox('Error',sqlca.SQLErrText + " (" + String(sqlca.SQLDBCode) + ")")

  messagebox('li_count',string(li_count))



if i do this... it doesn't



  sqlca.DBParm = "ProviderString='database=mydatabase"



  integer li_count

   Select isnull(count(*),0) into:li_count from mydatabase.dbo.mytable using sqlca;

 Messagebox('Error',sqlca.SQLErrText + " (" + String(sqlca.SQLDBCode) + ")")

  messagebox('li_count',string(li_count))



get the error 42S02 Error that says "object mytable is invalid (208)



 



 



 

  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 20 April 2018 13:45 PM UTC
The provider is wrong. For SS2008 you should have ... Provider='SQLNCLI10'

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.