March 25, 2010 at 12:46 pm
My company has a view that's controlled by a 3rd party application. It's a simple view with a simple WHERE clause. I have been asked to remove the WHERE clause. This application modifies this script weekly and will add it back in along with some additional stuff. What I need to do is create something (SSIS, SQLDMO,??) That I can call from a daily job that will script out the current view, replace all the WHERE clauses with --WHERE and then drop/create the automated (tweaked) view.
Can anyone help me with this??
Thanks in advance to any and all assistance.
March 25, 2010 at 1:09 pm
A DDL Trigger would be one possible approach.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 1:19 pm
I assume creating a different view or changing the application logic are not acceptable options. So...If you know when the application will re-alter the view, you could schedule a SQL job to kick off immediately following the application's changes. The SQL job could execute a simple ALTER statement.
March 25, 2010 at 2:00 pm
Thanks for the replies so far. I tried not to be too detailed in the actual view I need to tweak but it may help.
The view is altered by the third party app to add a union & select to the existing view based on some date formula. I need to remark out the where clause to the view for all of the select statements. The view is dropped and regenerated each time so all of the where clauses get recreated.
Sucks to be me on this 🙂
March 25, 2010 at 2:04 pm
I still say DDL. You can "catch" the CREATE or ALTER view statement and strip away the WHERE from there before the view gets created/altered.
But that's just my 2 cents
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 2:17 pm
Jason,
I am up for that idea. Any chance you have a snippet of code?
March 25, 2010 at 3:04 pm
Rick Osgood-429286 (3/25/2010)
Jason,I am up for that idea. Any chance you have a snippet of code?
Well, I don't have the exact code, but here's a trigger I've used to track schema changes. you'll have to modify it to work for your needs, but most of the functionality is in there.
/****** Object: DdlTrigger [ddl_sourceControl_DBTrigger] Script Date: 08/14/2008 11:31:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_sourceControl_DBTrigger]
ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TRIGGER_EVENTS
AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @objectID INT;
DECLARE @eventType sysname;
DECLARE @SqlText NVARCHAR(MAX);
DECLARE @objectType NVARCHAR(128);
DECLARE @spid INT;
DECLARE @serverName NVARCHAR(128);
DECLARE @loginName NVARCHAR(128);
DECLARE @databaseName NVARCHAR(128);
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
SET @objectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')
SET @serverName = @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(128)');
SET @loginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)');
SET @SqlText = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
SET @databaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)');
IF @object IS NOT NULL
BEGIN
SELECT
@objectID = object_id
,@objectType = type_desc
FROM
sys.objects
WHERE
[NAME] = CONVERT(sysname, @object)
IF ISNULL(@schema, '') = ''
PRINT 'Event Logged: ' + ISNULL(@eventType, '') + ' - ' + @object;
ELSE
PRINT 'Event Logged: ' + ISNULL(@eventType, '') + ' - ' + ISNULL(@schema + '.', '') + @object;
END
ELSE
BEGIN
SET @objectID = 0
PRINT 'Event Logged: ' + ISNULL(@eventType, '') + ' - ' + ISNULL(@schema + '.', '');
END
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
IF @object = 'ddlDatabaseTriggerLog' OR @eventType = 'CREATE_STATISTICS'
RETURN
-- find the change set info
DECLARE @beginTextPOS INT;
DECLARE @endTextPOS INT;
DECLARE @changeSetName NVARCHAR(128);
DECLARE @changeSetID NVARCHAR(128);
SET @beginTextPOS = CHARINDEX('/* CSName:=',@SqlText)
SET @endTextPOS = CHARINDEX('*/',@SqlText, @beginTextPOS)
IF @beginTextPOS > 0
BEGIN
SET @changeSetName = ISNULL(substring(@SqlText, @beginTextPOS, @endTextPOS - @beginTextPOS), '')
SET @changeSetName = REPLACE(RTRIM(LTRIM(RIGHT(@changeSetName, LEN(@changeSetName) - 2))), 'CSName:=','')
END
SET @beginTextPOS = CHARINDEX('/* CSID:=',@SqlText)
SET @endTextPOS = CHARINDEX('*/',@SqlText, @beginTextPOS)
IF @beginTextPOS > 0
BEGIN
SET @changeSetID = ISNULL(substring(@SqlText, @beginTextPOS, @endTextPOS - @beginTextPOS), 0)
SET @changeSetID = REPLACE(RTRIM(LTRIM(RIGHT(@changeSetID, LEN(@changeSetID) - 2))), 'CSID:=','')
END
INSERT [sourceControl].[dbo].[_sourceControl]
([eventType]
,[postTime]
,[SPID]
,[servername]
,[loginName]
,[userName]
,[databaseName]
,[schemaName]
,[objectID]
,[objectName]
,[objecttype]
,[hostName]
,[TSQL]
,[xmlEvent]
,[changeSetName]
,[changeSetID])
VALUES
(
@eventType,
GETDATE(),
@spid,
@serverName,
@loginName,
CURRENT_USER,
@databaseName,
CONVERT(sysname, @schema),
@objectID,
CONVERT(sysname, @object),
@objectType,
CONVERT(NVARCHAR(128), HOST_NAME()),
@SqlText,
@changeSetName,
@changeSetID
);
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddl_sourceControl_DBTrigger] ON DATABASE
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 3:32 pm
W/o a ddl trigger (which this is a good fit) it could be a simple SqlAgent job.
** note this assumes the where is removed, not commented out
if exists(
select * from information_schema.views
where table_name = 'YOURVIEW'
and view_definition like '%where%'
)
BEGIN
declare @aSql nvarchar(4000)
set @aSql = 'alter view YOURVIEW .....view code'
sp_executeSQL @aSql
END
March 26, 2010 at 7:19 am
Jason,
Thanks for the code sample. It seems pretty straightforward with one big catch. I see how you break out the text of the event into the @SqlText variable and how you use replace to update the statement within that variable. How do I now use that updated variable to alter the contents of my view?
Thanks
March 26, 2010 at 7:37 am
I was thinking about that after my post.
One thought is to EXEC the altered script. Then the under the hood actions are View created or altered by application, trigger fires and alters the view instantly after the app's change happens, thus overwriting it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply