1. RAFAEL RODRIGUEZ
  2. PowerBuilder
  3. Wednesday, 9 October 2019 03:16 AM UTC

I am migrating my aplication from powerbuilder 8 to powerbuilder 2019, but when i connect to my sql database and i try to retrieve data to my datawindow, i get this message: select error SQLSTATE = 42S02 Microsoft SQL Native Client invalid object name 'sysdatabases', i review that this table is created on my database, can someone help me, thank you

When I use database painter , i can see my database and tables, but when I run my program it connect to the database but not to the tables.

 

RAFAEL RODRIGUEZ Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2019 14:45 PM UTC
  2. PowerBuilder
  3. # 1

Thank you for your help, i resolve the problem with your comments.

On version 8 i used this:

SQLCA.Database = "BASEPRUEBA" 

but on version 2019 , i have to change for this:

SQLCA.DBParm = "Database='BASEPRUEBA' Provider='SQLNCLI' "

 

 

Comment
  1. Chris Pollach @Appeon
  2. Wednesday, 9 October 2019 15:21 PM UTC
Excellent news Rafael!

Yes, that would be the difference between the old MSS driver vs SNC in newer PB versions for SS.
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2019 14:14 PM UTC
  2. PowerBuilder
  3. # 2

sysdatabases is a view in the master database with an owner id of sys.

Make the change already suggested, fully qualify the select:

SELECT ... FROM master.sys.sysdatabases

 

 

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2019 13:48 PM UTC
  2. PowerBuilder
  3. # 3

Hi Rafael;

    In SQL Server, the table should be "Sys.Databases". For example ...

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc  
FROM sys.databases;

    Make sure that the table name is spelled correctly with the period (.) in the name.

HTH

Regards ... Chris

Comment
  1. Michael Kramer
  2. Wednesday, 9 October 2019 14:24 PM UTC
For new code I would definitely prefer using [sys.databases] instead of [sys.sysdatabases] - but existing code may continue to read from the "legacy compatibility" view [sys.sysdatabases]. I checked the legacy view: It still exists in MSSQL 2019 Preview.



App being PB 8 it may use a MSSQL version where [sys.databases] hasn't been defined yet.
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2019 09:40 AM UTC
  2. PowerBuilder
  3. # 4

Hi Rafael,

General note migrating any PRE PB 10 ==> PB 10+

Starting at PB 10, PowerBuilder moved from ANSI strings to Unicode UTF-16LE. So all strings are now 2 byte characters. Every access to external resources like databases and external function calls to DLLs is impacted.

RE: sysdatabases

Until you know what is actually failing it is hard give direction. Hence, ==>

Could you try just for experiment to prefix sysdatabases with its owner and/or database?
The fully qualified name should work no matter what may be your current database and/or current default schema.

SELECT . . . FROM master.sys.sysdatabases;

HTH /Michael

Comment
There are no comments made yet.
RAFAEL RODRIGUEZ Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2019 05:07 AM UTC
  2. PowerBuilder
  3. # 5

I connect to SQL 2008 with sqlncli as provider and with SQL 2014 with sqlncli11 as provider, in database painter i can see all the tables and the data, in dw painter i can see the data without the error

I review the parameters in the database painter and the app and are the same. But in the app i have the error when i try to retrieve data in the datawindow

 

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2019 04:54 AM UTC
  2. PowerBuilder
  3. # 6

Hi, Rafael -

What version of SQL Server are you using? What are the transaction object (SQLCA) property values (SQLCA.DBMS, etc.)? Can you retrieve data for this DataWindow Object in the DW Painter without the error? If so, what are the differences in the DB Profile you are using from the PB IDE and what the app is using during execution?

Also, I believe Microsoft is going to or may already have deprecated several old system tables in the "master" database.  Perhaps the newer Native Client interfaces disallow their use? Even though older constructs like sysdatabases may be visible, access rights may have changed. you should refactor your SQL SELECT statements to use the newer, recommended INFORMATION_SCHEMA views if your version of SQL Server supports them.

Comment
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.