CREATE VIEW in dynamic SQL
-
I would second the idea of using sp_OA* for this activity as the method of choise. But for the fun of it I can tell you that you can use a very unconventional way to achieve what you want with OPENROWSET
If you need a description of what is actually going on let me know
Try:
select * from OPENROWSET('SQLOLEDB','SERVER=XX;UID=yy;PWD=pp;Database=OtherDatabase','SET FMTONLY OFF; If object_id(''MYVIEW'') IS NULL begin exec(N'' CREATE VIEW dbo.MYVIEW as select * from Employees'') end; SELECT NULL') qry
* Noel -
Yeah, that's not particularly nice is it?
I'll stick with the OA methods that Paul has written for me (he's only actually sat 3 desks away from me - he's a wizard at using the OA methods).
This was made even more fun by the fact that the sproc we used to call all the OA methods had to be wrapped in an EXEC call itself. So our dynamic SQL to create the view was wrapped in more dynamic SQL to call the OA method wrapper proc. This was made all the more difficult by the pivoting requirement. Basically this was a lesson in handling apostrophes, as you can imagine. We've got '' and CHAR(39) all over the place.
The resulting sproc is pretty horrible to look at!!!
And I've just been informed by our requirements gathering boys that the original requirement which was simply to pivot some values has been made even more difficult. Oh joy!!Thanks for the advice everyone, especially to Paul of course!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Can we see what it looks like?? Haven't seen a real monster in some while
. -
Really? OK, here ya go! I could go through and colo(u)r code it all but I'm sure you're capable of copying it into QA
Oh yeah, the dynamic SQL was made even more difficult cos I had to decide which value field to pick out (ValueVarchar, ValueDecimal, ValueInt or ValueBit) as determined by the element lookup table...in case you were wondering what that bit was for! Fun fun fun!
Jamie (and Paul)
- SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
- /*
<procname>csp_DDLCreateCMRObject</procname>
<summary>
COPYRIGHT CONCHANGO 2005
- Build a given view in a given database based on metadata provided in cmrFactObject &
cmrFactObjectColumn
- It uses a call to csp_ExecuteImmediateInDatabaseViaOLEAutomation which enables us
to execute a DDL statement in a different database. [CREATE VIEW & DROP VIEW statements
only work for objects in the curernt database.]
</summary>
<parameters>
<param name="@pObjectName">Index to cmrFactMatrix</param>
</parameters>
<history>
<entry version="1.0.0" date="2005-08-03" name="Jamie Thomson" action="cqDMF00002181:Created" />
</history>
*/
ALTER PROCEDURE csp_DDLCreateCMRObject
(
@pDatabaseName VARCHAR(100),
@pObjectName VARCHAR(100),
@pPrintSQL BIT = 0,
@pExecuteSQL BIT = 1
)
- AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET DATEFORMAT DMY
- DECLARE @vSPName SYSNAME
DECLARE @vStepName SYSNAME
DECLARE @vError INT
DECLARE @vReturnStatus INT
- DECLARE @vCallSPMessage01 VARCHAR (500)
DECLARE @vCallSPMessage02 VARCHAR (500)
DECLARE @vPrintSQL CHAR(1)
DECLARE @vExecuteSQL CHAR(1)
- DECLARE @vStr0 AS VARCHAR(8000),
@vStr1 AS VARCHAR(8000),
@vStr2 AS VARCHAR(8000),
@vStr3 AS VARCHAR(8000)
- SET @vSPName = OBJECT_NAME(@@PROCID)
- SET @vStepName = 'Comments and CREATE Object'
SET @vStr0 = CHAR(39) + '/*This view is dropped and created every night as part of the publish process hence' + CHAR(10)
SET @vStr0 = @vStr0 + 'if you edit it, do not expect the edits to be here tomorrow*/' + CHAR(10) + CHAR(10)
SET @vStr0 = @vStr0 + ' CREATE VIEW vcmr' + @pObjectName + ' AS ' + CHAR(10)
- SET @vStepName = 'Field list'
SET @vStr1 = ' SELECT fReturnID, fReturnHeaderID, SortOrder, ReturnCurrencyID, PureCalYearID, ReportingCalYearID, CalQuarterID, CalMonthID, ReturnSyndicateClassOfBusinessID, RiskCodeID, RegionID, ReturnRealisticDisasterScenarioID, LORSID, ReturnExpenseTypeID, BrokerID, MemberSCDID, MajorLossID, ReturnReferenceLookupID' + CHAR(10)
- SET @vStepName = 'Pivoted values'
SET @vStr2 = ''
SELECT @vStr2 = @vStr2 + ' , ISNULL(MIN(CASE WHEN BaseElementNID = ''' + be.BaseElementNID + ''' THEN '+ CASE be.ValueType WHEN 'ValueInt' THEN 'ValueInt' WHEN 'ValueDecimal' THEN 'ValueDecimal' WHEN 'ValueVarchar' THEN 'ValueVarchar' ELSE 'ValueBit' END + ' END ), NULL) AS ' + be.BaseElementNID + CHAR(10)
- FROM (
SELECT fo.cmrFactObjectName, foc.ReturnBaseElementID
FROM dwPRESENTATION..cmrFactObject fo
INNER JOIN dwPRESENTATION..cmrFactObjectColumn foc
ON fo.cmrFactObjectID = foc.cmrFactObjectID
WHERE foc.EnabledFlag = 1
) fm
INNER JOIN (
SELECT CAST(CASE WHEN be.ValueIntFlag IS NOT NULL THEN 'ValueInt'
ELSE CASE WHEN be.ValueDecimalFlag IS NOT NULL THEN 'ValueDecimal'
ELSE CASE WHEN be.ValueVarcharFlag IS NOT NULL THEN 'ValueVarchar'
ELSE 'ValueBit'
END
END
END AS VARCHAR(12)) AS ValueType,
be.ReturnBaseElementID,
be.BaseElementNID
FROM ReturnBaseElement be
WHERE be.ValueIntFlag IS NOT NULL
OR be.ValueDecimalFlag IS NOT NULL
OR be.ValueVarcharFlag IS NOT NULL
OR be.ValueBitFlag IS NOT NULL) be
ON fm.ReturnBaseElementID = be.ReturnBaseElementID
WHERE ASCII(LEFT(be.BaseElementNID, 1)) >= 65
AND ASCII(LEFT(be.BaseElementNID, 1)) <= 90
AND fm.cmrFactObjectName = @pObjectName
- --Replace ' with ''
SET @vStr2 = REPLACE(@vStr2, CHAR(39), CHAR(39) + CHAR(39))
- SET @vStepName = 'FROM and GROUP BY clauses'
SET @vStr3 = '
FROM (
SELECT be.BaseElementNID,
f.fReturnID, f.fReturnHeaderID, f.ReturnBaseElementMappingID, f.SortOrder,
f.ValueInt, f.ValueDecimal, f.ValueVarchar, f.ValueBit,
f.ReturnCurrencyID, f.PureCalYearID, f.ReportingCalYearID, f.CalQuarterID,
f.CalMonthID, f.ReturnSyndicateClassOfBusinessID, f.RiskCodeID, f.RegionID,
f.ReturnRealisticDisasterScenarioID, f.LORSID, f.ReturnExpenseTypeID,
f.BrokerID, f.MemberSCDID, f.MajorLossID, f.ReturnReferenceLookupID
FROM fReturn f
INNER JOIN ReturnBaseElementMapping bem
ON f.ReturnBaseElementMappingID = bem.ReturnBaseElementMappingID
INNER JOIN ReturnBaseElement be
ON bem.ReturnBaseElementID = be.ReturnBaseElementID) AS q
GROUP BY
fReturnID, fReturnHeaderID, SortOrder, ReturnCurrencyID, PureCalYearID, ReportingCalYearID, CalQuarterID, CalMonthID, ReturnSyndicateClassOfBusinessID, RiskCodeID, RegionID, ReturnRealisticDisasterScenarioID, LORSID, ReturnExpenseTypeID, BrokerID, MemberSCDID, MajorLossID, ReturnReferenceLookupID'
IF @@Error <> 0
GOTO OnError
SET @vStr3 = @vStr3 + CHAR(39) +CHAR(10) --Put a ' on the end of it
IF @@Error <> 0
GOTO OnError
- SET @vCallSPMessage01 = '
DECLARE @vRet INT
DECLARE @vOutput VARCHAR (200)
DECLARE @vSource VARCHAR (200)
DECLARE @vDescription VARCHAR (200)
SET NOCOUNT ON
EXEC @vRet = dbo.csp_ExecuteImmediateInDatabaseViaOLEAutomation
@pDatabaseName,
'
SET @vCallSPMessage01 = REPLACE (@vCallSPMessage01, '@pDatabaseName', CHAR(39) + @pDatabaseName + CHAR(39))
- SET @vPrintSQL = CAST(@pPrintSQL AS CHAR(1))
SET @vExecuteSQL = CAST(@pExecuteSQL AS CHAR(1))
- SET @vCallSPMessage02 = ',
@vPrintSQL,
@vExecuteSQL,
@vOutput OUT,
@vSource OUT,
@vDescription OUT
- '
- SET @vCallSPMessage02 = REPLACE (@vCallSPMessage02, '@vPrintSQL', @vPrintSQL)
SET @vCallSPMessage02 = REPLACE (@vCallSPMessage02, '@vExecuteSQL', @vExecuteSQL)
- SET @vStepName = 'Print dynamic SQL'
IF @pPrintSQL = 1
- PRINT @vCallSPMessage01
PRINT @vStr0
PRINT @vStr1
PRINT @vStr2
PRINT @vStr3
PRINT @vCallSPMessage02
IF @@Error <> 0
GOTO OnError
- SET @vStepName = 'Execute dynamic SQL'
IF @pExecuteSQL = 1
EXEC (@vCallSPMessage01 + @vStr0 + @vStr1 + @vStr2 + @vStr3 + @vCallSPMessage02)
- IF @@Error <> 0
GOTO OnError
ELSE
GOTO OnSuccess
- OnError:
--Common Error Handling Section
BEGIN
RAISERROR(50001,16,1,@vSPName,@vStepName)
RETURN(1)
END
- OnSuccess:
--Common End Section
BEGIN
RETURN(0)
END
- GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson - SET QUOTED_IDENTIFIER ON
-
Looks like this one didn't catch on right away... might have been to direct too the point
. -
I did try it, honest
. Check the 7th post of this thread. Admittedly not wrapping it in exec(). If only I'd known!!!!Still, our chosen method is better so I'm happy with it.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Jamie,
Believe me, The OPENROWSET that I posted is very tricky, not only you are forced to use exec but all the extra stuff that is in there is necessary
* Noel -
Why is the set fmtonly off required?
-
As tricky as the stuff in the 30th post of this thread?
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
No particular reason. Its just part of our sproc template that's been in use since well before I came here.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I was referring to noeld's working solution for the openrowset.
-
BTW that post is actually the 24th message of the thread.
-
OK, little dissection of the code here:
select * from OPENROWSET('SQLOLEDB','SERVER=XX;UID=yy;PWD=pp;Database=OtherDatabase','SET FMTONLY OFF; If object_id(''MYVIEW'') IS NULL begin exec(N'' CREATE VIEW dbo.MYVIEW as select * from Employees'') end; SELECT NULL') qry
'SQLOLEDB','SERVER=XX;UID=yy;PWD=pp;Database=OtherDatabase'
Probably everyone knows that this was the way to switch db context:
'SET FMTONLY OFF;
This is required to tell the linkserver not to analyze (parse) the contents because it normally calls the code first with SET FMTONLY OFF to figure out the parameter types and quantity to be returned and then with ON to actually execute it. With this trick you are telling it to forget about it when called with 'ON' . This is necessary also when the stored procedure you call uses temp tables or dynamic sql as in this case
If object_id(''MYVIEW'') IS NULL begin exec(...) end;
because the code will be called twice you need to surround the creation with a check so that it doesn't bomb out because the view already exists in the second pass. The exec is to guarrantie that CREATE View is the first statement on the batch
SELECT NULL
Well we are using Openrowset after all and something needed to be returned so there you go
hth
* Noel -
Who the %?(&?%$ figured that technic out and how much time did it take him????????????
Can I marry him/her
😕 -
Who the %?(&?%$ figured that technic out and how much time did it take him????????????
Self
To be fair the bits and pieces are only part of different scenarios that I had to deal with and not many times you need to combine them all (like in this case).
The SET FMTONLY OFF usage is one of the most missunderstood items on linked servers. You can confirm if you remove the check for the existence that "the view already exists" error will popup. Select Null is a comodity that you figured out as well, right?
Today was a matter of putting together the whole thing but each one of them were experiments are various times
So to anwser your question let's just say ... it took long enough
* Noel
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply