Eloquent way of logging errors in SQL Server2

I enjoy this template of error trapping.
It uses the TRY CATCH way of doing things, which is awesome.

To take this out for a spin:
Create the error table (just like inside Adventureworks):

CREATE TABLE [dbo].[ErrorLog]( [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NOT NULL,[UserName] [sysname] NOT NULL,[ErrorNumber] [int] NOT NULL, [ErrorSeverity] [int] NULL, [ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL, [ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL,CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ( [ErrorLogID] ASC)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

This table will be filled when there is an error.
The below procedure will be filled with SQL Server's famous @error_number

<><>

CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted -- by uspLogError in the ErrorLog table. AS BEGIN SET NOCOUNT ON; -- ditch the extra messages SET @ErrorLogID = 0; -- all is ok with the world BEGIN TRY IF ERROR_NUMBER() IS null -- no error? RETURN; IF XACT_STATE() = -1 -- uncommitable transaction? don't do any damage! BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + char(10) + 'Rollback the transaction before executing uspLogError in order to successfully log error information.'; RETURN; END; INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine],[ErrorMessage] ) VALUES( --CONVERT(sysname, CURRENT_USER), -- current_user is the owner (ie: dbo.) CONVERT(sysname,system_user), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), coalesce(ERROR_PROCEDURE(),'n/a'), ERROR_LINE(),ERROR_MESSAGE() ); -- Pass back the ErrorLogID of the row inserted SELECT @ErrorLogID =convert(nvarchar(16),@@IDENTITY); END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH END;

Now, you can create any procedure and use the TRY CATCH method!

<><>

begin try begin transaction -- --insert a bad data type into a table insert into departments(deptnament) values ('a string that is way too long to append, way too long for sure absolutely no doubt to ') commit transaction End try Begin catch rollback transaction -----begin print error print'oops!' + char(10) + error_message() +char(10)+ 'error number: ' + convert(nvarchar(16),error_number()) ; ------end print error declare @err int set @err =error_number() ; execute uspLogError @err -- log the error here, passing the error # End catch -- test: select * from errorlog

And there you have your scripting for error logs. If you use a version of the above script, you will be able to track the errors way better
.

list local files from SQL Server 2005 (xp_cmdshell enabled)

-- found this on the sqlserver central website.

USE master
GO
CREATE PROCEDURE dbo.sp_ListFiles
@PCWrite varchar(2000),
@DBTable varchar(100)= NULL,
@PCIntra varchar(100)= NULL,
@PCExtra varchar(100)= NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)

SET @Work = 'DIR ' + '"' + @PCWrite + '"'

CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))

INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work

SET @Retain = @@ERROR

IF @Status = 0 SET @Status = @Retain

IF @Status = 0 SET @Status = @Return

IF (SELECT COUNT(*) FROM #DBAZ) < 4
BEGIN
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
IF @Wish IS NULL
BEGIN
RAISERROR ('General error [%d]',16,1,@Status)
END
ELSE
BEGIN
RAISERROR (@Wish,16,1)
END
END
ELSE
BEGIN
DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
IF @DBTable IS NULL
BEGIN
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
WHERE 0 = 0
AND (@DBUltra = 0 OR Name LIKE '% %')
AND (@DBUltra != 0 OR Name NOT LIKE '% %')
AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
ORDER BY 1
END
ELSE
BEGIN
SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))

+ ' SELECT SUBSTRING(Name,40,100) AS Files'

+ ' FROM #DBAZ'

+ ' WHERE 0 = 0'

+ CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END

+ CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END

+ CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END

+ CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING

(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END

+ ' ORDER BY 1'
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
END
END
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
GO


--And to test:
--EXECUTE sp_ListFiles 'c:\ftp',NULL,NULL,NULL,1

bcp format file: sundry thoughts

I am indexing this link for my own edification. 

http://msdn.microsoft.com/en-us/library/ms191516.aspx

What are the columns in that table?

Oh my.
I wish I found this many years earlier.

use MyDatabase
go

SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

replace MyDatabase and MyTable with your database and table values