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)
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
I ran across this post on Stack Overflow. Here is the code I ended up using:
Dim count = (From x In db.Orders Where x.OrderID = orderID From y In x.OrderDetails Select y).Count
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 Different Database Options
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 + ‘%’
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. Parameter name: value
The only thing I’ve been able to figure out to do is add the parent table value to the drop down list. This only works when the value saved in the parent table is the actual value and not the foreign key (the ID of the child table such as StateID). Here is my code:
<
asp:DropDownList ID=”ddlState” runat=”server” DataSourceID=”SqlDataSource1″ DataTextField=”StateName” DataValueField=”StateAbbr”></asp:DropDownList> <asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:DATABASE_01 %>“ SelectCommand=”SELECT [StateAbbr], [StateName] FROM [States] ORDER BY [StateName]”></asp:SqlDataSource>Then in code:
Sub
Page_Load()TrueddlState.AppendDataBoundItems =
If Not IsPostback Then
… ‘ read data from database
New ListItem… ‘ load values into controls
Items =
I’ve tried putting a ‘Try/Catch’ around setting the ddlState.SelectedValue but that is not where the error occurs. The error occurs as the page is being rendered. When using the SqlDataSource control is used the data gets bound as the page is being rendered. The page load event occurs before the page is rendered so the drop down selected value is set before the data is bound! This method prevents the “does not exist” error messages all the time. If you have a better solution I’d sure like to hear it!
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’ ] [ , [ @Password = ] ‘password’ ]
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp