Help required for Operating system error code 1311

[VIJSQL01]

Hi,

When i am trying to load a SQL table by using BCP command, i am getting below error.

Msg 4861, Level 16, State 1, Line 1 
Cannot bulk load because the file "\\10.48.164.48\Datamove1\REAS_QA_DATA\REAS1407\HPI_Bulk_Export_by_STATE_201407.csv" could not be opened. Operating system error code 1311(There are currently no logon servers available to service the logon request.)

This is the first time i am facing this issue. Moreover this issue is intermittent. I donno what is causing this issue.
Please help me to fix this issue.

Thanks in advance.

 

[simian336]

Can you open the file in a text editor?

Simi

 

[VIJSQL01]

you mean to say, the file should be in .txt format, instead of .csv?
And another thing is that, this issue is intermittent. DOes this got something to do with allocated memory..not sure..please help.

 

[simian336]

The file should be text regardless of it's file extension.

I was asking if you can open the file in a text editor like notpad or notpadd++. This would indicate weather or not the file is still locked by anouther program or process and that you have network rights to open it.

Simi

 

[SQLBill]

It sounds like an issue with your Domain Controllers. It could be they were down or there is a trusted connection issue.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[VIJSQL01]

It seems like the issue was with the file locked by anouther program or process and that others also have network rights to open it.
Because, after sometime i didnt face this issue.

But curous to know something about "issue with Domain Controllers. It could be they were down or there is a trusted connection issue."
Honestly i do not have any idea what are all these domain controllers. Is there something we can do in order to trouble shoot issue with domain controllers? or how to make sure there is no trusted connection issue?
please let me know about all these, if possible.
Anyways thanks very much for all your reply. Very helpful.

 

[SQLBill]

The sysadmins at my company handle this stuff...but here is how I understand it.

When you set up a 'domain' you have to have a Domain Controller that controls access to the different computers on the domain. For example...let's say you work for company 'XYZ'. When J. Smith logs into the company's computers, he/she uses the login XYZ\JSmith. That lets JSmith connect to any computer he/she has access to on the XYZ domain. This also allows JSmith to have a Windows account on SQL Server. If you notice, when creating a Windows account on SQL Server, you don't provide a password. That is because Domain accounts are trusted accounts. When JSmith connects to SQL Server, his/her login/password combination is checked on the Domain Controller and if correct, is then allowed to connect to SQL Server. That is a 'trusted' connection. (SQL Server trusts the login/password because the Domain Controller approved it). If you are trying to login into another computer and the Domain Controller is 'swamped'/down/unreachable, you can't log into other computers that require a trusted connection.

Hopefully that is kinda clear...talk to your sysadmins or network admins to see who sets up the domain where you work and they can give you a better description.


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[VIJSQL01]

This is a great explaination. Thank you very much. i learnt something new today.
if i circle back to my issue which i was facing, i would like to give a brief background on how the SQL server is being used for my purpose.
1) Like you mentioned, YES! we have a domain and we work on SQL server via remote desktop. Because client data is very sensitive and we are not allowed work locally. Whenever we login to remote desktop, we make sure that we provide right domain name(ofcourse we have got access to only one domain, as there are so many other domain availables in the company)
2) After logging into the remote desktop, we connect to SQL server by using SQL authentication.

My questions out of curiocity...............
a) I am really interested to know that, even after following the procedure of logging into SQL server very correctly, how this issue might occur.e
b) Since this issue is intermittent for me, is there a way i can avoid this in future by changing few settings in the system.

Please let me know on this.

 

[simian336]

Well because the issue is intermittent I would guess it is not the domain controller/permissions. As permissons don't change on their own. It is probably caused by the file being locked. This could be a couple of things.

The process that creates the file might not be releasing it.
The file might take a long time to create.
The file might be recreated so often that it is frequently locked.
A backup program might be locking it.
An antivirus program might be locking it. (especially if it is changing often).

That is just a few reasons off the top of my head.

One way around it might be to copy the file locally then process it. Many locked files can be copied but you will get the last saved version without the latest changes.

Simi

 

[fredericofonseca]

the particular error you got is most of the times caused by one of the following

1 - IPV6 defined on network card (server one on this case)
2 - dns cache issue
3 - DC controller unavailable temporarily
4 - one of the DC controllers that was used by the server in question is no longer available


Now just an assumption, but when you said using bcp command, are you issuing this command through TSL (e.g. cmdshell), using the TSQL BCP command, or bcp through a command line on your own remote desktop?

If the first 2 then it is the SQL Server service user that will try and access the share, and will therefore need to validate its logon to the DC which would cause this issue - should it be the 3rd option then you should already be authenticated on the DC and issue is less likely to be a DC one, but a possible dsn/networkcard issue


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[VIJSQL01]

Thank you very much for meaningful information on the issue faced. In fact, its a lot more information than expected. Thanks a bunch.
I would like to take this discussion little forward to know more about how to deal during these type of situations.
per one of our friend's suggestion, i have saved the file in new location and tried to load the file. Yes! it worked. I have faced this issue sometime ago and this is the solution i got.
As my second friend is asking about whether i use BCP command through TSL, YES! i am trying to execute the BCP command via cmdshell and this the first time i have got this error message.
It seems like there is no foolproof solution for fixing this issue.I think its a more of a trial and error.

 

[fredericofonseca]

One way for you to see if it is file locking or something is is to first issue a cmdshell command to list that file - should this fail you will know its not file being locked.
and only after successful execution of the list you would try to issue the bcp command.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[VIJSQL01]

Thanks for the reply.
Could you please let me know, what should be the syntax of cmdshell command to list a particular file? and on which platform i can execute..etc etc
Sorry for asking more, as i am newbie to this.

 

[fredericofonseca]

http://www.sqlusa.com/bestpractices2008/list-files-in-directory/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886