April 9, 2012 at 8:39 am
Where do you recommend we store entity and attribute definitions,in a separate database or in extended properties?
thanks very much
drew
April 9, 2012 at 9:03 am
i prefer the extended properties, because they stay with the database they are describing;
that's really the purpose of the extended properties anyway;
it also makes it a lot easier for any reports or methods you create to be able to report against other databases, and not depend on that one special database existing or not.
It's easy to create a tool that just points at a database and displays info about that one database, including the extended properties.
I know at our shop we have a process where we export the list of tables/columns, for example, and hand it to developers, where they fill out the table name and each columns purpose/description;
then we import that back in as extended properties.
Lowell
April 10, 2012 at 5:09 am
That's what we;re trying to accomplish too, get the definitions off paper in into the data.
Its true that collecting the data in extended properties keeps the data with the database defined, we were speculating on the value of central definitions to enable cross database reporting to catch inconsistencies.
Thanks very much
drew
April 11, 2012 at 9:39 am
Here is a script I wrote to create a script to set the column definitions for every column in every table. When filled in, just run the finished product as a SQL Script.
Sample Output:
/* ================================================================================================================================== */
/* Documentation for Server: MySQLServer Database: MyDatabase */
/* For Date: 11 Apr 2012 11:33:17:660 */
/* ================================================================================================================================== */
USE MyDatabase
GO
/* Column Definitions */
/* ================================================================================================================================ */
/* Documentation for Schema: dbo Table: Accounts Table Type: TABLE */
/* ======= Schema: dbo ========= Table: Accounts ======= Column: AcctID ======== */
--Setting the description of the column (how is it used, what data does it hold)
EXEC sp_addextendedproperty @name = 'CLMNDOC', @value = '',
@level0type = 'Schema', @level0name = 'dbo',
@level1type = 'Table', @level1name = 'Accounts',
@level2type = 'Column', @level2name = 'AcctID'
GO
/* ======= Schema: dbo ========= Table: Accounts ======= Column: ContID ======== */
--Setting the description of the column (how is it used, what data does it hold)
EXEC sp_addextendedproperty @name = 'CLMNDOC', @value = '',
@level0type = 'Schema', @level0name = 'dbo',
@level1type = 'Table', @level1name = 'Accounts',
@level2type = 'Column', @level2name = 'ContID'
GO
/* ======= Schema: dbo ========= Table: Accounts ======= Column: AccountTypeID ======== */
--Setting the description of the column (how is it used, what data does it hold)
EXEC sp_addextendedproperty @name = 'CLMNDOC', @value = '',
@level0type = 'Schema', @level0name = 'dbo',
@level1type = 'Table', @level1name = 'Accounts',
@level2type = 'Column', @level2name = 'AccountTypeID'
GO
/* ======= Schema: dbo ========= Table: Accounts ======= Column: AccountNoPt1 ======== */
--Setting the description of the column (how is it used, what data does it hold)
EXEC sp_addextendedproperty @name = 'CLMNDOC', @value = '',
@level0type = 'Schema', @level0name = 'dbo',
@level1type = 'Table', @level1name = 'Accounts',
@level2type = 'Column', @level2name = 'AccountNoPt1'
GO
/* ======= Schema: dbo ========= Table: Accounts ======= Column: AccountNoPt2 ======== */
--Setting the description of the column (how is it used, what data does it hold)
EXEC sp_addextendedproperty @name = 'CLMNDOC', @value = '',
@level0type = 'Schema', @level0name = 'dbo',
@level1type = 'Table', @level1name = 'Accounts',
@level2type = 'Column', @level2name = 'AccountNoPt2'
GO
Actual SQL Script to create the above:
/* ============================================================================================================= */
/* Generate Column Documentation Settings SCRIPT */
/* ============================================================================================================= */
DECLARE @includeviewsbit
DECLARE @spstatint
DECLARE @errmsgvarchar(200)
DECLARE @recnint
DECLARE @numrecs int
DECLARE @SNvarchar(50)
DECLARE @TN varchar(50)
DECLARE @de varchar(50)
DECLARE @tt varchar(50)
DECLARE @currsnvarchar(50)
DECLARE @currtnvarchar(50)
DECLARE @ev varchar(1000)
DECLARE @servname varchar(128)
DECLARE @dbname varchar(128)
DECLARE @currdt varchar(30)
DECLARE @currdtlen int
DECLARE @servnamelen int
DECLARE @dbnamelen int
DECLARE @linelen int
DECLARE @vl varchar(2000)
DECLARE @vl2 varchar(2000)
DECLARE @auditnote varchar(1000)
DECLARE @includeissensitive bit
DECLARE @includeclmnencrypt bit
DECLARE @includesourceconversionvalues bit
DECLARE @includedestconversionvalues bit
DECLARE @setasnew bit
DECLARE @includeaudittables bit
SET NOCOUNT ON
/* ============================================================================================================ */
/* User Can Set These Variables */
SET @includeviews = 0-- Set whether to include views 1=TRUE 0=FALSE
SET @includeissensitive = 0-- whether to include the ISSENSITIVE documentation
SET @includeclmnencrypt = 0-- whether to include the CLMNENCRYPT documentation
SET @setasnew = 1-- whether to do all settings as if from scratch, like re-creating the database and then using these settings
SET @includeaudittables = 0-- whether to include the settings for the audit tables
/* ============================================================================================================ */
SET @linelen = 130
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
/* ======================================================================================================== */
SELECT @currdt = CONVERT(varchar(30),GETDATE(),113)
SELECT @servname=@@servername
SELECT TOP 1 @dbname=TABLE_CATALOG FROM INFORMATION_SCHEMA.COLUMNS
SET @currdtlen = LEN(@currdt)
SET @servnamelen = LEN(@servname)
SET @dbnamelen = LEN(@dbname)
PRINT '/* ' + REPLICATE('=',@linelen) + ' */'
PRINT '/* ' + REPLICATE(' ',20) + 'Documentation for Server: ' + @servname + REPLICATE(' ',15) + 'Database: ' + @dbname + REPLICATE(' ',@linelen-20-27-@servnamelen-15-11-@dbnamelen) + ' */'
PRINT '/* ' + REPLICATE(' ',20) + 'For Date: ' + @currdt + REPLICATE(' ',@linelen-20-11-@currdtlen) + ' */'
PRINT '/* ' + REPLICATE('=',@linelen) + ' */'
PRINT ''
PRINT ''
PRINT 'USE ' + @dbname
PRINT 'GO'
PRINT ''
PRINT '/* ' + REPLICATE(' ',30) + 'Column Definitions' + REPLICATE(' ',30) + ' */'
PRINT ' '
PRINT ' '
SELECT TABLE_SCHEMA AS SchemaName2, TABLE_NAME AS TableName2, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN CONVERT(varchar(100),'TABLE')
WHEN 'VIEW' THEN CONVERT(varchar(100),'VIEW')
END as TableType2
INTO #tabledoc
FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'
ORDER BY SchemaName2, TableName2
SELECT TABLE_SCHEMA AS 'SchemaName',
TABLE_NAME AS 'TableName',
COLUMN_NAME AS 'DataElement',
ORDINAL_POSITION AS 'OrdinalPosition',
CONVERT(varchar(50),'') AS TableType
INTO #columndoc
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY SchemaName, TableName, ORDINAL_POSITION
UPDATE #columndoc
SET TableType = (SELECT TableType2 FROM #tabledoc WHERE SchemaName=SchemaName2 AND TableName=TableName2)
if @includeviews = 0
DELETE FROM #columndoc WHERE TableType='VIEW'
if @includeaudittables = 0
DELETE FROM #columndoc WHERE TableName LIKE '%Audit'
DECLARE backupFiles CURSOR FOR
SELECT SchemaName, TableName, DataElement, TableType
FROM #columndoc
ORDER BY SchemaName, TableName, OrdinalPosition
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de, @tt
--set the initial values so will print the heading
SET @currsn = ''
SET @currtn = ''
WHILE @@FETCH_STATUS = 0
BEGIN
--do the heading for a new table
if @SN <> @currsn or @TN <> @currtn
BEGIN
SET @auditnote = ''
if EXISTS(SELECT * FROM #columndoc WHERE SchemaName = @SN AND TableName = ltrim(rtrim(@tn))+'Audit')
SET @auditnote = 'NOTE: This table has a corresponding audit table'
PRINT '/* ================================================================================================================================ */'
PRINT '/* Documentation for Schema: ' + @SN + ' Table: ' + @TN + ' Table Type: ' + @tt + ' ' + @auditnote + ' */'
PRINT ''
if @SN <> @currsn
SET @currsn = @SN
if @TN <> @currtn
SET @currtn = @TN
END
PRINT '/* ======= Schema: ' + @SN + ' ========= Table: ' + @TN + ' ======= Column: ' + @de + ' ======== */'
PRINT ''
if @includeissensitive=1
BEGIN
SET @ev = ISNULL((SELECT CONVERT(varchar(1000),value)
FROM fn_listExtendedProperty('ISSENSITIVE',
'Schema', @SN,
'Table', @TN,
'Column', @de)),'')
if @ev = '' or @ev = 'FALSE'
BEGIN
PRINT '--Setting whether column holds sensitive data'
PRINT 'EXEC sp_addextendedproperty @name = ''ISSENSITIVE'', @value = ''FALSE'','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Table'', @level1name = ''' + @TN + ''','
PRINT '@level2type = ''Column'', @level2name = ''' + @de + ''''
PRINT 'GO'
PRINT ''
END
else
BEGIN
PRINT '--Setting whether column holds sensitive data'
PRINT '-- Does hold sensitive data. Already set.'
PRINT ''
END
END
/* Check here to see if CLMNENCRYPT is set. If so, skip this setting */
if @includeclmnencrypt=1
BEGIN
SET @ev = ISNULL((SELECT CONVERT(varchar(1000),value)
FROM fn_listExtendedProperty('CLMNENCRYPT',
'Schema', @SN,
'Table', @TN,
'Column', @de)),'')
if @ev = '' or @ev = 'FALSE'
BEGIN
PRINT '--Setting whether column holds encrypted data'
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNENCRYPT'', @value = ''FALSE'','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Table'', @level1name = ''' + @TN + ''','
PRINT '@level2type = ''Column'', @level2name = ''' + @de + ''''
PRINT 'GO'
PRINT ''
END
else
BEGIN
PRINT '--Setting whether column holds encrypted data'
PRINT '-- Does hold encrypted data. Already set.'
PRINT ''
END
END
SET @vl = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('CLMNDOC',
'Schema', @SN,
'Table', @TN,
'Column', @de)),'')
-- Replace single quote with double to make work
SET @vl2 = REPLACE(@vl,'''','''''')
PRINT '--Setting the description of the column (how is it used, what data does it hold)'
if @setasnew=1
BEGIN
if @vl = ''
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNDOC'', @value = '''','
else
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNDOC'', @value = ''' + @vl2 + ''','
END
else
BEGIN
if @vl = ''
PRINT 'EXEC sp_addextendedproperty @name = ''CLMNDOC'', @value = '''','
else
PRINT 'EXEC sp_updateextendedproperty @name = ''CLMNDOC'', @value = ''' + @vl2 + ''','
END
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Table'', @level1name = ''' + @TN + ''','
PRINT '@level2type = ''Column'', @level2name = ''' + @de + ''''
PRINT 'GO'
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de, @tt
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #tabledoc
DROP TABLE #columndoc
/* ======================================================================================================== */
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@Messagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
PRINT 'ERROR OCCURED: ' + @Message
--IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AppErrorLog]') AND type in (N'U'))
--BEGIN
--INSERT INTO AppErrorLog
--VALUES (GETDATE(), USER, @Message, 'xp_usp_Generate_Column_Documentation_Settings', @ErrorNo, @Severity, @LineNo, HOST_NAME())
--END
END CATCH
GO
April 11, 2012 at 9:45 am
Here is the companion Data Dictionary script that utilizes the CLMNDOC extended property, along with others, to create a decent picture of the database. It's not perfect, nor slick, but it definitely suits my needs. The CLMNDOC value shows up in the Comment column at the end, if it exists for that column.
Hope it helps.
/* ==================================================================================================================== */
/* Generate Data Dictionary (SCRIPT) */
/* ==================================================================================================================== */
/*
NOTE: Remember to set the database context before running this script.
*/
DECLARE @includeviewsbit
DECLARE @spstatint
DECLARE @errmsgvarchar(200)
DECLARE @recnint
DECLARE @numrecs int
DECLARE @currtime datetime
SET NOCOUNT ON
SET @includeviews = 0-- 0=false, 1=true
SET @spstat = 1-- go ahead and set to ok
SET @errmsg = ''-- go ahead and set to ok
SET @recn = 0-- go ahead and set to ok
BEGIN TRY
SET @currtime = getdate()--CONVERT(datetime,CONVERT(varchar(20),GETDATE(),101))
SELECT @@SERVERNAME as 'ServerName', TABLE_CATALOG As 'DatabaseName',(select top 1 d.name from sys.data_spaces d where d.data_space_id =(select top 1 i.data_space_id from sys.indexes i where i.object_id = (SELECT top 1 t.object_id FROM sys.tables t WHERE gg.TABLE_NAME= t.name) and i.index_id < 2)) AS 'TableOnFileGroup',
TABLE_SCHEMA AS 'SchemaName',
TABLE_NAME AS 'TableName',
COLUMN_NAME AS 'DataElement',
ORDINAL_POSITION AS 'OrdinalPosition',
DATA_TYPE AS 'DataType',
ISNULL(CONVERT(char(10),CHARACTER_MAXIMUM_LENGTH),'') AS 'MaxLen', ISNULL(CONVERT(char(10),NUMERIC_PRECISION),'') AS 'Prec', ISNULL(CONVERT(char(10),NUMERIC_SCALE),'') AS 'Scale',
CASE IS_NULLABLE WHEN 'YES' THEN '' ELSE 'NOT NULL' END AS 'NOTNULL',
CASE WHEN COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'YES' ELSE '' END AS 'Identity',
ISNULL(COLLATION_NAME,'') AS ColCollationName,
CONVERT(varchar(50),'') AS 'IsPKorFK',
ISNULL((SELECT top 1 object_name(k.referenced_object_id) + '(' + col_name(k.referenced_object_id,k.referenced_column_id) + ')' FROM sys.foreign_key_columns k WHERE object_name(k.parent_object_id) = gg.TABLE_NAME AND col_name(k.parent_object_id,k.parent_column_id) = gg.COLUMN_NAME),'') AS 'References',
ISNULL(COLUMN_DEFAULT,'') AS 'DefaultValue',
--'' AS 'Constraint','' AS 'Check Value', '' AS 'Index', '' AS 'IndexFileGroup',
CONVERT(varchar(100),'') AS PKConstName,CONVERT(varchar(100),'') AS 'IndexType',CONVERT(varchar(100),'') AS FKConstName,
CONVERT(varchar(50),'') AS MissingIndexOnFK,
CONVERT(varchar(50),'') AS IsSensitive,
CONVERT(varchar(50),'') AS IsEncrypted,
CONVERT(varchar(2000),'') AS 'Comment'
INTO #clmnInfoTable
FROM INFORMATION_SCHEMA.COLUMNS gg
ORDER BY SchemaName, TableName, OrdinalPosition
--TABLE_CATALOG is the database name
--SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT b.CONSTRAINT_NAME, a.TABLE_NAME,a.COLUMN_NAME, b.CONSTRAINT_TYPE
INTO #prikeytable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON b.CONSTRAINT_NAME=a.CONSTRAINT_NAME
WHERE b.CONSTRAINT_TYPE = 'PRIMARY KEY' -- 'FOREIGN KEY'
ORDER BY a.TABLE_NAME, a.COLUMN_NAME
SELECT b.CONSTRAINT_NAME, a.TABLE_NAME,a.COLUMN_NAME, b.CONSTRAINT_TYPE
INTO #forkeytable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON b.CONSTRAINT_NAME=a.CONSTRAINT_NAME
WHERE b.CONSTRAINT_TYPE = 'FOREIGN KEY' -- 'FOREIGN KEY'
ORDER BY a.TABLE_NAME, a.COLUMN_NAME
UPDATE #clmnInfoTable
SET [IsPKorFK] = [IsPKorFK] + 'PK;'
WHERE EXISTS (SELECT * FROM #prikeytable p WHERE p.TABLE_NAME=[TableName] and p.COLUMN_NAME=[DataElement])
UPDATE #clmnInfoTable
SET [IsPKorFK] = [IsPKorFK] + 'FK;'
WHERE EXISTS (SELECT * FROM #forkeytable p WHERE p.TABLE_NAME=[TableName] and p.COLUMN_NAME=[DataElement])
UPDATE #clmnInfoTable
SET PKConstName =(SELECT TOP 1 p.CONSTRAINT_NAME FROM #prikeytable p WHERE p.TABLE_NAME=[TableName] and p.COLUMN_NAME=[DataElement])
WHERE EXISTS (SELECT * FROM #prikeytable p WHERE p.TABLE_NAME=[TableName] and p.COLUMN_NAME=[DataElement])
UPDATE #clmnInfoTable
SET FKConstName = (SELECT TOP 1 p.CONSTRAINT_NAME FROM #forkeytable p WHERE p.TABLE_NAME=[TableName] and p.COLUMN_NAME=[DataElement])
WHERE EXISTS (SELECT * FROM #forkeytable p WHERE p.TABLE_NAME=[TableName] and p.COLUMN_NAME=[DataElement])
UPDATE #clmnInfoTable
SET IndexType =(SELECT TOP 1 type_desc FROM sys.indexes s WHERE s.name=PKConstName ORDER BY type_desc)
WHERE [IsPKorFK] LIKE '%PK%'
UPDATE #clmnInfoTable
SET IndexType = ISNULL((SELECT TOP 1 type_desc FROM sys.indexes s WHERE s.name=FKConstName ORDER BY type_desc),'')
WHERE [IsPKorFK] LIKE '%FK%'
UPDATE #clmnInfoTable
SET Comment = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('CLMNDOC',
'Schema', [SchemaName],
'Table', [TableName],
'Column', [DataElement])),'')
UPDATE #clmnInfoTable
SET IsSensitive = 'YES' WHERE ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('ISSENSITIVE',
'Schema', [SchemaName],
'Table', [TableName],
'Column', [DataElement])),'')='TRUE'
UPDATE #clmnInfoTable
SET IsEncrypted = 'YES' WHERE ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('CLMNENCRYPT',
'Schema', [SchemaName],
'Table', [TableName],
'Column', [DataElement])),'')<>''
SELECT *
into #dummyTableViews
FROM information_schema.tables
WHERE TABLE_TYPE='VIEW'
ORDER BY TABLE_SCHEMA, TABLE_NAME
if @includeviews = 1
BEGIN
UPDATE #clmnInfoTable
SET TableOnFileGroup = 'Is a VIEW'
WHERE EXISTS (SELECT * FROM #dummyTableViews WHERE TABLE_SCHEMA = SchemaName AND TABLE_NAME = TableName) AND TableOnFileGroup IS NULL
END
else
BEGIN
DELETE FROM #clmnInfoTable
WHERE EXISTS (SELECT * FROM #dummyTableViews WHERE TABLE_SCHEMA = SchemaName AND TABLE_NAME = TableName) AND TableOnFileGroup IS NULL
END
-- ----------------------------------------------------------------------
-- This section:
-- Author: Michael Smith, Minneapolis, MN
-- Purpose: Find unindexed foreign keys
-- Date: 2008-08-09
-- ----------------------------------------------------------------------
SELECT DISTINCT /* remove dups caused by composite constraints */
DB_NAME() AS database_name,
OBJECT_NAME(foreign_keys.parent_object_id) AS table_name,
foreign_keys.[name] AS fk_name
INTO #dummyFKIndex
FROM sys.foreign_keys AS foreign_keys
JOINsys.foreign_key_columns AS foreign_key_columns
ONforeign_keys.[object_id] = foreign_key_columns.constraint_object_id
WHERENOT EXISTS (
SELECT'An index with same columns and column order'
FROM sys.indexes AS indexes
JOINsys.index_columnsAS index_columns
ONindexes.[object_id] = index_columns.[object_id]
WHEREforeign_keys.parent_object_id = indexes.[object_id]
ANDindexes.index_id = index_columns.index_id
ANDforeign_key_columns.constraint_column_id = index_columns.key_ordinal
ANDforeign_key_columns.parent_column_id = index_columns.column_id
AND OBJECTPROPERTYEX(indexes.[object_id],'IsMSShipped') = 0
AND indexes.is_hypothetical = 0
)
AND foreign_keys.is_ms_shipped = 0;
UPDATE #clmnInfoTable SET MissingIndexOnFK='YES' WHERE EXISTS (SELECT * FROM #dummyFKIndex WHERE fk_name = FKConstName)
UPDATE #clmnInfoTable SET Prec='',Scale='' WHERE DataType = 'money' OR DataType = 'int' OR DataType = 'bigint' OR DataType = 'tinyint'
SELECT ServerName
, DatabaseName
, TableOnFileGroup
, Schemaname
, TableName
, DataElement
, OrdinalPosition
, DataType
, MaxLen
, Prec
, Scale
, NOTNULL
, [Identity]
--, ColCollationName
, IsPKorFK
, [References]
, DefaultValue
, PKConstName
, IndexType
, FKConstName
--, MissingIndexOnFK
, IsSensitive
, IsEncrypted
, Comment
FROM #clmnInfoTable
ORDER BY ServerName, DatabaseName, [SchemaName], [TableName], OrdinalPosition
--SELECT * FROM #clmnInfoTable
--ORDER BY ServerName, DatabaseName, [SchemaName], [TableName], OrdinalPosition
DROP TABLE #clmnInfoTable
DROP TABLE #prikeytable
DROP TABLE #forkeytable
DROP TABLE #dummyTableViews
DROP TABLE #dummyFKIndex
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@Messagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
PRINT 'Error occured: ' + @errmsg
END CATCH
GO
April 11, 2012 at 10:48 am
Holy Good Night!
That was spectacular.
I see i had about a 20% appreciation for the task at hand.
This fills the bill for us too.
Thank you immensely for your generosity
drew
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply