?
Solved

Update SQL EXPRESS database trough command prompt using script file

Posted on 2009-02-13
13
Medium Priority
?
700 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
0
Comment
Question by:LIBRALEX
13 Comments
 
LVL 12

Expert Comment

by:Dimitris
ID: 23632980
try this
sqlcmd -S (local)\SQLEXPRESS -i C:\Script.sql -U DBUsername -P DBPassword
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23633120
Are you trying to run the script file LOCALLY from each machine?  -S(local)\sqlexpress says to connect to the LOCAL instance.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 23633686
how about

sqlcmd -S "%computername%\SQLEXPRESS" -i C:\Script.sql -U DBUsername -P DBPassword

does every instance of sqlexpress have default name ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:LIBRALEX
ID: 23647463
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.
0
 

Author Comment

by:LIBRALEX
ID: 23648421
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.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 23648447
did you check the local "bad" workstation sql server logs ?
0
 

Author Comment

by:LIBRALEX
ID: 23649143
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??
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 23649507
according to MS
http://support.microsoft.com/kb/917828
the databse value auto_close is set to on, should be set to off
0
 

Author Comment

by:LIBRALEX
ID: 23657259
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.
0
 

Author Comment

by:LIBRALEX
ID: 23657679
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.
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 2000 total points
ID: 23657694
check the permissions for the username to the db
0
 

Author Comment

by:LIBRALEX
ID: 23659106
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!!!!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question