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 […]
Category: SQL Server
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 […]
Categories
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 […]
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 […]
Source: Simple-Talk 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 […]
.NET Data Access Architecture Guide Lock Down IIS and SQL Server Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication Planning for Extranet or Internet Deployment Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication
Categories
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’
Error 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. Solution 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 […]
DTS error: The log file for database ‘dtsPackageName’ is full. Back up the transaction log for the database to free up some log space. Solution: backup log epack_dev with TRUNCATE_ONLY