May 25, 2012 at 9:32 am
Hello -
this is my first time using the xp_cmdshell bcp query.
I'm trying to use a where clause in the bcp command and I'm getting an error.
THE T-SQL CODE
DECLARE @FileName varchar(100)
DECLARE @bcpCommand varchar(2000)
DECLARE @assetNumber varchar(10)
SET @assetNumber = 'A4336'
SET @FileName = REPLACE('D:\batchTest_'+convert(char(8),getdate(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "select * from pfo21.dbo.device where pfo21.dbo.device.assetnumber = '
SET @bcpCommand = @bcpCommand + @assetNumber
SET @bcpCommand = @bcpCommand + ' " queryout '
SET @bcpCommand = @bcpCommand + @FileName + ' -T -c -t,'
EXEC master..xp_cmdshell @bcpCommand
The above produces @bcpCommand = to the following:
bcp "select * from pfo21.dbo.device where pfo21.dbo.device.assetnumber = A4336 " queryout D:\batchTest_05-25-12.txt -T -c -t,
I think the problem with this is that the A4336 should be 'A4336'... The query seems to think that A4336 is a column name...
I can get the script to work without any conditions or usage of the where clause and so I'm wondering there is someway to get this to work with conditional clauses.. .
The error that is received is as follows: Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'A4336'.
Thank you in advance for any help you can provide.
May 25, 2012 at 9:50 am
This is the line you need to change:
SET @bcpCommand = @bcpCommand + '''' + @assetNumber + ''''
John
May 25, 2012 at 9:54 am
Thank you John... That was easy and it works..
One more question... Is it possible to export to a shared drive using the mapped drive location?
Possibly an ftproot location
May 25, 2012 at 9:57 am
You mean a mapped drive - N:\MyFolder, where N: is mapped to \\MyServer\MyShare? Better to use a UNC, since the code will be executed in the context of the account that runs SQL Server, not (necessarily) the account that mapped the drive.
John
May 25, 2012 at 10:05 am
Thank you again...
I used the UNC and still received an error:
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
Is their permissions that need to be available? If so, would know what I should be asking for with our network people?
May 28, 2012 at 1:33 am
The account that runs SQL Server needs read permission on the folder that contains the file you're importing from.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply