The instructions missed a step to ensure that database options are set for SqlDependency Notification
The alternative -to use SQL and POCO classes-see http://stackoverflow.com/questions/238504/linq-to-sql-loading-child-entities-without-using-dataloadoptions?rq=1
Product firstProduct = db.Product.Include("OrderDetail").Include("Supplier").First();
We should mtonitor database files(both data and log), and if they close to max capacity, manually increase the size.
execute sp_spaceused for the database in question or
One of my colleagues asked how to create SQL query to show results as columns of a row, that actually stored in multiple rows in the table. Other co-worker suggested to use cursor to manually generate required rows.
I’ve found a few links about SQL PIVOT.
It is easy to start with
More detailed are articles written by Itzik Ben-Ganhttp://www.sqlmag.com/article/tsql3/pivot-or-unpivot-your-data
How to use Pivoting Without Aggregation is described inhttp://www.sqlmag.com/article/tsql3/pivoting-without-aggregation
I’ve noticed multiple messages from SQL Server in EventLog on my machine
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.222.25.129]
I’ve found that there are machines of my co-workers, but they were not sure, which processes tried to access my SQL server.
I’ve tried a few things and finally in SQL Server Configuration Manager disabled tcp, as it was suggested in
if NOT Exists(select * from sys.columns where Name = N’CreatedDate’ and Object_ID = Object_ID(N’MyTableName‘))
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND TABLE_NAME = ‘MyTableName’ )
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME= ‘PK_MyTableName‘)
—Check if foreign key does not exists
if NOT Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =’FK_MyRefTable_MyInstanceTable’)
or (from here)
IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = ‘FK_Name’)
—Check if DEFAULT does not exist
if NOT Exists(select * from sysobjects where xtype=’D’ and NAME=’DF_MyTableName_MyColumnName’)
In our environment most of developers use SQL Server 2008,but in production we have SQL Server 2005, and some databases still have compatibility level 80(SQL server 2000)
It sometimes causes problems
E.g. the SQL wtitten on SQL Server 2008
INSERT INTO[dbo].[SiteIds] ([SiteID], [SiteCode],[ParentID])
VALUES (11,’ChildOfXX’,(select [SiteID] from [SiteIds] where SiteCode=’XX’))
on SQL Server 2005 caused
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Fortunately it’s easy to rewrite in SQL 2005 acceptable form
DECLARE @SiteId int
set @siteID=(select [SiteID] from [SiteIds] where SiteCode=’XX’)
INSERT INTO[dbo].[SiteIds] ([SiteID], [SiteCode],[ParentID]) VALUES (11,’ChildOfXX’,@siteID)
Another statement that generated in SQL Server 2008 should be removed in SQL Server 2005
ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE)
Also we had a stored procedure, that worked in compatibility level 80(SQL server 2000), but caused an error after changing to compatibility level 90
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
The explanation of the error can be found in SELECT DISTINCT and ORDER BY post. In my case DISTINCT was completely redundant and I’ve removed it without any side effects.