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

[BlueTube]

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?

 

[SQLSister]

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

 

[BlueTube]

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?

 

[RiverGuy]

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

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1135562,00.html

 

[mrdenny]

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