SQL Server 2008 Cluster installation on Windows 2008 Enterprise Server

SQL Server 2008 Cluster installation on Windows 2008 Enterprise Server
By Jeff Han 2009.09.30

1. Windows 2008 cluster ring installation

When you are trying to create windows 2008 cluster ring, you will get error like this:
“An error occurred while creating the cluster. An error occurred creating cluster ‘vanpgsql08clus’. The network location cannot be reached.”

It looks like 2008 Cluster requires a system share for it to be created. However, by default windows 2008 server system share was disabled by a registry key. In order to enabling system share, you have to modify this key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\
“AutoShareServer”=dword:00000000
To:
“AutoShareServer”=dword:00000001
After that the cluster can be created successfully.

Cluster ring configurations:

Cluster Name: vanpgsql08clus IP address: 10.165.21.132
Node1: vanpgsqlc08n1 IP address: 10.165.21.133
Node2: vanpgsqlc08n2 IP address: 10.165.21.134
MSDTC: vanpgsql08cldtc IP address: 10.165.21.135

2. SQL Server 2008 cluster installation
The SQL Server 2008 cluster installation is quite different than earlier versions. It doesn’t do the installation for the remote nodes. (See more at Ref. 1)
There are two installation options:
* Integrated failover cluster install with Add Node
SQL Server integrated failover cluster installation consists of two steps:
1. Create and configure a single-node SQL Server failover cluster instance. At the completion of a successful configuration of the node, you have a fully functional failover cluster instance. At this time it does not have high-availability because there is only one node in the failover cluster.
2. On rest nodes (NOT ON FIRST ONE) to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.

* Advanced/Enterprise installation
SQL Server Advanced/Enterprise failover cluster installation consists of two steps:
1. On each node that will be part of the SQL Server failover cluster, run Setup with Prepare Failover Cluster functionality. This step prepares the nodes ready to be clustered, but there is no operational SQL Server instance at the end of this step.
2. After the nodes are prepared for clustering, run Setup on the node that owns the shared disk with the Complete Failover Cluster functionality. This step configures and completes the failover cluster instance. At the end of this step, you will have an operational SQL Server failover cluster instance.

In this case we used option 1 – “integrated failover cluster install with add node”

Step1: usually we start installation by run “setup” to bring the GUI interface. However, sometimes a fully functional cluster ring couldn’t pass the sql server 2008 validation rule which will block the installation. In our case it’s blocked, so we have to skip this validation rule to let installation carry on (see more at ref.6):
Here are some commands to bypass the rule check:
* For an integrated failover setup, run the following command on each node that is being added:
Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
* For adding node to integrated installation instance
Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
* For an advanced or enterprise installation, run the following command:
Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverCluster
So the first step here we run (c:\temp is my installable location):
C:\temp\Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
We use the configuration as below:

Cluster mode: Active – Passive two nodes cluster
SQL Network name: vanpgsqlc2k8vip IP address: 10.165.21.136
Service Account: pgdev\service.pg.prod
Cluster domain group: pgdev\sqlcluster
SA password: 4Sqlserver

Step 2: According to the integrated failover cluster installation method, we have to run add node(by GUI/Command line) on second node to add this node into cluster. Because of the validation problem we run this here on node 2:
C:\temp\Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
However, Microsoft gave us an error again:
“The current SKU is invalid”
This is actually a installation bug (see more in ref. 7), so we download the hotfix (SQLServer2008-KB973601-x86), patched first node and second node(maybe not necessary). The patch apparently will failed on first node, but it’s ok you can bring the services up manually in cluster admin GUI.
After patching the installation worked well.

Step3: testing failover between two nodes
No problem at all

Reference:
1. “Getting Started with SQL Server 2008 Failover Clustering”
http://msdn.microsoft.com/en-us/library/ms189134.aspx

2. “Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation”
http://www.databasejournal.com/features/mssql/article.php/3771296/Setting-up-a-Two-node-SQL-Server-2008-Cluster-from-the-Command-Prompt—Preparation.htm

3. “Setting up a Two-NODE SQL Server 2008 Cluster from the Command Prompt – Integrated Installation”
http://www.databasejournal.com/features/mssql/article.php/3772826/Setting-up-a-Two-NODE-SQL-Server-2008-Cluster-from-the-Command-Prompt—Integrated-Installation.htm

4. “Installing a Two-node SQL Server 2008 Cluster – Advanced option”
http://www.databasejournal.com/features/mssql/article.php/3783326/Installing-a-Two-node-SQL-Server-2008-Cluster—Advanced-option.htm

5. “Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1-4”
http://www.mssqltips.com/tip.asp?tip=1687

6. “Cluster validation rule of the setup process failure workaround”
http://support.microsoft.com/kb/953748

7. “Error message when you try to add a second node to a SQL Server 2008 failover cluster: “The current SKU is invalid””
http://support.microsoft.com/kb/957459/

enable a disabled sql agent service

When SQL Server 2005 Management Studio’s Object Browser shows the SQL Server Agent service with a red down arrow and the text Agent XP’s disabled, the service is not started or disabled.
i had this issue on my sql 2005 active-active cluster even with sql agent resource started in my cluster admin interface, the agent actually was not running.

To fix this, you need to open the sql server advance configuration and enable sql agent, then close the advance configuration option again:
–step 1. open advanced configuration option
exec sys.sp_configure @configname = N’show advanced options’, @configvalue = 1
reconfigure with override

–step 2. change value from “0″ to “1″ which means enable
exec sys.sp_configure @configname = N’Agent XPs’, @configvalue = 1
reconfigure with override

–step 3. close advanced configuration option
exec sys.sp_configure @configname = N’show advanced options’, @configvalue = 0
reconfigure with override

table usages for ms sql database

This STP help us find usage for any objects (includes table)
sp_spaceused [object_name]

By combining other things you got full table usage list from db
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?’”

–or

select ‘exec sp_spaceused ‘+name from sys.tables order by name;

MS SQL Server 2005 alias doesn’t work on 64bit platform

we recently upgraded our 1 cluster from 32bit platform to 64bit platform.
In order to launch the replication monitor we have to use sql alias name which has been set up on “sql server configuration Manager” -> “sql native client configuration ” -> “alias”.

for 32bit sql client, there is only one option to set up alias. so from 32bit client to connect to 64bit server by using alias, not a problem!

However, if you like me are using 64bit sql client, and happen to set up the alias under “sql server configuration Manager” -> “sql native client configuration ” – “alias”, you will fail to connect to the server by using this alias.
The right spot you setup the alias should be “sql server configuration Manager” -> “sql native client configuration(32bit) ” – “alias”.

i have no idea why on 64bit client you still need to use 32bit client configuration for alias connection. But that is bloody truth:)

Note: if you are using sql server 2005 cluster then you have to keep the same alias in both of 32bit and 64bit client configuration. Otherwise, by missing alias in the 64bit one, you will get trouble with all the jobs and logs

How to make user to view they jobs in sql 2005

i tried to make user able to see their own jobs in sql 2005. First thing you need to assign job owner as user’s account.

Second thing, sql 2005 server has some new server roles (on MSDB database user only!) for managing sql agent stuff. Here is the screenshot:

With SQL Server 2005, a user must be a member of one of the new fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. After this basic introduction let us identify what are the specific uses of each role.

SQLAgentUserRole

This is the least privileged role among new three new fixed server roles. This role has permissions only on operators, local jobs, and job schedules. These types of users can only view the operator but they can’t create, modify or delete operators. They cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. These users cannot delete job history.

SQLAgentReaderRole

SQLAgentReaderRole has all the options that SQLAgentUserRole has. In addition, it does have permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.

SQLAgentOperatorRole

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server.

Link: http://www.sqlservercentral.com/articles/Administration/sqlserveragent2005/2376/

How to connection sql server named instance SSIS service

I’ve occasionally run into the following error when attempting to connect to SSIS from Management Studio:

"Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr)."

The problem, as it turns out, is that when I installed SQL Server 2005/2008 on a machine, that a default instance was already installed. In my case SQL Server 2000 was already installed as the default instance. The initial posts I read (and heard about in talking to other colleagues who had the same problem) said that SQL Server 2005/2008 had to be installed as the default instance  if you wanted to be able to use SSIS.

The problem is that unlike the database engine, which can have many running named instances running, SSIS can only run one instance on a server at a time. When SQL Server 2005/2008 is installed there is no option to specify which SQL Server instance should be used to run SSIS. Instead the default configuration is always applied and SSIS tries to start under the default instance. If the default instance is SQL Server 2000, you have a problem. You can only run SSIS on SQL Server 2005 or higher.

Since a server can only run one instance of SSIS you need to make sure that the config file points to the correct instance of SQL Server. Locate the MsDtsSrvr.ini.xml configuration fileand open it with a text or xml editor.

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

The default contents of the file:

<?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>

Notice the ServerName element points to the default instance of SQL Server (when connecting to the local machine “.” is the same as “(local)” – they both use NamedPipes to connect).

Modify the configuration file to point to the named instance as follows.

<?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>.\SQL2005</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

After making the change you MUST restart the SSIS service for the change to take effect.

Another normal issue to connect to SSIS in sqlserver 2005 is “access is denied”. The following workground can resolve this issue(http://msdn.microsoft.com/en-us/library/aa337083.aspx):

1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
5. Right-click on MsDtsServer and select Properties.
6. In the MsDtsServer Properties dialog box, select the Security tab.
7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9. Click OK to close the dialog box.
10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
11. Close the MMC snap-in.
12. Restart the Integration Services service.

sql server cluster re-ip best practise

Changing IP addresses for sql server 2005/2000 cluster basically bases on two steps:
1. changing virtual IP addresses in cluster administrator ( and registry for sql server 2000)
2. changing physical IP addresses

1. Changing Virutal IP addresses in cluster administrator
1).connect to cluster administrator and take all the group resource offline.
2).changing the virtual IP addresses by right clicking every item with ip address resource type, then “property”->”parameter” to change.
Usually for an active-active sql cluster environment, you need at least change three virtual ip address:
Two for each node’s virtual IP, one for cluster management IP.

Caution: for sql server 2005 cluster, after you changing the IP address in cluster administrator, the registry entries for these ip also get updated automatically.
But for sql server 2000 cluster, you have to update these in registry manually in these locations on both nodes:
For a default instance of virtual SQL Server, the following registry value points to a previous IP address-> HKLM\Software\Microsoft\MSSQLServer\Cluster
For a named instance of virtual SQL Server, the following registry value points to a previous IP address->HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\Cluster\ClusterIpAddr

Reference:
“when you change the IP address on a SQL Server failover cluster node: “Bind failed”" http://support.microsoft.com/kb/319578/EN-US/

2. changing physical IP addresses in windows network properties.
Nothing new here just regular IP changing process.

3. reboot the server.

4. bring the resource online.

5. Verify all the database connections and applications.
Tips: to verify the port binding after ip changing, you can do port scan for the virutal server name of both nodes to see if the designated port are opened for connection.
For unix: nmap -p1433 hostname
For windows: netstat

error for dynamic sql on sql server

i made a Stored procedure for dropping tables older than 6 months, it was running ok by failed recently and the error is:
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’

Here is the STP itself:
USE [OLAPPERF]
GO
/****** Object: StoredProcedure [dbo].[stp_removetbls] Script Date: 02/09/2009 12:52:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[stp_removetbls]
– Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON

DECLARE @table_name varchar( 128 )

DECLARE cur_tablenames CURSOR FOR select name from olapperf.sys.all_objects where type = ‘U’ and create_date<dateadd(month,-6,getdate())

DECLARE @sqlstring varchar(1000)

OPEN cur_tablenames
FETCH NEXT FROM cur_tablenames INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlstring = ‘drop table ‘+ @table_name

exec sp_executesql @sqlstring

FETCH NEXT FROM cur_tablenames INTO @table_name
END

CLOSE cur_tablenames
DEALLOCATE cur_tablenames
END

After searching the solution i found it is very easy, like here said(http://dotnetjunkies.com/WebLog/richard.dudley/archive/2004/09/29/27169.aspx):
If you are building dynamic SQL, you should declare your parameter as one of the accepted types (e.g., declare @sql nvarchar(4000)). If you are using a direct statement as your query, you need to preface it with the letter N, as seen below.

incorrect:
execute sp_executesql ’select * from pubs.dbo.employee where job_lvl = @level’,
‘@level tinyint’,
@level = 35

correct:
execute sp_executesql N’select * from pubs.dbo.employee where job_lvl = @level’,
N’@level tinyint’,
@level = 35

So what i did is just change my sqlstring definition from varchar(1000) to nvarchar(1000), done! Everything works again.

How to Run a DTS Package as a Scheduled Job

SUMMARY
A problem that you might frequently encounter with a Data Transformation Servic…
A problem that you might frequently encounter with a Data Transformation Services (DTS) package is that the DTS package runs error-free from the SQL Server Enterprise Manager, but the DTS package fails when it is scheduled to run as a job. Usually, this occurs because of a difference in the security context when the package is run as a job versus when the package is run interactively.

This article explains the security issues involved in running DTS packages.

Some Terms Description
———————-
DTS: Data Transformation Services
SQL authentication: A security system that is based on Microsoft SQL Server logins and passwords.
Standard security: See SQL authentication.
SQL Server authentication: See SQL authentication.
Microsoft Windows NT authentication: When a user connects through a Microsoft Windows user account, SQL Server verifies that the account name and password were validated when the user logged on to a computer running Microsoft Windows NT, Microsoft Windows 2000, Microsoft Windows 95 or Microsoft Windows 98.
Integrated security: See Microsoft Windows NT authentication.
Windows NT or Microsoft Windows 2000 authentication: See Microsoft Windows NT authentication.
Microsoft Windows NT account or login equals Windows login or account: Same as Microsoft Windows NT login account or Microsoft Windows 2000 login account.
SQL Agent: SQL Server Agent Service
SEM: SQL Server Enterprise Manager

#1. Where is the DTS Package Running?
One problem that is frequently reported about DTS packages is the difference in behavior when a package is run from the SQL Enterprise Manager versus when the DTS package is scheduled as a job. When you run the package from the DTS Designer in SQL Enterprise Manager (SEM), the package is running on the computer where you are seated. If you are at the server (either physically, or through remote access software), the package is run on the server. If you are seated at a workstation and you have registered the SQL Server server in Enterprise Manager, then the package is run on the workstation. The security context of the package is that of the Windows NT account you used to log in to that computer. When the package is run as a scheduled job, the package is always run on the server.

Frequently, a developer creates and tests the DTS package interactively on their workstation through the DTS Designer in Enterprise Manager. After the DTS package is debugged, the package is then scheduled as a job. This changes the location of the package from the developer’s workstation to the server. If the package was loading text data into SQL Server, the package fails unless the text file and the path to the file exist on the server. If the package was connecting to another server, the package fails if the security context of the job does not support the connection.
Who Owns the Job That Runs the DTS Package?
Packages are scheduled by creating a job that is managed by the SQL Agent service. This job, as any other scheduled job, has an owner. The owner can be either a SQL Server login or a Windows NT account.

To determine the owner:

* Double-click the job in Enterprise Manager and then look at the Owner drop-down combo box.

-or-

* Run the msdb.dbo.sp_help_job system stored procedure.

SQL Server 2000

The security context in which the job is run is determined by the owner of the job. If the job is owned by a login that is not a member of the Sysadmin server role, then the package is run under the context of the account setup as the SQL Agent Proxy Account, and has the rights and permissions of that account.

For SQL Agent Proxy to be able to run jobs that connect to SQL Server, the SQL Agent Proxy account must have proper Windows/NT permissions and be granted login access to SQL Server with appropriate database permissions. For the jobs that execute a DTS package, the SQL Agent Proxy Account must have read and write permissions to the temp directory of the Account the SQL Server Agent is running under. For example,
c:\Documents and Settings\\Local Settings\Temp
If the job is owned by an account (either a SQL Server login or a Windows NT authenticated login) that is a member of the Sysadmin role, the SQL Agent job runs under the context of the account used to start the SQL Agent service.

Also, if the job is owned by a Windows NT domain account and if the package is stored in the SQL Server or SQL Server repository (not as a file), you must start the SQL Server service by using an account from the same domain or an account from a trusted domain. For example, if the SQL Agent job is owned by an account from the USA domain, then the account used to start the SQL Server service must be either from the USA domain or a domain trusted by the USA domain. If the SQL Server is started using a local account, the package fails to run.

#2. What Determines the Owner?
Question: When you right-click the DTS package and you choose to schedule the package, how is the owner assigned?

Answer: The owner of the SQL Agent job depends on how the SQL Server is registered in the Enterprise Manager. If the SQL Server is registered using a Windows NT authentication, the owner of the scheduled job is the account used to start the SQL Agent service. If the SQL Server is registered in SEM using SQL Server authentication (for example, the SA login), the owner of the job is that same SQL Server login.

To change the ownership of the package:

1. Double-click the job in Enterprise Manager.
2. Click the General tab, and then click the Owner drop-down combo box.

You can also use the msdb.dbo.sp_update_job system stored procedure to change the ownership of the package.

#3. How is the DTS Package Launched?
If you manually run a package by using the DTSRun.exe command line utility, the security context is that of the Windows account you used to log in to the computer. If you run the package by using DTSrun.exe through the xp_cmdshell extended stored procedure, the package is run in the context of the account used to start the SQL Server service, provided that the user that executed xp_cmdshell is a member of the Sysadmin role. If the user that executed xp_cmdshell is not an account in the Sysadmin role, then DTSRun.exe runs in the context of the SQLAgentCmdExec account.

If the SQL Server was started using the Local System account, the DTS package has no permissions outside of the computer that is running SQL Server.

If the SQL Server service is started under a Windows NT account, the package has the same rights and permissions as that Windows NT account. If that Windows NT account is a local machine account (as opposed to a domain account), the package does not have any rights outside of that computer. If the Windows NT account is a domain account, the package may be able to access resources on many different computers on that domain.
How are Windows NT-authenticated Connections Made?
Sometimes a DTS package contains an object that makes a connection to a data source using Windows NT authentication. The security context used for this connection is the same as the context of the package that is running. If the package is run from a command prompt by using DTSRun.exe, the credentials of the currently logged-in Windows NT account is used. If the package is run as a SQL Server Agent job, then the integrated security connection is made using the account you used to start SQL Agent (assuming that the owner of the package is a member of the Sysadmin role).

#4. Common Problems
Here are a few other common problems you might encounter when you run DTS packages as scheduled jobs in SQL Agent:

1). OS level Security permission error (this is the most common error for DTS job, most users failed on this)
Error:
Executed as user: xxx\yyy. The process could not be created for step 1 of job 0xE04DF638883CED43BC09B920B39D43A4 (reason: A required privilege is not held by the client). The step failed.
I was searching on the internet and ms kb for a quite long for finding this “required privilege”. Unfortunately microsoft failed to mention this. After a 3-hour call with MS support Engineer, finally i got the solution:
The proxy account as well as the SQL Server startup account have to be enabled on these security policies in Local Security Policy(use “secpol.msc” to get interface, it’s in “security settings”->”local policies”->”user rights assignment” ):
1. Act as part of the Operating System
2. Log on as a service (SeServiceLogonRight)
3. Log on as a batch job (SeBatchLogonRight)
4. Replace a process-level token (SeAssignPrimaryTokenPrivilege)
5. Bypass traverse checking (SeChangeNotifyPrivilege)
6. Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
After that, restart sql agent service and you’re done!

2). Mapped Drives
If the package relies on the physical location of a file designated by a mapped drive letter, the package may fail when it is run as a scheduled SQL Agent job, regardless of who owns the package. SQL Agent is a Windows NT service and Windows NT services cannot see mapped drive letters. The mapping is part of the user’s profile that is loaded when a user logs on to a Windows NT session. Services do not work with user profiles. Use a UNC path instead of a mapped drive letter. For additional information about why a service cannot use a mapped drive, click the following article number to view the article in the Microsoft Knowledge Base:
180362 (http://support.microsoft.com/kb/180362/ ) INFO: Services and redirected drives

3). Relative Path
A relative path (or drive letter) is specific to the current location of the package (like C:\). If a package is designed on a workstation and is then scheduled, the location from which the package is run changes. The drive letter paths now reference a different physical location, that of the server. Unless the referenced files are also moved to the server, the package execution fails.

4). COM Components in ActiveX Scripts
If COM components (for example, calls to Microsoft ActiveX Data Objects (ADO), Remote Data Objects (RDO), or Decision Support Object (DSO) objects) are called in an ActiveX script, the called components must exist on the computer from which the DTS package is being run. If you run the package from the DTS Designer in SEM or DTSRun.exe, the components must exist on the computer at which you are seated. If the package is scheduled to be run by SQL Agent, the called components must be loaded on the computer hosting the SQL Server.

5). Package Security
DTS packages can have owner passwords and user passwords. These passwords affect who can edit and run the packages. Neither of these affect the security context in which the package is run.

6). SQLAgentCmdExec Permissions
If the job is executed under the context of the SQLAgentCmdExec account, and the SQLAgentCmdExec account does not have login rights to the SQL Server, the job may fail with the following error message:
DTSRun: Loading… DTSRun: Executing… DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217843 (80040E4D) Error string: Login failed for user ‘NT_name\SQLAgentCmdExec’. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user ‘NT_name\SQLAgentCmdExec’. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

MS KB Link:
http://support.microsoft.com/kb/269074
http://support.microsoft.com/kb/918760

Find Long running queries in SQL2005

The following query shows you the long running queries in the database by combination of few dynamic management views. It’s quite useful to get the “bottleneck” on-the-fly when your system is super busy.

Select dmText.text as 'Executed Query',dmStats.last_execution_time as 'Last Executed Time', dmstats.*
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
total_elapsed_time desc,
dmStats.last_execution_time desc