February 2012 - Posts

sp_refreshview
15 February 12 08:42 PM | MartinBell | with no comments

The other Friday we had a very successful go live of a new production box… apart from a couple of minor things. Some of the reports that were being produced ran significantly slower and the other was a SQL Agent Job on a third party server suddenly stopped working.

As the reports were not mission critical they got shunted to the end of the queue, so the SQL Agent job was top priority on Monday morning.

The SQL Agent package was failing after about 20 seconds with the error messages

Msg 65535, Sev 16, State 1: SQL Server Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. [SQLSTATE 42000]

Msg 7412, Sev 16, State 1, Line 14 : OLE DB provider "SQLNCLI10" for linked server "<Linked Server>" returned message "Login timeout expired". [SQLSTATE 01000]

Msg 7412, Sev 16, State 1, Line 14 : OLE DB provider "SQLNCLI10" for linked server "<Linked Server>" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000]

I tracked down the query that failed and although the linked server had been changed, I check that I could browse it in SSMS and do simple SELECT queries using the objects used in the query. I could also execute the INSERT…SELECT statement that failed in SSMS. If a problem is easily re-creatable it is quickly solved, so I created a test job and the SELECT queries still worked but the INSERT..SELECT statement failed when I included any view that referenced the linked server. This was turning out to be very annoying especially as the linked server used a specific login, I could not see why this should fail only in a SQL Agent job.

Bingoogling the error message turned up the usual suspects such remote logins not being enabled, firewall issues and protocols not being enabled. Despite being pretty sure these weren’t the cause I checked each of them out and dismissed each one. To quote Sherlock Holmes “When you have eliminated the impossible, whatever remains, however improbable, must be the truth?” What was left was that the error message is erroneous and the problem had nothing to do the linked server configuration. This go me thinking about the views, which in turn sparked the question “Is the meta data being incorrect?”. A quick test on one of the views proved me correct.
There is an example in Books Online that shows you how to update dependent objects within a given database, this is not going to work for linked servers or update all views in every database.

so I set about creating a script to update the relevant views in every database that used the linked server:

EXEC sp_msforeachdb 'USE [?];
DECLARE @viewname sysname ;
DECLARE @cmd nvarchar(4000) ;
DECLARE view_cursor CURSOR
   FOR SELECT name FROM sys.views WHERE OBJECT_DEFINITION(object_id) LIKE ''%<Linked Server>%'' ;
OPEN view_cursor
FETCH NEXT FROM view_cursor INTO @viewname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ''EXEC sp_refreshview '''''' + @viewname + ''''''''
SELECT DB_NAME(), @cmd ;
EXEC ( @cmd ) ;
FETCH NEXT FROM view_cursor INTO @viewname
END

CLOSE view_cursor
DEALLOCATE view_cursor'


Change the name of the linked server if you want to use this script. As this wasn’t production code use the shortcut of sp_msforeachdb.

This solution happened to kill two birds, the reports actually started to run quicker!!

Filed under:
Chris Date in Edinburgh
05 February 12 11:48 AM | MartinBell | with no comments

Chris date is returning to the UK on June 7th and 8th.

Details of the course titled "Normal Forms and All That Jazz: A Database Professional's Guide to the Theory of Database Design" can be found at http://www.justsql.co.uk/chris_date/cjd_edin_may_2012.htm

This will appeal to everyone from Architects, DBAs and Developers and is based on his latest book due to be published soon.

Having attended one of Chris’ courses in the past, I know what a great teacher he is.

To register email: rde@justsql.com

This Blog

SQL Blogs

Syndication