I needed to migrate multiple SSRS reports (.rdl’s) between environments (production -> qa ->development) and didn’t want to download/upload each individual report. I searched but couldn’t find a solution built into SSRS report manager, so I began my Google search. I found a solution on Code Project (SSRS Downloading .RDL Files) that worked after some tweaking.
This project written in C# using Visual Studio 2010, and all I really needed to change was the URL to the SSRS web service to get the project up and running. Some of the other changes I made included building the folder structure as it is on the SSRS server and only saving the .rdl files.
Here’s what I came up so far:
After I ran the project and downloaded all the reports really fast, I still had to manually upload the ones I needed to migrate. When I have time hopefully I can implement uploading to an SSRS server into the project.
CHAR can be used to insert control characters into character strings. The frequently used control characters are:
- CHAR(9) = Tab
- CHAR(10) = Line Feed
- CHAR(13) = Carriage Return
My problem was trying to export data in Management Studio from a 3rd party application database into a spreadsheet.
I found knowing this useful when a column contained carriage returns as delimiters (which looked like spaces in Management Studio) so my SQL statement turned out to be something like this:
--; can be change to whatever is useful
REPLACE(column_with_carriage_return_delimiters, CHAR(13), ';')
Now I could copy/paste the result in a text editor and replace ; with whatever I needed. There maybe a better solution but this is what I came up with so far.
In SQL Server 2008, the TOP 100 PERCENT and ORDER BY is ignored. In SQL Server 2005, the output order isn’t guaranteed.
For example if you created a view like so:
CREATE VIEW vwProduct
SELECT TOP 100 PERCENT lProductID, vsProduct FROM dbo.tblProduct AS tl
ORDER BY lProductID DESC
And tried to select from it expecting the results to be ordered as specified in the view, it’s not going to happen.
-- Select From View
SELECT lProductID, vsProduct
This would return the results ordered ascending not descending:
1 Widget A
2 Widget B
3 Widget C
4 Widget D
5 Widget E
USE Database --Change to the database you want to use
SELECT name, create_date, modify_date
FROM sys.objects --User defined objects system view
WHERE type = 'P' --Only return stored procedures
AND modify_date > '2009-09-01' --If you aren't looking for a specific data range, take this line out
ORDER BY modify_date DESC
Read sys.objects documentation
Also read documentation on Querying the SQL Server System Catalog, it contains some information on backward compatibility and future releases of SQL Server.
Changes the name of a user-created object (i.e. table, column, or user-defined data type) in the current database.
Rename a table
This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'
Rename a column
This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
Failed to save package file “C:\TEMP\tmp18D.tmp” with error 0x8002802B “Element not found”. Turns out that this is due to MSXML6 not being registered.
To register MSXML 6.0.
- From the Start menu, click Run.
- Enter the following command: regsvr32 %windir%\system32\msxml6.dll
- Click OK
The cause of this problem is the SQL server has been configured to operate in “Windows Authentication Mode (Windows Authentication)” and doesn’t allow the use of SQL accounts. This was the default setting in my case, I got the error after installing a new instance of SQL Server 2005.
I was able to solve this problem by changing the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)” to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.