You are here: Home / 2010 / Archives for March 2010

xp_sendmail: failed with mail error 0×80040111

03/30/2010 By

I ran into this error today while diagnosing a sql alert that was sending mail utilizing xp_sendmail on a clustered SQL Server 2000 machine.  The funny thing was that I was told that when SQL gets failed over to the other node it worked fine.  That seemed really strange to me…so I decided to investigate to see if I could get it fixed once and for all.  Sure enough when I tried to send a test xp_sendmail message I got the following error:

Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0×80040111

Since this was 2000, the mail must be configured to use a MAPI profile in order to send mail.  Because of this I decided I should take a look at the mail profile itself.  So I opened Outlook and took a look at the mail profile.  Everything seemed to be working fine for me.  That’s funny, it works for me, but when SQL fires it on one node it works, but on the other one it doesn’t.  What could be causing this to stop working?  I failed the cluster over and I took a look at the email profile over on the side where it worked.  Everything was the same on the email profile.  This got me thinking, that perhaps it is permissions.  So I looked at the SQL Server service account and compared that to the other node.  It turns out that they were using two different accounts for the SQL Server service.   When I logged in as the service account where the errors were occuring and I opened up Outlook, I got the error stating that the exchange server was unavailable.  This confirmed that is must have due to the login credentials of the SQL Server service account.  I changed the service account to use the same service account that worked on the other node and all the email alerts were back to normal again.  So, if you ever run into an  0×80040111 error with xp_sendmail, we should make sure that the sql server service account that is running has access to the mail account used in the MAPI.  Once that is set and the account can freely send and recieve mail, then everything should work beautifully.

UPDATE:

I got a great tip in an email from Tim Edwards BLOG|Twitter so I am copying it in here now:

“Unfortunately, because the MAPI protocol isn’t cluster aware, SQL Mail is not fully supported by Microsoft on Windows clusters (see http://support.microsoft.com/kb/298723/EN-US/).  If you have an SMTP server available, the preferable method in that situation would be to go send your alerts out via SMTP (good article from SQLServerCentral.com on how to accomplish that here: http://www.sqlservercentral.com/scripts/Miscellaneous/30510/).  The only downside is that this means you have to create an extra job step for notification since SMTP is not native in SQL Server 2000 and thus is not integrated with the SQL Agent notifications and alerts.”

Filed Under: Tips

Error: 0xC00F9304 The Excel Connection Manager is not supported in the 64-bit version of SSIS

03/29/2010 By

If your reading this error, your probably experiencing what I did today when trying to run an SSIS package on a 64 bit machine that has a connection manager to Excel.  It seems that the Excel, and Access Jets have issues communicating with SQL Server 2008.  There is a work around fortunately and it involves running the package using DTExecute via the X86 Program Files Directory.  The way I handled it was to create a batch file to run the package similar to the below script and save it as a BAT file:

“D:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe” /f “D:Test.dtsx” /X86

Another way to do it is schedule it to run as a job and then check the Use 32 bit Runtime checkbox in the execution options of the job like below:

Filed Under: Tips

Integration Services in a SQL Server 2K8 Clustered Environment

03/25/2010 By

Today I was setting up a new SQL Server 2008 Server in a cluster and I discovered that the Integration Services service is not cluster aware.  I also discovered that Microsoft advises AGAINST installing the service as a clustered resource.  Why on earth would Microsoft neglect to make the Integration Services service cluster aware?  That doesn’t seem very good as it leaves a single point of failure, right?  Well after careful consideration and researching the following two articles from Microsoft I decided that it wasn’t going to be as big of a deal as I thought. 

Both of these Microsoft articles give you the information you need to decide for yourself

Definition of the Integration Services Service and some alternatives to use when used in clustered Environments  -  The recommended ways.

To configure Integration Services as a cluster resource  -  The not so recommended way

Filed Under: Database Administration, Tips

Load trace data from Profiler into a table

03/19/2010 By

Today’s post is a short but extremely useful.  I often find myself needing to import a trace I made previously into a table so I can peruse through the data in more meaningful ways.  You would think that by this iteration of SQL they would have thought to have made the columns sortable in the GUI, but unfortunately they have not.  In order to get the same result though we can import the data into a SQL table by using a simple select into using a system function called fn_trace_gettable.  To see it in action create a profiler trace file  and then run the following Query with your desired tablename and trace file location:

SELECT * INTO trace20100318 FROM ::fn_trace_gettable(‘c:trace20100318.trc’, default)

Now we can query the table and sort it any way we like.  Enjoy!

Filed Under: Performance Tuning, Tips

Linked Server errors connecting to SQL2k from SQL2k8

03/16/2010 By

Today I was setting up a linked server from a SQL Server 2008 machine connecting to a SQL 2000 machine using SQLOLEDB. After configuring the server in 2008 I tested it on SQL 2008 machine and everything seemed to be running fine. When I tested out the linked server in SQL 2000 though I got the following errors:

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’ . (Microsoft SQL Server, Error: 229) 

To fix this error I granted execute rights to the xp_prop_oledb_provider proc via the following script:

GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [LOGIN]

After that I got the following error:

Message: Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server “[Linked Server Name]“.  The provider supports the interface, but returns a failure code when it is used.

To fix this error I found a Microsoft article that describes the needed steps to remedy

Filed Under: Tips

GADZOOKS!!! Where did this cursor come from?

03/13/2010 By

I was researching some performance issues today on some SQL Server 2000 machines that were experiencing high CPU spikes and I noticed some strange cursors being called on one of the referenced servers.  The funny thing was that none of the queries were explicitly creating the cursor themselves.  How is this possible, you may ask yourself?  Well, the two servers were communicating with one another via the 4 part naming conventions(  Server.database.dbo.tablename )  via a SQLOLEDB configured linked server.  SQL Server was then creating server side cursors to pass the data back between to two servers.  In a profiler trace I saw numerous sp_cursorfetch calls and the CPU started going through the roof.  I also noticed that it seemed to be calling sp_cursorfetch for EACH ROW of the recordset.  As you can imagine if it is a large table then the number of roundtrips will be immense and in my case that’s exactly what was occurring.  To fix the problem I used OPENQuery to run the query statement on the remote server.  This allowed me to return the resultset once instead of having a roundtrip for each record.

Here is a sample of a server side cursor that would get created


DECLARE  @P1 INT
SET @P1 = 180150000
DECLARE  @P2 INT
SET @P2 = 8
DECLARE  @P3 INT
SET @P3 = 1
DECLARE  @P4 INT
SET @P4 = 109

EXEC Sp_cursoropen   @P1 OUTPUT ,
N'select name from sysdatabases
where name not in (''master'',''tempdb'',''model'',''pubs'')
and name not like ''%sqltech%''' ,
@P2 OUTPUT ,
@P3 OUTPUT ,
@P4 OUTPUT

SELECT @P1,@P2,@P3,@P4

 Then you will see numerous lines like the following:


exec sp_cursorfetch 180150000, 16, 1, 1

 When you execute the OPENQuery on the remote server this doesn’t occur.  It executes it once and returns the whole resultset to you.

Filed Under: Performance Tuning

Unable to modify tables in SQL Server 2008

03/02/2010 By

In Sql Server 2008, you may be unable to modify tables if they require a table rebuild.  The error you get is as follows:

Saving changes is not permitted.  The changes you have made require the following table to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be recreated.

 

In order to get around this error you have to click Tools–>Options–>Designers and uncheck the check box for Prevent saving changes that require table re-creation.

 

Filed Under: Tips

Installing SQL Server 2008 on Windows 2008 R2

03/01/2010 By

Today I was performing a new SQL Server 2008 install on a Windows 2008 R2 server.  I got 3 errors while performing the install and thought I would save everyone the trouble of searching the internet for all the workarounds so I included them all here.  The first error came when I tried installing the .Net framework via the SQL Setup.  It was not letting me install via the SQL install and I had to install the .Net framework in the features of the server management console.  You can do that by doing the following steps from this article

Filed Under: Database Administration, Tips, Upgrades