Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Automating .sql Scripts Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:47 AM
Points: 6, Visits: 11
I am in a bit of a jam. I am using SQL Express 2008 on my PC. I am new to this but I am creating a new database off of several processes within Server 08. I have everything running through Stored Procedures and I created a .sql script that Executes each of the stored procedures in order. I know if I had SSIS I could have the package run overnight, but my company does not support SSIS on SQL Express machines. Is there any other way to have SQL Server automatically execute my .sql file overnight, while I am away and my computer is locked? I am a novice SQL Server user.
Post #995534
Posted Wednesday, September 29, 2010 3:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 9:51 AM
Points: 47, Visits: 366
It semes SQL Express installs a disabled SQL Server Agent, so my initial suggestion of putting your T-SQL script into an agent job T-SQL step won't work (homework assignment: search more exhaustively than I am able to if there is a way to start the Agent for Express edition)

However, you could write an application as simple as a form with a button, that opens an ODBC connection to your database, and runs your stored procedures.

Post #995627
Posted Wednesday, September 29, 2010 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 6,829, Visits: 13,290
I would use a Windows scheduled task with the related sqlcmd.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #995633
Posted Wednesday, September 29, 2010 3:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, November 25, 2012 6:31 PM
Points: 1,375, Visits: 173
i used to do this by creating batch file using sqlcmd and will schedule it in the windows scheduler

Regards,
Subbu
Click here to Get Speedy answer or solution
Post #995637
Posted Thursday, September 30, 2010 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:47 AM
Points: 6, Visits: 11
I will look into this. I think you are on to something. Thanks
Post #995843
Posted Thursday, September 30, 2010 6:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:47 AM
Points: 6, Visits: 11
I looked into the Windows scheduler, all it would so was open the .sql file but it did not actually execute it.
Post #995844
Posted Thursday, September 30, 2010 6:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 562, Visits: 1,019
Use the Windows Scheduler to execute a SQLCMD command and point the SQLCMD to .sql file. There are other connection details that SQLCMD will require, but this is how I used to do this at a previous place.
Post #995851
Posted Thursday, September 30, 2010 6:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:47 AM
Points: 6, Visits: 11
I will definitely take a look at the sqlcmd today, thank you all so much.
Post #995855
Posted Thursday, September 30, 2010 7:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 562, Visits: 1,019
Rather simple, but BOL is a good source for examples, syntax, etc. Good luck and let us know how it goes
Post #995899
Posted Thursday, September 30, 2010 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 6, 2011 6:47 AM
Points: 6, Visits: 11
OK, I was able to schedule a sqlcmd. Got that part figured out. It runs and closes in about 15 seconds. But I did not notice any change in the actual database. I have a field that captures the time when the script is done running. When I execute the script manually it takes about 45 minutes for the process to complete. I did not get any error message from the SQLCMD. Any ideas, what I am doing wrong?

Here's what my SQL Cmd looks like
sqlcmd -S .instancename\SQLExpress -i"C:\Documents and Settings\locationexample\ProcessCreateCommercialCatalyst.sql "
Post #995955
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse