Return More Than 4000 Characters From CLR Stored Procedure
Mar 31, 2006
Hi,
I have a clr stored procedure that takes in 2 parameters, input xml and a query name. The stored procedure transforms the xml with a the xslt for the given query name (stored in a database). I am currently using and output parameter that is of type NVarChar(4000) to retrieve the xml in .net.
This all works fine unless the xml that is being transformed is greater than 4000 characters which will happen. Are there any ways of returning a string/xml greater than 4000 characters (in the region of 60-70k characters).
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
My problem (excuse me for being a novice) is that whenever the query becomes complex in the @whereclause (and it does) it cant exceed the character limit of a nvarchar. Now if someone could shed some ligfht on this for me I would be incredibly grateful. I have tried doing the exec(@var1 + @var2) but this did not seem to work.
I have included the stored procedure below.
ALTER PROCEDURE [dbo].[SearchTenderMultiRegions] ( @whereclause nVarChar(MAX), --4000 chars @PageIndex int, @PageSize int ) AS
SET NOCOUNT ON;DECLARE @sql nvarChar(MAX)
SET @sql= 'SELECT * FROM (SELECT DISTINCT TOP (' + CONVERT(nvarchar(10),(@PageIndex*@PageSize)) SET @sql= @sql + ') ROW_NUMBER() OVER (ORDER BY Tenders.ID DESC)AS Row,
Tenders.ID,Tenders.Title,Address.Suburb,Tenders.ClosingDateTime,Tenders.IsApproved,Tenders.TendersSourceID, Tenders.SourceDate, UserTracking.CreateUserID,UserTracking.CreateDateTime FROM Tenders LEFT JOIN TenderContact ON Tenders.ID = TenderContact.TendersID LEFT JOIN TenderCategory ON Tenders.ID = TenderCategory.TendersID LEFT JOIN TenderRegion ON Tenders.ID = TenderRegion.TendersID LEFT JOIN RegionStateCountry ON TenderRegion.RegionStateCountryID = RegionStateCountry.ID LEFT JOIN Address ON Tenders.ID = Address.TendersID LEFT JOIN UserTracking ON Tenders.ID = UserTracking.TendersID WHERE '
SET @sql= @sql + @whereclause SET @sql = @sql + ') as TenderSearchEntries WHERE Row between ('
SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex) SET @sql= @sql + ' - 1) * ' SET @sql= @sql + CONVERT(nvarchar(10), @PageSize) SET @sql= @sql + '+ 1 and ' SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex) SET @sql= @sql + ' * ' SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)
Is it not possible for expressions to work with variables that contain more than 4000 characters? I've heard of a limitation of expressions and 4000 characters, but I thought this meant that the expression itself cannot be more than 4000 characters -- not that the expression cannot work with values of more than 4000 characters.
For example, I have a two variables:
Test1 (type = String): Value = [Some really long string whose length is > 4000 characters]
A trigger existed on the job steps table which captured any changes before and after. The insert table was limited changes to 4000 characters.
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit a size limit of ~4000 characters on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255 String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo) Additional information: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) String or binary data would be truncated. The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit some size limit on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255 String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo) Additional information: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) String or binary data would be truncated. The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
I'm putting a process together to run a DBCC CHECKCONSTRAINTS process against copies of client databases.The author application doesn't set the constraints as trusted, and therefore we need to check the integrity of the data.
The problem is that some of the Check constraints have a definition that is longer than 4,000 characters.When this is the case, DBCC CHECK CONSTRAINTS fails.One option is that I write a cursor to select the constraints that have a definition less than 4,000 characters and then call the DBCC command for those particular constraints. However, I'd prefer a more elegant approach - ideally a way to run DBCC CHECKCONSTRAINTS against all constraints regardless of the length of the definition
I want the reason for the above statement where I user nvarchar(4000) to insert the japanese text it give the same error , why we cannot have maximum size ? if we can have maximum size than 8060 what is the setting
When using SQL CE 3.5 and I try to insert data to DataSet with NTEXT type column that has more than 4000 characters I get this error
"InvalidOperationException was unhandled @p4 : String truncation: max=4000, len=8414
I had not problem with SQL Compact 3.1 I didn't even changed DataSet I just upgraded my database 3.1 -> 3.5 and I get this error. This is serious bug....
There are other people who have the same problem http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812683&SiteID=17
I'm using sys.dm_fts_parser dynamic management function to tokenize a string of characters >4000. The function doesn't accept a query_string parameter >4000 characters. Is there a way around this? I've tried to execute the SELECT defined in the function but that doesn't work.
cmd = New SqlCeCommand(strSQL, cn) Try cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message & vbNewLine & strSQL, "Table Error 7", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 8", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try If cn.State = ConnectionState.Closed Then cn.Open() End If
Try cmd = New SqlCeCommand(strSQL, cn) cmd.Parameters.AddWithValue("@docType", "a type") cmd.Parameters.AddWithValue("@docFlag", "a flag") cmd.Parameters.AddWithValue("@docData", info) cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try End If
Changing the cmd.Parameters as follows works:
Dim paramdocData As SqlCeParameter Try cmd = New SqlCeCommand(strSQL, cn) cmd.Parameters.AddWithValue("@docType", "a type") cmd.Parameters.AddWithValue("@docFlag", "a flag") paramdocData = cmd.Parameters.Add("docData", SqlDbType.NText) paramdocData.Value = info cmd.ExecuteNonQuery() Catch sqlexception As SqlCeException MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Catch ex As Exception MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1) Finally cn.Close() End Try
Thanks to the following Microsoft ReadMe for the above suggestion. See: http://download.microsoft.com/download/f/7/2/f72ebbf8-4df1-4800-b4db-c2405c10d937/ReadmeSSC35.htm
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
Hello everyone, trying to use stored procedure for my datagrid. in there i have the parameter that i would like to combine with wild character to retrieve some data..Does not work. Please help to come up with right syntax... here is the code: SELECT First_Name, Last_Name, Address, City, Customer_ID, Company_Name, State, ZIP, Phone_Number_1, Phone_Number_2, Email FROM v2_Customers WHERE (City = @search_text) OR (First_Name = @search_text) OR (Last_Name LIKE '%'+@search_text) OR (Address = @search_text)
Hi all,I have a internet page written in asp to submit into authorscurriculum vitae publications (title, author, year, etc.).If the author submit less than 8000 characters it functions OK, but Ifthe author try's to submit more than 8000 characters the asp page doesnot return an error but the text is not saved in the database or,sometimes, it returned a "Typ mismatch" error.Here is the sp:---------------------------------------------------------------------CREATE PROCEDURE sp_CV_publications(@formCommandnvarchar(255)='process',@id numeric=null,@id_personint=null,@typPubID tinyint= NULL,@publicationstext=null)ASif @formCommand='process'beginINSERT INTO CV_publications(idperson,typPubID,publications)VALUES (@id_person@typPubID,@publications);select 1 as status, @@IDENTITY AS insertedID, * FROMCV_publications WHERE id=@@IDENTITYend----------------------------------------------------------------------------The server is running IIS5 and SqlServer 2000Any ideas ???
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
Hi guys I know this is a really common question, and I have read loads of replies on it but everything I try does not work. I have written a small stored procedure in SQL server to upload images to a table and return the new ID using scope_identity. I have tested it and it works fine. here it is:******* @siteID numeric(18,0), @imgNum numeric(18,0), @title NVarchar(50), @MIMEtype nchar(10), @imageData varbinary(max)ASBEGINSET NOCOUNT ONdeclare @imageID intINSERT INTO [site_images] ([img_siteID], [img_num], [img_title], [img_MIME], [Img_Data]) VALUES (@siteID, @imgNum, @Title, @MIMEType, @ImageData) SET @imageID = SCOPE_IDENTITY()RETURN @imageIDSET NOCOUNT OFF************If I run this in management studio express it runs fine and returns the ID under 'return value'. The problem I have is trying to actually call that return value in VB. If I try using these lines:Dim returnParam As SqlParameterreturnParam = New SqlParameter("@imageID", SqlDbType.UniqueIdentifier)returnParam.Direction = ParameterDirection.OutputcmdTest.Parameters.Add(returnParam)withcnBKTest.Open()cmdTest.ExecuteNonQuery() imageIDparam = returnParam.value.toStringcnBKTest.Close() I get the error "procedure has too many arguments specified"And if I try to access the return value like this: imageIDparam = cmdTest.Parameters("@return_value").ValueI get the error "@return_value is not contained by this sqlparametercollection" What am I doing wrong? Any help would be greatly appreciated. Robsa
I have written this stored procedure but I get no return value (neither 0 nor 1). What I hope is when the transaction successful, return value 1. If fails, return value 0.1 set @TransactionOk = 0 2 3 BEGIN TRAN 4 5 UPDATE WhiteList_IMEI SET WhiteList_IMEI_Used = 1, Whitelist_IMEI_UsedDate = getdate() 6 WHERE WhiteList_IMEI_Code = @IMEICode_New 7 8 IF @@ERROR <> 0 9 BEGIN 10 ROLLBACK TRAN 11 12 PRINT ('Error. Contact Software Engineer.') 13 RETURN 14 END 15 16 COMMIT TRAN 17 set @TransactionOk = 1
Hi, I have been trying to this this for quite a while with no joy can someone please tell me the error of my ways. I am trying to add a new record by stored procedure, this I can do, but my problem lies with the returnvalue part of the procedure. I cannot get it to work. When I debug it tells me that the "Specified cast is not valid" see C# code as i comment the line where it errors. I enclose a sample stored procedure and its c# code. Please can someone tell me where I am going wrong? as this is annoying me alot SQL:create procedure SPUAddVehicleInsert @VehicleDetailsRegistrationNumber varchar(50), @VehicleDetailsMake varchar(50), @VehicleDetailsModel varchar(50), @NID bigint =null as insert into tblvehicledetails ( VehicleDetailsRegistrationNumber, VehicleDetailsMake, VehicleDetailsModel,
I have a stored procedure that returns an integer value, declared as:Public Function MyProc(..) As Int32 . . Return <integer>End FunctionI return an integer value, and can do this and get the value returned from the method, as such:declare @rc intexec @rc = dbo.MyProc <params>select @rcThis returns the value; how do I return the value to code and get the value; I've been debugging and that is my problem, I can't get the value to return.Thanks a lot.
Hello all of members, I have written a Stored Procedure.that creates a new account and then returns a value witch displays a result to me.if result is 1 "Username already exists" or 2 "E-Mail already exists".I did it with "Return" instruction.But, I don't know how can I get the returned value in ASP.NET(VB.NET)? Please help me. Thanks in advance
Hi all,Is there anyway to get a returned value from a called Stored Procedure from within a piece of SQL? For example, I have the following code...DECLARE @testval AS INTSET @testval = EXEC u_checknew_dwi_limits '163'IF (@testval = 0)BEGIN PRINT '0 Returned'ENDELSEBEGIN PRINT '1 Returned'END...which as you can see calls a SP called 'u_checknew_dwi_limits'. This SP (u_checknew_dwi_limits) actually returns a value (1 or 0), so I want to assign that value to the '@testval' variable (as you can see in my code) - but Query Analyser is throwing an error at me. Is this the correct way to do this?ThanksTryst
I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end: Return Scope_Identity() I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string. I have intReturn defined as an integer I've tried :Dim retValParam As OleDbParameter = cmd.Parameters.Add("@RETURN_VALUE", OleDbType.Integer)retValParam.Direction = ParameterDirection.ReturnValueintReturn=cmd.Parameters("@RETURN_VALUE").Value whenever I add this section - I get an error that there are too many arguments for the sProc. I've tried:intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors. What is the best way to do this in the code part (VB.Net)? Thanks ahead of time
I don't know whey this code does not return the values when I run it in sql server 2005 manager.set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[usp_usr_Add]
@FName nvarchar(30), @LName nvarchar(30), @UserName nvarchar(20), @Password nvarchar(20), @Email nvarchar(50), @Country nvarchar(2), @AIM nvarchar(10) AS SET NOCOUNT ON BEGIN
DECLARE @usrID int DECLARE @usrEmail int
SELECT @usrID = NULL SELECT @usrEmail = NULL
SELECT @usrID = usrID FROM usr WHERE usrName = @UserName
IF (@usrID IS NOT NULL ) BEGIN RETURN 1 END
SELECT @usrEmail = usrID FROM usr WHERE usrEmail = @Email
When i run this code i executes fine except when the two conditions become true they do not return thier values, nor does it return 0 when it inserts a row.
I have the following stored procedure which when exectuted within the Query Analyzer adds a record and returns the @SeqRunNo value. However when I call it in the from ado the record is added but the value is empty.
SELECT @Seq_No = QTR_SEQ_RUN_NO FROM Transmission_Quarter WHERE IID = @IID AND Tracking_ID = @TrackingID AND ADP_Code = @ADP_Code
IF ISNULL(@Seq_No,0) = 0 BEGIN
SET @LastUpdate = GETDATE()
SELECT @Max_Seq_No = CAST(MAX(QTR_SEQ_RUN_NO) AS int) FROM Transmission_Quarter WHERE IID = @IID AND ADP_Code = @ADP_Code
IF ISNULL(@Max_Seq_No,0) = 0 BEGIN SET @SeqRunNo = '0001' END ELSE BEGIN IF @Max_Seq_No = 9999 BEGIN SELECT @SeqRunNo = '0001' END ELSE BEGIN SELECT @SeqRunNo = REPLICATE('0', 4 - DATALENGTH(CAST(@Max_Seq_No AS VARCHAR))) + CAST((@Max_Seq_No + 1) AS VARCHAR) END END
Hello Newsgroup !My Tools are:Windows 2000, VBA(Access 2000) and MS SQL Server 7.0I wrote in an *.adp project (Access 2000) a Stored Procedure "xyz"with parameters a,bIn my VBA Code i wrote:Dim par As New ADODB.ParameterCmd.CommandType = adCmdStoredProcCmd.CommandText = "[prcSucheUNRWIAEStichtag]"Set par = Cmd.CreateParameter("@a", adInteger)Cmd.Parameters.Append parSet par = Cmd.CreateParameter("@b", adVarChar, adParamInput, 5)Cmd.Parameters.Append par........Cmd.Parameters(0) = aCmd.Parameters(1) = bSet rsTemp = Cmd.ExecuteNow my Problem is the following:there is an error in the stored procedure and i want to use something likethis:if @idontknow = '000000000'Beginreturn(1) -- Something <>0endHow can i use this return value in my VBA code ? Maybe i should ask thisquestion in an other Newsgroup. Pleaselet me know in which oneGreetingsFrank
In one of my SSIS jobs I have an 'Execute SQL Task' which is calling a stored procedure. This SP returns 0 or 1, if the SP returns 1 the package needs to stop executing. Is there a way to get the value that is returned from the SP and either continue or stop the job from continuing to run?
so if value is 0 it goes to the next step, if its 1 it stops running
