January 22, 2002 at 6:26 am
I am trying to write an audit trigger which needs to run sp_executesql. However, I am not passing the parameters correctly, and I don't know how to fix it. The error message I get is:
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
Here is the code, can anyone see how I can fix it?
Thaks in advance,
BEGIN TRANSACTION
PRINT 'Begin Transaction'
DECLARE @ColumnName VARCHAR(100)
DECLARE @DataType VARCHAR(50)
DECLARE @ColumnNameString VARCHAR(1000)
DECLARE @ColumnValsString VARCHAR(1000)
DECLARE @DataTypeString VARCHAR(1000)
DECLARE @SQL VARCHAR(1000)
DECLARE ColumnNamesCursor CURSOR FOR
SELECT [column_name],[data_type] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Task'
OPEN ColumnNamesCursor
FETCH NEXT FROM ColumnNamesCursor INTO @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColumnNameString = @ColumnNameString + '¬' + @ColumnName
SET @DataTypeString = @DataTypeString + '¬' + @DataType
SET @SQL = @SQL + @ColumnName + ','
FETCH NEXT FROM ColumnNamesCursor INTO @ColumnName, @DataType
END --END CURSOR
PRINT 'DEALLOCATE CURSOR'
CLOSE ColumnNamesCursor
DEALLOCATE ColumnNamesCursor
--Get rid of last chars
SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL) -1)
--Retun the vals
SET @SQL = N'SELECT @ColumnValsString = (SELECT ' + @SQL + N' FROM Deleted)'
EXEC sp_executesql @SQL,
N' @ColumnValsString VARCHAR(1000) OUTPUT' ,
@ColumnValsString OUTPUT
EXEC uspCreateUpdateAuditFull 'Task',@AuditID,@ColumnNameString,@DataTypeString, @ColumnValsString
January 22, 2002 at 7:10 am
You've declared @SQL as a varchar, not nvarchar:
quote:
DECLARE @SQL VARCHAR(1000)
Then later on you try and set this variable equal to an nchar/nvarchar value:
quote:
SET @SQL = N'SELECT @ColumnValsString = (SELECT ' + @SQL + N' FROM Deleted)'
Since you're going to simply pass it to sp_executesql to carry out, might as well declare it as nvarchar(1000) to start with.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 22, 2002 at 7:15 am
Thanks
January 22, 2002 at 7:46 am
As some of you will already know, mu code is fatally flawed. The sp_executesql cannot see my deleted statement. I tried to do a select * into #deleted from deleted and then select from there, but as my base table includes text fields, it will not copy them. I will be putting them aside anyway, when I crack this other bit, but can I put a where clause in my select into to omit the offending text fields?
Thanks again
January 22, 2002 at 7:52 am
What version of SQL Server are you using?
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 22, 2002 at 8:06 am
The server is runnign SQL Server 7
January 22, 2002 at 10:28 am
Why do you need dynamic sql?
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 22, 2002 at 2:32 pm
Aside from Steve's question, the answer you can choose which columns to return by specifying the columns by name. Don't do a SELECT *. Do instead a SELECT Col1Name, Col2Name, etc., and ensure you avoid the text column. The WHERE clause operates on the rows, and you're after the columns.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 23, 2002 at 12:28 am
Yes, thank you both, I was trying to write a fairly generic trigger (hence the dynamic SQL), which I could apply to any table I wanted to audit. I have now realised that this is going to cause more problems than it will solve. I will have to write a specific trigger for each, but I Can use a vb function to generate them at least. I am , however, worried about text & images, as these will be audited, but in a different way (I can't add them to a string). If I can't see them in the inserted/deleted tables, how can I acheive this?
Edited by - julliff on 01/23/2002 02:06:46 AM
January 23, 2002 at 12:04 pm
The best way is to insert the audit record by joining back to the original table. To handle multi row inserts, I've done the following.
in trigger:
if update()
begin
insert AuditTable (col1, col2)
select a.col1, a.col2
from mytable a, inserted i
where a.pk = i.pk
end
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 24, 2002 at 2:28 am
Sorry to advertise another site here but you might look at
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12229
Which was concerning someone else trying to build an audit trail trigger.
Also info that might be applicable at
sp_executeSQL
Columns_updated()
Generate Trigger
p.s. I can't agree to using a cursor here or ever - just one of my prejudices.
Cursors never.
DTS - only when needed and never to control.
January 24, 2002 at 3:44 am
Hi Nigel,
Thanks for your post. My trigger has changed significantly since my last post. I am not very clued up on triggers, I don't like them, and have always cotrolled everything through SPs. Anyway, I have been told I must use triggers, and am probably coming at it all wrong. I don't like cursors either. Bad news, I've put in another one - when I realised that my trigger would only audit one record. Noted the other thread re this - but I am beginning to get confused. I wanted my audit to reflect the deleted value (what it was before the update). I have a third table, which will audit my text fields, but you can't reference the text fields in deleted. If I link to the underlying table, I get the new vaue. Anyway, this is the code, as it stands now. I have to be able to create triggers from an app using DMO, so I originally wanted to make it as generic as possible (maybe I'm just lazy!!)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[U_Task]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[U_Task]
GO
CREATE TRIGGER [U_Task] ON [Task]
FOR UPDATE
AS
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON
DECLARE @AuditID UNIQUEIDENTIFIER
DECLARE @OriginalKeyID VARCHAR(16)
DECLARE @ORDERID VARCHAR(16)
DECLARE @FMID VARCHAR(16)
DECLARE @TaskTitle VARCHAR(255)
DECLARE @PMS VARCHAR(12)
DECLARE @MOP int
DECLARE @STATECODE int
DECLARE @TASKINTERVAL FLOAT
DECLARE @NATURALINTERVAL FLOAT
DECLARE @TASKTYPEID VARCHAR(16)
DECLARE @WORKLISTNO VARCHAR(20)
DECLARE @USERACTIONID VARCHAR(20)
DECLARE @S340Starred int
DECLARE @S340Mandatory int
DECLARE @CanDefer BIT
DECLARE @TaskTime float
DECLARE @TaskCatID VARCHAR(16)
DECLARE @IssueNumber VARCHAR(10)
DECLARE @AmendmentNumber VARCHAR(10)
DECLARE @Timestamp TIMESTAMP
DECLARE @TaskIntervalUnitID VARCHAR(16)
DECLARE @NaturalIntervalUnitID VARCHAR(16)
DECLARE @RECORDDATE dATEtIME
DECLARE @SponsorName VARCHAR(50)
DECLARE @DateLastUpdated DATETIME
DECLARE @GenerationCount VARCHAR(16)
IF NOT UPDATE(RECORDDATE)
BEGIN
--Do we need an audit record
BEGIN TRAN
IF EXISTS(SELECT 1 FROM [Table] WHERE Audit = 1 AND TableName = 'Task')
BEGIN
DECLARE DeletedCursor CURSOR FOR
SELECT TaskID,
ORDERID,
FMID,
TaskTitle,
PMS,
MOP,
STATECODE,
TASKINTERVAL,
NATURALINTERVAL,
TASKTYPEID,
WORKLISTNO,
USERACTIONID,
S340Starred,
S340Mandatory,
CanDefer,
TaskTime,
TaskCatID,
IssueNumber,
AmendmentNumber,
[Timestamp],
TaskINtervalUnitID,
NaturalIntervalUnitID,
RECORDDATE,
SponsorName,
DateLastUpdated,
GenerationCount from Deleted
OPEN DeletedCursor
PRINT 'Fetch First'
FETCH NEXT FROM DeletedCursor INTO
@OriginalKeyID ,
@ORDERID,
@FMID,
@TaskTitle,
@PMS,
@MOP,
@STATECODE,
@TASKINTERVAL,
@NATURALINTERVAL,
@TASKTYPEID,
@WORKLISTNO,
@USERACTIONID,
@S340Starred,
@S340Mandatory,
@CanDefer,
@TaskTime,
@TaskCatID,
@IssueNumber,
@AmendmentNumber,
@Timestamp,
@TaskIntervalUnitID,
@NaturalIntervalUnitID,
@RECORDDATE,
@SponsorName,
@DateLastUpdated,
@GenerationCount
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Create Audit Record'
EXEC uspCreateUpdateAudit 'TaskID', @OriginalKeyID, @AuditID OUTPUT
--do we need a full audit record?
IF EXISTS(SELECT 1 FROM [Table] WHERE FullAudit = 1 AND TableName = 'Task')
PRINT 'Full Audit'
BEGIN
DECLARE @ColumnName VARCHAR(100)
DECLARE @DataType VARCHAR(50)
DECLARE @ColumnNameString VARCHAR(1000)
DECLARE @ColumnValsString VARCHAR(1000)
DECLARE @DataTypeString VARCHAR(1000)
DECLARE ColumnNamesCursor CURSOR FOR
SELECT [column_name],[data_type] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Task' AND Data_type NOT IN('Text','Image','ntext')
OPEN ColumnNamesCursor
FETCH NEXT FROM ColumnNamesCursor INTO @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColumnNameString = @ColumnNameString + ',' + @ColumnName
SET @DataTypeString = @DataTypeString + ',' + @DataType
FETCH NEXT FROM ColumnNamesCursor INTO @ColumnName, @DataType
END --END CURSOR
CLOSE ColumnNamesCursor
DEALLOCATE ColumnNamesCursor
--Get rid of last char
SET @ColumnNameString = SUBSTRING(@ColumnNameString,2,LEN(@ColumnNameString) - 1)
SET @DataTypeString = SUBSTRING(@DataTypeString,2,LEN(@DataTypeString) - 1)
--Retun the vals
SET @ColumnValsString =
RTRIM(CONVERT(CHAR,@OriginalKeyID)) + ',' +
RTRIM(CONVERT(CHAR,@ORDERID))+ ',' +
RTRIM(CONVERT(CHAR,@FMID))+',' +
RTRIM(CONVERT(CHAR,@TaskTitle))+',' +
RTRIM(CONVERT(CHAR,@PMS))+',' +
RTRIM(CONVERT(CHAR,@MOP))+',' +
RTRIM(CONVERT(CHAR,@STATECODE))+',' +
RTRIM(CONVERT(CHAR,@TASKINTERVAL))+',' +
RTRIM(CONVERT(CHAR,@NATURALINTERVAL))+',' +
RTRIM(CONVERT(CHAR,@TASKTYPEID))+',' +
RTRIM(CONVERT(CHAR,@WORKLISTNO))+',' +
RTRIM(CONVERT(CHAR,@USERACTIONID))+',' +
RTRIM(CONVERT(CHAR,@S340Starred))+',' +
RTRIM(CONVERT(CHAR,@S340Mandatory))+',' +
RTRIM(CONVERT(CHAR,@CanDefer))+',' +
RTRIM(CONVERT(CHAR,@TaskTime))+',' +
RTRIM(CONVERT(CHAR,@TaskCatID))+',' +
RTRIM(CONVERT(CHAR,@IssueNumber))+',' +
RTRIM(CONVERT(CHAR,@AmendmentNumber))+',' +
RTRIM(CONVERT(CHAR,@Timestamp))+',' +
RTRIM(CONVERT(CHAR,@TaskIntervalUnitID))+',' +
RTRIM(CONVERT(CHAR,@NaturalIntervalUnitID))+',' +
RTRIM(CONVERT(CHAR,@RECORDDATE))+',' +
RTRIM(CONVERT(CHAR,@SponsorName))+',' +
RTRIM(CONVERT(CHAR,@DateLastUpdated))+',' +
RTRIM(CONVERT(CHAR,@GenerationCount))
EXEC uspCreateUpdateAuditFull 'Task',@AuditID,@ColumnNameString,@DataTypeString, @ColumnValsString
END --END FULL AUDIT
UPDATE TASK
SET Recorddate = GetDate()
FROM Task T
INNER JOIN Inserted I
ON T.TaskID = I.TaskID
PRINT 'Fetch Next'
FETCH NEXT FROM DeletedCursor INTO @OriginalKeyID,
@ORDERID,
@FMID,
@TaskTitle,
@PMS,
@MOP,
@STATECODE,
@TASKINTERVAL,
@NATURALINTERVAL,
@TASKTYPEID,
@WORKLISTNO,
@USERACTIONID,
@S340Starred,
@S340Mandatory,
@CanDefer,
@TaskTime,
@TaskCatID,
@IssueNumber,
@AmendmentNumber,
@Timestamp,
@TaskIntervalUnitID,
@NaturalIntervalUnitID,
@RECORDDATE,
@SponsorName,
@DateLastUpdated,
@GenerationCount
END
END
CLOSE DeletedCursor
DEALLOCATE DeletedCursor
END
COMMIT
GO
January 24, 2002 at 4:55 am
OK, I have now changed my code to get rid of one cursor, by bringing my sp code into the trigger. One problem is that now I can't pass back my generated PK for Audit to use as a FK in Audit Detail.
I have also changed the way I am going to audit, I will record the updated values, so that I can link to the underlying table from the inserted values. If I try to audit the pre update values, I can't audit text, as I can't reference them in the deleted table, and the underlying table has already changed.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[U_Task]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[U_Task]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [U_Task] ON [Task]
FOR UPDATE
AS
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON
DECLARE @userid VARCHAR(20)
SET @userid = (SELECT UserName FROM CurrentUSers
WHERE ConnectionID = @@SPID)
IF NOT UPDATE(RECORDDATE)
BEGIN
UPDATE TASK
SET Recorddate = GetDate()
FROM Task T
INNER JOIN Inserted I
ON T.TaskID = I.TaskID
--Do we need an audit record
BEGIN TRAN
PRINT 'Create Audit Record'
-- SET @AuditID = NEWID()
INSERT INTO AUDIT
(AuditID,
RecordID,
TableName,
UpdatedByUser,
UpDatedDate)
SELECT
NEWID(),
TaskID,
'Task',
GETDATE()
FROM Inserted
--do we need a full audit record?
IF EXISTS(SELECT 1 FROM [Table] WHERE FullAudit = 1 AND TableName = 'Task')
PRINT 'Full Audit'
BEGIN
DECLARE @ColumnName VARCHAR(100)
DECLARE @DataType VARCHAR(50)
DECLARE @ColumnNameString VARCHAR(1000)
DECLARE @ColumnValsString VARCHAR(1000)
DECLARE @DataTypeString VARCHAR(1000)
DECLARE ColumnNamesCursor CURSOR FOR
SELECT [column_name],[data_type] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Task' AND Data_type NOT IN('Text','Image','ntext')
OPEN ColumnNamesCursor
FETCH NEXT FROM ColumnNamesCursor INTO @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColumnNameString = @ColumnNameString + ',' + @ColumnName
SET @DataTypeString = @DataTypeString + ',' + @DataType
FETCH NEXT FROM ColumnNamesCursor INTO @ColumnName, @DataType
END --END CURSOR
CLOSE ColumnNamesCursor
DEALLOCATE ColumnNamesCursor
--Get rid of last char
SET @ColumnNameString = SUBSTRING(@ColumnNameString,2,LEN(@ColumnNameString) - 1)
SET @DataTypeString = SUBSTRING(@DataTypeString,2,LEN(@DataTypeString) - 1)
--Retun the vals
INSERT INTO AuditDetails
(AuditDetailID,
AuditID,
AuditFieldNames,
AuditFieldTypes,
AuditFieldVals )
SELECT
NEWID(),
@AuditID,
@ColumnNameString,
@DataTypeString,
RTRIM(CONVERT(CHAR,OriginalKeyID)) + ',' +
RTRIM(CONVERT(CHAR,ORDERID))+ ',' +
RTRIM(CONVERT(CHAR,FMID))+',' +
RTRIM(CONVERT(CHAR,TaskTitle))+',' +
RTRIM(CONVERT(CHAR,PMS))+',' +
RTRIM(CONVERT(CHAR,MOP))+',' +
RTRIM(CONVERT(CHAR,STATECODE))+',' +
RTRIM(CONVERT(CHAR,TASKINTERVAL))+',' +
RTRIM(CONVERT(CHAR,NATURALINTERVAL))+',' +
RTRIM(CONVERT(CHAR,TASKTYPEID))+',' +
RTRIM(CONVERT(CHAR,WORKLISTNO))+',' +
RTRIM(CONVERT(CHAR,USERACTIONID))+',' +
RTRIM(CONVERT(CHAR,S340Starred))+',' +
RTRIM(CONVERT(CHAR,S340Mandatory))+',' +
RTRIM(CONVERT(CHAR,CanDefer))+',' +
RTRIM(CONVERT(CHAR,TaskTime))+',' +
RTRIM(CONVERT(CHAR,TaskCatID))+',' +
RTRIM(CONVERT(CHAR,IssueNumber))+',' +
RTRIM(CONVERT(CHAR,AmendmentNumber))+',' +
RTRIM(CONVERT(CHAR,Timestamp))+',' +
RTRIM(CONVERT(CHAR,TaskIntervalUnitID))+',' +
RTRIM(CONVERT(CHAR,NaturalIntervalUnitID))+',' +
RTRIM(CONVERT(CHAR,RECORDDATE))+',' +
RTRIM(CONVERT(CHAR,SponsorName))+',' +
RTRIM(CONVERT(CHAR,DateLastUpdated))+',' +
RTRIM(CONVERT(CHAR,GenerationCount)))
FROM INSERTED
END --END FULL AUDIT
UPDATE TASK
SET Recorddate = GetDate()
FROM Task T
INNER JOIN Inserted I
ON T.TaskID = I.TaskID
END
COMMIT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 24, 2002 at 10:06 am
Apologies for not debugging the code, but it's quite a bit. If the text field auditing is a real problem, I'd look at the Lumigent product advertised on this site (Log Explorer). The cost might outweigh the time spent in development (unless you don't have other stuff to do).
I haven't tried to audit text fields, but I'll take a look and see what I can find out.
Steve
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 24, 2002 at 1:05 pm
Please don't aplologise, the information has been invaluable. Before now, I had not used triggers, but had controlled everything via stored procedures, so I didn't need them. I don't know much about triggers (that much is obvious), and I came at it all wrong, trying to generate dynamic, generic code. Also, I didn't know that there were limitations using the deleted and inserted tables, but I do now.
I have now created a routine which uses DMO to create my triggers, so I am not using cursors anymore - phew.
However, the one outstanding hurdle is, as you say, not being able to audit text fields. I am not sure what to do. My boss probably won't go for the app in the banner, although I will probably download the sample and take a look.
Next step will be to ensure my triggers fire in the correct order - hopefully I'll get there.
Thanks to all
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply