• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Connectivity issue PBApp Driver SQL Native Client 11.0

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

Resolved Connectivity issue PBApp Driver SQL Native Client 11.0

  1. Issue
0
Votes
Undo
  1. arnell va
    arnell va
  2. PowerBuilder
  3. Friday, 28 July 2023 20:50 PM UTC

Hi everyone!

 

I currently have a issue when mi PB app is communicating with driver SQL Native Client v11.0, when try to make the connection I received the following message:

SQLSTATE = 1S00
Microsoft SQL Server Native Client 11.0
Invalid connection string attribute

This connection was working without problems and suddenly the application started to had this issue.

I make a test connection with the odbcad32.exe to validate if there are issues with the driver, but the connection was created successfully.

The string connection looks like this:

DBMS = SNC SQL Native Client(OLE DB)

ServerName = XXXX

DataBase = XXXXX

DBParm = XXXX

PROVIDER='SQLNCLI11';SERVER='XXXXX,PORT';DATABASE='XXXX'

LogId = XXXX

LogPassword = XXXX

Lock = RU

 

Any suggestion?

 
  1. PowerBuilder
  2. SQL Server
Httpclient patch method?
Diff Tool Powerbuilder 2022 not working
Responses (5)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 28 July 2023 21:13 PM UTC
  2. PowerBuilder
  3. # 1

Hi Arnell;

  Are those the values of SQLDBCode & SQLErrText on the App's transaction object when the failure occurs?

  Also, any recent changes to the SS environment by your DBA team?

Regards ... Chris

Comment
Load more comments
arnell va
  1. arnell va
  2. Thursday, 17 August 2023 16:34 PM UTC
Hi Chris,



I added the SQLDBCode and it returned value "-1" But is not specific
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 17 August 2023 20:25 PM UTC
-1 sounds like SQLCode (as set by the DB client interface) and not a value returned in SQLDBCode (as set by the DBMS itself).
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 18 August 2023 13:29 PM UTC
BTW: if you are getting a -1 all around, then that means that any SS client that your using is NOT getting to SS. In that case, I would look at your SS DB Client(s) as the issue
  1. Helpful
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Monday, 31 July 2023 13:55 PM UTC
  2. PowerBuilder
  3. # 2

For PORT are you using the actual port the server listens on or the word PORT? Port 1433 is the default port.

https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver16

 

Comment
arnell va
  1. arnell va
  2. Monday, 31 July 2023 16:39 PM UTC
Hi Roland,

I'm using the port the server listens, and is not the default port
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Monday, 31 July 2023 17:00 PM UTC
Try without the single quotes:

Provider=SQLNCLI11;Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 31 July 2023 15:19 PM UTC
  2. PowerBuilder
  3. # 3

Hi, Arnell -

I've been connecting to SQL Server databases daily for nearly 15 years using the SNC database interface, and I do not recognize nor can I find any documentation supporting a DBParm setting named "Server".

    https://docs.appeon.com/pb2019r3/connection_reference/ch01s01.html

We identify the server via the SQLCA.ServerName property, but not in DBParm. While there does exist a ServerName DBParm setting, it is described as being for use only with the ORA database interface.

If you are trying to copy configuration settings from ODBC (???), I cannot say with certainty, but I doubt very much that will work.

Using the SQLNCLI11 provider, we set only the following:

SQLCA.DBMS = "SNC"
SQLCA.ServerName = "servername\instance"
SQLCA.AutoCommit = False
SQLCA.DBParm = "dbparm-settings"

DBParm Settings used:
  Database='databasename'
  Provider='SQLNCLI11'
  TrustedConnection=1        (we connect to SQL Server via Windows Authentication)
  TrimSpaces=1
  DateFormat='mm/dd/yyyy'
  TimeFormat='hh:mm:ss.ffffff'
  DateTimeFormat='mm/dd/yyyy hh:mm:ss.ffffff'

Best regards, John

Comment
John Fauss
  1. John Fauss
  2. Monday, 31 July 2023 15:44 PM UTC
Ah - I think you may want to examine the DBParm setting named ProviderString:

https://docs.appeon.com/pb2022r2/connection_reference/ProviderString.html

However, if your SQL Server database uses the default port number of 1433, you should not need to specify the port number, as Roland indicated.
  1. Helpful
arnell va
  1. arnell va
  2. Monday, 31 July 2023 16:34 PM UTC
Hi John, thank you for your time to response.



I was validating and you're sure, I don't see any documentation about this field. But we use the field 'SERVER' to stablish the port (we aren't using the DB Default port).

I tried to remove this field and the connection with the DB still working, at least in DEV environment.



But, as I was explaining, my current issue is weird, because this configuration was working perfect since long time ago, and suddenly stopped working.



My question John, is:



Do you know which other issue could generate the message



"SQLSTATE = 1S00 -

Microsoft SQL Server Native Client 11.0

Invalid connection string attribute"?



  1. Helpful
arnell va
  1. arnell va
  2. Monday, 31 July 2023 16:35 PM UTC
As additional info: I validated the version of the driver installed and it didn't receive any update, is working with v.11.4.7001 for all environments.
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 31 July 2023 18:01 PM UTC
  2. PowerBuilder
  3. # 4

Here is what I would try:

SQLCA.DBMS = "SNC"
SQLCA.ServerName = "xxxxxxxxx"
SQLCA.AutoCommit = False  // or True, depending on your needs...
SQLCA.DBParm = "Database='xxxxxxx',Provider='SQLNCLI11',ProviderString='Server=xxxxxxxxx,nnnn'"

The only way I know to pass the non-default port number into the SQLNCLI11 provider is to use the ProviderString DBParm setting.

As I understand things, the SNC database interface will not examine or validate the content of the ProviderString setting, but will pass it undisturbed to the (SQLNCLI11) provider. However, since I have not ever used the ProviderString DBParm setting, I make no guarantee that it will work. The documentation I can find:

    https://docs.appeon.com/pb2022r2/connection_reference/ProviderString.html

    https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver16#ole-db-provider-connection-string-keywords

suggests that this is the ProviderString value that will work.

Good luck!

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 3 August 2023 07:42 AM UTC
  2. PowerBuilder
  3. # 5

We are using the following settings to connect to a sql server without any issues.   The contents of our ini file is

DBMS="SNC SQL Native Client(OLE DB)"
Database=database
LogId=logid
LogPassword=pass
ServerName=Servername\SQL2012
AUTOCOMMIT=True
Lock="RC"
DBParm = "Database='database',Provider='SQLNCLI10'"

But the following connection parameter gives the error that you mentioned, in few systems recently. Earlier it was working fine.   In the same system, this message started appearing recently.  Since the connection succeeds and the application is working fine, we haven't taken it seriously.

DBMS="OLEDB"
Database=database
LogId=logid
LogPassword=pass
ServerName=ServerName\SQL2012
AUTOCOMMIT=True
Lock="RC"
DBParm="PROVIDER='SQLOLEDB',DATASOURCE='Server\SQL2012',PBMaxBlobSize='100000',PBMaxTextSize='100000',PROVIDERSTRING='Database=database'"

Happiness Always
BKR Sivaprakash

 

Comment
arnell va
  1. arnell va
  2. Thursday, 17 August 2023 16:39 PM UTC
Hi Siva,



Could you validate what was the RCA of this issue? I also validated with the following changes and it worked, but i'm still in the research of the origin in the error, because there are no changes in the app or DB.



DBMS = OLEDB

DBParm= "PROVIDER='SQLOLEDB',DATASOURCE='XXXX,Myport',PROVIDERSTRING='DataBase=MyDBName'"
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 18 August 2023 05:25 AM UTC
That should work without any issue arneli va. That was working without any issue in my client place also for also for so many years. Suddenly, we started seeing this 'Invalid connection string attribute' message in few of the computers. Since clicking OK proceeds without any issue, clients are still continuing to press OK and continue to work. Say, if a client got 5-10 computers, we see this message in 2-3 computers only, not in all computers.
  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.