Geeks With Blogs

News My Blog has been moved to
Michael Freidgeim's Blog My Blog has been moved to 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 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 ......

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

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

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

"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

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 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 and SharpHSQL from 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

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

Copyright © Michael Freidgeim | Powered by: | Join free