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 spset @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'MadhivananFailing to plan is Planning to fail |
|
|
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'MadhivananFailing to plan is Planning to fail
--------------------keeping it simple... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-10 : 05:46:34
|
See if this worksUse 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 quotesexec master..xp_cmdshell @sql MadhivananFailing to plan is Planning to fail |
|
|
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 worksUse 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 quotesexec master..xp_cmdshell @sql MadhivananFailing to plan is Planning to fail
--------------------keeping it simple... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-10 : 06:04:33
|
Replace exec master..xp_cmdshell @sqlbyexec ('master..xp_cmdshell '+@sql)MadhivananFailing to plan is Planning to fail |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-08-10 : 06:07:43
|
uhmm.. wouldn't this be the same?incorrect syntaxi'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 @sqlbyexec ('master..xp_cmdshell '+@sql)MadhivananFailing to plan is Planning to fail
--------------------keeping it simple... |
|
|
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 thatWhat 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'MadhivananFailing to plan is Planning to fail |
|
|
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 thatWhat 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'MadhivananFailing to plan is Planning to fail
--------------------keeping it simple... |
|
|
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?MadhivananFailing to plan is Planning to fail |
|
|
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 pubsset nocount onexec master..sp_addlogin 'tg', 'pw'goexec pubs..sp_grantdbaccess 'tg'gocreate proc dbo.junksp @p1 varchar(10), @p2 varchar(10) as select 'tg' as [name]gogrant exec on dbo.junksp to tggodeclare @sql varchar(200)set @sql = 'bcp "exec pubs..junksp ''tg'', ''tg''" queryout d:\junksp.txt -c -Srdevsql1 -Utg -Ppw'exec master..xp_cmdshell @sqlexec master..xp_cmdshell 'dir "d:\junksp.txt"'exec master..xp_cmdshell 'type "d:\junksp.txt"'goexec master..xp_cmdshell 'del "d:\junksp.txt"'godrop proc dbo.junkspexec pubs..sp_dropuser 'tg'goexec 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 OptimizerTG |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-08-10 : 21:01:53
|
thanks TGactually 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... |
|
|
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 fileanyone encountered this before?--------------------keeping it simple... |
|
|
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 errorThats why I told to check the string returned by @sql >>anyone encountered this before?No. I also run this type of job without any problemSeems that you create a new Excel file and export data to itIf 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=49926MadhivananFailing to plan is Planning to fail |
|
|
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 filethen 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 awaynext, i tried calling it from a job, but then it only creates the file and no datathen i tried calling the bcp in the job itself, the bcp calls the sp to generate the dataand 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... |
|
|
|