How to run DTS from Stored Procedure

You can run DTS package from the client PC by these ways.

• Run DTSRUN.exe (It is provided as DTS utility.)
• Call the method in DTS object
• Call stored procedure (Stored procedure is necessary to be prepared in server side.)

The first 2 ways are necessary to install component in client. So I'll introduce the last way this time. There are two ways to run DTS from stored procedure.

Run DTS by xp_CmdShell

1. Create the command file (.bat or .vbs and so on) to execute DTSRUN
2. Create the stored procedure to call xp_CmdShell to execute the command file which is created in step1
3. Call the stored procedure

Run DTS by SQLServer Agent

1. Create SQLServer Agent job to run DTSRUN
2. Create the stored procedure to call sp_start_job
3. Call the stored procedure

CREATE PROCEDURE [ExecuteDTS_TestPackage] AS
EXECUTE msdb..sp_start_job @job_name = 'TestPackage'

GO

SQLServer : How to count the length of byte

SQLServer have no funtion to count the length of byte like LENB. This SQL provide same function. *TEST:VARCHAR(25)

DATALENGTH(CONVERT(VARCHAR(25), TEST))

SQLServer : Lock Status

You can see the lock status by sp_lock.

EXEC sp_lock
GO

Type on the result of sp_lock

Type	Description
DB	Database
FIL	File
IDX	Index
PG	Page
KEY	Key
TAB	Table
EXT	Extent
RID	Row ID

Improve your work efficiency #1

If you would like to improve your work efficiency, the management of information become the key.
Many part of your task is spent to search some information.
Even if you are incredible skilled engineer, you will need the information in web.

To improve the work efficiency, you should install Firefox which you can use instead of Internet Explorer. Firefox provide the smooth and safe browsing.

Firefox have strong relationship with Google services. You can search and find the information which you want to know by Google toolbar easily.

Download Firefox -Free

You can add various Add-on to add the function to Firefox.
I'll introduce those Add-on someday.

SQLServer : How to count the number of records at each table

This is the sample SQL to count the number of records at each table on current database.

SQL:Table for the result

CREATE TABLE [dbo].[TEST](
  [TableName] VARCHAR(255) NOT NULL,
  [NrOfRecords] [int] NOT NULL
) ON [PRIMARY]

SQL:Count the number of records

DECLARE @TABLE_NAME VARCHAR(255)
DECLARE @SQL        VARCHAR(8000)

DECLARE C_TABLE CURSOR FOR 
SELECT 
  name 
FROM 
  sysobjects 
WHERE 
      OBJECTPROPERTY(id, N'IsUserTable') = 1 
  AND status >= 0 
ORDER BY 
  name

OPEN C_TABLE
FETCH NEXT FROM C_TABLE INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM C_TABLE INTO @TABLE_NAME
  PRINT @TABLE_NAME
  SET @SQL = 
      '
        INSERT INTO TEST
        SELECT ''' +
          @TABLE_NAME + ''' AS TableName
          ,COUNT(*) AS NrOfRecords
        FROM ' +
          @TABLE_NAME
      
      EXEC (@SQL)
END

CLOSE C_TABLE

SQLServer : Tables and Columns list

This SQL returns userr tables in current database and their columns.

SELECT
   O.name AS TableName
  ,C.name AS ColumnName
FROM
  sysobjects O
    INNER JOIN syscolumns C
    on 
      O.id = C.ID
WHERE
      OBJECTPROPERTY(O.id, N'IsUserTable') = 1 
  AND O.status >= 0   
ORDER BY
   O.name
  ,C.name

ReportNet : Relationship between ReportView and source

This SQL returns the relationship between ReportView and their source.
Run on content store database.
This is for SQLServer.

SELECT
  N.Name AS ReportViewName
 ,P.BASE AS ReportSource
FROM
 CMOBJECTS O
  INNER JOIN CMOBJPROPS6 P
   ON
    O.CMID = P.CMID 
  INNER JOIN CMOBJNAMES N
    ON
    O.CMID = N.CMID 
WHERE
     O.CLASSID = 19
 AND N.LOCALEID = 122