Hi,
I have a standalone application that uses a local database using Microsoft SQL Server Desktop engine Version 8.00.761. We are going to retire the application. There are many of these machines some on XP pcs some Win2K pcs but both with the same SQL database.
We want to be able to look at the old dbs on one machine (a Win2k) I am able to restore a DB from the XP machine and it gives me the message Restore database successfully processed.
However when I launch the application it gives a connection string error.
I thought that this might be a setting that is in the application itself so I copied the entire folder of the application from the XP machine over to my Win2K machine and tried to use it with the Win2Ks Db and it works fine.
So can anyone give me an idea whether a db backed up on a 8.00.761.XP machine can be restored on a 8.00.761.Win2K machine?
it can. likely your connection problem is from a string in the application (hard-coded or maybe in a config file). You'll need to change that to point to the new machine
1 Like
I copied the application folder for the program from the XP machine and it could open the Win2k machines DB without issue. So dont think a specific value is hardcoded. however I can see that the SQL server has the computer name by default.
Could it be that when backing up the db includes the server name... and then when I restore it and try to connect to it I get the fault? My next qustion is whether I can change the name of my local server to the name of the computer that the other database came from is this possible`?
OK then on the machine where it doesn't work. open a command prompt and type:
sqlcmd -S [server] -E
(use -E if using Windows Authentication, otherwise provide username password)
does it connect?
If not, does the account you are using exist in the target instance?
1 Like
Ok ill do that when i get to work tomorrow.
But just to clarify you want me to check the name of the sql server on the XP machine right? So that when i transfer it to the computer where it cant load i can Compare the server right?
Just want to check that you can connect (or not)
Im back, i had a lot of trouble with the sqlcmd tool and then found out ineeded osql 
Anyway the server comes back with the name of the folder and i can list the tables in it etc....
It works using the -E... What does that do? Should i have the same windows users on the source computer as the destination computer?
Do i need to do something obout moving the transaction logfile? I havent restored that.
I also tried to change the name of the destination computer to the same name of the source pc of the transferred database. This changed the name of the server.
This however meant that i couldnt use either databases as when i restored the destination pcs original db it gave the same connection string error. ( i renamed the computer and all was fine)
Just to be clear there is only ever one db at a time on the pc
osql?!? did you not see the warning. "This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use sqlcmd instead. For more information, see sqlcmd Utility."
AFAIK there is nothing osql can do that sqlcmd cannot. What trouble do you have with sqlcmd and how do you get around it with osql?
The -E is telling it to use Windows Security (Trusted or Integrated Security) and yes the same user, so a Windows Domain User. Using User and Password it would be:
osql -UmyLogin -PmyReallySecurePassword -SmyServer
With either utility osql /? or sqlcmd /? will display all the command line "switches"
osql is deprecated but still ships with SQL Server 2016 RTM.
Sure, but AFAIK it is not being actively worked on. It will not be enhanced, only patched if needed. I'd take the "Avoid" seriously!
This is a virtual machine from an image of a Win2000 machine with an old mssql on it. There is no sqlcmd. The application is over 10 years old you see
Thanks John,
So could the application in its connection string be unable to connect because the database i am using is from a different pc with different users. When i attempt to use the application it sends the local user who is not thhe user from the original pcthat i have tsken the database?
Yes that's right. To use integrated security you need to be on a domain and using a domain account if the SQL Server is not co-located with the client. You could use SQL Server authentication.
The frontend application is a third party one that i cant change. The sql server is a local one which takes the localhost name as the name of the server.
They are both old and not on the domain. So when i spin up the vm i use the local administrator account to log on to windows 2000 machine. That local account will be uknown on the machine that provided the db I have now restored.
If the application is connecting using the -E parameter then the guest database wont recognise it? (I have changed the name of the vm to that of the machine that provided the db, this causes the error to come slightly later when launching the application)
If i create a local user on the providing pc that matches my one on the vm and then take a backup should that help?