Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Function Sequence Error

Author  Topic 

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-09 : 21:24:00
I'm running this command for bcp and encountered this error. I'm actually exploring different ways of doing this. The idea is to query out the result of the sp


set @sql= 'bcp "exec db..spRPT ''' + @param1 + ''',''' + @param2 + ''','''
+ @param3 + '''" queryout c:\newfolder\' + @filename +'.xls -c -SServerName -Uuser -Ppassword'

exec master..xp_cmdshell @sql


I've searched the internet but the info I got is that something is being done before it should be done.

Any help appreciated...

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 05:10:24
Make sure @sql has this string

exec master..xp_cmdshell 'bcp "Exec DB..spRPT ''param1value'',''param2value'',''param3value''"
queryout "c:\newfolder\filename.xls" -c -server -user -password'




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-10 : 05:16:13
thanks maddy,

there's an extra double quote...

i tried enclosing the query in double quotes, plus the filename but still same error...

quote:
Originally posted by madhivanan

Make sure @sql has this string

exec master..xp_cmdshell 'bcp "Exec DB..spRPT ''param1value'',''param2value'',''param3value''"
queryout "c:\newfolder\filename.xls" -c -server -user -password'




Madhivanan

Failing to plan is Planning to fail



--------------------
keeping it simple...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 05:46:34
See if this works
Use Five single quotes (assuming that all are non-numeric data types)

set @sql= 'bcp "exec db..spRPT ''''' + @param1 + ''''',''''' + @param2 + ''''','''''+ @param3 + '''''"
queryout "c:\newfolder\' + @filename +'.xls" -c -SServerName -Uuser -Ppassword'

Select @sql --Make sure parameters are enclosed with two single quotes
exec master..xp_cmdshell @sql



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-10 : 05:50:14
incorrect syntax, my guess is too many single quotes?

i appreciate your help maddy, keep the ideas coming...

quote:
Originally posted by madhivanan

See if this works
Use Five single quotes (assuming that all are non-numeric data types)

set @sql= 'bcp "exec db..spRPT ''''' + @param1 + ''''',''''' + @param2 + ''''','''''+ @param3 + '''''"
queryout "c:\newfolder\' + @filename +'.xls" -c -SServerName -Uuser -Ppassword'

Select @sql --Make sure parameters are enclosed with two single quotes
exec master..xp_cmdshell @sql



Madhivanan

Failing to plan is Planning to fail



--------------------
keeping it simple...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 06:04:33
Replace exec master..xp_cmdshell @sql
by
exec ('master..xp_cmdshell '+@sql)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-10 : 06:07:43
uhmm.. wouldn't this be the same?

incorrect syntax

i'm not sure if I need to update a patch or something, or if this is even allowed (calling SPs in bcp)


quote:
Originally posted by madhivanan

Replace exec master..xp_cmdshell @sql
by
exec ('master..xp_cmdshell '+@sql)

Madhivanan

Failing to plan is Planning to fail



--------------------
keeping it simple...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 06:18:10
>>i'm not sure if I need to update a patch or something, or if this is even allowed (calling SPs in bcp)

I am not sure about that

What happens if you copy the result of @sql and run it in the Query Analyser?

exec master..xp_cmdshell 'bcp "Exec DB..spRPT ''param1value'',''param2value'',''param3value''"
queryout "c:\newfolder\filename.xls" -c -server -user -password'


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-10 : 06:23:49
that won't run coz bcp is dos command?


quote:
Originally posted by madhivanan

>>i'm not sure if I need to update a patch or something, or if this is even allowed (calling SPs in bcp)

I am not sure about that

What happens if you copy the result of @sql and run it in the Query Analyser?

exec master..xp_cmdshell 'bcp "Exec DB..spRPT ''param1value'',''param2value'',''param3value''"
queryout "c:\newfolder\filename.xls" -c -server -user -password'


Madhivanan

Failing to plan is Planning to fail



--------------------
keeping it simple...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 09:58:43
>>that won't run coz bcp is dos command?

Do you mean you try to run this as osql?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-10 : 13:45:37
I don't think the issue is the bcp syntax or the call from xm_cmdshell. I was able to perform a similar call (code below). I wonder if it could be something about the output of your sp. What does the output look like?


use pubs
set nocount on
exec master..sp_addlogin 'tg', 'pw'
go
exec pubs..sp_grantdbaccess 'tg'
go
create proc dbo.junksp @p1 varchar(10), @p2 varchar(10) as select 'tg' as [name]
go
grant exec on dbo.junksp to tg
go

declare @sql varchar(200)
set @sql = 'bcp "exec pubs..junksp ''tg'', ''tg''" queryout d:\junksp.txt -c -Srdevsql1 -Utg -Ppw'

exec master..xp_cmdshell @sql
exec master..xp_cmdshell 'dir "d:\junksp.txt"'
exec master..xp_cmdshell 'type "d:\junksp.txt"'
go
exec master..xp_cmdshell 'del "d:\junksp.txt"'
go
drop proc dbo.junksp
exec pubs..sp_dropuser 'tg'
go
exec master..sp_droplogin 'tg'


EDIT:
Are you calling this from an application? "Function Sequence Error" sounds like an odbc or some other sql provider thing. Or perhaps you're doing some funky bulk insert thing from within the called SP?

Be One with the Optimizer
TG
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-10 : 21:01:53
thanks TG

actually i'm exploring ways on how to do this, i've tried using the table itself, staging table and it works.

I'm now trying to use stored procedure to generate the file so that I won't have to create another table (temp tables don't work either).

But the error keeps popping up. I'm doing this directly in QA and the output is a simple select query (very straightforward).

What I can't understand is what function sequence is the error talking about?



--------------------
keeping it simple...
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-11 : 05:27:37
After banging my head on the wall next to my chair, i realized that there was an invalid character in my filename which caused the error.
I was transforming the filename into a pattern and that broke the code.

Anyways, I have a new problem:

if i run the code in QA it runs fine and returns n-number of records,
if i run it in a job, the file gets created but does not return any records

the job owner is sa, service account has enough privilege on the server to create the file

anyone encountered this before?



--------------------
keeping it simple...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-11 : 05:58:37
>>i realized that there was an invalid character in my filename which caused the error

Thats why I told to check the string returned by @sql

>>anyone encountered this before?

No. I also run this type of job without any problem

Seems that you create a new Excel file and export data to it
If you do it by bcp actually the Excel file will be created as "Tab Delimited Text"

Why dont you do this by OpenRowSet as I specified here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-08-11 : 21:49:23
actuall maddy, that was the original error, the filename error was unable to open bcp file

then i realized one of the datatype in the sp was out of range, so i fixed that and it's working and the function sequence error went away

next, i tried calling it from a job, but then it only creates the file and no data

then i tried calling the bcp in the job itself, the bcp calls the sp to generate the data

and I'm now happy and quit banging my head...

just so, I hope MS will add more options to BCP, like append data to an existing file (wish list)



--------------------
keeping it simple...
Go to Top of Page
   

- Advertisement -