The SQL Server instance specified in SSIS service configuration is not present or is not available

If you get this error when you are trying to expand the MSDB section in SSIS and your target SQL Server is clustered then this is probably because the default reference to an instance of SQL Server in the SSIS configuration file is pointing at “.” (which is the alias for local server). As the locally installed instance of sql server is only a node of a cluster and not a standalone sql server then “.” is not a valid server name.

To get a “.” server name you would have to be on the cluster, not the node machine

Simply edit the config file for SSIS and enter the name of the cluster instead.

e.g.

The file is at C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

and contains (by default)

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Change the <ServerName>.</ServerName> bit, replacing . with the cluster name.

You will now have to restart the SSIS service to force it to read the new configuration file. NOTE THAT YOU DO NOT HAVE TO RESTART THE BOX OR SQL SERVER! JUST SSIS.

Note: To edit the config file you may need to use an editor in administrator mode depending on the version of windows. Open notepad (or whatever) using right click and Run as Administrator then browse for the file.
I recommend turning off the hide extensions for known types. If you want to do this then use Windows explorer, press left ALT and F then on Tools menu choose Folder options to search for this option.

Sadly the rest of the information presented by the error message is a tad misleading but it is factually correct as the SQL Server really is not found (there is no local sql server in a cluster node)…


ADDITIONAL INFORMATION:

This might occur when there is no default instance of SQL Server on the computer.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server. (MsDtsSrvr)

Regards

Dave

Posted in Developer stuff, SQL Stuff | Tagged , , , , | 3 Comments

Attaching the Script debugger to process ‘[5836] iexplore.exe’ on machine ” failed. A debugger is already attached.

If you get this error message when running a c# application in Visual Studio 2010 by pressing F5 (Start Debugging) then the chances are you have multiple browsers installed and this has caused problems with the debugger.

—————————
Microsoft Visual Studio
—————————
Attaching the Script debugger to process ‘[5836] iexplore.exe’ on machine ‘machinename’ failed. A debugger is already attached.
—————————
OK
—————————

Debugging still works but you have to cancel the annoying message in Visual Studio every time.

To get rid of the error message simple close all browsers (leave VS 2010 running if you like) and then in a command window with admin privilege, simply enter the following command then continue debugging as you were before the problem started. Simple!

regsvr32.exe "%ProgramFiles(x86)%\Common Files\Microsoft Shared\VS7Debug\msdbg2.dll"

Posted in Developer stuff | Leave a comment

SharePoint startup time very slow

I created a SharePoint Server installation to look after project documentation. All was well and I uploaded all of the specifications, design documents, project plans etc. The following day, when I wanted to show the site to someone important, it did not work! I refreshed the browser several times thinking (correctly) that the application pool would have recycled overnight and there would be a delay as it re-built and re-loaded but this took forever. Several minutes later it did spring in to life but by that time, the business-minded (non-technical) client had already lost interest and walked away.

I was furious – SharePoint had made me look bad and I don’t like looking bad!

Perhaps I should have checked this just before the meeting but to be honest the meeting was not planned at that time. I had been put on the spot and was looking for something to impress. What annoyed me more is that by recycling the application pool myself, I as able to repeat the error many times over. This must be a bug I thought so I went to ask Uncle Google.

Why so slow?

The answer, and how David Klein found it, is contained in the following blog post link but I shall quickly explain the bit that matters.

Basically its due to a bit of “guilty til proven innocent” attitude from Microsoft.

Microsoft check for revoked certificates every time the application pool is recycled and to do this the site references a couple of URL’s at crl.microsoft.com

In my case, a network block was preventing this so the check takes a very long time before it times out, following which, the application loads.

David’s wonderful blog post pointed out the issue and suggested that if I add the crl.microsoft.com address to the hosts file and point it locally (127.0.0.1) then this will result in an immediate revoked certificate lookup failure and massively reduce startup time.

This type of lookup redirection hack is often used to install illegal software by preventing it from registering etc. (a lot of software calls home during the install). In this case Microsoft decided to check much more often.

There may be implications to applying this workaround in some cases, such as notification of updates etc. but if it fixes the startup time then it might be worth it.

I think it’s a brilliant workaround but on account of other features that might exist now or in the future at crl.microsoft.com it may be better to schedule the recycle for out of hours time and force an immediate refresh when nobody is looking (to reload the pool)
What happened to innocent until proven guilty?
It would appear that software companies these days reverse this and everyone pays the price.

So did it have the desired effect? You bet!

Before : 2 minutes
After : 20 seconds

Now that was a good tip. Thanks David.

Posted in Developer stuff, Operating Systems, Other stuff | Tagged , , , | 3 Comments

Windows Server 2008 R2 – Full or Server Core

I did not know which to install so I looked around and found this very entertaining and yet informative blog post. I like the fact that the post is attempting to add a humorous and more interesting spin on a very boring subject.

http://www.trainsignal.com/blog/windows-server-2008-server-core-full-install

Kudos to you Brian

For a most official version head here and check out the bullet lists.

http://technet.microsoft.com/en-us/library/dd184075.aspx

Cheers

Dave

Posted in Developer stuff, Operating Systems | Tagged , , , , | Leave a comment

Sharepoint database connection string – what is it?

I use Team Foundation Server (TFS) for source control of my current data warehouse project with SSIS, SSRS and SSAS but my requirements documents which are mostly word but also Excel, Visio and xMind (Excellent mind mapping diagram software) were simply held on a file share. To get these into TFS requires Windows Sharepoint Server which I had not yet installed. Mapping the Sharepoint to the TFS is a nightmare though with terrible supporting documentation which I will cover this in another post. Anyway, I digress: As part of the process though I wanted to view the Sharepoint Services database in SSMS and could not remeber how to do this and had to look it up which is annoying as I have done this before so I thought I would write a simple post here mainly for my own future reference of what the ridiculous connection string looks like

Windows Sharepoint Services version 3.0 is the free version that you can plug in to TFS to manage document version control.

Here’s how you can connect to the database…

Open Management studio on the server (not a remote connection)
Put this in the server name
\\.\pipe\mssql$microsoft##ssee\sql\query
use windows authentication

Note:
“.” is the name of the server and means local machine
Windows Authentication must be used as this sql server is owned and created by the Sharepoint Installation
SQL Authenticated cannot be used. You can make one but you still cannot use it on this type of database so there isnt much point.
You must be on the local machine too as remote connections cannot be enabled on this type of SQL Server database
(I bet you can hack both of these restricts by enabling editing of the system catalogs and changing a few settings – never tried it so don’t blame me if you break it)

This connection string also works for the 2007 Sharepoint databases

and before you post a comment on this – I know this is not a connection string really – its the string that you put in the server name but you can’t really call it a server name can you? (its a bit weird for that, which is why its forgettable)

For 2010 its a simpler affair with just
.\sharepoint
but thats only for the fully paid version which I don’t have as I don’t need the features at the moment.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | 5 Comments

SSAS: Excel Data Connection Wizard missing cube(s)

This gallery contains 1 photo.

Have you got missing cubes in your Excel Data Connection Wizard? A customer noticed this yesterday and had spent all afternoon looking for the issue, rebuilding, deploying, processing, deleting and recreating on the server etc. all to no avail. He … Continue reading

Gallery | Tagged , , , , , , , , , , | 4 Comments

Excel Error: There was a problem sending the command to the program

Excel Error: There was a problem sending the command to the program

15 August 2012

If you click on an excel file and get this error instead of Excel opening your document and you have followed the MS link to disable the DDE apps already but it is still failing then you probably are victim of a Windows Update issue.

On 15th August it would appear that Office was security patched and if you have Power Pivot installed as an Excel Add-in then you get this error whilst it loads power pivot.

Security Update for Microsoft Office 2007 suites
A security vulnerability exists in Microsoft Office 2007 suites that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability. http://support.microsoft.com/kb/2596856)

I don’t know what the solution is to this yet so its probably just a case of waiting for MS to issue another patch but in the meantime, if you can, disable the Power Pivot Add-in and Excel will load documents properly again.

open Excel – Goto File – Options – Add-ins – at the bottom choose to manage COM add-ins and go – Uncheck the Power Pivot Add-in in dialog and click OK

If, like me, you are a Business Intelligence developer and need Power Pivot then you will not be too happy about this so please urge MS to supply a fix soon. You can re-enable the plugin as and when you need it though until this is fixed.

Hope this helps someone

Dave
Excel will now start super fastbut you cannot use the power pivot.

Knowledgebase artice referenced above = http://support.microsoft.com/kb/211494

Posted in Developer stuff, Other stuff, SQL Stuff | Tagged , , , , , , , | 1 Comment