Wednesday, July 25, 2012

VS2010 Network BIOS Command Limit has been reached

Resolution :

1. Start Menu | Run | Regedit.exe
2. Create a DWORD key (32 bits) in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\parameters , name the key "MaxCmds" with a decimal value of 500.
3. Create a DWORD key (32 bits) in  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\parameters, name the key "MaxMpxCt" and give it a decimal value of 500.
4. Reboot the computer.

Friday, July 20, 2012

Ace oledb cannot be found with oledb connection (ssis)

Currently on my workstation I have Office 2010 64 bits, and Ace oledb driver 64 bits. Working in BIDS I could not find the Ace driver available to create a new Oledb Connection.

Since BIDS is 32 bits I had to :
  1. Remove Ace driver 64 bits
  2. Remove 64 bits Office Products
  3. Reinstall Ace 32 bits
  4. Reinstall Office 32 bits (!)
  5. In Bids, choose Project Properties and  in "Debugging"  set Run64bitsRuntime to "False".
Hope this helps :)

Monday, July 16, 2012

stop ssis package when a For Each loop enumerator is empty

Problem : You want to stop a package when a For Each Loop Enumerator is empty.

Solution : Add a OnWarning event to the for each loop checking for right Error code.

Steps :

1. Add an OnWarning Event handler to the For Each Loop Enumerator:

2. Add A Script component to the Event handler that can read the following system variables:



3. Add the following code to test if the For each loop is empty:


Thursday, July 12, 2012

SSIS Error : Failed to decrypt protected XML node DTS:Password" with error 0x80070002

error Message :
 Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error 

 I've had the same error and just solved it.
As well as changing the protection level of the packages to DontSaveSensitive, you also need to make sure that anywhere within the package that points to a network drive i.e. all connection managers, and within any execute package containers, all the the file paths do not start off with the letter of the drive, instead use the server name.

i.e. K:\foldername\inputfile.xls

becomes: -

\\ServerName\foldername\inputfile.xls

Wednesday, July 11, 2012

Unable to start mail session (reason: No mail profile defined) - Message in Error Log

Message
[098] SQLServerAgent terminated (normally)
This is: the most common message that will be logged when there is no "Mail Session" defined for the SQL Server Agent Alert System
.

To Fix this or to make this message disappear in the Error Log, you have to enable "Mail Session" and re-start the SQL Server Agent.
To Do this,


  • Connect to the Server
  • Right Click on the "SQL Server Agent" and go to "Properties"
  • Then Go to "Alert System" Tab
  • Make sure the check box "Enable Mail Profile" is checked
  • Click OK to exit
  • Re-start the SQL Server Agent Service

SQL setup fails to update - installer cache is missing files

installing the service pack you are receiving the following error message:


a link to correct this problem can be found here but still it is quite tricky.

You can download a file, FindSQLInstalls.vbs to gather the details of your SQL Server  installation. I had errors running the file at first , removing the comments at the top of the file solved the issue.

Mostly I found errors relating to missing path, both the original cd that was copied in a directory, and directory created  by the SQL server setup that were missing like d:\6e712df430a776566d80fe027ca507.

recreating the file to the original setup cd on the disk was mandatory, I tried to recreate the setup directory above (d:\6e712df430a776566d80fe027ca507) as a shortcut for the setup to relink source and destination files within  the c:\windows\installer directory, but the latter did not work.

I didn't have to copy all the files listed in the one created by the vbs script - I relaunched the Setup 4-5 times, looked for the missing files in the .txt file and finally went through the SP3 setup normally.

Steps:

  1. Go to http://support.microsoft.com/kb/969052 and download the FindSQLInstall.vbs script
  2. Using a command prompt (elevated privileges), run  Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt
  3. open the text file to view the details.
  4. Recreate the path to the original setup cd if required
  5. Try to rerun setup and look in the text file for the missing file, copy it in the %windir%\installer directory (serveral trials may be required if serveral files are missing as in my case)