We help IT Professionals succeed at work.

Update SQL EXPRESS database trough command prompt using script file

LIBRALEX
LIBRALEX asked
on
Medium Priority
709 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

DimitrisSenior Solution Architect

Commented:
try this
sqlcmd -S (local)\SQLEXPRESS -i C:\Script.sql -U DBUsername -P DBPassword
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" -i C:\Script.sql -U DBUsername -P DBPassword

does every instance of sqlexpress have default name ?

Author

Commented:
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.

Author

Commented:
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.
did you check the local "bad" workstation sql server logs ?

Author

Commented:
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$SQLExpressTempDB'.
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

Author

Commented:
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.

Author

Commented:
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.
check the permissions for the username to the db

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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!!!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.