Solved

Getting Function sequence error in BCP executing T-SQL block

Posted on 2007-11-15
10
3,394 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

615 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