BCP to SP Returns Conversion Error

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 2005 Forums

Transact-SQL (2005)

BCP to SP Returns Conversion Error

Author

Topic

BobW
Starting Member

3 Posts

Posted - 2010-09-02 : 16:27:23

Folks -

I have a process attempting to execute BCP which calls a SP with multiple parameters. Upon execution the process throws an error in SSMS - "Error = [Microsoft][SQL Native Client][SQL Server]Error converting data type varchar to int." I need a wee bit of direction on how to determine where this error originates. I can run the SP using the same set of parameters created in the BCP process (listed below) and it executes successfully.

Declaratives & Ugly BCP commands:
DECLARE @DocID Int
DECLARE @SaleFlag varchar(3)
DECLARE @CreditDebit char(1)
DECLARE @TPID varchar(50)
DECLARE @Name char(50)
DECLARE @PartnerID varchar(25)
DECLARE @PartnerIdentifierAgency varchar(30)
DECLARE @FileName varchar(255)
DECLARE @SQLCmd varchar(2000)

SET @SQLCmd = 'bcp "EXEC Test_EDI_Sales_History.dbo.usp_Build_867_XML_BCP ''@DocID, @SaleFlag, @CreditDebit, @TPID, @Name, @PartnerIdentifierAgency, @PartnerID''" queryout ' + @FileName + ' -c' + ' -S'+ @@ServerName + ' -T'

Result of PRINT for @SQLCmd:
bcp "EXEC Test_EDI_Sales_History.dbo.usp_Build_867_XML_BCP '@DocID, @SaleFlag, @CreditDebit, @TPID, @Name, @PartnerIdentifierAgency, @PartnerID'" queryout D:\XMLOutbound\047908165P\047908165P_21189_20109216131173.xml -c -SPALES -T

Values for the SP parms(in order):
21189
WSS
D
047908165P
XXXX CORPORATION
IDENTIFIER
001307032

Declaratives for called SP:
@DocID int,
@Sale_Type_Flag as varchar(3),
@CreditDebit as char(1),
@TPID as varchar(50),
@VendorName as varchar(30),
@PartnerIdentifierAgency as varchar(30),
@PartnerID as varchar(25),
@xml as VARCHAR(max) OUTPUT -- XML Result

Thank You for Giving This a Look! Ordinarily I'd paste in all the code for the calling and called processes but that would be a horrendous amount of code - the called SP produces XML documents and dare I say it? The calling process uses a dreaded cursor....I'll be happy to email it to whomever might like to give it a look though.

Bob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-02 : 16:44:31

You need to convert @DocID to varchar in order to put it into @SQLCmd, which is varchar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BobW
Starting Member

3 Posts

Posted - 2010-09-02 : 17:02:12

Thank You Tara Almighty SQL Goddess! I'll certainly give that a go and if they had an emoticon for it, I'd use embarrassed......dunno how I missed that particular gem!

Take Care,

Bob

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-02 : 17:45:26

Everyday I learn something that somebody else already knew

BobW
Starting Member

3 Posts

Posted - 2010-09-03 : 10:34:11

Tara (ASG) -

Can you tell me if the parameters used on the BCP command need to be individually surrounded by quotes? When the process attempts to execute the BCP, it throws an error telling me the SP expects the 2nd parameter in the list but it was "not supplied". I've checked the list of parms on the BCP and the SP - they match in name, order and data type. So I'm beginning to wonder if each parm needs to be quoted separately.

UPDATE:
I've since run the BCP from the command line using quotes around each parameter - it apparently attempts to execute but returns yet another error: Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column

Searching on that error, one post suggested running the SP with SET FMTONLY ON to see the metadata. The output variable (@xml nvarchar(max)) is returned as NULL and the Return value from the SP is zero. I suspect this may have something to do with the content of the output variable being an XML document.

ANOTHER UPDATE:
Apparently this is a known issue with BCP:
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/29/bcp-command-calling-stored-procedure-with-dynamic-sql-and-not-having-the-first-statement-as-a-select-fails-with-the-following-error-error-microsoft-sql-native-client-bcp-host-files-must-contain-at-least-one-column.aspx

Wish I could say problem solved. Guess I go back to SQLCLR!

Thanks and Happy Friday,

Bob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources