Hello all,

Microsoft released the Service Pack 4 for SQL Server 2005 to RTM on 12/17/2010 kind of quietly before the holidays. The version number is 9.00.5000.00 once the sp4 is installed. Here is the link that will take you to download site and this link is for what's fixed with sp4.

HTH,

Bulent

SSIS Warning: Truncation May Occur

I was working on SSIS package to load data from several sources into final destination table. And I see a yellow exclamation mark on data flow task from one of the sources. The message was 'Warning: Truncation may occur due to inserting data from data flow column "Description" with a length of 255 to database column "Description" with a length of 250. When I checked the source column length and destination it was obvious that the lengths of the columns were different and SSIS was correct about complaining. I verified that there was not a record that was inserted with the length greater than 175 char (the field is populated by app in one of the 5 possible defined descriptions). So I did the changes below to get SSIS stop complaining about the length of the column. To fix the issue follow the steps below:

Open the Data Flow task

On the source task right click and select Advanced Editor

Click on Input and Output Properties tab

Expand OLE DB Source Output

Expand External columns and check the columns we need

Expand Output Columns

Click on corresponding column

Change Length property of the column to desired length and refresh.



Also on Destination side:

Open Advanced Editor (on OLE DB Destination)

Click on Input and Output Properties tab

Click on Refresh button

Check the value of the length is correct



HTH,

Bulent

SQL Server Error 5042 Can Not Remove File X Because It's Not Emty

Couple weeks ago one of the job failed with the message stating that the file can not be removed because it is not empty. I have implemented quite a few partitioned tables and created a stored procedure which switches data from a 3 months old partition and then merge the partition. The process has been working fine but suddenly it failed. I check the partitions, but there was nothing in the file. I started to search the Internet and I found a help in here by Jeffrey Yao at SQLServerCentral.com. In my case the file was created originally 5 GB in size but during the month it grew to 5.5 GB in size and the file size was still 5.5 GB. I followed Jeffrey's steps and shrank the file to it's original size of 5 GB and then the process ran successfully to merge the partition.

HTH

SQL Server Collate Clause And Case Sensitivity

The servers in the current environment I am working has been configured with case insensitive collation by default during the installation. I had to help a developer who was trying to write a clr function to compare values stored in a database. We had to find out all the uppercase first and last names stored in one of the tables. The columns in the table have been created case insensitive. I thought of using built in feature instead of doing it in CLR and finding out the results. As you see in the simple script below I am creating 4 columns 2 case insensitive and 2 case sensitive. Populate it with sample data and finally compare the columns against each other. Note that once you create a columns like this you can not compare case insensitive column against so pay attention the where clause in the select statements. With this simple solution we were able to easily produce all the records stored in uppercase in the table. For more information look in the Books Online and search for collate

HTH,


USE tempdb
GO

CREATE TABLE dbo.CaseSensitiveTest(
RowId TINYINT IDENTITY (1,1),
CIFName VARCHAR (30),
CILName VARCHAR (30),
CSFName VARCHAR (30) COLLATE Latin1_General_CS_AS,
CSLName VARCHAR (30) COLLATE Latin1_General_CS_AS

)

GO

INSERT INTO dbo.CaseSensitiveTest
SELECT 'MICKEY', 'MOUSE', 'Mickey','Mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'mickey','mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'Mickey','Mouse'
UNION ALL
SELECT 'MICKEY', 'MOUSE', 'MICKEY','MOUSE'
UNION ALL
SELECT 'bunny', 'rabbit', 'Bunny','Rabbit'
UNION ALL
SELECT 'BUNNY', 'RABBIT', 'BUNNY', 'RABBIT'

GO

SELECT * FROM dbo.CaseSensitiveTest

SELECT *
FROM dbo.CaseSensitiveTest
WHERE CIFName COLLATE Latin1_General_CS_AS = CSFName
AND CIFName COLLATE Latin1_General_CS_AS = UPPER(CIFname)


DROP TABLE dbo.CaseSensitiveTest

SQL Server Failed to Alert Operator

I just deployed a new SQL Server Server and set up some jobs. Couple of days later wanted to check the server and see how jobs were executing. I was surprised to find out one job has been failing and I remember not seeing any alert. I did remember that I set up database mail and operators. Job history tells me that sql server failed to notify the operator. I started to look around and finally thought of checking the sql server agent properties. To my surprise I found out that I forgot to set up the properties under alert and putting check for enable email profile, selecting the profile and another check for enabling fail safe operator did all the trick. After changing the setting started to work and sql server agent started to notify me with emails.

HTH

SQL Server 2008 Invalid SKU error

I was trying to set up SQL Server 2008 Cluster on Windows 2008 OS. Creating the cluster with a single node went pretty smooth. However when I wanted to add the second node to cluster I kept getting 'Invalid SKU error' and installation kept aborting the operation. I downloaded the new ISO of SQL Server 2008 from MSDN website with no help. Searching the net came out with the link below which stated that it was a know bug and ways to workaround it. I started the setup process from command line using the command below. At the end the node has been added with success.

Here is the command that I started the installation:

setup.exe /q /ACTION=AddNode /INSTANCENAME=""

Microsoft Connect bug report site:

HTH

SQL Server 2008 R2 Availalbe to Download

SQL Server 2008 R2 is available for downloading to MSDN and Technet subscribers. Here are the links:

• http://msdn.microsoft.com/subscriptions
• http://technet.microsoft.com/subscriptions

Implicit Data Type Conversion

Hello all,

Yesterday by accident I ran into a query running slow. In this case the query is generated by Nhibernate. After looking at the query and execution plan I figured out that it was the implicit data type conversion.

The data in the table is stored in column of varchar data type. The column is a primary key with clustered index. However the query is issued against the table with a parameter passed in and it's declared as NVARCHAR. Do you see where this is going? So when the query is executed with nvarchar parameter sql server does an index scan on the clustered key and converts each column data to nvarchar and then compares the result against the parameter passed in. As we all know clustered index scan is equal to table scan.

I changed the query and declared the parameter as varchar and ran it. It worked as expected. There was no need to do a data type conversion so SQL Server used Clustered Index Seek operator and completed the operation 4 times faster than the slow query. In my case the table I am talking has 199K rows. Imagine how much slower it could be if it had millions of rows.

I talked to principal architect and the solution was to update Nhibernate mappings. He said that Nhibernate by default maps to nvarchar data type and if you're not using nvarchar for unicode characters in your table you definitely want to map Nhibernate to your tables properly using correct data type.

HTH,

MileHighDBA

SQL Server 2008 R2 Released To Manufacturing

Here is the announcement from Microsoft Data Platform blog that SQL Server 2008 R2 has been released to manufacturing.

SQL Server 2008 R2

Here is the link to free ebook download from Microsoft. Start learning before the product releases on May 2010.

SQL Server Trimming miliseconds (Zero out miliseconds)

Hi,
Where I work we needed to zero out the milliseconds from a datetime field in one of the database. I found out that somebody created function below which returns the passed datetime with the milliseconds zero out. Instead of 2010-04-15 14:50:49.953 this time we needed to return 2010-04-15 14:50:49.000. When I looked at the function I thought there is just to much DATEPART, CONVERT and concatenation being used. And I asked myself If I could make it much shorter, cleaner. So I did come up with a result and it works. Here is the original function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMilliseconds] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CONVERT(datetime,
CONVERT(varchar(4), DATEPART(yyyy, @D)) + '-' +
CONVERT(varchar(2), DATEPART(mm, @D)) + '-' +
CONVERT(varchar(2), DATEPART(dd, @D)) + ' ' +
CONVERT(varchar(2), DATEPART(Hh, @D)) + ':' +
CONVERT(varchar(2), DATEPART(mi, @D)) + ':' +
CONVERT(varchar(2), DATEPART(ss, @D)))

return @Date
END
GO

Here is my updated version:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[TrimMillisecondsNew] (@D datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date datetime
SET @Date = CAST(CONVERT(VARCHAR(19),@D,20) AS DATETIME)

return @Date
END
GO

As you see the second function is much cleaner and simpler. I only used convert and cast functions once to do the work. Less typing, less t-sql, work is done.

24 Hours Of Pass 2010

Check the link to obtain information and register for 24 hours of Pass 2010. This is 24 hours of back to back SQL Server related webcasts starting on May 19 at 12:00 GMT(UTC). There are a lot of good presentations available go ahead, check it out and register now. I don't think I can watch all but I hope I will have time for most.

MSSQL Replication 'Field Size Too Large'

Back from a vacation and I am ready to blog at least once a week. Last week there was an update to a large table which was involved in transactional replication. We wanted to move from using GUID as primary key and implement integer based identity key. So I had to stop publishing the table to be able to drop the primary key and add a new column, populate it create the primary key on the new column.

So we had the script executed to do the job. The I used the same script which alters the table to make the necessary changes in the subscriber database. Because the table in subscriber was in different file group with different indexes and I did not want to the replication drop and recreate the table itself (which is default). I then initiated the replication with a new snapshot and waited for 5 minutes. Then I saw the error message and found out that it's complaining about the field size too larger. How can that be? Both tables are exactly same for the basic schema. I tried to create a new snapshot with the same ugly error message. Then I decided to script out my additional indexes in subscriber and dropped the table. I created the table in subscriber from a script created in the publisher with my additional indexes in the specific file group I want. The I set the publication to create a new snapshot with leaving the target table in subscriber left as it is. And that did it.

Hope this helps.

SQL Server 2005 and 2008 Service Pack Announcements

Microsoft announced the upcoming targeted release dates for SQL Server 2005 and 2008 Service Packs.

SQL Server 2005 Service Pack 4 targeted release date is Q42010.
SQL Server 2008 Service Pack 2 targeted release date is Q32010.

For most people who has been waiting for SQL 2005 SP4 maybe over before the end of the year.

Suspect_Pages

I think not many database administrators are aware of the msdb.dbo.suspect_pages table in SQL Server 2005/2008. The database engine uses this table to record possible suspect page data. It records the pageid and event_type_value which indicates what type of error or how the page repaired along with some other data. For detecting suspect page in your server I recommend setting up a job which looks at that table with certain event_type_value. (the values can be 1,2,3,4,5,7) If database engine records a row with value less than equal to 3 then it means you're facing possible CRC failure, checksum error, or torn page. Then you can send an email alert to your self to start looking for solution. You can use DBCC commands to repair the page or if it does nt work you may have to restore the page from a backup. Suspect_pages table is like having some early warning system so I think dba's should use this table to alert themselves. You can read more about it here on MSDN Site.

HTH

SQL Server 2008 System Views Map

Microsoft has published SQL Server 2008 System View map in .pdf and .xps file format. It's a must for SQL Server DBA. Get the map from here.

Cannot drop the database 'MyDatabase' because it is being used for replication. [SQLSTATE 42000] (Error 3724)

I wanted to drop and restore a database which I restored couple days ago in a test environment. The same db in production is the publisher and there are objects in transactional replication. I was asked to restore the new backup. However drop database returned the error message in the subject line and did not drop the db. Little bit of searching and I found out that the stored procedure can be used to remove the replication prior to dropping the database in test environment. So I connected to my test server and executed following statements in the order.

1. USE MASTER EXEC dbo.sp_removedbreplication @dbname = 'MyDatabase'
2. USE MASTER DROP DATABASE MyDatabase

It worked and I was able to drop and restore the latest backup to the test environment.

Do not run this in production since it will remove the replication from your production publisher.

HTH,

SQL Server SA Login Disabled and Can Not Login To Server

I needed to fix the login issue on newly installed SQL Server 2008 developer edition for a developer who is just starting to work. The tech who prepared the workstations forgot to add the local admin and the AD user account and he was out of the office. So even with the local administrator account no one can log in to SQL Server. Thanks to new security settings of SQL Server 2008. I did a little research and found out the solution. Here is the list of actions for the solution. First open the SQL Server Configuration Manager.

1. Stop SQL Server Service.
2. Double click on SQL Server service and goto advanced tab.
3. add -m; to the beginning of Startup Parameters
4. Start SQL Server Service
5. Open command prompt and connect to your the server using SQLCMD
6. Create a log in (CREATE LOGIN BillGates WITH PASSWORD = 'password')
7. Now add the log in to sysadmin group (EXEC sp_addsrvrolemember 'BillGates','sysadmin')
8. Remember you need to issue GO command to execute the commands.
9. Once you created the login and add it to sysadmin role go to SQL Server Configuration Manager.
10. Stop SQL Server service.
11. Remove -m; from startup parameters.
12. Start SQL Server Service and login with the user that you just created.
13. Now you have sysadmin rights.

HTH,

Bulent