BCP question

Hi,
I have just finished a BCP ,with 35 columns, it extracts data from a SQL
SERVER Database to a text file, using a format file. The next step I will ad
d
more 58 in it, but for my surprise when I run this BCP (with 35 columns) it
appears the message "Query hints exceed maximum command buffer size of 1023
bytes (1229 bytes input). : No error"
So, the question is : What can I do to supply my necessity ?
ThanksAdalberto Andrade (Adalberto Andrade@.discussions.microsoft.com) writes:
> I have just finished a BCP ,with 35 columns, it extracts data from a
> SQL SERVER Database to a text file, using a format file. The next step I
> will add more 58 in it, but for my surprise when I run this BCP (with 35
> columns) it appears the message "Query hints exceed maximum command
> buffer size of 1023 bytes (1229 bytes input). : No error"
That was indeed surprising. Can you post:
o CREATE TABLE statement
o The format file.
o And the BCP command.
Without a repro it's difficult to say much.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||A Google search turns up this potential workaround:
[url]http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1116[/
url]
<quote>
Hi all. Fixed my problem.
I now have to create a dynamic temporary stored procedure which selects
the result set I want exporting. I then ask bcp to execute this and
export its contents
A bit long winded though...
</quote>
and this reply (from Erland, whose recommendation to post more detail is
well-taken)
<quote>
One idea would be to create a global temporary stored procedure that
returns the result set you're after and then only pass call to that
procedure to BCP.
Something like this:
EXEC ('CREATE PROCEDURE ##temp AS ' + @.sql_str)
DECLARE @.bcpCommand varchar(2000)
SET @.bcpCommand = 'bcp "EXEC ##temp" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U U2ADJC1 -P xxxxxx -c'
SET QUOTED_IDENTIFIER OFF
EXEC master..xp_cmdshell @.bcpCommand
DROP PROCEDURE ##temp
Erland Sommarskog, SQL Server MVP
</quote>
Steve Kass
Drew University
Adalberto Andrade wrote:

>Hi,
> I have just finished a BCP ,with 35 columns, it extracts data from a SQ
L
>SERVER Database to a text file, using a format file. The next step I will a
dd
>more 58 in it, but for my surprise when I run this BCP (with 35 columns) it
>appears the message "Query hints exceed maximum command buffer size of 1023
>bytes (1229 bytes input). : No error"
>So, the question is : What can I do to supply my necessity ?
>
> Thanks
>
>|||Hi Steve,
I tried what you suggested me, but unfortunately the error message was
"Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"
And my script is :
DECLARE @.consulta varchar (4000)
SET @.consulta = 'SELECT i2017_insc_munic from DB002.dbo.ds2017 WHERE
i2017_data_pgto_y2>20050601'
EXEC ('CREATE PROCEDURE ##TEMP AS ' + @.consulta)
DECLARE @.bcpcomando varchar(4500)
SET @.bcpcomando = 'bcp "EXEC ##TEMP" queryout c:\dts\cis.txt -f
d:\users\sql\bcp\cis\formato.fmt -SPRODUCTIONDB -Umyuser -Pmypassword'
PRINT @.bcpcomando
SET QUOTED_IDENTIFIER OFF
exec master..xp_cmdshell @.bcpcomando
DROP PROCEDURE ##temp
Note : I can run the same BCP in the command line and it really work OK. I
think the program xp_cmdshell isn't seeing the bcp program. Isn't it ?
What is my mistake ? and What is wrong ?
Thanks a
lot
Adalberto Andrade
"Steve Kass" wrote:

> A Google search turns up this potential workaround:
> http://www.sql-server-performance.c...ID=1116

> <quote>
> Hi all. Fixed my problem.
> I now have to create a dynamic temporary stored procedure which selects
> the result set I want exporting. I then ask bcp to execute this and
> export its contents
> A bit long winded though...
> </quote>
> and this reply (from Erland, whose recommendation to post more detail is
> well-taken)
> <quote>
> One idea would be to create a global temporary stored procedure that
> returns the result set you're after and then only pass call to that
> procedure to BCP.
> Something like this:
> EXEC ('CREATE PROCEDURE ##temp AS ' + @.sql_str)
> DECLARE @.bcpCommand varchar(2000)
> SET @.bcpCommand = 'bcp "EXEC ##temp" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U U2ADJC1 -P xxxxxx -c'
> SET QUOTED_IDENTIFIER OFF
> EXEC master..xp_cmdshell @.bcpCommand
> DROP PROCEDURE ##temp
> --
> Erland Sommarskog, SQL Server MVP
> </quote>
> Steve Kass
> Drew University
> Adalberto Andrade wrote:
>
>|||Adalberto,
Is the D: drive a network share? If so, the SQL Server account may not
know about it.
Drive letter network mappings are defined for users, and you could try
referring to
the file by its UNC name (with slashes and a server name - the name you
specify
when you set up a network mapping).
SK
Adalberto Andrade wrote:
>Hi Steve,
>I tried what you suggested me, but unfortunately the error message was
>"Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"
>And my script is :
>DECLARE @.consulta varchar (4000)
>SET @.consulta = 'SELECT i2017_insc_munic from DB002.dbo.ds2017 WHERE
>i2017_data_pgto_y2>20050601'
>EXEC ('CREATE PROCEDURE ##TEMP AS ' + @.consulta)
>DECLARE @.bcpcomando varchar(4500)
>SET @.bcpcomando = 'bcp "EXEC ##TEMP" queryout c:\dts\cis.txt -f
>d:\users\sql\bcp\cis\formato.fmt -SPRODUCTIONDB -Umyuser -Pmypassword'
>PRINT @.bcpcomando
>SET QUOTED_IDENTIFIER OFF
>exec master..xp_cmdshell @.bcpcomando
>DROP PROCEDURE ##temp
>Note : I can run the same BCP in the command line and it really work OK. I
>think the program xp_cmdshell isn't seeing the bcp program. Isn't it ?
>What is my mistake ? and What is wrong ?
>
> Thanks
a
>lot
>Adalberto Andrade
>
>"Steve Kass" wrote:
>
>|||Hi Steve,
D is a drive of my machine because I was running in a client. So I
think SQL SERVER didn't have visibility and don't know what or where it.
I decided running this script in the SERVER machine to avaid problem and
substituted D by C (with the same directory of the format file) and
everything worked OK.
Thanks more
one time,
Adalberto
Andrade
"Steve Kass" wrote:

> Adalberto,
> Is the D: drive a network share? If so, the SQL Server account may not
> know about it.
> Drive letter network mappings are defined for users, and you could try
> referring to
> the file by its UNC name (with slashes and a server name - the name you
> specify
> when you set up a network mapping).
> SK
>
> Adalberto Andrade wrote:
>
>

BCP Error handling

I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.Are your BCP statements in a stored procedure?|||Here is something I hope will help:

declare
@.FILENAME_C nvarchar(255),
@.sqlstmt2 nvarchar(255),
@.outputcatch varchar(1000)

SET @.FILENAME_C= 'c:\it.txt'
SET @.SQLSTMT2='BCP "SELECT claimnumber from demo.dbo.wcclm1" queryout '+@.FILENAME_C+' /c /t, /r \n /U demo /P demo /S '+@.@.servername
EXEC @.outputcatch=MASTER.DBO.XP_CMDSHELL @.SQLSTMT2, no_output --@.outputcatch=OUTPUT

select @.outputcatch

Note that this sample has a simple select statement. If I change claimnumber to clamnumber @.outputcatch is 1 due to failure. Put it back to claimnumber and it returns 0 which indicates success.

Just keep looking at @.outputcatch and if you get a 1 RETURN.

bcp

Hello World,
I have to load table from a a delimited file. The delimited char is (";").
Bcp is used with a format file (*.fmt).
First step :
The table contain a date field.
In the import file, the date format is : DD/MM/YYYY.
When the import runs, I obtain the message like : right truncating, so the
import stops.
Second step :
The struct table is modified with a varchar (10) for the date field.
The format file is modified.
The import runs well.
My question is :
Can we import directly date field from a file with bcp ?
If it is possible, how can we do it ?
Thank's in advance.
MLTry reformatting the source file so that the datatime values are in an
unambiguous format "yyyymmdd", or (which is my favourite) connect to the fil
e
using sp_addlinkedserver and describe it's format using the schema.ini file,
which enables you to query the file as if it were a regular SQL table.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
(Example H.)
...and here:
http://msdn.microsoft.com/library/d...ma_ini_file.asp
ML|||Hi
I would expect that you would get a message about the date being invalid if
dates were being interpretted as MM/DD/YYYY, therefore it may not be your
date field where this error message is being generated!!!
You may want to post your format file, command and a small section of
example data that re-creates this problem.
John
"Michel" wrote:

> Hello World,
> I have to load table from a a delimited file. The delimited char is (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating, so the
> import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
>
> Thank's in advance.
> ML
>
>|||Michel wrote:
> Hello World,
> I have to load table from a a delimited file. The delimited char is
> (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating,
> so the import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
You can use DTS and define a transformation for this column.
You could also try to not use bcp but do it from QA: change the session's
date format and use BULK INSERT (untested)
http://support.microsoft.com/defaul...kb;en-us;173907
http://msdn.microsoft.com/library/d...br />
4fec.asp
Kind regards
robert