Archive / SQL Server

RSS feed for this section

SQL Server Unique Index with Multiple NULL Values

Sometimes I require unique values in a column that allows nulls. CREATE UNIQUE NONCLUSTERED INDEX IndexName ON dbo.TableName(ColumnName) WHERE ColumnName IS NOT NULL Update: SQL Server Management Studio has this functionality built into the Index Properties dialog. ([ColumnName] IS NOT NULL)

Grant Alter Trace Permission SQL Server Profiler

SQL Server Profiler requires you to have ALTER TRACE permission. There are two ways to grant that permission. Properties: Object Explorer -> Security -> Logins -> your_login -> Securables -> Search button -> “The server” radio button -> “Grant” checkbox on “Alter Trace” SQL: GRANT ALTER TRACE TO myuser

.Net Options for Data Access

Data Access Practices Using Microsoft .Net: A Nerdly Comparison In this article a comparison of these options: – Connected Data Access with ADO.NET– Disconnected Data Access with ADO.NET and Typed DataSets– Basic Object Relation Mapping with LINQ to SQL– Object Relational Mapping with LINQ to Entities and the Entity Framework See also: Extending NerdDinner: Exploring […]

SQL Like with Parameter

I have a stored procedure that will do a search on records based on a value passed in. To use a parameter value with the SQL ‘Like’ command use this format:    SELECT * FROM clients WHERE FirstName LIKE ‘%’ + @sWhere + ‘%’ OR LastName LIKE ‘%’ + @sWhere + ‘%’  

SelectedValue which is invalid because it does not exist in the list of items

To populate drop down lists I bind the list to a lookup table in the database (i.e. States). Occasional the parent table (i.e. Customers) has a value that is “not on the list” and this error is genreated:    ‘ddlState’ has a SelectedValue which is invalid because it does not exist in the list of items. […]

Restore Database and Database User

When restoring an SQL Server 2005 database from a different database instance the database login will be orphaned. Use this SQL script to fix the orphaned login. sp_change_users_login ‘Auto_Fix’, ‘user’, NULL, ‘password’ Actual syntax:sp_change_users_login [ @Action = ] ‘action’     [ , [ @UserNamePattern = ] ‘user’ ]     [ , [ @LoginName = ] ‘login’ […]