SQL Server 2005 Export Data

josgood
josgood used Ask the Experts™
on
I using SQL Server 2005 Express and Microsoft SQL Server Management Studio Express.
I have a database with several tables, which I'd like to export.  I'm switching from Vista to Windows 7.

To export a table, I'm using
exec master..xp_cmdShell 'bcp "SELECT * from MyDB.dbo.Symbols Order By Symbol"  queryout ''D:\Databases\Export\Symbols.txt'' -S Name\SQLEXPRESS -c -T'
(where Name is my computer's name)

I get "Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file".

Both SQLExpress and MSSQLSERVER are logged in as Local System.

I sure could use some help here!  I'm over my head.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
does local system have read/write on :

''D:\Databases\Export\"
Top Expert 2007

Author

Commented:
Local Service isn't displayed as user to whom I can assign permissions, so I
  * Added Network Service as a group with permissions on D:\Databases\Export
  * Ensured Network Service has Read/Write permissions (checked all except Special Permissions)
  * Ensured SQLEXPRESS logs on as Network Service
  * Ensured MSSQLSERVER logs on as Network Service

Still the same problem.
Top Expert 2007

Author

Commented:
I bet you're on the right track, though.  There are several other SQL-related services...perhaps one of them needs the same logon and permissions?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Pls post if the SQL Server services are running with domain/local system accounts on the source machine. If they are running with local system, then you need to change the account to a domain account(It should have full previleges at folder level on destination machine)

Hope this helps!
Try adding full read/write permission to the folder fo the user: everyone
if the function work after that we know it definatly is a persmissions issue with the folder.
Top Expert 2007

Author

Commented:
geek_vj:
This is my home machine, running in a workgroup.  SQL Server is on the same machine.

Service                                                                             Status/Startup Type   LogOn
SQL Server (MSSQLSERVER)                                    Started-Automatic     Network Service
SQL Server (SQLEXPRESS)                                       Started-Automatic     Network Service
SQL Server Active Directory Helper                            Disabled                    Network Service
SQL Server Agent (MSSQLSERVER)                         Manual                       Local System
SQL Server Analysis Services (MSSQLSERVER)   Started-Automatic     Local System
SQL Server Browser                                                     Started-Automatic     Network Service
SQL Server Full Text Search (MSSQLSERVER)      Automatic                   Local System
SQL Server Integration Services                                Started-Automatic     Network Service
SQL Server VSS Writer                                                 Started-Automatic     Local System
Top Expert 2007

Author

Commented:
zadeveloper:
This boot is Vista SP1 64-bit.  I'm happier with my other boot, Win 7, which is why I'm going through this.

For 'D:\Databases\Export, the users specified were
Authenticated Users   Everything except Full Control and Special Permissions
System                           Everything except Special Permissions
Network Service           Everything except Special Permissions
Administrators              Everything except Special Permissions
Users                              Everything except Special Permissions

I gave Authenticated Users Full Control, but that didn't help.

I gave Network Services Full Control on the next level up -- D:\Databases -- but that didn't help either.
Users has full control on that folder, as well.
Top Expert 2007

Author

Commented:
I have three hard drives here.  My D:\ drive is the data drive.  I connect one of the other two to be my C:\ drive.  (Once I'm through migrating, then the Vista drive goes on the shelf for emergencies).

So both systems see the same folders on D:.  The folders were originally written by Vista.

I have SQL Server 2005 on both boots.  I've imported the schema into the Win 7 boot's SQL Server 2005.  All I'm really trying to do is import the data...can you suggest an alternate to the approach I'm taking?

I'll be gone all week on business.

I hope you guys have a great week and I'll be in touch on Friday.

Cheers!
Commented:
>>SQL Server (SQLEXPRESS)     Started-Automatic     Network Service

Pls try changing this service account to 'Local System' instead of Network Service and give a try.
Top Expert 2007

Author

Commented:
Sorry about the delay -- trip was longer than expected.

I changed the SQLEXPRESS service to log in as the local system (had to enable named pipes also), but still no cigar.  Same problem.

Ah-hah!  Removed the doubled single-quotes around the path and then it worked!
exec master..xp_cmdShell 'bcp "SELECT * from MyDB.dbo.Symbols Order By Symbol"  queryout D:\Databases\Export\Symbols.txt -S MyMachineName\SQLEXPRESS -c -T'

The output file does not need to exist, it gets created when the data is exported.
Top Expert 2007

Author

Commented:
I feel that you both provided valuable tips that moved me towards the solution.  The problem was actually the doubled single-quotes (I think) so I can't judge whether any answer was more valuable that the others.  Each of you provided two answers, so I'm splitting the points down the middle.

I hope you agree that's fair.

I appreciate the help and the encouragement.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial