December 28, 2015 at 1:28 pm
I am using @cmd_bcp to generate my BCP command, depending on the parameters passed into the export program. But I am getting a truncate error and the statement is too long.. is there any way around this?
The identifier that starts with 'select COL_001, COL_002, COL_003, COL_004, COL_005, COL_006, COL_007, COL_008, COL_009, COL_010, COL_011, COL_012, COL_013, COL_' is too long. Maximum length is 128.
December 28, 2015 at 1:35 pm
What do you mean by "using @cmd_bcp to generate my BCP command"? Are you storing the command in a variable? Are you using the correct data type for the variable? Why not use a view or a stored procedure?
December 28, 2015 at 1:53 pm
I am using varchar(8000) so that shouldn't be limiting it to 128.
December 28, 2015 at 2:09 pm
Can you post what you're doing?
This doesn't give an error.
DECLARE @cmd_bcp varchar(8000)
SET @cmd_bcp = 'bcp "SELECT BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate FROM AdventureWorks2012.Person.Person WHERE FirstName=''Jarrod'' AND LastName=''Rana'' " queryout "C:\Users\public\Documents\US12058Jarrod Rana.dat" -T -c -S MyServer\MyInstance'
EXEC xp_cmdshell @cmd_bcp
December 28, 2015 at 4:38 pm
dwilliscp (12/28/2015)
I am using @cmd_bcp to generate my BCP command, depending on the parameters passed into the export program. But I am getting a truncate error and the statement is too long.. is there any way around this?The identifier that starts with 'select COL_001, COL_002, COL_003, COL_004, COL_005, COL_006, COL_007, COL_008, COL_009, COL_010, COL_011, COL_012, COL_013, COL_' is too long. Maximum length is 128.
Something else is wrong. Please post your entire BCP command.
Also, there's no way that I'd embed such a query in a BCP command. Create a stored procedure and use BCP to call the stored procedure using a trusted connection in BCP.
--Jeff Moden
December 29, 2015 at 2:49 pm
I would love to post the code.. but it is my vendor's code... sorry.
December 29, 2015 at 3:01 pm
dwilliscp (12/29/2015)
I would love to post the code.. but it is my vendor's code... sorry.
I will remove their proc call from our code and just do the export in a non-dynamic way (theirs you pass in a table and a few parameters that it uses to create a new temp table... I do not need all the options they are using so I only need to get the order of the columns right and the where stmt correct.)
So once I have done that tomorrow.. will see if I still get the 128 char error or not.
December 29, 2015 at 7:02 pm
dwilliscp (12/29/2015)
I would love to post the code.. but it is my vendor's code... sorry.
Actually, that explains a lot.
So who's code is it that's in your first post on this thread?
--Jeff Moden
December 30, 2015 at 7:58 am
Jeff Moden (12/29/2015)
dwilliscp (12/29/2015)
I would love to post the code.. but it is my vendor's code... sorry.Actually, that explains a lot.
So who's code is it that's in your first post on this thread?
I have a proc, that I wrote, that calls their proc. It passes down the table name and several parameters used to generate the sql statement used in the BCP statement that they create. So first I am just going to hard code a BCP statement into my code and comment out the call to their proc that writes the data to file using a dynamic BCP statement.
December 30, 2015 at 8:01 am
dwilliscp (12/30/2015)
Jeff Moden (12/29/2015)
dwilliscp (12/29/2015)
I would love to post the code.. but it is my vendor's code... sorry.Actually, that explains a lot.
So who's code is it that's in your first post on this thread?
I have a proc, that I wrote, that calls their proc. It passes down the table name and several parameters used to generate the sql statement used in the BCP statement that they create. So first I am just going to hard code a BCP statement into my code and comment out the call to their proc that writes the data to file using a dynamic BCP statement.
BTW the error "The identifier that starts with 'select COL_001, COL_002, COL_003, COL_004, COL_005, COL_006, COL_007, COL_008, COL_009, COL_010, COL_011, COL_012, COL_013, COL_' is too long. Maximum length is 128." is generated when I run my proc, but in fact is coming from their proc.. when my code reaches that exec stmt.
BTW their code is using varchar(max) for what is being passed into their program and varchar(8000) for everything else. So nothing is using varchar(128).. to match the error that I am getting about the SQL stmt inside the bcp being longer than 128 char...
December 30, 2015 at 8:05 am
BTW their code is using varchar(max) for what is being passed into their program and varchar(8000) for everything else. So nothing is using varchar(128).. to match the error that I am getting about the SQL stmt inside the bcp being longer than 128 char...
December 30, 2015 at 8:10 am
dwilliscp (12/30/2015)
Jeff Moden (12/29/2015)
dwilliscp (12/29/2015)
I would love to post the code.. but it is my vendor's code... sorry.Actually, that explains a lot.
So who's code is it that's in your first post on this thread?
I have a proc, that I wrote, that calls their proc. It passes down the table name and several parameters used to generate the sql statement used in the BCP statement that they create. So first I am just going to hard code a BCP statement into my code and comment out the call to their proc that writes the data to file using a dynamic BCP statement.
The big hint here is the word "identifier". Obviously, their code works but your call to their code does not. If you're using and EXEC or EXECUTE statement on a variable, remember that your EXEC statement must contain the variable in parenthesis. For example... you code must look like...
EXEC (@SQL);
... and not ...
EXEC @SQL;
... the later of which will give you the oversized identifier error.
--Jeff Moden
December 30, 2015 at 8:43 am
The big hint here is the word "identifier". Obviously, their code works but your call to their code does not. If you're using and EXEC or EXECUTE statement on a variable, remember that your EXEC statement must contain the variable in parenthesis. For example... you code must look like...
EXEC (@SQL);
... and not ...
EXEC @SQL;
... the later of which will give you the oversized identifier error.[/quote]
Hmm so here is the call...
EXEC[their_proc] @tbl_nam = @tbl_nam, @fil_nam = @fil_nam
so this should look
EXEC ([their_proc] @tbl_nam = @tbl_nam, @fil_nam = @fil_nam) ?
December 30, 2015 at 8:58 am
dwilliscp (12/30/2015)
The big hint here is the word "identifier". Obviously, their code works but your call to their code does not. If you're using and EXEC or EXECUTE statement on a variable, remember that your EXEC statement must contain the variable in parenthesis. For example... you code must look like...EXEC (@SQL);
... and not ...
EXEC @SQL;
... the later of which will give you the oversized identifier error.
Hmm so here is the call...
EXEC[their_proc] @tbl_nam = @tbl_nam, @fil_nam = @fil_nam
so this should look
EXEC ([their_proc] @tbl_nam = @tbl_nam, @fil_nam = @fil_nam) ?[/quote]
when I put it into a set of brackets.. like above.. got the following error
Msg 102, Level 15, State 1, Line 219
Incorrect syntax near 'their_proc'.
December 30, 2015 at 9:17 am
Never mind, earlier post below, a co-worker had opened my file... after I ran in cmd window.
However I do know why our vendor was using a temp table and added a new column on the far left.. BCP does not seem to export out the column headers, is there any way to get BCP to do this?
-------------------------------------------------------------------------
I tried to hard code the export, and found out why they were using the temp table... you do not get the column headers as the first row...
It worked fine, when just doing a select *, from a command prompt, but when I try and run the full SQL inside of SSMS or command line, it tells me that it is unable to open BCP host file... what does that mean?
exec master..xp_cmdshell 'bcp "SELECT [Day_dt],[Business],[Business_Region],[Region],[Material_Group],[Plant_TX],[Material_TX],[Plant-Material_TX],[Plant_CD],[Material_CD],[Plant-Material_CD],[Material_Group_OR_MatPla],[Stock_Status_SAP],[Batch_Age_Stocking_Status],[Tot$BatOpp],[Category],[Material_Type],[Unrestricted_QTY],[In_Transfer_Qty],[Quality_Inspection_qty],[Blocked_Qty],[Restricted_qty],[Cust_Consgn_Qty],[<30 Days],[>30<60 Days],[>60<90 Days],[>90 Days],[Total_Valuated_Stock],[Total_Value_USD],[120dUSD],[120dKG],[USDchg],[KGchg],[Obsolete_Qty] as Obsolete,[Slow_QTY] as Slow_Moving,[Imperfect_QTY] as Imperfect,[KPI_Count],[Total_Usable_Inventory],[Total_Usable_Inventory_$],[DOI],[Last_Comment_DT],[Last_Comment_By],[Last_Comment] FROM[RADAC.net].dbo.ztb_kpis_hist ORDER BY [Tot$BatOpp] DESC" queryout "\\R2D2\excel_exports\david.xls" -c -T -S"R2D2"'
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply