[EN} BIDS Helper version 1.6.5 released

BIDSHelperSmallLogoTwo days ago (January 7th) new version of BIDS (Business Intelligence Development Studio) Helper has been released /and yesterday Jan. 8th files has been updated/. This fantastic feature is an add-in using for Business Intelligence projects with:

  • Visual Studio 2005 and SQL Server 2005
  • Visual Studio 2008 and SQL Server 2008 and SQL Server 2008 R2
  • Visual Studio 2010 and Visual Studio 2012 for SQL Server 2012

When You get file from CodePlex, unzipping, and installing, You will get access for many features:

  • Analysis Services Multidimensional:

Aggregation Manager Calculation Helpers Column Usage Reports Delete Unused Aggregations Delete Unused Indexes Deploy Aggregation Designs Deploy MDX Script Deploy Perspectives Dimension Data Type Discrepancy Check Dimension Health Check Dimension Optimization Report Duplicate Role Many-to-Many Matrix Compression Measure Group Health Check Non-Default Properties Report Parent-Child Dimension Naturalizer Printer Friendly Aggregations Printer Friendly Dimension Usage Roles Report Similar Aggregations Smart Diff Show Extra Properties Sync Descriptions Test Aggregation Performance Tri-State Perspectives Update Estimated Counts Validate Aggregations Visualize Attribute Lattice

  • Analysis Services Tabular:

 Roles Report Smart Diff Tabular Actions Editor Tabular Display Folders Tabular HideMemberIf Tabular Pre-Build Tabular Sync Descriptions Tabular Translations Editor Tabular Annotation Workaround Unused Columns Report 

  • Integration Services:

Batch Property Update Biml Package Generator Create Fixed Width Columns Deploy SSIS Packages Design Warnings dtsConfig File Formatter Expression and Configuration Highlighter Expression List Fix Relative Paths Non-Default Properties Report Pipeline Component Performance Breakdown Reset GUIDs Smart Diff Sort Project Files Sortable Package Properties Report SSIS Performance Visualization Variables Window Extensions

  • Reporting Services:

 Dataset Usage Reports Delete Dataset Cache Files Smart Diff

  • General:

Enable/Disable features Preferences Version Notification

From add-in’s download page You can choose from few versions:

  • BIDSHelper2012Setup(1.6.5.0).exe | application, 1743K, uploaded Tue – 141 downloads
  • BIDSHelper2012_1_6_5_0.zip | application, 2317K, uploaded Tue – 17 downloads
  • BIDSHelper2008Setup(1.6.5.0).exe | application, 1711K, uploaded Tue – 80 downloads
  • BIDSHelper2008_1_6_5_0.zip | application, 2277K, uploaded Tue – 16 downloads
  • BIDSHelper2005Setup(1.6.5.0).exe | application, 1711K, uploaded Tue – 16 downloads
  • BIDSHelper2005_1_6_5_0.zip | application, 2276K, uploaded Tue – 5 downloads

And when You get Your favorite… let’s play.

[EN] How to DO IT? My Maintenance SUB-Plans doesn’t work.

Last night I discovered on one of my customers systems, that Maintenance Plan doesn’t work. After few hours I received request form customer with printscreen:

image

Pict. 01 | Error with execution of Maintenance Plans.

When we look into message error we get more details:

image

Pict. 02 | Weekly Batch contains multiple plans…

But what is wrong with that? Customer’s idea was: to executing one batch (in this case weekly) with four subplans such: DBCC CHECKDB, REORGANIZE INDEXES, UPDATE STATISTICS and BACKUP (type FULL) for All Systems Databases. All together looks like this:

Captura_MaintenancePlans2005_08

Pict. 03 | One batch, four subplans and errors

The administrator creates exactly the same weekly batch for Users Databases, but it’s one small and important difference:

Captura_MaintenancePlans2005_09

Pict. 04 | Executing “exactly” the same batch for All User Databases.

 

The difference is very clear: they are four plans in first example and one in second. Let’s comparing differences:

image

Pict. 05 | I’ve got You! Right side: System Databases and Leftt side: User Databases.

As we see our maintenance plans are different, and they cannot work the same. Now we have two options: we can change first batch in SSIS or creat the new one (simple and fast option). But be sure that we will not repeat the design error:

image

Pict. 06 | We must remember about second option: Single schedule for ENTIRE PLAN or no schedule

At the end we have two Maintenance Plans, designed and scheduler very similar (only schedule od execution and types od databases are different):

image

Pict. 07 | Comparing the batches.

Think twice (what you need), design (how it shoud work), think one more (how it looks) time and execute (to check).

[EN] How to DO IT? FIX sp_timerequest Stored Procedure (and it’s FIXED now)

I like bubbles. Generally not too big, not too small. Better in less than two colors. Few weeks ago I discovered at one of my SQL Server, a little disquieting bubble on my free Idera SQL Server Monitor  – bigger than others and multicolored:

Capture_SQLSRV2005Wait_01

Yes, process No 78 is “a little bit different that others”. When I click on the red bubble {unfortunately is not 99 Red Baloon(s)} I get info:

image

As we see in last row: inputbuffer  is stored procedure: msdb.dbo.sp_readrequest. It will be good to look inside this procedure. This instance of SQL Server is compatible with 90 (DatabaseMail90) so it is SQL Server 2005. This stored procedure execute in specified interval task for checking queue data (for sending/receiving email information). We know that is the name of procedure – if not… anytime we can ask our server with the code:

   1:  SELECT Name 

   2:      FROM sys.procedures 

   3:      WHERE OBJECT_DEFINITION(object_id) 

   4:      LIKE '%part_of_sp_procedure_name%'

If we want (and in this case need) go inside this procedure, we can climb on the tree on this path: INSTANCE/ Databases/ System Databases/ msdb/ Programmability/ Stored Procedures/ System Stored Procedures/ dbo.sp_readrequest but… I don’t like it. Much better workaround I found at Pinal Dave {BLOG|TWITTER} post {HERE} with the fantastic function:

       sp_helptext 'dbo.sp_readrequest'

After executing – we can see full procedure code in very friendly view:

image

Of course some of You – the T-SQL coders – don’t like tables. If you want see this procedure – it’s simple too:

USE [msdb] GO /****** Object: StoredProcedure [dbo].[sp_readrequest] Script Date: 8.6.2012 12:39:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO — sp_readrequest : Reads a request from the the queue and returns its — contents. ALTER PROCEDURE [dbo].[sp_readrequest] @receive_timeout INT — the max time this read will wait for new message AS BEGIN SET NOCOUNT ON — Table to store message information. DECLARE @msgs TABLE ( [conversation_handle] uniqueidentifier, [service_contract_name] nvarchar(256), [message_type_name] nvarchar(256), [message_body] varbinary(max) ) — Declare variables to store row values fetched from the cursor DECLARE @exit INT, @idoc INT, @mailitem_id INT, @profile_id INT, @conversation_handle uniqueidentifier, @service_contract_name NVARCHAR(256), @message_type_name NVARCHAR(256), @xml_message_body VARCHAR(max), @timediff INT, @rec_timeout INT, @start_time DATETIME, @localmessage NVARCHAR(256), @rc INT –Init variables SELECT @start_time = GETDATE(), @timediff = 0, @exit = 0, @rc = 0 WHILE (@timediff <= @receive_timeout) BEGIN — Delete all messages from @msgs table DELETE FROM @msgs — Pick all message from queue SET @rec_timeout = @receive_timeout – @timediff WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout — Check if there was some error in reading from queue SET @rc = @@ERROR IF (@rc <> 0) BEGIN IF(@rc < 4) — make sure return code is not in reserved range (1-3) SET @rc = 4 –Note: we will get error no. 9617 if the service queue ‘ExternalMailQueue’ is currently disabled. BREAK END –If there is no message in the queue return 1 to indicate a timeout IF NOT EXISTS(SELECT * FROM @msgs) BEGIN SET @rc = 1 BREAK END — Create a cursor to iterate through the messages. DECLARE msgs_cursor CURSOR FOR SELECT conversation_handle, service_contract_name, message_type_name, CONVERT(VARCHAR(MAX), message_body) FROM @msgs; — Open the cursor OPEN msgs_cursor; — Perform the first fetch and store the values in the variables. FETCH NEXT FROM msgs_cursor INTO @conversation_handle, @service_contract_name, @message_type_name, @xml_message_body — Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE (@@FETCH_STATUS = 0) BEGIN — Check if the message is a send mail message IF(@message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail’) BEGIN — Get the handle to the xml document EXEC @rc = sp_xml_preparedocument @idoc OUTPUT, @xml_message_body, N’‘ IF(@rc <> 0) BEGIN IF(@rc < 4) — make sure return code is not in reserved rang (1-3) SET @rc = 4 END ELSE — parse the document and process its contents BEGIN — Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId. SELECT @mailitem_id = MailItemId FROM OPENXML (@idoc, ‘/requests:SendMail’, 1) WITH (MailItemId INT ‘./MailItemId’) –Close the handle to the xml document EXEC sp_xml_removedocument @idoc — get account information SELECT @profile_id = profile_id FROM sysmail_mailitems WHERE mailitem_id = @mailitem_id IF(@profile_id IS NULL) — mail item has been deleted from the database BEGIN — log warning SET @localmessage = FORMATMESSAGE(14667, convert(NVARCHAR(50), @mailitem_id)) exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage — Resource clean-up IF(@conversation_handle IS NOT NULL) END CONVERSATION @conversation_handle; — return code has special meaning and will be propagated to the calling process SET @rc = 2 END ELSE BEGIN — This returns the mail item to the client as multiple result sets EXEC sp_MailItemResultSets @mailitem_id = @mailitem_id, @profile_id = @profile_id, @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = @message_type_name SET @exit = 1 — make sure we exit outer loop END END — always break from the loop upon processing SendMail message BREAK END — Check if the message is a dialog timer. This is used for account retries ELSE IF(@message_type_name = N’http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer’) BEGIN — Handle the retry case. – DialogTimer is used for send mail reties EXEC @rc = sp_process_DialogTimer @conversation_handle = @conversation_handle, @service_contract_name = @service_contract_name, @message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail’ — Always break from the loop upon processing DialogTimer message — In case of failure return code from procedure call will simply be propagated to the calling process SET @exit = 1 — make sure we exit outer loop BREAK END — Error case ELSE IF (@message_type_name = ‘http://schemas.microsoft.com/SQL/ServiceBroker/Error’) — Error in the conversation, hence ignore all the messages of this conversation. BREAK — This is executed as long as fetch succeeds. FETCH NEXT FROM msgs_cursor INTO @conversation_handle, @service_contract_name, @message_type_name, @xml_message_body END CLOSE msgs_cursor; DEALLOCATE msgs_cursor; — Check if we read any request or only SSB generated messages — If a valid request is read with or without an error break out of loop IF (@exit = 1 OR @rc <> 0) BREAK –Keep track of how long this sp has been running select @timediff = DATEDIFF(ms, @start_time, getdate()) END — propagate return code to the calling process RETURN @rc END

I’m kidding now – just used “paste as html”  – but in this procedure You need find one line (line no 121):

SET @localmessage = FORMATMESSAGE
     (14667, convert(NVARCHAR(50), @mailitem_id))

which should be changing for:

SET @localmessage = FORMATMESSAGE(14667,  @mailitem_id)

After changing, just EXECUTE and effect will be shown immediately (HURRA!!):

Capture_SQLSRV2005Wait_06

Yes, no more red bubbles. But it is not the end of this story…

###

This problem appears first time in SQL Server 2005 and still is alive – for all versions except the new one – SQL Server 2012. Lets see it:

SQL Server 2005 (express) – line 121:

image

SQL Server 2008 (express) – line 121:

image

SQL Server 2008 R2 (express) – line 121:

image

SQL Server 2008 R2 (enterprise) – line 121:

image

ACHTUNG BABY!!! This problem has been fixed in SQL Server 2012

SQL Server 2012 (express) – line 111:

image

SQL Server 2012 (enterprise) – line 111:

image

That’s good. That’s good news for Aaron Bertrand, right?

So my advice for all DBA’a – after installation correct this procedure as an element of “first SQL Server Configuration Steps” – and no more red baloons (of course with this case and of course if it is important for you and of course if you will use DatabaseMail) for SQL Server 2005, 2008 and 2008 R2.