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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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 Otttawa. 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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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])
Cross-posted from SQLBlog! -
http://www.sqlblog.com
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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com
I was at the MVP Summit in Seattle last week. It was my first summit and just like I expected, I ended up having great time. Aside from reconnecting with many friends, I made a few more. As always, the biggest challenge was getting up at 6:30 to make the first session after hanging out late into the night. It was kinda cool seeing Bill Gates live, even though he is a bit dry as a speaker. I like the guy, that’s why I am willing to forgive him that he mentioned MS Access and not SQL Server as his favorite product. On the other hand, I was quite happy to hear that many SQL heads “like” SharePoint just as much as I do.
But then the nightmare began for me. I was supposed to fly home on Thursday night. In the afternoon Delta left me a voicemail and told me that my flight was cancelled and that I was rescheduled for Saturday. After being on hold for an hour my cell phone died so I decided to go to the airport, and that’s where I found out about airport closures on East Coast. I ended up getting a cheap hotel near the airport for two nights where for a lack of other things to do, I caught up on sleep. This actually turned out to be a good (and much needed) thing for me later on. On Saturday I made it to Atlanta and when I was about to board my plane to Burlington, they told me that the plane was too heavy to board anymore passengers. The most frustrating thing was when they told me I was bumped off on first come, first served basis so remember this lesson – board your plane as early as you can. After waiting at the ticket counter for 2 hours, I was told that the next available flight to Burlington was on Thursday the 22nd. They offered other options, like flying to Montreal in 4 days, or flying to Detroit(!) on Wednesday. It seemed that all I could do was to show up at the airport every day and hope I would get on a flight with a standby ticket. The most annoying thing was that Delta wasn’t even willing to pay for a hotel, even though it was obvious that they overloaded the plane with luggage.
So at that point after adding up the cost of 4 more unpaid days plus a hotel, I decided to rent a car and drive home. I booked a car from Hertz for $430, they made me do a two day minimum one way rental since it’s such a distance. I waited in another line for 30 minutes trying to get my luggage, only to be told that I would have to wait at least 3 hours if I was lucky. So I finally left the airport at midnight and I can say, it was very liberating to be finally in charge of my destiny. The drive went really well, thank God for that extra sleep in Seattle because I was able to drive without any breaks, aside from getting gas and junk food. 19 hours later at 7PM on Sunday I was finally home, only to be greeted by 3 feet of snow in my driveway. The girls were very happy to have me back…
Since I recently switched jobs, I pretty much had to finance this trip myself and take unpaid time off work. This travel nightmare added about $1,000 to the total cost, things like the hotel in Seattle, cabs, car rental, gas from Atlanta to Vermont and all the tolls along the way. Would I still have gone to the MVP Summit if I had known this was gonna happen? You bet!
I've been receiving the Simple-Talk newsletter since July but having been very busy, I never took the time to check it out. I noticed that Simple-Talk.com was a site owned by Red Gate Software, database and developer tools company from Cambridge, England. A few weeks ago something cought my attention in the newsletter and I finally went to the website. I have to admit that I tend to be sceptical about resources and websites backed by a vendor (although some are pretty good), but Simple-Talk turned out to be a big surprise in a positive way. The site positions itself as a technical journal and it has been run for a few months by Tony Davis, former editor at Wrox. I was pleased to find quite a few useful articles about SQL Server, all of them of very high quality. As a consultant, I also appreciated a few pieces in the Opinion section, like the article titled Contract Coding: Ensuring your client pays up. In about an hour I read many articles and bookmarked many others for reference or later reading. I haven't looked at any .NET stuff yet but I found a lot of pieces in the Opinion section to be a fascinating read, especially in the Database Geek of the Week section,
I highly recommend to all SQL geeks to subscribe to the newsletter and include the site in your list of resources.
when while writing an email you hit Caps Lock by mistake, write a very very long sentence without noticing and then to fix it you fire up Query Analyzer, paste the text there and hit Ctrl+A, Ctrl+Shift+L to get the lowercase back. Much faster than retyping it ;-)
My friends in Montreal just confirmed that I will be presenting my new “Native XML Web Services in SQL Server 2005” session twice in October. First at the Montreal Code Camp held on Saturday October 15th (only two English speakers so far) and then a few days later at the Montreal SQL Server User Group meeting on October 23rd. This will be a great warm-up for the ultimate SQL Server conference, PASS Community Summit in Seattle in November.
I spent the last two evenings reading one of the latest SQL Server 2005 books - Programming Microsoft SQL Server 2005. The book was written by Andrew Brust and one of my closest friends in the industry, one and only Stephen Forte (if you know Stephen, you know what I am talking about). The book is really very good and I highly recommend getting it. It has great coverage of major 2005 features, especially the new stuff and useful real-life scenario code samples. I will be using the Business Intelligence part of the book a lot for my own learning since this is the area I haven't worked much with, but we see an increasing interest and demand from our customers.
Please note that I may be a little biased with my recommendation since I was one the technical reviewers for the book. While I really have no time, patience, discipline or desire to write a book, I did enjoy providing feedback and it was nice to see that some of my suggestions and code samples made it into the book. If any of you are working on a SQL book and need a tech reviewer, feel free to get in touch with me and I'll be glad to help out.
I just received an email from Microsoft that made my weekend – I was awarded an MVP title for SQL Server. It feels really good to join this great group of SQL Server professionals, especially after a few not so successful nominations.
I want to thank Microsoft for considering me worth the title, also all the people that nominated me for the award, and last but not the least - my company Competitive Computing for providing me with great opportunities to grow and putting up with my travels to SQL Server conferences and other related events.
I just wanted to remind any potential speakers that the PASS Call for Speakers is still open for two more days. This will be my fourth year on the program committee, selecting sessions for the Database & Application Development track. I wanted to write a few pointers to help you improve your chances of getting selected based on my experience with session selections but Bill Graziano already wrote something similar in his blog. I would just add one more thing – submit more than one abstract and if possible, your session proposals should cover different areas of SQL Server.
My tip from last week about deleting all data in the database easily seems to have generated a lot of interest and a good discussion about using DELETE vs. TRUNCATE TABLE. Obviously the truncate command is more efficient but you get an error if you try to run it on a table that's a parent to another table. So I started thinking that maybe there is an easy way to figure out if a table is a parent. In the end, my intuition about the SERVERPROPERTY proved correct - if you pass in 'TableHasForeignRef' as a parameter, SERVERPROPERTY returns 1 if the table has any foreign key references.
So here is is the new version of the script, it uses TRUNCATE TABLE on stand-alone and child tables, or DELETE otherwise:
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
This tip comes from my latest project. If you run into a scenario where you need to delete all of the data in your database, you can do it easily with just two lines of code using the MSForEachTable stored procedure. The trick here is to first disable referential integrity checks so you can delete data from parent tables. If you have a lot of data, you may want to rewrite the delete part and turn it into a batch delete, in my experience 100,000 rows is a reasonable chunk to delete in one shot. Here is the code:
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Last week Laura Blood and myself did a "SQL Server 2005 Launch" presentation at a joing meeting of Vermont DotNet User Group and Vermont SQL Server User Group. The event went great, it seems we have a lot of interest in SQL Server 2005 as Julia Lerman pointed out based on the attendance count. We did a combination of supplied slides, custom slides and a bunch of demos to demonstrate the coolest features. Our main focus was development but it seems that a lot of people got super excited over the potential provided by SQL Server Integration Services.
The slides are available on the VtSql website. I will be including a zip with some of the demos shortly, I just have to script some data inserts, write instructions and package it all together.
My company, Competitive Computing, has several job openings in our Vermont office. You can get more details at JobsInVT.com. Please contact me for more details if you decide to apply.
The call for speakers for the fourth DevTeach conference in Montreal is still open. I am the tech chair for the SQL Server and Server Side track. If you would like to speak in my track and have any interesting SQL Server, BizTalk, SharePoint or other server-based product, please contact me via my blog for more details about the requirements and compensation. The main focus of this track is SQL Server 2005 but since the next DevTeach will not have the FoxPro track, there is now room to show off some other technologies.
You can get more details about the submission process at this link on the DevTeach site.