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
