Can I Return Output From Stored Procedure Into An HTA To Display In Browser?
Jul 20, 2005
Hi all. I have a stored procedure on my sql server that returns a
simple informtation about that tim a database was backed up. I owuld
like to create an HTA that operator types can look at to make sure
that the backups finished, can someone help me do this?
The stored procedure in called sp_lastback and the output looks like
this:
database days since backup timestamp of backup
dbase1 0 2004-10-14 00:41:21.000
dbase2 0 2004-10-14 00:08:36.000
dbase3 1 2004-10-13 22:46:57.000
Can this be done? If someone could help me with this simple problem, I
could probably reuse the same method a 100 useful ways.
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable? Below is the source code that I have:1 ' Insert question_text into Question table 2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 Dim QuestionID As Integer 5 6 ' Set connection to DB 7 cmd.Connection = conn 8 9 cmd.CommandText = "usp_QuestionResponse_ins" 10 cmd.CommandType = Data.CommandType.StoredProcedure 11 12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output 13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID)) 14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID)) 15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn)) 16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp)) 17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn)) 18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder)) 19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3)) 20 QuestionID = sp1.Value 21 cmd.Connection.Open() 22 cmd.ExecuteNonQuery() 23 24 conn.Close() 25 cmd.Dispose() 26
Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins 2 ( 3 @question_id int = null output, 4 @assessment_id int = null, 5 @domain_id int = null, 6 @question_text_en nvarchar(1000) = null, 7 @question_text_sp nvarchar(1000) = null, 8 @category_en nvarchar(500) = null, 9 @display_order smallint = null, 10 @question_template_id int = null 11 ) 12 As 13 BEGIN 14 DECLARE @Err Int 15 16 DECLARE @QuestionID INT 17 DECLARE @ReturnValue INT 18 SET @ReturnValue = 0 19 20 DECLARE @CategoryId int 21 SELECT @CategoryId = NULL 22 23 -- Check to see if an answer has already been inserted (i.e. revisiting) 24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0) 25 IF ( @CategoryId IS NOT NULL ) 26 BEGIN 27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0 28 SET @QuestionID = @ReturnValue 29 END 30 31 IF ( @QuestionID IS NOT NULL ) 32 BEGIN 33 DECLARE @question_locale_id int 34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0 35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0 36 37 END 38 39 RETURN @QuestionID 40 End
What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine. Thanks
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
Apologies if this post doesn't belong here. We are in the process of moving steps from a large SQL job to a web portal. The intent here is to allow the user to manually trigger the jobs to run. The problem I am having is in determining a way to run these job steps in the background, thus allowing the browser to take control while the step runs. These jobs can take enough time that forcing the browser to wait is not an option.The steps have been consolidated into stored procedures so in order to run a step, one would call the sproc. What I would like to do is run this in a separate thread and let that thread end on its own while the server transfers back to the client. Is something like this possible? My understanding is that the thread would be orphaned once the browser takes control again. Thanks in advance for any advice.
ex: myprocedure(@Cusname varchar(50), @Cusid int output)as Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter { get {if (_CuslistAdapter == null) _CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter; } } Now how i write function in BLL to receive output paramter from creatcustomer function?
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
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.
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
hye everyone, i have Problem for the prompts will display at the top of the report in the browser :
1)go to the report manager on computer and set the set the Prompt User check boxes to checked. --> auto check
2)select the report viewer control, if you are using it, and set the Parameters property to false. --> the eror mesage will be display " The ' field " parameter is missing value. "
what should i do...any idea thanks in advance thank you very much
I have designed plenty of reports in VS 2005 and have been deployed on the reporting server. I have been designed one web page which shows UI through which user can access which ever reports he/she wants. I have kept some buttons on UI. When button is clicked appropriate report accessed and displayed on the browser.
Problem is that, report width is about 40 - 50 % of the screen and report viewer shows the report on the left align of the screen. I just want to show the report at the center of the screen. Also, any property I can use to set the report size in the proportion of screen in Percentage(%).
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?
Why does the Stored Procedure display error? I define a Stored Procedure Named Wish_DisplayTitleOfThing7, why does the Stored Procedure display error? Thanks! Msg 156, Level 15, State 1, Procedure Wish_DisplayTitleOfThing7, Line 9Incorrect syntax near the keyword 'case'. create procedure Wish_DisplayTitleOfThing7 @UserName varchar(80), @Status int, @Tag varchar(80)=null, @CreateDate datetime=null AS select * from Th_TopicTable where (UserName=@UserName) case @Status when 0 then '' --All when 1 then ' And (CompleteDate is not null )' --Completed when 2 then ' And (CompleteDate is null )' --Uncompleted end case @CreateDate when null then '' else ' And (DatePart(year,@CreateDate)=DatePart(year,CreateDate))' end
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 want to display the data in datagrid using the stored procedure,
Can you please tell me, how i can create the stored procedure for the following: using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)
I want to display the stored procedure data in my Datagrid.
Thank you very much for the help. the following is complete inline code on my webform.
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection")) Dim objCmd As New SqlCommand Dim dataAdapter As SqlDataAdapter
objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
objConn.Open()
dataAdapter = New SqlDataAdapter dataAdapter.TableMappings.Add("Table", "Orders") dataAdapter.SelectCommand = objCmd
dataSet = New DataSet("Orders") dataAdapter.Fill(dataSet) dtgOrders.DataSource = dataSet dtgOrders.DataBind()
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
Hi Guys, I have a sql procedure that returns the following result when I execute it in query builder: CountE ProjStatus 6 In Progress 3 Complete 4 On Hold The stored procedure is as follow: SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus This is the result I want but when I try to output the result on my asp.net page I get the following error: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn> Line 272: <asp:TemplateColumn> Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate> Line 274: </asp:TemplateColumn> Line 275: </columns> My asp.net page is as follows: <script runat="server"> Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC myCommandPS.CommandType = CommandType.StoredProcedure Dim num as integer num = CInt(myCommand.ExecuteScalar) 'Set the datagrid's datasource to the DataSet and databind Dim myAdapterPS As New SqlDataAdapter(myCommandPS) Dim dsPS As New DataSet() myAdapter.Fill(dsPS)
I have a variable @NetPay as type money, and a stored proc spGetNetPay. The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.
Now I need assgin output from spGetNetPay to user variable @NetPay. How can I do That?
Set @NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?
I have little knowledge about User defided function. Is is the way I should go?
Hi I am running a stored procedure which first puts the data in a temp table and then gives the output... the output is supposed to generate a report based on data from temp table
However when i run it, the first 2 statements are
(15345 row(s) affected)
(407 row(s) affected)
abd then the select statement runs...due to this, the report in ASP returns an error...does anyone know how i can suppress the first 2 lines and get only the actual data as output
When I run a script in query analyzer using a script (A "GO" statement exists after each SQL) I get the results on screen as soon as each query completes. When I run thru stored proc, I can get the result only after the whole procedure completes execution. Is there any way to get the outpout immediately as soon as each query completes? This will be useful in tracking thre progress of a stored proc. Thanks Satish
The procedure will write about 30,000 records or so and then quit writing to the file. Is there a setting I have to modify to write more records or is there a better way to do this?
Dear All,How can I show the resultrecords of a SP.I can be done by doubleclick the SPname?But how to do it by code.I want the following interfaceIn my form the user1 selects a SP (combobox showing a userfrinly name)2 adds the related parameters3 and then click the show result-buttonbut the .execute command doen't show teh records.I want the same output as you have doublclicking the SPname in theobjectwindow.Thanks,Filip
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.
