Solved

Getting Function sequence error in BCP executing T-SQL block

Posted on 2007-11-15
10
3,289 Views
Last Modified: 2012-05-05
The following block work fine in studio

select int_custodial_agent_nbr into #tempCA from [IND-2].dbo.custodial_agents where int_custodial_agent_id = 14 DECLARE @table varchar(20), @str_sql varchar(200) select @table = (select 'TEMP_BAM_' + cast(int_custodial_agent_nbr as varchar(20)) from #tempCA) select @str_sql = 'select * from ' + @table exec(@str_sql)

But when trying to send it to BCP with xp_cmdshell recive Function sequence error.

declare @cmdstring varchar(4000),
      @bcpstring varchar(4000)

set @cmdstring = 'select int_custodial_agent_nbr into #tempCA from [IND-2].dbo.custodial_agents where int_custodial_agent_id = 14 DECLARE @table varchar(20), @str_sql varchar(200) select @table = (select ''TEMP_BAM_'' + cast(int_custodial_agent_nbr as varchar(20)) from #tempCA) select @str_sql = ''select * from '' + @table exec(@str_sql)'

set @bcpstring = 'bcp "' + @cmdstring + '" queryout d:\test\data\bcpTest2.txt -T -c'  

print @bcpstring
exec xp_cmdshell @bcpstring

Not sure if using a temp table is the problem.

Any help would be appreciated.

0
Comment
Question by:FusionApps
10 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20293437
I had something similar a whileback ---i think i made declare @cmdstring varchar(8000) and for some reason that worked......

declare @cmdstring varchar(8000),
      @bcpstring varchar(8000)

apologies if totally wrong....
0
 

Author Comment

by:FusionApps
ID: 20293562
Tried the solution and got same error
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20293623
can you post a error code? full error message?
0
 

Author Comment

by:FusionApps
ID: 20293882
Here is the output with error.

bcp "select int_custodial_agent_nbr into #tempCA from [IND-2].dbo.custodial_agents where int_custodial_agent_id = 14 DECLARE @table varchar(20), @str_sql varchar(200) select @table = (select 'TEMP_BAM_' + cast(int_custodial_agent_nbr as varchar(20)) from #tempCA) select @str_sql = 'select * from ' + @table exec(@str_sql)" queryout d:\test\data\bcpTest2.txt -T -c
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Function sequence error
NULL
0
 

Author Comment

by:FusionApps
ID: 20294092
Tried not using a temp table and get a different error.

bcp "declare @tempdata varchar(1000) select @tempdata = cast(int_custodial_agent_nbr as varchar(20)) from [IND-2].dbo.custodial_agents where int_custodial_agent_id = 14 DECLARE @table varchar(20), @str_sql varchar(200) select @table = (select 'TEMP_BAM_' + @tempdata)  select @str_sql = ('select str_data from ' + @table) exec(@str_sql)" queryout d:\test\data\bcpTest2.txt -T -c
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
NULL
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:FusionApps
ID: 20294093
Here is the SQL.

declare @cmdstring varchar(4000),
      @bcpstring varchar(4000)


set @cmdstring = 'declare @tempdata varchar(1000) select @tempdata = cast(int_custodial_agent_nbr as varchar(20)) from [IND-2].dbo.custodial_agents where int_custodial_agent_id = 14 DECLARE @table varchar(20), @str_sql varchar(200) select @table = (select ''TEMP_BAM_'' + @tempdata)  select @str_sql = (''select str_data from '' + @table) exec(@str_sql)'

set @bcpstring = 'bcp "' + @cmdstring + '" queryout d:\test\data\bcpTest2.txt -T -c'  

print @bcpstring
exec xp_cmdshell @bcpstring

0
 

Author Comment

by:FusionApps
ID: 20294111
I have test data in a table and when I run the sql it returns data.

declare @tempdata varchar(1000) select @tempdata = cast(int_custodial_agent_nbr as varchar(20)) from [IND-2].dbo.custodial_agents where int_custodial_agent_id = 14 DECLARE @table varchar(20), @str_sql varchar(200) select @table = (select 'TEMP_BAM_' + @tempdata)  select @str_sql = ('select str_data from ' + @table) exec(@str_sql)
0
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 20296687
Dos command line is limited to 255 characters.

bcp command is getting truncated

You need to put those statements into a stored procedure and make BCP execute the stored procedure
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20296746
Dos command line is limited to 255 characters - couldn't you have the script in a file that the dos calls?
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20297641
Hi,

This error would occur on the querires that does not return any columns

Just check:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=535939&SiteID=1

I feel kselvia's suggestion is the best . create a stored procedure & 
run bcp command as 'bcp "exec myproc" queryout "outputfile" -S server -U user -P pwd '
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use odbc in vb to connect to ms sql 14 38
sql server query from excel 3 57
TSQL - IF ELSE? 3 29
Getting same value for every field in SQL 2 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now