Sql2000 to SqlExpress

Hi
I have a Sql2000 database which I put onto a SqlExpress server. The way I did this was to take the PosServer.bak and restore it into the SqlExpress server.
My program which is access2000 had problem with the new database which I fixed by removing relationships from the database and creating them again.
Another program which looks at the same data is now having trouble saving data to the database although it connects and reads data no problem.

The following code:

Dim RsNewDlv As ADODB.Recordset
Set RsNewDlv = New ADODB.Recordset

RsNewDlv.Open "Select top 1 * From DeliveryHead", CnnPosServer, adOpenKeyset, adLockOptimistic, adCmdText

RsNewDlv.AddNew
    RsNewDlv!DeliveryNo = gblDeliveryNo
    RsNewDlv!GrnCreated = False
RsNewDlv.Update

Gives this error
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'QLIKVIEW\SQLEXPRESS' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.


this is the connection string.
"Driver={SQL Server};Server=QLIKVIEW\SQLEXPRESS;Database=PosServer;Uid=Po;Pwd=stpass"

What might be the cause of this. I don't understand why I should have to add it as a linked server because it is not and I tried it anyway and different errors came up.


LVL 1
EamonAsked:
Who is Participating?
 
Sham HaqueSenior SAP CRM ConsultantCommented:
try using a OLE DB connection instead of your ODBC:

"Provider=sqloledb;Data Source=QLIKVIEW\SQLEXPRESS;Initial Catalog=PosServer;User ID=Po;Password=stpass;"
0
 
EamonAuthor Commented:
I forgot to mention the program having the problem is vb6 and the reference to ADO is 2.7 but I tried changing it to 2.8 but it did not help.
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
save this site somewhere safe!

http://connectionstrings.com/?carrier=sqlserver
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Sham HaqueSenior SAP CRM ConsultantCommented:
the ADO version should not make a difference - however, for compatibility, I usually use 2.6....
0
 
EamonAuthor Commented:
Thanks gbshahaq using an OLE DB connection instead seems to work. I won't accept just yet until I have had a chance to fully check it.
Can you explain why my old connection string would not work.
Also if I replace my connection strings with the new one, in locations where I am still using sql2000 will I run into problems.
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
no probs - glad you're starting to get there....
the trouble with ODBC that i think you have is it had established a "READ ONLY" connection - not quite sure why this happens though....

the OLE DB connection string will work with 2000 just fine - the only change you'll need to make is the DATA SOURCE parameter, assuming that everything else remains the same (database name, user and pass)
0
 
EamonAuthor Commented:
Thanks gbshahaq
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.