Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Opening a file in a SQL Server Agent job (T-SQL)

Status
Not open for further replies.

BlueTube

Technical User
Aug 4, 2006
24
GB
Hi all,
I’m trying to create a SQL Server T-SQL script that will basically drop and restore certain database on a daily basis. Because the names of databases that need to be restored may vary, the script may also vary. I have a VB Program that creates the script based on some parameters. I would like to use SQL Sever Agent Jobs to create a job that would run my script. I know I can copy and paste my script in one of the steps within the job, but I want it to be dynamic so that I don’t need to copy and paste the script as the database names change everyday (and therefore my script would also change). My question is: is there is a way that I can basically link the job to a T-SQL file (i.e. C:\restore.sql) so that the job would run based on whatever I have in c:\restore.sql file?
 
HAve your app create a stored proc. Then the job would simply exec the stored proc.

"NOTHING is more important in a database than integrity." ESquared
 
Thanks SQLSister. But my app cannot create a stored procedure. I guess my question is: how can I include some code in a T-SQL script to open a file and read from it? i.e. let's assume that I have the file C:\somescript.txt with the code " drop database ABC" in that file, how can I write a T-SQL script to open that file and read this line from it then execure it?
 
Why can't you have the application make a connection to the database server? I'd opt for having the application execute the code so you don't have any timing issues.

Anyways, if not, here's an article to do what you want:
Stored procedure: Execute T-SQL code from a file
 
Have the SQL Agent job run osql.exe or sqlcmd.exe with the -E and -i switches.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top