| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2002-10-08 : 17:59:16
|
| I literally think I'm dying. I swear this thing has caused a blood clot somehwere. Ok, here's what's happening. I have a table in SQL that I want to transform to xml(yes, I'm using sql2k and win2k). Runs great when I try to save the file locally, kills me trying to bcp it to the web server.The helpful descriptive message is:SQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-fileNULLNow, before you decide what the problem is, keep reading. I'm using trusted connection logged into the same domain as the web server. I'm an admin on the web server and on the sql box. I'm not running it as a job, I'm in QA on the sql box. So if it's a right issue, I have no damn idea. I'd prefer not to set up a virtual path between the web server and sql because I don't really want any overhead on sql, that's why I made the table xml in the first place(it's static). I certainly don't want to use DTS, because I hate DTS and it more like a Yugo than a practical data movement tool. Here's the code:xp_cmdshell 'bcp "select top 1 companyname from northwind.dbo.customers for xml auto, elements" queryout \\nxclmch1\d$\test1.xml -S"wjcpw20008" -T -c -r -t'If your hair falls out and there's no one in the office with you, does it make a sound?Please help me before they find my cold dead hands around my coffee mug.Mike"oh, that monkey is going to pay" |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-08 : 18:25:57
|
| curious... but if you do the BCP from a command prompt rather than xp_cmdshell do you get a simular error?-----------------------SQL isn't just a hobby, It's an addictionEdited by - M.E. on 10/08/2002 18:26:50 |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-10-08 : 18:30:34
|
| Ah ha! It goes right through. Ok, so what am I missing?Mike"oh, that monkey is going to pay" |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-08 : 18:34:15
|
| Hmm, could you just use Windows to schedual the bcp then? Its gotta be permissions somewhere-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-10-08 : 18:38:04
|
| Hmm. If you have any other permission thoughts, I'll try them. I'd rather not schedule anything. The file is based on a trigger on the table so if it changes, it creates a new xml file and in theory moves it over to the web server. A lot of these tables only change once a month, but if they do change, it needs to be available to users right away. This keeps our data server overhead down to because in my opinion, it's a waste to hammer the same static tables over and over again. Anyway, whatever you got, post it here!Mike"oh, that monkey is going to pay" |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-08 : 18:39:37
|
Hey now... BOL saysquote:
When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.
Hmm, what permissions does you Microsoft SQL server login have?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-10-08 : 18:56:41
|
| STUPID STUPID STUPID!!!In between postings I set up the SQLAgent account as an admin to the server, didn't work. Of course the rvolk called me(nice having an inside person) and the light over my head came on that it doesn't do me a lot of good unless I'm running the query from the server, not from my desktops development server, which is a totally different sqlagent login. So having conclusively proven I'm a dumb ass. Thank you M.E. for your help. I'll make one less Canada joke per day now.Mike"oh, that monkey is going to pay" |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-08 : 19:01:31
|
| Hmm, how many more times do you need to be helped before you stop making canadian jokes? heheheheAnd RobVolk... can I give you my number the next time I have a problem?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-08 : 19:05:52
|
 It depends on the problem. SQL related, that's OK. Anything else, I'd rather not know. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-10-08 : 20:33:37
|
| ..anything else, you don't really want his help!Mike"oh, that monkey is going to pay" |
|
|
|
|
BCP From SQL To Web Server