MelvinsWebStuff My Online Notepad

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

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.

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…

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

.NET Data Access Architecture Guide

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.

1. From the Start menu, click Run.
2. Enter the following command: regsvr32 %windir%\system32\msxml6.dll
3. 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)”.

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

You could use

sp_helpdb [ [ @dbname= ] 'name' ]

exec msdb..sp_enum_dtspackages