RDA Corp - Business Intelligence and SQL Server

« January 2007 | Main | March 2007 »

February 28, 2007

Crystal Reports to Reporting Services Converter

I came across a blog that referenced this product. Don’t know anything about it, but thought I’d pass it on in case someone wants to try it out.


http://www.ktlsolutions.com/t-crystalconverter.aspx



Jim Pletscher
RDA Corp.
www.rdacorp.com

Share:  

Posted by Steve Mann at 7:37 AM | Comments (0)

February 23, 2007

Rolling Back Service Pack 2 (SP2) for SQL Server 2005

This is a bit scary, but if you want to rollback SP2 from SQL 2005 you have perform a complete reinstallation. Here's a blog entry from Chad Boyd, an MS employee, that gives a little more detail:
http://blogs.msdn.com/chadboyd/archive/2007/02/21/sp2-rollback-requires-complete-reinstallation.aspx
Let's be careful out there kids !

JoeT

Share:  

Posted by Steve Mann at 1:46 PM | Comments (0)

Issues Transferring Data back and forth from SQL 2000 to SQL 2005

by Mark Meyerovich, RDA Senior Software Engineer


Overview
Just to give some context for the issue, I wrote a couple of SSIS packages to implement bidirectional transfers between SQL Server 2005 and SQL Server 2000 databases. The problem appears when I write data to SQL Server 2000 only. The following is an excerpt from SSIS execution log:

Error:
Code: 0xC0202009
Source: Transform Facilities Create the site [5664]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Connection is busy with results for another command".
End Error
Log:
Name: OnError
Source Name: Transform Facilities
Source GUID: {7F14434A-17FC-4C57-A55D-7602A6963BD1}
Execution GUID: {594B2210-86BB-46CE-B483-044FA586367E}
Message: "component "Create the site" (5664)" failed validation and returned validation status "VS_ISBROKEN".

SQL Server service pack upgrade
A quick search on Google turns up the following article (http://support.microsoft.com/kb/822668):
FIX: "Connection is busy with results for another command" error message occurs when you run a linked server query.

It basically implies the issue is a bug and recommends an upgrade to Service Pack 4. We have started out with SQL Server 2000 SP3 and we do have some linked servers in the equation, so we give it a try. After the upgrade to SP4 †same result.

New feature of SQL Server 2005
Another insightful article appears on MSDN: http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx
It suggests turning on Multiple Active Result Sets (MARS) to deal with the issue. Just add the following parameter to SQL connection string: “MARS Connection=True”. This is one of the new features of SQL Server 2005. I wish I had that option!

Already seen in DTS environment
More searching leads me to SQL Server Central site (free membership required): http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19messageid=147003
Although quite old, the post is actually more relevant to DTS/SSIS and the issue at hand:
“I was getting all those errors because the changes made by the dynamic properties task were not all 100% in place when the next task started and tried to use the connection that was in process of being changed. I put a delay in place to make sure the connection is not used for about 3 or 4 seconds and now everything is fine.”
“I have seen this kind of delay in applying changes before. Like when a task executes and is finished and the next task checks the status of the execution of the first task. It always would come back as failed even though the previous task had succeeded. I needed at least a 2 second delay to ensure the execution status was finished being set.”

Perhaps it is a solution for some cases. But I wonder if it is 100% reliable. Besides, it may require significant code changes.

A quick property change
If you are looking for a quick solution and performance is not mission critical, then try MaxConcurrentExecutables package property. From online help:
“Specifies the maximum number of executables to concurrently execute in the package”; or
“Sets the number of threads that a package can create”

“A value of -1 means that Integration Services (SSIS) allows the maximum number of concurrently running executables to equal the total number of processors on the computer that is executing the package, plus two. Setting this property to zero or any other negative value fails with an error code that indicates an invalid argument.”

So -1 is the default, which in my case resulted in an actual value of 4. Setting this property value to one (1) basically causes the package to execute all tasks on a single thread. It is more of a workaround, but quickly gets rid of the error.

Closing
A few notes regarding this situation:
- the issue appears while updating SQL Server 2000 database only;
- if a package uses configuration file, you can include the property value in the file;
- when you deploy a package, SQL Server “Run Package” dialog has its own setting for the “Concurrent Executables” property, which is passed to DTS Exec command line.

Share:  

Posted by Steve Mann at 1:39 PM | Comments (0)

February 15, 2007

SQL Server 2005 Best Practices Site

This site looks pretty good ! It includes white papers and scripts.

https://owa.rdacorp.com/exchweb/bin/redir.asp?URL=http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

JoeT

Share:  

Posted by Steve Mann at 3:16 PM | Comments (0)

Sorting SSIS Packages

If you want to sort the files added to SSIS or SSMS projects: http://www.sqldbatips.com/showarticle.asp?ID=78

Share:  

Posted by Steve Mann at 3:15 PM | Comments (0)

February 14, 2007

Me.Name for Stored Procedures

The situation came up where we wanted to log the execution of procedures and the resultant rows. This is usally done by calling some insert log procedure which takes in various parameters. In the past, I would simply copy and paste the name of the procedure within the logging procedure call.

This is fine but it is hard coded. Now the same block of code will be copied and pasted into other procedures. Will we remember to change the name in the logging procedure call? Maybe 80% of the time. I've seen too many of these copy and paste errors happen.

So I wished there was some way to reference the name of the procedure being executed, kind of like a Me.Name in VB or some sort of C# reflection type of deal.

Well, seek and ye shall find. There is a system variable that stores the procedure identification of the executed code. It is accessed via @@PROCID. There is also a system function which will return the object name based on a ProcID input. Perfect! This is just what I was looking for!

There is a sample code block in SQL Server 2005 Books-Online but here is a sample block from almost production code with the newly found "nugget" in bold:


/* Log the query run */
DECLARE @ProcName VARCHAR(100), @RowCount BIGINT
SET @RowCount = @@ROWCOUNT
SET @ProcName = OBJECT_NAME(@@PROCID)
exec dbo.usp_InsertQueryLog @ReportInstance,@UserID,@ProcName, @RowCount


PLEASE NOTE THAT THIS MUST COME IMMEDIATLEY AFTER THE MAIN BLOCK OF CODE IN ORDER FOR @@ROWCOUNT TO RETURN THE PROPER VALUE. OTHERWISE DECLARE THE VARIABLES AT THE TOP OF THE PROCEDURE AND GRAB THE @@ROWCOUNT AFTER THE ACTION YOU NEED TO RECORD THE AMOUNT OF ROWS AFFECTED.

Share:  

Posted by Steve Mann at 8:33 PM | Comments (0)

February 13, 2007

Using Multi-Value Parameters in Stored Procedures

The SQL Server 2005 Reporting Services documentation states you cannot use a stored procedure with a mult-value parameter. It suggests you use embedded SQL and use the "IN (@Param)". Using embedded SQL goes against best practices.

There is, however, a way to allow your stored procedures to accept multi-value parameters. It involves a Strip function which parses out a character based (varchar, nvarchar, etc.) parameter.

Strip Function:


CREATE FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

Within your stored procedure SQL your WHERE clause would look like this:


WHERE ProductID IN (SELECT Item FROM dbo.Split (@ParamMV, ','))


This works outside of Reporting Services too and is a great alternative to dynamic SQL and does not pose a threat via SQL injection.

Share:  

Posted by Steve Mann at 5:13 PM | Comments (0)

February 12, 2007

Reporting Services - Hogging from Logging

By default, reporting services stores logs on the C: drive (or where SQL Server is installed). The location is within C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles (or similiar path). Over time, depending on the allocated space on the system drive, these log files can take up too much space.

You can configure how much logging to keep in the Site Settings page or through the Server Settings page in Management Studio (see screenshots below). The default is 60 days, which, on a heavily used RS server, might be too much. Perhaps 14 days (2 weeks) might be a better number.

Share:  

Posted by Steve Mann at 11:13 AM | Comments (0)