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:
>
>