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?
>
>.
>

BCP error

Hi,
Env: win2k, mssql 8.00.194, Standard Edition

Please help me to make the following bcp to work.
I want to save RS from store procedure to ascii file, using BCP. The sp creates temporary tables and the final select in sp is exec sp_executesql @.comm.
The following example demonstrates the problem:

declare @.bcpCommand nvarchar(100)
set @.bcpCommand = 'bcp "exec sp_helpdb" queryout "\\kkkk\bcp_dbs.log" -c'
exec master..xp_cmdshell @.bcpCommand

I'm getting the following error:
Password:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
NULL

Thanksdeclare @.bcpCommand nvarchar(100)
set @.bcpCommand = 'bcp "set fmtonly off exec sp_helpdb" queryout "c:\test.txt" -c'
exec master..xp_cmdshell @.bcpCommand|||Damn...who was that (un)masked man??

Very nice...but it's as clear as mud

BOL

Returns only meta data to the client.

Syntax
SET FMTONLY { ON | OFF }

Remarks
No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON.

The setting of SET FMTONLY is set at execute or run time and not at parse time.

Permissions
SET FMTONLY permissions default to all users.

Examples
This example changes the SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.

SET FMTONLY ON
GO
USE pubs
GO
SELECT *
FROM pubs.dbo.authors
GO|||Thanks for the replay - it works .

BCP - BULK INSERT - Memory mapped files

Hi,
I have an application server which receives several thousand events per
second. And it needs to store that data in an SQL server running on a
separate server connected thru network to the application server. I would
like to know the fastest and the efficient way to store the data in to SQL
server tables.
I looked in to BCP and BULK INSERT, but the question is how do I move the
data file(presumably created by the application server) to the SQL server to
use with BULK INSERT? I can probably create the file locally on the
application server, and use bcp_execute etc programatically, but is this the
best way? Since the data is already in the application memory, is it possible
make bcp_execute access the in-memory data, using memory-mapped file or some
such thing?
Thank you for your help, Srini
Check SqlBulkCopy class in msdn or .Net documentation.
You can feed it with any object (WriteToServer method) implementing
IDataReader
HTH
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:AB6E2972-4C04-47AE-BA3D-1BF7EEE827A5@.microsoft.com...
> Hi,
> I have an application server which receives several thousand events per
> second. And it needs to store that data in an SQL server running on a
> separate server connected thru network to the application server. I would
> like to know the fastest and the efficient way to store the data in to SQL
> server tables.
> I looked in to BCP and BULK INSERT, but the question is how do I move the
> data file(presumably created by the application server) to the SQL server
> to
> use with BULK INSERT? I can probably create the file locally on the
> application server, and use bcp_execute etc programatically, but is this
> the
> best way? Since the data is already in the application memory, is it
> possible
> make bcp_execute access the in-memory data, using memory-mapped file or
> some
> such thing?
> Thank you for your help, Srini
|||I am not using .Net Framework in my code. I need a solution using ODBC or BCP
API. etc.
Thanks for your help.
"AlexS" wrote:

> Check SqlBulkCopy class in msdn or .Net documentation.
> You can feed it with any object (WriteToServer method) implementing
> IDataReader
> HTH
> "Srini" <Srini@.discussions.microsoft.com> wrote in message
> news:AB6E2972-4C04-47AE-BA3D-1BF7EEE827A5@.microsoft.com...
>
>
|||Look up bcp_bind and bcp_sendrow in MSDN
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:62EB5419-1361-40BA-A66E-40EC2817A5BC@.microsoft.com...[vbcol=seagreen]
>I am not using .Net Framework in my code. I need a solution using ODBC or
>BCP
> API. etc.
> Thanks for your help.
> "AlexS" wrote:

BCP - BULK INSERT - Memory mapped files

Hi,
I have an application server which receives several thousand events per
second. And it needs to store that data in an SQL server running on a
separate server connected thru network to the application server. I would
like to know the fastest and the efficient way to store the data in to SQL
server tables.
I looked in to BCP and BULK INSERT, but the question is how do I move the
data file(presumably created by the application server) to the SQL server to
use with BULK INSERT? I can probably create the file locally on the
application server, and use bcp_execute etc programatically, but is this the
best way? Since the data is already in the application memory, is it possibl
e
make bcp_execute access the in-memory data, using memory-mapped file or some
such thing?
Thank you for your help, SriniCheck SqlBulkCopy class in msdn or .Net documentation.
You can feed it with any object (WriteToServer method) implementing
IDataReader
HTH
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:AB6E2972-4C04-47AE-BA3D-1BF7EEE827A5@.microsoft.com...
> Hi,
> I have an application server which receives several thousand events per
> second. And it needs to store that data in an SQL server running on a
> separate server connected thru network to the application server. I would
> like to know the fastest and the efficient way to store the data in to SQL
> server tables.
> I looked in to BCP and BULK INSERT, but the question is how do I move the
> data file(presumably created by the application server) to the SQL server
> to
> use with BULK INSERT? I can probably create the file locally on the
> application server, and use bcp_execute etc programatically, but is this
> the
> best way? Since the data is already in the application memory, is it
> possible
> make bcp_execute access the in-memory data, using memory-mapped file or
> some
> such thing?
> Thank you for your help, Srini|||I am not using .Net Framework in my code. I need a solution using ODBC or BC
P
API. etc.
Thanks for your help.
"AlexS" wrote:

> Check SqlBulkCopy class in msdn or .Net documentation.
> You can feed it with any object (WriteToServer method) implementing
> IDataReader
> HTH
> "Srini" <Srini@.discussions.microsoft.com> wrote in message
> news:AB6E2972-4C04-47AE-BA3D-1BF7EEE827A5@.microsoft.com...
>
>|||Look up bcp_bind and bcp_sendrow in MSDN
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:62EB5419-1361-40BA-A66E-40EC2817A5BC@.microsoft.com...[vbcol=seagreen]
>I am not using .Net Framework in my code. I need a solution using ODBC or
>BCP
> API. etc.
> Thanks for your help.
> "AlexS" wrote:
>