Automating .sql Scripts

Question

Automating .sql Scripts

clasley 57551

SSC Enthusiast

Points: 174
More actions
September 29, 2010 at 1:02 pm

#227470

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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

keith.gerritsen SSC Eights! Points: 973 More actions · Answer 1

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.

LutzM SSC Guru Points: 107049 More actions · Answer 2

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

subbu_e SSCrazy Points: 2431 More actions · Answer 3

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

clasley 57551 SSC Enthusiast Points: 174 More actions · Answer 4

I will look into this. I think you are on to something. Thanks

clasley 57551 SSC Enthusiast Points: 174 More actions · Answer 5

I looked into the Windows scheduler, all it would so was open the .sql file but it did not actually execute it.

grahamc SSCertifiable Points: 6762 More actions · Answer 6

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.

clasley 57551 SSC Enthusiast Points: 174 More actions · Answer 7

I will definitely take a look at the sqlcmd today, thank you all so much.

grahamc SSCertifiable Points: 6762 More actions · Answer 8

Rather simple, but BOL is a good source for examples, syntax, etc. Good luck and let us know how it goes 🙂

clasley 57551 SSC Enthusiast Points: 174 More actions · Answer 9

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 "

grahamc SSCertifiable Points: 6762 More actions · Answer 10

ok, 2 options:

run profiler to capture everything thats happening as well as errors

or

specify an output file on SQLCMD using the -o flag Oh and the file location for the output file

clasley 57551 SSC Enthusiast Points: 174 More actions · Answer 11

Success! I was able to use the sqlcmd to accomplish my process. Thank you all so much for pointing me in the right direction. I am working through the process as we speak but the sqlcmd utility seems to be working right. Thank you all.

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

clasley 57551 (9/30/2010)
Here's what my SQL Cmd looks like
sqlcmd -S .instancename\SQLExpress -i"C:\Documents and Settings\locationexample\ProcessCreateCommercialCatalyst.sql "

Why is there a full stop (or period, if you're American) at the beginning of the instance name? You would also need to use -E (for a trusted connection) if you're running this as a Windows user with appropriate rights on the database.

[EDIT] Whoops, sorry, missed the second page and didn't see you'd already solved the issue...ignore me.