xp_cmdshell syntax - 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

General SQL Server Forums

New to SQL Server Programming

xp_cmdshell syntax

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-08-17 : 05:09:41

Hi Guys.

Can someone please assist me.

I'm trying to export a few rows out of a table into a csv file using xp_cmdshell but have run into a wall.

My Command is as follows.

EXEC
xp_cmdshell
'bcp "SELECT * FROM [Message] where createdate between
2009-08-14 00:01:20.748 and
2009-08-15 00:00:01.000
" queryout "C:\Export\Message14-15-082009.txt" -T -c -t,'

My SQL works 100% if i do this.

SELECT * FROM [Message] where createdate between
'2009-08-14 00:01:20.748' and
'2009-08-15 00:00:01.000'

But for some reason when i try it, it does not work because of syntax errors

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '2009'.

Can someone please help.

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 05:11:29

You have single quotes for the dates in your T-SQL query, but not in your BCP command.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 05:12:13

EXEC
xp_cmdshell
'bcp "SELECT * FROM [Message] where createdate between
''2009-08-14 00:01:20.748'' and
''2009-08-15 00:00:01.000''
" queryout "C:\Export\Message14-15-082009.txt" -T -c -t,'

N 56°04'39.26"
E 12°55'05.63"

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-08-17 : 05:16:32

quote:
Originally posted by Peso

EXEC
xp_cmdshell
'bcp "SELECT * FROM [Message] where createdate between
''2009-08-14 00:01:20.748'' and
''2009-08-15 00:00:01.000''
" queryout "C:\Export\Message14-15-082009.txt" -T -c -t,'

N 56°04'39.26"
E 12°55'05.63"

Hi Peso.

I tried this.

EXEC
xp_cmdshell
'bcp "SELECT * FROM [Message] where createdate between
"2009-08-13 00:01:20.748" and
"2009-08-15 00:00:01.000"
" queryout "C:\Export\Message14-15-082009.txt" -T -c -t,'

But still i get something else...

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

So i'm thinking there is still a syntax error somewhere?

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-08-17 : 05:47:35

Hi Guys.

I have even tried the following, but still get errors?
ExportMessage is a SP.

//////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ExportMessages] (
@StartDate datetime,
@EndDate datetime
)
AS
BEGIN
SELECT * FROM [Message] where createdate between
@StartDate and
@EndDate
END
//////

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2009-08-13 00:01:20.748'
SET @EndDate = '2009-08-15 00:00:01.000'
EXEC
xp_cmdshell
'bcp "exec ExportMessages @StartDate,@EndDate" queryout "C:\Export\Message13-15-082009.txt" -T -c -t,'

SQLState = 42000, NativeError = 137
Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@StartDate".
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 05:49:39

Why did you replace the double single quote characters, with a single double quote character?

EXEC 
xp_cmdshell 
'bcp "SELECT * FROM [Message] where createdate between 
''2009-08-14 00:01:20.748'' and 
''2009-08-15 00:00:01.000''
" queryout "C:\Export\Message14-15-082009.txt" -T -c -t,'

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-17 : 08:51:56

For better control, use variable

declare @sql varchar(8000)
set @sql='bcp "SELECT * FROM [Message] where createdate between
''2009-08-14 00:01:20.748'' and
''2009-08-15 00:00:01.000''" queryout "C:\Export\Message14-15-082009.txt" -T -c -t,'
--print @sql
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources