Solved

Over coming 4000 char limit

Posted on 2007-11-15
22
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 4
  • +2
22 Comments
 
LVL 6

Expert Comment

by:Marcel Hopman
ID: 20293134
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
ID: 20293200
... 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
ID: 20293974
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 8

Expert Comment

by:lexiflex
ID: 20296496
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
ID: 20300802
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
ID: 20301251
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
ID: 20301940
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
ID: 20314244
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
ID: 20314547
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
ID: 20315168

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
ID: 20315967
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20316023
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
ID: 20316135
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
ID: 20317871
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
ID: 20317961
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
ID: 20317963
and if that doesn't do it, then just
SET @sProcText3 =  'USE BSOL9'
without the GO should also work
0
 

Author Comment

by:Tunkster2
ID: 20319878
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
ID: 20320345
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
ID: 20320518
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
ID: 20320575
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
ID: 31409459
Thanks for your perseverence - I got it to run finally...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20322349
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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

726 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