Bob Schneider
asked on
SQL Server 2012 to SQL Server 2016
I am migrating to SQL Server 2016 and my VB6 program's connection string won't open the new data base. Can someone tell me how to modify it so that it opens? Here is my old connection string:
Thank you!
Set conn = New ADODB.Connection
conn.Open "Provider=SQLNCLI11;Server=" & Environ("computername") & "\SQLExpress;Database=db;Trusted_Connection=yes;"
Thank you!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This gave me the "Object doesn't support named arguments." error: conn.providerName="System. Data.SqlCl ient"
Victor, is your solution for vb.net? It doesn't like the syntax in vb6.
I don't think it is my firewall because the db is on my local (Win10) machine.
Thanks for the help...still searching.
Victor, is your solution for vb.net? It doesn't like the syntax in vb6.
I don't think it is my firewall because the db is on my local (Win10) machine.
Thanks for the help...still searching.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am wading through that article but it doesn't seem to be working. Interestingly, I can connect to my server db from my local machine using the same program via:
Note that this still uses the "Provider" parameter. For some reason it is just not finding it on my local machine. Here is where the data files reside:
C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS0 1\MSSQL\DA TA
Which is different than where the SSQL Server 2012 files were located:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRE SS\MSSQL\D ATA
Is there a driver that needs to be updated somewhere?
Thanks for your continued assistance.
Set srvr_conn = New ADODB.Connection
srvr_conn.Open "Provider=SQLNCLI11;Server=72.52.136.29;Database=db;Uid=uid;Pwd=pwd;"
Note that this still uses the "Provider" parameter. For some reason it is just not finding it on my local machine. Here is where the data files reside:
C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS0
Which is different than where the SSQL Server 2012 files were located:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRE
Is there a driver that needs to be updated somewhere?
Thanks for your continued assistance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I used a modified form of my server connection string and I got a "Login failed for uid." Progress, right? Do I have to make an entry in my Security folder in SSMS?
ASKER
On the server it is here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER \MSSQL\DAT A
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have connected to the db using SSMS. The Instance is SQLSERVER01
When I remove Provider=SQLNCLI11; from the connection string it can't find the instance. When I leave it in the login fails. I have added the login to the db but it still says the login failed for those credentials.
I really appreciate all the help. I had no idea converting to 2016 would be so problematic.
When I remove Provider=SQLNCLI11; from the connection string it can't find the instance. When I leave it in the login fails. I have added the login to the db but it still says the login failed for those credentials.
I really appreciate all the help. I had no idea converting to 2016 would be so problematic.
ASKER
This worked:
Set conn = New ADODB.Connection
conn.Open "Provider=SQLNCLI11;Server=" & Environ("computername") & "\SQLEXPRESS01;Database=db;Trusted_Connection=yes;"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am so sorry for wasting your time. I was thinking it was the "01" that was the difference. Feeling pretty stupid right now...
ASKER
Open in new window
Still no luck. The error message says "Data source name not found and no default driver specified." The database is "attached" to the sql engine and it is running (from the services menu).
Could this be a conflict with older SQL Server versions running on the machine? That is, could it not know where to look since I have the following folders: MSSQL10.SQLEXPRESS, MSSQL10_50.SQLEXPRESS, MSSQL11.SQLEXPRESS, and MSSQL13.SQLEXPRESS01. These are under the following path: C:\Program Files\Microsoft SQL Server
Is SQL Server 2016 in a different directory?