Category: SQL Server

SQL Server Reporting Services (SSRS) – Download Multiple RDL files

ssrs dashboardI 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:
SSRSExtractor.zip

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.

How To Convert Confusing Ascii Code To A Character

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
SELECT
REPLACE(column_with_carriage_return_delimiters, CHAR(13), ';')
FROM MyDBTable

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.

TOP 100 PERCENT and ORDER BY

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
AS
SELECT TOP 100 PERCENT lProductID, vsProduct FROM dbo.tblProduct AS tl
ORDER BY lProductID DESC
GO

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
FROM vwProduct

This would return the results ordered ascending not descending:
lProductId  vsProduct
———– ———
1           Widget A
2           Widget B
3           Widget C
4           Widget D
5           Widget E

SQL 2005 Stored Procedures Last Modify Date

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.

Five Database Design Errors To Avoid

Source: Simple-Talk

  1. Common Lookup Tables
    Always use separate tables for each logical entity, identifying the appropriate columns with correct types, constraints and references. It is better to write simple routines and procedures to access and manipulate the data in the tables without aiming for “dynamic code”.

    Common lookup tables have no place in sensible database design, whether used as a short-term makeshift fix or as a long-term viable solution.

  2. Check Constraint Conundrum
    Three specific criteria to choose between a check constraint or a separate table with foreign key constraints.

    1. If the list of values changes over a period of time, you must use a separate table with a foreign key constraint rather than a check constraint.
    2. If the list of values is larger than 15 or 20, you should consider a separate table.
    3. If the list of values is shared or reusable, at least used three or more times in the same database, then you have a very strong case to use a separate table.
  3. Entity-Attribute-Value Table(Entity-Attribute-Value)
    A nickname for a table that has three columns, one for the type of entity it is supposed to represent, another for a parameter or attribute or property of that entity and a third one for the actual value of that property.
  4. Application Encroachments Into Database Design
    Enforcing Integrity via applications: Databases are more than mere data repositories; they are the source of rules associated with that data. Declare integrity constraints in the database where possible, for every rule that should be enforced. Use stored procedures and triggers only where declarative integrity enforcement via keys and constraints isn’t possible. Only application-specific rules need to be implemented via the application.
    Application Tail wagging the Database Dog: Applications come and go, but databases usually stand for a long time.
  5. Misusing Data Values As Data Elements
    No two tables in a database should have overlapped meanings

Read More…

sp_rename

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'

Login failed for user ‘username’. The user is not associated with a trusted SQL Server connection.

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)”.