SQL DBA - TSQL to find all TSQL/Commands running on a server at a time?

SELECT sqltext.TEXT,

req.session_id,

req.status,

req.command,

req.cpu_time,

req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

SQL DBA - TSQL to get current location of all data and log files of all databases of the server

SELECT name, physical_name AS current_file_location

FROM sys.master_files

SQL DBA - Get Complete Information of a SQL Server

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

CONVERT(CHAR(100),SERVERPROPERTY('ResourceLastUpdateDateTime'))AS ResourceLastUpdateDateTime,

CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion,

CASE

WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1

      THEN 'Integrated security'

WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0

      THEN 'Not Integrated security'

END AS IsIntegratedSecurityOnly,

CASE WHEN SERVERPROPERTY('EngineEdition') = 1

      THEN 'Personal Edition'

WHEN SERVERPROPERTY('EngineEdition') = 2

      THEN 'Standard Edition'

WHEN SERVERPROPERTY('EngineEdition') = 3

      THEN 'Enterprise Edition'

WHEN SERVERPROPERTY('EngineEdition') = 4

      THEN 'Express Edition'

END AS EngineEdition,

CONVERT(CHAR(100), SERVERPROPERTY('InstanceName'))AS InstanceName,

CONVERT(CHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))AS ComputerNamePhysicalNetBIOS,

CONVERT(CHAR(100), SERVERPROPERTY('LicenseType'))AS LicenseType,

CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses'))AS NumLicenses,

CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion'))AS BuildClrVersion,

CONVERT(CHAR(100), SERVERPROPERTY('Collation'))AS Collation,

CONVERT(CHAR(100), SERVERPROPERTY('CollationID'))AS CollationID,

CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle'))AS ComparisonStyle,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('EditionID')) = -1253826760

      THEN 'Desktop Edition'

WHEN SERVERPROPERTY('EditionID') = -1592396055

      THEN 'Express Edition'

WHEN SERVERPROPERTY('EditionID') = -1534726760

      THEN 'Standard Edition'

WHEN SERVERPROPERTY('EditionID') = 1333529388

      THEN 'Workgroup Edition'

WHEN SERVERPROPERTY('EditionID') = 1804890536

      THEN 'Enterprise Edition'

WHEN SERVERPROPERTY('EditionID') = -323382091

      THEN 'Personal Edition'

WHEN SERVERPROPERTY('EditionID') = -2117995310

      THEN 'Developer Edition'

WHEN SERVERPROPERTY('EditionID') = 610778273

      THEN 'Enterprise Evaluation Edition'

WHEN SERVERPROPERTY('EditionID') = 1044790755

      THEN 'Windows Embedded SQL'

WHEN SERVERPROPERTY('EditionID') = 4161255391

      THEN 'Express Edition with Advanced Services'

END AS ProductEdition,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('IsClustered')) = 1

      THEN 'Clustered'

WHEN SERVERPROPERTY('IsClustered') = 0

      THEN 'Not Clustered'

WHEN SERVERPROPERTY('IsClustered') = NULL

      THEN 'Error'

END AS IsClustered,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('IsFullTextInstalled')) = 1

      THEN 'Full-text is installed'

WHEN SERVERPROPERTY('IsFullTextInstalled') = 0

      THEN 'Full-text is not installed'

WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL

      THEN 'Error'

END AS IsFullTextInstalled,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet'))AS SqlCharSet,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName'))AS SqlCharSetName,

CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder'))AS SqlSortOrderID,

CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName'))AS SqlSortOrderName

ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))

SQL DBA - Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass'

TITLE: Microsoft Visual Studio

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

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

Solution

To fix the problem do the following:

1.       Open the command prompt

2.       Navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Binn

3.       Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll

That should fix the problem.