Tsql statements to write to an ascii file onto file server (basically writing log when sql server script runs)

Question

Post reply

Tsql statements to write to an ascii file onto file server (basically writing log when sql server script runs)

JasonSmith

SSC Enthusiast

Points: 167
More actions
November 23, 2010 at 12:25 pm

#226925

Howdy,
New to sql server.
I have a sql server script that needs to run. It runs fine. but it doesn't have any logging statements in it. can some show me how to write to log onto file server when the script runs.
this is the script I have and I want to write to an ascii file (on file server), the timestamp when this script started running and the content of @TableName and timestamp when this script ended running.
Thanks
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'dbo'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

Viewing 5 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

Edit: By the way, I found that by searching for "t-sql write to file" in Bing. Search engines are your friend.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

It would be best to use power shell instead of using T-SQL to write to OS files. Phil factor has some articles that help you with doing it in T-SQL.

-Roy

scott.shaw Ten Centuries Points: 1071 More actions · Answer 3

If you put your code in as a t-sql job step in a scheduled sql job you can have the output written to a log file. This in the Advanced section within the Job Step Properties. You could then send the file to a remote share if needed.

Hope this helps.

JasonSmith SSC Enthusiast Points: 167 More actions · Answer 4

GSquared,

Thanks for posting the link.

I will try that and will let you know.

thanks again.