SQL output

[pvbangera]

hi,

can i direct my sql statement output directly to any text file??

like: db.execute("selecte * from table1")

i want the output of the statement in a text file. i dont want to loop it across.

is it possible??

regards

[Dman]

If you mean write the results of a query to a text file, yes you can. One example I could offer is selecting a string from a temporary table. Within the context of a stored procedure you could do this:

Declared at top of stored procedure:
@FilePath varchar(128),
@cmdString varchar(256)


In the body of the procedure:

Set @FilePath='C:\MyFolder\MyFile.txt'

Set @cmdstring='bcp "Select TransString from ##TempTable"' +
' queryout ' + @FilePath + ' -c -S' + @@ServerName + ' -Usa -P'

Or you substitute the above line with:

Set @cmdstring='bcp ##DailyTable' + ' out ' +
@FilePath + ' -c -S' + @@ServerName + ' -Usa -P'

This would just copy the whole temporary table to the text file.
And then...

Execute master..xp_cmdshell @cmdstring

In this example your username is 'sa' and password is blank. For more detail check out the bulk copy (bcp) info in the help file.