Update SQL EXPRESS database trough command prompt using script file
Hi
I would like to update a SQL database using a script file and command prompt. I manage to accomplish this on one of my workstations but it does not work on all the other workstations. I cannot find any difference in the settings of SQL Server Express (unless Im looking in the wrong place). All workstations run SQL Server Express
I use the following code in the command prompt of windows:
sqlcmd -S WORKSTATION\SQLEXPRESS -i C:\Script.sql -U DBUsername -P DBPassword
On all the workstations I can see the output of the command and how the rows append etc. (See Screenshot).The database light also flashes while all the output and results are printed in the command prompt.
After this process has completed I open the database. On all except one workstation, no data has been changed or added. All workstations have the same database name and user name and password. Only the SQL server Express instance name is different.
Is there perhaps a setting that I have missed on the other workstations except for the one that works. I've also tried different SQL script files. All of them work on only one workstation. The rest show exactly the same output on the command prompt screen.
Thank you in advance.
Sceenshot.jpg
I would like to update a SQL database using a script file and command prompt. I manage to accomplish this on one of my workstations but it does not work on all the other workstations. I cannot find any difference in the settings of SQL Server Express (unless Im looking in the wrong place). All workstations run SQL Server Express
I use the following code in the command prompt of windows:
sqlcmd -S WORKSTATION\SQLEXPRESS -i C:\Script.sql -U DBUsername -P DBPassword
On all the workstations I can see the output of the command and how the rows append etc. (See Screenshot).The database light also flashes while all the output and results are printed in the command prompt.
After this process has completed I open the database. On all except one workstation, no data has been changed or added. All workstations have the same database name and user name and password. Only the SQL server Express instance name is different.
Is there perhaps a setting that I have missed on the other workstations except for the one that works. I've also tried different SQL script files. All of them work on only one workstation. The rest show exactly the same output on the command prompt screen.
Thank you in advance.
Sceenshot.jpg
Are you trying to run the script file LOCALLY from each machine? -S(local)\sqlexpress says to connect to the LOCAL instance.
how about
sqlcmd -S "%computername%\SQLEXPRESS
does every instance of sqlexpress have default name ?
sqlcmd -S "%computername%\SQLEXPRESS
does every instance of sqlexpress have default name ?
ASKER
Hi All
Thank you for your feed back.
Yes, I run each script file locally on each machine. They are not linked in anyway. Yes every instance of sqlexpress has a default name. I tried using -S (local)\SQLEXRESS but the results are still the same. I get the nice output etc like the screen shot but the database stays exactly the same. This is why I think that it might be some sort of setting in SQLEXPRESS because the workstation that allows the script file to alter its database was installed by someone else(who I can't find). All the other workstations were installed by me and all have the same settings - which does not allow the script file to alter the database.
I hope this might help.
Thank you for your feed back.
Yes, I run each script file locally on each machine. They are not linked in anyway. Yes every instance of sqlexpress has a default name. I tried using -S (local)\SQLEXRESS but the results are still the same. I get the nice output etc like the screen shot but the database stays exactly the same. This is why I think that it might be some sort of setting in SQLEXPRESS because the workstation that allows the script file to alter its database was installed by someone else(who I can't find). All the other workstations were installed by me and all have the same settings - which does not allow the script file to alter the database.
I hope this might help.
ASKER
Sorry, I can't manage to find something that is out of the ordinary. I've read trough the link and also the installation instructions. Everything looks OK but I still can't manage to alter the database using the sqlcmd.
The Script works 100% if I open it in management studio and run it as a query but I need to run it from cmd prompt.
The Script works 100% if I open it in management studio and run it as a query but I need to run it from cmd prompt.
did you check the local "bad" workstation sql server logs ?
ASKER
Hi Roads
I've checked the Errorlog of the "bad" workstation and found the following errors everytime I run the script file in sqlcmd
2009-02-16 14:05:05.67 spid51 Starting up database 'ReportServer$SQLExpressTe
2009-02-16 14:05:06.00 spid13s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-02-16 14:05:06.00 spid13s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-02-16 14:05:06.00 spid13s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
The "Good" workstation only shows something like: Starting up database 'DatabaseName'
The "Bad" workstations don't even show the database name in the errorlog
Any idea what those errors mean??
I've checked the Errorlog of the "bad" workstation and found the following errors everytime I run the script file in sqlcmd
2009-02-16 14:05:05.67 spid51 Starting up database 'ReportServer$SQLExpressTe
2009-02-16 14:05:06.00 spid13s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-02-16 14:05:06.00 spid13s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-02-16 14:05:06.00 spid13s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
The "Good" workstation only shows something like: Starting up database 'DatabaseName'
The "Bad" workstations don't even show the database name in the errorlog
Any idea what those errors mean??
according to MS
http://support.microsoft.com/kb/917828
the databse value auto_close is set to on, should be set to off
http://support.microsoft.com/kb/917828
the databse value auto_close is set to on, should be set to off
ASKER
Ok, I've set the auto_close to OFF. Now I get no Error in the Error log. All that I get is the Starting up database 'DatabaseName' but still no luck.All the database remains unchanged. I have service pack 2 installed on the workstations. And I guess that might contribute to the problem. I'm going to try and install service pack 3 and see if this might help.
ASKER
I've installed SP3 and still no luck. Error log now says:
2009-02-17 11:29:46.15 spid51 Starting up database 'DatabaseName'.
2009-02-17 11:30:58.92 spid52 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2009-02-17 11:29:46.15 spid51 Starting up database 'DatabaseName'.
2009-02-17 11:30:58.92 spid52 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx, you are right. The default database was set to master.I changed it and it worked 100%. I also noticed that it has updated the master database although I specified in the sqlcmd the database name.
Thanx again for all your trouble!!!!
Thanx again for all your trouble!!!!
sqlcmd -S (local)\SQLEXPRESS -i C:\Script.sql -U DBUsername -P DBPassword