July 25, 2011 at 9:24 am
Background - we have a table that contains 100 columns. This table is populated with data from a device that transmits any one of 70 different messages, which contain anywhere from 1 to 99 different pieces of data.
Based on the metadata we parse and store the messages into a single table.
The stored procedure creates views over this table based on the meta data. I have run into a size limitation for the NVARCHAR variable that stores the sql command to exectue.
I tried to cast the @ViewDefinition as NTEXT or create an NTEXT variable(not allowed)
Can't use Varchar for the view definition as only Nvarchar,Nchar, or Ntext are allowed.
Looking for some suggestions, thanks.
Here is the stored procedure that creates the views:
CREATE PROCEDURE [dbo].[CreateViewBasedOnSMI]
@SMI VARCHAR(3)
,@SubSMI VARCHAR(8) = NULL
,@SubSMI2 VARCHAR(8) = NULL
,@Version VARCHAR(8) = NULL
AS
-- Set default value for NULL params
SELECT
@SubSMI = COALESCE(@SubSMI, '')
,@SubSMI2 = COALESCE(@SubSMI2, '')
,@Version = COALESCE(@Version, '')
DECLARE Flds CURSOR
FOR
SELECT
FieldName
,ColumnMapping
,FieldNumber
FROM
myDatabase.dbo.MyFieldMapping
WHERE
UPPER(SMI) = UPPER(@SMI)
AND UPPER(SubSMI) = UPPER(@SubSMI)
AND UPPER(SubSMI2) = UPPER(@SubSMI2)
AND UPPER([Version]) = UPPER(@Version)
ORDER BY
FieldNumber
DECLARE
@FieldName VARCHAR(50)
,@ColumnMapping VARCHAR(50)
,@FieldNumber INT
,@SqlCmd VARCHAR(8000)
,@SourceTable VARCHAR(50) = 'dbo.Messages'
,@ViewDefinition NVARCHAR(MAX)
OPEN Flds
FETCH NEXT FROM Flds INTO @FieldName, @ColumnMapping, @FieldNumber
SET @SqlCmd = 'SELECT ID, ''' + @SMI + ''' AS SMI ,''' + @SubSMI
+ ''' AS SubSMI ,''' + @SubSMI2 + ''' AS SubSMI2 ,''' + @Version
+ ''' AS Version, MessageDate, Reg, FNumber, Stn, ThisTableCreateDT'
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlCmd = @SqlCmd + ' ,' + @ColumnMapping + ' AS ['
+ @FieldName + ']'
FETCH NEXT FROM Flds INTO @FieldName, @ColumnMapping, @FieldNumber
END
SET @SqlCmd = @SqlCmd + ' From ' + @SourceTable + ' Where SMI = '''
+ @SMI + ''' AND SubSMI = ''' + @SubSMI + ''' AND SubSMI2 = '''
+ @SubSMI2 + ''' AND Version = ''' + @Version + ''''
--PRINT @SqlCmd
CLOSE Flds
DEALLOCATE Flds
IF OBJECT_ID(N'dbo.' + @SMI) IS NOT NULL
BEGIN
SET @ViewDefinition = 'DROP VIEW dbo.' + @SMI
EXEC sp_executesql @ViewDefinition
END
SET @ViewDefinition = 'Create View dbo.' + @SMI + ' AS ' + @SqlCmd
--PRINT @ViewDefinition
EXEC sp_executesql @ViewDefinition
July 25, 2011 at 9:31 am
How about NVARCHAR(MAX) With exec?
July 25, 2011 at 9:34 am
i think because you are concatenating @ViewDefinition with @SQLCmd, they should both be NVARCHAR(max) definitions:
DECLARE
@FieldName VARCHAR(50)
,@ColumnMapping VARCHAR(50)
,@FieldNumber INT
,@SqlCmd NVARCHAR(MAX) ,@SourceTable VARCHAR(50) = 'dbo.Messages'
,@ViewDefinition NVARCHAR(MAX)
Lowell
July 25, 2011 at 9:35 am
that is what I am currently using, the variable name is @ViewDefinition, this is the variable that gets executed, it's too small and cuts off the view definition so the execute fails with a syntax error.
July 25, 2011 at 9:38 am
Can you post the DDL, I'll make this work on my system.
For sure the concatenation will do implicit convertions. This is annoying but I preffer to cast everything explicitely (every concatenations). That way I don't get screwed. 😉
July 25, 2011 at 9:42 am
Lowell,
That appeared to work, I'm confused now, when I do the print statement I only get part of the definition, but the execution works.
Is Nvarchar(4000) the same as Nvarchar(max)?
July 25, 2011 at 9:53 am
Ninja, what do you mean by post DLL?
Sorry nevermind, didn't read properly
July 25, 2011 at 10:24 am
Tom Van Harpen (7/25/2011)
Lowell,That appeared to work, I'm confused now, when I do the print statement I only get part of the definition, but the execution works.
Is Nvarchar(4000) the same as Nvarchar(max)?
Print stops at 8000 no matter what the real lenght. You can always print LEN(@cmd) to see the real length.
July 25, 2011 at 10:24 am
Thanks for the help, obviously my understanding of nvarchar(max) was not so good. I get it now, guess I've never run into this before.
July 25, 2011 at 10:33 am
Tom Van Harpen (7/25/2011)
Thanks for the help, obviously my understanding of nvarchar(max) was not so good. I get it now, guess I've never run into this before.
Strangely the same way I learnt about it :hehe:.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply