October 14, 2008 at 8:27 am
Hi experts,
I have a procedure that I would like to run which has a variable column output so I would just like to have it send the results to a file just like when you tell Query Analyzer to send the 'Results To File' rather than 'Results To Grid' (I always make it .xls extension). We have one report using sp_makewebtask but I read that it is deprecated. I truly don't want anything more than the plain results sent to a file with the fields separated by a caret '^'. What suggestions could anyone make on creating this output file? Thank you for your comments.
Warm regards
EDIT: Actually there is something tricky, the Query Analyzer handles three separate SELECT outputs and I would like all three query outputs to go into the file.
2nd EDIT: I have simplified the output to only one SELECT output, I don't have to capture three queries in the output. I'm still hoping someone has suggestion on what to use besides sp_makewebtask. Warm regards,
October 15, 2008 at 11:20 am
You can use the BCP utility to execute a query and direct the output to a data file on disk.
To launch BCP from within a stored procedure, build the BCP command string in a variable and use EXEC xp_cmdshell to run it.
Both BCP and xp_cmdshell are well documented in MSDN.
This approach assumes that you want the file to wind up somewhere on disk accessible by the server, and not on the client.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2008 at 11:56 am
i have this code saved in my snippets hat might help you; it featues two of the things you might want: BCP of data, and appending multiple files together.
the original issue this came from was to create a special "header" with variable number of lines of information, append it to the data, and then append a results/ recap info to the end of THAT.
as you read the code, you'll see it uses a function to write/append to a file.
the basic process here is:
sticks info in a temp table.
gets metadata about the data int he temp file.
write sthe header
writes the body via bcp
--implied writing to the results/footer...not included but obviously the same as header
using xp_cmdshell to append the three files together.
[font="Courier New"]CREATE FUNCTION dbo.Ufn_WriteToFile
(
@FileName VARCHAR(1000), @Text1 VARCHAR(1000)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @status VARCHAR(100), @eof VARCHAR(10)
SET @status = 'SUCCESS'
DECLARE @FS INT, @OLEResult INT, @FileID INT
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
SET @status= 'Error: Scripting.FileSystemObject'
--Open a file
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 8, 1 -- Append if required (8)
--execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 1
IF @OLEResult <>0
SET @status ='Error: OpenTextFile'
--Write Text1
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
SET @status= 'Error : WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
RETURN @status
END
GO
--table to capture xp_cmdshell output
CREATE TABLE #results(resultstext VARCHAR(1000))
DECLARE @sql VARCHAR(4000),
@rowcount INT
--sample query: you would do the same to your existing bcp
--note i erased the password for the -P flag...make sure to fix
SET @sql = 'bcp "SELECT TOP 5 * FROM SYSOBJECTS" queryout "c:\body.txt" -c -U"sa" -P""'
--export via bcp
INSERT INTO #results
EXEC MASTER..xp_cmdshell @sql
SET @rowcount=@@rowcount --used only as example that that is NOT the rows you wanted to count
PRINT @rowcount -- this is the rows from xp_cmdshells work, NOT the bcp!!!!
--you have to get it from the output of xp_cmdshell
--select * from #results where resultstext like '%rows copied.'
SELECT @rowcount = CONVERT(INT,SUBSTRING(resultstext,1,CHARINDEX(' ',resultstext)))
FROM #results WHERE resultstext LIKE '%rows copied.'
--header preamble
SET @sql = 'HEADER1:53910072007000000000086456'
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--header date
SET @sql = 'Date: ' + CONVERT(VARCHAR,GETDATE(),101)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--header time
SET @sql ='Time: ' + CONVERT(VARCHAR,GETDATE(),108)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--header rowcount
SET @sql ='Total rows: ' + CONVERT(VARCHAR,@rowcount)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--combine the header and query together into a final file.
SET @sql ='copy c:\header.txt + c:\body.txt c:\results.txt'
EXEC MASTER..xp_cmdshell @sql
DROP TABLE #results[/font]
Lowell
October 15, 2008 at 10:19 pm
Here's a working example of the BCP/XP_CmdShell method. If you don't need a header, just comment it out...
--===== These could be parameters in a stored procedure
DECLARE @Directory VARCHAR(256)
DECLARE @FileName VARCHAR(256)
DECLARE @Header VARCHAR(8000)
DECLARE @Query VARCHAR(8000)
--===== These hold the necessary DOS commands for BCP and COPY
DECLARE @HeaderDosCmd VARCHAR(8000)
DECLARE @QueryDosCmd VARCHAR(8000)
DECLARE @FilesDosCmd VARCHAR(8000)
SET NOCOUNT ON
SELECT @Directory = 'C:\Temp\',
@FileName = 'Test'
+ CONVERT(VARCHAR(30),GETDATE(),112)
+ LEFT(REPLACE(CONVERT(VARCHAR(30),GETDATE(),108),':',''),4),
@Header = 'SELECT ''AddressID'',''AddressLine1'',''AddressLine2'',''City'',''StateProvinceID'',''PostalCode''',
@Query = 'SELECT AddressID,AddressLine1,AddressLine2,City,StateProvinceID,PostalCode FROM AdventureWorks.Person.Address',
@HeaderDosCmd = 'BCP "'+@Header+'" QUERYOUT "'+@Directory+@FileName+'.hdr" -S"'+@@SERVERNAME+'" -c -t"^" -T',
@QueryDosCmd = 'BCP "'+@Query +'" QUERYOUT "'+@Directory+@FileName+'.txt" -S"'+@@SERVERNAME+'" -c -t"^" -T',
@FilesDosCmd = 'COPY "'+@Directory+@FileName+'.hdr"+"'+@Directory+@FileName+'.txt" "'+@Directory+@FileName+'.csv"'
--===== Just shows what the commands end up looking like
PRINT @HeaderDosCmd
PRINT @QueryDosCmd
PRINT @FilesDosCmd
--===== Do the work of exporting and combining files.
-- I intentionally did not include a delete on the hdr and txt file.
-- I didn't want to make anyone nervous ;-)
EXEC Master.dbo.xp_CmdShell @HeaderDosCmd
EXEC Master.dbo.xp_CmdShell @QueryDosCmd
EXEC Master.dbo.xp_CmdShell @FilesDosCmd
--Jeff Moden
October 16, 2008 at 6:46 am
thanks to Jeff and Lowell
- I cut and pasted examples to add to my useful scripts
October 16, 2008 at 7:56 pm
Thanks for the feedback, Seggerman.
--Jeff Moden
October 17, 2008 at 6:05 am
Thank you to bhovious, Lowell and Jeff for helping me. I learn from all and it raises my abilities. Once I implement use of bcp in my work I'm sure we will find many more uses and not worry about rewrites when we upgrade.
Warm regards to all,
October 17, 2008 at 11:12 am
Jeff, I used your example and I have the following error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
I have verified that I can write to the folder using this method:
EXEC xp_cmdshell '@ECHO hi! > "\\HCS\Public\A_Test\hi.txt"'
I output the BCP string and it looks like:
BCP "SELECT Afield, Bfield FROM thistable" QUERYOUT "\\HCS\Public\A_Test\hi.txt" -S"SQL_PROD" -c -T
I have search for things to try but have not gotten anywhere. Do you have any suggestions?
Warm regards,
October 17, 2008 at 8:25 pm
Did you, by any chance, have the file you create with the @ECHO command open in a word processor or NotePad when you tried to run the BCP command?
--Jeff Moden
October 20, 2008 at 6:27 am
Did you, by any chance, have the file you create with the @ECHO command open in a word processor or NotePad when you tried to run the BCP command?
--Jeff Moden
I did open the file to verify the contents but I am not sure I understand your question.
October 20, 2008 at 6:45 pm
If you have the file open with something when you try to do the run, you will get an error like you did.
--Jeff Moden
October 20, 2008 at 7:00 pm
I dont know what are you using the file for but you can also use sp_send_dbmail to query output into a flatfile
and have it comma separated cause using xp_cmdshell is not a best practice.
In 2005 and onwards, one should use CLR functions to workaround the xp_cmdshell dependent problems.
October 20, 2008 at 7:26 pm
Nikhil Shikarkhane (10/20/2008)
I dont know what are you using the file for but you can also use sp_send_dbmail to query output into a flatfileand have it comma separated cause using xp_cmdshell is not a best practice.
In 2005 and onwards, one should use CLR functions to workaround the xp_cmdshell dependent problems.
Nikhil... not blasting you about anything... You just happened to tickle a hot spot and I just want to say something about it.
I'm sometimes amazed at the fear that xp_CmdShell puts into people. Yes, I agree, on a public facing system (shouldn't be directly public facing if you do it right), xp_CmdShell is a genuine "Bozo-no-no". But on a backend system or a tightly controlled ETL system where there is no public access, the use of xp_CmdShell opens up a world of features and speed. I sometimes think that the supposed best practices for not using xp_CmdShell is simply a Microsoft plot to sell more C#. 😛
So far as CLR functions go, they're almost always slower than T-SQL except for RegExReplace and some file handling... but then the file handling can be misused just like xp_CmdShell unless you're very careful about how you build it. My recommendation is that if you even think for a minute that you need a CLR because your developers can't figure out how to do something in T-SQL, talk with your DBA before you spend a minute on the CLR. You'll be surprised what can be done in T-SQL and how much faster it can be than a CLR if the T-SQL is written properly.
--Jeff Moden
October 21, 2008 at 7:04 am
Jeff, sorry to be so dense, I did not have the file open with another application when I ran the bcp. The xp_cmdshell @echo command created the file in the proper folder and I opened it afterward to verify the contents. So, basically, the problem bcp has is not with folder permissions.
EXEC xp_cmdshell '@ECHO hi! > "\\HCS\Public\A_Test\hi.txt"'
The above can create the file in the desired location but
EXEC xp_cmdshell @bcp_string
does not. I output the BCP string and it looks like:
BCP "SELECT Afield, Bfield FROM thistable" QUERYOUT "\\HCS\Public\A_Test\hi.txt" -S"SQL_PROD" -c -T
so formatting of the @bcp_string seems good. What is the "BCP host data-file" the error is complaining about?
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
Thank you for the attention and
Warm regards,
October 21, 2008 at 6:39 pm
I think it's because you've not identified the database where the table is at in your query. Try using a 3 part name for the table in th Query.
--Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply