Dev Team Assemble

One thing I always seem to forget when moving from one db system to the other (not using DB2 all that often) is how to select the TOP n rows from a query.

In MS SQL Server this is expressed as SELECT TOP n * FROM [table] ...

the equivilent in DB2 is SELECT * FROM [table] FETCH FIRST n ROWS ONLY

Technorati Tags: DB2, SQL Server

A little while ago I was having a problem with MS SQL Server 2005, It was ballooning my log files and not recovering space after a backup and I posted a fix for this here.  The only issue I had left at that point was rolling it into a complete solution that can be run on the server and does not require being run manually.  The following script I put together can run as a Job (or as a SQL task in a Maintenance plan) and will backup all of the databases on your server with the exception of the system db's (master, msdb, temp, model) .  There are also sections in the script to add your own exclusions in case you want to remove long running backups or databases that do not require this sort of treatment, search for DB_2EXCLUDE.

NOTE: The word press plug-in for code formatting changes my greater than and less than sign into > and < respectivly so you will need to change this back if copying the code....until i figure it out anyway.

DECLARE @dbExt VARCHAR(4)
DECLARE @logExt VARCHAR(4)
DECLARE @backupLocation VARCHAR(100)
DECLARE @DATE VARCHAR(50)
DECLARE @tempFile VARCHAR(4000)
DECLARE @tempPath VARCHAR(4000)
DECLARE @LOG nvarchar(MAX)
DECLARE @lasterror int
DECLARE @mailprofile VARCHAR(100)
DECLARE @mail_rec VARCHAR(1000)
-- cursor vars
DECLARE @dbname VARCHAR(100)
DECLARE @dbid int
DECLARE @recModel int
 
SELECT @dbExt = N'.bak'
SELECT @logExt = N'.trn'
SELECT @backuplocation = N'F:\SQL Backup'
SELECT @DATE = CONVERT(VARCHAR(8), GETDATE(), 112)
 
SELECT @mailprofile = 'default'
SELECT @mail_rec = 'myemailAddress@mycompany.com'
SELECT @LOG = 'SQL Server Backup' + CAST(GETDATE() AS nvarchar(100))
 
-- cycle the log
EXEC sp_cycle_errorlog
 
CREATE TABLE #dbInfo
(
database_id int NOT NULL,
dbName nvarchar(100) NOT NULL,
recoveryModel int
)
 
SELECT TOP 1 @dbid = database_id FROM sys.databases WHERE [name]
   NOT LIKE 'DB_2EXCLUDE%' AND database_id > 4  ORDER BY database_id ASC;
SELECT @dbname = [name] FROM sys.databases WHERE database_id = @dbid;
SELECT @recModel = recovery_model FROM sys.databases WHERE database_id = @dbid;
 
-- begin backup process
WHILE @dbid > 0
BEGIN
 
   INSERT INTO #dbInfo
      (database_id, dbName, recoveryModel)
      VALUES
      (@dbid, @dbname, @recModel);
 
   IF @recModel = 1
   BEGIN
 
      SELECT @tempFile = @backuplocation + N'\transactionlog_backup_'
      + @dbname + @DATE + @logExt
      SELECT @LOG = @LOG + '
      Backing up [' + @dbname + '] transaction log file to: '  + @tempFile
      PRINT 'Backing up [' + @dbname + '] transaction log file to: '  + @tempFile
 
      CHECKPOINT
      BACKUP LOG @dbname TO DISK = @tempFile WITH NOFORMAT, NOINIT,
        NAME = N'Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
      SELECT @lasterror = @@ERROR
      IF @lasterror <> 0
      BEGIN
       -- log the error
       SELECT @LOG = @LOG + '-ERROR: Backing up database log for [' +
          @dbname + '] with error number ' +
          CAST(@lasterror AS nvarchar(25))
       PRINT 'ERROR: Backing up database log for [' + @dbname + '] with error number ' +
          CAST(@lasterror AS nvarchar(25))
      END
   END
 
   SELECT @tempFile = @backuplocation + N'\backup_' + @dbname + @DATE + @dbExt
   SELECT @LOG = @LOG + 'Backing up [' + @dbname + '] database file to: '  + @tempFile
   PRINT 'Backing up [' + @dbname + '] database file to: '  + @tempFile
 
   BACKUP DATABASE @dbName TO  DISK =  @tempFile WITH NOFORMAT, NOINIT,
      NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 
   SELECT @lasterror = @@ERROR
   IF @lasterror <> 0
   BEGIN
      -- log the error
      SELECT @LOG = @LOG + '-ERROR: Backing up database: ['
         + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
      PRINT 'ERROR: Backing up database: [' + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
   END
 
   DECLARE @SQL nvarchar(4000)
   DECLARE @logName nvarchar(100)
   DECLARE @param nvarchar(4000)
 
   -- get the transaction log name
   SELECT @LOG = @LOG + '
   Getting Log File Name [' + @dbname + ']'
   PRINT 'Getting Log File Name [' + @dbname + ']'
 
   SELECT @SQL = 'SELECT TOP 1 @out = name FROM ' + @dbname
      + '.sys.sysfiles where FileName like ''%.ldf'' '
   SELECT @param = N'@out varchar(100) OUTPUT'
   EXEC sp_executesql @SQL, @param, @OUT = @logName OUTPUT
 
   SELECT @LOG = @LOG + 'Log File Name Is ' + @logName
   PRINT 'Log File Name Is ' + @logName
 
   -- shrink the transaction log file
   SELECT @SQL = 'USE ' + @dbname
   SELECT @SQL = @SQL + ' DBCC SHRINKFILE (@dblogname, 0, TRUNCATEONLY);'
   SELECT @SQL = @SQL + ' SELECT @lasterr = @@ERROR;'
   SELECT @param = '@dblogname nvarchar(500), @lasterr int'
   --SELECT @tempPath = @dbname+'_log'
 
   SELECT @LOG = @LOG + '
   Performing Log File Shrink [' + @dbname + ']'
   PRINT 'Performing Log File Shrink [' + @dbname + ']'
 
   EXEC sp_executesql @SQL, @param, @dblogname = @logName, @lasterr = @lasterror
   -- error obtained in the stored procedure
   IF @lasterror <> 0
   BEGIN
      -- log the error
      SELECT @LOG = @LOG + '-ERROR: Shrinking the database log file: ['
         + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
      PRINT 'ERROR: Shrinking the database log file: ['
         + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
   END
 
   SELECT @LOG = @LOG + 'Log File Shrink Completed'
 
   -- get a new record
   SELECT @dbid = 0;
   SELECT TOP 1 @dbid =  database_id FROM sys.databases
      WHERE [name] NOT LIKE 'DB_2EXCLUDE%' AND database_id > 4 AND
      database_id NOT IN (SELECT DISTINCT x.database_id FROM #dbInfo x);
   SELECT @dbname = [name] FROM sys.databases WHERE database_id = @dbid;
   SELECT @recModel = recovery_model FROM sys.databases WHERE database_id = @dbid;
 
END
 
DROP TABLE #dbInfo
EXEC msdb.dbo.sp_send_dbmail @profile_name='default',
   @recipients=@mail_rec,@Body_Format = 'HTML', @BODY=@LOG;
PRINT @LOG

Technorati Tags: Configuration, DBCC Shinkfile, logfile, shink, SQL Server, sql server 2005

You may get the following error sometimes when trying to do something in SharePoint: Operation aborted (Exception from HRESULT: 0x80004004 (E_ABORT))

This is not actually a SharePoint issue but more a SQL issue...usually a result of the transaction log being full or no drive space available to it.

To view the log information use DBCC LOGINFO('[dbName]')

To fix the issue above, use the following code to reduce the size of the transaction log (replacing the items in square brackets with your variables - [dbName]):

Technorati Tags: Configuration, SharePoint, SQL Server, sql server 2005

Great Blog posthere on getting file size onfo from a SharePoint content db. Here's the T-SQL

Technorati Tags: SharePoint, SQL Server, sql server 2005

From November 2008:

Whew...its been a long week and its only Thursday. This years conference was much the same as last years with the exception of being held in various portions of the Mandalay Bay convention center as opposed to on one floor. This made for lots and lots and lots and lots of walking. But I'm still young (yes i am) so it makes for good excercise to walk off all the food they shovel in your mouth.

Most of the sessions I attended were really good with the exception of a couple. I wouldn't blame the speakers because these problems were mostly due to technical issues like the computers not working, the projectors broken or feedback in the presenters microphone sort of thing. All in all there were some great things presented this week.

Some of the sessions that really stuck out were:

Kimberly Tripp - Index Internals and Usage
This was a great session that talked about thins like SQL Server Statistics, Query Optimization, Types of indexes, diminishing returns on performance, etc. Of all the session I think this one stood out the most and because it is probably the most relevant for me right now being one of the "SQL Server DBA" for my company (Christie Digital). I say it that way because its a committee based DBA

Rick Strahl - Using WCF for JSON and REST Services with ASP.Net

Great session....last one of the conference for me so it really sticks out but essentially this covers the next gen replacement for ASMX or first gen webservices in the .NET Framework.

John Papa - Practical Strategies with the Entity Framework

For me this was an introduction to the Entity Framework that was released in VS 2008 SP1. John did a great job of quickly introducing the technology and getting on to the meat and potatoes of what we were there to discuss. There was a good discussion on how to use the IDE to build the data mappings and what happens after you make changes to the backend server, which coinceidently can be any data source...not just SQL as Linq to SQL supports.

Things I would change...(and only because its my blog and I can say whatever I want!!!)

...the time between sessions was ridiculous...one hour or in some cases and hour and a half is just way too long. They could have added an extra session or two to the day. I realize they want you in the expo hall but they could have extended the day to facilitate that or leave it open all day and then some people may skip a couple of sessions here and there to go and see what the vendors are offering. this was my gripe last year and its my gripe this year. Its probably going to be the thing that keeps me from going to this in the future.

...provide video or podcasts of the sessions so we can take in the whole conference...even the sessions we didn't get to go to. I see there point about this being intellectual property and the speakers are consultants and this is their livelyhood but I mean if they are willing to teach this at a conference...and we pay to see it...shouldn't we be entitled to review this information after the fact? Maybe they will supply it on their site (i heard they were recording the sessions this year).

...ease up on the food. Damn, there was too much...I ate too much...I felt stuffed the whole time...I guess this is really my fault! Okay dont change that

Technorati Tags: .net, ASP.NET, conference, Development, SharePoint, SQL Server, sql server 2005

Kudos to Scott Elliott a colleague of mine for putting this together...

Here is how I got my SQL Express to remotely respond to SQL Management Studio.

In SQL Server Configuration Manager:
Under SQL Server 2005 Network Configuration:
Protocols for

• Enable Shared Memory
• Enable Name Pipes
• Enable TCP/IP

Under SQL Server 2005 Servers

• SQL Server (ServerInstance) Properties

Log on as: Local System (have to restart)
SQL Server Browser

• Log on as: Local System (may have to enable this service first in the Services MMC applet)

And with that, you should be able to remotely connect! Hazzah!

Technorati Tags: Security, SharePoint, SQL Server, sql server 2005

I recently came across this problem on my reporting services server when a user attempts to save a report from a model to their my reports folder

The permissions granted to user X are insufficient for performing this operation. ---> The permissions granted to user 'X' are insufficient for performing this operation.
----------------------------
Exception of type 'Microsoft.ReportingServices.RsProxy.AccessDeniedException' was thrown.

It seems that this problem is due to SQL server 2005 SP2 being applied because it worked fine until this was installed. The work around as far as I can tell was originally posted here and I used this solution to fix the problem for myself.

Basically it boils down to the default save location has changed to the Models folder and the user has no rights on this folder. By granting them read only rights they are then able to navigate to their own folder to save the report.

Technorati Tags: Reporting Services, SQL Server, sql server 2005