Author |
Topic |
mikenason
Starting Member
13 Posts |
Posted - 2006-06-26 : 12:25:32
|
I am having a problem with OS2003 and SQL2005. I am attempting to do a Bulk Insert from my desktop using SQl Server Mangement Studio connecting to a SQL2005 database server that is looking to a mapped drive on a OS2000 file server.truncate table OST_DataComm_Log_Data_Loadbulk insert OST_DataComm_Log_Data_Load from '\\myserver\r-drive\DataComm\Logs\Site2Site.Log'with(datafiletype = 'char',rowterminator = '\n',TABLOCK)I get the following message:Msg 4861, Level 16, State 1, Line 2Cannot bulk load because the file "\\myserver\r-drive\DataComm\Logs\Site2Site.Log" could not be opened. Operating system error code 5(Access is denied.).I can login to the Database server directly and the code runs no problem so I know taht my login has permissions, the SQL Service account has permissions and the Database server can see the file server. I can also browse to the folder/file.HELP! |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-27 : 05:33:13
|
The error sounds like it is windows preventing access to the file.>> I can login to the Database server directly and the code runs no problemIf you are using the same account to do that as you are from your desktop then there should be no difference as far as sql server is concerned.I would check the login under both circumstances.The only other difference is a remote versus local login - that shouldn't matter as long as remote logins are allowed.try a xp_cmdshell to see if you can access the file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-06-27 : 17:11:11
|
Thanks NRstill no joy...I can use Remote Desktop to connect to the SQL Server from my desktop using my network login and the query runs correctly...The xp_cmdshell DOES work..exec master.dbo.xp_cmdshell 'dir \\hornet\r-drive\DataComm\Logs\*.Log'returns a directory listing |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-28 : 03:22:33
|
Yes. You can connect to the network directory with your account. Is this your domain account or the local SQL Server account you are connecting with?Has the account for which SQL Server is runnning with, have necessary permission to do this?Peter LarssonHelsingborg, Sweden |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-06-28 : 09:47:28
|
I am using a network account that has permissions...the SQL Service account is a network admin account. I can login to my desktop with either account and the script does not run. If I log into the SQL server with either account the script does run. MORE on this issue...If I connect from my desktop using the SQL login of 'sa' the script works. |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-07-20 : 10:05:55
|
Does anybody have any more thoughts on this? I am almost to the point of having to open an issue with MS God forbid! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-21 : 05:49:40
|
Try select system_userselect user_name()when you log in from your desktop and on the server and see if there are any differences.The only other thing I can think of is the protocol but that shouldn't have this effect.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-07-21 : 10:19:26
|
In both cases; when I connect from my desktop and when I remote into the server and then launch Enterprise Manager, theselect system_user -> returns my network loginselect user_name() -> returns 'dbo' |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-24 : 04:53:38
|
being my old dense self...if you logon to the server using the sql server account, open query analyzer and run the script there, any luck?--------------------keeping it simple... |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-07-24 : 09:59:11
|
Jen,Yes, if I remote into the SQL server from my desktop or login directly on the SQL server everything works. Here is something strange tho..If I run Enterprise Manager from my desktop and login as 'sa', the query works...THAT I do not understand. |
|
|
dwumpus
Starting Member
1 Post |
Posted - 2006-07-24 : 13:23:24
|
For those interested, when your Bulk Insert task fails in SSIS and gives you an error like:Cannot bulk load because the file \\myserver\r-drive\myspecialdatafile.txt could not be opened. Operating system error code 5(Access is denied.). you might be inclined to believe you have a permissions problem. WRONG! you really have a problem with your format file. There is a column size out of whack or a row terminator in the wrong format. I suggest trying it through query analyzer manually first, work out the bugs in the files and then attempting your bulk insert task. I hope this saves someone else a day of headaches. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-24 : 23:07:11
|
if dwumpus suggestion didn't resolve your problem:try to open the mapped drive from your computer using the account you're running the bcp (windows authenticated?), create a file there(do not remotely access), this bulk insert --> is it in a job?--------------------keeping it simple... |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-07-26 : 09:38:50
|
Jen,I am not using a format file so that is not the issue. I am running the script in the Enterprise Manager query window. I can open and create files in the folder on the file server no problem. With the exception of the sa account working from my desktop, it almost seems like my credentials are not being passed from the desktop to the SQL Server to the File server. |
|
|
jagt
Starting Member
1 Post |
Posted - 2006-07-27 : 07:44:33
|
I'm having the same problems in SQL 2005, but if I do login with the user 'sa' the issue resolved.that's incredible.All my services are login with administrators accounts:Search Text SQL Server - domain\userSQL (administrators)SQL Server - domain\userSQL (administrators)Agent SQL Server - domain\administrator(administrators)Explorer SQL Server - domain\administrator(administrators) what is the difference of login with 'sa' or login with a 'administrator'?what is the system account that use 'sa'?thanks |
|
|
mikenason
Starting Member
13 Posts |
Posted - 2006-07-27 : 11:53:47
|
jagtKnowing that you are having the same issue at least confirms, maybe only suggests, that I am not crazy. I have NO idea why the 'sa' account works from my desktop but my other admin accounts do not.I hate to admit that after much fruitless searhing on the internet I am no closer to solving this issue. |
|
|
|