Archive for the 'SQL Server' Category



Disable Auto-Recover in SSMS

I recently upgraded to SQL Server Management Studio 2008. (I LOVE the Script as … Drop and Create functionality.) Everything was working great at first, but after a while I noticed that management studio was freezing up on for a couple of seconds every minute. This got progressively worse throughout the day.

Then I noticed that this freeze coincided with the Auto Recover save time. I had never messed with this setting before, so I looked around to see where to change it.

Apparently there is no way to change this in the GUI, but you can change it in the registry.

SQL 2005

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

SQL 2008

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

~Ron

Installing (and Running) SSRS 2008 in Windows Vista

It’s been a while since I’ve played around with SQL Server Reporting Services, and I found a couple of interesting articles on using a report model with SSRS, so I decided to give it a go.

The installation went smoothly, as I have performed this task many times. However, when I visited the reporting services site I had no access. I am an administrator on the machine, and as such should have had access to the site.

I searched around for a good half hour and still hadn’t found anything that worked. Somehow I got the idea to run my internet browser as Administrator. Eureka! I added my local account to the SSRS instance and everything works very nicely.

~Ron

Debug Stored Procedures in SQL 2000 with Visual Studio

Before I forget this again … if you are not a member of the sysadmin role you need to run this to debug SQL 2000 stored procedures in Visual Studio.

USE [master];
GO

GRANT EXECUTE ON sp_sdidebug TO [User\Group\Role];

Attach Database Scripts

I was tasked with migrating a development database server to another development database server. Time was of the essence, so the decision was made to detach the files from the original database server and attach them to the new server.

There were twenty-one databases in all. The caveat in all this was each database had multiple file groups and multiple files within each file group. There are plenty of attach database scripts out there, but none of them worked for me with the conditions listed above.

Since time was of the essence and this would be a massive undertaking if done manually, I wrote my own script to add to the masses. This script worked perfectly for me, and I hope you find it useful.

NOTE: I have added the option about half way down to create the scripts and have the logs rebuilt in case you only want to move the data files.

NOTE2: You should run the results to text.

~Ron

CREATE TABLE #_dba_migrate_db
(
	FileID int IDENTITY(1,1) NOT NULL,
	DBName varchar(255) NOT NULL,
	DBGroupName varchar(255) NOT NULL,
	DBFileLogicalName varchar(255) NOT NULL,
	DBFileName varchar(1000) NOT NULL,
	DBSize varchar(15) NOT NULL,
	DBMaxSize varchar(15) NOT NULL,
	DBFileGrowth varchar(15) NOT NULL,
	CONSTRAINT PK_#_dba_migrate_db PRIMARY KEY CLUSTERED
	(
		FileID
	)
);
GO

sp_msforeachdb
'
	USE [?];

	IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
		INSERT INTO #_dba_migrate_db
		(
			DBName,
			DBGroupName,
			DBFileLogicalName,
			DBFileName,
			DBSize,
			DBMaxSize,
			DBFileGrowth
		)
		SELECT
			DB_NAME(),
			ISNULL(g.groupname,''LOG'') GroupName,
			LTRIM(RTRIM(f.name)) LogicalName,
			LTRIM(RTRIM(f.filename)) FileName,
			CAST(CEILING(((CONVERT(decimal(25,2),f.size)/1024)*8)) AS varchar(15)) + ''MB'' [Size],
			CASE f.maxsize WHEN -1 THEN ''UNLIMITED'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.maxsize)/1024)*8)) AS varchar(15)) + ''MB'' END [MaxSize],
			CASE WHEN f.status & 0x100000 > 0 THEN CAST(f.growth AS varchar(15)) + ''%'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth)/1024)*8)) AS varchar(15)) + ''MB'' END [FileGrowth]
		FROM
			sysfiles f
		INNER JOIN
			sysfilegroups g ON
				f.groupid = g.groupid
		UNION ALL

		SELECT
			DB_NAME(),
			''LOG'' GroupName,
			LTRIM(RTRIM(f.name)) LogicalName,
			LTRIM(RTRIM(f.filename)) FileName,
			CAST(CEILING(((CONVERT(decimal(25,2),f.size)/1024)*8)) AS varchar(15)) + ''MB'' [Size],
			CASE f.maxsize WHEN -1 THEN ''UNLIMITED'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.maxsize)/1024)*8)) AS varchar(15)) + ''MB'' END [MaxSize],
			CASE WHEN f.status & 0x100000 > 0 THEN CAST(f.growth AS varchar(15)) + ''%'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth)/1024)*8)) AS varchar(15)) + ''MB'' END [FileGrowth]
		FROM
			sysfiles f
		WHERE
			groupid = 0
'
GO

SET NOCOUNT ON;

DECLARE
	@DBName varchar(255),
	@DBGroupName varchar(255),
	@FileID int,
	@MaxData int,
	@MaxLog int,
	@IncludeLog bit;

SET @IncludeLog = 0;

DECLARE db_cursor CURSOR FOR
	SELECT DISTINCT
		DBName
	FROM
		#_dba_migrate_db

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT 'CREATE DATABASE ' + @DBName + ' ON'

	DECLARE group_cursor CURSOR FOR
		SELECT
			MIN(FileID) FileID,
			DBGroupName
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			(DBGroupName <> 'LOG' OR @IncludeLog = 1)
		GROUP BY
			DBGroupName
		ORDER BY
			MIN(FileID);

	OPEN group_cursor;

	FETCH NEXT FROM group_cursor INTO @FileID,@DBGroupName;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT
			@MaxData = MAX(FileID)
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName <> 'LOG';

		SELECT
			@MaxLog = MAX(FileID)
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName = 'LOG';
			

		SELECT CASE WHEN @DBGroupName NOT IN ('PRIMARY','LOG') THEN 'FILEGROUP ' + @DBGroupName
			WHEN @DBGroupName = 'LOG' THEN @DBGroupName + ' ON' ELSE @DBGroupName END;

		SELECT
			+ '('
			+ CHAR(10)
			+ '	NAME = ' + DBFileLogicalName + ', '
			+ CHAR(10)
			+ '	FILENAME = ''' + DBFileName + ''','
			+ CHAR(10)
			+ '	SIZE = ' + DBSize + ','
			+ CHAR(10)
			+ '	MAXSIZE = ' + DBMaxSize + ','
			+ CHAR(10)
			+ '	FILEGROWTH = ' + DBFileGrowth
			+ CHAR(10)
			+ ')'
			+ CASE WHEN FileID IN (@MaxData,@MaxLog) THEN '' ELSE ',' END
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName = @DBGroupName;

		FETCH NEXT FROM group_cursor INTO @FileID,@DBGroupName;
	END

	CLOSE group_cursor;

	DEALLOCATE group_cursor;

	SELECT
		CASE WHEN @IncludeLog = 1 THEN 'FOR ATTACH;' ELSE 'FOR ATTACH_REBUILD_LOG;' END
		+ CHAR(10)
		+ 'GO'
		+ CHAR(10);

	FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;

DEALLOCATE db_cursor;

DROP TABLE #_dba_migrate_db

Search SQL Agent Job Steps

I’ve never really that about how enigmatic SQL Agent jobs can be because I am usually the one creating them. Anyways, a developer was asking if I knew a way to search the T-SQL within a job, so here it is …

DECLARE
      @SearchString varchar(255),
      @IsEnabled bit;
 
SET @SearchString = 'warp'; -- Enter Search String Here. Leave Blank for All
SET @IsEnabled = 1; -- 0 = Disabled, 1 = Enabled, 2 = All
 
SELECT
      j.Name JobName,
      j.Description JobDescription,
      js.step_id StepID,
      js.step_name StepName,
      js.database_name DatabaseName,
      js.command StepCommand  
FROM
      msdb..sysjobs j
INNER JOIN
      msdb..sysjobsteps js ON
            j.job_id = js.job_id
WHERE
      (j.enabled = @IsEnabled OR @IsEnabled = 2) AND
      js.command LIKE '%' + @SearchString + '%'
ORDER BY
      j.Name,
      js.step_id;

~Ron

Change Default Directories for SSMS 2005

I needed to change the default directories for SSMS (SQL Server Management Studio) 2005, and it took me a bit Googling to figure it out, so I thought I would post it here in case I forget. 🙂

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM]

The default value is “%USERPROFILE%\My Documents\SQL Server Management Studio Express\Projects\” in case you really bork things up and need to reset them.

Hopefully this helps you, too.

~Ron

Debugging Stored Prcedures in Visual Studio

I was speaking with a developer friend of mine the other day and the topic of debugging stored procedures came up. My friend was unaware that this was even possible, so I thought that I would share.

In SQL2000 debugging was as simple as right clicking on a stored procedure in Query Analyzer and selecting Debug from the context menu. This was a simple, yet effective method, but is very limited in its capabilities. That feature was removed in SSMS (SQL Server Management Studio).

For SQL 2005 and above, this can be done in Visual Studio. In server explorer create a data connection. Drill down to the stored procedure that you want to debug and click “Step Into Stored Procedure.” From here you can step though the stored procedure.

Alternatively, you can right click on the stored procedure and select “Open” from the context menu. From there you can set breakpoints, change SQL blocks, execute SQL blocks and step into the stored procedure.

My two favorite features of debugging stored procedures in Visual Studio are the ability to see and change variables as you step through the code and the ability to see the managed code when you open a CLR procedure.

For a step by step explanation (with pictures) you can check out SQL Team‘s article, which explains the steps better than I.

Happy debugging!

~Ron

Helpful SQL Server Date Functions

One of my common duties is to create and/or update reports in SSRS (SQL Server Reporting Services). A large number of these reports ran from very unclean data. To compensate for this, the original developers handled the data in a variety of inconsistent manners, mostly around dates. Although this may have gotten the job done originally, it does nothing for the quality of the report.

As these reports were updated, we began using standard date functions to improve development time and report quality.


/* First Day of the Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

/* Last Day of the Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0))

/* First Day of Next Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,1,GETDATE())),0)

/* Last Day of Next Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,2,GETDATE())),0))

/* First Day of Last Month */
SELECT DATEADD(mm,DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())),0)

/* Last Day of Last Month */
SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

/* First Day of the Year */
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

/* Last Day of the Year */
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,DATEADD(yy,1,GETDATE())),0))

/* First Day of the Quarter */
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

/* Last Day of the Quarter */
SELECT DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,DATEADD(qq,1,GETDATE())),0))

/* First Monday of the Month */
SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6 - DATEPART(DAY,GETDATE()),GETDATE())),0)

~Ron

SharePoint Farm Expansions

When I started my current position, I inherited a SharePoint mess. Before I arrived a tech was tasked with installing SharePoint development environment in preparation for a deployment. Due to hardware constraints or some such issue, the development installation and hardware soon became the production environment.

The first hurdle was actually licensing the software. The installation was done with a demo version. We purchased the requisite licenses under out volume license agreement, which of course means that activation failed. Remember, this is a production environment. A couple of weeks later we acquired the proper key and we were on our way.

Our production environment had less than 250 total users and less than 50 regular users for the last year and has humming along quietly. Then came the expansion project.

Now, the current installation was the default installation, which means that it was running off of SQL Server 2005 Express. The service accounts were all NETWORK SERVICE. (Again, and I cannot say this enough, this was done before I arrived!)

We had to expand (finally) our SharePoint Farm. OK, first things first, lets get those service accounts configured properly and put into production. Best practices aside, this is necessary as we will be standing up an additional SQL Server and the SharePoint service accounts are the credentials used to access the databases.

Set the service principle name for each SharePoint service account.

setspn -A HTTP/ServerFQDN DOMAIN\Account
setspn -A HTTP/ServerFQDN:80 DOMAIN\Account
setspn -A HTTP/ServerNetBIOSName DOMAIN\Account
setspn -A HTTP/ServerNetBIOSName :80 DOMAIN\Account

Now do it for each SQL service account.

setspn -A MSSqlSvc/ServerFQDN DOMAIN\Account
setspn -A MSSqlSvc/ServerFQDN:1433 DOMAIN\Account
setspn -A MSSqlSvc/ServerNetBIOSName DOMAIN\Account
setspn -A MSSqlSvc/ServerNetBIOSName :1433 DOMAIN\Account

Now that the accounts are created we need to swap out the credentials. Simple enough task, but it failed miserably. The error referenced that an update was currently in progress. I was a bit confused, as I was the only one in the system, but after a bit of research I located a fix.

Stop the “Windows SharePoint Services Timer” service. Navigate to C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config\{GUID}. Backup the contents of this directory. After the backup completes delete everything except cache.ini. Open cache.ini and replace the contents with “1” and save the file. Start the “Windows SharePoint Services Timer” service.

Now to redirect the SQL Server. Open C:\windows\system32\cliconfg.exe. Added an alias on the application server redirecting the old instance to the new one. Stop the SQL services on the old server and copy the data and log files to the new server. Attach the databases on the new server.

SharePoint was still throwing a fit, so for good measure I rebooted both servers.

SUCCESS!!!

~Ron

Cannot Connect to Named Instance of SQL Server behind a Firewall

I installed SQL Server 2008 Express on all of my workstations at home this weekend to have a test environment to play with before my 70-453. I had never installed SQL Server across a workgroup before, so it was a learning experience.

Two of the workstations were Windows XP Professional and one was Windows Vista Home Premium. The installs were all fairly uneventful. Being SQL Express intalls, i used the SQLEXPRESS named instance. I opened up TCP port 1433 in Windows Firewall and enabled remote connections.

All set, right? No.

OK. Let’s check everything again.

  1. SQL Server Browser service running? Check
  2. Remote connections enabled? Check
  3. Exception for TCP port 1433? Check

All set, right? No.

Crap.

After a half hour of searching and five minutes of cursing I discover the SQL Server Browser service uses UDP port 1434. I added the exception and everything is hunky-dory.

~Ron