ODBC Connect To SQL Server 2005

[jhai_salvador]

Yoe guys, need your help..

I want to know on how to setup my client computers and the server so they can connect to sql server 2005 Developer Edition.

Client computers are Windows XP.
Server is Windows Xp installed with a SQL Server 2005.

They are on the same Workgroup.

Now how will I connect my Client to server using ODBC, and Use that client ODBC so my Client Application can connect to the Server database.

Hoping for your positive response.

[AndreRet]

First of all you will need the actual connection string to be 100% correct. This can be found on - http://www.connectionstrings.com/sql-server-2008

This will be the Sql Server forum page with hundreds of code samples - http://www.codeplex.com/wikipage?ProjectName=SqlServerSamples

You can then troubleshoot ALL your connection and errors from the following. There are a few step by step tutorials as well at the following -

http://blogs.msdn.com/b/sql_protocols/archive/2005/10/22/483684.aspx
And
http://blogs.msdn.com/b/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx
And from Microsoft
http://msdn.microsoft.com/en-us/library/dd207004.aspx
And very good solver lastly
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/c488cf76-2515-440f-b3f8-9cfad689c5b6/

[vb5prgrmr]

Good info Andre but the OP is asking about ODBC DSN's, and I agree with you, using ADO's DSN Less connection strings would be the best bet in a situation like this but what you did not tell the OP about is exactly what they would have to do to use an ODBC DSN...

jhai, to use an ODBC DSN for what you are talking about, you will have to go to each machine where your software is to be installed on and create the ODBC DSN... YES You will have to create an ODBC DSN ON EACH COMPUTER! That is if you still insist on using and ODBC DSN and not use ADO's DSN Less connection string... However, if for some reason you must use and ODBC DSN, you can actually create one with a few API calls to add some information to the registry... BUT you better have the dlls on the machine that you are going to be pointing to...

Here is some more info on creating a ODBC DSN as part of your program...
http://support.microsoft.com/kb/171146
http://support.microsoft.com/kb/184608
http://support.microsoft.com/kb/123008/EN-US/
http://gallery.technet.microsoft.com/ScriptCenter/en-us/635ff2a1-4188-4664-8cd8-4bd3bf832592

Also, if you search the registry for say a system dsn, you will find some entries within and if you follow those entries (and make notes along the way), you should be able to create an ODBC DSN by direct calls to the registry... Did you know that ODBC DSN's do not need to be displayed in the ODBC Manager? Yep! As a matter of fact, you can use these methods and specify that the ODBC DSN not be visible or by leaving out one registry entry...

Another way is to use a File DSN...
http://support.microsoft.com/kb/165866
http://support.microsoft.com/kb/159557/EN-US/

Good Luck

[jhai_salvador]

Thank you for answering it. Ill just visit those sites later.
for the meantime, I'm gonna try this code.

Public Sub ConnectViaIP(TempConn As ADODB.Connection, strDbname As String, _
                             strServerIP As String, _
                             Optional strUID As String, Optional strPWD As String)
On Error GoTo err:
Set TempConn = Nothing
Set TempConn = New ADODB.Connection
TempConn.Open "Provider=SQLOLEDB.1;" & _
           "Network Library=DBMSSOCN;" & _
           "Data Source=" & strServerIP & ",1433;" & _
           "Initial Catalog=" & strDbname & ";" & _
           "User ID=" & strUID & ";" & _
           "Password=" & strPWD
TempConn.CursorLocation = adUseClient
Exit Sub
err:
MsgBox err.Description
End
End Sub

Do you think this code will work?.. :) this will be a DSN less connection right?..

[vb5prgrmr]

Yes, that is a DSN Less connection string. One suggestion... On Error Go To Err... Err is the Err Object... Perhaps Change to On Error GoTo ErrHandler... That way there is no confusion as you can see by your code err does not capitalize when you go err.Description...

Good Luck

[AndreRet]

This looks perfect.

Should any errors be generated, look in your error log file on your server for the type of error as per my links above. You can open the log file from the admin console under sql server.