Solved

Over coming 4000 char limit

Posted on 2007-11-15
22
213 Views
Last Modified: 2010-04-21
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
Comment
Question by:Tunkster2
  • 10
  • 5
  • 4
  • +2
22 Comments
 
LVL 6

Expert Comment

by:Marcel Hopman
Comment Utility
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
 
LVL 18

Expert Comment

by:Yveau
Comment Utility
... 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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 8

Expert Comment

by:lexiflex
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 6

Assisted Solution

by:Marcel Hopman
Marcel Hopman earned 50 total points
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility

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
 

Author Comment

by:Tunkster2
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 18

Expert Comment

by:Yveau
Comment Utility
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
 
LVL 25

Accepted Solution

by:
imitchie earned 225 total points
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
and if that doesn't do it, then just
SET @sProcText3 =  'USE BSOL9'
without the GO should also work
0
 

Author Comment

by:Tunkster2
Comment Utility
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
 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 225 total points
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 

Author Comment

by:Tunkster2
Comment Utility
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
 

Author Closing Comment

by:Tunkster2
Comment Utility
Thanks for your perseverence - I got it to run finally...
0
 
LVL 18

Expert Comment

by:Yveau
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now