Cannot fetch a row using a bookmark from OLE DB provider (Msg 7333, Level 16, State 2)

Linked Server Msg 7333

If you are getting this error and are trying to resolve it. Let me give you a little background information. A bookmark in this error is a left over from SQL Server 2005 SP1 and earlier. Bookmarks after 2005 SP1 is really a referred to as a key lookup, not to be confused with primary/foreign keys. From now on I am going to refer to bookmarks as key lookups so we are uniform and using the modern terminology.

Key lookups are objects within a query that refer to a data page (where the data is actually stored in the database) from a non-clustered index.

When SQL Server is executing DML (Insert, Update, Delete) against a linked server successfully for days, months, or even years and then fails with this error:

 Cannot fetch a row using a bookmark from OLE DB provider (Msg 7333, Level 16, State 2)

Something in the query execution plan is wrong. So far I have only run into this problem using linked servers and it probably has to do with resource management within SQL Server. Perhaps someone could elaborate on this that has more experience than I. But within the query’s execution plan, SQL Server decides to switch to a different index to run the query. The query then fails because it is using an index that is lacking information to complete the request.

Resolution

Ultimately, you have to do the following:

1. Look at your where criteria in your query that is dependent on the linked server.
2. Review indices on the table, that the where criteria is referring to.
3. Is your one of your criteria covered by more than one index?

If you answered yes to number 3, there is a good chance that the behavior I am describing is actually causing your problem.

To diagnose this, disable one of the indexes  that is in question.

NOTE: Never disable the clustered index, you will prevent data access to the table.

Run the query again, and it should work, if not rebuild the disabled index to bring it back online and then disable the other index. If the query works you are in business. Talk with your database administrator, developer, or vendor of the application to come up with a good solution. But this will temporarily get your query to work.

Advertisement

RSOD – Red Screen of Death! No, I’m not kidding.

So there is actually a red screen of death. Microsoft does have an RSOD, it is very rare to see these days. As a matter of fact, it was only visible in a beta version of Windows Vista. Seen here:

Windows Vista Red Screen of Death

There is another RSOD, HP has one too and I saw it first hand today:

HP Red Screen of Death

This error indicates a problem with your SAS Controller. A firmware upgrade from HP might be able to fix your problem.

Either error is not good, my sympathies.

Canceled By User ~ Debugging CLR Objects in SQL Server and Visual Studio

So you enabled CLR debugging on your TEST SQL Server (don’t ever do this on production!)

You are trying to debug but you get a message “Canceled by user” in the Output window in Visual Studio. Here is the answer, you have to enable the ports on BOTH your workstation and the SQL Server machine.

Add the following inbound exceptions to both machines:
TCP: 135
UDP: 400,500

On your WORKSTATION:
Add the program devenv.exe to your exceptions list in your firewall.

This will worked for me. Let me know if you had a different solution.

SQL Server ~ Creating a CLR User Defined Function Error 181

Cannot use the OUTPUT option in DECLARE or CREATE FUNCTION statement

So this may or may not trip you up. If you are like me, you want thing to run fast. One of those ways is to pass a reference to a variable as opposed to passing a copy of the value to the function. In .Net this works well, however SQL Server translates the byref to it’s equivalent which is OUTPUT option. Simply bite the bullet and change the function to have all of your parameters to byval and you are good to go.

Hopefully I saved you some time and agrivation.

VB.Net C# Designtime Error ~ ‘.ctor’ is not a valid identifier.

Tired of searching you project for that string? The answer is simple, which was proposed by Vladmir from Serbia on MSDN. The name of your class or form shares it’s name with a function or subroutine contained within that class. Like below:

public class Form1

private sub Form1()

…

end sub

end class

Changing the name of the method will fix this issue.

Error MSB3482: SignTool reported an error ‘Keyset does not exist’.

So I got this today: error MSB3482: SignTool reported an error ‘Keyset does not exist’. I was trying to publish a project in Visual Studio 2005. Here is the resolution:

Right click the ClickOnce key marked with the PFX extension in your projects folder, install it and try to publish again.

The cause apparently was that I changed the configuration of the compiling. Changing them back to the original state did not work because the damage was done.

Let me know if this helps!