Geeks With Blogs

News My Blog has been MOVED to https://mfreidge.wordpress.com
Michael Freidgeim's OLD Blog My Blog has been MOVED to https://mfreidge.wordpress.com SQL Server
SqlDependency Notification database Options troubleshooting
For SQL notifications we followed the steps described in http://dimarzionist.wordpre... it didn't work for our database. The instructions missed a step to ensure that database options are set for SqlDependency NotificationI've created a function to Follow recommendation to always check the notification source, info and type, it returned SqlNotificationEventArgs, Type : Subscribe, Source : Statement, Info : Options //TODO: pass SQL string ......

Posted On Friday, February 19, 2016 10:08 PM

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

Posted On Sunday, December 9, 2012 3:45 PM

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

Posted On Sunday, September 30, 2012 11:44 AM

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

Posted On Saturday, April 14, 2012 2:14 PM

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

Posted On Friday, August 12, 2011 7:20 AM

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

Posted On Saturday, May 12, 2012 7:34 PM

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

Posted On Monday, July 11, 2011 7:34 AM

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

Posted On Monday, June 27, 2011 5:58 AM

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

Posted On Monday, June 13, 2011 2:19 AM

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

Posted On Friday, July 22, 2011 8:00 PM

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

Posted On Sunday, June 5, 2011 8:42 AM

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

Posted On Thursday, May 12, 2011 6:41 AM

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

Posted On Tuesday, December 22, 2009 4:18 AM

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

Posted On Saturday, July 4, 2009 7:07 AM

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

Posted On Sunday, May 3, 2009 9:13 PM

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

Posted On Saturday, February 28, 2009 5:32 PM

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

Posted On Sunday, January 18, 2009 11:26 AM

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

Posted On Thursday, November 13, 2008 11:55 PM

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

Posted On Friday, January 9, 2009 11:55 AM

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

Posted On Friday, July 25, 2008 1:53 AM

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

Posted On Thursday, August 3, 2006 9:31 AM

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

Posted On Tuesday, July 15, 2008 11:45 PM

View html source in "Reporting Services" Report Design Preview

When I am using Report Design Preview in "Reporting Services" , I often want to view html source( in particular generated URLs), but Context Menu doesn't give me the option.
However I can select "Export/Web Archive" and save report as MHTML, than view it in IE. 

I feel that it is quicker than deploy the report to the server.

Posted On Thursday, January 3, 2008 10:51 AM

SQL Server Reporting Services Notes
I am doing some work with "SQL Server Reporting Services" at the moment. So I am updating this post with different links, which makes the post quite messy. "Report Parameters" are not visible in XML code view .It seems that they are stored in database, but not in XML definition. See also Reporting Services Report Parameters CountRows Function -Returns a count of rows within the specified scope-the dataset, grouping, or data region. The "Every Other Page Is Blank" Feature -reduce size of Body to fit ......

Posted On Sunday, November 25, 2007 7:55 PM

SQL Server Seminar presented by Kevin Kline
Last week I was on SQL Server Seminar presented by Kevin Kline. Performance, Baselining, Benchmarking and Monitioring Presentation Stored Procedure Best Practices Presentation Surviving the Data Avalanche Presentation Top 10 Mistakes on SQL Server Presentations There were a few interesting points. •Causes of performance problems-5%-hardware, 15%-bad database design, 80%-bad application code. •Do not Interleaved DDL and DML -No create/Drop table in the same SP as insert/Select. •Keep transactions ......

Posted On Sunday, October 21, 2007 10:02 AM

SQL Server Stored Procedure Naming Standard Recommendations
I've included a few articles that recommend similar but slightly different SP naming standards: SSW SQL Stored Procedure Naming Standard [proc] [MainTableName] By [FieldName(optional)] [Action] e.g procClientRateSelect' Practical Methods: Naming Conventions Prefix all stored procedures with "p". Complete the name with the primary table affected, then the job performed. This will group all procedures for a given table in one location alphabetically. Samples: pCustomerList, pCustomerSearch, pCustomerCreate, ......

Posted On Thursday, October 4, 2007 1:00 AM

T-SQL User defined function to concatenate column to CSV string
I had a complicate (and buggy) T-SQLstored procedure, that concatenate fields from details table and show them as one field in the resultset of the main table(Itinerary and summary of itinerary items. I've looked in pivot functionality, but didn't fint how to make it working in the way I needed. Furthermore, Pivot is available for SQL Server DB with compatibility level 90(aka SQL server 2005), but our db still has compatibility level 80(aka SQL server 2000). So I desided to write UDF to concatenate ......

Posted On Tuesday, October 2, 2007 3:18 PM

"This SqlTransaction has completed; it is no longer usable." errors
Our system started to get intermittent errors like the foolowing in the code that was NOT changed recently: System.InvalidOperationExce... This SqlTransaction has completed; it is no longer usable. at System.Data.SqlClient.SqlTr... at System.Data.SqlClient.SqlTr... I was pointed to MS Kb http://support.microsoft.co... that I've installed, but it didn't change -errors still appeared. Unfortunately, the KB doesn't have a list of changed files and their ......

Posted On Monday, September 3, 2007 1:08 AM

Reference to different database in "Database Mirroring" environment
We have a stored procedure in one database that (among other things) insert a row to another database on the same server -something like INSERT INTO OtherDatabase.dbo.LogTable. We are using "Database Mirroring" feature of SQL Server 2005 having mirror on another server. However failover switch happens for each database separately and it can be situations, when one database run on the same principle server, but another switched to the mirror server. For this situation the mentioned above SP failed, ......

Posted On Saturday, May 26, 2007 2:54 PM

Start Transaction from ASP.Net application can cause unexpected locks on the database.
I have the ASP.Net application, that uses SqlTransaction.BeginTransac... before the call to Update SP and commit after the call.If any exception would happened, transaction should be Rollback. The code snippet is the following: conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { ds = SqlHelper.ExecuteDataset(tr... CommandType.StoredProcedure, SPName); trans.Commit(); } catch (Exception ex) { trans.Rollback(); //Log the error } } We noticed that during update of web.config ......

Posted On Wednesday, May 16, 2007 2:25 AM

.Net SQL Parser
I had a requirement to parse SQL string, in particular with CONTAINS(column_list) and modify SELECT column list.I've found two most promising implementations General SQL Parser from http://www.sqlparser.com/ and SharpHSQL from http://www.c-sharpcorner.co... article. General SQL Parser has quite qood API, but it doesn't have open source and doesn't recognize contains with column_list. SharpHSQL is a early C# port of the HSQL Database Engine which has a source code, but also doesn't ......

Posted On Saturday, August 12, 2006 3:36 PM

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

Posted On Thursday, February 23, 2006 7:35 AM

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

Posted On Wednesday, February 22, 2006 8:36 AM

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

Posted On Tuesday, November 15, 2005 11:08 AM

Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net