May 29, 2012 at 1:28 pm
Hello, I need to add a step to a SQL job that exports a sql table to a share location as a .csv file. I tried exploring BCP from command line and although the commands seemed to create the file on the share, the files don't look the way I need them to. First, I created it as a .txt, but when I looked, it didn't have headings and it doesn't seem to be comma delimited (csv). Then I tried .csv and it left out the headings and all the row data is in one cell. I then, tried .dat and much worse. The SQL job will have 3 steps: 1st, the sproc that generates the table (this is done). Then, the step that creates the file on the share from the sql table (bcp??), and the 3rd step will be an email with counts results on a txt attachement using exec msdb.dbo.sp_send_dbmail (this is done, too). Is it smart to use bcp, or should I use something else for step 2? Below are the command lines I've used so far:
1- c:\ bcp DATABASE.dbo.TABLENAME out C:\tempTABLENAME.txt -SSERVERNAME -T -c
2- c:\ bcp DATABASE.dbo.TABLENAME out C:\tempTABLENAME.csv -SSERVERNAME -T -c
3- c:\ bcp DATABASE.dbo.TABLENAME out C:\tempTABLENAME.dat -SSERVERNAME -T -c
Let me know if add'l info is needed. Any help here is appreciated,
John
May 29, 2012 at 1:44 pm
bcp doesn't add headers, so you have to do it manually.
here's two examples i keep onhand for that:
--BCP to get headers
--Version 1: global temp table
--note i'm making sure my headers are large enough for my data i insert later....
--otherwise CITYNAME would be varchar(8), and I need maybe 100
SELECT
identity(int,1,1) AS BCPORDERID,
CONVERT(varchar(100),'CITYTBLKEY') AS CITYTBLKEY,
CONVERT(varchar(100),'CITYNAME') AS CITYNAME,
CONVERT(varchar(100),'COUNTYTBLKEY') AS COUNTYTBLKEY,
CONVERT(varchar(100),'COUNTYNAME') AS COUNTYNAME,
CONVERT(varchar(100),'COUNTYFIPS') AS COUNTYFIPS,
CONVERT(varchar(100),'STATETBLKEY') AS STATETBLKEY,
CONVERT(varchar(100),'STATECODE') AS STATECODE,
CONVERT(varchar(100),'CITYDESCRIP') AS CITYDESCRIP
INTO ##BCP_Results
INSERT INTO ##BCP_Results(CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP)
SELECT
CITYTBLKEY,
CITYNAME,
COUNTYTBLKEY,
COUNTYNAME,
COUNTYFIPS,
STATETBLKEY,
STATECODE,
CITYDESCRIP
FROM VW_CITYCOUNTYSTATE
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM ##BCP_Results ORDER BY BCPORDERID" queryout MyFileName.txt -c -T'
--drop table ##BCP_Results
--Version TWO:
--export two files, then combine them together.
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ''CITYTBLKEY'',''CITYNAME'',''COUNTYTBLKEY'',''COUNTYNAME'',''COUNTYFIPS'',''STATETBLKEY'',''STATECODE'',''CITYDESCRIP''" queryout MyFileNameT1.txt -c -T'
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM VW_CITYCOUNTYSTATE" queryout MyFileNameT2.txt -c -T'
set @sql ='copy c:\MyFileNameT1.txt + c:\MyFileNameT2.txt c:\MyFileName.txt'
EXEC master..xp_cmdshell @sql
Lowell
May 29, 2012 at 2:51 pm
Thanks, Lowell.
I went with option one and after some tweaks, I have my temp table. Now, I'm getting ready to exec EXECUTE master.dbo.xp_cmdshell but I can't find this anywhere. Pardon my ignorance, but where do I exec this from?
May 29, 2012 at 2:56 pm
latingntlman (5/29/2012)
Thanks, Lowell.I went with option one and after some tweaks, I have my temp table. Now, I'm getting ready to exec EXECUTE master.dbo.xp_cmdshell but I can't find this anywhere. Pardon my ignorance, but where do I exec this from?
doh that was a big assumption on my part, sorry;
you can run just the bcp portion from a cmd window, just like you've done in the past, i reckon.
but of you wanted to do it from within SQL Server Management Studio, then you use teh built in procedure xp_cmdShell.
most questions about bcp seem to say "i want to call bcp from TSQL", so that's how my examples are oreiented.
Lowell
May 29, 2012 at 4:14 pm
Thanks, man. I was able to run it from the SQL Mgtmt Console and cmd window. This tentatively completes the requirements for my solution:
Create/sched. a SQL Job that: runs sproc that generates table. Exports table to network share, and last, sends email with counts summary and table information.
I'm going to make this as a sproc that includes the exec command at the end. Then I'll be ready to put all my steps to the test in a sql job.
Thanks a lot.
May 29, 2012 at 5:34 pm
also consider that it sounds like you can skip bcp completely;
sp_sendmail has the option to include commad delimited results, including headers as an attachment.
here's a code sample:
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Myprofile For Email',
@recipients='lowell@somedomain.net',
@subject = 'SQL 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 1,
@exclude_query_output = 0,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'qry.txt',
@query_result_no_padding = 1
Lowell
May 30, 2012 at 6:07 am
I'll definitely look into it.
Thanks,
John
June 5, 2012 at 1:52 pm
Lowell, let me bounce this off you now:
I have tested my 3 steps of the solution and they all work great. However, after presenting it, I was asked that it would be nice if they could have the exported .csv file attached as a .zip file to the email, and I told them I'd have to look into it. So here's my solution sequence which is a sql job:
step 1 runs a sproc that refreshes a sql table. step 2 execs the bcp command which exports and saves the exported table as .csv on a share folder. step 3 execs sp_send_dbmail which emails the summary count info to the group. My question is, where and how can I possibly add the zipping of the file created in step 2?
Is this possible?
Regards,
John
June 5, 2012 at 2:20 pm
latingntlman (6/5/2012)
Lowell, let me bounce this off you now:I have tested my 3 steps of the solution and they all work great. However, after presenting it, I was asked that it would be nice if they could have the exported .csv file attached as a .zip file to the email, and I told them I'd have to look into it. So here's my solution sequence which is a sql job:
step 1 runs a sproc that refreshes a sql table. step 2 execs the bcp command which exports and saves the exported table as .csv on a share folder. step 3 execs sp_send_dbmail which emails the summary count info to the group. My question is, where and how can I possibly add the zipping of the file created in step 2?
Is this possible?
Regards,
John
You would need to create a job step between 2 and 3 that would use a command-line version of a zip program (such as 7zip) to compress the file. You email step (which would become step 4) would then email the zipped file instead of the uncompressed version.
June 5, 2012 at 2:25 pm
and here's a code example of 7zip and xp_cmdShell:
--http://www.7-zip.org/download.html
--http://downloads.sourceforge.net/sevenzip/7za920.zip
--xp_cmdshell is limited to ONE set of double quotes.
--one of the paths needs to not contain spaces!
--see 2003 MS KB http://support.microsoft.com/kb/266224
DECLARE @results TABLE(results varchar(255))
declare @command varchar(2000)
--zip one file
SET @command =
'"C:\DataFiles\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!
+ '7za.exe"' --the exe: i'm using in the command line utility.
+ ' a ' --the Add command: add to zip file:
+ 'C:\DataFiles\' --path for zip
+ 'myZipFile.zip' --zip file name, note via xp_cmdshell only one pair of dbl quotes allowed names!
+ ' ' --whitespace between zip file and file to add
+ 'C:\DataFiles\' --path for the files to add
+ 'SandBox_2011-07-25.bak' --the file
+ ' -y' --suppress any dialogs by answering yes to any and all prompts
print @command
--"C:\DataFiles\7zip_CommandLine_7za465\7za.exe" a "C:\DataFiles\myZipFile.zip" "C:\DataFiles\SandBox_2011-07-25.bak" -y
insert into @results
exec xp_cmdshell @command
select * from @results
/*
NULL
7-Zip (A) 4.65 Copyright (c) 1999-2009 Igor Pavlov 2009-02-03
NULL
Scanning
NULL
Updating archive C:\DataFiles\myZipFile.zip
NULL
Compressing SandBox_2011-07-25.bak
NULL
Everything is Ok
NULL
*/
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy