[EN] New instalation versus existing feature: SQL Connect in VS2012

sql_connect_270x220My environment is not very complicated: just windows platform, some sql server instances, recording stuff, bloggers kit, visual studio installation and some third party (vendors) tools. One of them, installed ‘by default’ in my Visual Studio 2012 instance is SQL Connect by RedGate {LINK}.  Ingeniously simple database development in Visual Studio.  Connected, source-controlled database development in Visual Studio:

  • Develop databases in Visual Studio alongside application code
  • Query databases and execute stored procedures directly from Visual Studio
  • Track and share changes using your source control system

Very useful tool, especially when You think abou controlling your code, and data. I had installed, and fully updated Visual Studio 2012 environment, and tried install my RedGate’s SQL Developer Bundle (more about this ingeniously awesome pack in next posts). And I got an error:

Captura_RedGateDevBnd_error

Pict. 1 | in the process of installing full package, received error from SQL Connect 1.1 (MSI Error 1603)

On Microsoft Support page we can find information about cause of this error – KB834484:

You may receive this error message if any one of the following conditions is true:

  • The folder that you are trying to install the Windows Installer package to is encrypted.
  • The drive that contains the folder that you are trying to install the Windows Installer package to is accessed as a substitute drive.
  • The SYSTEM account does not have Full Control permissions on the folder that you are trying to install the Windows Installer package to. You notice the error message because the Windows Installer service uses the SYSTEM account to install software.

Because nothing of that is my cause I decided to skip this software in installation process, and installing all others software.

Captura_RedGateDevBnd_error2

Pict.2 | result is green for me.

Next I back to problematic package, using both paths with installation: default by vendor (c:\… I never use partition C for any of files if I really needn’t it), and default by me (somewhere else).

Captura_RedGateDevBnd_error3

Pict.3 | installation folder by default for this installation…

Captura_RedGateDevBnd_error4

Pict.4 | cannot help me. Still got an error.

I checked my Visual Studio 2012 installation and I discovered that SQL Connect 1.1. has been installed. Hmmm.. maybe conflict? I de-istalled this feature from my Visual Studio {Menu/Tools/Extentions and Updates}. And run RedGate setup again.

Captura_RedGateDevBnd_error5

Pict.5 | Got You. Finally green light.

 

It is a little strange for me. just installer crashed – better solution should be show me my mistake, like: “You have installed version. please delete other versions before you install this software”. Well, maybe Redgater’s could repair this installer, because this dependiences are important.

[PL] Windows Update Error: raz, drugi, trzeci..

Trzy serwery, wszystkie takie same: Windows Server 2008 R2 SP1 x64. Na dwóch poprawki ostatnio dało się zainstalować na trzecim już nie. Jedna próba, druga, trzecia. Restart, zmiana ustawień, sprawdzenie połączenia, adresacji, proxy i-te-pe i-te-de. I dalej nic. Efekt ciągle taki sam:

Rys.1 | Problem z pobraniem i instalacją poprawek.

W globalnej sieci (przy użyciu wybranej przez siebie wyszukiwarki) możemy znaleźć takie proste rozwiązanie tego problemu:

   1:  Uruchom CMD (z opcja Uruchom jako Administrator)
   2:  Pierwsze polecenie: 
   3:      net stop wuauserv
   4:  Drugie polecenie: 
   5:      ren c:\windows\SoftwareDistribution softwaredistribution.old
   6:  Trzecie polecenie: 
   7:      net start wuauserv

Rys.2 | Uruchomienie na koncie użytkownika lokalnego, mimo wybrania opcji runas nie dało odpowiedniego rezultatu.

Rys.3 | Natomiast na koncie administratora lokalnego zadziałało…

Rys.4 | … tak jak miało, czyli pozwoliło na odpytanie o poprawki…

Rys.5 | Oraz co najważniejsze ich pobranie i instalację. Na szczęście system pamiętał ile tych poprawek już było i informacja: “Updates were instaled: never” okazała się nie prawdziwa.

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