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).
Thanks for your help
N
View 7 Replies
ADVERTISEMENT
Apr 7, 2008
Dear All
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.
Here is my query sample for yours to understand.
SET NOCOUNT ON
DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)
Set @qstring = 'select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm'
PRINT '-------- ITEM Products Report --------'
DECLARE ITEM_cursor CURSOR FOR
execute (@qstring)
OPEN ITEM_cursor
FETCH NEXT FROM ITEM_cursor
INTO @ITEMCODE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From ITEM: ' +
@ITEMNAME
PRINT @message
-- Get the next ITEM.
FETCH NEXT FROM ITEM_cursor
INTO @ITEMcode
END
CLOSE ITEM_cursor
DEALLOCATE ITEM_cursor
Why i use @qstring? It is because the query will be changed by different critiera.
Regards
Edmund
View 6 Replies
View Related
Sep 12, 2007
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)
EXEC(@sql)
View 5 Replies
View Related
Aug 3, 2007
Hi all,I'm using MSSQL 2005, using the nvarchar(MAX) but it doesn't seems to take more than 4000 characters... Any idea why?Thank you,Kenny.
View 6 Replies
View Related
Mar 31, 2008
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]
Test2 (type = String, EvaluateAsExpression = True):
Expression = "-" + @[User::Test1] + "-"
Test2 throws the following error:
The variable User::Test1 contains a string that exceeds the maximum allowed length of 4000 characters.
Reading the variable "User::Test1" failed with error code 0xC0047100.
Why is there such a limitation when working with string values? This seems really restricting. Are there any possible workarounds?
Thanks in advance.
Jerad
View 5 Replies
View Related
Apr 29, 2015
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.
View 4 Replies
View Related
Oct 26, 2006
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve
the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?
Any workaround?
Here is the pseudo code:
create Procedure foo(@output varchar(max))
{
set @foo = 'string that has more than 4000 characters...';
return;
}
Java code:
CallableStatement cs = connection.prepareCall("{call foo ?}");
cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.
cs.execute();
String result = cs.getString(1); // The result is truncated to 4000 char.
-- Also tried
CLOB clob = cs.getClob(1);
long len = clob.length(); // The result is 4000.
Thanks,
Eric Wang
View 3 Replies
View Related
Apr 29, 2015
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.
View 9 Replies
View Related
Oct 14, 2015
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
View 0 Replies
View Related
Feb 7, 2007
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
Please help me ..
Thanks in anticipation
Ashish Tahasildar
View 4 Replies
View Related
Nov 7, 2007
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
View 4 Replies
View Related
Jul 21, 2015
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.
View 2 Replies
View Related
Jan 25, 2008
This is with SQLCe NET 3.5.0.0 running on Windows Server 2003 or Server 2008, not on a Windows mobile operating system.
The following code fails with ntext entries above 4000 characters:
Dim cn As New SqlCeConnection(ConnectString())
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
Dim info as string
info = "This is lengthy text".PadLeft(4200)
Dim cmd As SqlCeCommand
strSQL = "create table testTable ("
strSQL &= "docType nvarchar (50) NULL, "
strSQL &= "docFlag nvarchar(10) NULL, "
strSQL &= "docData ntext NULL, "
strSQL &= " )"
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
'---- insert a row into the testTable
strSQL = "INSERT INTO testTable ("
strSQL &= "docType, "
strSQL &= "docFlag, "
strSQL &= "docData, "
strSQL &= ") "
strSQL &= "VALUES ("
strSQL &= "@docType, "
strSQL &= "@docFlag, "
strSQL &= "@docData, "
strSQL &= ")"
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
View 3 Replies
View Related
Feb 12, 2008
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.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
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.
View 2 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Nov 28, 2006
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)
where @search_text is the parameter,
thank you!
View 5 Replies
View Related
Jul 20, 2005
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 ???
View 1 Replies
View Related
Jun 13, 2007
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?
View 3 Replies
View Related
Aug 19, 2015
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)
How can I code a stored procedure to do this?
View 17 Replies
View Related
May 10, 2007
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?
View 3 Replies
View Related
Nov 20, 2007
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
View 3 Replies
View Related
Nov 28, 2007
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
View 6 Replies
View Related
May 16, 2008
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,
)
values
(
@VehicleDetailsRegistrationNumber,
@VehicleDetailsMake,
@VehicleDetailsModel,
);
select @NID = scope_identity();c# code on sqldatasource: protected void dsAddVehicle_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
Int64 VID = (Int64)e.Command.Parameters["NID"].Value; //errors with specified cast is invalid
Response.Redirect("details.aspx?VID=" + VID.ToString());
}
protected void dsAddVehicle_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
SqlParameter p = new SqlParameter("NID", SqlDbType.BigInt);
p.Direction= ParameterDirection.ReturnValue;
e.Command.Parameters.Add(p);
}
sql datasource:<asp:SqlDataSource ID="dsAddVehicle" runat="server" ConnectionString="<%$ ConnectionStrings:National %>"
InsertCommand="SPUAddVehicleInsert" InsertCommandType="StoredProcedure" SelectCommand="SPUAddVehicleSelect"
SelectCommandType="StoredProcedure" OnInserted="dsAddVehicle_Inserted" OnInserting="dsAddVehicle_Inserting">
<InsertParameters>
<asp:Parameter Name="VehicleDetailsRegistrationNumber" Type="String" />
<asp:Parameter Name="VehicleDetailsMake" Type="String" />
<asp:Parameter Name="VehicleDetailsModel" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
View 4 Replies
View Related
Jun 20, 2005
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.
View 2 Replies
View Related
Nov 20, 2005
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
View 3 Replies
View Related
Nov 25, 2005
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
View 2 Replies
View Related
Apr 28, 2006
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
View 2 Replies
View Related
May 16, 2006
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
IF (@usrEmail IS NOT NULL)
BEGIN
RETURN 2
END
INSERT INTO usr (usrFName, usrLName, usrName, usrPassword, usrEmail, usrCountry, usrAIM, usrJoinDt)
VALUES (@FName, @LName, @UserName, @Password, @Email, @Country, @AIM, GetDate())
RETURN 0
END
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.
View 5 Replies
View Related
Oct 17, 2000
Hi
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.
I enter the parameter as:
.Parameters.Append .CreateParameter("@SeqRunNo", adVarChar, adParamOutput, 4)
and try and access it as:
SeqRunNo = oCommand.Parameters("@SeqRunNo").Value
Any ideas?
Thanks.
john
CREATE PROCEDURE up_mix_Set_Seq_Run_No
@IID int,
@TrackingID int,
@ADP_Code varchar(6),
@ClientID varchar(20),
@SeqRunNo varchar(4) OUTPUT
AS
DECLARE @Max_Seq_No int,
@Seq_No varchar(4),
@LastUpdate datetime
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
INSERT INTO Transmission_Quarter
(IID, Tracking_ID, QTR_SEQ_RUN_NO, ADP_Code, Last_Updated_By, Last_Updated_Date)
VALUES
(@IID, @TrackingID, @SeqRunNo, @ADP_Code, @ClientID, @LastUpdate)
END
ELSE
BEGIN
SELECT @SeqRunNo = @Seq_No
END
GO
View 2 Replies
View Related
Mar 27, 2002
How do I get then return value from an insert stored procedure?
for example:
CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS
INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)
SELECT @Iden=@@Identity
GO
Thanks
View 1 Replies
View Related
Mar 27, 2002
How do I get then return value from an insert stored procedure?
for example:
CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS
INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)
SELECT @Iden=@@Identity
GO
Thanks
View 2 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Apr 22, 2008
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
View 8 Replies
View Related