April 2, 2012 at 8:36 am
So i'm converting a feed file process from dot net to sql based. I've got a select that needs to be pushed to a file on a different box. I've mapped a drive to go to the files location. When i print the bcp command and run it on the sql box in the command prompt all works well. However when i run it in sql i get a "unable to open host file error". Ideas?
error i get :
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
SQL:
declare @bcpsql varchar(2000)
, @BCPFileName varchar(100)
set @BCPFileName = 'Z:\ld.txt'
set @bcpsql = 'SELECT 1'--[Class],[Manufacturer],[Model],[Year],[StockType],[Price],[EngineModel],[FuelType],[Length],[Mileage],[Stock#],[TextDescription],[Site],[ImageURLDelimited],[ImageURLLabelDelimited] FROM [LazydaysDotComDB].[dbo].[EXPORT_RVUSA] order by id'
SET @BCPSQL = 'bcp "' + @BCPSQL + '" queryout ' + @BCPFileName + ' -c -T'
print @BCPSQL
declare @rc varchar(100)
exec @rc = master..xp_cmdshell @BCPSQL
April 2, 2012 at 8:49 am
looking at an old post i see where someone posted the below
=============================
Run
exec master..xp_fixeddrives
What is the result?
==============================
when i do this the mapped drive i created doesnt show.
April 2, 2012 at 8:55 am
does the sql account have permissions to the shared folder?
also try to stay away from mapping drives if you can, use UNC paths instead, if you cant get around mapped drives, mapp the drive in SQL using xp_cmdshell and NET USE commands
April 2, 2012 at 8:58 am
does the sql account have permissions to the shared folder?
Do you mean the sql account i'm logged in as when running the bcp? Or the account the sql service is running under?
April 2, 2012 at 9:01 am
the account which SQL is running as
April 2, 2012 at 9:42 am
thats it. the sql service on this specific box is running off the local system instead of domain account therefore it can acces the share. It doesnt surprise me. This group is scheduling .net executables to write a views data to a file on a different box. Their writting them as services in some instances and scheduled tasks in others. It's driving me crazy because the proper failure allerting isnt in place not to mention there is no moitoring in place as well. i want to convert this process to a sql job and schedule it w/ proper monitor/alerting.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply