Feeds:
Posts
Comments

Archive for the ‘SQL Error Messages’ Category

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)

Posted in PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on September 6, 2012 | 10 Comments »

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)

About these ads

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged , on September 3, 2012 | 7 Comments »

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)

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 31, 2012 | 18 Comments »

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)

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL on August 12, 2012 | 3 Comments »

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)

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on June 28, 2012 | 9 Comments »

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)

Read Full Post »

« Newer Posts - Older Posts »