2012年3月6日星期二

BCP Errors

I execute a store procedure like
Sploadnew 'may'
The may is char type which is converted to a datetime and the query is
something like and I create a tem table
SELECT into ##tetable SUM(sales)+SUM(sales1)
FROM table
group by state
where datepart(mm,prod_date)=datepart(mm,@.datenew)
I want to BCP this result to a server location from within the store
procedure and I did
exec master..xp_cmdshell BCP ##tetable
OUT "//server/folder/rsult.txt"
-S server
-U sa
-P Password
I get this error
erver: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '##tetable'
what is wrong in this approach
Any Answers?I'm not quite sure where the error is occurring here, but
one way to help to determine this is instead of using the
actual BCP command syntax within the xp_cmdshell call
(which may be what is causing the SQL parser to find an
error), put your BCP command in a Windows NT .cmd command
file. Then use xp_cmdshell to execute this .cmd file.
This should eliminate any wacky allowable syntax
differences between what BCP allows and what the SQL
parser allows.
Also, I don't know if you left this out just in your post
or if you left it out in your stored proc, but you might
want to try enclosing the entire BCP command syntax in
single quotes:
exec master..xp_cmdshell 'BCP ##tetable
OUT "//server/folder/rsult.txt"
-S server
-U sa
-P Password'
generally, with xp_cmdshell, if your command syntax
includes spaces, the commnad syntax needs to be within
single quotes.
I hope that this helps.
Matthew Bando
BandoM@.CSCTechnologies (remove) . com
>--Original Message--
>I execute a store procedure like
>Sploadnew 'may'
>The may is char type which is converted to a datetime
and the query is
>something like and I create a tem table
>SELECT into ##tetable SUM(sales)+SUM(sales1)
>FROM table
>group by state
>where datepart(mm,prod_date)=datepart(mm,@.datenew)
>I want to BCP this result to a server location from
within the store
>procedure and I did
>exec master..xp_cmdshell BCP ##tetable
>OUT "//server/folder/rsult.txt"
>-S server
>-U sa
>-P Password
> I get this error
>erver: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '##tetable'
>what is wrong in this approach
>Any Answers?
>
>.
>

没有评论:

发表评论