Roman's Weekly SQL Server Tip
Practical SQL Server tips.
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
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.
If you work with Reporting Services, you probably noticed how long it takes to start Report Manager after a period of inactivity. One of the reasons for this is that it actually needs to initialize and load two applications (RM is an ASP.NET web application and the backend Report Server is a web service). You can keep the application loaded in Windows 2003 Server if you open the application pool seettings and uncheck “Shutdown worker process after being idle for...”. After you do this, any web application that uses the application pool will remain loaded.
When you code or troubleshoot database applications, it's very likely is that you often need to look at the structure of your SQL Server tables - what columns there are, column names, datatypes, constraints, relationships to other tables, etc. If you do performance tuning, you probably also need to examine indexes and unique constraint. You can get all this stuff from Enterprise Manager or Query Analyzer. If you're more savvy, you can run a few system stored procedures or queries against INFORMATION_SCHEMA objects. And while I consider EM to be a great tool, I've always thought that I had to do too much drilling to get to the parts of the table I wanted to look at, especially if I want to compare tables in two or more databases. And there still doesn't seem to be an easy way to examine table relationships - I don't want to create a dummy diagram just to see the parent and the child tables.
So eventually I wrote a simple, but pretty handy tool called TableInfo. My design goal was to have a utility that would let me get to the most useful table metadata within 5 seconds and provide a comprehensive view of columns, relationships, indexes, constraints and table security. TableInfo prompts you for server name, database name, user name and password, you can also choose Windows authentication. Tables are displayed without the owner prefix, but you can filter by table owner. This way you can still use keyboard shortcuts to scroll down to tables that start with a "P" for example.
I also built in a few command line parameters. You can pass in server, database and login credentials in the following format:
TableInfo ServerName, DatabaseName, UserName, Password
This functionality allows you to register TableInfo as an external tool in both Enterprise Manager and Query Analyzer and configure them to send the server name and the database name as command line arguments. So when you're working with one of these tools and want to use TableInfo, you can launch it and open in the same database you're working in. Query Analyzer can also send the user name, but not the password. It used to until SP3, but no more. Maybe Don Kiely got on their case?
You can download TableInfo from this location. It's a .NET WinForm executable so there is no install, just drop the files to a folder. The zip file also contains a document that shows in more detail how to register TableInfo with EM and QA.
Please let me know about any bugs you find and I would also love to get some feedback or feature suggestions.
This is actually an ADO/ADO.NET tip and it applies to other database systems as well.
I can code basic connection strings by heart, but anytime I need to do something fancy or access something other than SQL Server, I use ConnectionStrings.com as a resource. It pretty much covers every combination of a database system and a data access API under the sun. There are some databases listed I've never even heard of, like Mimer SQL. The SQL Server section also includes some additional stuff, like specifying the network protocol and all connection strings properties.
The best thing is that the URL is so intuitive, you don't even need to bookmark it.
Did you know that you can download a clickable map of SQL Server 2000 system tables from the SQL Server website? This map is also included with the resource kit but if don't have it, you can download it from this page. It's very useful if you need to do any work with system tables. The top level is displayed as a diagram with all relationships, then you can drill down to individual tables and look at column details.
Last week I talked about setting up your own certificates on RS servers. Today I'll cover a few issues around using SSL with the Report Server web service and the Report Manager application.
There are two ways to enforce SSL requirement with Reporting Services. One option is to configure the Reports web application and the ReportServer web service in IIS Manager console to require SSL. The other option is to modify the value of the SecureConnectionLevel setting in the Report Server configuration file (RSReportServer.config). The SecureConnectionLevel can have 4 different levels - 0, 1, 2 and 3. Depending on the level you choose, SSL will be required for none, some, most or all calls to the RS. The level 3 is the equivalent of using the site properties in IIS Manager to require SSL, it basically enforces SSL for all method calls. Level 0 means no SSL will be required.
The initial RTM release of RS has a few issues that make using SSL with RS complicated. The levels 1 and 2 require only certain methods to use SSL, but the Report Manager app doesn't always get those methods right and it renders some links with http instead of https. If you click on one of those links, you'll get an error telling you that SSL needs to be used. The level 3 works the best - all application links are rendered as https, with the exception of Help. The help link still renders with http instead of https, and you get the same error when you open the link.
My personal opinion is that there is very little reason to use levels 1 or 2, why not just go all the way and make everything secure. You can follow these steps in the RTM version to set up Report Manager so that all links render with https and Help still works:
- In IIS Manager, configure the ReportServer web service to require SSL. Instead of this step you can set the SecureConnectionLevel to 3 in the config file, but I prefer enforcing it in IIS.
- Do the same in the Reports web application setting (Report Manager). After this change all links in RM will be rendered with https, except for Help. We will fix that in the next step
- Expand the Reports site in IIS Manager, open the properties of the EN subfolder and uncheck the SSL setting. The EN folder contains the English help files so deselecting SSL on that folder will allow you to view help without SSL. You will need to modify other folders if you use help other than English.
It appears that all of these issues have been addressed in SP1. I tested all levels without getting any errors. Levels 1 and 2 seem to correctly render links with https or just http as needed. Level 3 renders everything with https. If you manually change the link to http, Report Manager redirects the call to https. You now have two options for fully enforcing SSL - you can either follow the previous steps and configure the folders in IIS, or you can just set SecureConnectionLevel to 3 in the config file.
If your Reporting Services is available over the Internet, you shouldn't even consider putting it out without requiring SSL, otherwise your username and password will be exposed without encryption. That's often easier said than done, especially because a certificate authenticated by a trusted authority is expensive and most companies don't want to purchase a certificate for every development/staging/QA server in their environment. While you can generate your own certificates with a server that has the Certificate Authority services installed, such certificates are not considered fully trusted because they haven't being issued by a trust authority going all the way to “the root”. You might recall that if a browser detects a certificate that's not totally kosher (it could be expired, issued to a different computer or issued by a non-trusted authority), it pops up a dialog and gives you the option to use or not use the certificate.
If you use such certificate with Report Server, it's likely that you will get this error:
The underlying connection was closed: Could not establish trust relationship with remote server.
This error is common if a client application calls a web service via SSL and the certificate is not fully trusted. Even though you generated a certificate on the same network, the full trust is missing because your certificate server is not trusted by a root authority. If you're using a custom client application, you can use the workaround described in the Q823177 article and implement the ICertificatePolicy such that the CheckvalidationResult method always validates the certificate. This workaround doesn't help you though if you're using Report Manager because you can't really modify the source code in the application. But luckily there is another workaround that I discovered after many hours of trial and error - Reporting Services will use a certificate if it was generated on the SAME server where the IIS part of RS is running. So if you run into this problem, install the Certificate Authority component on the IIS server and use a certificate generated by that service.
Next week I'll discuss additional issues you may run into when using SSL with Reporting Services.
Column and table descriptions are stored as extended properties, you can read more about extended properties in BOL. Microsoft uses a convention of naming them MS_Description, they are the ones that show in Enterprise Manager. You can declare your own extended properties and use them for data validation or other things.
You can create extended properties with the sp_addextendedproperty stored procedure and you can retrieve them by calling the fn_listextendedproperty() function. Here is how you get all column descriptions for the Orders table:
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', 'column', null)
More examples:
USE NORTHWIND
GO
-- add table comment
DECLARE @v sql_variant
SET @v = N'This table holds orders'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo',
N'table', N'Orders', NULL, NULL
GO
-- add column comment
DECLARE @v sql_variant
SET @v = N'OrderID is auto-generated'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo',
N'table', N'Orders', N'column', N'OrderID'
GO
-- get table description
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', null, null)
-- get OrderID column description
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', 'column', 'OrderID')
-- get descriptions for all columns in the table
SELECT * FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo',
'table', 'Orders', 'column', null)
-- get all table properties
SELECT * FROM ::fn_listextendedproperty(null, 'user', 'dbo',
'table', 'Orders', null, null)
-- get all column properties
SELECT * FROM ::fn_listextendedproperty(null, 'user', 'dbo',
'table', 'Orders', 'column', null)
In this tip I may be stating the obvious, but in the last few years and recently again I've seen a few SQL Server installations where someone was trying to save space and in doing so they enabled Windows compression on the folders where the data and log files were residing. This was usually done by system or network administrators that were not so familiar with the SQL Server side.
This setup is a BAD idea and it results in queries and updates taking a few times longer than they would if the files were not compressed. Believe it or not, this issue often goes unnoticed for a long time. If you administer or examine SQL Server for your customers and clients like I do, I recommend you add on your checklist to examine all folders used by SQL Server and make sure compression is not used.
If you have multiple instances of SQL Server running on the same computer, you might run into run into a situation where you need to identify what server instance corresponds to the SQL Server executable. Windows Task Manager shows you the process ID (PID) of each instance of sqlserver.exe. You can find out the process ID of each running SQL Server by executing this code:
SELECT SERVERPROPERTY('ProcessID')
Occasionally you might have a scenario where you need to select only x leftmost number of characters from a column. We do this often when the screen real estate is limited or when showing data in a grid. But when you don't want to display the whole field, you can't just blindly select whatever number of characters you need using the LEFT() function. Well, you could, but not without potentially suffering from a side effect when certain words start taking on a different meaning after being truncated. Just think SELECT LEFT('... professional association ...', X) . Something like this actually happened on one of our sites, although it wasn't as bad as my example.
So the smart thing to do is to make sure the result never gets truncated in the middle of the word. To do that, you first need to get the position of the rightmost space character, then select everything up to that point and maybe append “...” to show that there is more text. Unfortunatelly T-SQL doesn't have something like LastIndexOf, you can only get the index of the first character using the CHARINDEX function. So the trick here is to use the REVERSE function first, get the index of the first space and subtract this number from the number of characters you need. Here is a sample query for selecting 100 leftmost characters and appending “...” if the Description field is longer than that:
SELECT CASE
WHEN LEN(Description) <= 100 THEN Description
ELSE LEFT(Description, 100 - CHARINDEX(' ',
REVERSE(LEFT(Description, 100)))) + '…'
END AS ShortDescription FROM MyTable