Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

BCP works from command line, but fails from when I call it from SQL master..xp_cmdshell...

Avatar of ThoughtProcess
ThoughtProcessFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments1 Solution2117 ViewsLast Modified:
Disclaimer:  I am not a "database guy"... Our team's Excel spreadsheets are getting out of hand, and I'm just trying to help us evolve to a more sophisticated way of tracking data.

I have a plain-vanilla SQL Server 2008 Express installation.  I've created a new instance and set up some tables.  I'm trying to run a set of SELECT queries on the table, and generate some text files from those queries.  My goal (for now) is to have some kind of SQL script (or maybe stored procedure?) that works like the following pseudo code:

for (each team in the Teams table)
   {
   SELECT item FROM itemsTable
   WHERE owningTeam = [current team]
   print results to [current team].txt
   }

As a C++ programmer, my knee-jerk reaction would be to write a program to connect to the database, and then use ODBC to query the database and then write output files by looping through the result sets.  But before I resort to that, I want to try and understand if there's a "better" way to do what I need without writing an external program.

I did some background reading, and I chose to use the BCP command-line tool to generate these output files.

First, I enabled xp_cmdshell using the following instructions:
http://technet.microsoft.com/en-us/library/ms190693.aspx

Then, I tried the following query, which works from the command prompt:
bcp "SELECT TeamName FROM TestDB.dbo.Teams" queryout c:\test.txt -c -S MySystemName\SQLEXPRESS -T

This works great from the command prompt.  I get a text file with a list of items, just like how I wanted.

When I try this from within SQL Server, I've typed the following:

master..xp_cmdshell 'bcp "SELECT TeamName FROM TestDB.dbo.Teams" queryout c:\test.txt -c -S MySystemName\SQLEXPRESS -T'

But this results in an error message:

SQLState = 08004, NativeError = 916
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]The server principal "MyDomain\MySystemName$" is not able to access the database "TestDB" under the current security context.
NULL

Any ideas why this isn't working?

Also, given the pseudo code that I provided, can I evolve my bcp call to accommodate my end goal?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmadaFlag of Canada image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers