Connection String

[Jacksparrow87]

Hi people

I was hoping someone out there could offer me some help please, basically I’m in the early stages of moving from Access to SQL, I got one of my colleagues to convert the database and he’s changed it to SQL 2000 and he’s given me the network path of where the converted database is.

I’ve looked on

http://www.connectionstrings.com/?carrier=sqlserver

and the most obvious connection string I need to use is OLE DB, OleDbConnection (.NET) as that’s is the connection I have used, but for some reason it’s not working on my application.

For example I have the following coding:

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter("Select * from Log Order By LogNumber DESC, NoteNumber DESC; ", conn)
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False

Now the above coding works perfect, my colleague told me that he has converted the database and asked me if I could test to see if it works or not. He said he’s created an SQL Server 2000 Database on W23-dbserver\supporttest

Well my connection is:

"Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb"

And the connection I think I need to use is:

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI

So I have tried the following:

Provider=sqloledb;Data Source= W23-dbserver\supporttest;Initial Catalog= supporttest;Integrated Security=SSPI

Anyone know where I have gone wrong? When I run the coding it breaks on the adapter.fill line with the following error:

[DBNETLIB][ConnectionOpen (Connect())).]SQL Server does not exist or access denied

Any help please?

 

[RonRepp]

There are a lot of different ways to do this, and everyone will have a different opinion.

However, I use a UDL file to give me the string, and then just read from the UDL file like this:

 Public Function GetConString() As String
        Dim SR As New StreamReader(UDL)
        Dim i As Int16
        Dim S As String

        Do While SR.Peek > 0
            i += 1
            S = SR.ReadLine()
            [b]If i = 3 Then[/b]
                sCon = S
            End If
        Loop
        SR.Close()

        Return sCon
    End Function 'GetConString

If you don't know how to make a UDL file, simply create a new text file and change the extension to UDL. Afterwards, go in and create your datasource and connection information and click OK. When complete, open as text and you will see the connection string is on the third line.

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[Sorwen]

I'm not the best on connection strings I just keep trying verations until they work. My provider is slightly different so you might try it.

Provider=sqloledb.1

The other thing is ours is password protected. Really if he set it up correctly then your should be as well. Check with who set it up and if so add.

User ID=ActualUserID;Password=ActualPassword

Possibly your Intergrated Security lets you by pass that line. I just don't know on that. The only other thing I can say is that from what little I've seen the data source should be the address (ie. 10.7.5.192 or Something.Something.com) and Initial Catalog the path to the directory where the table is located.

You might try just to see.

Data Source=W23-dbserver;Initial Catalog=supporttest
or
Data Source=W23-dbserver;Initial Catalog=supporttest\supporttest
if it is two deep.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[gmmastros]

He said he's created an SQL Server 2000 Database on W23-dbserver\supporttest

Hmmm.......

When you install SQL Server, there are many options to choose from. In this case, it appears as though a named instance was used. For each server, you can have only 1 default instance, but you can have many named instances. If you have a default instance, the Data Source=[blue]ComputerName[/blue]. With a named instance, you need to use both computer name and instance name, like this... Data Source=[blue]ComputerName\InstanceName[/blue].

The UDL method mentioned by RonRepp will allow you to see a list of servers to choose from. After picking the server, you will see a list of databases on the server. Since you can pick from a list, it will be easier to build the connection string with the correct values.

the data source should be the address (ie. 10.7.5.192 or Something.Something.com)

When specifying the data source, you have many options. Normally, I use the computer name. Some of my customers have experienced problems using computer name. In every case, it's been a problem with DNS (resolving the computer name to an IP Address). In those cases, it's 100x faster to use the IP Address rather than getting IT people to fix the DNS problems. If you use the IP Address, and you have a named instance, then you will also need to identify the tcp/ip port that SQL Server uses for that instance (each instance uses a different port). When you use IP Address, the connection string should be:

Data Source=10.10.10.10

Or

Data Source=10.10.10.10,1234

** The 1234 above would be the actual port number.

If my assumption is correct, and a named instance was used, then you should ask what the name of the database is. You need to know.

Just guessing here, but try:

[!]Provider=sqloledb;Data Source=W23-dbserver\supporttest;Initial Catalog=CNS;Integrated Security=SSPI;[/!]

(I'm guessing that the name of the database is CNS based on the name of the access database this was converted from.)

Or

[!]Provider=sqloledb;Data Source=W23-dbserver;Initial Catalog=supporttest;Integrated Security=SSPI;[/!]

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SBendBuckeye]

For reference purposes, here are a couple connection string sites that I use. Good Luck!

http://www.connectionstrings.com

http://www.carlprothman.net/Default.aspx?tabid=81

 

[Jacksparrow87]

Thanks everyone, much appreciated especially gmmastros as the following worked a treat.

Provider=sqloledb;Data Source=W23-dbserver;Initial Catalog=supporttest;Integrated Security=SSPI;

Thanks again.