Link to home
Start Free TrialLog in
Avatar of Bob Schneider
Bob SchneiderFlag for United States of America

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:

    
Set conn = New ADODB.Connection
conn.Open "Provider=SQLNCLI11;Server=" & Environ("computername") & "\SQLExpress;Database=db;Trusted_Connection=yes;"

Open in new window


Thank you!
SOLUTION
Avatar of Chris Watson
Chris Watson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider

ASKER

Here is my new connection string:
conn.Open "Server=" & Environ("computername") & "\SQLEXPRESS01;Database=db;Trusted_Connection=yes;DataTypeComptability=80;"

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This gave me the "Object doesn't support named arguments." error:  conn.providerName="System.Data.SqlClient"

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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:
        Set srvr_conn = New ADODB.Connection
        srvr_conn.Open "Provider=SQLNCLI11;Server=72.52.136.29;Database=db;Uid=uid;Pwd=pwd;"

Open in new window


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.SQLEXPRESS01\MSSQL\DATA

Which is different than where the SSQL Server 2012 files were located:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

Is there a driver that needs to be updated somewhere?

Thanks for your continued assistance.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
On the server it is here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
This worked:

    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLNCLI11;Server=" & Environ("computername") & "\SQLEXPRESS01;Database=db;Trusted_Connection=yes;"

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am so sorry for wasting your time.  I was thinking it was the "01" that was the difference.  Feeling pretty stupid right now...