BCP - I don't know what is wrong here please help

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 2005 Forums

Transact-SQL (2005)

BCP - I don't know what is wrong here please help

Author

Topic

tanu
Yak Posting Veteran

57 Posts

Posted - 2009-07-09 : 17:26:17

declare @date varchar(20)
set @date =(select rtrim(convert(varchar(6),getdate(),106)))

declare @sql varchar(100)
set @sql = 'Select sar from lu_camswork.dbo.FINEDEDailyImport where FinAidYear =''SEM0910'''
select @sql
declare @bcpcommand varchar(200)
set @bcpcommand = 'bcp "'+''+ @sql+'"'
--select @bcpcommand
set @bcpcommand = @bcpcommand + ' queryout
"\\RO003-RB\C$\CAMS\FinancialAid\ISIR\ExportFile\SEM0910\'+@date+'.txt" -c -Ssmart -T'
select @bcpcommand
set @bcpcommand = ''+@bcpcommand+''
select @bcpcommand
exec xp_cmdshell @bcpcommand

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 17:28:28

What is the result of last "select @bcpcommand"?

N 56°04'39.26"
E 12°55'05.63"

tanu
Yak Posting Veteran

57 Posts

Posted - 2009-07-09 : 17:36:20

bcp "Select sar from lu_camswork.dbo.FINEDEDailyImport where FinAidYear ='SEM0910'" queryout "\\RO003-RB\C$\CAMS\FinancialAid\ISIR\ExportFile\SEM0910\09 Jul.txt" -c -Ssmart -T

I want to create 5 different textfile and I want to use
exec xp_cmdshell @bcpcommand for this purpose. If I run it in command window, it works fine.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 17:42:59

Do you get an error message?
If so, which?

Something to do with Error 5?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 17:46:46

The reason I ask, is that when you copy the string result and paste it in a cmd window, you run the code in your account permissions.

When using cmd_shell, you are executing the query with permissions given by the account for which sql server is run.

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-09 : 18:35:37

The service account doesn't have permissions to the drive?

Dump it to C:\test.dat

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tanu
Yak Posting Veteran

57 Posts

Posted - 2009-07-09 : 19:06:20

When I run exec xp_cmdshell @bcpcommand
I get this in my result.
But when I look at the BCP Command in QRY Window, It looks like I have some syntex error.

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

tanu
Yak Posting Veteran

57 Posts

Posted - 2009-07-09 : 22:59:28

I wanted to say that when I ran

exec xp_cmdshell 'bcp "Select sar from lu_camswork.dbo.FINEDEDailyImport where FinAidYear =''SEM0910''" queryout "\\RO003-RB\C$\CAMS\FinancialAid\ISIR\ExportFile\SEM0910\09 Jul.txt" -c -Ssmart -T'

It works fine but I want to pass file name as the variable. I want to save five different files in five different folders depending on different year.
I don't want to hardcode the file name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 23:26:56

try don't CR after queryout


set @bcpcommand = @bcpcommand + ' queryout "\\RO003-RB\C$\CAMS\FinancialAid\ISIR\ExportFile\SEM0910\'+@date+'.txt" -c -Ssmart -T'

KH
[spoiler]Time is always against us[/spoiler]

tanu
Yak Posting Veteran

57 Posts

Posted - 2009-07-17 : 13:11:12

Thanks. That was it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-17 : 13:33:50

Oh, I thought that was the forum linebreaking the message...

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources