Write out to text file using T-SQL
Posted
by
sasfrog
on Stack Overflow
See other posts from Stack Overflow
or by sasfrog
Published on 2011-02-09T06:49:02Z
Indexed on
2011/02/09
7:25 UTC
Read the original article
Hit count: 158
I am creating a basic data transfer task using TSQL where I am retrieving certain records from one database that are more recent than a given datetime value, and loading them into another database. This will happen periodically throughout the day.
It's such a small task that SSIS seems like overkill - I want to just use a scheduled task which runs a .sql file.
Where I need guidance is that I need to persist the datetime from the last run of this task, then use this to filter the records next time the task runs. My initial thought is to just store the datetime in a text file, and update (overwrite) it as part of the task each time it runs.
I can read the file in without problems using T-SQL, but writing back out has got me stuck. I've seen plenty of examples which make use of a dynamically-built bcp command, which is then executed using xp_cmdshell. Trouble is, security on the server I'm deploying to precludes the use of xp_cmdshell.
So, my question is, are there other ways to simply write a datetime value to a file using TSQL, or should I be thinking about a different approach?
EDIT: happy to be corrected about SSIS being "overkill"...
© Stack Overflow or respective owner