Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts

Wednesday, March 21, 2012

many BULK INSERTs and error "cannot obtain a lock"...

Hi,
I still have problem with my script that perform a sequence of
many
data files with a BULK INSERT in a loop and rarely (about 10% of
times) fails with "cannot obtain a lock" (see the original post
below).
Since this happens also on a dedicate server without any other
activity
(both sql server and the O.S. are idle apart for the BULK INSERT
loop)
I start thinking the cause could be in the ODBC connection and cursor
that are
constantly re-used during the whole loop (the odbc connection is
in autocommit) and so
A friend of mine suggested me that I could try to enforce a checkpoint
could a ' cursor.execute("CHECKPOINT") ' statement issued before
any BULK INSERT
solve my issue?
TIA!
bye,
PiErre
on 2007-02-16 PiErre wrote:
> Subject: suggestion to avoid error "cannot obtain a lock"
> Hi,
> I have a script (actually the same python script
> that runs locally on several ms sql 2000sp4
> server - connecting via mxodbc)
> that every night tries to bulk insert about 20 files
> of various size (from less than 10 MB
> to more than 300 MB - about 900 MB in total )
> and sometimes (about 5% of the times)
> the importation fails (randomly on
> one or more of the servers) with the error like
> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
> The SQL Server cannot obtain a LOCK resource at this time...
>
> There is no other activity on the db server at the error time and
> so I cannot detect what is the cause of the resource lock.
> The db is normally configured with recovery-model set to simple
> and autoshrink enabled (not my decision
> and I must provide clear evidence to have
> that "standard" changed), but even after
> disable it the errors still appear.
>
> What can I do to detect the actual lock cause?
> More important: what can I do to avoid
> such errors? I heard something
> about to set a checkpoint after every bulk insert
> but I don't know how to do that...
>
> Thanks in advance for your help!
> bye,
> PiErreTry specifying TABLOCK on the BULK INSERT statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PiErre" <siggy2@.supereva.it> wrote in message
news:1178184835.364450.30740@.y80g2000hsf.googlegroups.com...
> Hi,
> I still have problem with my script that perform a sequence of
> many
> data files with a BULK INSERT in a loop and rarely (about 10% of
> times) fails with "cannot obtain a lock" (see the original post
> below).
> Since this happens also on a dedicate server without any other
> activity
> (both sql server and the O.S. are idle apart for the BULK INSERT
> loop)
> I start thinking the cause could be in the ODBC connection and cursor
> that are
> constantly re-used during the whole loop (the odbc connection is
> in autocommit) and so
> A friend of mine suggested me that I could try to enforce a checkpoint
> could a ' cursor.execute("CHECKPOINT") ' statement issued before
> any BULK INSERT
> solve my issue?
> TIA!
> bye,
> PiErre
> on 2007-02-16 PiErre wrote:
>> Subject: suggestion to avoid error "cannot obtain a lock"
>> Hi,
>> I have a script (actually the same python script
>> that runs locally on several ms sql 2000sp4
>> server - connecting via mxodbc)
>> that every night tries to bulk insert about 20 files
>> of various size (from less than 10 MB
>> to more than 300 MB - about 900 MB in total )
>> and sometimes (about 5% of the times)
>> the importation fails (randomly on
>> one or more of the servers) with the error like
>> 'S1000', 1204, '[Microsoft][ODBC SQL Server Driver][SQL Server]
>> The SQL Server cannot obtain a LOCK resource at this time...
>> There is no other activity on the db server at the error time and
>> so I cannot detect what is the cause of the resource lock.
>> The db is normally configured with recovery-model set to simple
>> and autoshrink enabled (not my decision
>> and I must provide clear evidence to have
>> that "standard" changed), but even after
>> disable it the errors still appear.
>> What can I do to detect the actual lock cause?
>> More important: what can I do to avoid
>> such errors? I heard something
>> about to set a checkpoint after every bulk insert
>> but I don't know how to do that...
>> Thanks in advance for your help!
>> bye,
>> PiErre
>|||On 3 Mag, 12:08, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> Try specifying TABLOCK on the BULK INSERT statement.
>
There are good chance that your hint was EXACTLY what I was looking
for...
Thanks a lot!
bye,
PiErre

Monday, March 12, 2012

Manual creation of aspnet database fails "does not allow remote connections"

What do I have to do to get this to work?

C:\>aspnet_regsql.exe -A m -E

Start adding the following features:
Membership

............
An error has occurred. Details of the exception:
An error has occurred while establishing a connection to the server. When conne
cting to SQL Server 2005, this failure may be caused by the fact that under the
default settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Unable to connect to SQL Server database.

Hi,

If you're connecting to a remote machine, you will need to turn on remote connection on that server. Please follow this KB article to achieve this.

http://support.microsoft.com/kb/914277/en-us

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||SQL Server is set to allow remote connections already. I probably should have mentioned that in the first post.|||

Hi,

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Is just a basic connectivity error meaning the client could not connect to the target SQL Server. So just follow the basic connectivity troubleshooting guidelines on our SQL Protocols blog, see:

SQL Server 2005 Connectivity Issue Troubleshoot - Part I

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

and

SQL Server 2005 Connectivity Issue Troubleshoot - Part II

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

This should help you debug the problem.