zorvek (Kevin Jones)
asked on
SQL Server Express Connection String
I have a local installation of SQL Server Express 2012 LocalDB and I'm trying to connect to it. My connection string is:
Provider=SQLOLEDB;Data Source=xxx;Integrated Security=SSPI
where xxx is supposed to be the name of the SQL Server Express instance.
When I use SMS to manage the instance it is displayed as:
(localdb)\sqlinvctl (...)
When I use this connection string:
Provider=SQLOLEDB;Data Source=(localdb)\sqlinvctl ;Integrate d Security=SSPI
ADODB returns with an error that it can't find the instance.
What am I doing wrong?
Kevin
Provider=SQLOLEDB;Data Source=xxx;Integrated Security=SSPI
where xxx is supposed to be the name of the SQL Server Express instance.
When I use SMS to manage the instance it is displayed as:
(localdb)\sqlinvctl (...)
When I use this connection string:
Provider=SQLOLEDB;Data Source=(localdb)\sqlinvctl
ADODB returns with an error that it can't find the instance.
What am I doing wrong?
Kevin
ASKER
TCP/IP is already enabled.
Kevin
Kevin
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 tried:
Provider=SQLOLEDB;Data Source=localdb\sqlinvctl;I ntegrated Security=SSPI
and that did not work.
Kevin
Provider=SQLOLEDB;Data Source=localdb\sqlinvctl;I
and that did not work.
Kevin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have resolved the issue by using the provider "SQLNCLI11" instead of "SQLOLEDB".
Unfortunately, I have to support SQL Server 2008 with the same application - when the user is not connected to the company network they use the local SQL Server LocalDB installation, otherwise they use a production SQL Server 2008 installation which SQLNCLI11 does NOT handle.
Is there a way to make a single connection string work with both? Or do I have to switch?
Kevin
Unfortunately, I have to support SQL Server 2008 with the same application - when the user is not connected to the company network they use the local SQL Server LocalDB installation, otherwise they use a production SQL Server 2008 installation which SQLNCLI11 does NOT handle.
Is there a way to make a single connection string work with both? Or do I have to switch?
Kevin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check connectionstrings.com site for reference.
Still have some issue to get connection up
refer : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_10612-SQL-Server-does-not-exist-or-access-denied.html
Still have some issue to get connection up
refer : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_10612-SQL-Server-does-not-exist-or-access-denied.html
ASKER
>Since when does SQLNCLI11 not support SQL Server 2008?
I did not do my testing correctly. SQLNCI11 DOES support 2008 and 2005 and probably even earlier SQL Server versions.
Since ADODB returns immediately if a provider is not available, I'm going to first try opening the connection with SQLNCI11 and, if I get an error 3706 (Provider cannot be found. It may not be properly installed.) I'll open with SQLOLEDB. Preliminary tests are working as desired.
Thank you!
Kevin
I did not do my testing correctly. SQLNCI11 DOES support 2008 and 2005 and probably even earlier SQL Server versions.
Since ADODB returns immediately if a provider is not available, I'm going to first try opening the connection with SQLNCI11 and, if I get an error 3706 (Provider cannot be found. It may not be properly installed.) I'll open with SQLOLEDB. Preliminary tests are working as desired.
Thank you!
Kevin
You're welcome and thanks for the points.
Thanks for the points.
This is for SQL 2008 R2, I am assuming SQL 2012 is the same.