Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved MSSQL State Error 42S02 "native client invalid object name

  1. Issue
0
Votes
Undo
  1. Samir Patel
    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

  1. Client
Can we install 2017 PBVM on Windows Server Core 20...
How to Dynamically Change Currency Column Format
Responses (3)
Chris Pollach @Appeon
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
Samir Patel
  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
Samir Patel
  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
Chris Pollach @Appeon
  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
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
Samir Patel
  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
Samir Patel
  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
Chris Pollach @Appeon
  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
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
Samir Patel
  1. Samir Patel
  2. Thursday, 19 April 2018 14:46 PM UTC
yes, i have



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

  1. Helpful
Samir Patel
  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
Chris Pollach @Appeon
  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.