Metadata Services: exist

Showing posts with label exist. Show all posts

Friday, March 30, 2012

no locks exist

We have an application running on SQL 7 which we just made changes to. Since
then we've been getting intermittant error message saying "no locks exist for
the table: cddegree. Unable to reset the lock status.." This happens about
25% of the time when we try to enter information into a table that has
nothing to do with cddegree. Any ideas would be greatly appreciate.
Thanks.
PatPat
What kind of changes did you do?
"PatW" <PatW@.discussions.microsoft.com> wrote in message
news:6766EC59-5E07-4741-870D-BA116E1E31B0@.microsoft.com...
> We have an application running on SQL 7 which we just made changes to.
Since
> then we've been getting intermittant error message saying "no locks exist
for
> the table: cddegree. Unable to reset the lock status.." This happens
about
> 25% of the time when we try to enter information into a table that has
> nothing to do with cddegree. Any ideas would be greatly appreciate.
> Thanks.
> Pat|||Is this a SQL Error message or an application message?
--
Andrew J. Kelly SQL MVP
"PatW" <PatW@.discussions.microsoft.com> wrote in message
news:6766EC59-5E07-4741-870D-BA116E1E31B0@.microsoft.com...
> We have an application running on SQL 7 which we just made changes to.
> Since
> then we've been getting intermittant error message saying "no locks exist
> for
> the table: cddegree. Unable to reset the lock status.." This happens
> about
> 25% of the time when we try to enter information into a table that has
> nothing to do with cddegree. Any ideas would be greatly appreciate.
> Thanks.
> Pat|||Most of the changes are in the front end app, which is in Visual FoxPro. In
SQL, we added some fields to two test score tables and modified some stored
procedures to update one of those tables with information from the other
table. Neither table is dependent on the table referred to in the error
message and vice versa.
"Uri Dimant" wrote:
> Pat
> What kind of changes did you do?
>
> "PatW" <PatW@.discussions.microsoft.com> wrote in message
> news:6766EC59-5E07-4741-870D-BA116E1E31B0@.microsoft.com...
> > We have an application running on SQL 7 which we just made changes to.
> Since
> > then we've been getting intermittant error message saying "no locks exist
> for
> > the table: cddegree. Unable to reset the lock status.." This happens
> about
> > 25% of the time when we try to enter information into a table that has
> > nothing to do with cddegree. Any ideas would be greatly appreciate.
> >
> > Thanks.
> >
> > Pat
>
>|||I can't tell - it's in the SQL Server log, but I haven't been able to find
anything like it on the SQL web site.
"Andrew J. Kelly" wrote:
> Is this a SQL Error message or an application message?
> --
> Andrew J. Kelly SQL MVP
>
> "PatW" <PatW@.discussions.microsoft.com> wrote in message
> news:6766EC59-5E07-4741-870D-BA116E1E31B0@.microsoft.com...
> > We have an application running on SQL 7 which we just made changes to.
> > Since
> > then we've been getting intermittant error message saying "no locks exist
> > for
> > the table: cddegree. Unable to reset the lock status.." This happens
> > about
> > 25% of the time when we try to enter information into a table that has
> > nothing to do with cddegree. Any ideas would be greatly appreciate.
> >
> > Thanks.
> >
> > Pat
>
>|||I haven't seen that one before. This google search brings up a number of
potentials and it is probably worth a look:
site:support.microsoft.com Unable to reset the lock status
Andrew J. Kelly SQL MVP
"PatW" <PatW@.discussions.microsoft.com> wrote in message
news:339B7DF0-30F3-498B-9C94-A5625F18DD84@.microsoft.com...
>I can't tell - it's in the SQL Server log, but I haven't been able to find
> anything like it on the SQL web site.
> "Andrew J. Kelly" wrote:
>> Is this a SQL Error message or an application message?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "PatW" <PatW@.discussions.microsoft.com> wrote in message
>> news:6766EC59-5E07-4741-870D-BA116E1E31B0@.microsoft.com...
>> > We have an application running on SQL 7 which we just made changes to.
>> > Since
>> > then we've been getting intermittant error message saying "no locks
>> > exist
>> > for
>> > the table: cddegree. Unable to reset the lock status.." This happens
>> > about
>> > 25% of the time when we try to enter information into a table that has
>> > nothing to do with cddegree. Any ideas would be greatly appreciate.
>> >
>> > Thanks.
>> >
>> > Pat
>>

Monday, March 26, 2012

No duplicates exist but get Msg 2627, Cannot insert duplicate key in object

On SQL Server 2000, a transaction of thousands of INSERTs contains these three consecutive INSERT statements:

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', 0, 1)

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05e51bee-ac3b-4257-90f1-f34adcb185cb', 0, 128)

INSERT INTO PARAMETER_ATTRIBUTES (ID, MIN_VALUE, MAX_VALUE)
VALUES ('05f33ded-979b-41ee-9d6c-ac5adc4d8635', -1000000000, 1000000000)

The transaction fails with a Msg 2627 error, Violation of PRIMARY KEY constraint 'ARR_Constraint2'. Cannot insert duplicate key in object 'PARAMETER_ATTRIBUTES'. SQL Profiler reveals that the last statement executed is the second INSERT.

The primary key is on the ID column. What is particularly significant is that before running the transaction, this query returns no rows:

select * from PARAMETER_ATTRIBUTES
where id in (
'05b3b88a-f0f2-4e7e-a82f-73a7bd177a96', -- pk value in first insert
'05e51bee-ac3b-4257-90f1-f34adcb185cb', -- pk value in second insert
'05f33ded-979b-41ee-9d6c-ac5adc4d8635') -- pk value in third insert

In other words, all three INSERTs contain primary key values that do not exist in the table. As you can see, each INSERT is inserting a unique value for the primary key. I am asserting that there are no duplicates. How can the fact of a Msg 2627 error be reconciled with the facts of no duplicates? Can SQL Profiler not be trusted to show the specific statement causing a duplicate key problem? In other words, in a transaction of 1000s of statements, does a dup key problem cause SQL Profiler to stop before it displays the statement causing the error?

Is there, by any chance, a trigger involved?

It does not look, on the surface, that you can both get no results from the select and the duplicate error. Therefore, we need to question the assumptions. Make sure that you can make the select return results from a known existant string. You might put the select after each of the selects.

What datatype are you using for the id?

That is how I would approach the debugging.

|||

No triggers exist.

The id column, the primary key column, is a uniqueidentifier.

|||Can you run the insert with just those three statements? One of the other batch inserts earlier in the order could contain an id with the same value. What you really need to do is run the select... where id in.... right before the insert, then just don't insert any rows after the insert, and see if there were rows existing the moment before statement 2 is run.|||The id value only exists in that one INSERT statement. I suspect that the last INSERT shown in SQL Profiler is not the INSERT causing the problem.|||

Further testing reveals that the the duplicate key INSERT occurs 100s of lines before the last statement shown in SQL Profiler.

Here is a detailed explanation of what is happening:

1. A very popular and well regarded database update utility is being used to update 100s of very similar databases.

2. This vendor's product puts all of the 10,000s of statements in a single transaction.

3. Within the single transaction are many batches of statements.

4. Within a particular batch, there is a statement that causes a duplicate key error. Not on the QA database, not on the many real databases, just on one developer's database.

5. The last statement in the batch does not cause a duplicate key error. It was the statement I was looking at because it was the last one the SQL Profiler displayed. I also looked at the immediately preceding and following (first statement of next batch) INSERT statements. None of these statements could or did cause a duplicate key error.

What we can conclude from this is that depending on how statements are grouped and processed, the last statement displayed by SQL Profiler in an abort situation may not physically be anywhere near the statement actually causing the problem.

No Default SQL Instance on Install?

Problem: When the application installs it requires SQL Express Server 2005. If it does not exist it will install it or rollback. -> continue installation -> When the form attempts to locate an SQL Instance on a machine that has not had SQL server or any components connected before it shows up as <null>. I need to have access to the server(s) instances in order to build the application database inside SQL. Am I using the wrong approach with SQLDMO? Is there adifferent method to access the installed SQL Express 2005 connection string?
Current code to locate SQL Instances:
try
{
////////////////////////////////////////
// Search and List SQL Server Instances
////////////////////////////////////////
//set cursor to thinking/hourglass
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.AppStarting;
//begin
ArrayList aServers = new ArrayList();
SQLDMO.ApplicationClass acServers = new SQLDMO.ApplicationClass();
//loop through the servers found
for (int iServerGroupCount = 1; iServerGroupCount <= acServers.ServerGroups.Count; iServerGroupCount++)
{
for (int iServerCount = 1; iServerCount <= acServers.ServerGroups.Item(iServerGroupCount).RegisteredServers.Count; iServerCount++)
{
aServers.Add(acServers.ServerGroups.Item(iServerGroupCount).RegisteredServers.Item(iServerCount).Name);
}
}
//add the servers to the drop down box
if (aServers.Count < 1)
{
string NotExist = "<No Servers Found>";
cboServers.Items.Add(NotExist);

cboServers.SelectedIndex = 0;
btnSQLDataBaseCheckDB.Enabled = false;

}
else
{
cboServers.Items.AddRange(aServers.ToArray());
//select the first item in the array index to display
cboServers.SelectedIndex = 0;
}
}
catch (Exception ServerException)
{
MessageBox.Show(ServerException.Message);
}

Slightly stuck at the moment with this. Any tips or leads in the right direction would be greatly appreciated.
sorry to double post. Found a relative link in the forums which may add some more insight to the problem, though certainly not solve it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=385579&SiteID=1&mode=1

Friday, March 30, 2012

no locks exist

no locks exist

no locks exist

Monday, March 26, 2012

No duplicates exist but get Msg 2627, Cannot insert duplicate key in object

No Default SQL Instance on Install?

Metadata Services

Blog Archive

About Me