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... ......

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... ......

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 ......

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 ......

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 ......

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 ......

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 ......

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 ......

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 ......

SQl Server Management Studio doesn't show new lines in records.

We are storing in the database plain text messages formatted with new lines. When it was required to update the message, my colleague used SQL Server Management Studio to retrieve text in Grid View. The text was shown as one long string without any newline separators. She copied the text to editor, updated it and use SQL Update statement to save the text back to the database row. The problem was that all new line formatting was lost. SQL Server Management Studio didn't indicate new lines and causes ......

SSIS import from Excel to SQL table doesn't like empty lines

I had a task yo import data from Excel spreadsheet to SQL Server table. Last time I tried to use SQL Server Management Studio import option, but it gave me some not clear errors, that I didn't understand. So I've created SSIS project in VS and was able to narrow down the errors and import the data. This time for similar spreadsheet I had some errors again. The output contained the warnings about length of the string column, that usually can be ignored, e.g.: Warning: 0x802092A7 at First Task, Destination ......

"Login failed for user" may mean "database name is invalid"

One of my colleagues tried to work with a new database from DevServer and got an error "Login failed for user". We checked everything related to security and permissions, but it didn't help. After a while we recognize that there was spelling mistake in the name of the database in connection string. In this case "Login failed for user" error actually meant "database name is invalid" Why it wasn't shown as a reason in plain English? Other possible reason for the errors are described in multiple articles, ......

T-SQL not equal WHERE condition excludes records with null values.

I've had a query which expected to return all records except with specified value in the nullable column. Select * FROM MyTbl where (MyColumn<>'ValueToEx... But the query didn't return any records with null values in the column. The correct query to include records with null should have explicit is Null condition like the following: Select * FROM MyTbl where (MyColumn<>'ValueToEx... or MyColumn is Null) Update: I found, that it is a well known problem, discussed in many forums, ......

Full SQL Server Archive

«October»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678