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
Solved

Over coming 4000 char limit

Posted on 2007-11-15
22
243 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

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…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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