Assigning A Stored Procedure Return Value To A Control
Jan 11, 2008
Hi, I have an app where I am calling a stored procedure that runs and returns an integer. I would like to assign this integer to a control on the page. Here is a sample of code and below I will give the errors:
Protected Sub NewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewButton.Click
Dim tempconnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString)
Dim tempproc As New Data.SqlClient.SqlCommand("SP_FetchProviders", tempconnection)
Dim tempparam As New Data.SqlClient.SqlParameter
tempproc.Parameters.Add(New SqlParameter("@Agency_ID", Data.SqlDbType.Int))
tempproc.Parameters("@Agency_ID").Value = "8" 'Using a number just to test, will be populated by a session variable later
tempparam = tempproc.CreateParameter()
tempparam.ParameterName = "@OutValue"
tempparam.Direction = Data.ParameterDirection.ReturnValue
tempparam.SqlDbType = Data.SqlDbType.Int
tempconnection.Open()
tempproc.ExecuteNonQuery()
Dim labelfill As String
labelfill = tempproc.Parameters("@OutValue").Value
tempconnection.Close()
CType(Me.DetailsView1.FindControl("OmbudINSDDL"), DropDownList).Items.Add(labelfill) 'This is inside of a detailsview templatefield
End Sub
Here is the stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[SP_FetchProviders]
@Agency_ID int
as
Select Name, ProviderSubUnit_ID
From ProviderSubunit a join Ombudsman b on a.contractproviders_id=b.contractproviders_id
Where b.agency_id = @Agency_ID
Order by Name
This takes the @Agency_ID as input variable, and has returns an integer (according to SQL management studio). When I try to run my code I get the error that an sqlparameter with parametername @OutValue is not contained by this sqlparametercollection.
This may be a simple question, but does the outvalue have to be declared in the stored procedure? If so, can you provide the right syntax? If not, can you offer a suggestion to populate the dropdownlist with the returned value? Thanks in advance.
In the following stored procedure I would like to test whether @TopCategoryID is null. If so I would like to insert the @CategoryID value into the @TopCategoryID value. So, once @CategoryID recieves the @@IDENTITY value, how do I enter this same value into @TopCategoryID as well???
CREATE PROCEDURE sp_CT_InsertCategory
@Namevarchar(50), @Description varchar(250), @topCategory bit, @ParentCategoryID int, @TopCategoryID int, @CategoryID int OUTPUT AS DECLARE @CurrID int
Hi, I'm try to get the query second column value when it is assinged to a varchar variable. Ex:In SP below is statement I wrote SET @Values = (SELECT COL1,COL2 FROM TABLE) Question 1: How to access the COL2 value from @Value2? Question 2: How to access the other row values if above returns more than a row? Please send me the solution as soon as possible. Thanks-Vikash/Bala
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.
I'm trying to assign a datasource to a gmap control in 2.0 so that i can get pushpins in the map for all the locations in the sql db. I've enclosed my code below as well as a link to the site that provides the control and their sample. Can anyone tell me why it won't show the pins? What am I doing wrong? Thanks. My Code (sqllocations is a datasource): GMap1.DataSource = sqllocations; GMap1.DataBind(); Their site: http://en.googlemaps.subgurim.net/ejemplos/ejemplo_991000_DataSource.aspx Their example (part of their explanation is not english, sorry): List<DataSourceField> fields = new List<DataSourceField>(); fields.Add(new DataSourceField(45, 2, "hola"));fields.Add(new DataSourceField(46, 3));// Lo comentamos porque al utilizar la clase DataSourceField, // pero si fuera un dataset o cualquier otra cosa, // le deberíamos dar los nombres correspondientes// GMap1.DataLatField = "lat";// GMap1.DataLngField = "lng";// GMap1.DataGInfoWindowTextField = "gInfoWindowText";GMap1.DataSource = fields;GMap1.DataBind();
I will need some examples in assigning and getting values using SQLServer 2005. For eg. How can I store the value that I retrieved in a variable and return that value ? How can I use a function inside a stored procedure ? Do we have any examples or some simple sample code just to take a look ?
For eg I have written the following function which I called from a stored procedure. BEGIN --Declare the return variable here DECLARE @Rows NUMERIC(10) DECLARE @RETURN_ENABLED VARCHAR(1) -- Add the T-SQL statements to compute the return value here
SELECT @Rows = MAX(PROFILE_INDEX) FROM PROFILE_PERMISSION PP INNER JOIN sys_menu_item ON PP.MENU_ITEM=sys_menu_item.menu_item WHERE PP.PROFILE_INDEX in (select up.profile_index from user_profile up where up.user_id= @is_user) and not exists (select up.profile_index from user_profile up where up.user_id= @is_user and up.profile_index=1) and PP.APPLICATION_CODE = @is_appl AND PP.MENU_NAME=@menu_name Group By Profile_INdex
-- Return the result of the function RETURN @RETURN_ENABLED;
END
Is it correct ? The variable @ROWS will be assigned with the values that the sql statement will return ?
From the stored procedure I'm calling the function inside a CTE.
;WITH GetHierarchy (item_text ,orden , read_order, item_parent , menu_item , enabled) AS (--Anchor. select tb1.item_text, tb1.orden, tb1.read_order, tb1.item_parent , tb1.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled From sys_menu_item as tb1 where tb1.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb1.item_parent not in ('m_toolbar','m_window','m_help') And tb1.item_parent= @menu_name --Members UNION ALL select tb2.item_text, tb2.orden, tb2.read_order, tb2.item_parent , tb2.menu_item , dbo.f_sty_print_menu_per_role_per_app2(@menu_name , @is_user , @is_appl) as enabled from sys_menu_item as tb2 , GetHierarchy where tb2.MENU_ITEM not in ('m_window','m_help','m_toolbar') and tb2.item_parent not in ('m_toolbar','m_window','m_help') And tb2.item_parent = GetHierarchy.menu_item and tb2.menu_name = @menu_name ) select Space(5*(orden)) + item_text as menui, orden, read_order, item_parent , menu_item ,enabled From GetHierarchy
Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl
I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.
CREATE PROC proc_Emp
@Sel_Country AS BEGIN
SELECT DISTINCT employee, hrid FROM emp_table WHERE country = @Sel_Country
@SEND_RESULTS = CASE WHEN row_count = 0 THEN
SELECT 'No Country', 4444444444 ELSE
(send original results) END
END
I need to:
Query for the employee list
If the row count is zero/null then return fixed values
Else return the orginal data from Step 1. I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.
Hi all, I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row. The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.
Is there any way i could do this?
Exmaple: In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!
Procedure dbo.AddressInsert(....) as BEGIN
Insert INTO Adress ....
SELECT * FROM AdressView WHERE AddressID = @@identity END
Procedure dbo.CustomerInsert(...) as begin -- transcation begin...
EXEC dbo.AddressInsert
INSERT INTO Customer....
-- end transcation
SELECT * FROM Customer WHERE CustomerID = @@identity
I create StringBuilder type forconcating string to create a lot of stored procedure at once However When I use this command BEGIN TRANSACTIONBEGIN TRY--////////////////////// SQL COMMAND ///////////////////////// -------------------- This any command --/////////////////////////////////////////////////////////// --COMMIT TRANEND TRYBEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;END CATCHIF @@TRANCOUNT > 0 COMMIT TRANSACTION; on any command If I use Create a lot of Tablessuch as BEGIN TRANSACTION BEGIN TRY --////////////////////// SQL COMMAND /////////////////////////CREATE TABLE [dbo].[Table1]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY]CREATE TABLE [dbo].[Table2]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY] --/////////////////////////////////////////////////////////// --COMMIT TRAN END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION; It correctly works. But if I need create a lot of Stored procedure as the following code : BEGIN TRANSACTION BEGIN TRY --////////////////////// SQL COMMAND ///////////////////////// CREATE PROCEDURE [dbo].[DeleteItem1] @ProcId Int, @RowVersion Int AS BEGIN DELETE FROM [dbo].[ItemProcurement] WHERE [ProcId] = @ProcId AND [RowVersion] = @RowVersion END CREATE PROCEDURE [dbo].[DeleteItem2]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END CREATE PROCEDURE [dbo].[DeleteItem3]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END
--/////////////////////////////////////////////////////////// --COMMIT TRAN END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION; It occurs Error ????? Please help me How should I solve them ?
Hi, I've a ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine) { ConnectionState currState = connection.State; if (((connection.State & ConnectionState.Open) != ConnectionState.Open)) connection.Open(); try { SqlParameter[] parameters = new SqlParameter[3]; parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo); parameters[1] = new SqlParameter("@DataInizio", DataInizio); parameters[2] = new SqlParameter("@DataFine", DataFine); SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } finally { if ((currState == ConnectionState.Closed)) connection.Close(); } } How Can I manage a list of values ??Thanks for help me!!
Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure
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 defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.
Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):
OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.
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 have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount
User::giRowCount is populated by a Row Count Component previously in the Data Flow.
After reading http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97494&SiteID=1 it is very clear that you can actually only use variables in the PostExecute of a Data Flow Script Component or you will get an error "Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute."
What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as second line as part of the header, I also need to parse a read/write variable such as gsFilename to save me hardcoding the path
-they must go in the PreExecute sub --workarounds please-here is the complete script component that creates a file with header, data and trailer --Is there any workaround
Public Class ScriptMain Inherits UserComponent 'Dim fs As FileStream Dim fileName As String = "F:FilePickUpMyfilename.csv" 'Dim fileName = (Me.Variables.gsFilename.ToString)
Dim myFile As FileInfo = New FileInfo(fileName) Dim sw As StreamWriter = myFile.CreateText Dim sbRecord As StringBuilder = New StringBuilder
Public Overrides Sub PreExecute()
sbRecord.Append("RECORD_START").Append(vbNewLine)
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
Public Overrides Sub PostExecute() sbRecord.Append("RECORD_COUNT").Append((vbTab)) sbRecord.Append(Me.Variables.giRowCount.ToString).Append(vbNewLine) sbRecord.Append("RECORD_END").Append(vbNewLine) 'Now write to file before next record extract sw.Write(sbRecord.ToString) 'Clear contents of String Builder sbRecord.Remove(0, sbRecord.Length)
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
Greetings All, I am a newbie to SSIS and need some help. I need to get the return value from a stored procedure into a SSIS variable. I'm assuming I would use an OLE DB Command but I havn't a clue on how to capture the return value. Can someone get me started on how I can do this?
Note, the return value is actually an identity of the inserted value. I need this value in my data flow for further processing.
