Oct 16, 2008

How to enter NULL value in SQLServer

  • SQLServer 2000
Ctrl+0
  • SQLServer 2005
Enter 'NULL'

Aug 5, 2008

How to select the table have extended property

SELECT
  *
FROM
  sys.tables AS tbl
    INNER JOIN sys.extended_properties AS p 
    ON 
      p.major_id=tbl.object_id 
      AND 
      p.minor_id=0 
      AND 
      p.class=1

How to add the extended property on each table

  1. Rewrite the content of PROPERTY_NAME
  2. Select database
  3. Run SQL
DECLARE @TABLE_NAME VARCHAR(255)
DECLARE @SQL VARCHAR(8000)
DECLARE @PROPERTY_NAME VARCHAR(255)

SET @PROPERTY_NAME = 'Description'

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 =
'
EXEC sys.sp_addextendedproperty
@name=N''' + @PROPERTY_NAME + ''',
@value=N'''' ,
@level0type=N''SCHEMA'',
@level0name=N''dbo'',
@level1type=N''TABLE'',
@level1name=N''' + @TABLE_NAME + ''''

EXEC (@SQL)
END

CLOSE C_TABLE

May 7, 2008

How to remove the prompt name and border from Value prompt

Value prompt has the prompt name and the border(------------) in the list. You can remove these from the list with Java script.

  • Set ID on the property of the dropdown list
  • Add two 'HTML Item'd after the dropdown list
  • Edit first HTML Item as follows
<script type="text/javascript">
function go(){
  var combo1 = document.getElementsByName("_oLstChoicesC0")[0];
  var combo1List = combo1.getElementsByTagName("OPTION");

  combo1.removeChild( combo1List.item(1) );
  combo1.removeChild( combo1List.item(0) );

}
</script>
_oLstChoices means a dropdown list. C0 is ID which is set in the property.
  • Edit second HTML Item as follows
<img src="http://www.blogger.com/pat/images/blank.gif" onload="go()" />
This is to execute Java script on that report.

Apr 30, 2008

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

Apr 17, 2008

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

Apr 15, 2008

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