Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting Function sequence error in BCP executing T-SQL block

Posted on 2007-11-15
10
Medium Priority
?
3,456 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 1500 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

704 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