Sql Server Reporting Services passing multi-value parameters to a stored procedure

If you are attempting to pass a multi-value parameter from SSRS to a Stored Proedure using something like WHERE tablename.rowname IN (@iParam) it will probably bomb out for reasons I won’t explain here. To fix you need to create a SQL function to help split the parameter in a way SQL can read it.

CREATE FUNCTION SplitQuotedString (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(10)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS INT))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

After creating the function you can then rewrite the query to look like this:
WHERE tablename.rowname IN (SELECT * FROM dbo.SplitQuotedString(@iParam, ','))

I didn’t come up with this answer I gleened the tubes and finally found a fix from www.sqlmag.com

Sql Server Reporting Services 400 Bad Request Error

I spent a good two days trying to fix this issue. Basically when I tried to view a report via the Report Viewer on a C# page it would throw a 400 Bad Request Error. Weird thing was I could actually get to http://<servername>/Reports and http://<servername>/ReportServer with out issue.

To fix: Went into the properties of the individual ReportViewer control and set ReportServerUrl to http://<servername>/reportserver. Instead of http://localhost/reportserver. I’m still not sure why this decided to break because everything had been working with localhost for a few months, then it just decided it didn’t like localhost anymore.

FYI if you get the same error by going to http://<servername>/Reports or http://<servername>/ReportServer check the RSWebApplication.config (located in Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportManager). Set <ReportServerUrl>http://<FullyQualifiedURL>/<VirtualDirectoryOfReportServer></ReportServerUrl> Also make sure the next line looks like this:
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Sql Server Reporting Services cannot update database

I can’t remember the exact error, but SSRS will tell you that the database version is wrong and you need to upgrade. When you hit the upgrade button it will error out saying it can’t run the script.
This is because the script is wrong. If you back out and get back into the configuration tool, go back to the database setup screen and click the Scripts button at the bottom. This will open up a screen where you can output the Upgrade script to a file. Do this, then open the file and you will see it has some syntax errors. At the top it will say something like USE BAD[ReportingServerTempDB], you need to change all instances of this to just [ReportingServerTempDB] (or whatever name you named your Database) and remove anything that is in front of that. I have read about people having different things in front of their database name in any case you just to remove all instances of this and run the script and you should be good to go.

Sql Server Reporting Services (SSRS) error retrieving WMI properties

You get this error when you try to switch panels using the SSRS Configuration Tool. I also recieved this error when starting up the SSRS Configuration Tool: System.Management.ManagementException: Not found
The fix for me was fairly simple, you just need to restart the WMI Service in Windows
Start - Run - services.msc - select Windows Management Instrumentation - right click - restart.
A reboot would also probably work, but if you’re on a production machine that’s probably not an option

Remove Login Box from Sql Server Reporting Services (SSRS)

Seems like I always forget how to do this so here it is:

Access the project from Visual Studio.

Double Click on the .rds file, then click the Edit button

Set username and password making sure to check the Save my password box

Call it a day...Easy right?

Using Dymanic SQL in Reporting Services

Set up the report using Shared DataSources like you would normally do.

Set up a parameter for the table name (or whatever dynamic variable you want). In my case I set up a parameter called iTableName.

Go to the Data portion of the Report and edit the DataSource you wish to add the variable to. Click on the function button next to the Query string.

Enter in your Query with the parameter in for the table name. My example:
="Select * from " & Parameters!iTableName.Value

**Note: You can also do the same thing for the DataSource inserting a parameter for the server name. Ex.
="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=DBName" **

ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

To delete individual rows one by one run the following SQL Statement:

SET ROWCOUNT 1
DELETE FROM Table
WHERE col = '1'

Microsoft SQL Server Error: 18486 - SA Account locked out

Login with windows user/pass and run this command:

ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

Microsoft SQL Server Install Walkthrough

This is a great site to walk you through a SQL Server install. http://www.builderau.com.au/program/sqlserver/soa/Avoiding-Microsoft-SQL-Server-2005-deployment-pitfalls/0,339028455,339229543-2,00.htm
I have had about a million issues installing MS crap software, so this was a god send. Usually I just install everything with the default values and pray it will work, only to find out 6 months down the road I did something wrong during the install and something isn't working properly. Now maybe I am just an idiot when it comes to installing things, but if you need a tutorial to install software, something isn't right.