SQL SERVER – Guest Post by Sandip Pani – SQL Server Statistics Name and Index Creation

Sometimes something very small or a common error which we observe in daily life teaches us new things. SQL Server Expert Sandip Pani (winner of Joes 2 Pros Contests) has come across similar experience. Sandip has written a guest post on an error he faced in his daily work. Sandip is working for QSI Healthcare as an Associate Technical Specialist and have more than 5 years of total experience. He blogs at SQLcommitted.com and contribute in various forums. His social media hands are LinkedIn, Facebook and Twitter.


Once I faced following error when I was working on performance tuning project and attempt to create an Index.

Mug 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name ‘Ix_Table1_1′ already exists on table ‘Table1′.

The immediate reaction to the error was that I might have created that index earlier and when I researched it further I found the same as the index was indeed created two times. This totally makes sense. This can happen due to many reasons for example if the user is careless and executes the same code two times as well, when he attempts to create index without checking if there was index already on the object. However when I paid attention to the details of the error, I realize that error message also talks about statistics along with the index. I got curious if the same would happen if I attempt to create indexes with the same name as statistics already created. There are a few other questions also prompted in my mind. I decided to do a small demonstration of the subject and build following demonstration script.

The goal of my experiment is to find out the relation between statistics and the index. Statistics is one of the important input parameter for the optimizer during query optimization process. If the query is nontrivial then only optimizer uses statistics to perform a cost based optimization to select a plan. For accuracy and further learning I suggest to read MSDN.

Now let’s find out the relationship between index and statistics. We will do the experiment in two parts. i) Creating Index ii) Creating Statistics

We will be using the following T-SQL script for our example.

IF (OBJECT_ID('Table1') IS NOT NULL)
DROP TABLE Table1
GO
CREATE TABLE Table1
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO

We will be using following two queries to check if there are any index or statistics on our sample table Table1.

-- Details of Index
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, Name AS IndexName, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO
-- Details of Statistics
SELECT OBJECT_NAME(OBJECT_ID) TableName, Name AS StatisticsName
FROM sys.stats
WHERE OBJECT_NAME(OBJECT_ID) = 'table1'
GO

When I ran above two scripts on the table right after it was created it did not give us any result which was expected.

Now let us begin our test.

1) Create an index on the table

Create following index on the table.

CREATE NONCLUSTERED INDEX Ix_Table1_1 ON Table1(Col1)
GO

Now let us use above two scripts and see their results.

We can see that when we created index at the same time it created statistics also with the same name.

Before continuing to next set of demo – drop the table using following script and re-create the table using a script provided at the beginning of the table.

DROP TABLE table1
GO

2) Create a statistic on the table

Create following statistics on the table.

CREATE STATISTICS Ix_table1_1 ON Table1 (Col1)
GO

Now let us use above two scripts and see their results.

We can see that when we created statistics Index is not created. The behavior of this experiment is different from the earlier experiment.

Clean up the table setup using the following script:

DROP TABLE table1
GO

Above two experiments teach us very valuable lesson that when we create indexes, SQL Server generates the index and statistics (with the same name as the index name) together. Now due to the reason if we have already had statistics with the same name but not the index, it is quite possible that we will face the error to create the index even though there is no index with the same name.

A Quick Check

To validate that if we create statistics first and then index after that with the same name, it will throw an error let us run following script in SSMS. Make sure to drop the table and clean up our sample table at the end of the experiment.

-- Create sample table
CREATE TABLE TestTable
(Col1 INT NOT NULL,
Col2 VARCHAR(20) NOT NULL)
GO
-- Create Statistics
CREATE STATISTICS IX_TestTable_1 ON TestTable (Col1)
GO
-- Create Index
CREATE NONCLUSTERED INDEX IX_TestTable_1 ON TestTable(Col1)
GO
-- Check error
/*Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics with name 'IX_TestTable_1' already exists on table 'TestTable'.
*/
-- Clean up
DROP TABLE TestTable
GO

While creating index it will throw the following error as statistics with the same name is already created.

In simple words – when we create index the name of the index should be different from any of the existing indexes and statistics.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – FIX ERROR – Cannot connect to . Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

Just a day ago, I was doing small attempt to connect to my local SQL Server using IP 127.0.0.1. The IP is of my local machine and SQL Server is installed on the local box as well. However, whenever I try to connect to the server it gave me following strange error.

Cannot connect to 127.0.0.1.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

The reason was indeed strange as I was trying to connect from local box to local box and it said my login was from an untrusted domain. As my system is not part of any domain, this was really confusing to me. Another thing was that I have been always able to connect always using 127.0.0.1 to SQL Server and this was a bit strange to me. I started to think what did I change since it  last time I connected to SQL Server. Suddenly I remembered that I had modified my computer’s host file for some other purpose.

Solution:

I opened my host file and immediately added entry like 127.0.0.1 localhost. Once I added it I was able to reconnect to SQL Server as usual. The location of the host file is C:\Windows\System32\drivers\etc. You will find file with the name hosts in it, make sure to open it with notepad.

If you are part of a domain and your organization is using active directory, make sure that your account is added properly to active directory as well have proper security permissions to execute the task.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DQS Error – Cannot connect to server – A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessions” – SetDataQualitySessionPhaseTwo

Earlier I wrote a blog post about how to install DQS in SQL Server 2012. Today I decided to write a second part of this series where I explain how to use DQS, however, as soon as I started the DQS client, I encountered an error that will not let me pass through and connect with DQS client. It was a bit strange to me as everything was functioning very well when I left it last time.  The error was very big but here are the first few words of it.

Cannot connect to server.
A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessions”:
System.Data.SqlClient.SqlException (0×80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessionPhaseTwo”:

The error continues – here is the quick screenshot of the error.

As my initial attempts could not fix the error I decided to search online and I finally received a wonderful solution from Microsoft Site. The error has happened due to latest update I had installed on .NET Framework 4. There was a  mismatch between the Module Version IDs (MVIDs) of the SQL Common Language Runtime (SQLCLR) assemblies in the SQL Server 2012 database and the Global Assembly Cache (GAC). This mismatch was to be resolved for the DQS to work properly.

The workaround is specified here in detail. Scroll to subtopic 4.23 Some .NET Framework 4 Updates Might Cause DQS to Fail.

The script was very much straight forward.

Here are the few things to not to miss while applying workaround.

  • Make sure DQS client is properly closed
  • The NETAssemblies is based on your OS.
  • NETAssemblies for 64 bit machine – which is my machine is “c:\windows\Microsoft.NET\Framework64\v4.0.30319″. If you have Winodws installed on any other drive other than c:\windows do not forget to change that in the above path. Additionally if you have 32 bit version installed on c:\windows you should use path as “c:\windows\Microsoft.NET\Framework\v4.0.30319″
  • Make sure that you execute the script specified in 4.23 sections in this article in the database DQS_MAIN. Do not run this in the master database as this will not fix your error.
  • Do not forget to restart your SQL Services once above script has been executed.
  • Once you open the client it will work this time.

Here is the script which I have bit modified from original script. I strongly suggest that you use original script mentioned 4.23 sections. However, this one is customized my own machine.

/*
Original source: http://bit.ly/PXX4NE (Technet)
Modifications:
-- Added Database context
-- Added environment variable @NETAssemblies
-- Main script modified to use @NETAssemblies
*/
USE DQS_MAIN
GO
BEGIN
-- Set your environment variable
-- assumption - Windows is installed in c:\windows folder
DECLARE @NETAssemblies NVARCHAR(200)
-- For 64 bit uncomment following line
SET @NETAssemblies = 'c:\windows\Microsoft.NET\Framework64\v4.0.30319\'
-- For 32 bit uncomment following line
-- SET @NETAssemblies = 'c:\windows\Microsoft.NET\Framework\v4.0.30319\'
DECLARE @AssemblyName NVARCHAR(200), @RefreshCmd NVARCHAR(200), @ErrMsg NVARCHAR(200)
DECLARE ASSEMBLY_CURSOR CURSOR FOR
SELECT
name AS NAME
FROM sys.assemblies
WHERE name NOT LIKE '%ssdqs%'
AND name NOT LIKE '%microsoft.sqlserver.types%'
AND name NOT LIKE '%practices%'
AND name NOT LIKE '%office%'
AND name NOT LIKE '%stdole%'
AND name NOT LIKE '%Microsoft.Vbe.Interop%'
OPEN ASSEMBLY_CURSOR
FETCH NEXT FROM ASSEMBLY_CURSOR
INTO @AssemblyName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
TRY
SET @RefreshCmd = 'ALTER ASSEMBLY [' + @AssemblyName + '] FROM ''' +
@NETAssemblies + @AssemblyName + '.dll' +
''' WITH PERMISSION_SET = UNSAFE'
EXEC sp_executesql @RefreshCmd
PRINT 'Successfully upgraded assembly ''' + @AssemblyName + ''''
END TRY
BEGIN CATCH
IF ERROR_NUMBER() != 6285
BEGIN
SET
@ErrMsg = ERROR_MESSAGE()
PRINT 'Failed refreshing assembly ' + @AssemblyName + '. Error message: ' + @ErrMsg
END
END
CATCH
FETCH NEXT FROM ASSEMBLY_CURSOR
INTO @AssemblyName
END
CLOSE
ASSEMBLY_CURSOR
DEALLOCATE ASSEMBLY_CURSOR
END
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Error: Fix – Msg 208 – Invalid object name ‘dbo.backupset’ – Invalid object name ‘dbo.backupfile’

Just a day before I got a very interesting email. Here is the email (modified a bit to make it relevant to this blog post).

“Pinal,

We are facing a very strange issue.

One of our query  related to backup files and backup set has stopped working suddenly in SSMS. It works fine in application where we have and in the stored procedure but when we have it in our SSMS it gives following error.

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dbo.backupfile’.

Here are our queries which we are trying to execute.

SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;

This query gives us details related to backupset and backup files when the backup was taken.”

When I receive this kind of email, usually I have no answers directly. The claim that it works in stored procedure and in application but not in SSMS gives me no real data. I have requested him to very first check following two things:

  1. If he is connected to correct server? His answer was yes.
  2. If he has enough permissions? His answer was he was logged in as an admin.

This means there was something more to it and I requested him to send me a screenshot of the his SSMS. He promptly sends that to me and as soon as I receive the screen shot I knew what was going on.

Before I say anything take a look at the screenshot yourself and see if you can figure out why his queries are not working in SSMS. Just to make your life a bit easy, I have already given a hint in the image.

The answer is very simple, the context of the database is master database. To execute above two queries the context of the database has to be msdb. Tables backupset and backupfile belong to the database msdb only.

Here are two workaround or solution to above problem:

1) Change context to MSDB

Above two queries when they will run as following they will not error out and will give the accurate desired result.

USE msdb
GO
SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;

2) Prefix the query with msdb

There are cases above script used in stored procedure or part of big query, it is not possible to change the context of the whole query to any specific database. Use three part naming convention and prefix them with msdb.

SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM msdb.dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM msdb.dbo.backupfile;

Very simple solution but sometime keeps people wondering for an answer.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – ERROR: FIX using Compatibility Level – Database diagram support objects cannot be installed because this database does not have a valid owner – Part 2

Earlier I wrote a blog post about how to resolve the error with database diagram. Today I faced the same error when I was dealing with a database which is upgraded from SQL Server 2005 to SQL Server 2008 R2. When I was searching for the solution online I ended up on my own earlier solution SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed because this database does not have a valid owner.

I really found it interesting that I ended up on my own solution. However, the solution to the problem this time was a bit different. Let us see how we can resolve the same.

Error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Workaround / Fix / Solution :

Follow the steps listed below and it should for sure solve your problem. (NOTE: Please try this for the databases upgraded from previous version. For everybody else you should just follow the steps mentioned here.)

  • Select your database >> Right Click >> Select Properties
  • Go to the Options
  • In the Dropdown at right labeled “Compatibility Level” choose “SQL Server 2005(90)”
  • Select FILE in left side of page
  • In the OWNER box, select button which has three dots (…) in it
  • Now select user ‘sa’ or NT AUTHORITY\SYSTEM and click OK.

This will solve your problem. However, there is one very important note you must consider. When you change any database owner, there are always security related implications. I suggest you check your security policies before changing authorization. I did this to quickly solve my problem on my development server. If you are on production server, you may open yourself to potential security compromise.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix : Error 3623 – An invalid floating point operation occurred

Going back in time, I always had a problem with mathematics. It was a great subject and I loved it a lot but I only mastered it after practices a lot. I learned that mathematics problems should be addressed systematically and being verbose is not a trick, I learned to solve any problem. Recently one of reader sent me an email with the title “Mathematics problem – please help!” and I was a bit scared. I was good at mathematics but not the best. When I opened the email I was relieved as it was Mathematics problem with SQL Server. My friend received following error while working with SQL Server.

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

The reasons for the error is simply that invalid usage of the mathematical function is attempted. Let me give you a few examples of the same.

SELECT SQRT(-5);
SELECT ACOS(-3);
SELECT LOG(-9);

If you run any of the above functions they will give you an error related to invalid floating point. Honestly there is no workaround except passing the function appropriate values. SQRT of a negative number will give you result in real numbers which is not supported at this point of time as well LOG of a negative number is not possible (because logarithm is the inverse function of an exponential function and the exponential function is NEVER negative).

When I send above reply to my friend he did understand that he was passing incorrect value to the function. As mentioned earlier the only way to fix this issue is finding incorrect value and avoid passing it to the function. Every mathematics function is different and there is not a single solution to identify erroneous value passed. If you are facing this error and not able to figure out the solution. Post a comment and I will do my best to figure out the solution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error: 10920 Cannot drop user-defined function. It is being used as a resource governor classifier

If you have not read my SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor yesterday’s detailed primer on Resource Governor, I suggest you go ahead and read it before continuing this article. After reading the article the very first email I received was as follows:

“Pinal,

I configured resource governor on my development server and it worked fine with tests I ran. After doing some tests, I decided to remove the resource governor and as a first step I disabled it however, I was not able to drop the classification function during the process of the clean up. It was continuously giving me following error.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function myudfname. It is being used as a resource governor classifier.

Would you please give me solution?”

The original email was really this short and there is no other information. I am glad he has done experiments on development server and not on the production server. Production server must not be the playground of the experiments. I think I have covered the answer of this error in an earlier blog post.

If the user disables the Resource Governor it is still not possible to drop the function because it can be enabled again and when enabled it can still use the same function. Here is the simple resolution of the how one can drop the classifier function (do this only if you are not going to use the function).

The reason the classifier function can’t be dropped because it is associated with resource governor. Create a new classified function for your resource governor or just assign NULL as described in the following T-SQL Script and you will be able to drop the function without error.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO

I am glad that user asked me question instead of doing something radically different, which can leave the server in the unusable state. I am aware of this only method to avoid this error. Is there any better way to achieve the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)