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:
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.
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?