Yet another BCP issue

**Erland Sommarskog** · Jul 20 '05, 03:50 AM

Re: Yet another BCP issue

bc (bkc98@excite.c om) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

bc · Jul 20 '05, 03:51 AM

Re: Yet another BCP issue

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns94F7F2 562D8D1Yazorman @127.0.0.1>...[color=blue]
> bc (bkc98@excite.c om) writes:[color=green]
> > 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.[/color]
>
> 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.[/color]

Okay, here goes...

Here's my BCP command:
bcp "EXEC SCMDC..scm_extr acts_SelectByTa bleName
'ITEM_SERVICE_C ONTROL'" queryout
\\ausscmdevdb02 \bc_test\SCMDC_ Extracts\chassi s.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_LOC ATION_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(ful fillment_loc_id AS nvarchar(4000)) , '')
+ '"|' + CASE WHEN sp_flag = 1 THEN 'True|' WHEN sp_flag IS NULL THEN
'|' ELSE 'False|' END FROM fulfillment_loc ation_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_Extract s_SelectByTable Name
@p_sExtractTabl eName varchar(1000),
@p_sExtractHint varchar(1000) = ''

AS SET NOCOUNT ON

DECLARE @sExtractQuery nvarchar(4000)
DECLARE @sCharValueSele ct nvarchar(1000)
DECLARE @sIntValueSelec t nvarchar(1000)
DECLARE @sMoneyValueSel ect nvarchar(1000)
DECLARE @sDateTimeValue Select nvarchar(1000)
DECLARE @sBitValueSelec t nvarchar(1000)
DECLARE @sUniqueIDSelec t nvarchar(1000)

SET @sExtractQuery = 'SELECT '
SET @sCharValueSele ct = ' + ''"'' + ISNULL(CAST(**C OLUMN** AS
nvarchar(4000)) , '''') + ''"|'' '
SET @sIntValueSelec t = ' + ISNULL(CAST(**C OLUMN** AS
nvarchar(4000)) , '''') + ''|'' '
SET @sMoneyValueSel ect = ' + ISNULL(CAST(**C OLUMN** AS
nvarchar(4000)) , ''0'') + ''|'' '
SET @sDateTimeValue Select = ' + ISNULL(CONVERT( nvarchar(4000),
**COLUMN**, 121), '''') + ''|'' '
SET @sBitValueSelec t = ' + CASE WHEN **COLUMN** = 1 THEN ''True|''
WHEN **COLUMN** IS NULL THEN ''|'' ELSE ''False|'' END'
SET @sUniqueIDSelec t = ' + ''{'' + ISNULL (CONVERT(nvarch ar(36),
**COLUMN**), '''') + ''}'' + ''|'' '

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

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

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

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

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

-- Unique Identifier
WHEN 'uniqueidentifi er' THEN REPLACE(@sUniqu eIDSelect,
'**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_sExtractTabl eName) 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(@sE xtractQuery, 3))
-- print @sExtractQuery

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

SET @sExtractQuery = RTRIM(LTRIM(@sE xtractQuery)) + ' FROM ' +
RTRIM(LTRIM(@p_ sExtractTableNa me)) + ' ' +
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

**Erland Sommarskog** · Jul 20 '05, 03:51 AM

Re: Yet another BCP issue

bc (bkc98@excite.c om) writes:[color=blue]
> Here's my BCP command:
> bcp "EXEC SCMDC..scm_extr acts_SelectByTa bleName
> 'ITEM_SERVICE_C ONTROL'" queryout
> \\ausscmdevdb02 \bc_test\SCMDC_ Extracts\chassi s.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_LOC ATION_CLASS '.
>[/color]

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_YIE LDS_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_YIE LDS_NULL, ANSI_NULLS,
and ANSI_WARNINGS and ANSI_PADDING. The only option have control
over is QUOTED_IDENTIFI ER.

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

bc · Jul 20 '05, 03:55 AM

Re: Yet another BCP issue

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns94FBF0 F1D1E63Yazorman @127.0.0.1>...[color=blue]
> bc (bkc98@excite.c om) writes:[color=green]
> > Here's my BCP command:
> > bcp "EXEC SCMDC..scm_extr acts_SelectByTa bleName
> > 'ITEM_SERVICE_C ONTROL'" queryout
> > \\ausscmdevdb02 \bc_test\SCMDC_ Extracts\chassi s.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_LOC ATION_CLASS '.
> >[/color]
>
> 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_YIE LDS_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_YIE LDS_NULL, ANSI_NULLS,
> and ANSI_WARNINGS and ANSI_PADDING. The only option have control
> over is QUOTED_IDENTIFI ER.
>
> 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.[/color]

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

**Erland Sommarskog** · Jul 20 '05, 03:56 AM

Re: Yet another BCP issue

bc (bkc98@excite.c om) writes:[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

bc · Jul 20 '05, 04:01 AM

Re: Yet another BCP issue

>[color=blue]
> 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.[/color]

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_Extr acts_SelectByTa bleName
'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,nv archar,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,sysc olumns 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

**Erland Sommarskog** · Jul 20 '05, 04:02 AM

Re: Yet another BCP issue

bc (bkc98@excite.c om) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

bc · Jul 20 '05, 04:03 AM

Re: Yet another BCP issue

>[color=blue]
> 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.[/color]

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.

[color=blue]
> Ideally, I would start the bulk copy from C# as well, but there is no bulk-
> copy interface in ADO .Net.[/color]

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

**Erland Sommarskog** · Jul 20 '05, 04:03 AM

Re: Yet another BCP issue

bc (bkc98@excite.c om) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

Yet another BCP issue

Yet another BCP issue

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment