Incorrect bcp command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect bcp command

Author

Topic

cosuji
Starting Member

3 Posts

Posted - 2008-12-11 : 11:33:17

Hi there,

I am trying to use a stored procedure on SQL Server 2005 to write an .xml file to a folder, but am having problems with the bcp command I am using (I think).

The SELECT statement inside the bcp command produces the desired result, so I have a feeling it is the bcp bit that is incorrect.

Would anyone be able to kindly point out where I am going wrong? I would be most grateful for any help. My code looks like this:

DECLARE @bcpCommand VARCHAR(1000)
DECLARE @fileName VARCHAR (50)

-- Path to the website folder where file will be stored

SET @fileName = 'C:\Temp\test\test_Sitemap.xml'

-- Selects the URL from the page table and replaces the tilda '~' with 'http://' in order to build a recognisable URL (e.g. 'http://www.XXX.co.uk/login.aspx'). Also selects the timestamp of the last modification of a website page

SET @bcpCommand = 'bcp "SET QUOTED_IDENTIFIER OFF SELECT DISTINCT dbo.fnEncodeURL("http://www.XXX.co.uk" + REPLACE( Page.Url, "~", "" )) AS loc, convert( char(10), Page_Audit.ActionDateTime, 103 ) AS lastmod, "weekly" AS changefreq, 0.5 AS priority FROM Page INNER JOIN Page_Audit ON Page.ID = Page_Audit.ID FOR XML PATH ("url"),ELEMENTS,ROOT("urlset") SET QUOTED_IDENTIFIER ON" queryout "'

SET @bcpCommand = @bcpCommand + @fileName + '" -c -x -T'

END

Many thanks,

Chima

cosuji
Starting Member

3 Posts

Posted - 2008-12-11 : 11:45:57

Ah, silly me I had not included:

-- Run the command
EXEC master..xp_cmdshell @bcpCommand

However I'm getting errors all over the place now.

SQLState = 42000, NativeError = 102
Error = [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 'http:'.

SQLState = 42000, NativeError = 105
Error = [Microsoft][SQL Native Client][SQL Server]Unclosed quotation mark after the character string ' )) AS loc, convert( char(10), Page_Audit.ActionDateTime, 103 ) AS lastmod, weekly AS changefreq, 0.5 AS priority FROM Page INNER JOIN Page_Audit ON Page.ID = Page_Audit.ID FOR XML PATH (url),ELEMENTS,ROOT(urlset) SET QUOTED_IDENTIFIER ON'.

SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 12:15:38

do a print and see what it looks like

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cosuji
Starting Member

3 Posts

Posted - 2008-12-12 : 05:14:27

Hi,

I have tweaked the code a little bit, and when printed it displays:

bcp "SELECT DISTINCT dbo.fnEncodeURL('http://www.XXX.co.uk' + REPLACE( Page.Url, '~', '' ))AS loc, convert( char(10), Page_Audit.ActionDateTime, 103 ) AS lastmod, 'weekly' AS changefreq, 0.5 AS priority FROM Page INNER JOIN Page_Audit ON Page.ID = Page_Audit.ID FOR XML PATH ('url'),ELEMENTS,ROOT('urlset')" queryout "C:\Temp\test\test_Sitemap.xml" -c -x -T

When I execute the stored procedure, however, all I get is this:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

Would anyone be able to kindly shed some light on this?

Many thanks,

Chima

PS. Code is now:

-- Selects the URL from the page table and replaces the tilda '~' with 'http://www.bwellbirmingham.co.uk'
-- in order to build a recognisable URL (e.g. 'http://www.bwellbirmingham.co.uk/login.aspx'). Also selects
-- the timestamp of the last modification of a website page
SET @bcpCommand = 'bcp "SELECT DISTINCT dbo.fnEncodeURL(''http://www.XXX.co.uk'' + REPLACE( Page.Url, ''~'', '''' ))AS loc,
convert( char(10), Page_Audit.ActionDateTime, 103 ) AS lastmod,
''weekly'' AS changefreq, 0.5 AS priority
FROM Page INNER JOIN Page_Audit ON Page.ID = Page_Audit.ID
FOR XML PATH (''url''),ELEMENTS,ROOT(''urlset'')" queryout "'

SET @bcpCommand = @bcpCommand + @fileName + '" -c -x -T'

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-12 : 10:25:03

try creating the select a a view and bcp that out

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources