SQL Server

Eager Loading more than 1 table in LinqtoSql

When I've tried in Linq2Sql to load table with 2 child tables, I've noticed, that multiple SQLs are generated. I've found that it isa known issue, if you try to specify more than one to pre-load it just picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)There are more explanations in http://codebetter.com/blogs... reason the relationship ......

Monitoring Database disk space

An article Data files: To Autogrow Or Not To Autogrow? recommends NOT to rely on auto-grow, because it causing delays in unplanned times.We should mtonitor database files(both data and log), and if they close to max capacity, manually increase the size. However it doesn't give references, how to monitor the free space inside databases. I've tried to look how to do it. It can be done manually using execute sp_spaceused for the database in question or sp_SOS (can be downloaded from http://searchsqlserver.tech... ......

Links about SQL PIVOT

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 withhttp://www.kodyaz.com/a... or http://programmingsolution.... More detailed are articles ......

T-SQL function to Get Maximum of values from the same row

Based on the ScottPletcher solution from http://www.experts-exchange... --SELECT dbo.GetMax(23, 45, 64, 22, 18, 224, 74, 138, 1, 98, 11, 86, 198) --Naturally adjust data type to match what you actually need for your specific values I’ve created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values. CREATE FUNCTION GetMaxOfDates13 ( @value01 DateTime = NULL, @value02 DateTime = NULL, @value03 DateTime = NULL, ......

Use CompiledQuery.Compile to improve LINQ to SQL performance

After reading DLinq (Linq to SQL) Performance and in particular Part 4 I had a few questions. If CompiledQuery.Compile gives so much benefits, why not to do it for all Linq To Sql queries? Is any essential disadvantages of compiling all select queries? What are conditions, when compiling makes whose performance, for how much percentage? World be good to have default on application config level or on DBML level to specify are all select queries to be compiled? And the same questions about Entity Framework ......

Lookup Tables with fallback support

Our database has a few lookup tables that uses similar approach to implemented by .Net Framework for localized resources: At run time, ASP.NET uses the resource file that is the best match for the setting of the CurrentUICulture property. If there is no match for the current UI culture, ASP.NET uses resource fallback. It starts by searching for resources for a specific culture. If those are not available, it searches for the resources for a neutral culture. If these are not found, ASP.NET loads the ......

Disable messages from SQL Server “Login failed for user” in Event log

I’ve noticed multiple messages from SQL Server in EventLog on my machineLogin failed for user 'NT AUTHORITY\ANONYMOUS 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 inhttp://blogs.msdn.com/b/p... ......

DDL 'IF not Exists" conditions to make SQL scripts re-runnable

As a part of continuous integration we are using deployment of database scripts, which makes very important to make the scripts re-runnable. Some checks for DDL elements are not obvious, and I decided to put hem in one place.Most answers were found on StackOverflow --Column does not exists if NOT Exists(select * from sys.columns where Name = N'CreatedDate' and Object_ID = Object_ID(N'MyTableName')) --Check if primary key does not exists IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CO... ......

Restore SQL Server Database from suspect

Below are a few links, that can help, if you need Restore SQL Server Database from suspect MyITforum.com : How to recover user databases from a “Suspect” status( http://www.myitforum.com/ar... ) http://forums.devx.com/arch... You could also try detaching the DB, then do a single file attach only using the db, dropping the log file. That solved the suspect issue for one of our DBs. Somewhere in the SQL message logs it indicated that the LOG file was corrupted ......

Some compatibility differences between versions of SQL Server

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')) GO on SQL Server 2005 caused Msg 1046, Level 15, State 1, Line 5 Subqueries are not allowed in this context. Only scalar expressions ......

SQL Script to create indexes for Foreign keys

http://stackoverflow.com/qu... I’ve used SQL script similar to paul_nielsen’s to Create Indexes for Foreign Keys and added “if not exists” condition DECLARE @SQL VARCHAR(max); SET @SQL = '' SELECT @SQL = @SQL + 'if not exists (select * from sys.indexes where id=object_id(''' + TableName +''') and name=''Ix' + ForeignKeyName+''') CREATE INDEX Ix' + ForeignKeyName + ' ON ' + TableName + '(' + ColumnName + '); ' FROM ….... --SELECT @SQL ......

SQL's "like" patterns to compare in .Net

I wanted to use SQL's "like" patterns to compare in .Net. I found the good C# implementation of function in thread Using Regex to create a SQL's "like" like function. The function IsSqlLikeMatch works fine, but I've noticed that the search is case-sensitive. It's also doesn't match % if there are multiple lines. But it was easy to change by modifying IsMatch call to return Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase | RegexOptions.Singleline); Note that it could be very serious performance ......

Safe Save DateTime to SQL Database.

When I tried to save DateTime data to SQL database, I've got SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. The problem is described in http://blogs.vertigosoftwar... I've had to create a function object SafeSqlDateTime(DateTime dtValue) { object theValue = dtValue; if (dtValue <(DateTime) SqlDateTime.MinValue) // January 1, 1753. { theValue = null; } return theValue; } For opposite SqlDateTime to DateTime Conversion ......

Custom alert on the SQL Database

I wanted to create a custom alert on the SQL Database when number of records with some values(considered as invalid) exceeds the expected limit. First of all you need to Set up Database Mail for SQL 2005 and follow procedure How to setup SQL Server alerts and email operator notifications IMPORTANT: don't forget to Restart SQL Agent to activate settings. Similar to the article Define custom error messages in SQL Server 2005 I've defined the error EXEC sp_addmessage 60001, 1, N'Number of not-processed ......

Stored procedure to find the biggest tables in the database

To find the biggest tables in the SQL Server database I am using good stored procedure EXEC dbo.sp_SOS @OrderBy='T' /*downloaded from http://media.techtarget.com... The related article "Find size of SQL Server tables and other objects with stored procedure" Valid @OrderBy parameters are: 'N' --> Listing by object name 'R' --> Listing by number of records 'T' --> Listing by total size 'U' --> Listing by used portion (excluding ......

Error: "Autogrow of file in database was timed out"

We've had an error Autogrow of file 'MyDatabase_dat' in database 'MyDatabase' was cancelled by user or timed out after 30078 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size. I was able to fix the issue using: ALTER DATABASE MyDatabase MODIFY FILE ( NAME = MyDatabase_dat, size=5000MB) We should follow the best practices from MSDN article "Considerations for the "autogrow" and "autoshrink" settings in SQL Server" In particular, use ......

Use GUID, not IDENTITY as a primary key for tables that are required "merge-like replication”.

I have the following scenario. Vendor has a database with table having some vendor-provided data inserted. Customers have a copy of the database with ability to add new rows to the same table, but they should not delete some vendor-provided records. Periodically vendor sends patches/updates with additional records to be added to the same table. Someone can say that a better design will be to store vendor and customer data in separate tables, but the structure of both types of records is identical ......

"default collation" for a SQL Server 2000 database

Some our clients reported “'Cannot resolve collation conflict for equal to operation'” error. As it is explained here, it could happen if the tempdb database collation differs from the user database collation. I was curious where "default collation" for a SQL Server 2000 database is stored - in the database itself or in master system tables. I didn't find any good documentation for SQL Server 2000, but according to SQL Server 2005 doc http://msdn2.microsoft.com/... ......

MS Access Project uses incorrect SQL if RecordSource starts with leading space.

I had a MS Access 2002 VB code Form.RecordSource = " Select * from dbo.[TempBarcodesList] where SessionID= " & Quoted(hdnSessionID) with a leading space before Select in SQL statement. Profiler showed that the following SQL was send to the database exec sp_executesql N'SELECT * FROM Select * from dbo.[TempBarcodesList] where SessionID= ''2006-02-22 12:07:06.327'' WHERE "SessionId" = @P1 AND "AutoID" = @P2', N'@P1 varchar(23),@P2 bigint', '2006-02-22 12:07:06.327', 22 and it obviosly had sintax ......

Remove NewLine characters from the data in SQL Server

UPDATE: Xavid at 8/2/2007 in a comment suggested a simpler code: REPLACE(REPLACE(REPLACE(MyF... CHAR(10), ''), CHAR(13), ''), CHAR(9), '') I found that some string in the database have NewLine characters where they do not required. To remove them in T-SQL I wrote the following SQL script (TODO: write re-usable SP, also special option to remove NewLine characters from the end of the string) declare @NewLine char(2) set @NewLine=char(13)+char(10) update TableName set ColumnName =Replace(ColumnName ......

Full SQL Server Archive

«November»
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456