BCP problem running from EM and Query analyzer

I am running on W2K server with Sql Server 7. I am having a lot of
problems getting some of my stuff to run. I suspect it has to do with
permissions.
But in this case I am trying to run the following command from EM as a
Job as well as directly from Query Analyzer.
BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
I get the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
I copied and pasted it into a cmd window and it worked fine.
What is the problem here? It is getting very frustrating.
Thanks,
TomHi,
You cant execute directly a BCP command from Query analyzer. As a job you
can execute it but the job type should be "operating system command".
Otherwise use xpcmdshell to execute the BCP from query analyzer
master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
sa -S DINO'
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC704D7.1030207@.deltanet.com...
> I am running on W2K server with Sql Server 7. I am having a lot of
> problems getting some of my stuff to run. I suspect it has to do with
> permissions.
> But in this case I am trying to run the following command from EM as a
> Job as well as directly from Query Analyzer.
> BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
> I get the following error.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> I copied and pasted it into a cmd window and it worked fine.
> What is the problem here? It is getting very frustrating.
> Thanks,
> Tom
>|||Hari wrote:
> Hi,
> You cant execute directly a BCP command from Query analyzer. As a job you
> can execute it but the job type should be "operating system command".
> Otherwise use xpcmdshell to execute the BCP from query analyzer
> master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
> sa -S DINO'
>
That was it.
I was having trouble getting it to work in EM, where I don't use the
xp_cmdshell SP and just copied it directly into the query analyzer.
All works fine now
Thanks,
Tom.
> Thanks
> Hari
> MCDBA
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC704D7.1030207@.deltanet.com...
>>I am running on W2K server with Sql Server 7. I am having a lot of
>>problems getting some of my stuff to run. I suspect it has to do with
>>permissions.
>>But in this case I am trying to run the following command from EM as a
>>Job as well as directly from Query Analyzer.
>>BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
>>I get the following error.
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near '.'.
>>I copied and pasted it into a cmd window and it worked fine.
>>What is the problem here? It is getting very frustrating.
>>Thanks,
>>Tom
>>
>

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
TIA,
ChrisR
Chris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
sql

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons
.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR

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