bcp error

I am getting an error that I can't open the host file when running bcp
through a tsql statement. When I set the path to a local drive on the sql
server the utility runs. However, when I set the path to a unc path that th
e
server and service account both have permission to then it won't run.
Any ideas on what I should try to fix this problem?Who is the owner of the job? If it is not sa then you will use the account
setup for the Proxy. See this: http://www.support.microsoft.com/?id=269074
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>I am getting an error that I can't open the host file when running bcp
> through a tsql statement. When I set the path to a local drive on the sql
> server the utility runs. However, when I set the path to a unc path that
> the
> server and service account both have permission to then it won't run.
> Any ideas on what I should try to fix this problem?|||I don't know if this helps answer your question, but it does not run as a
job. It is running through a stored procedure. DBO is the ower of the
stored procedure.
"Andrew J. Kelly" wrote:

> Who is the owner of the job? If it is not sa then you will use the accoun
t
> setup for the Proxy. See this: [url]http://www.support.microsoft.com/?id=269074[/ur
l]
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:213D9833-AFC6-4122-8BCC-DFEB1BAF945D@.microsoft.com...
>
>|||Whether it is a job or not still has the requirement of who is trying to
execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
that user is a member of the sa role it will use the account that SQL Agent
is running under. If not it will attempt to use the account that is set for
the Proxy account of SQL Agent. The proxy account most likely does not have
the right permissions. From BOL under xp_cmdshell:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...[vbcol=seagreen]
>I don't know if this helps answer your question, but it does not run as a
> job. It is running through a stored procedure. DBO is the ower of the
> stored procedure.
> "Andrew J. Kelly" wrote:
>|||I executed the following:
xp_sqlagent_proxy_account n'get'
It returned an account. I checked the account and it was a member of the
sysadmin role.
To me, it seems like it should be working.
"Andrew J. Kelly" wrote:

> Whether it is a job or not still has the requirement of who is trying to
> execute xp_cmdshell will determine what account gets used. IN SQL 2000 if
> that user is a member of the sa role it will use the account that SQL Agen
t
> is running under. If not it will attempt to use the account that is set f
or
> the Proxy account of SQL Agent. The proxy account most likely does not ha
ve
> the right permissions. From BOL under xp_cmdshell:
> When xp_cmdshell is invoked by a user who is a member of the sysadmin fixe
d
> server role, xp_cmdshell will be executed under the security context in
> which the SQL Server service is running. When the user is not a member of
> the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent prox
y
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail. This is true only for
> Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
> impersonation and xp_cmdshell is always executed under the security contex
t
> of the Windows 9.x user who started SQL Server.
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:6C2F8E62-E43B-49AD-8815-C37A56A2FA6E@.microsoft.com...
>
>|||That does not mean it has permissions to access the file share or even the
server. Is that account a domain account with permissions to that share?
The sa part has to do with who is running the command and not the proxy
account. Please read the entry in BOL for xp_cmdshell.
Andrew J. Kelly SQL MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...[vbcol=seagreen]
>I executed the following:
> xp_sqlagent_proxy_account n'get'
> It returned an account. I checked the account and it was a member of the
> sysadmin role.
> To me, it seems like it should be working.
>
> "Andrew J. Kelly" wrote:
>|||The account is a domain account and does have permissions to the share.
"Andrew J. Kelly" wrote:

> That does not mean it has permissions to access the file share or even the
> server. Is that account a domain account with permissions to that share?
> The sa part has to do with who is running the command and not the proxy
> account. Please read the entry in BOL for xp_cmdshell.
>
> --
> Andrew J. Kelly SQL MVP
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:E95DD8DB-D95A-49DA-8BE8-EC7149C01301@.microsoft.com...
>
>

BCP and Copy permissions

Hi,
I'm creating a number of CSV files in a stored procedure and I want to
copy them to a mapped network drive. I can create the CSV files
locally without any problem but I'm having trouble copying them to the
network drive. While I try to write to the network drive directly
using BCP I get the following error:
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host
data-file
Any when I try to use a batch file (which is trigger by the query) to
copy the files locally to the network I get the following error:
The system cannot find the drive specified.
If I run either through the command prompt they both work fine.
I've looked at the permissions on the destination folder and set that
to all full control for everyone and I've also changed the user
account SQL Agent run under without any success.
Any help on this would be much appreciated.
Thanks
SimonOn Oct 11, 12:35 pm, accyboy1981 <accyboy1...@.gmail.com> wrote:
> Hi,
> I'm creating a number of CSV files in a stored procedure and I want to
> copy them to a mapped network drive. I can create the CSV files
> locally without any problem but I'm having trouble copying them to the
> network drive. While I try to write to the network drive directly
> using BCP I get the following error:
> Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host
> data-file
> Any when I try to use a batch file (which is trigger by the query) to
> copy the files locally to the network I get the following error:
> The system cannot find the drive specified.
> If I run either through the command prompt they both work fine.
> I've looked at the permissions on the destination folder and set that
> to all full control for everyone and I've also changed the user
> account SQL Agent run under without any success.
> Any help on this would be much appreciated.
> Thanks
> Simon
I'm not positive about this, but aren't network drives mapped on a per-
user basis? Try using the UNC path instead of a mapped drive letter.
Sandy Barnabas

batch file to copy file and append date

The Sql Server database can only see the local drive.

I would like to set up a batch file that will copy a SQL Server

backup file from the local drive to the network drive. I would

like to append the file date to the end of the copied file. I

assume a batch file can accomplish this but I am new to batch

file writing. Does anyone have code that they already created

for this sort of task??

Thank you!You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?|||How do you plan to do this?

From the context of xp_cmdshell it would run under the sql server service account, which would need write to write to the network drive.

I've heard of people using robocopy...what do you want to use? ftp?

Just a plain copy command?

And from where a sproc, SQL Server agent Job?

I just want a plain copy within a batch file, but one that can append the file date (ie BackupFile_mm_dd_yyyy). Currently this copy has been set up within windows Task scheduler. I want to do the same but run the batch file in the task. Fyi: Because SQL Server cannot see the network, the task was set up with Windows tasks scheduler.

Let me know your thoughts..

Thank you.|||You can easily do this sort of stuff with a VBScript batch file. See the FileSystemObject. It's a great tool for moving and renaming files.

Thank you for your suggestion. I have not worked with VB Script so was hoping to accomplish this within a batch file. But thank you for your response. I will look into this as another option.|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP

Can you please explain the %1 and %~t1, will this append the file date?
Thanks!|||Go to the Windows XP help, and enter "Using batch parameters" (please include the quotation marks). It has a full explaination.

-PatP|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatPIf the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.|||I have a job which save databases structure into files. It create directory 'year-month-day' and dump structure there. This is code:
----------
DECLARE @.command varchar(1000);
-- create local directory for dump
SET @.command='mkdir C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--create dump
SET @.command='xp_cmdshell ''"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s TESTERS /d ? /P 1 /f C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+'\?.sql''';
exec sp_MSforeachdb
@.command1 = @.command,
@.replacechar = '?'

--create remote dir
SET @.command='mkdir \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;

--copy databases structure to remote computer
SET @.command='copy '+'C:\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar)+' \\saturn\DBStruct\'+CAST(YEAR(GETDATE()) as varchar)+'-'+CAST(MONTH(GETDATE()) as varchar)+'-'+CAST(DAY(GETDATE()) as varchar);
exec xp_cmdshell @.command;
----------
I hope this help you|||If the destination file doesn't exist your trick will not work. If it does exist the copy will result in "The syntax of the command is incorrect" error. The best place to generate the new filename would be in T-SQL.
Can you suggest how to append mmddyyyy to end of filename in a backup command within a SQL SERVER job?|||If you are using XP or Windows 2003, and N:\networkpath\ is where you want the files to go, then you might try a batch file like:copy %1 "n:\networkpath\%~1 %~t1"-PatP
The results is close but beacuse of the slashes in the date, the output is going into subdirectories not a single filename. Any suggestions?|||I figured out how to copy a file and append a date to the copy. Here is the code for the batch file:

:: COPY FILE AND DATE
::
@.ECHO OFF

FOR /F "tokens=2,3,4 delims=/ " %%a IN ('DATE /t') DO SET mydate=_%%a_%%b_%%c

::ECHO The value is "%mydate%"
copy "c:\readme" "L:\DATABASES\PROJECT REVIEW\readme%mydate%" /Y|||I am sure you did, and so did I, after reading this (http://www.computerhope.com/batch.htm#5)and similar pages ;)