Connect to sql express 2005 through code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Connect to sql express 2005 through code

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-02-01 : 20:20:08

Hi All:

I'm new to using sql express 2005 as the database, so I apologize if my question is very basic.

I can connect to db through sql mngmt studio, but I don't know how to connect through application code. The authentication mode is windows. I'm having trouble with the provider in the application code.

Can someone please tell me where I can find more detailed information on available providers for sql express?

Thanks for any help, as I'm against a tight deadline.

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-02-02 : 10:07:44

To add to the above:

The trouble seems to be in my connection string:

cn.Open "Provider=SQLNCLI;Server=(local)\sqlexpress;DataBase=Master; "

gives an error "2147467259 Invalid authorization specification"

cn.open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Data Source=(local)\sqlexpress"

gives an error "2147467259 Encryption not supported on the client"

cn.Open "Data source=.\SQLEXPRESS;Integrated Security=True; User Instance=True;Database=Master;"

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work has been done"

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-02-02 : 14:09:04

Check out http://www.connectionstrings.com/?carrier=sqlserver2005 to validate your connection string.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-02-02 : 15:29:19

Thank you for your response.

From connectionstrings.com I am now using the following:

cn.Open "Data Source=(local)\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;"

or this:

cn.Open "Data Source==.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;"

or this:

cn.Open "Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=D:\Documents and Settings\myusername\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\tempdb.mdf;User Instance=true;"

Using any of the above connection strings, I receive this error message:

"2147217887: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work has been done"

Any suggestions?

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2007-02-05 : 11:18:20

Ok, I am finally able to connect to sql express and get the user instance name using this code:

Public Function GetUserInstanceName() As String
'Purpose: Gets the user instance name for sql express which is a named pipe instance.

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Dim rs As New Recordset
Dim userinstancename As String

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider= SQLOLEDB; Data Source=.\SQLEXPRESS; Integrated Security=SSPI;Connect Timeout=30"

'Get the user instance name
strSQL = "SELECT owning_principal_name, instance_pipe_name From sys.dm_os_child_instances"

cn.Open

'Check the state of the connection to ensure we are connected

If cn.State = adStateOpen Then

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

If Not rs.EOF And Not rs.BOF Then

userinstancename = rs(1).Value

Debug.Print "Records affected: " & lngRecsAff
Debug.Print rs(1).Value

End If

rs.Close

End If

cn.Close
Set cn = Nothing
Set rs = Nothing

GetUserInstanceName = userinstancename

End Function

Now for my next question.... How do I use the user instance name to attach a database? It looks like I can only use the SQLNCLI.1 provider to do this, but it is asking for a path to the database. Is this the same as the user instance name?

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-02-07 : 11:42:33

Is this cross-posted the the MSDN forum? Looks like the same question I've answered at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1197488&SiteID=1.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

michaelxvo
Starting Member

47 Posts

Posted - 2007-03-14 : 14:47:05

quote:

Now for my next question.... How do I use the user instance name to attach a database? It looks like I can only use the SQLNCLI.1 provider to do this, but it is asking for a path to the database. Is this the same as the user instance name?

I assume that you try to connect to Northwind
in your connection string, specify this code INITIAL CATALOG=NORTHWIND
in your strSQL, specify the table you want to query, like this
strSql = "SELECT categoryname FROM category"
cn.open
set rs = cn.execute(strSql)
if not rs.eof then
rs.movefirst
while not rs.eof
---assign to a variable like this: strContainer = strContainer & rs(0)
rs.movenext
loop
end if
rs.close
cn.close
set cn=nothing
msgbox strContainer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources