Duplicate Commands within the Data Menu of BIDS 2008

I recently stumbled upon a problem where there were duplicate commands within the sub menu of Data in Business Intelligence Development Studio 2008. To give you a birds eye view below is what we were facing.

With a little research and some musical chairs with keywords I stumbled upon the following post: Visual Studio team System 2008 Database Edition FAQ which pinpointed the solution. You’ll need to scroll down a bit till you see “I have installed the GDR, but now I see duplicate commands on the menus in the IDE.”

Resolution Steps

1) Be sure to exit out of all the visual studio sessions
2) Open a command prompt
3) Within the command prompt navigate to %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\

Execute the following:

DBProRepair.exe RemoveDBPro2008

You will not see a confirmation message after running the above statement. To verify launch BIDS and click on Data. It should now look like the following.

Advertisements

SQL Agent Won’t Start After Switching the Service Account

My problem started shortly after I switched the service account that powered the SQL Services. On the initial install I used my domain login during the install. Then I received the good old change password nag and changed my password three or four days later. Life went on just peachy until I had to reboot. So in order to avoid this again I decided to use a different domain account. Using SQL Server Configuration Manager I changed all the services to use the new domain account and began the process of starting each service leaving the agent as the last. To my surprise I received a nice little error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

At this point I thought a reboot might resolve this which it didn’t. So I decided to listen to the error message and search through the error logs. In the Windows Event Viewer I found the following:

Login failed for user ‘xxxxx\sqlservice’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

After reading this I thought the SQL permissions were incorrect so I double checked those and they turned up fine. So I proceeded on. This time to the SQL error logs in the instance directory. In my case “C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV08R2\MSSQL\Log”.

I opened the ERRORLOG with notepad and began reviewing the contents and I found some interesting information. Essentially it stated:

Error: 18456, Severity: 14, State: 38.
Login failed for user ‘XXXXX\sqlservice’

Now I am thinking perhaps I mistyped the password which seemed like a viable action seeing the rest of the services were working fine. So I visit the Configuration Manager again and attempt to redo the password. For Schnitz and Giggles I decided to deliberately enter in the wrong password and surprisingly I received a nice error:

The specified network password is not correct.

Now I am puzzled. The password was right all along yet the previous error is saying that the login failed. WTF? To rule out if this is a Windows permissions or SQL Server permissions issue I added the service account to the local Administrators group and suddenly I was able to start the service. At the very least I now know that this is a Windows permission issue. Now I just need to figure out where this permissions issue resides.

I started to review the accounts and remembered during the installation that SQL Server creates Windows groups. So I hone in on the SQLServerMSSQLUser$XXXXXX$DEV08R2 group and find that the service account is not listed. So I add the service account to this group and kick off the Agent service and boom… it works! I tried changing the service account on a SQL Server 2005 instance and I noticed that the Windows group was updated so I am not sure why SQL Server 2008 R2 didn’t behave the same way.

The actual permissions are required within the instance directory. Here is where I found it.

And the required permissions are as illustrated.

Side Note

Once you switch the service account make sure you take a look at the owner for the databases and jobs. In my case I wanted all the databases and jobs to have the owner of the sqlservice so I ran the following query to address the database owner.

SELECT 
	name
	,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd'
	,SUSER_SNAME(owner_sid) 'owner'
FROM sys.databases
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)

Then I copied the contents from the ‘cmd’ column and executed them to make the change.


Note: you cannot change the owner of the master, model, msdb and tempdb databases. If you attempt to change the owner of the system databases you will be greeted with an error.

To circumvent this you can add an additional condition to the WHERE clause as followed and this will skip the system databases.

SELECT 
	name
	,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd'
	,SUSER_SNAME(owner_sid) 'owner'
FROM sys.databases
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)
  AND (database_id NOT IN (1,2,3,4))

The stored procedure sp_changedbowner will be removed in a future version of SQL Server. Which means you need to learn how to use ALTER AUTHORIZATION. Which I picked up from Jes’s article Changing a SQL Server Database. Here is the revised script you would use to generate the ALTER AUTHORIZATION statement.

SELECT 
	name
	,SUSER_SNAME(owner_sid) 'owner'
	,'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO [sa]' 'cmd'
FROM sys.databases
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)

Lastly I ran the following query to generate the ‘cmd’ statement which changed the job owner for all the jobs I had set up. Keep in mind that if you have any backup jobs that are backing up to a network share you will need to update the share permissions and add the service account to permit writing to that location.

SELECT
	name
	,'EXEC msdb..sp_update_job @job_name = ''' + name + ''', ''@owner_login_name = xxxxxx\sqlservice''' 'cmd'
	,SUSER_SNAME(owner_sid) 'owner'
FROM msdb..sysjobs	
WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice')
   OR (SUSER_SNAME(owner_sid) IS NULL)

Update to SQL Server 2008 R2 CU6

I got my work laptop environment finally squared away and today was the first attempt to update my instances of SQL Server 2008 R2 Developer and Express to CU3. Yes I know the post title say CU6, trust me I’ll get to that in a moment. Originally I was going to update to CU3 reason being because it addressed an issue I was experiencing with creating, adding or editing SQL Agent jobs in SSMS. Matter of fact here is the Microsoft article address: http://support.microsoft.com/kb/2315727

Here is the specific error I received when I attempted to edit an agent job.

Related Connect feedback

So I downloaded CU3 from Microsoft.

When I tried to apply CU3 I encountered an error which basically suggested that I had to uninstall SQL Native Client because it was not installed via sqlcnli.msi. Here is the url that the error pointed me to.

Source: setup.rll ID: 50000 (SQL Server 10.50.1600.1) – Events And Errors Message Center: Message Details

Needless to say I decided to reboot hoping that would some how, some way correct the issue auto-magically. WRONG! So I decided (executive decision) to push forward and apply CU6. See I told you I would eventually get to it! Well I downloaded CU6 and attempted to install and ran into another error.

Here is the full Summary.txt

Well again I tried a reboot and that did not work, so I surfed around and then stumbled on a post by Jonathan Kehayias (@SQLSarg | blog) titled: SQL Server 2008 failed installation blocks Service Pack 1 (SP1) installation. He essentially performed a repair which resolved his issue so I felt inclined to do the same and BOOM… CU6 applied oh so nicely. I cannot say if this will help you, but it helped me. Both my Dev and Express editions are at CU6 so I am a happy camper.

Get row count from all tables

I was reviewing some of my older scripts and I came across one in particular that stood out. I wrote it quite a while ago and looking back does indicate where I came from in terms of logic. To give a little background my script was written to grab the row counts from all tables within the given database. The problem… it created a temp table then loaded the temp table from querying the INFORMATION_SCHEMA.TABLES. Then it looped through each of the table names loading them it into a variable and then passed them to an update statement which updated the temp table. As you can see this was a very inefficient way to go about it.

I was reading up on catalog views and it seemed that I could query sys.sysobjects and join sys.sysindexes. So I figured I would get this verified. I posed the question and following are the responses I received.

Yep. sys.sysindexes is depreciated in Denali. Information here: msdn.microsoft.com

So here is my script. I originally left out line 15 and when I did I saw a lot of duplication in the results set. So I surfed around and found a post titled, “Display Row count for all tables” by Shyam Skj that used the indid field from sysindexes to filter the records. The closes thing I found was the index_id from sys.partitions and lo and behold using similar syntax the results returned nicely.

/* Check if object already exists */
IF OBJECT_ID('usp_GetTableRowCounts') IS NOT NULL
/* If available drop object */
BEGIN
DROP PROCEDURE usp_GetTableRowCounts
END
GO
/* Create procedure */
CREATE PROCEDURE usp_GetTableRowCounts
AS
SELECT o.name 'TableName', p.rows 'Rows'
FROM sys.objects o
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE (o.type = 'U')
AND (p.index_id IN (0,1))
ORDER BY p.rows DESC

AdventureWorks Results

Dropping a subscriber database

Usually I can simply run an ALTER statement against the database and SET SINGLE_USER WITH NO_WAIT like so:

ALTER DATABASE MyDBName SET SINGLE_USER WITH NO_WAIT

but I ran into a problem with active processes that prevented me from switching to SINGLE_USER mode. So I executed the sp_Who2 stored procedure and identified the spids responsible and killed them!

EXEC sp_Who2

I essentially ran the kill command for every spid that was associated to the specific database. So you can see it was very inefficient and a great waste of time.

KILL 54
KILL 58

Now imagine if I had to do this will 200 subscribers… not a fun time!

Then I re-ran the stored procedure sp_Who2 again to make sure they were dead.

EXEC sp_Who2

This worked for me at the time, but later in the future I don’t want to deal with the hassles of running through all the steps involved. I turned to BOL and then I remembered I could easily query the sysprocesses and mock up a script but I didn’t really have the time to run through the whole Q/A process. So I searched the interwebs and stumbled upon the blog post [Script to drop all connections to a Database] by Tijo. It was exactly what I was looking for. So I borrowed it and merged it with some additional items that are specific to our clean up steps. In a nutshell here is the final outcome.

-- KILL ACTIVE PROCESSES
DECLARE @db NVARCHAR(50)
DECLARE @spid int
SET @db = 'myDBName'

SELECT @spid = MIN(spid) 
FROM sys.sysprocesses
WHERE dbid = db_id(@db)
WHILE @spid IS NOT NULL
	BEGIN
		SELECT @spid
		EXEC ('KILL ' + @spid)
		SELECT @spid = MIN(spid) 
		FROM sys.sysprocesses
		WHERE dbid = db_id(@db)
	END
	
-- DROP DATABASE
DECLARE @cmd1 NVARCHAR(75)
DECLARE @cmd2 NVARCHAR(50)
SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT'
SET @cmd2 = 'DROP DATABASE ' + @db
EXEC(@cmd1)
EXEC(@cmd2)
	
-- DROP USER ACCOUNT
DECLARE @cmd3 NVARCHAR(65)
IF  EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent')
SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]'
EXEC(@cmd3)
GO

Also thinking about the future I combined this sql script with a batch file using SQLCMD and provided it to the onsite support staff so that they can handle the reinitialization for subscriptions that fall outside of their threshold.

@echo off
color 17
Title Device Reinit Clean Up . . .

:: =========================
SET srv=(local)\Instance
:: =========================

SQLCMD -S %srv% -E -i "Device.ReinitCleanUp.sql"
PAUSE

Both files need to reside in the same directory in order to work; however, if you decide to call the sql script from a network share be sure to make the necessary adjustments. Reason being is that we don’t deploy management studio on the subscribers and some of the onsite technical support staff are unfamiliar with SQL Server. So I would rather not take any chances which is why I am going to add an additional condition.

IF HOST_NAME() = 'servername'
BEGIN
	PRINT '**** WARNING ****'
	PRINT 'You attempted to apply the cleanup script against the Production Server!'
	PRINT 'Terminating process!'
END

ELSE
	BEGIN
		-- KILL ACTIVE PROCESSES
		DECLARE @db NVARCHAR(50)
		DECLARE @spid int
		SET @db = 'myDBName'

		SELECT @spid = MIN(spid) 
		FROM sys.sysprocesses
		WHERE dbid = db_id(@db)
		WHILE @spid IS NOT NULL
			BEGIN
				SELECT @spid
				EXEC ('KILL ' + @spid)
				SELECT @spid = MIN(spid) 
				FROM sys.sysprocesses
				WHERE dbid = db_id(@db)
			END
			
		-- DROP DATABASE
		DECLARE @cmd1 NVARCHAR(75)
		DECLARE @cmd2 NVARCHAR(50)
		SET @cmd1 = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER WITH NO_WAIT'
		SET @cmd2 = 'DROP DATABASE ' + @db
		EXEC(@cmd1)
		EXEC(@cmd2)
			
		-- DROP USER ACCOUNT
		DECLARE @cmd3 NVARCHAR(65)
		IF  EXISTS (SELECT * FROM sys.syslogins WHERE name = + HOST_NAME() + '\MergeAgent')
		SET @cmd3 = 'DROP LOGIN [' + HOST_NAME() + '\MergeAgent]'
		EXEC(@cmd3)
	END

Calculate Subscription Expiration Threshold

While I was piecing together a few queries for a report intended to deliver subscription information about subscribers that are nearing the expiration threshold for replication I found myself needing to calculate warning threshold levels. In one case I found not only do I need to calculate the threshold warning criteria but I also needed to round the outcome to the nearest whole integer.

As you probably know the default retention period for replication defaults to 14 days; however, in replication monitor you can set up warnings to indicate if a subscriber is nearing the threshold. Typically this is 80% so in short I am going to take the retention value and multiple it by .8 and round to the nearest whole integer. For this example I really only need to query data from one table [MSreplication_monitordata] which resides within the distribution database. If you have replication setup then you will have a distribution database, but if you don’t then the distribution database will not be available. Also note this example is based on a single publication so if you have multiple publications then you will see more than one entry per subscriber per publication.

Step 1: Simple Query

I just want the retention period…

SELECT retention 
FROM [Distribution]..[MSreplication_monitordata] 

Now we know what the retention period is set to and that is 14 days. Now I need to multiply the retention value by .8 and the result will be 11.2. So 80% of 14 = 11.2.

Step 2: Simple Calculation

SELECT retention
      ,(retention * .8) 
FROM [Distribution]..[MSreplication_monitordata]

Side Note:

Keep in mind if you want to get the threshold value of the metric being monitored from the database JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id and pull the t.[value] which will return the value of 80 which is the default. If it was changed then of course the value will differ. Just note that this is a sql_variant data type so you will need to CAST or CONVERT it to perform the calculation.

SELECT	m.retention 
		,t.[value]
		-- These steps are just for illustration
		,(CAST(t.[value] AS DECIMAL(10,2))/100) '1'
		,(m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) '2'
		,CAST((m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) AS INT) '3'
		-- The following is faster and easier
		,(m.retention * CAST(t.[value] AS INT)/100) '4'
FROM [Distribution]..[MSreplication_monitordata] m
JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id

What this means in terms of Replication Monitor is that any subscriber that has not synchronized for 11 days with the retention set to 14 days will have a subscription status of “Expiring Soon/Expired“. If the retention was set to 30 days which is also common and the threshold set to the default of 80% then the subscribers would show the same the status when they reach 24 days without synchronizing.

Step 3: Round the Results

11.2 is a good start, but I need a whole number so I will wrap line 2 from (Step 2) inside a ROUND function and supply 0 for the length or precision as it needs one argument.

SELECT retention
      ,ROUND((retention * .8) ,0)
FROM [Distribution]..[MSreplication_monitordata]

Step 4: Remove the Decimal

The above statement returns a value of 11.0 which is rounded, but now I need to get rid of the decimal place so that 11 is the only thing returned. Again I will take line 2 this time from (Step 3) and wrap it in a CAST function and CAST it as INT.

SELECT retention
      ,CAST(ROUND((retention * .8) ,0) AS INT)
FROM [Distribution]..[MSreplication_monitordata]

Or you could have saved yourself a few steps and done this…

 
SELECT retention 
      ,CAST((retention * .8) AS INT) 
FROM [Distribution]..[MSreplication_monitordata] 

Final Results

When I apply the rounded value to my query as part of the WHERE clause only the records that fall within the expiration threshold (retention * .8) will be returned. Meaning subscriptions that are close to expiration and subscriptions that are expired.

The results are exactly what I wanted. Now I just need to take it a bit further… think email delivery!

I have wrapped this into a stored procedure and added some logic that will check for expiring records and if there are records found it will then tie the results into a nice html formatted message delivered by database mail. Then I setup a sql job which runs the stored procedure daily including weekends and fires off an email only when records are found. Technically I could fire off a report subscription which can be formatted very nicely and just fire off the job using exec sp_start_job @JobName = ‘Report Subscription ID goes here’ when ever records are detected.

For more information about using ROUND please visit: http://msdn.microsoft.com/en-us/library/ms175003.aspx

Download the code

SELECT.GetExpiringSubscriptions_v0.01.sql

/*****************************************************************
**	Author	:	Samson J. Loo (justsamson.com | @just_samson)
**	Created : 9/26/2010
**	Intent	: Notification of subscriptions that are nearing the
**						the expiration threshold
**	Notes	: Requires db mail
**	Version	: 0.01
*****************************************************************/

IF OBJECT_ID('sp_RP_GetExpiringSubscribers') IS NOT NULL
DROP PROCEDURE sp_RP_GetExpiringSubscribers
GO
CREATE PROCEDURE [dbo].[sp_RP_GetExpiringSubscribers]
AS

DECLARE @cnt INT
SET @cnt = 0
SELECT	@cnt = COUNT(a.[subscriber_name])
FROM [Distribution]..[MSmerge_sessions] s
	JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
	JOIN [Distribution]..[MSreplication_monitordata] m ON s.agent_id = m.agent_id
	JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
WHERE s.end_time IN
(
	SELECT TOP 1 s1.end_time 
	FROM [Distribution]..[MSmerge_sessions] s1
	WHERE s.agent_id = s1.agent_id
	ORDER BY s1.end_time DESC
) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
AND t.[isenabled] = 1
GROUP BY s.[start_time]

IF @cnt > 0
	BEGIN
		
		DECLARE @tableHTML  NVARCHAR(MAX) ;

		SET @tableHTML =
			N'<H1><font color="#FF0000">Expiring Subscription Report</font></H1>' +
			N'<table border="0" cellspacing="2" cellpadding="2">' +
			N'<tr><th bgcolor="#BDBDBD">Subscriber</th>' +
						N'<th bgcolor="#BDBDBD">Status</th>
							<th bgcolor="#BDBDBD">Delivery Rate</th>
							<th bgcolor="#BDBDBD">Last Sync</th>' +
						N'<th bgcolor="#BDBDBD">Duration</th>
							<th bgcolor="#BDBDBD">Conn Type</th>
							<th bgcolor="#BDBDBD">Result</th>
							<th bgcolor="#BDBDBD">Days Behind</th>					
						  <th bgcolor="#BDBDBD">Subscriber Status</th></tr>' +
			CAST ( ( 

		SELECT
		td = CASE
					WHEN CHARINDEX('\',a.[subscriber_name]) > 0 THEN LEFT(a.[subscriber_name],CHARINDEX('\',a.[subscriber_name])-1)
					ELSE a.[subscriber_name]
				END	
			,''
			,td = CASE
				WHEN s.[runstatus] = 3 THEN 'Synchornizing'
				WHEN s.[runstatus] = 5 THEN 'Retrying failed command'
			ELSE 'Not Synchronizing'
			END 
			,''
			,td = CAST(s.[delivery_rate] AS VARCHAR) + ' rows/sec'
			,''
			,td = s.[start_time]
			,''
			,td = CAST((s.[duration]/86400) AS VARCHAR) 
			+ '.' + CAST(REPLACE(STR(((s.[duration]/3600) - ((s.[duration]/86400) * 24)),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 3600/60),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 60),2),SPACE(1),0) AS VARCHAR)	
			,''
			,td = CASE 
				WHEN s.[connection_type] = 1 THEN 'LAN'
				WHEN s.[connection_type] = 2 THEN 'Dialup'
				WHEN s.[connection_type] = 3 THEN 'Web Sync'
			END
			,''
			,td = CASE
				WHEN s.[runstatus] = 1 THEN 'Start'
				WHEN s.[runstatus] = 2 THEN 'Succeed'
				WHEN s.[runstatus] = 3 THEN 'In Progress'
				WHEN s.[runstatus] = 4 THEN 'Idle'
				WHEN s.[runstatus] = 5 THEN 'Retry'
				WHEN s.[runstatus] = 6 THEN 'Error'
			END
			,''
			,td = DATEDIFF(d,s.[start_time],getdate())
			,''
			,td = CASE 
				WHEN (DATEDIFF(d,s.[start_time],getdate()) < CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)) THEN 'Good'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) <= m.[retention]) THEN 'Expiring Soon'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) > m.[retention]) THEN 'Expired'
			END
			--,m.[retention]
		FROM [Distribution]..[MSmerge_sessions] s
			JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
			JOIN [Distribution]..[MSreplication_monitordata] m ON a.id = m.agent_id
			JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
		WHERE s.end_time IN
		(
			SELECT TOP 1 s1.end_time 
			FROM [Distribution]..[MSmerge_sessions] s1
			WHERE s.agent_id = s1.agent_id
			ORDER BY s1.end_time DESC
		) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
		AND t.[isenabled] = 1
		ORDER BY s.[start_time]    
		    
			FOR XML PATH('tr'), TYPE 
			) AS NVARCHAR(MAX) ) +
			N'</table>' ;

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'WorkingNotifier',
			@recipients='you@yourdomain.com',
			@copy_recipients='someone@somewhere.com',
			@subject = 'Expiring Subscription Report',
			@body = @tableHTML,
			@body_format = 'HTML' ;
		
		
	END
ELSE
	BEGIN
		PRINT 'No Records Found!'
	END