What is wrong with this statement

[FatalExceptionError]

I have the following statement in Query analyzer

declare @file_name varchar(255)
declare @current_date_time varchar(255)

select @current_date_time = REPLACE(CAST(GETDATE() as VARCHAR),' ','_') 

set @file_name = 'C:\'+ @current_date_time + '.txt'

select @file_name as 'filename'

execute master..xp_cmdshell 
'bcp 
"select date, id 
from dbo.Weights where customer = ''xxx''" 
QUERYOUT @filename -Ssomeserver  -T -c'
[code]

I get the correct filename as output but for the results that should show all I get is the "usage bcp"

I can't see what is wrong with my syntax can someone help me out. 

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[FatalExceptionError]

Proper code format because I don't know how to use preview or find edit buttons.

declare @file_name varchar(255)
declare @current_date_time varchar(255)

select @current_date_time = REPLACE(CAST(GETDATE() as VARCHAR),' ','_')

set @file_name = 'C:\'+ @current_date_time + '.txt'

select @file_name as 'filename'

execute master..xp_cmdshell
'bcp
"select date, id
from dbo.Weights where customer = ''xxx''"
QUERYOUT @filename -Ssomeserver  -T -c'

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[mrdenny]

Double quotes around the filename?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vongrunt]

I'm not sure @filename is visible inside EXECUTEd string... what about ' + @filename + ' ... with some quotes around?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[FatalExceptionError]

I tried that but even when I simply change it to

execute master..xp_cmdshell
'bcp
"select date, id
from dbo.Weights where customer = ''xxx''"
QUERYOUT C:\foo.txt -Ssomeserver  -T -c'

I get the same thing. It is weird because it worked when I left the other day. I am running off 2 hours of sleep, pepsi and chocolate it is probably something stupid.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[vongrunt]

Try without carriage returns:

execute master..xp_cmdshell
'bcp "select date, id from dbo.Weights where customer = ''xxx''" QUERYOUT C:\foo.txt -Ssomeserver -T -c'

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[FatalExceptionError]

sweet thinks vongrunt that worked.

Going to make things look nasty because the actual statement is very long.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[FatalExceptionError]

Thanks everyone I got it to work right with filename.

Just in case someone cares to know, I simply broke the bcp command into multiple strings so it would be easier on the eyes. I then made one large string and passed that to xp_cmdshell.

P.S. If there is a downfall to do that would someone let me know please.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't