SQLTech Consulting

I’m not sure a lot of people are aware of this or not, but I figured I’d just let everyone know just in case.  Microsoft has a program available to small business startups, called Bizspark.  Bizspark a global program that provides Microsoft software, support and visibility for small companies that is basically free of charge for 3 years.  You pay $100 once you exit the program, but before that it’s absolutely free!  As with everything there are a few caveats, but for must of us they are pretty hard to hit when you are just starting out. 

The restrictions are as follows:

• Your company has to be privately held
• Your company has to be less than three years old
• You have to make less than $1 million annually
• And you have to develop software or provide support services  (consulting etc.)

You also get publicity that can lead to potential investors, clients, and partnerships etc.  With the MSDN subscription you can download fully functional software, ie SQL Server 2008 R2, Visual Studio 2010, Office 2010…the list goes on and on.  This is one of the best deals I think I have ever run across, so if you are not currently aware of it, and you are a new startup, check it out today. 

I know it’s been a while since I posted anything in my blog so I thought I would put something up today that gave me a little more trouble today then I thought it would.   There wasn’t much help on the internet for it either so perhaps this post will help others that have the same issue as I did today.  I was investigating a SQL Agent job that simply executed a stored procedure on a remote server via a linked server call.  The job was extremely sporadic.  It was running fine for weeks and then other times it would fail every day.  The query in the job when run manually in SSMS executes just fine in about 5 minutes.  The only consistent thing I was seeing was that it would only fail if it was running over 5 minutes and 37 seconds.  The error I was getting in the job failure was as follows:

Could not relay results of procedure ‘SSP_SAMPLE’ from remote server ‘LNKTEST’. [SQLSTATE 42000] (Error 7221)   [SQLSTATE 01000] (Error 7312).  The step failed.

At first glance I thought it must have been the remote query timeout setting for the server.   So in order to check this I took a look by running  sp_configure

The setting for remote query timeout (s) was set at 600.  This tells me I have the default setting which is a 10 minute timeout period for remote queries.  The query when ran manually runs for 5 minutes so that’s almost half the time of  the 10 minute timeout period so that wasn’t it.  What on earth could it have been then? Maybe it was missing a setting in the linked server or permissions, so I took a look at the security for the linked server.  Unfortunately it wasn’t going to be that easy for me either.  The login for the linked server was given the appropriate rights to execute the proc in the database and again, it was running manually just fine so it couldn’t have been permissions.  HMMMMM.  Maybe it was the RPC, or RPC Out permissions.  By default SQL sets those to false.  As you can see below though, they are set to true and again when run manually, it was working just fine.

I started investigating further and then I thought maybe it could be something else but that wasn’t it either.  I banged my head on my desk three times and then it hit me…..it was right there in front of my face.  Do you see it?  Look again?  It was the Query Timeout!  Someone set this to 337 in the linked server itself.  Normally by default this is set to 0.  I always keep it at 0 myself so I never thought to check it there.  Sometimes its just the silliest things that give us troubles, huh?  Funny thing was that there was no where on the internet where it was stated that this may be the cause.  It was just the fix for me, so if you are scowering the internet looking for a fix for the error above, and your using linked servers and can’t seem to find the issue…. just check the query timeout in the linked server itself.  It may be just the thing you were looking for. 

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.”

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 Server\100\DTS\Binn\dtexec.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:

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

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!

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

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.

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.

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

This week I was installing a new failover cluster at a client site.  Boy have there been a lot of changes regarding clustering between Windows Server 2003 and Windows Server 2008.  Microsoft has redefined clustering with Windows Server 2008, supposedly making it simpler and easier to implement, but I found it still quite an effort since a lot of the steps have changed.  I found this great article on the web that helped me to set everything up correctly so I thought I would share it here:

installing SQL Server on a Windows Server 2008 cluster

Have you ever wondered what edition of SQL Server you really needed for your specific project needs but you could never really remember where you can find the proper documentation to help you decide.  I was looking on the web today and it took me a while to find it myself so I thought I would share it with you all now.   Take a look at the following link and you can see for yourself. 

Features Supported by the Editions of SQL Server 2008

If you are interested in the costs of the various editions check out the following:

SQL Server 2008 Pricing

Over the weekend I was running into a blocking issue with one of my clients.  I was using SQL Server 2008 and I was used to using the current activity pane in 2005 etc. to find the lead blocker. This is now gone in 2008 with the new Activity Monitor.  I started to use a lot of the old ways to diagnose using the profiler etc but this is a very tedious process.  I found this issue I was working on lastnight and it is resolved now but I did some searching on the web today and I found a really great article by Cherié Warren .  Cherie does a great job describing the ways to do it in SQL 2008 so I would advise you all to take a look when you get a chance.  She includes two really great scripts and I have included them below. 

To gather Lock stats run the following query:

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance,
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

To review the objects run the following query:

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Did you know that with SQL Server 2008 you can now clear the query plan for a specific statement you are testing?  This is a wonderful addition for SQL Server 2008 as you do not have to drop everything in cache anymore.  You can just drop the query you are working on.  Look at the following code below from Books Online (BOL) and try it out yourself to see how it works:

USE AdventureWorks;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

plan_handle                                         text

--------------------------------------------------  -----------------------------

0x060006001ECA270EC0215D05000000000000000000000000  SELECT  * FROM Person.Address;

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
GO

For this month’s T-SQL Tuesday, we had a guest host Rob Farley  and we were asked to write a blog post about relationships:

As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!

Rules

As before, any blogger can–and should–feel free to participate in this event. In order to make the event slightly more international, I’m changing the time range from PST-based to UTC-based. So the rules are:

• Your post must go live between 00:00:00 UTC and 23:59:00 UTC on Tuesday, February 9, 2010
• Your post must link back to this one, and it’s recommended that you clearly identify the post as a T-SQL Tuesday post
• You are responsible for ensuring that a trackback or comment appears here so that I can find the posts

My Submission

For my post in this month’s TSQL2SDAY submission I chose to discuss SQL Server Diagrams.  This month’s focus was on Relationships so I figured the Diagramming in SQL Server would be a great topic to discuss.  A lot of people are aware of the capability to add relationships through foreign keys etc but I always found it much more tedious then it can be if we just used the Digraming features in SQL Server.  This makes things much more easier to apply relationships and to see how all the tables are relating as a whole.  It is also a free Entity Relationship modeling tool.  Let’s discuss the basics. 

1) First if you never used the diagrams before it will quickly ask you to install some needed files.  These will create some system tables that are used to hold the diagram information.  They used to be called Davinci Tables but I am not sure what they are called any more. 

2) Next we just right click on Diagrams and Select Create New Diagram.

3)From here we add the tables we are interested in setting up relationships for and then hit ok.

For my example we have two simple tables one part table and one order table.  Review them below:

Next we click on the ID in the Part Table and hold down on the mouse and drag the ID field to the Order table.  This will bring up the following screen shot where we select the Primary Key table and the foreign Key table and the field we are using for the relationship like below:

Next we specify The cascading Update or Deletes if we want that to occur and select whether we want the relationship to be checked on creation.

 Finally we can see the completed relationship and we can save the diagram.

.

This is a much easier way of adding relationships to our databases using a GUI tool that makes it a snap.  Try it out and see how much easier it is.