November 21, 2007 at 1:56 am
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 !
November 21, 2007 at 2:05 am
Hi,
Check this link http://www.sqlteam.com/article/osql-storing-result-of-a-stored-procedure-in-a-file
Regards,
Ahmed
November 21, 2007 at 6:08 am
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
November 21, 2007 at 7:40 am
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'
November 21, 2007 at 8:30 am
As I've explain in my first post, I can't use this method for security reason.
Maybee an other one ?
November 21, 2007 at 8:01 pm
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
November 21, 2007 at 8:14 pm
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
November 22, 2007 at 1:40 am
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 *...
November 22, 2007 at 4:39 am
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
November 22, 2007 at 6:40 am
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...
November 22, 2007 at 7:21 am
Without using command line utilities, it would be almost impossible to achieve what you want.
One thing you can do is to write to text file for each record using OLE objects and a loop. I know this would surely scale poorly in production and i would never recommend to use such a solution.
This below solution could easily be converted to non-cursor loop.
--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
DECLARE SomeCursor CURSOR FOR SELECT Col1 + Col2 + Col3 FROM SomeTable WHERE Col4 = 'blah'
OPEN SomeCursor
FETCH FROM SomeCursor INTO @someline
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
EXECUTE @iCOMret = sp_OAMethod @FileID, 'Write', Null, @someline
FETCH NEXT FROMSomeCursor INTO @someline
END
CLOSE SomeCursor
DEALLOCATE SomeCursor
-- Fermeture fichier de log
EXECUTE @iCOMret = sp_OADestroy @FileID
EXECUTE @iCOMret = sp_OADestroy @FS
--Ramesh
November 22, 2007 at 10:36 am
I'm looking everywhere to write easily the result of an T-SQL query in an output file.
The use of xp_CmdShell to fire off BCP or OSQL would definitely be the way to go but, I understand that you do not have the REQUIRED SA PRIVS to do that. Same thing with the OLE DB sp_OA* procs... they also require SA PRIVS.
That leaves you with three options and only three options...
1. Write to a file that already exists using OPENROWSET or...
2. Have your DBA create a "text" linked server to the directory so that you can write to files that already exist or...
3. Have your DBA setup a proxy user that can use xp_CmdShell and a job that you can call that exercises a stored proc that does use xp_CmdShell.
Option 3 is the best because the code you write in the spoc has all the benefits of making calls to the command line and still has all the security of a "super fixed role" that can only be altered by the DBA. The DBA will also have the chance to review your code to ensure you're not violating any security measures. It will also allow you to create new files programmatically, etc, etc...
... of course, you have to have a good DBA that 1) knows how to do that, 2) isn't too lazy to help (some just say "nope, security" because they simply don't want to take the time or don't know the value of doing so), 3) understands that there is NO security risk in option 3. You shouldn't have to prove it to the DBA... they should know that this is a viable method with no risk if the code is properly reviewed.
I've setup a file handling system at work using option 3... it even allows me to rename files as "processed" and/or move them to a "processed" subdirectory. I use a combination of BCP (import files), OSQL (export data to files), and a compination of DOS COPY and OPENROWSET to write the log files. I use a combination of DIRTREE and xp_GetFileDetails (no longer available in 2k8) to figure out what the latest files are by date or just DIRTREE if the date is embedded in the file name somewhere.
Of course, you could always use DTS... if you don't mind it being slow 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2007 at 6:19 am
Yes Ramesh,
it's approximatively the code that I've done.
But I don't want to precise all the field of each tables because each table that I want to return in the output file make about 150 field.
So it's difficult to maintain.
I've tryed to have each field dynamically from dbo.syscolumn table but the process and the code is really complexe for what I would like to do.
Maybee someone has a simple method to without cursor to loop on each field to write...
November 23, 2007 at 6:29 am
Thanks Jeff,
Maybee I will retry with xp_cmdshell command.
The problem of using this method is not really for security reason because I'm sure that no SQL DBA will review my code.
But when I'v used this method, the result of the select * query was unexploitable like the result can appear in a cmd window.
Do you have some example of the code that you have implemented with OSQL and BCP ?
In addition, can you tell me wich prerequisites must have my SQL login "sysadm" to be able to use this command ?
Baptiste.
November 23, 2007 at 5:22 pm
But when I'v used this method, the result of the select * query was unexploitable like the result can appear in a cmd window.
Sure, I can help... but I don't understand what you mean in the quote above? Could you explain just a bit more... might direct my efforts in providing an example for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply