SQL Server
SQL Server
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.
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.
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.
My latest Reporting Services article called Reporting for Duty is in the latest issue of SQL Server Magazine. It should be pretty useful for you if you need to call the SOAP API to generate reports from custom applications.
I came across this ridiculuos (in my opinion) EWeek article today. The author's opinion is that "Database administrators have little incentive to implement Microsoft's long-awaited update". Looks to me like Charles didn't do his homework about all the new features and the excitement over this release in the SQL Server world before writing the opinion. I also don't think he is right about the timeframe for major adoption.
AAinTX wrote a pretty good comment and I will echo what he's saying - "You have got to be kidding"
When I was installing SQL Server 2005 last weekend, one of the first things I wanted to find out was whether I could run SSRS 2000 and SSRS 2005 on the same computer. I seriously doubted it was possible knowing that RS installs a windows service and creates a couple of ASP.NET sites. But after a lot of digging in BOL, I found that you can indeed run both version on the same computer under certain conditions - you have to run Windows Server 2003, SQL Server 2000 has to be the default instance and SQL Server 2005 has to be a named instance. This procedure is from BOL:
-
Verify that SQL Server 2000 Reporting Services is running as the default instance.
-
Run Setup for SQL Server 2005. When you use the SQL Server Installation Wizard, specify a unique instance name on the Instance Name page. When you run Setup from the command prompt, specify a unique instance name by using the instancename option.
-
Choose a files-only installation for the report server. Use the install but do not configure option in the Report Server Installation Options page in the SQL Server Installation Wizard or RSCONFIGURATION=filesonly in a command line installation.
-
Run the Reporting Services Configuration tool to deploy the new SQL Server 2005 instance. Choose a unique virtual directory for the report server and Report Manager. The names cannot be identical to those used in the SQL Server 2000 instance.
The path to the topic in BOL is "ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4ae50570-7c84-4404-8f4f-f76b646f8518.htm".
I ran into a couple of issues. I was not for whatever reason able to configure a RS database. I tried creating it many times and the configuration tool was showing that it was not able to connect to the report server database. Eventually I got it working after uninstalling and reinstalling SSRS 2005. The other issue was that when I ran rsconfig manually (hoping I would be able to create connection settings this way), the utility was modifying the config file for my SSRS 2000 install. So keep in mind that if you decide to run rsconfig to configure SSRS 2005, you have to refernce the one in the "C:\Program Files\Microsoft SQL Server\90\Tools\Binn" folder because the DOS PATH will likely use the 2000 version since it appears in the PATH before the new one. The chance is that you may never have to touch it since the RS config tool can do the full setup, but it's good to know.
Before you start instaling SSRS 2005, you should backup up both RS 2000 databases, make a copy of the key and the RSReportServer.config file just in case your SSRS 2000 instance gets messed up.
A few months ago I wrote a tip showing you how you can make sure that a table doesn't get dropped by accident. At the time it generated a lot of feedback and comments in other blogs. The solution involved creating a dummy view using the "WITH SCHEMABINDING" clause. That solution is workable but it is somewhat kludgy, even though you can automate it if you wish so..
Today I want to show you how you can do the same thing in SQL Server 2005 much more elegantly using a DDL trigger. These triggers are new to SQL Server 2005, they allow you to capture almost 90 DDL events either at the database level or at the server level. In this particular case all you need to do is to create a database level DDL trigger, specify the "DROP_TABLE" event and then use XQuery to extract the table name from the EVENTDATA:
CREATE TRIGGER NoTableDrop
ON DATABASE
FOR DROP_TABLE
AS
DECLARE @data XML
SET @data = EVENTDATA()
-- check table name
IF @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') = N'MyPreciousTable'
BEGIN
PRINT 'You can''t drop this table!!!'
ROLLBACK
END
IIS 6.0 allows you to create application pools, then each application pool can be configured to run under a separate identity. When my company deploys web sites, our practice is to create an account for each web application and then create a new application pool to run under that account. This approach however doesn't work with Reporting Services, you might end up getting weird errors like:
"The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database..."
"Key not valid for use in specified state."
This happened to us with our first RS deployment. After many hours of frustration we opened a support call with Microsoft, then it still took some time to finally diagnose the problem. The bottom line is that RS in IIS 6.0 needs to run under the predefined Network Service account, you cannot use a configured account.
First I'll start with a warning - avoid dynamic SQL as much as you can and always try to use sp_executeSQL instead of EXEC() if possible.
When I look at SQL code written by other developers, I often notice that many of them don't realize that you are not limited by the 8K limit when using a varchar to execute dynamic SQL with EXEC(); You can actually construct your dynamic SQL using multiple varchar variables and then execute them as an expression. The following code allows you to execute 16K of SQL code:
EXEC(@sql1 + @sql2)
You cannot do the same thing with sp_executeSQL directly, but you can nest the call to sp_executeSQL inside of EXEC() and convert the final string to Unicode:
EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + '''')
If you try to connect to a SQL Server in another domain in the forest using Windows Authentication, you might end up getting this error:
A Connection could not be established to YourServer.
Reason: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection...
This is a common problem on some networks, basically TCP/IP is not passing windows credentials along and a trust relationship is not established across domains. This is a complex issue, you can get more details in this KB article.
But, you still might be able to connect with the Named Pipes protocol instead of TCP/IP, that usually does the trick for me. You can create an alias with SQL Server Client Network Utility and select Named Pipes as the protocol and then use the alias in your connection strings or when registering the server in Enterprise Manager. Another option for enforcing NP is to include “Network Library=dbnmpntw;“ in the connection string.
The Database Maintenance Plan Wizard doesn't give you an option to configure job status notifications for the maintenance jobs it creates. You can configure these jobs to send notifications, but you have to do it after you ran the wizard. Open the job properties and configure the Notifications section just like you would for any other types of jobs. For example, you can look up the backup job created by the wizard and then configure it to send you an email if a database backup fails.
Any modifications made on the Notifications tab will remain in place even after you subsequently modify the maintenance plan. When you modify an existing maintenance plan, it only modifies the Schedules and the Steps sections of the scheduled job so you don't have to worry about your notifications getting reset. But if you ever delete and recreate a maintenance plan, you have to go back and modify notifications again.
SQL Server uses the tempdb system database to store both temporary tables and worktables holding intermediate results from sorting and many other complex data retrieval operations. In addition, it's also heavily used during certain DBCC operations. Depending on the server workload, the tempdb database can grow to a few gigabytes in size. Everytime SQL Server is restarted, the tempdb is recreated from scratch and it will start growing again whenever more space is needed. By default, the tempdb only starts at few MB in size and then it needs to autogrow anytime more space is needed. Since frequent autogrow can negatively affect performance and in some cases it results in errors if it cannot be done fast enough, it is considered a good practice to presize the tempdb database to the expected maximum size it will ever reach. Once you change the default size, SQL Server will use the new size settings when rebuilding tempdb during startup. One of the guidelines I recall reading is to set tempdb to 25% of the largest database.
You can get more details plus additional tempdb tips from the SQL-Server-Performance.com website.
I had to do this recently in one of my utility stored procedures so I thought it would make a good tip. If you need to programatically determine whether the table has an identity column, you can use the OBJECTPROPERTY function and ask for the value of the 'TableHasIdentity' property:
SELECT OBJECTPROPERTY(object_id('MyTable'), 'TableHasIdentity')
SQL Server returns 1 if the table has an identity column, 0 if it doesn't.
A lot of other bloggers already mentioned the DevTeach 2005 conference in Montreal . This is the third year for DevTeach, my second year as the Tech Chair for the SQL Server track. The SQL Server track is really strong this year. We will have several SqlJunkies bloggers presenting - Adam Machanic, Kirk Haselden, Don Kiely, Rick Heiges, Robert Hurlbut and John Kane. Then we have Bill Vaughn, Peter DeBetta, Randy Dyess, Sylvain Duford and last but not least, my two great friends from the PASS community, Kevin Kline and Rushabh Mehta.
You can still register for only $766 US until April 30th. There is also a discount for user group members, just contact your local UG leader to get the discount code. This is a great deal, even with the Canadian dollar being so high. Montreal is a great place to visit, especially in the summer. The conference runs from Saturday till Tuesday, so you don't have to miss the whole week of work to attend this conference.
In a few weeks I'll start organizing a "SQL Junkies get together" during DevTeach. Since we all are nice guys, we'll probably let other SQL geeks join us. Also, if you're interested in doing some sightseeing on Saturday (with heavy focus on things epicurious), please let me know.