How to write query result in an output file in T-SQL (MSDE)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » How to write query result in an output file...

25 posts, Page 1 of 3

1 2 3 »»»

How to write query result in an output file in T-SQL (MSDE)

Rate Topic

Display Mode

Topic Options

Author

Message

littlesquall

Posted Wednesday, November 21, 2007 1:56 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
Points: 15, Visits: 42

I'm looking everywhere to write easily the result of an T-SQL query in an output file.

I work with MSDE (SQL2000) an the T-SQL code is in a stored procedure.

The query is like :
"select * from myTable where ..."
There are about 150 fields in the table to return in the output file.

I would like to write the line result in one line in an output file in the hard drive.

I've tryed 2 method :

1) Use of "xp_cmdshell" command
DECLARE @PrintInLogFile sysname
DECLARE @var sysname
SET @var = ...
SET @PrintInLogFile = 'echo ' + @var + ' > C:\File.log'
EXEC xp_cmdshell @PrintInLogFile

The problem is that I can't use this command in production because of security constraints.

2) Use of "sp_OAMethod" command with 'WriteLine' method

--variables d'écriture
DECLARE @FS int
DECLARE @FileID int
DECLARE @iCOMret int

DECLARE @FileName VARCHAR(4000)

SET @FileName='C:\File.log'

--creation de l objet pour manipulation du fichier de log
EXECUTE @iCOMret = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXECUTE @iCOMret = sp_OAMethod @FS, 'CreateTextFile', @FileName, true

--Ouvre le fichier (2 = ForWriting, 8 = ForAppending)
EXECUTE @iCOMret = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 2

EXECUTE @iCOMret = sp_OAMethod @FileID, 'WriteLine', Null, 'Bonjour'

-- Fermeture fichier de log
EXECUTE @iCOMret = sp_OADestroy @FileID
EXECUTE @iCOMret = sp_OADestroy @FS

The problem with this method is that I can't write one line with the resul of a select *

Can anyone help me ?
I'm sure that in T-SQL we can write easily the entirely result of a select * in one line of an output file in the hard drive...

Thnaks in advance !

Post #424456

SQL_ABD

Posted Wednesday, November 21, 2007 2:05 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi,
Check this link http://www.sqlteam.com/article/osql-storing-result-of-a-stored-procedure-in-a-file

Regards,
Ahmed

Post #424460

littlesquall

Posted Wednesday, November 21, 2007 6:08 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
Points: 15, Visits: 42

Thanks Ahmed

I've already tried this method

osql -E -d NOMADE -S (local) -Q "select * from MyTable where ..." -o C:\File.txt

But I can't launch the command from a cmd window. THe T-SQL code must be in a stored procedure.
In addition, the return result is written on multiple lines in the output file.
I would like to write the return result of a select * command into one output line.

Thank you for helping me

Post #424555

SQL_ABD

Posted Wednesday, November 21, 2007 7:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi,

You can use xp_cmdshell to execute from a stored proc
Exec master..xp_cmdshell 'osql -E -d NOMADE -S (local) -Q ''select * from MyTable where ...'' -o C:\File.txt'

Post #424601

littlesquall

Posted Wednesday, November 21, 2007 8:30 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
Points: 15, Visits: 42

As I've explain in my first post, I can't use this method for security reason.
Maybee an other one ?

Post #424634

Carl Federl

Posted Wednesday, November 21, 2007 8:01 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 2,224, Visits: 4,080

Writing to a file from SQL is not supported, so you will need to use one of the SQL Server add-on services.

How is the process to write the file being invoked ?
If on a scheduled basis, a SQL Server job can run a Data Transformation Services package.
If based on some data changes, Notification Services can be used.
Alternatively, Reporting Services could be used.

SQL = Scarcely Qualifies as a Language

Post #424805

SQL_ABD

Posted Wednesday, November 21, 2007 8:14 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi Carl,
I already redirect the result to file using osql (whithin a stored proc and xp_cmdshell ) or using sp_OACreate 'Scripting.FileSystemObject' and it works fine.
The problem is littlesquall can not use the 2 methods because of security constraints (The network admin does not allow)
Regards,
Ahmed

Post #424807

littlesquall

Posted Thursday, November 22, 2007 1:40 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
Points: 15, Visits: 42

Thanks Ahmed,

The 1st method with xp_cmdshell cannot be used because of security method.

With the second one (with execute @OLEResult = sp_OAMethod @FileID ,'WriteLine', Null, @Str)
I don't arrive to return the entirely result of the select * in one line in an output file.

Maybee there is an other method than WriteLine or Write to return easily the result of a select *...

Post #424869

SQL_ABD

Posted Thursday, November 22, 2007 4:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi littlesquall,
You want one line in the output. It means you want each 150 fields in one line, or something else.
I think the line length of the text file is limited, then when you reach a certain value, he will go to the next line.
May be you need to do some process on the fields, before generating yor output text file.
Regards,
Ahmed

Post #424927

littlesquall

Posted Thursday, November 22, 2007 6:40 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
Points: 15, Visits: 42

Of course ahmed,

With the editor used, it can appears on multiple lines. It's not really a problem...

But I can't use like "OSQL ... -o C:\Output.txt" does.
The result of a select * in the output file is totally random,
like it can appear in a cmd windows when the select * returns more than 150 fields...

Post #424974

« Prev Topic | Next Topic »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions