How Do I Retrieve Results Of The Output Parameters??
Feb 17, 2008
I am able to perform my Stored Procedure, but I can't figure out how to get access to the OUTPUT parameters!
Here is my code:
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim mySqlDataSource As SqlDataSource = New SqlDataSource
mySqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
mySqlDataSource.ConnectionString = "my connection string"
mySqlDataSource.InsertCommand = "MyStoredProc"
mySqlDataSource.InsertParameters.Add("someId", TypeCode.Int16, 166)
mySqlDataSource.InsertParameters.Add("someNumber", TypeCode.Int16, 4)
' Add Some Parameter
Dim someParameter As Parameter = New Parameter("someParameterName", TypeCode.String)
someParameter.DefaultValue = New String("Some Value")
someParameter.Direction = Data.ParameterDirection.Output
mySqlDataSource.InsertParameters.Add(someParameter)
mySqlDataSource.Insert()
End Sub
Any ideas as to how I get the new value that was "hopefully" assigned to someParameter???
Thanks for any help
View 3 Replies
ADVERTISEMENT
May 28, 2015
I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 16 Replies
View Related
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Jan 24, 2006
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.
The item is added into the db but the output param is not modified.
Here is the code:
SP
CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc )
select @intItemID = SCOPE_IDENTITY()GO
I have tried in the last line of the SP
select @intItemID = SCOPE_IDENTITY()
select @intItemID = @@IDENTITY
select @intItemID = max(itemid) from RR_Item
but nothing seems to work.
I'm calling the store procedure as follows from asp.net:
Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int)
intItemID.Direction = ParameterDirection.Output
SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID)
MsgBox(intItemID.Value.ToString)
Any help would be appreciated.
Thanks
View 1 Replies
View Related
May 18, 2007
Hi,
I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error
An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows:
int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close();
my stored procedure:
CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS
INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted)
VALUES (@publicationid,@title,@categoryid,0)
SELECT @slideshowid = SCOPE_IDENTITY()GO
View 1 Replies
View Related
Aug 28, 2007
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
Feb 20, 2005
HI
I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance
VS
View 9 Replies
View Related
Jul 20, 2005
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies
View Related
Oct 5, 2006
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
View 2 Replies
View Related
May 11, 2015
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies
View Related
Jan 20, 2000
What would be the correct synatx if I wanted to output the results of a stored to a file
instead of printing?
View 1 Replies
View Related
Apr 14, 2008
Hi,
I was wondering is there was is a way in SQL 2005 to not only email job completion notifications but also include the output of a step (e.g. TSQL step) within the email. I was able to accomplish this in the past with SQL 2000 by writing output to a file and then adding an extra CMDEXEC step that exectuted BLAT (CLI SMTP engine) that sent the contents of the output file as email body out of band. Although this worked and I can do the same with 2005 it is not very elegant and I was wondering if 2005 version might have something built-in.
Any thoughts?
Thank you,
-Ed
View 1 Replies
View Related
Feb 26, 2008
Hi everyone, can anyone help me please
I have a stored procedure that works quite well and returns one of the following codes, 1 2 3 or 4 which is dependant on business criteria. This sp works well and satsisfies all the criteria etc.
I am calling this SP in asp.net (c#) like this
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["blah"].ConnectionString)) { int? intSQLCheck = null;
con.Open(); SqlCommand cmd = new SqlCommand("check_values", con); cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@param_clothing_id", this.DropDownList2.SelectedValue.ToString())); cmd.Parameters.Add(new SqlParameter("@empNo", this.DropDownList1.SelectedValue.ToString())); cmd.Parameters.Add(new SqlParameter("@createdBy", this.txtEmpNo.Text.ToString())); cmd.Parameters.Add(new SqlParameter("@price", this.txtPrice.Text)); cmd.Parameters.Add(new SqlParameter("@qty", this.txtQty.Text)); //cmd.Parameters.Add(new SqlParameter("@returnValue", 0)); cmd.Parameters.Add(new SqlParameter("@returnVal", 99)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
What I would like to do know is use a variable intSqlCheck to hold the return value from the SP (and then do some processing of business logic using the variable)
Any ideas anyone please
View 2 Replies
View Related
Mar 5, 2008
Hi, I have a form with a detailsview and a sqldatasource. The sqldatasource uses a stored procedure as the select command. This stored procedure requires 2 input parameters and returns 3 output parameters. A button on the form is enabled or disabled depending on the value of the output parameters. When the page loads, I get a "Object reference not set to an instance of an object" message, caused by the null value of an output parameter. SQL Profiler shows that the input parameters are correct, and if I run the stored procedure directly, it's okay. Can anybody tell me why I wouldn't get the output parameters? Thanks,Neil
View 1 Replies
View Related
Oct 16, 2007
I am new to SQL and trying to work out how to do this
i have a table called projectAllocationLog with 4 fields, there can be many users of any particular project, ie
ID,projectID, User, Date
4 5000, 6, 10/02/2007
3 5000, 12, 08/07/2005
2 5004, 13, 08/05/2003
1 5000, 14, 04/05/2001
I want to write a stored proceedure that I can pass in the projectID, and return the most current user, this will always be the user with the highest ID for the project
ie for project 5000 it will be user 6
I was thinking along the lines of for each project select the top 1 from the table. where I can pass in the projectID, and pass out the ID field of the table. i am just not sure how to start this.
Any help would be appreciated.
View 4 Replies
View Related
Jul 31, 2007
I am trying to select multiple values from the row with the max id,and output them within my stored procedure. How would I combine thefollowing two rows so that I only need to use "from mytable whereIDnum = (select (max(IDnum)) from mytable))" once. I have at least 8other outputs I will be setting and would like this to work moreefficiently than setting each individually... if possible.set @outmtd = (select outmtd from mytable where IDnum = (select(max(IDnum)) from mytable))set @outytd = (select outytd from mytable where IDnum = (select(max(IDnum)) from mytable))Thanks for any help you can giveMatt
View 3 Replies
View Related
Apr 3, 2007
how do i include output parameters from a stored procedure into a report?
View 2 Replies
View Related
Jun 2, 2005
Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
View 1 Replies
View Related
Apr 3, 2001
I have come across manual queries being run daily and the results saved to a txt file on the network.
I basically want to set run these up a stored procedure and set up as a scheduled task to run daily.
Does anyone know if you can automatically save the results of a query/stored procedure to a text file on a network??
Many Thanks..
View 1 Replies
View Related
Jul 20, 2005
Hi All !Is it possible to get rid of these dash symbols which are underliningthe column name when recordset is returned after query execution ?For example, using isql.exe:SELECT 'blah'goproduces the following results:----blahWhat I want to achieve is justblahI know that SET NOCOUNT ON switches the "X row affected" thing. Buthow about column headers ?Thanks for your time,Seeker
View 5 Replies
View Related
Feb 21, 2008
Hi all,I have a stored procedure which sets an output variable. How could I access this after calling an SqlDataSource.Select()? ThanksChris
View 9 Replies
View Related
Sep 9, 2004
Hello, i got this working using vb.net but becuase I'm learning asp.net I thought it would be a ggood idea to replicate the same function using C# as well1
The problem is I receive an output parameter through a stored procedure which is the users id.
I want to add this to a cookie as I know I'll be using it again and again, but the error I keep getting is cannot cxonvert type 'string' to type 'int'.
I've tried using conversion functions to make the value returned into an int value but I had no success!
I did this -
int id = (int)mycommand.Parameters["@id"].Value;
and then try to save that to the cookie but the error keeps appearing, I've also tried using the parse and ToInt32 function but the same/similar problems - any ideas?
Thanks
View 1 Replies
View Related
Oct 10, 2005
Hi all,Is there a data transfer difference between returning a datareader that only has one row or using output parameters? For instance, I have a login page that shows the last login, member since, display level, number of file views, etc. Currently I am returning a datareader that reads one row and assigns this data to labels. But would it be quicker to use OUTPUT parameters instead of using a dataread and just get the values from the command object?ThanksJosh
View 3 Replies
View Related
Feb 8, 2005
When I call a proc with one input parameter and two output parameters, (all the three parameters having defaults in the proc), I was expecting to see these values defaulted to in the proc. But apparently, this not the case. Could someone tell me what am I doing wrong here? Appreciate your time.
USE Pubs
GO
CREATE PROCEDURE dbo.MyTestProc
(@InputParamVARCHAR(30) = 'Input',
@OutPutParam1VARCHAR(30) = 'OutputParam1' OUTPUT,
@OutPutParam2VARCHAR(30) = 'OuputParam2' OUTPUT)
AS
BEGIN
SELECT @InputParam, @OutPutParam1, @OutPutParam2
END
GO
-- Call to the proc
USE Pubs
GO
DECLARE @I1VARCHAR(30)
DECLARE @P1VARCHAR(30)
DECLARE @P2VARCHAR(30)
SET@I1 = 'PassedInput'
EXECdbo.MyTestProc
@I1,
@P1 OUTPUT,
@P2 OUTPUT
SELECT@I1, @P1, @P2
EXEC dbo.MyTestProc
View 3 Replies
View Related
Feb 23, 2008
Hi can any one explain why when I specify an outparameter as a string I only get back the first letter of the string when I call the procedure from .net?
my procedure is...
ALTER PROCEDURE dbo.UpdateUserPACSPASDetails
@UserGUID uniqueidentifier,
@UserPACSName varchar(32),
@PASName varchar(32),
@UserPACSPassword varchar(95),
@UserPACSPasswordIV varchar(95),
@UserFriendlyName varchar(32),
@Message varchar(32) OUTPUT,
@MessageNumber int OUTPUT
AS
SET NOCOUNT ON
DECLARE @tmpGUID as uniqueidentifier /*stores the temp guid from the function */
DECLARE @ReturnDomainName as varchar(32) /*stores the tmp domainname of the user who has the PACS name */
SET @Message=CAST('' as varchar(32))
SET @MessageNumber= 0
/* first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPACSName(@UserPACSName)
IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN
/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 1
RETURN
END
END
/*now check the PAS name to see if it exists or is already in use by someone else
first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPASName(@PASName)
IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN
/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 2
RETURN
END
END
UPDATE tblUsers
SET UserPACSName=@UserPACSName,UserPACSPassword=@UserPACSPassword,
UserPACSPasswordIV=@UserPACSPasswordIV, PASName=@PASName, UserFriendlyName=@UserFriendlyName
WHERE UserGUID=@UserGUID
SET @Message=CAST('' as varchar(32))
SET @MessageNumber=3
RETURN
I call this proc via a vb.net program using a command.executenonquery and then look at the parameter.
For some reason I only get the first letter of the string back! i.e. if the string is LSDNET/nik I get 'L'.
If I run the proc from visual studio step in to procecure it works fine!
the vb I'm using is... (the createaddparameter simply creates a bunch of parameters to add to a command -with the specified names and dbtypes).
I'm obviously missing a trick but what it is I can't see.
I seem to be able to pass all other data types back easily!
Dim LoadUserCommand As New SqlCommand
'create add command
Dim CreateAddParameter() As String = {"@UserGUID", _
"@UserPACSName", "@PASName", "@UserPACSPassword", _
"@UserPACSPasswordIV", "@UserFriendlyName", "@Message", "@MessageNumber"}
Dim UpdateDBTypes() As DbType = {DbType.Guid, DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, _
DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, DbType.Int32}
LoadUserCommand = lclSQLHelper.CreateOLEDBCommand("UpdateUserPACSPASDetails", CreateAddParameter, UpdateDBTypes, lclDBConn.DBConnection)
LoadUserCommand.Parameters(6).Direction = ParameterDirection.Output
LoadUserCommand.Parameters(7).Direction = ParameterDirection.Output
'update the data in the data table in the database
LoadUserCommand.Parameters(0).Value = lclUser.UserGUID
LoadUserCommand.Parameters(1).Value = lclUser.UserPACSName
LoadUserCommand.Parameters(2).Value = lclUser.UserPASName
LoadUserCommand.Parameters(3).Value = lclUser.UserPACSPassword
LoadUserCommand.Parameters(4).Value = lclUser.PACSPasswordIV
LoadUserCommand.Parameters(5).Value = lclUser.UserFriendlyName
LoadUserCommand.Parameters(6).Value = ""
LoadUserCommand.Parameters(7).Value = 0
Try
Dim lclRet As Integer = LoadUserCommand.ExecuteNonQuery()
Dim tmpDomainName As String
tmpDomainName = LoadUserCommand.Parameters(6).Value.ToString 'read the name back
Dim tmpMsgNumber = LoadUserCommand.Parameters(7).Value 'read the message number back
Select Case tmpMsgNumber 'not written yet as I haven't got this far!!!
Case 1 'pacs username already exists
Case 2 'pas username already exists
Case 3 'execute fine and all names updated
End Select
View 3 Replies
View Related
Sep 4, 2007
can somebody let me understand how this Output parameters works in sp_executesql / Dynamic TSQL
I want to store a integer value resulting from executing a dynamic query. I was trying to follow the syntax but to be honest I didn't get it, and resulted in err. So can somebody help me in understanding how it works and how and where to declare the variables to be output and what command does that Output etc.
Thanks a lot in advance
View 4 Replies
View Related
Jun 16, 2006
I'm trying to output all the values selected through a multivalue parameter, but I am having difficulties. In the text box, the parameter contains only four member functions (value, label, count, ismultivalue). However, to output a value or label for the parameter you need to specify an index in the form of:
Parameters!Names.Value(0)
Ideally, I would like to be able to obtain all these values with a function call, but it doesn't look like there is one. Any ideas of how to get all the values?
Thank you!!
View 4 Replies
View Related
Feb 3, 2008
I have a stored procedure that outputs an XML result. As it is right now, I will periodically go into SQL Server Mngmnt Studio and run the procedure, then save the XML output to an XML file that my website uses. The procedure takes too long run (it is recursive through the entire data set) to have the website call the procedure on its own, and wait for the results.
My question is this: Can I create a trigger that will run this stored procedure and save the results to a file? Is that possible? The trigger would call the procedure any time a specific table is updated (which will be a rare occurrence), then save the file to the path provided so the website users could always have the most up-to-date information without having long wait times caused by long waits for the procedure to run.
Any advice on how best to accomplish this will be appreciated.
Thanks in advance.
View 1 Replies
View Related
Jul 23, 2005
I have a dynamic database that will be periodically queried to selectthe data from a blob field. This blob data field is text of a variablelength. The data will be selected using an id field and a date range.There will be multiple blob fields returned that I would like to outputinto a txt file in a local folder.I have the blob fields showing up as text in the field and not areferring link. Can someone point me to an output to text solution?Thanks
View 1 Replies
View Related
Oct 30, 2007
When I run my CLR/C# stored procedure inside Visual/BI Dev Studio, the results in a datatable are sent over pipe and displayed in output window without the grid format. I looked up inOptions and settings but couldn't find a way to change the format of output window to grid. I would like to be able to do that so I can copy/paste the results in excel.
View 4 Replies
View Related
Feb 11, 2008
i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.
View 3 Replies
View Related
Mar 17, 2004
Hi,
thanks for reading!
Here is my problem: I have a strored procedure that inserts some records
into one table and then selects some records from another table at the end.
The stored procedure takes several parameters, first one of them is marked as
OUTPUT. I'm using it to return an id of the inserted record. The procedure is called from asp.net code with first parameter set as ParameterDirection.InputOutput (tried with just Output as well). Now for the problem: if the the select statement at the end returns 0 records everything works and i my first parameter contains the @@IDENTITY value from the insert statement like it is supposed to.
If the select statement at the end returns 1 or more records my output parameter is not updated at all and contains the same value as before the procedure was run. All the records are inserted correctly.
if i try to return the @@identity as a plain select statement instead of through the parameter
i get System.DBNull.
I hope you can shed some light on this for me. Here is my stored procedure:
CREATE PROCEDURE cwSaveProductInquiry
@inquiryId int OUTPUT,
@libraryName nvarchar(500),
@contactName nvarchar(200),
@address nvarchar(100),
@city nvarchar(50),
@state nvarchar(3),
@zip nvarchar(10),
@phone nvarchar(50),
@email nvarchar(100),
@comment nvarchar(3000),
@productIds nvarchar(2000)
AS
INSERT INTO INQUIRY (LibraryName, ContactName, Address, City, State, Zip, Phone, Email, Comment) VALUES(@libraryName, @contactName, @address, @city, @state, @zip, @phone, @email,@comment)
--i tried including this statement at the end as well but that did not do the
--trick either
select @inquiryId=@@IDENTITY FROM INQUIRY
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @objectId varchar(200) -- this holds each array value as it is returned
if(@productIds is not null)
begin
while patindex('%,%' , @productIds) <> 0
begin
select @separator_position = patindex('%,%' , @productIds)
select @objectId= left(@productIds, @separator_position - 1)
INSERT INTO PRODUCT_INQUIRY_LOOKUP (ProductId,InquiryId) VALUES(@objectId, @inquiryId)
select @productIds = stuff(@productIds, 1, @separator_position, '')
end
end
set nocount off
Select Distinct Email from vPRODUCT_CONTACT WHERE ProductId in
(Select ProductId From Product_Inquiry_Lookup Where InquiryId=@inquiryId)
GO
View 3 Replies
View Related
