How can i write result from query to a text file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

How can i write result from query to a text file

Author

Topic

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-03-09 : 10:17:12

Hi,

I have a cursor in my script which compares the results of 2 columns (int)
For e.g in Row 1 column 1 has the value 6
Column 2 has the value 7
Row 2 column 1 has the value 10
Column 2 has the value 10 etc

I would like to output the results of each row to a text file

Here is the code snippet:
DECLARE db_cursor CURSOR FOR
SELECT tablename, CountBeforeArchive, CountAfterArchive
FROM CVTGameplay.dbo.Archive

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename, @CountBeforeArchive, @CountAfterArchive

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColumnName = @tablename
SET @ColumnName2 = @CountBeforeArchive
SET @ColumnName3 = @CountAfterArchive

IF @ColumnName2 = @ColumnName3
BEGIN
print '@tablename = ' + @tablename + ' ' + ' ' + @CountBeforeArchive + ' ' + @CountAfterArchive + 'counts match'

FETCH NEXT FROM db_cursor INTO @tablename, @CountBeforeArchive, @CountAfterArchive
END
ELSE
BEGIN
print '@tablename =' + @tablename + ' ' + ' ' + @CountBeforeArchive + ' ' + @CountAfterArchive + 'COUNTS DO NOT MATCH'

FETCH NEXT FROM db_cursor INTO @tablename, @CountBeforeArchive, @CountAfterArchive
END
END
CLOSE db_cursor
DEALLOCATE db_cursor

How can i output each result from the cursor loop to a text file?
The line i would like written to text file is

print '@tablename = ' + @tablename + ' ' + ' ' + @CountBeforeArchive + ' ' + @CountAfterArchive + 'counts match'

Riaz

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-10 : 04:50:51

Try this
DECLARE @Text AS VARCHAR(MAX)
SET @Text = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename, @CountBeforeArchive, @CountAfterArchive

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColumnName = @tablename
SET @ColumnName2 = @CountBeforeArchive
SET @ColumnName3 = @CountAfterArchive

IF @ColumnName2 = @ColumnName3
BEGIN
SET @text = @text + CHAR(13) + '@tablename = ' + @tablename + ' ' + ' ' + @CountBeforeArchive + ' ' + @CountAfterArchive + 'counts match'
FETCH NEXT FROM db_cursor INTO @tablename, @CountBeforeArchive, @CountAfterArchive
END
ELSE
BEGIN
SET @text = @text + CHAR(13) + '@tablename =' + @tablename + ' ' + ' ' + @CountBeforeArchive + ' ' + @CountAfterArchive + 'COUNTS DO NOT MATCH'
FETCH NEXT FROM db_cursor INTO @tablename, @CountBeforeArchive, @CountAfterArchive
END
END
CLOSE db_cursor
DEALLOCATE db_cursor

PRINT @text

Vabhav T

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-03-10 : 05:56:53

Thanks,

This does not write it to a .txt file though.
i would like the result written to a .txt file. I can then open the text file and check my results when all my scripts have finished running.

Anyone know how to output values to a .txt file?

Riaz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources