Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to write query result in an output file in T-SQL (MSDE) Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2007 1:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, November 21, 2007 2:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 1,326, Visits: 1,283
Hi,
Check this link http://www.sqlteam.com/article/osql-storing-result-of-a-stored-procedure-in-a-file

Regards,
Ahmed
Post #424460
Posted Wednesday, November 21, 2007 6:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, November 21, 2007 7:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 1,326, Visits: 1,283
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
Posted Wednesday, November 21, 2007 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, November 21, 2007 8:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
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
Posted Wednesday, November 21, 2007 8:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 1,326, Visits: 1,283
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
Posted Thursday, November 22, 2007 1:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, November 22, 2007 4:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 1,326, Visits: 1,283
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
Posted Thursday, November 22, 2007 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase 123»»»

Permissions Expand / Collapse