| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2010-07-07 : 13:14:18
|
| When I run this,exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out C:\Test8b.txt -T -c -t "|"'I get a query result like this. (I am using localhost):outputSQLState = S1000, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-fileNULLDuane |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-07 : 19:25:45
|
| Thank you for your response, but I put the -S back in there and still get this.exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out "C:\Test8m.txt" -T -c -t "|" -S "localhost"'I did find out however, if I put a path in such as the following, it runs:exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out "C:\Test\Test8m.txt" -T -c -t "|" -S "localhost"'If I leave the C:\ out, it also runs and the file ends up in C:\Windows\System32.exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out "Test8m.txt" -T -c -t "|" -S "localhost"'It does not work with My Documents either:exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out "C:\Documents and Settings\X649390\My Documents\20100707\Test8h.txt" -T -c -t "|"'And it doesn't even work with just Documents and Settings:exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out "C:\Documents and Settings\Test8h.txt" -T -c -t "|"'Neither an external network share:exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out "\\nccicmshr001\desktop services\DTS\Software Reporting\MSDN\Test8f.txt" -T -c -t "|"'All these I have access to with a trusted connection, and they all work if I run BCP from the command prompt. But not in xp_cmdshell from SSMS. And they don't work either if I put them in a SQL Server Agent job. I get the same errors.Duane |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-07-07 : 19:29:55
|
| SQL Server Agent job and xp_cmdshell from SSMS run as who, you or sa (are you sure)? use sql profiler to see what is happening.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-07 : 20:35:53
|
| Thank you for these last 2 comments. I am going to check them out tomorrow. As far as I know, I am not logging in as SA - I just log into my workstation with Windows. I installed the SQL Server myself as a fairly standard install. I don't knnow ...Duane |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-08 : 18:15:17
|
| I used the profiler and nothing seemed to be out of the ordinary, but I don't really know how to use it and don't know what I am looking for. It did log in under my trusted domain/user account but then seemed to use the NT AUTHORITY\NETWORK SERVICE for everything in between (a few steps).Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-09 : 11:42:50
|
| I am not sure totally what you are talking about. In the configuration manager, I clicked on or selected SQL Server Services under the main node SQL Configuration Manager (local) and there were several "names" on the right side. One of them was SQL Server (SQLEXPRESS) and the other was SQL Server (MSSQLSERVER) and they both had a Service type of SQL Server and they both logged on as NT AUTHORITY\NetworkService. Hopefully this will shed some light on all this. Thanks again for helping.Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-09 : 12:09:40
|
| I have the dialog box open and it has 2 radio buttons. "This account" is checked. It has NT AUTHORITY etc. The other button, unchecked is Local System account. Should I check that one? And then you mentioned to provide permissions to that account to any network paths, etc. How do I do that?Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-09 : 12:47:13
|
| I think I am over my head in this one. Right now I log into a Windows domain at work from my machine. This gives me access to the network and several SQL Servers to which I have created linked servers from my Localhost SQL Server. (That is another issue altogether) These linked servers already work as long as I am running from my localhost SQL Server. I also have full admin permissions on my machine. My localhost version (SQLEXPRESS), I installed myself and as said, it has been working already, except for this problem in my OP. I would appreciate it if you could either explain this process or lead me to some tutorial to do it. What I want to make sure is that I don't mess up my localhost SQLExpress AND that I don't mess up the client/server access that I have with the network servers. Right now I can go into SSMS and connect to any of them including localhost and the others. I must be careful here because I don't know what I am doing with these accounts. So, with all that, can you explain first of all, if what I do will compromise what I already have and secondly, explain the steps:1 ) Create a generic account on the Windows side, like sqlser or sqlservice. Is this just like creating a new user account on the local machine? You mentioned a "domain" account. I am not sure I know what that is.2 ) How do you add it to the database server and grant it local admin?3 ) How do you grant it any permissions it needs to network resources and granting the permission?Sorry, I don't understand better, but it will greatly help me if this process can be explained.Duane |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-09 : 12:56:54
|
| 1) A local account will not have access to nccicmshr001 server, which you've shown you wanted to do in the bcp command. To get a domain account created, you'd have to request it from a domain administrator. If you don't care about nccicmshr001, then we could try a local account instead. And yes it would be an account on the local machine. 2) Add the account to the administrators group on the local machine. The task is not done inside SQL Server, but rather at the Windows layer.3) You'd have to work with a server administrator on that one, but that's only if you get a domain account created. This will not affect your access to the network or any other servers. xp_cmdshell runs under the context of the SQL Server service account and not your account. When you run bcp from a command prompt, it instead uses your account. So this is why you are experiencing issues with one way and not the other.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
xp_cmdshell error using bcp