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.
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.
I can create 1 view of a database then use that views results to query
but i want do this with a stored procedure
pass some data to it, so it selects some data
query the results it created and output this data.
ie
pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output
)
select from a database with the firstpassed value
Select *
From TableName
Where ID = @FirstValue
Then
using the results from the above select
Select *
From theResultsOfAbove
Where ID = @SecondValue
any ideas would be fabo !
Hi
I would like to create and run a stored procedure from VB. I will pass a variable to the stored procedure and after that according to the parameter, records are selected and shown in the DB grid on the form.. I'm using VB 5 and mssql 2000. I have to show selective data in the DB Grid.
Any suggestions and help are welcomed. Thanks in advance.
I am working on an OLAP modeled database.
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.
In SQL Query Analyzer, there is a Query drop down window that gives youthe option to change the output from grid to text for printing ifneeded. My question is, can this be programmed so a stored procedurewill always print in text without having to manually change the windoweach time the procedure is run? I could find nothing under the logicalsearches in books online.Thanks JAB
View 1 Replies View RelatedI can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.
I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.
How do I do that?
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedPerhaps I was seeing things earlier, and it's really a minor thing, but when I display query results in Management Studio, isn't there a setting that turns the background of cells containing NULL to a pale yellow color? I could've sworn it used to do this, and I've searched high and low for a preference setting, but no joy.
Does this setting exist?
Thanks,
Dave
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related 1 public static List<string> viewtree(int root)
2 {
3 SqlConnection con = new SqlConnection(mainConnectionString);
4 con.Open();
5 try
6 {
7 List<string> ids = new List<string>();
8 SqlCommand command = new SqlCommand(@"ShowHierarchy2", con);
9 command.Parameters.AddWithValue("@root", root);
10 command.Parameters.Add(new SqlParameter("@outstring", SqlDbType.VarChar));
11 command.Parameters["@outstring"].Direction = ParameterDirection.Output;
12 command.CommandType = CommandType.StoredProcedure;
13 //command.ExecuteScalar();
14 //ids = command.Parameters["@outstring"].Value.ToString();
15
16 SqlDataReader dr = command.ExecuteReader();
17 while (dr.Read())
18 {
19 ids.Add((dr["@outstring"].ToString()));
20 }
21 //command.Parameters.Clear();
22
23 return ids;
24 }
25 finally
26 {
27 con.Close();
28 }
29 }
Can someone tell me why i'm getting the following error:String[1]: the Size property has an invalid size of 0. Thanks in advance
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?
Thanks.
David J.
I'm calling a stored procedure from outside program using
Execute SPid(a stored procedure).
In the SPid procedure i want to return a value using
Output.
Can any one tell me how do it since i never used it before ?
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
any help will be appreciated
regards,
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
I am using a stored procedure to query tables and format a record to write to a file. Below is the syntax I'm using to do the write.
set @cmd = 'echo ' + rtrim(@patient_rec) + ' >> f:output
ecall.csv'
exec master..xp_cmdshell @cmd
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
View 1 Replies View Relateddatagrid. Sorry, I'm new to it. For the few times I've done datagrids in the past, I've built
my own selects and filled the grid. This time I need to use someone elses stored procedure.
I simply don't know how to get the results back into my VB datagrid. Can someone point
me in the right direction? Thanks.
How do I filter the results of a stored procedure?
View 2 Replies View RelatedHi
I am trying to execute a stored procedure (which returns multiple tables) and use these results to populate an Excel file. I am totally lost on how to capture the results and use it. Any help will be appreciated.
Thanks
I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
View 1 Replies View RelatedI have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have:
IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL
DROP PROCEDURE dbEvent
GO
CREATE PROCEDURE
@Name varchar(200)
,@Location varchar(200)
AS
INSERT INTO Event
(
NAME
, LOCATION
)
VALUES
(
NAME
, @LOCATION
)
SELECT SCOPE_IDENTITY()
And my code behind: public Int64 Insert(SqlConnection Conn)
{
try
{
using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output;
Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName;
Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location;
if (Conn.State != ConnectionState.Open) Conn.Open();
Command.ExecuteNonQuery();
Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString());
return _requestId;
}
I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value.
Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
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.
Hi
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?
Hi,Got an annoying SQL / .net error. I've created a stored procedure to take in an ID and map this to an ID table and get another ID out. So i have one ID in and one out. I get the following error:Procedure 'sp_SvrMapping' expects parameter '@rID', which was not supplied.Any idea why this happens? surely the output ID doens't need to be defined before its retrived? If so how?Thanksdrazic19p.s. code for both below.**************************************************CREATE PROCEDURE dbo.sp_SvrMapping
( @uID int, @rID int OUTPUT )
As
SELECT @rID = rID FROM tbl_mapping WHERE uID= @uIDGO**************************************************
Dim conStr As String = ConfigurationSettings.AppSettings("str_connAuth")
Dim myConn As New SqlConnection(conStr)
Dim myComm As New SqlCommand("sp_SvrMapping", myConn)
Dim test As String = "1"
myComm.CommandType = CommandType.StoredProcedure
myComm.Parameters.Add("@uID", CInt(test))
myConn.Open()
myComm.ExecuteNonQuery() <--- error called
If Not IsDBNull(myComm.Parameters("@rID").Value) Then
MappedServerAccount = myComm.Parameters("@rID").Value
End If
myConn.Close()
EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?
View 2 Replies View RelatedHi.
I'm using stored procedures in SQL7 to return a list of items to a website where they are presented. A new requirement is to present these items in a random order on the website. I have two approaches to this one, either making the result from SQL7 random or presenting the ordered results randomly. Since the presentation logic is used several times (different country subsites), it would be the easiest way to get a randomized result from the stored procedure, which would mean that I only have to change the procedure.
Any thoughts on how to accomplish this, that is returning a list of items but in random order, so that the presentation sequence would be different for each access to the website ??
Any help would be appreciated : )
Thanks!
Eirik Kjølsrud
Systems Developer, Apropos Internett - Norway
Hello everyone,
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname
Thanks
Kevin
Hi
I should validate a USER and if the user is not a valid user it should
return a message to the front end application for this I have a procedure
like this .
Basically I need to pass an output parameter to the front end application
returned by this procedure.
Create procedure test
@userid VARCHAR(20)
AS
Declare c1 cursor AS
Select userid from USERS_TBL
Open Cursor C1
FETCH NEXT from C1 INTO @Temp
While @fetch_status = 0
BEGIN
If @Temp <> @Userid
-- IF the USER is not a valid USER ...It should not execute
the Futher code(statement) it should exit the procedure
by returning the error statement.
---
---
---
FETCH NEXT from C1 INTO @Temp
END
Thanks
VENU
can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter.
ie insert record and get its id column value in output parameter.
any one knows the way?
I have a stored procedure (named Insert_SRegister) that calls another
stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one.
The program goes like this:
The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored
procedure.
The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission;
The code of the first procedur is :
Create Procedure dbo.Insert_SRegister
@YGroup VarChar (3),
@YDate VarChar (4),
@YTerm Char(1),
@SName VarChar(30),
@SSecondName VarChar(30),
@SFirstName VarChar(30),
@SGender TinyInt,
@SBDate DateTime,
@SBPlace VarChar(50),
@SOCountry VarChar(50),
@SOPassCount VarChar(50),
@PassNo VarChar(50),
@SOLang VarChar(50),
@MOLang VarChar(50),
@MName VarChar(50),
@MHAdd VarChar(50),
@MHT1 VarChar(20),
@MHT2 VarChar(20),
@MHT3 VarChar(20),
@MHMob VarChar(20),
@MWAdd VarChar(50),
@MWT1 VarChar(20),
@MWT2 VarChar(20),
@MExt VarChar(10),
@MWMob VarChar(20),
@FName VarChar(50),
@FHAdd VarChar(50),
@FHT1 VarChar(20),
@FHT2 VarChar(20),
@FHT3 VarChar(20),
@FHMob VarChar(20),
@FWAdd VarChar(50),
@FWT1 VarChar(20),
@FWT2 VarChar(20),
@FExt VarChar(10),
@FWMob VarChar(20),
@EAdd VarChar(50),
@ETele VarChar(20),
@SchName VarChar(50),
@SAdd VarChar(50),
@FBNo Smallint,
@FSNo Smallint,
@FONo VarChar(50),
@EMedical VarChar(1000),
@FDetails VarChar(1000),
@FRDetails VarChar(1000),
@PName VarChar(50),
@StudentStatus VarChar(50) OUTPUT,
@ID VarChar (50) OUTPUT
As
Begin
Declare @Exists Int, -- Return Value
@StudentCode VarChar(50)
-----------------------------------------
-- here I call the second proc and assgin it output to variable
--@StudentCode
-----------------------------------------------------------------------------------
exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT
----Do work and insert info in db
End
Code of the second stored procedure is:
ALTER Procedure dbo.Generate_Student_Code
@YGroup VarChar (3),
@YDate VarChar (4),
@newID VarChar (50) OUTPUT
As
Begin
set nocount on
Declare @Exists int, -- Return Value
@Mv varchar(5),
@IdLen int
If Exists(Select SID
From SRegisteration)
Begin
select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration
Set @IdLen = DATALENGTH(@Mv)
Set @Mv =
case
when @IdLen = 1 then '0000' + @Mv
when @IdLen = 2 then '000' + @Mv
when @IdLen = 3 then '00' + @Mv
when @IdLen = 4 then '0' + @Mv
else @Mv
end
Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv
---------Here I return the ID value
select @newID
--------------------------------------
Select @Exists = 1
End
Else
Begin
Select @Exists = 0
Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001'
---------Here I return the ID value
select @newID
------------------
Return @Exists
End
End
Hi,
I've a large number of stored procedures that output result sets, as part of a test script it would be useful for me to be able to save the results from these stored procedures into temporary tables, without having to pre-create these tables, i.e. use the equivalent of a select * into statement.
Is this possible ?
Sean