Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Syntax errors using bcp (Bulk Copy Program)

Author  Topic 

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-10 : 11:26:43
I have a query that works fine outside of bcp, but returns a syntax error when sent to the bcp. I thought it was quotation mark related, and may be, but I only have single quotes in the query.

Any ideas?

Error:

Incorrect syntax near ' + CONVERT(VARCHAR(3),A.CHARACTER_MAXIMUM_LENGTH) + '.


Note bcp requires the entire query on one line.

EXEC master..xp_cmdshell
'bcp "SELECT A.TABLE_NAME , A.COLUMN_NAME, (SELECT CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN A.DATA_TYPE + '(' + CONVERT(VARCHAR(3),A.CHARACTER_MAXIMUM_LENGTH) + ')' ELSE A.DATA_TYPE END) AS DATA_TYPE, (SELECT TC.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME WHERE TC.TABLE_SCHEMA = 'DBO' AND TC.CONSTRAINT_TYPE IN ('PRIMARY KEY','UNIQUE') AND TC.TABLE_NAME = A.TABLE_NAME AND KCU.COLUMN_NAME = A.COLUMN_NAME) AS KEYS FROM INFORMATION_SCHEMA.COLUMNS A WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'AIP%' AND TABLE_NAME NOT LIKE '%CHANGES%') ORDER BY TABLE_NAME " QUERYOUT "\\hostname\testing\SCHEMA01.TXT" -Uuser -Ppwd -c -ec:\error.txt'

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-10 : 11:58:14
A.DATA_TYPE + ''('' + CONVERT(VARCHAR(3),A.CHARACTER_MAXIMUM_LENGTH) + '')'' ELSE

TC.TABLE_SCHEMA = ''DBO'' AND TC.CONSTRAINT_TYPE IN (''PRIMARY KEY'',''UNIQUE'') AND TC.TABLE_NAME = A.TABLE_NAME AND KCU.COLUMN_NAME = A.COLUMN_NAME) AS KEYS FROM INFORMATION_SCHEMA.COLUMNS A WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''AIP%'' AND TABLE_NAME NOT LIKE ''%CHANGES%'')

those are two single quotes not a double quote.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-10 : 15:09:53
Hrm, still seems upset about those tics:

EXEC master..xp_cmdshell
'bcp "SELECT A.TABLE_NAME, A.COLUMN_NAME, (SELECT CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN A.DATA_TYPE + ''('' + CONVERT(VARCHAR(3),A.CHARACTER_MAXIMUM_LENGTH) + '')'' ELSE A.DATA_TYPE END) AS DATA_TYPE, (SELECT TC.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME WHERE TC.TABLE_SCHEMA = ''DBO'' AND TC.CONSTRAINT_TYPE IN (''PRIMARY KEY'',''UNIQUE'') AND TC.TABLE_NAME = A.TABLE_NAME AND KCU.COLUMN_NAME = A.COLUMN_NAME AS KEYS FROM INFORMATION_SCHEMA.COLUMNS A WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @MASK AND TABLE_NAME NOT LIKE ''%CHANGES%'') ORDER BY TABLE_NAME" QUERYOUT "\\hostname\testing\SCHEMA01.TXT" -Uuser -Ppwd -c -ec:\error.txt'



Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'DBO'.

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-02-11 : 01:28:51
You've changed the query and added @MASK, which is out of scope, and you've dropped a parenthesis before "AS KEYS".

Try getting the SELECT working first on its own, then double up all single quotes and then put it in double quotes in your BCP expression.

Or make it into a Stored Procedure and just call that from BCP - that will save having to double up all the single quotes.

Kristen
Go to Top of Page

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 11:09:43
hey hey! it works, thanks all!
Go to Top of Page
   

- Advertisement -