467,923 Members | 1,262 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

Yet another BCP issue

bc
Hello all,

Background:
I've posted a couple questions recently regarding using BCP to extract
many tables to text files. I've written an extract application that
bcp's about 100 tables from a database to text files. I've run into a
couple limitations using BCP, but I finally landed on (what I thought)
was a solid solution. I now call bcp passing a sproc name and a
parameter or two and having that sproc execute the query for me,
thereby doing the extracts to the proper files.

Background-Detail:
The sproc that executes the query seems to be working just fine for
all but a couple tables. I have a need for extracting files in a
particular format (quoted and pipe-delimited, with quoted and piped
column headers as the first row in the resulting file), and that's
what my sproc does. For a given table name, I select all the columns
and interrogate their column type (systype) and wrap the proper
formatting around the particular field value (i.e. "|" + field_name +
"|"). After building the proper SQL query, I execute it and the
results are bcp'd to the specified extract file.

Issue:
For some reason, any table that contains a bit field as the last field
in the table design, BCP cannot execute the query, claiming there's a
syntax error near the end of my query (unclosed quotation mark). For
bit field types in SQL Server, the select query that I build for the
format of the extract involves a SELECT..CASE statement to convert a 1
to TRUE and 0 to FALSE. The odd thing is that the query that is built
in the sproc will work just fine in SQL Query Analyzer, as well as
just putting the desired query in a varchar variable in a sproc
(without building it by looking up field types) and executing it via
BCP. BCP only croaks when I run the sproc as designed and the final
field selected in the generated query has a type of 'bit'. There are
other tables that contain bit fields and BCP seems to run the extract
queries just fine for those instances. The only difference being that
those table designs don't contain a bit field as the last field.

It's a tough thing to accurately describe, so please reply with
further questions, and I'll try to help you better understand. But the
root issue is that the SAME EXACT query will work in query analyzer as
well as a stored procedure, but not when executing the stored
procedure via BCP.

Anyone seen this type of inconsistency using BCP?

Thanks for any help on this one.

/bc
Jul 20 '05 #1
  • viewed: 9574
Share:
9 Replies
bc (bk***@excite.com) writes:
For some reason, any table that contains a bit field as the last field
in the table design, BCP cannot execute the query, claiming there's a
syntax error near the end of my query (unclosed quotation mark). For
bit field types in SQL Server, the select query that I build for the
format of the extract involves a SELECT..CASE statement to convert a 1
to TRUE and 0 to FALSE. The odd thing is that the query that is built
in the sproc will work just fine in SQL Query Analyzer, as well as
just putting the desired query in a varchar variable in a sproc
(without building it by looking up field types) and executing it via
BCP. BCP only croaks when I run the sproc as designed and the final
field selected in the generated query has a type of 'bit'. There are
other tables that contain bit fields and BCP seems to run the extract
queries just fine for those instances. The only difference being that
those table designs don't contain a bit field as the last field.


This could certainly be easier to answer if you included:

o The BCP command.
o The error message.
o The stored procedure.
o The actually generated SQL.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
bc
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
bc (bk***@excite.com) writes:
For some reason, any table that contains a bit field as the last field
in the table design, BCP cannot execute the query, claiming there's a
syntax error near the end of my query (unclosed quotation mark). For
bit field types in SQL Server, the select query that I build for the
format of the extract involves a SELECT..CASE statement to convert a 1
to TRUE and 0 to FALSE. The odd thing is that the query that is built
in the sproc will work just fine in SQL Query Analyzer, as well as
just putting the desired query in a varchar variable in a sproc
(without building it by looking up field types) and executing it via
BCP. BCP only croaks when I run the sproc as designed and the final
field selected in the generated query has a type of 'bit'. There are
other tables that contain bit fields and BCP seems to run the extract
queries just fine for those instances. The only difference being that
those table designs don't contain a bit field as the last field.


This could certainly be easier to answer if you included:

o The BCP command.
o The error message.
o The stored procedure.
o The actually generated SQL.


Okay, here goes...

Here's my BCP command:
bcp "EXEC SCMDC..scm_extracts_SelectByTableName
'ITEM_SERVICE_CONTROL'" queryout
\\ausscmdevdb02\bc_test\SCMDC_Extracts\chassis.txt -c -r \r\n -S
ausscmdevdb02 -U <user> -P <password>

Here's the error message the BCP command displays:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax
near 'E'. SQLState = 37000, NativeError = 105 Error = [Microsoft][ODBC
SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ' FROM FULFILLMENT_LOCATION_CLASS '.

Here's some sample generated SQL that the sproc eventually runs (you
should be able to put this query in Query Analyzer to see it better).
This is the query that works just fine in Query Analyzer, but not thru
BCP!:

SELECT + '"' + ISNULL(CAST(fulfillment_loc_id AS nvarchar(4000)), '')
+ '"|' + CASE WHEN sp_flag = 1 THEN 'True|' WHEN sp_flag IS NULL THEN
'|' ELSE 'False|' END FROM fulfillment_location_class


Here's the stored procedure code (kind of long). You should be able to
put this in Query Analyzer and see it okay. Can look at my initial
post in this thread to see a brief explanation of what I'm doing in
the sproc:

CREATE PROCEDURE dbo.scm_Extracts_SelectByTableName
@p_sExtractTableName varchar(1000),
@p_sExtractHint varchar(1000) = ''

AS SET NOCOUNT ON

DECLARE @sExtractQuery nvarchar(4000)
DECLARE @sCharValueSelect nvarchar(1000)
DECLARE @sIntValueSelect nvarchar(1000)
DECLARE @sMoneyValueSelect nvarchar(1000)
DECLARE @sDateTimeValueSelect nvarchar(1000)
DECLARE @sBitValueSelect nvarchar(1000)
DECLARE @sUniqueIDSelect nvarchar(1000)

SET @sExtractQuery = 'SELECT '
SET @sCharValueSelect = ' + ''"'' + ISNULL(CAST(**COLUMN** AS
nvarchar(4000)), '''') + ''"|'' '
SET @sIntValueSelect = ' + ISNULL(CAST(**COLUMN** AS
nvarchar(4000)), '''') + ''|'' '
SET @sMoneyValueSelect = ' + ISNULL(CAST(**COLUMN** AS
nvarchar(4000)), ''0'') + ''|'' '
SET @sDateTimeValueSelect = ' + ISNULL(CONVERT(nvarchar(4000),
**COLUMN**, 121), '''') + ''|'' '
SET @sBitValueSelect = ' + CASE WHEN **COLUMN** = 1 THEN ''True|''
WHEN **COLUMN** IS NULL THEN ''|'' ELSE ''False|'' END'
SET @sUniqueIDSelect = ' + ''{'' + ISNULL (CONVERT(nvarchar(36),
**COLUMN**), '''') + ''}'' + ''|'' '

SELECT @sExtractQuery = @sExtractQuery +
CASE types.name
-- Text
WHEN 'nvarchar' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'varchar' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'char' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'nchar' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'ntext' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'text' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'char' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)
WHEN 'sql_variant' THEN REPLACE(@sCharValueSelect, '**COLUMN**',
cols.name)

-- Numbers
WHEN 'int' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'decimal' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'float' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'tinyint' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'bigint' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'smallint' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'binary' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'varbinary' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'numeric' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)
WHEN 'real' THEN REPLACE(@sIntValueSelect, '**COLUMN**',
cols.name)

-- Money
WHEN 'money' THEN REPLACE(@sMoneyValueSelect, '**COLUMN**',
cols.name)
WHEN 'smallmoney' THEN REPLACE(@sMoneyValueSelect, '**COLUMN**',
cols.name)

-- Datetime
WHEN 'datetime' THEN REPLACE(@sDateTimeValueSelect, '**COLUMN**',
cols.name)
WHEN 'timestamp' THEN REPLACE(@sDateTimeValueSelect, '**COLUMN**',
cols.name)
WHEN 'smalldatetime' THEN REPLACE(@sDateTimeValueSelect,
'**COLUMN**', cols.name)

-- Bit
WHEN 'bit' THEN REPLACE(@sBitValueSelect, '**COLUMN**',
cols.name)

-- Unique Identifier
WHEN 'uniqueidentifier' THEN REPLACE(@sUniqueIDSelect,
'**COLUMN**', cols.name)
--ELSE ''
END
FROM syscolumns cols
INNER JOIN sysobjects obj ON
obj.id = cols.id
INNER JOIN systypes types ON
types.xtype = cols.xtype
WHERE obj.id = (SELECT id FROM sysobjects WHERE name =
@p_sExtractTableName) AND
types.name <> 'sysname' -- I noticed some columns are repeated in
syscolumns for a given table. The only difference
-- is the column type. So a column name can be listed 2x in
syscolumns, but one record will have
-- a type of 'nvarchar' and the other 'sysname'. For this
purpose, I want DON'T want the sysname type.
ORDER BY cols.colid

--
-- Add From clause with table name and any optional query hints
(where clauses and/or order by clauses)
--

-- Modify the query string to remove the last two characters (i.e. |'
) so the query will execute properly.
-- If the last select statement in the query string IS NOT a CASE
statement, then don't edit the string built thus far.
DECLARE @CheckString nvarchar(20)
SET @CheckString = RTRIM(RIGHT(@sExtractQuery, 3))
-- print @sExtractQuery

IF (@CheckString <> 'END')
SET @sExtractQuery = LEFT(@sExtractQuery,LEN(@sExtractQuery)-2) +
'''' -- remove the last two characters (i.e. |' ) from query
--ELSE
--SET @sExtractQuery = @sExtractQuery + '"'

SET @sExtractQuery = RTRIM(LTRIM(@sExtractQuery)) + ' FROM ' +
RTRIM(LTRIM(@p_sExtractTableName)) + ' ' +
RTRIM(LTRIM(@p_sExtractHint))

EXEC sp_executesql @sExtractQuery

SET NOCOUNT OFF
GO

Thanks a lot for any help on this. It's a weird one...

/bc
Jul 20 '05 #3
bc (bk***@excite.com) writes:
Here's my BCP command:
bcp "EXEC SCMDC..scm_extracts_SelectByTableName
'ITEM_SERVICE_CONTROL'" queryout
\\ausscmdevdb02\bc_test\SCMDC_Extracts\chassis.txt -c -r \r\n -S
ausscmdevdb02 -U <user> -P <password>

Here's the error message the BCP command displays:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax
near 'E'. SQLState = 37000, NativeError = 105 Error = [Microsoft][ODBC
SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ' FROM FULFILLMENT_LOCATION_CLASS '.


I have looked at your procedure, and I'm afraid that it is back to the
drawing board.

There are two problems with the procedure. When I first ran it, and I
added a "SELECT @sExtractQuery", NULL was all I got. When I first
ran the command "SET CONCAT_NULL_YIELDS_NULL OFF", I did get some
SQL. However, BCP is an ODBC client, which means that by default it
runs with these options on: CONCAT_NULL_YIELDS_NULL, ANSI_NULLS,
and ANSI_WARNINGS and ANSI_PADDING. The only option have control
over is QUOTED_IDENTIFIER.

The second problem is the constrcut:

SELECT @x = @x + val FROM tbl ORDER BY val

This is called aggregate concatenation, and there is no correct behaviour
defined for this. That is, you may get what you expect, or you may get
something different. So you are better of replacing this with a cursor
loop.

See also http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
bc
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
bc (bk***@excite.com) writes:
Here's my BCP command:
bcp "EXEC SCMDC..scm_extracts_SelectByTableName
'ITEM_SERVICE_CONTROL'" queryout
\\ausscmdevdb02\bc_test\SCMDC_Extracts\chassis.txt -c -r \r\n -S
ausscmdevdb02 -U <user> -P <password>

Here's the error message the BCP command displays:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax
near 'E'. SQLState = 37000, NativeError = 105 Error = [Microsoft][ODBC
SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string ' FROM FULFILLMENT_LOCATION_CLASS '.


I have looked at your procedure, and I'm afraid that it is back to the
drawing board.

There are two problems with the procedure. When I first ran it, and I
added a "SELECT @sExtractQuery", NULL was all I got. When I first
ran the command "SET CONCAT_NULL_YIELDS_NULL OFF", I did get some
SQL. However, BCP is an ODBC client, which means that by default it
runs with these options on: CONCAT_NULL_YIELDS_NULL, ANSI_NULLS,
and ANSI_WARNINGS and ANSI_PADDING. The only option have control
over is QUOTED_IDENTIFIER.

The second problem is the constrcut:

SELECT @x = @x + val FROM tbl ORDER BY val

This is called aggregate concatenation, and there is no correct behaviour
defined for this. That is, you may get what you expect, or you may get
something different. So you are better of replacing this with a cursor
loop.

See also http://support.microsoft.com/default.aspx?scid=287515.


Erland,

Thanks for your help with this issue thus far. I appreciate it very
much.

Reading the link you provided that regarding aggregate conact.
statements and order by clauses was beneficial. Just commenting out my
order by clause allowed all necessary queries to work from BCP.
However, I need that order by clause, so I followed your advice and
used a cursor to build my select and then execute it, but I now get
the old 'BCP host-files must contain at least one column' error for
ALL queries, not just the couple that were bothering me before.

So, I did away with the cursor and opted for loading a temp table with
the values I need to key off of (where I would normally issue the
order by), and built the select from the temp table (already in the
order I need it), but I get the same original error - complaining
about an unclosed quote in the built select statement.

I'm beginning to think that your previous comment about going back to
the drawing board is right-on.

I'm about to throw in the towel on this BCP crap.

/bc
Jul 20 '05 #5
bc (bk***@excite.com) writes:
However, I need that order by clause, so I followed your advice and
used a cursor to build my select and then execute it, but I now get
the old 'BCP host-files must contain at least one column' error for
ALL queries, not just the couple that were bothering me before.
Which probably have to do with the fact that when you queryout, BCP
must find out how the result set looks like, and therefore issues
the query first once with SET FMTONLY ON. In this mode, SQL Server
just sifts through the statements without executing them and reports
all result sets it finds. But if you are using temp tables that you
create in the procedure, there will be no result set to find.

I have not used queryout myself that much, but I've seen other MVPs
point out that queryout is not that robust.
So, I did away with the cursor and opted for loading a temp table with
the values I need to key off of (where I would normally issue the
order by), and built the select from the temp table (already in the
order I need it), but I get the same original error - complaining
about an unclosed quote in the built select statement.

I'm beginning to think that your previous comment about going back to
the drawing board is right-on.

I'm about to throw in the towel on this BCP crap.


BCP is not crap, but you have to take it for what it is. It is certainly
quite a squared tool. In many cases it is able to do the job, but if
you try to push it to its limits, you fall through quite soon.

I don't know exactly what you are really trying to do, but I get the
feeling that you are doing the mistake of trying to do everything in
T-SQL. My guess is that if I had your problem I would write a Perl
script which extracted the table information and save all data to
extract into a temp table, and then used the BCP API to bulk copy
the stuff out.

You don't have to do it in Perl, but you would to use something
from which you can access the BCP API, so you might have to resort
to C++, as there is no object model available.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
bc
>
I don't know exactly what you are really trying to do, but I get the
feeling that you are doing the mistake of trying to do everything in
T-SQL. My guess is that if I had your problem I would write a Perl
script which extracted the table information and save all data to
extract into a temp table, and then used the BCP API to bulk copy
the stuff out.

You don't have to do it in Perl, but you would to use something
from which you can access the BCP API, so you might have to resort
to C++, as there is no object model available.


I have a C# app that executes BCP commands to extract data from each
of my tables. All tables need to be extracted, but some are too large
for a single extract file. This situation requires ranges of data to
be pulled for each extract. Each BCP command includes a call to a a
stored proc, passing in a table name and optional hints (where clause
criteria, order by), as well as db login info.

Example: bcp "exec scmdc..scm_Extracts_SelectByTableName
'ITEM_CATALOG','WHERE item_catalog_id between 10 and 19 order by
catalog_id asc'" -queryout -c \r\n \\file\location\name.txt <login
info>

The actual WORK is done in the stored proc. The extracts must be
formatted in a certain way. All fields should be pipe-delimited. If a
field is a char,varchar,nvarchar,etc (text field), the value in the
extract should be quote-delimited (i.e. |"foo"|). If it's a numeric or
datetime field, there should NOT be any quotes -- just between the
pipes (i.e. |12345|). In order to determine how to format the
extracted data, I need to determine a field's type, then build a
SELECT statement and subsequently execute it to run the extract.

The stored proc logic:
- Get all fields for a given table and their type.
- Do this by selecting col_id, col_name, col_type from
sysobjects,syscolumns db tables.
- Then use aggregate concatenation to build the select statement that
will be run to create an extract with the correct format for all the
records selected.
I spawn off multiple threads to do several BCP extracts at a time,
logging the number of records extracted and any errors along the way.

Hope that makes sense.

Erland, your help (along with others) in this forum has been a HUGE
help. I'll try and post my final stored proc code here soon. It's very
concise and quite speedy.
later,

/bc
Jul 20 '05 #7
bc (bk***@excite.com) writes:
I have a C# app that executes BCP commands to extract data from each
of my tables. All tables need to be extracted, but some are too large
for a single extract file. This situation requires ranges of data to
be pulled for each extract. Each BCP command includes a call to a a
stored proc, passing in a table name and optional hints (where clause
criteria, order by), as well as db login info.


I would do the work to build the SQL statements in the C# app. That
would save you from all the hassle of BCP queryout playing tricks with
you, because BCP would just see the final SELECT statement.

Ideally, I would start the bulk copy from C# as well, but there is no bulk-
copy interface in ADO .Net.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8
bc
>
I would do the work to build the SQL statements in the C# app. That
would save you from all the hassle of BCP queryout playing tricks with
you, because BCP would just see the final SELECT statement.
Yeah, I tried that initially (actually wrote the code in C# using
SQLDMO to look at a table and get the fields/types and built the
select statement), but the BCP queryout SQL hint got too large (1024
byte limit) on a couple tables, so that's why I went to TSQL. It's
actually working quite well now. The TSQL that I run to build the
select statement is sub-second in my unit tests. I'll send you the
..sql file of my sproc and you can have a look-see.

Ideally, I would start the bulk copy from C# as well, but there is no bulk-
copy interface in ADO .Net.


I used the SQLDMO COM component inside of C# to get the fields/types
for a particular table in my previous attempts (as noted in my
previous posts on this issue) at doing extracts using BCP. It seemed
to work just fine, albeit a little slow.

Thanks,

/bc
Jul 20 '05 #9
bc (bk***@excite.com) writes:
Yeah, I tried that initially (actually wrote the code in C# using
SQLDMO to look at a table and get the fields/types and built the
select statement), but the BCP queryout SQL hint got too large (1024
byte limit) on a couple tables, so that's why I went to TSQL. It's
actually working quite well now. The TSQL that I run to build the
select statement is sub-second in my unit tests. I'll send you the
.sql file of my sproc and you can have a look-see.


Could have contructed views, and bulked from those. The views could
have been tempdb to not litter the main database.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by George Hester | last post: by
2 posts views Thread by Anthony Cuttitta Jr. | last post: by
reply views Thread by Kevin Spencer | last post: by
reply views Thread by Charles Leonard | last post: by
4 posts views Thread by Paul | last post: by
1 post views Thread by AlekseyUS | last post: by
13 posts views Thread by SAL | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.