February 12, 2013 at 9:06 am
Hi All
I have a maintenance process to run whereby Indexed views are dropped and recreated during down time. They are dropped and recreated so that the View the index is restricted by a more recent date, therefore making the indexes smaller. The indexes are only there to enforce uniqueness across fields in separate tables anyway.
Now, dropping the views is simple enough, but recreating them is proving to be more difficult.
I have an sp_ExecuteSQL statement for each to create them, passing in a date parameter which is declared at the start. For example:
--DROP TABLE dbo.ViewExample
CREATE TABLE dbo.ViewExample
(
Name VARCHAR(20) NULL,
Date DATETIME2 NULL
)
GO
DECLARE @ViewStr AS NVARCHAR(MAX)
DECLARE @ViewDate AS DATETIME = GETUTCDATE()
DECLARE @ViewParams AS NVARCHAR(50) = N'@Viewdate DATETIME'
SET @ViewStr = N'
CREATE VIEW [dbo].[ViewEx]
WITH SCHEMABINDING
AS
SELECT
name,date
FROM
dbo.ViewExample
WHERE
date > CONVERT(DATETIME2, ''' + CAST(@ViewDate AS VARCHAR(40)) + ''', 120)
'
PRINT @ViewStr
EXEC sp_ExecuteSQL @Statement = @ViewStr, @Parameters = @ViewParams, @ViewDate = @ViewDate
This gives me two errors:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'VIEW'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
Despite being syntactically correct.
I'll then have to nest this statement within another sp_ExecuteSQL command to change the current context. As a view creation statement must be the first statement in the batch, and this Stored Procedure will be run in a different DB, I'll have to change the current context with the first sp_ExecuteSQL statement. Is this possible?
It's more than possible that my usage of sp_ExecuteSQL is incorrect, please feel free to correct me on anything that looks out of place.
Thanks in advance for your help.
February 12, 2013 at 9:13 am
it works for me fine when i use
EXEC(@ViewStr)
--or
EXEC sp_ExecuteSQL @Statement = @ViewStr
I don't think you need the parameters in the EXEC anyway;
the string passed doesn't have any parameters declared in it; you constructed a static string just before the call.
Lowell
February 12, 2013 at 9:24 am
I'm not quite sure why but the problem is the parameters you are trying to pass in.
If you look at the value @ViewStr takes when it is passed into sp_ExecuteSQL then you can see that it does not actually use any parameters, so you do not need to pass any in.
If you change your last line to EXEC sp_ExecuteSQL @Statement = @ViewStr then everything should work fine.
February 12, 2013 at 9:37 am
Thank you both for your responses.
The reason why the parameters are there is because of the second part of the problem, this code must be executed against a different database to where the procedure is stored, and you can't specify a USE command immediately before a View Create in the same batch. I also need to set ANSI NULLS and QUOTED_IDENTIFIER ON to allow the view to be indexable.
February 12, 2013 at 9:52 am
Sorry, last post was a bit vague. This one may or may not help 😀
I know this code doesn't work, but this is the kind of thing I'm trying to do. Once again, my usage may be way off, but this View needs to be created in another DB Instance.
CREATE TABLE dbo.ViewExample
(
Name VARCHAR(20) NULL,
Date DATETIME2 NULL
)
GO
DECLARE @ViewStr AS NVARCHAR(MAX)
DECLARE @ViewDate AS DATETIME = GETUTCDATE()
DECLARE @ViewParams AS NVARCHAR(50) = N'@Viewdate DATETIME'
DECLARE @SQLStr AS NVARCHAR(MAX)
EXEC sp_ExecuteSQL @Statement = N'
USE master
SET ANSI_NULLS ON
SET @ViewStr = N''
CREATE VIEW [dbo].[ViewEx]
WITH SCHEMABINDING
AS
SELECT
name,date
FROM
dbo.ViewExample
WHERE
date > CONVERT(DATETIME2, '''' + CAST(@ViewDate AS VARCHAR(40)) + '''', 120)
EXEC sp_ExecuteSQL @Statement = @ViewStr
', @Parameters = @ViewParams, @ViewDate = @ViewDate
February 12, 2013 at 10:04 am
To execute the code against a different database then you just need to put the database name infront of the sp_ExecuteSQL stored proc. So:
EXEC DatabaseName.dbo.sp_ExecuteSQL
I think if you just set ANSI NULL and QUOTED_IDENTIFIER on within the session, they should apply but someone else on here can give you better advice on that.
February 12, 2013 at 10:16 am
Called sp_ExecuteSQL as a 3 part name works brilliantly, however it won't recognise that I have set QUOTED_IDENTIFIER and ANSI_NULLS to on earlier in the procedure.
Much closer though, thank you 🙂
February 12, 2013 at 10:25 am
chris.johnson 46127 (2/12/2013)
To execute the code against a different database then you just need to put the database name infront of the sp_ExecuteSQL stored proc. So:
EXEC DatabaseName.dbo.sp_ExecuteSQLI think if you just set ANSI NULL and QUOTED_IDENTIFIER on within the session, they should apply but someone else on here can give you better advice on that.
Just spotted that the SP was created with ANSI_NULLS and QUOTED_IDENTIFIER both off, those being the overriding options by the looks of it.
Problem solved, thank you very much 🙂
February 12, 2013 at 10:55 am
This won't really help fix your problem, but my guess (and it's nothing more than an educated guess) is that when you pass in parameters sp_executesql automatically adds code to the top of your code to declare and populate the parameters.
This part may fix your problem 🙂
My default in my query window is ANSI NULLS ON. I used sp_executesql (no extra parameters) to create a view and it was ANSI NULLS ON. I used the following code to check.
select OBJECTPROPERTY(object_id('viewex'), 'IsAnsiNullsOn')
Next I ran the following
USE msdb
GO
SET ANSI_NULLS OFF
GO
DECLARE @ViewStr AS NVARCHAR(4000)
DECLARE @ViewDate AS DATETIME = GETUTCDATE()
DECLARE @ViewParams AS NVARCHAR(50) = N'@Viewdate DATETIME'
SET @ViewStr = N'CREATE VIEW [dbo].[ViewEx]
WITH SCHEMABINDING
AS
SELECT
name,date
FROM
dbo.ViewExample
WHERE
date > CONVERT(DATETIME2, ''' + CAST(@ViewDate AS VARCHAR(40)) + ''', 120);
'
SET @ViewStr = 'CREATE VIEW dbo.viewex2
AS
SELECT * FROM viewexample'
PRINT @ViewStr
EXEC dba.dbo.sp_ExecuteSQL @ViewStr
When I checked
select OBJECTPROPERTY(object_id('viewex2'), 'IsAnsiNullsOn')
ANSI NULLS is now set to OFF for the new view.
So it looks like if you change your connection properties, then run sp_executesql from whatever database you like you may be able to eliminate the need for the extra layer and the parameters.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply