Remote SQL Server Database Connection Problem using VB6 using OLEDB

[ksenthilbabu]

Please Help me,

We had a Software application Developed using VB6 with MSSQL SERVER2005 as Database(Remote) in www.znetindia.com our web space provider , the application was working properly until they upgraded the Server configuration, Now we are not able to connect with the database using the normal OLEDB connection String

adoConn.Open PROVIDER=MSDataShape;Data Provider=SQLOLEDB.1;Persist Security Info=0;User ID=UserName;Password=Password;Initial Catalog=DatabaseName;Data Source=servername

It is giving an ERROR (Error No.-2147467259 at Line No. 0 ([DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.) in procedure )

We contacted our service providers they informed that they asked us to try with the SQL Server Instance

adoConn.Open PROVIDER=MSDataShape;Data Provider=SQLOLEDB.1;Persist Security Info=0;User ID=UserName;Password=Password;Initial Catalog=DatabaseName;Data Source=servername/instancename

It is giving an ERROR (Error No.-2147467259 at Line No. 0 ([DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.) in procedure)

Again they informed to use the ip address instead of servername without the named instance and also with the named instance,but the same errors are generating.

WE TRIED WITH SQL NATIVE CLIENT : -

With Instance Name:

adoConn.Open PROVIDER=MSDataShape;Data Provider=SQLNCLI.1;Persist Security Info=0;User ID=UserName;Password=Password;Initial Catalog=DatabaseName;Data Source=servername/Insancename

Error No.-2147467259 at Line No. 0 (Named Pipes Provider: Could not open a connection to SQL Server [53]. ) in procedure Command1_Click of Form Form1

WithOut Instance Name:

adoConn.Open PROVIDER=MSDataShape;Data Provider=SQLNCLI.1;Persist Security Info=0;User ID=UserName;Password=Password;Initial Catalog=DatabaseName;Data Source=servername

IT IS WORKING PROPERLY.

The Web Service Provider came online using Team Viewer worked directly in our machine and tried the connection string

adoConn.Open PROVIDER=MSDataShape;Data Provider=SQLOLEDB.1;Persist Security Info=0;User ID=UserName;Password=Password;Initial Catalog=DatabaseName;Data Source=servername/instancename

using Microsoft Data Link Provider It is not working and generating Errors.

When they tried in their system with the same connection string using Microsoft Data Link Provider it is connecting with the database without any problems. ( We also viewed through Team Viewer their system)

Please help us to solve this connection problem using OLEDB, because when we use SQL NATIVE CLIENT we will not be able to connect using Crystal Reports 10 because CR10 will not support SQL NATIVE CLIENT.
REWRITING THE ENTIRE APPLICATION TO USE SQL NATIVE CLIENT IS VERY DIFFICULT.

Please help us..

With Thanks,
K.Senthil Babu

[AndreRet]

Try the following links.

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

http://www.connectionstrings.com/sql-server-2008

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx - here, you need to get the error status from your errorlog file which normally resides in c:\Program Files\MS SQL Server\MSSQL10 or 9 or whatever version\Log Files\Errorlog.txt. Go to the line where connection error failed. Will normally state something to the effect of Error 18456, Severity 14, State 11 or 30 etc. Compare this error message with your specific case.

And lastly some steps to check if all setting is correct after the upgrade = http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

Hope this helps.

[ksenthilbabu]

Thank you Mr. AndreRet

We are able to connect with the remote database server, We modified the connection string with Like
adoConn.Open "Provider=SQLOLEDB.1;Persist Security Info=0;Network Library=DBMSSOCN;Data Source=servername,1433;Initial Catalog=DatabaseName;User ID=UserName;Password=Password;MultipleActiveResultSets=yes;Encrypt=yes;"

Just we added 1. Listening Port to Datasource=1433 , 2. Network Library =DBMSSOCN(TCP/IP),3.MultipleActiveResultSets=yes and 4.Encrypt=yes;

with the above mentioned modification now we are able to connect to the remote database server.

Thanks a lot for your kind information and guidance.

With Thanks
K.Senthil Babu

[AndreRet]

Only a pleasure.

[akhilesh12]

With so many cheap web hosting services services companies available, the website hosting industry has become very competitive and each of them use every trick in the book to remain popular in this cutthroat industry. The advertisements made by a majority of these website hosting companies misleading and are not a lot accurate as they claim to be. For this sites
india no1 webhosting
http://www.webspacehosting.in