[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Over coming 4000 char limit

Want to have scripts, that create views - but the data needed in the view requires language in the script that ends up being over the 4k limit SQL has...
eg.
SET @sProcText = ''
SET @sProcText = @sProcText + 'CREATE VIEW VIEW_EMPLOYEE_CHECK_COMPOSITE AS '+ @sCRLF
SET @sProcText = @sProcText + 'SELECT E.Employee_ID,'+ @sCRLF
SET @sProcText = @sProcText + 'E.Employee_Name,'+ @sCRLF
SET @sProcText = @sProcText + 'CK.Department_Code,'+ @sCRLF
0
Tunkster2
Asked:
Tunkster2
  • 10
  • 5
  • 4
  • +2
3 Solutions
 
Marcel HopmanCommented:
In SQL 2000 the limit is 4000 (nvarchar), in SQL 2005 there is no limit : varchar(MAX).

You could test the lenght of your variable and when you are reaching the limit continue in a new variable and then concatenate the result:

EXEC (@sProcText1 + @sProcText2)
will work


0
 
YveauCommented:
... something else. If not using any special characters, use varchar() data types instead of nvarchar() !
If you have a nvarchar(100) data type, t allows you to store 50 characters (2 bytes per char)
If you have a varchar(100) data type, it allows you to store 100 characters (1 byte per char)

But as mahopman said, in SQL 2005 varchar(max) makes sure the length is not an issue any more ... you can sore up tot 2GB of data in a field of the varchar(max) data type.

Hope this helps ...
0
 
imitchieCommented:
some tidbit info for you

SET @sProcText = '
CREATE VIEW VIEW_EMPLOYEE_CHECK_COMPOSITE AS
SELECT E.Employee_ID,
E.Employee_Name,
CK.Department_Code,'

is far more readable.  SQL server can handle linebreaks in strings.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lexiflexCommented:
When using SQL 2000 it´s possible to execute statements dynamicly that are over NVARCHAR(4000) or VARCHAR(8000) by using the EXEC statement. Simply declare multiple variables of the type NVARCHAR(4000) or VARCHAR(8000) and concatenate them when executing:

EXEC (@sProcText1 + @sProcText2 + @sProcText3 + ...)
0
 
Tunkster2Author Commented:
so... if this is my code, and I need to split it due to size - what exactly would the altered text be (assume I break it at "midlname"
Would I rename the 2nd half as sproctext3 (see execution lines at end)
Plz cut/paste exactly how'd it go THX!!

SET @sProcText = @sProcText + 'CREATE VIEW VIEW_EMPLOYEE AS '+ @sCRLF
SET @sProcText = @sProcText + 'SELECT '+ @sCRLF
SET @sProcText = @sProcText + 'E.FRSTNAME AS FirstName,'+ @sCRLF
SET @sProcText = @sProcText + 'E.LASTNAME AS LastName,'+ @sCRLF
SET @sProcText = @sProcText + 'E.MIDLNAME AS Middle,'+ @sCRLF
SET @sProcText = @sProcText + 'E.JOBTITLE AS Job_Title,'+ @sCRLF
SET @sProcText = @sProcText + 'E.BRTHDATE AS Birth_Date,'+ @sCRLF
SET @sProcText = @sProcText + 'FROM '+ @sCRLF
SET @sProcText = @sProcText + 'UPR00100 E '+ @sCRLF
SET @sProcText = @sProcText + 'JOIN UPR00102 A ON E.EMPLOYID = A.EMPLOYID AND (A.ADRSCODE LIKE ''PR%'' OR A.ADRSCODE LIKE ''MA%'')'+ @sCRLF
SET @sProcText = @sProcText + 'JOIN ETHNIC_GROUP EG ON E.ETHNORGN = EG.ETHNIC_GROUP_CODE '+ @sCRLF

SET @sProcText = @sProcText + ' '+ @sCRLF
SET @sProcText2 = 'USE '+@sDatabaseName + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE' )
0
 
Tunkster2Author Commented:
More specifically - if I rname some of the lines to sProcText3
and concatenante as said... I get a Syntax Error... below is the before and after:

BEFORE:
SET @sProcText2 = 'USE '+@sDatabaseName + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText

AFTER:
SET @sProcText2 = 'USE '+@sDatabaseName + @sCRLF + 'EXECUTE dbo.sp_executesql (@sProcText+@sProcText3)
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
0
 
Marcel HopmanCommented:
Just directly EXECUTE your concatenate statements, don't use .sp_executesql

e.g. :
DECLARE
@DynamicSQL1	VARCHAR(8000),
@DynamicSQL2	VARCHAR(8000),
@CrLf			CHAR(2),
@Tab			CHAR(1)
 
SET @CrLf = CHAR(10) + CHAR(13)
 
 
SET	@DynamicSQL1 = 'USE [Foodmart 2005]' + @CrLf
 
SET @DynamicSQL2 = 'SELECT	*' + @CrLf
SET @DynamicSQL2 = @DynamicSQL2 + 'FROM	[customer]'
 
EXEC (@DynamicSQL1 + @DynamicSQL2)

Open in new window

0
 
Tunkster2Author Commented:
Ohhh - almost there!
I am creating a view in this example - I don't know if that effects it
I am getting an error that says :

Server: Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.

ANy ideas ??
The first segment starts off "Create view..."
The 2nd 1/2 obviously doesn't have that wording, snce its a continuation of the view...
0
 
Tunkster2Author Commented:
Seems like if I don't use the .sp_executesql I get that error - even when I take the concatenation off...
Sorry this is becoming such a pain - I REALLY appreciate your help
0
 
imitchieCommented:

SET @sProcText = '
CREATE VIEW VIEW_EMPLOYEE AS 
SELECT 
E.FRSTNAME AS FirstName,
E.LASTNAME AS LastName,
E.MIDLNAME AS Middle,
E.JOBTITLE AS Job_Title,
E.BRTHDATE AS Birth_Date,'
SET @sProcText2 = '
FROM 
UPR00100 E 
JOIN UPR00102 A ON E.EMPLOYID = A.EMPLOYID
  AND (A.ADRSCODE LIKE ''PR%'' OR A.ADRSCODE LIKE ''MA%'')
JOIN ETHNIC_GROUP EG ON E.ETHNORGN = EG.ETHNIC_GROUP_CODE'
 
SET @sProcText3 = '
USE '+@sDatabaseName + '
GO
' -- extra line here after GO
 
EXEC( @sProcText3 + @sProcText1 + @sProcText2 )
PRINT('CREATE View for Database '+@sDatabaseName+' Function : VIEW_EMPLOYEE' )

Open in new window

0
 
Tunkster2Author Commented:
Sooo close
One error =
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@sProcText3'.


I do have, at the top:
DECLARE @sProcText3 nvarchar(4000),
@sProcText2 nvarchar(4000),
@sProcText nvarchar(4000),
@sCRLF char(2),
@sTAB char(1)

SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
0
 
imitchieCommented:
can you please paste the entire SQL again please?
@sProcText3 is clearly defined at the top already.. are you sure it's not quoted somewhere it shouldn't be, like

EXEC( '@sProcText3' + @sProcText1 + @sProcText2 )
?
0
 
Tunkster2Author Commented:
I REALLY appreciate this !!
Here's the whole enchilada
The Code Snippet attached gives me the Error:
Must declare the variable '@sProcText3'

If I change the quotes on the line before the GO to:
SET @sProcText3 = ' USE BSOL9
GO
'
I get this error:

Server: Msg 105, Level 15, State 1, Line 78
Unclosed quotation mark before the character string ' USE BSOL9
'.
Server: Msg 170, Level 15, State 1, Line 78
Line 78: Incorrect syntax near ' USE BSOL9
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '

EXEC( @sProcText3 + @sProcText + @sProcText2 )
PRINT('.
Server: Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
Server: Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark before the character string ' )
DECLARE @sProcText3 nvarchar(4000),
@sProcText2 nvarchar(4000),
@sProcText nvarchar(4000),
@sCRLF char(2),
@sTAB char(1)
 
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
 
 
SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE BSOL9' + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE_CHECK_DEPT' )
 
SET @sProcText ='
CREATE VIEW VIEW_EMPLOYEE_CHECK_DEPT AS 
SELECT W.EMPLOYID AS Employee_ID,
ISNULL(dbo.FN_GET_DEPARTMENT_CODE(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT_CODE(MAX(E.DEPRTMNT))) AS Department_Code,
ISNULL(dbo.FN_GET_DEPARTMENT(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT(MAX(E.DEPRTMNT))) AS Department,
dbo.FN_GET_EMP_ACCOUNT(W.EMPLOYID) AS Account#, 
MAX(SUBSTRING(E.SOCSCNUM,1,3) + ''-'' + SUBSTRING(E.SOCSCNUM,4,2) + ''-'' + SUBSTRING(E.SOCSCNUM,6,4)) AS SSN,
W.CHEKNMBR AS Check_Number,
MAX(SUBSTRING(W.EMPLOYID, 3, 3)) AS Client_Code,
dbo.FN_GET_EMP_CLIENT_NAME(W.EMPLOYID) AS Client_Name,'
 
SET @sProcText2 = '
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''SICK EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Sick,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''HOLIDAY EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Holiday,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Regular,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN2'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan2,
ISNULL(dbo.FN_GET_EMP_PAYTYPE_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS Gross 
 FROM UPR30300 W INNER JOIN UPR00100 E ON W.EMPLOYID = E.EMPLOYID 
 GROUP BY W.DEPRTMNT, W.EMPLOYID, W.CHEKDATE, W.CHEKNMBR'
 
SET @sProcText3 =  'USE BSOL9' 
GO
 
EXEC( @sProcText3 + @sProcText + @sProcText2 )

Open in new window

0
 
YveauCommented:
Remove the 'Go' from line 40.
After each 'Go' you have to declare every parameter again as you start a new batch from that point on.

Hope this helps ...
0
 
imitchieCommented:
Tunkster2: as Yveau has pointed out, change

SET @sProcText3 =  'USE BSOL9'
GO

to

SET @sProcText3 =  'USE BSOL9' +char(13) + char(10) + 'GO'
GO is special, don't leave it alone on it's own, not even in multi-line single-quoted strings...
0
 
imitchieCommented:
and if that doesn't do it, then just
SET @sProcText3 =  'USE BSOL9'
without the GO should also work
0
 
Tunkster2Author Commented:
Not quite :(
I am getting an error w/ the GO at the end=
Line 2: Incorrect syntax near 'GO'.
'CREATE VIEW' must be the first statement in a query batch.

Then - if I try w/o the GO, I get:
'CREATE VIEW' must be the first statement in a query batch.
0
 
YveauCommented:
First I wonder why you still don't use varchar(max) ... you're on SQL 2005 right ?!

But this might be a solution:
Just include the correct database into the FQN of the view.
That way you don't need the @sProcText3 parameter ...

Hope this helps ...


DECLARE @sProcText3 nvarchar(4000),
@sProcText2 nvarchar(4000),
@sProcText nvarchar(4000),
@sCRLF char(2),
@sTAB char(1)
 
SET @sTAB = char(9)
SET @sCRLF = char(13) + char(10)
 
 
SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE BSOL9' + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE_CHECK_DEPT' )
 
SET @sProcText ='
CREATE VIEW BSOL9..VIEW_EMPLOYEE_CHECK_DEPT AS 
SELECT W.EMPLOYID AS Employee_ID,
ISNULL(dbo.FN_GET_DEPARTMENT_CODE(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT_CODE(MAX(E.DEPRTMNT))) AS Department_Code,
ISNULL(dbo.FN_GET_DEPARTMENT(W.DEPRTMNT),dbo.FN_GET_DEPARTMENT(MAX(E.DEPRTMNT))) AS Department,
dbo.FN_GET_EMP_ACCOUNT(W.EMPLOYID) AS Account#, 
MAX(SUBSTRING(E.SOCSCNUM,1,3) + ''-'' + SUBSTRING(E.SOCSCNUM,4,2) + ''-'' + SUBSTRING(E.SOCSCNUM,6,4)) AS SSN,
W.CHEKNMBR AS Check_Number,
MAX(SUBSTRING(W.EMPLOYID, 3, 3)) AS Client_Code,
dbo.FN_GET_EMP_CLIENT_NAME(W.EMPLOYID) AS Client_Name,'
 
SET @sProcText2 = '
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''SICK EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Sick,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''HOLIDAY EARNINGS'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Holiday,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''REGULAR'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''1''),0) AS Regular,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan,
ISNULL(dbo.FN_EMP_AMT(W.EMPLOYID, ''401K LOAN2'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR,''2''),0) AS K401_Loan2,
ISNULL(dbo.FN_GET_EMP_PAYTYPE_AMT(W.EMPLOYID,''1'', W.CHEKDATE, W.DEPRTMNT, W.CHEKNMBR),0) AS Gross 
 FROM UPR30300 W INNER JOIN UPR00100 E ON W.EMPLOYID = E.EMPLOYID 
 GROUP BY W.DEPRTMNT, W.EMPLOYID, W.CHEKDATE, W.CHEKNMBR'
 
EXEC( @sProcText + @sProcText2 )

Open in new window

0
 
Tunkster2Author Commented:
I am stuck w/ SQL 2000

Putting the database there gives me the error:

'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.

I know I must be close - I appreciate your help/perseverence...
Is this possible?

0
 
Tunkster2Author Commented:
I changed the NvarChar(4000)  to varchar(8000)
Then changed the statement to one long vs. two to concatenante
added back the GO line and voila! It worked
BUT
My drop line gives me an error - I am deducing its looking for an "n" type varchar (based on the error) - what do I change on the attached DROP statement to make it work ? Then I am done :)
THXXXX
ERROR=
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


SET @sProcText = ''
SET @sProcText = @sProcText + 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[VIEW_EMPLOYEE_CHECK_DEPT]'') and OBJECTPROPERTY(id, N''IsView'') = 1)' + @sCRLF
SET @sProcText = @sProcText + 'drop view [dbo].[VIEW_EMPLOYEE_CHECK_DEPT] ' + @sCRLF
SET @sProcText2 = 'USE BSOL9' + @sCRLF + 'EXECUTE dbo.sp_executesql @sProcText'
EXECUTE dbo.sp_executesql @sProcText2 , N'@sProcText nvarchar(4000)', @sProcText
PRINT('Drop VIEW for Database BSOL9 View : VIEW_EMPLOYEE_CHECK_DEPT' )

Open in new window

0
 
Tunkster2Author Commented:
Thanks for your perseverence - I got it to run finally...
0
 
YveauCommented:
From BOL:

sp_executesql [ @stmt = ] stmt
[
    {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' }
     {, [ @param1 = ] 'value1' [ ,...n ] }
]
 

Arguments
[ @stmt = ] stmt
Is a Unicode string that ...

So it has to be nvarchar() ...

Hope this helps ...


0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now