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.

A data error occurred whe…

A data error occurred when calculating the preferred size of a cell. This value indicates that the DataGridView failed to calculate the preferred width or height of a cell when programmatically resizing a column or row. This can occur if the cell failed to format its value.

Alright so I think I now have a good handle on this now. If you are using auto sizing for your columns, you might run into a formatting exception. The error you get says that you can handle the error in the DataError event.
If you start error handling within that event you might notice that you get a context of 5 which says it is either a formatting or preferredsize data error context. To quote MSDN:

A data error occurred when calculating the preferred size of a cell. This value indicates that the DataGridView failed to calculate the preferred width or height of a cell when programmatically resizing a column or row. This can occur if the cell failed to format its value.

Powershell ~ Shorten Shortcut Names

Renaming Files

Here is something I just came up with. It is a really short Powershell one-liner to remove the annoying ‘- Shortcut’ from your shortcuts.

dir “*- Shortcut*” | foreach{ren -Path $_.Name -NewName $_.Name.Replace(” – Shortcut”,””)}

It is a good idea to execute it first with -whatif in the foreach loop.

Note: Be sure to be in the desktop directory when you run this.

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
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.

DataGridView, FormattingException, DataError, and PreferredSize ~ Auto-Sizing Issue

Alright so I think I now have a good handle on this now. If you are using auto sizing for your columns, you might run into a formatting exception. The error you get says that you can handle the error in the DataError event.

If you start error handling within that event you might notice that you get a context of 5 which says it is either a Formatting or PreferredSize data error context. To quote MSDN:

A data error occurred when calculating the preferred size of a cell. This value indicates that the DataGridView failed to calculate the preferred width or height of a cell when programmatically resizing a column or row. This can occur if the cell failed to format its value.

Cancel the event by issuing the following e.Cancel = True after checking to make sure that the DataGridViewDataErrorEventArgs (e) has the context of 5 for PreferredSize

SQL Server Reporting Services ~ MultiValued Parameters

This is a really good article on multivalued parameters in a stored procedure. Very good read for SQL Server Reporting Services.

Tech Updates

In SSRS reports we can have multi valued paremeters, which means we can select multiple values for that parameter and based on all those selected values result in the report is shown. We can write the SQL query to pass those parameter’s values and populate the data set, but sometimes it is required or to optimize the performance we need to use the stored procedure instead of direct sql query. This stored procedure will be taking the report paremeter as input and returing the result set to populate the data set.

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values…

View original post 398 more words

Visual Basic 2010 Object and List Initialization

I just found this out and I am really excited about it! The With & From are cooler than ever!

Check this out:

Module Module1
Private Class TestClass
Public Property Subject As String
Public Property Grade As String
Public Property Student As String
End Class
Sub Main()
Dim myTest As New TestClass With {.Grade = "A+", .Student = "Myself", .Subject = "Advanced Chromotography"}

Dim test As New System.Collections.Generic.List(Of TestClass) From {
myTest,
New TestClass With {.Subject = "Math", .Student = "John Bowman", .Grade = "B"}}

End Sub
End Module

The With and From are now part of the declaration statement, saving even more real estate on the screen.

Resolution: Error Syncing Windows Phone to Zune

So after several hours of trying to sync my Windows Phone to Zune, I have a couple of things for you to try. These have helped me and I hope they will help you.

Fix the Drivers

This will work well if Windows is not detecting the phone. The Microsoft Fix It can be found here: http://support.microsoft.com/mats/Windows_phone_7_and_Zune_diagnostic/

Forget the Phone

Well this is not me being sarcastic! In your Zune Settings > Phone > Sync Options > Forget this Phone. Once you do that unplug your phone and plug it back in. This seems to work exceptionally well.

Reinstall Zune

If you made it past that point, chances are something might be messed up in your Zune software. It could be that the information for the phone is corrupted within the Zune software, if this is the case try this Fix It: http://support.microsoft.com/kb/929358

Temporarily Turn Off Zune Launcher

This trick worked for me very well, I think what can happen is that the zunelauncher.exe doesn’t release the phone’s resources, thereby blocking Zune from syncing with your phone. Here is what to do:

  1. Disconnect your phone.
  2. Open Task Manager (Ctrl-Alt-Del and select Task Manager).
  3. In the processes tab, select zunelauncher.exe and click on End Task.
  4. Open Zune.
  5. Connect your phone.

If this solution works with you open msconfig and disable zunelauncher. This will prevent it from starting on startup of your computer. Please note: doing this will prevent Zune from launching when your phone is connected to your computer.

Reset to Factory

Use this as a last resort backup photos and other material you wish to keep to your Sky Drive, because you will lose all your data and applications. If you bought any apps, you are allowed to download them 5 times before you have to pay for them again, so only install them again if you have resolved all your issues. I don’t want to hear that you had to pay for your applications again.  Do this sparingly!

On your phone go to Settings > About > reset your phone.

Update 9/2/2012

I found one other issue that could cause syncing issues. If your computer has bad RAM or your motherboard doesn’t play nice with the RAM in your computer, you might have intermittent problems syncing. Recently upgraded my computer to 8GB and that caused Zune to have issues. Other symptoms included general slowness with the computer I was using.

Thanks for reading, let me know if this worked for you or if you found another solution.

SQL Server Error 823 Troubleshooting and Resolution

SQL Server Error 823

If you are getting this error, chances are you are having a hardware failure on your server, or perhaps someone deleted one of the database files.

Assuming you took care of that situation:

Do not Detach the Database

The very easy fix to do is to simply restore the database using your backups. But if you don’t have a good set of backups, Paul Randal ( he and his wife Kim, are SQL GODS) has a great set of steps to try in his article here.

Here is the short story of how to get your database up and running if your transaction log was damaged.

Error 823 Resolution

Switch the database into the emergency/single-user mode:

alter database <dbname> set emergency;
go
alter database <dbname> set single_user;
go
dbcc checkdb(‘<dbname>’,REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs;
go
alter database <dbname> set multi_user;
go
alter database <dbname> set online;
go

The main thing to realize here is that if the transaction log is damaged or missing that the transactions are not going to be found in the transaction log. You are going to lose any active transactions that have not been commited to the database. The real area of interest is the REPAIR_ALLOW_DATA_LOSS. This particular arguement allows you to recreate the transaction log. Do not take this command lightly as it will lose data, so please use it only as a last resort.

 

DBA Test/Development Server Best Practices

These are things I learned from a hardware failure of a test server (you might also call it a staging area). So what’s the big deal? Developers should have all their code checked into source control and nothing critical should be on there right?

Sadly no. These are things you should do on your test server to limit your exposure as a DBA.

1. Treat the server as a production server. This includes transaction log backups, backups of user accounts, SSIS, SSRS, SQL Agent jobs, and Windows Tasks.

2. Trust, but verify. Trust the developers to be following best practices, after all your job is to make the company money and so is theirs. Periodically issue correspondence with the developers to verify that nothing is running in a production manner.

3. Get a good snapshot/system image from time to time. If someone or something destroys your server, you have something to fall back upon. This is especially true when developers are creating lots of dependencies in their code to OS level libraries.