Roman Rehak

Synonyms and SQL Server Express

2008-05-03T03:37:00Z

I've been a big fan of using SQL Server aliases for a long time because it allows you to make physical location of SQL Servers transparent to the client machines. With SQL Server 2005 Microsoft introduced synonyms, allowing you to define logical names for objects in another database or even on another server. This could be among other things beneficial if you need to move some tables to another database. Instead of recoding your application, you can define synonyms and point them to the new location (I wrote an article for SearchSQLServer.com about synonyms recently, you can get more details there if interested) This week I realized that synonyms can have another great benefit. As you know, SQL Server Express has a limit of 4GB per database. If your database begins to grow close to 4GB, you can move one or more large tables to another database on the same server, create synonyms in the original database and point them to the new location. I tested it on my SQL Server Express instance and it does work as expected. So with this knowledge, this limitation might become less of an obstacle for you to consider SQL Server Express.

Granting EXECUTE on all stored procedures

2008-01-16T22:39:00Z

I came across this a few months ago when I was researching security in 2005 and now thought it would be useful to post it here. I have saved code that loops through all stored procedures in the database and grants execute privileges to a database user. I've had it around since SQL Server 7.0. Now I discovered that in SQL Server 2005 you can create a server role and grant it EXECUTE privileges on all stored procedures. This solution is much more elegant, plus I don't have to rerun my script if I add more procs:

-- create custom database role
CREATE ROLE db_executor

-- grant EXECUTE permission

GRANT EXECUTE TO db_executor

-- add security account to the role

exec sp_addrolemember 'db_executor', 'YourSecurityAccount'

Note - security account can be a database user, database role, a Windows login or Windows group.

DevTeach/SQLTeach follow-up

2007-12-02T07:50:00Z

I just got back from Vancouver where I was attending/presenting at the SqlTeach conference. There were other speakers from sqlblog as well - Adam Machanic, Paul Nielsen and Peter DeBetta. This conference is a venture put on by Jean-Rene Roy and his wife Maryse from Montreal, it started as DevTeach in 2003 and this year the SQL Server track was marketed as a separate conference. This is the largest Microsoft conference in Canada and 2007 started a new 18 month rotation between Montreal, Vancouver and Toronto. Jean-Rene and Maryse did a great job as always, which is even more admirable and appreciated considering they just moved from Montreal to Ottawa. The next event will take place in Toronto, from May 12th to May 16th.

I resurrected my SQL Server Tips and Tricks talk that I used to do in 2003/2004 and updated it with 2005 content. Just like before, attendees seemed to like my utility ScripExec for executing script files with some logging and error handling capabilities. I will add a couple of features I've been thinking about and release it through SqlBlog as a freeware. I will also look into the possibility of putting it on CodePlex and see if we can get other coders interested in enhancing it.

How to rethrow errors in T-SQL

2007-12-02T01:40:00Z

If you do application development and you are used to rethrowing errors in a catch block, you may have noticed that error handling in T-SQL still doesn't support this functionality. However, you can easily emulate this functionality by rolling out your own "rethrow" stored procedure and using RAISERROR to throw the error back to the client. BOL contains a good example of that, there is a stored procedure called usp_RethrowError. It uses the built-in error functions and RAISERROR to create a new error with the same message text, number and other details.

Here is the code:

CREATE PROCEDURE [dbo].[usp_RethrowError]
AS -- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN ;

DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200) ;

    -- Assign variables to error-handling functions that
    -- capture information for RAISERROR.
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;

    -- Building the message string that will contain original
    -- error information.
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
        'Message: ' + ERROR_MESSAGE() ;

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine-- parameter: original error line number.
        ) ;
GO

Sample code showing how to use it:

DECLARE @Zero INT
SET @Zero = 0

BEGIN TRY
SELECT 5 / @Zero
END TRY
BEGIN CATCH
PRINT 'We have an error...'
EXEC usp_RethrowError
END CATCH

DevTeach/SqlTeach 2007 followup

2007-05-22T04:17:00Z

As Peter DeBetta already mentioned in his post, a few of SqlBlog-ers including myself were at the DevTeach/SqlTeach conference in Montreal. It was great to catch up with everybody and as always, there wasn't enough time to hang out with the fellow speakers, attend all the good sessions and enjoy the Montreal nightlife. Peter and I did a duet session called SQL Server 2005 Worst Practices. It was really a great talk and I want to thank Peter again for letting me join him in this session, originally developed by him and Richard Hundhausen.

As the Tech Chair for SqlTeach, I was really pleased with the quality of the sessions, feedback from the attendees but especially much higher attendance than in the previous years. It's great to see that SQL Server is gaining momentum in Canada and we had quite a few FoxPro heads wanting to learn more about SQL Server while making the move to a new platform. I am sure SQL Server won't dissapoint them :-). And as always, Jean-Rene Roy and his wife Maryse (the husband-wife team behind the conference) did a superb job as organizers and hosts. Why Jean-Rene is still not an MVP after all the work he has done for the Canadian developer and DBA community is beyond me. BTW, Next DevTeach/SqlTeach will be taking place in Vancouver this November.

Also - thanks to my friend Tom Cooley and Martin Lapierre you can find a detailed report about DevTeach on the Universal Thread Coverage website. The coverage includes Peter's and my session.

'ASP.NET session has expired' error in the ReportViewer control

2007-05-04T15:20:00Z

I deployed an ASP.NET application to another server and the page that includes a few ReportViewer controls started showing the "ASP.NET session has expired" error in each report. The same web app worked totally fine on my box. I didn’t have time to investigate before I moved on to other work and then a few days later I came across a solution to the problem by accident when reading a KB article related to RS security on WebHost4Life.com. They suggested changing the AsyncRendering property to false. Since there are some performance penalties when turning off asynchronous rendering, I decided to make it a setting in Web.config and turn it off only if needed:

bool asyncRendering = bool.Parse(ConfigurationManager.AppSettings.Get("AsyncRendering"));
rptvAverageOrder.ServerReport.ReportServerUrl = rsUrl;

After I tried that, the error went away but I ended up with another issue. Reports with images (in my case charts) ended up having a missing image with that ugly placeholder with an X in it. I did some googling and found out that this can happen if asynchronous rendering in ReportViewer is off and the server name contains an underscore as it does in my case so these two issues are related. When I use the IP address or "localhost" in the URL, everything works fine regardless of how this property is set. Also, some people pointed out is that if you turn off asynchronous rendering, reports get messed up when viewed with Firefox. I will report this as an issue on Microsoft Connect and I guess in the meantime we have to avoid deploying ReportViewer applications on servers with underscores in their names.

Why use "Included" columns in SQL Server 2005

2007-05-03T15:15:00Z

Recently I was working on an application where I got a lot of mileage out of using a new feature of SQL Server 2005 called included columns. When you add a column as an “included” column, it gets stored at the leaf level of the index and it is not part of the index key. This only works for non-clustered indexes.

The main advantages of included columns are

1) You can exceed the 900 byte limitation of the index key, any columns that would push you over that limit could instead be added as included rather than key columns

2) You can include datatypes that are not allowed as key columns, such as varchar(max), nvarchar(max) or XML. Note – you still cannot use the old datatypes such as text or ntext.

3) You can greatly expand you options for creating covering index.

4) You can reduce the size of the index to make it more efficient by including only lookup columns in the key, and then adding other query covering columns as included columns

As always, you should use some judgment. If you include four varchar(max) columns and they contain a lot of data, the size of your index will be huge. Also, since each column will be maintained in the table and in the index, updating large columns will slow down the application somewhat. Below is a sample CREATE INDEX script that indexes the Title column and includes a varchar(max) column called Content:

CREATE NONCLUSTERED INDEX [IX_MyTable] ON [dbo].[MyTable]
(

[Title] ASC

)
INCLUDE ( [Content])

Problems when removing a filegroup

2007-04-26T20:19:00Z

This week we ran into a weird problem. We were not able to remove an empty filegroup, SQL Server was throwing the 5042 error “The filegroup 'FG18' cannot be removed because it is not empty”. There were definitely no files in that filegroup so the whole thing appeared to be more like a bug. As it turns out, this can happen if a table that was using the filegroup had statistics defined on that filegroup. You can tell if you have any dangling statistics for that filegroup if you run this query and replace X with your filegroup ID:

select object_name(id) AS TableName, * from dbo.sysindexes where groupid = X

Once you know the table name, you can run DROP STATISTICS and hopefully after that you should be able to remove the filegroup.

Change in trace flags behavior in SQL Server 2005

2007-04-24T18:03:00Z

The other day I was setting up trace flags when chasing a deadlock issue. The way it works is that after you run DBCC TRACEON, SQL Server outputs detailed log to the SQL Server event log. But for some reason this trace was just not showing. The only thing I could see in the event log was a message that DBCC TRACEON was executed, but nothing after that. I used Google and read some articles but still didn't find what the problem was. I went back to BOL and I finally noticed the reason why:

"In SQL Server 2000, a simple DBCC TRACEON (1204) is enough to enable deadlock reporting to the error log. In SQL Server 2005, you must enable the flag globally because the session-level flag is not visible to the deadlock monitor thread."

So as it turns out, it's a change in behavior from the previous version. Here is how you enable the trace globally:

DBCC TRACEON (1204, -1);