Displaying a list of user selected parameters in Reporting Services

I’m generating a notice that the user will need to select reasons for an application was rejected. The user could select multiple reasons, but must include at least one. So I needed to display the reasons of rejection in the report like:

* Reason1

* Reason2

*Reason4

The problem I was running into was checking how many reasons were selected and displaying only the selected amount. I was at first trying something like this for all counts of Parameters!Reasons:

=IIF(Parameters!Reasons.Count < 2, “”, “* “+Parameters!Reasons.Value(1))

The problem with this is, even though the count was less than 2 the false part of the IIF still gets evaluated, giving me an “#Error” in my document with an index out of bounds.

I then got cleaver and tried using the join and came up with this:

="* " + Join(Parameters!Reasons.Value,VbCrLf+VbCrLf + "* ")

While, normally a join would use something like a whitespace or comma to delimit.

Timeout Errors Prevention

After a while, you may end up getting complaints or request dealing with timeout issues. I’m going to use this post to help collect some ways to increase the timeout seconds and prevent errors from happening.

*Note: Even though you increase the timeout seconds, timeouts might still occur but with a longer delay then before. This might make your customers even more upset because of the longer wait for the error to display.

• In code, increase the sqlCommand timeout. Ex:
   

Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)

myCommand.CommandType = CommandType.StoredProcedure
'change default time out setting
myCommand.CommandTimeout = 120

• In code, increase the connection’s string timeout by appending “Connection Timeout=” to it. Ex:
  
  Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;Connection Timeout=120
  
• On SQL-Server 2005, In management studio’s Tools > Option > Designers Increase the “Transaction time-out after:” even if  “Override connection string time-out value for table designer updates” checked/unchecked. 
  
• Make (non-dynamic) stored procedures instead of using inline sql statements within the code. This will also allow for easy fixes instead of having to recompile and deploy for future changes.

 

If you have any other suggestions, please contact me so I can add them to the list.

Bulk Export from SQL Server into a CSV file

Well, here is a simple query/stored procedure that you can run to bulk export the tables in a database into a csv file.

Just change the “databaseName” in the file to the one you want to point to and also change the location if you wish. Currently it is the C:\ drive.

 

DECLARE @var nvarchar(MAX)

DECLARE curRunning
CURSOR LOCAL FAST_FORWARD FOR
select name from sysobjects where type = 'U'

Open curRunning

Fetch NEXT From curRunning into @var

WHILE @@FETCH_STATUS = 0
BEGIN
    --select 'exec master.dbo.xp_cmdshell ''bcp databaseName.dbo.' + @var + ' out C:\' + @var + '.csv -c -T -t ,'''
    DECLARE @Exec nvarchar(MAX)
    set @Exec = 'exec master.dbo.xp_cmdshell ''bcp databaseName.dbo.' + @var + ' out C:\' + @var + '.csv -c -T -t ,'''
    execute sp_executesql @Exec
FETCH NEXT FROM curRunning into @var
END

close curRunning
DEALLOCATE curRunning

Interesing line count of MS Dev Products

Code sizes:
• Visual Studio 2005: 7.5 million lines
• SQL Server 2005: 3 million lines
• BizTalk Server: 2 million lines
• Visual Studio Team System: 1.7 million lines
• Windows Presentation Foundation: 900K lines
• Windows SharePoint Services: 750K lines
• Expression Blend: 250K lines
• SharePoint Portal Server: 200K lines
• Content Management Server: 100K lines
• Dynamics SL has 3.4 million Lines

Good source on optimizing SQL Server 2005

http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

Changing Dynamic SQL to Static SQL

I've been on this project for a couple of weeks now, changing store procedures that use dynamic sql to a static sql. The reason for this change is too speed up the store procedures.

The store procedure might have a dynamic sql statement like:

SET @tsql = 'SELECT * FROM TABLE1
WHERE TABLE1.Name = ' + @Name

IF @ID <> ''
@tsql = @tsql + ' AND TABLE1.ID = ' + @ID

exec sp_executeSql @tSqlQuery

This would be changed to:

SELECT * FROM TABLE1 WHERE TABLE1.Name = @Name
AND( (@ID <> '' AND TABLE1.ID = @ID) OR
(@ID = '' ))

Now came the problem if they dynamically set a column to be sorted:

IF LTRIM(RTRIM(@sortColumn)) <> ''
@tsql = @tsql + 'ORDER BY ' + @sortColumn

This unfortunately had to be solved by making a case statement for all possible columns that are returned. So in this case this table returns only two columns (name and id):

SELECT NAME, ID FROM TABLE1
WHERE TABLE1.Name = @Name AND ((@ID <> ''
AND TABLE1.ID = @ID) OR (@ID = '' ))
ORDER BY
CASE @sortColumn WHEN 'ID' THEN ID ELSE NULL END,
CASE @sortColumn WHEN 'NAME' THEN NAME ELSE NULL END

The reason for the seperate case statements in the example is because it can only return one data type. If NAME is of varchar and ID is of int, then they have to be separated.

Well, that's a very basic and simple run down of what I've been doing. I do run into larger more complex store procedures and other situations. For example, when a dynamic sql statement is using a table name as a variable.

Rollbacks and Commits in Stored Procedures

So, I ran into this problem of my transaction counts being offset. The problem was that store procedure A has a "Begin Transaction" then calls store procedure B which also has it's transactions of begin, commit, and rollback.

Microsoft says:

If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs. This can happen in two ways:

A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a ROLLBACK TRANSACTION statement. @@TRANCOUNT decrements to 0 and causes an error 266 when the stored procedure completes.


A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a COMMIT TRANSACTION statement. @@TRANCOUNT decrements by 1 and causes an error 266 when the stored procedure completes. However, if BEGIN TRANSACTION is executed after the COMMIT TRANSACTION, the error does not occur.

-- http://msdn2.microsoft.com/en-us/library/ms187844.aspx

DECLARE @LocalTransActive Bit
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION Trans_Discharge
SET @LocalTransActive = 1
END

For the commit part:
IF @LocalTransActive = 1
BEGIN
COMMIT TRANSACTION Trans_Discharge
END

For the rollback part:
IF @@TRANCOUNT > 0
BEGIN
IF @LocalTransActive = 1
BEGIN
ROLLBACK TRANSACTION Trans_Discharge
END
END

Database Error 17: SQL Server does not exist or access denied.

Okay, I had to install VS.Net on top of VS 2005 and while I did that I figured I would update the security knowledge base from microsoft. Apparently it must of disabled the TCP/IP ports to prevent potential worms and such. So when I started one of our apps, I would now end up getting:

Database Error 17: SQL Server does not exist or access denied.

So here is the solution I found to work thanks to http://www.aspfaq.com/sql2005/show.asp?id=3

Step 1)
Make sure that SQL Server 2005 is functioning properly:

Start / Run... / type "CMD" without the quotes and hit OK
Type "SQLCMD" without the quotes and hit Enter
Verify that you have a "1>" prompt
Type "Exit" without the quotes and hit Enter

Step 2)
Start the SQL Browser service:
Start / Run... / type "NET START SQLBROWSER" without the quotes and hit OK

Step 3)
Make sure that named pipes and TCP/IP protocols are enabled:

Start / Programs / SQL Server 2005 / Configuration Tools / SQL Server Configuration Manager
Open "SQL Server 2005 Network Configuration"
Highlight "Client Protocols"
Right-click the Tcp node and make sure it is enabled (click "Enable" if it is available)
Repeat for the Named Pipes node


Step 4)
Restart SQL Server 2005 if you made any changes above:

Start / Run... / type "NET STOP MSSQL" without the quotes and hit OK
Start / Run... / type "NET START MSSQL" without the quotes and hit OK

Step 5)
If SQL Server 2000 is installed on the same machine, make sure that SP4 is installed prior to installing SQL Server 2005.

BizTalk Assessment Question: BizTalk 2006 supports what SQL Servers

What versions of Microsoft(R) SQL Server(TM) are supported by Microsoft BizTalk(R) Server 2006? (Choose all that apply.)

• Microsoft SQL Server 6.5
• Microsoft SQL Server 2000
• Microsoft SQL Server 2000 with Service Pack 4
• Microsoft SQL Server 2005
• Microsoft SQL Server 7.0

Answer found: Microsoft SQL Server 2005 or Microsoft SQL Server 2000 with Service Pack 4