Solved

Getting Function sequence error in BCP executing T-SQL block

Posted on 2007-11-15
10
3,373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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