This is just a short post on an interesting issue I ran across recently.
In an older application we are supporting, I have a stored procedure which gathers data, and one of the places it gathers data from is a view. Now nothing had changed in this view, or the procedure. All had been going along nicely. But then suddenly, I was getting an SQL Exception in the app which utilizes this database – cannot use the SUM operator on SmallDateTime.
What the heck? I never asked you to sum up SmallDateTimes! That would be silly! So I dig in to the stored procedure, and I try doing a quick ALTER on it to see what happens, and sure enough, once again, now SQL Server Management Studio is admonishing me – you cannot use SUM on SmallDateTime. I KNOW THIS ALREADY.
So I dig further down into the actual tuple it is complaining about, and it is in a view. The tuple is clearly of type SmallMoney. So I do an ALTER on the view, just to refresh it, then an alter on the procedure, and TADA, all is good again.
So then I went looking for a solution should this ever arise again anywhere in this db, and I came across this great script:
Code Snippet
- SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
- FROM sys.objects AS so
- WHERE so.type = 'V'
This will effectively select a script you can run which will refresh / recompile each of the views in a given database. Neat huh?
Print | posted on Friday, 20 August 2010 9:33 AM