 | |
large scripted stored procedure
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
large scripted stored procedure -
05-17-2011
, 09:30 AM
#2
| |||
| |||
|
|
I need to compile stored procedures and functions larger than 8000bytes on Sql Server 2005. I even tried CLR procedure, but unfortunately it didnt work. what can I do. Please help me. |
with, there is a stored procedure which is 3806 lines long, and some
160.000 bytes in size.
Could you describe your problem in more detail?
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
#3
| |||
| |||
|
|
avcitamer (ttavci (AT) gmail (DOT) com) writes: I need to compile stored procedures and functions larger than 8000bytes on Sql Server 2005. I even tried CLR procedure, but unfortunately it didnt work. what can I do. Please help me. Not sure that I understand where the problem is. In the system I work with, there is a stored procedure which is 3806 lines long, and some 160.000 bytes in size. Could you describe your problem in more detail? |
have varchar(max)?
Sincerely,
Gene Wirchenko
#4
| |||
| |||
|
|
avcitamer (tta... (AT) gmail (DOT) com) writes: I need to compile stored procedures and functions larger than 8000bytes on Sql Server 2005. I even tried CLR procedure, but unfortunately it didnt work. what can I do. Please help me. Not sure that I understand where the problem is. In the system I work with, there is a stored procedure which is 3806 lines long, and some 160.000 bytes in size. Could you describe your problem in more detail? -- Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se Links for SQL Server Books Online: SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx |
below...
declare @sql01 varchar(max) set @sql01='create procedure dbo.foo() as
begin bla bla bla......' --8000 bytes long...
declare @sql02 varchar(max) set @sql02='bla bla bla......bla bla' --
8000 bytes long...
declare @sql03 varchar(max) set @sql03='bla bla bla...... end' -- 8000
bytes long...
exec (@sql01+ @sql02+@sql03)
but sql server sees only first 8000 bytes of script and gives
error....
this situation occurs during dynamic creation...(not at query
analyzer)
thanks
#5
| |||
| |||
|
|
In my project, I compile stored procedures dynamically as shown below... declare @sql01 varchar(max) set @sql01='create procedure dbo.foo() as begin bla bla bla......' --8000 bytes long... declare @sql02 varchar(max) set @sql02='bla bla bla......bla bla' -- 8000 bytes long... declare @sql03 varchar(max) set @sql03='bla bla bla...... end' -- 8000 bytes long... exec (@sql01+ @sql02+@sql03) but sql server sees only first 8000 bytes of script and gives error.... this situation occurs during dynamic creation...(not at query analyzer) thanks |
But apart from that, the scheme as presented should work. Obviously, you are
doing something wrong, but since I don't see your actual code, I can say
what it is.
How do you conclude that SQL Server only sees the first 8000 bytes? You are
not looking at SELECT @sql01 + @sql02 + ... in Mgmt Studio, are you? SSMS
will only show you the first 8000 characters.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#6
| |||
| |||
|
|
avcitamer (tta... (AT) gmail (DOT) com) writes: In my project, I compile stored procedures dynamically as shown below... declare @sql01 varchar(max) set @sql01='create procedure dbo.foo() as begin bla bla bla......' --8000 bytes long... declare @sql02 varchar(max) set @sql02='bla bla bla......bla bla' -- 8000 bytes long... declare @sql03 varchar(max) set @sql03='bla bla bla...... end' -- 8000 bytes long... exec (@sql01+ @sql02+@sql03) but sql server sees only first 8000 bytes of script and gives error.... this situation occurs during dynamic creation...(not at query analyzer) thanks Since varchar(MAX) can fit up to 2GB of data, why mess with many variables? But apart from that, the scheme as presented should work. Obviously, you are doing something wrong, but since I don't see your actual code, I can say what it is. How do you conclude that SQL Server only sees the first 8000 bytes? You are not looking at SELECT @sql01 + @sql02 + ... in Mgmt Studio, are you? SSMS will only show you the first 8000 characters. -- Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Alıntıyı gizle - - Alıntıyı göster - |
you saved my brain being burned.
thaks a lot ...
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.