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.
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
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 the following SP SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE spGetResettedPassword@Email nvarchar(50)ASBEGIN SET NOCOUNT ON; declare @newid uniqueidentifier set @newid=newid() declare @newPass nvarchar(10) set @newPass=convert(nvarchar(100),@newid) set @newPass=substring(@newPass,1,7) UPDATE aspnet_Membership SET Password=@newPass WHERE Email=@Email return @newPassENDGO When calling it from code like this: MyConnection.Open() cmd = New SqlCommand("spGetResettedPassword", MyConnection) cmd.CommandType = Data.CommandType.StoredProcedure myTrans = MyConnection.BeginTransaction() cmd.Transaction = myTrans fBeginTransCalled = True bSuccess = False cmd.Parameters.Add(New SqlParameter("@Email", Email)) Dim NewPassword As Object = cmd.ExecuteScalar If NewPassword <> Nothing And CType(NewPassword, String) <> "" _ And TypeOf (NewPassword) Is String Then Return CStr(NewPassword) Else Return "ERROR, CONTACT US ON THE SITE" End If bSuccess = True fBeginTransCalled = False
I get the following error: Conversion failed when converting the nvarchar value '40DC5F3' to data type int. What is happening here?!?!
I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE BEGIN INSERT INTO table1 (nameA, NameB) VALUES (@nameA, @nameB) RETURN 03 END **************************************************************************** dim cn as New sqlConnection(connectionstring) dim cm as sqlcommand cn.Open() With Cm .CommandType = CommandType.StoredProcedure .CommandText = "STOREDPROCEDURE" .Connection = cn End With 'INSERT Names Cm.Parameters.Add("@nameA", SqlDbType.VarChar, 25).Value = nameA.text Cm.Parameters.Add("@nameB", SqlDbType.VarChar, 25).Value = nameB.text param_returnValue = Cm.Parameters.Add("RETURN_VALUE", SqlDbType.Int) param_returnValue.Direction = ParameterDirection.ReturnValue*******************************************************************************************This is where I get stuck. I need to display certain error messages depending on the return value.******************************************************************************************* dim insertStatus as integer insertStatus = Cm.ExecuteScalar() If insertStatus = 01 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 02 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 9999 Then Response.Redirect("success.aspx") End If End If End If I am pretty sure the IF statements arent the way to go especially because it is not working but i am curious to how I am to make this work.
whats wrong with this SP? I want @id to contain the row identity of the newly created row as a return value.ALTER PROCEDURE setCountry ( @name varchar( 50 ) = NULL, @alt varchar( 24 ) = NULL, @code varchar( 3 ) = NULL, @id int = null OUT ) AS SET NOCOUNT ON INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @name, @alt, @code ) @id = @@identity RETURN
Quote: CREATE PROCEDURE dbo.usp_ProfQuals_Add_AwardingBody_nr ( @awardbody varchar(100) ) AS BEGIN declare @message int
if EXISTS(SELECT vchr_awardingbody from tbl_ProfQuals_AwardingBody where vchr_awardingbody = @awardbody) set @message = 1 else set @message = 2 insert into tbl_ProfQuals_AwardingBody (vchr_AwardingBody) values (@awardbody)
END return @message
The problem I am having is that it returns the correct value if there is a record that exists or indeed if it doesn't BUT ... even when a record exists it still carries out the insert!
Hi, I want to return the next row in a select ... order by ... cursor. I don't want to loop,just SQL,Do you know any solution? For example: in Oracle we use rownum,is there any equivalent in SQL Server? -Thanks
If you look at the table, client ABC has a lot of transactions but if I SUM them the balance is 0, same with client GHI. The only row that show show on my query is client DEF.
I am trying to do something like this:
select * from @T1 having Code in ( SELECT code from @T1 group by code having SUM(amount) <> 0)
I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is.....
If there are multiple rows for a specific field, is there a way in SQL to only return the last row based on that specific field.
For example, I wrote a query that returns a list of patients who have a specific lab type. If a patient has had several labs taken, they show up in the result set several times (for each lab date). I only want to return the most recent lab date.
FROM dbo.lab_result LEFT OUTER JOIN dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
Dear All,I try this but it keeps returning zero !Dim newid As LongCurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as'newID'"Me.RequeryMsgBox newid >> returns 0Msgbox DMax("ORD_ID","[ORDER]") >> returns the new idMy question stays: is it possible to return the newid into a VB-variabledirectly?Filip
HelloI was wondering if someone could help me out with something.With the following rows:agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl1 1 1 10-10-2000 01 10-15-2000 021 1 2 10-09-2000 06 051 2 1 10-08-2000 05 10-20-2000 011 2 1 10-05-2000 01 10-15-2000 03What I need is SQL to get one row per agnt/supv with the following:strt_dt, strt_lvl of earliest strt_dttrm_dt, trm_lvl of the latest trm_dt but only if all trm_dts forthe supv arefilled in, otherwise null for bothagnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl1 1 10-09-2000 06 null null1 2 10-05-2000 01 10-20-2000 01If anyone has any ideas on the most efficient way to accomplish this,I'd appreciate a reply.Thanks!
I have set of procedures that implement business logic in such a way that I am just interested to return only one row when there could possibly be millions of them. Those procedures are in this form
SELECT Top 1 SGP.GroupID
FROM dbo.SubGroupPerm SGP
INNER JOIN dbo.GroupPermStats GPS on SGP.GroupID = GPS.GroupID
INNER JOIN dbo.IMISProduct IP on GPS.ProductID = IP.ProductID
INNER JOIN dbo.HasMeasureGroup HMG on SGP.GroupID = HMG.GroupID
WHERE SGP.SubscriberID = 40335
AND HMG.MeasureTypeID = 119
AND GPS.IndustryCode = 'CT'
Such tables are properly indexed and most queries return results using clustered index seek/index seek operations. Unfortunately, in presence of large tables I don't get the desired response time because query generates massive IOs. I was wondering if someone could possibly have another clever way to re-write above query where we just have to return one row?
in my package i am executing a stored procedure from execute sql task which is something like DECLARE @return_value int EXEC @return_value = [dbo].[NextDigitalDataLoad] SELECT 'ReturnValue' = @return_value GO
Now based on the value returned by this i have to check in a dataflow task whether the file content is correct or not. please tell me how to do
I have a table that contains multiple entries per a specific Id similar to the following: 1 09-01-2007 1 09-02-2007 1 09-15-2007 1 09-06-2007 2 09-03-2007 2 06-09-2007 2 07-09-2007 3 01-01-2007 3 02-02-2007 I need to get a single row back for each id which corresponds to the row with the minimum datetime. Using the example I would need to return: 1 09-01-2007 2 06-09-2007 3 01-01-2007 Anyone have any ideas on how to accomplish this with a TSQL query? Thanks.
Select Model, Kota as [Çıkan Anket] , (Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model) as [Verilen Adres] , (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model) as [Donen Adres] , (Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model) as [Donen Anket] ,((Select cast(Count(*) as decimal(2,0)) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model ) / (Select top 1 (cast(Kota as decimal(2,0))) From dbo.Model Where Model = tt.Model))*100 as [Oran] , (Kota - (Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model)) as [Kalan Anket] , ((Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model)- (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model)) as [Kalan Adres] From dbo.Model as tt where tt.Model in('A3')
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
I have the following code in my page: <asp:SqlDataSource ID="SqlHolidayDateRange" runat="server" ConnectionString="(OurConnectionString)" SelectCommand="CountHolidays" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="WeekEndingDatePicker" Name="EndDate" PropertyName="SelectedDate" /> <asp:Parameter Direction="ReturnValue" Name="RowCount" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data? Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you - have to work with is what's above.
Hello, I created a stored procedure which selects a value according to ContentId. I know that will be only one value returned or none. So if a record is found I want to return the string contained in ContentHtml. Else I want to return the string "NotFound" Could somebody help me out with this? Here is my present stored procedure: -- Specifies the SQL-92 equals compliant behavior SET ANSI_NULLS ON GO -- Specifies the SQL-92 quotation mark rules SET QUOTED_IDENTIFIER ON GO -- Alter procedure ALTER PROCEDURE [dbo].[by27_Content_GetContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Select localized content from by27_ContentLocalized SELECT dbo.by27_ContentLocalized.ContentHtml FROM dbo.by27_Content INNER JOIN dbo.by27_ContentLocalized ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName); END -- Create procedure GO Thanks, Miguel
i have query which does the following select x from y where t = "House"x is an integer. If no record is found, how do i get it to return 0 rather than null?
I have the following stored procedure... CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet. Obviously, I need to wrap this in an IF...EXISTS IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID)) (the SELECT above, since that's what needs to come back if the data exists) ELSE Do the same select but put in a static value for BudgetCode, like '0000' GO How could I do the part where the IF...EXISTS fails? I'm... not sure I can use RETURNS, since it feeds into this recordset: rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID") and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>
How can I tell if table "CompanyInfo" has any rows? Below is the partial code behind and the SP I am running to fill CompanyInfo; PROCEDURE newdawn.AcceptSubmission @CompanyID intASSELECT C_ID, CG_ID, LS_ID, L_Rank, L_Name, L_URL, FROM tblStoreSubmitWHERE C_ID = @CompanyIDRETURN SqlCommand cmd3 = new SqlCommand("AcceptSubmission", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd3; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo");
I guess a pretty basic sql question here. I am using tableadapters. And some of the queries have multiple parameters. But often a parameter is at a default or null - wherein I'd like to be able to pass it in and have it not filter at all - ie., return everything. Simple example: Select * from ReportsWhere ReportID = @ReportID If I have a dropdownlist of reports where the values are ReportIDs - but the topmost unselected value is say "", then the results will be generated by Select * from ReportsWhere ReportID = '' Whis not what I want. What I would want is simply Select * from Reports or Select * from ReportsWhere ReportID = ReportID So how can one setup say an ObjectDataSource or somehow handle this where I don't need to use dynamic sql to expand/contract my where clause? Really need hlpe on this and hope I've made my question reasonably clear. Thanks.
Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
Hi Guys, I can't for the life of me get the output from the stored procedure into my variable in VB.net. Can you guys help out, I'm on my third hour. Trying to learn this peice as I go (output parameters). I have truncated the code to look at the output parameter, everything works except the VB side of the output parameter, my messagebox keeps giving me a value of zero. I have even tried .SQLValue instead of .Value and it didn't work...thoughts????Thanks in advance!Tim SQL:ALTER PROCEDURE dbo.SDC_RisIncidentInsert @SeqInc int output ASSelect @SeqInc = Max(Sequence) + 1 From _Smdba_._Telmaste_Select @SeqInc--------------------------------------------------------------------------------------------- VB.NetPrivate Sub InsertIncident()Dim conn As SqlConnectionDim comm As SqlCommandDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("SDC_RisIncidentInsert", conn)comm.CommandType = System.Data.CommandType.StoredProcedurecomm.Parameters.Add("@SeqInc", SqlDbType.Int)comm.Parameters("@SeqInc").Direction = ParameterDirection.Output Dim SeqIncident As Integer = comm.Parameters.Item("@SeqInc").ValueMsgBox(SeqIncident) Try conn.Open() comm.ExecuteNonQuery()Catch ex As Exception dberrorlabel.Text = "Could not Insert Incident Record" Finally conn.Close() Response.Redirect("/RISDEV/SDC_Incident.aspx?Name=" & SeqIncident & "#Incident")End TryEnd Sub
Hi...I have table who contain words in Hebrew (right to left language), and when i try to select:WHERE (Topic LIKE '%Hebrew word%')I get no rows...Any idea? thanks...
