Need help with BCP syntax

[pandatime]

Hi,

I am having issues trying to get this to work. I can't really see what the problem with my syntax is:

declare @execMe varchar(255)
declare @fileDt varchar(20), @fileName varchar(40)

select @fileDt = convert(varchar, GetDate(), 120)
set @fileDt = substring(@fileDt,1,4)+substring(@fileDt,6,2)+substring(@fileDt,9,2)+substring(@fileDt,12,2)+substring(@fileDt,15,2)+substring(@fileDt,18,2)
set @fileName = 'myFile_' + @fileDt + '.txt'
print @fileName

set @execMe = 'bcp "SELECT * FROM myTable where name = ''Joe'' and id <= 10" queryout "G:\myDir\' + @fileName + '"' + ' -T -c -e "G:\myDir\error.txt"'
print @execMe

The error is:
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

(I have write perms in this directory)

Any ideas?

Thanks much!

 

[markros]

Worked fine for me when I used the local drive:

declare @execMe varchar(255)
declare @fileDt varchar(20), @fileName varchar(40)

select @fileDt = convert(varchar, GetDate(), 120)
set @fileDt = substring(@fileDt,1,4)+substring(@fileDt,6,2)+substring(@fileDt,9,2)+substring(@fileDt,12,2)+substring(@fileDt,15,2)+substring(@fileDt,18,2)
set @fileName = 'myFile_' + @fileDt + '.txt'
print @fileName

set @execMe = 'bcp "SELECT * FROM AdventureWorks.HumanResources.Department where name like ''I%''" queryout "c:\Tests\' + @fileName + '"' + ' -T -c -e "C:\Tests\error.txt"'
print @execMe

PluralSight Learning Library

 

[monkeylizard]

I could be wrong, but I thought that the BCP was executed using the login rights of whoever is logged on to the SQL Server. So you may have writes to the G: path, but if the same network path isn't mapped to G: on the server, it won't work.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[djj55]

monkeylizard has a point, I forget that every once in a while and wish I had hair to pull out.

URL's help even if the drive is mapped.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!