edit: the declare

DECLARE @s datetime, 
        @e datetime,
        @Error_Type int, 
        @Error_Loc int,
        @sprc int,
        @Parameters varchar(MAX),
        @Sprocname varchar(255),
        @res_version int,
        @res_cd      int,
        @path_id     int,
        @resource_count   int,
        @count       int,
        @count2       int,
        @res_name varchar(255)

The DDL:

CREATE TABLE [dbo].[SprocExecLog](
	[SprocName] [varchar](256) NOT NULL,
	[TranStart] [datetime] NOT NULL,
	[TranEnd] [datetime] NOT NULL,
	[LogStart] [datetime] NOT NULL,
	[LogEnd] [datetime] NOT NULL,
	[ReturnCode] [int] NULL,
	[ErrorCode] [int] NULL,
	[ErrorMessage] [varchar](4000) NULL,
	[Rows] [int] NOT NULL,
	[App_User] [varchar](30) NOT NULL,
	[Sys_User] [varchar](30) NOT NULL,
	[User_Name] [varchar](30) NOT NULL,
	[Host_Name] [varchar](256) NOT NULL,
	[Parameters] [varchar](max) NULL
) ON [PRIMARY]


    SET @Parameters = 'DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int; '+CHAR(13)+CHAR(10)
		+ 'EXEC '    + @SprocName							   +CHAR(13)+CHAR(10)
        + '  @DATA=' + COALESCE(CHAR(39)+@DATA+CHAR(39),'NULL')+CHAR(13)+CHAR(10)
		+ ', @User=' + COALESCE(CHAR(39)+@User+CHAR(39),'NULL')+CHAR(13)+CHAR(10)
		+ ', @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT; '+CHAR(13)+CHAR(10)
		+ 'SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]'
 
  EXEC [isp_LOG_SprocExecLog] @Sprocname, @s, @e, @rc, @Error, @Error_Message, @Rowcount, @User, @Parameters, @sprc OUTPUT	

SELECT TOP 1 LEN(Parameters)
  FROM SprocExecLog
WHERE SprocName = 'USP_INS_ADMIN_DETAILS'
ORDER BY TranStart DESC

quote:

---

Originally posted by tkizer

Why is 4000 wrong?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

DECLARE @cmd varchar(8000)
SET @cmd = 
' bcp '
+ '"SELECT TOP 1 Parameters'
+ ' FROM ISRS.dbo.SprocExecLog'
+ ' WHERE SprocName IN (''USP_INS_ADMIN_DETAILS_V2'', ''USP_INS_ADMIN_DETAILS'')'
+ ' ORDER BY TranStart DESC"'
+ ' queryout'
+ ' D:\SPROC_EXEC_XML.SQL'
+ ' -SNJROS1BBLD0304\DEV2K08'
+ ' -T'
+ ' -c'

SELECT @cmd

exec master..xp_cmdshell @cmd

DECLARE @cmd varchar(8000)
SET @cmd = 
' bcp '
+ '"SELECT TOP 1 Parameters'
+ ' FROM ISRS.dbo.SprocExecLog'
+ ' WHERE SprocName IN (''USP_INS_ADMIN_DETAILS_V2'', ''USP_INS_ADMIN_DETAILS'')'
+ ' ORDER BY TranStart DESC"'
+ ' queryout'
+ ' D:\SPROC_EXEC_XML.SQL'
+ ' -SNJROS1BBLD0304\DEV2K08'
+ ' -T'
+ ' -w'

SELECT @cmd

exec master..xp_cmdshell @cmd

exec master..xp_cmdshell 'Dir D:\*.sql'

exec master..xp_cmdshell 'Copy D:\*.sql \\L-llrltenn\X002548_Sprocs\*.*'

quote:

---

Originally posted by tkizer

That's why I had asked for the code. You posted all of the other code, but not the code for the logging sproc.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---