Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
SQL SERVER – Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery
Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery Following statement will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” MS SQL Server doesn’t support it. USE PUBS GO SELECT AVG(COUNT(royalty)) RoyaltyAvg…
Read MoreSQL SERVER – Binary Sequence Generator – Truth Table Generator
Run following script in query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively. Script: USE AdventureWorks; DECLARE @Binary TABLE ( Digit bit) INSERT @Binary VALUES (0)…
Read MoreSQL SERVER – MS TechNet : Storage Top 10 Best Practices
This one of the very interesting article I read regarding SQL Server 2005 Storage. Please refer original article at MS TechNet here. Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application. More / faster spindles are better for performance. Try not to…
Read More
SQL SERVER – Query to Find First and Last Day of Current Month – Date Function
Following query will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is very handy function when working with Alphanumeric String only. I have used this many times. CREATE FUNCTION dbo.UDF_ParseAlphaChars ( @string VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @IncorrectCharLoc SMALLINT SET @IncorrectCharLoc…
Read MoreSQL SERVER – 2005 – List all the database
List all the database on SQL Servers. All the following Stored Procedure list all the Databases on Server. I personally use EXEC sp_databases because it gives the same results as other but it is self explaining. ----SQL SERVER 2005 System Procedures EXEC sp_databases EXEC sp_helpdb ----SQL 2000 Method still works…
Read More