Installing and configuring the ArcSDE service for SQL server is an easy job, but sometimes the ArcSDE service which is dependent on SQL server stops due to server restart or due to user shutdown. This happens even though the ArcSDE service is set to automatic start in case there is a server reboot.

Problem: The ArcSDE 9.3 service running on SQL server cannot be started. When attempted to start from GUI (cmd ->services.msc -> rt click esri_sde service -> start), it will show an error message that "the ArcSDE service started and stopped. This may happen due to the fact that some services may stop as they have no work to do"

Solution/Approach:

1. run -> cmd -> sdeservice -o list (this will list the status of the sde service running)
Note: to see the status of the service use ------ sdemon -o status -i 5151 ------
2. sdemon -o start
Provide the sde password that was created during the ArcSDE installation.

This will start the service !!!

I came across a very minor issue, but often not scrutinized and this can lead to major trouble !!

Problem: After the SP3 installation, the report server was not available to the application accessing it. i.e the user (report user: it is a fixed user used to pull out reports -- user1) was denied access to the database.

solution:

1. login to the server where your report server is installed.

2. go to Administrative tools --> IIS manager --> on left part Expand local computer --> websites --> default websites --> reports --> rt click --> properties --> DIRECTORY SECURITY --> Authentication and access control --> EDIT --> CHECK THE CHECK BOX at top part [enable annonymous access]

This happens during the installation of service pack and the ""enable annonymous access"" becomes unchecked !!!


This fixes the issue !!

SQL server SP3 installation problem

Recently, I installed SP3 for SQL server 2005. The installation went successful, but after the installation the maintenance plans were failing.

Problem: The maintenance jobs are failing after the SP3 installation on the test server.

Error Message: Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'.


Solution/Approach: This message is generally due to the unregistered COM in the dts.dll file in the \90\binn folder of the SQL server installation folder.

On Server follow below steps to fix the issue:

1. start -> run -> cmd
2. cd c:\Program Files\Microsoft SQL Server\90\DTS\binn
3. REGSVR32.EXE dts.dll

This fixes the issue !!

SSIS package fails to execute when scheduled as a job

Recently, I was creating an SSIS package and the package when executed in the BIDS environment was successfully run, but when scheduled through a job in SSMS gave me an error:
Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0×8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-09-15 16:34:32.57 Code: 0xC0202009 Source: TextFileImport Connection manager “DecipherDestination” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Description: “Login timeout expired”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0×80004005 Descrip… The package execution fa… The step failed.

Meanwhile I came across a good post describing the same situation:

http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/

Thanks to the original poster !!