calling multiple stored procedures from a stored procedure

Hi.

I'm quite sure that it's just my search methods that suck, but I can't find any good examples of how to call multiple stored procedures from a stored procedure.

The thing is that I have a whole bunch of tables, each with auto generated stored procedures for get by id, get all, insert, delete, update...
Since I want avoid multiple calls to the database to fill my business objects I thought I'd make a sp that gathers data from multiple tables, using the standard get sp's...

the structure looks like this

base (baseID, baseCol1, baseCol2)
base_elementContainer (base_elementContainerID, baseID, elementContainerID)
elementContainer (elementContainerID, elementContainerCol1, elementContainerCol2)
elementContainer_element (elementContainer_elementID, elementContainerID, elementID)
element (elementID, elementCol1, elementCol2)
element_objectOne (element_objectOneID, elementID, objectOneID)
objectOne (objectOndeID, objectOneCol1, objectOneCol2)

element_objectTwo (element_objectTwoID, elementID, objectTwoID)
objectTwo (objectTwoID, objectTwoCol1, objectTwoCol2)

and what I need to do is to fetch all objectOne and objectTwo by baseID... and collect a few things on the way...

would be happy to get a few quick pointers or a link to some good example codes or a tutorial...

thanks...

You can get results from multiple procs into local tables. Refer to this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1016268&SiteID=1

Calling External Code that returns a Dataset

I know there are ways to embed custom code into these reports, but the
examples I've seen call the custom code outside the context of
acquiring a resultset.
Suppose I've created a class/assembly that has a shared method which
returns a dataset. Is it possible to use this in my report to
generate the record set rather than calling a stored prodedure or SQL
from within the report?
RobRob,
Yes, the Microsoft sample dataset extension as explained in the product
documentation does this. If you want a custom applicaton to bind to the
dataset, you may find my dataset extension useful.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
<rhoeting@.hotmail.com> wrote in message
news:1098317642.985613.50720@.c13g2000cwb.googlegroups.com...
> I know there are ways to embed custom code into these reports, but the
> examples I've seen call the custom code outside the context of
> acquiring a resultset.
> Suppose I've created a class/assembly that has a shared method which
> returns a dataset. Is it possible to use this in my report to
> generate the record set rather than calling a stored prodedure or SQL
> from within the report?
> Rob
>|||Teo,
I will check into this. Actually, I've built some stronly typed
collections (using Rocky Lhotka's CSLA framework), which can be easily
converted to Datasets. Currently they are used to display results in
a bindable listview on a heavy client. My goal is to "reuse" these
classes as the engine for the reports (or simply the "print" version of
the listview). Can these reports bind to a strongly typed collection,
or does it need to be a dataset?
BTW, you book came in the mail today and it looks fantastic.
Thanks,
Rob|||Teo is off at a book signing and can't reply (okay, I lied!).
Reporting Services is extensible in the area of data providers. You would
have to write your own data provider though.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"rhoeting" <rhoeting@.hotmail.com> wrote in message
news:1098325731.315791.23320@.f14g2000cwb.googlegroups.com...
> Teo,
> I will check into this. Actually, I've built some stronly typed
> collections (using Rocky Lhotka's CSLA framework), which can be easily
> converted to Datasets. Currently they are used to display results in
> a bindable listview on a heavy client. My goal is to "reuse" these
> classes as the engine for the reports (or simply the "print" version of
> the listview). Can these reports bind to a strongly typed collection,
> or does it need to be a dataset?
> BTW, you book came in the mail today and it looks fantastic.
> Thanks,
> Rob
>|||I am back... woof, my hands hurt :-)
Your custom dataset extension can report off anything that can expose its
data in a tabular fashion.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TIM ELLISON" <TimEllison@.direcway.com> wrote in message
news:OL%23cxv1tEHA.948@.tk2msftngp13.phx.gbl...
> Teo is off at a book signing and can't reply (okay, I lied!).
> Reporting Services is extensible in the area of data providers. You would
> have to write your own data provider though.
> --
> Regards,
> Tim Ellison, MCP
> Ironworks Consulting, LLC
> (m) 804.405.4874
> "rhoeting" <rhoeting@.hotmail.com> wrote in message
> news:1098325731.315791.23320@.f14g2000cwb.googlegroups.com...
> > Teo,
> >
> > I will check into this. Actually, I've built some stronly typed
> > collections (using Rocky Lhotka's CSLA framework), which can be easily
> > converted to Datasets. Currently they are used to display results in
> > a bindable listview on a heavy client. My goal is to "reuse" these
> > classes as the engine for the reports (or simply the "print" version of
> > the listview). Can these reports bind to a strongly typed collection,
> > or does it need to be a dataset?
> >
> > BTW, you book came in the mail today and it looks fantastic.
> > Thanks,
> >
> > Rob
> >
>|||I am new to Reporting Services but mayby someone can help me.
I am trying to get a pdf directly from a report with an external dataset. The external dataset works fine if I read it directly from an xml file in my compiled dataset extension. But when I try to do that whith a parameter wich contains xml , it isn't possible to create a dataset from my parameter(xml).
ERR=An attempt was made to set a report parameter 'xmlData' that is not defined in this report. But it is defined with my parameters and also one default in my report parameters
some code:
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null;
ParameterValue[] optionalParams = null;
Warning[] optionalWarnings = null;
DataSet ds=new DataSet();
ds.ReadXml("C:\\RS\\C#\\test.xml");
string Param=ds.GetXml().ToString();
ParameterValue[] pv = new ParameterValue[1];
pv[0] = new ParameterValue();
pv[0].Name = "xmlData";
pv[0].Label = "xmlDataLabel";
pv[0].Value = Param;
ResultStream = rs.Render("/test/Custom", "PDF", null, "some xml",
pv,null, null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
Response.BinaryWrite(ResultStream);
this code requests a PDF from the report /test/Custom wich use an external dataset
I calls my external dataset but I cant find my parameters
some code external dataset
public Microsoft.ReportingServices.DataProcessing.IDataParameterCollection
Parameters
{
get { return _parameters; }
}
public class DSXParameter : IDataParameter
{
string _parameterName;
object _parameterValue;
// Default constructor
public DSXParameter()
{
}
// Constructor to accept parameter name & value.
public DSXParameter(string parameterName, object value)
{
_parameterName = parameterName;
_parameterValue = value;
}
public String ParameterName
{
get { return _parameterName; }
set { _parameterName = value; }
}
public object Value
{
get
{
return _parameterValue;
}
set
{
_parameterValue = value;
}
}
}
--
Doesn't reach this method ExecuteReader
--
public Microsoft.ReportingServices.DataProcessing.IDataReader ExecuteReader
(Microsoft.ReportingServices.DataProcessing.CommandBehavior behavior)
{
DSXExtensionDataReader testReader = new DSXExtensionDataReader(this.Parameters);
testReader.CreateDataSet();//_connection._xmlSchema
return testReader;
}
Hope you can help me
Thanks in advance
]]>
From http://www.developmentnow.com/g/115_2004_10_0_0_452155/Calling-External-Code-that-returns-a-Dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Calling a Dts package from VB.NET

hi,

i'm trying to call a dts package from vb.net.

i got 2 examples which both don't work.

first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error.

source code:
Dim serverName As String = "SERVERNAME"
Dim oPackage As New DTS.Package()
Dim oStep As DTS.Step
Dim pVarPersistStgOfHost As Object = Nothing

oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD",
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _
"DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost)

For Each oStep In oPackage.Steps
oStep.ExecuteInMainThread = True
Next

oPackage.Execute()

Dim err As Long
Dim source, description, message As String
For Each oStep In oPackage.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo(err, source, description)
message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source,
description)
Else
message = "Success"
End If
Next

oPackage.UnInitialize()
oPackage = Nothing

second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface.

source code:
Dim oPackage As New Package2()
Dim oConnection As Connection2
Dim oStep As Step2
Dim oTask As Task
Dim oCustomTask As BulkInsertTask

oConnection = oPackage.Connections.[New]("SQLOLEDB")
oStep = oPackage.Steps.[New]()
oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")
oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error

With oConnection
.Catalog = "CATALOG"
.DataSource = "SERVERNAME"
.ID = 1
.UseTrustedConnection = True
.UserID = "USERID"
.Password = "PASSWORD"
End With

oPackage.Connections.Add(oConnection)
oConnection = Nothing

With oStep
.Name = "InsertGemal"
.ExecuteInMainThread = True
End With

With oCustomTask
.Name = "InsertGemal"
.DataFile = "D:\ImportGemal.dat"
.ConnectionID = 1
.DestinationTableName = "Gemal"
.FieldTerminator = ";"
.RowTerminator = "\r\n"
End With

oStep.TaskName = oCustomTask.Name

With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With

oPackage.Execute()

oPackage.UnInitialize()
oPackage = Nothing

any help highly appreciated!

t.i.a.,
ratjetoes.

Then you need to use xp_cmdshell by granting a proxy account for SQL Server Agent the right permissions to run the package through Asp.net. The links below covers the extended stored proc xp_cmdshell and the proxy account. One more thing the DTS site is run by a DTS expert there are many resources you could use. Post again if you have more questions. Hope this helps.

http://forums.asp.net/thread/1440879.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

|||

tnx 4 your help,

it was indeed somehting with permission rights.

i got it working now.

ratjetoes.

|||

ratjetoes:

tnx 4 your help,

it was indeed somehting with permission rights.

i got it working now.

ratjetoes.

I am glad I could.

call db2 stored procedure

Are there any examples of aclling a db2 stored procedure with input and output parameters from sql 2000 tsql? Thanks

use linked server..

example:

EXEC sp_addlinkedserver

@.server='DB2',

@.srvproduct='Microsoft OLE DB Provider for DB2',

@.catalog='DB2',

@.provider='DB2OLEDB',

@.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

EXEC sp_addlinkedsrvlogin 'DB2', 'false', NULL, 'DB2User', 'DB2Password'

Then use OPENQUERY function to call your target DB2 objects..See more on online book about Linked Server.|||Thanks for the update. I see examples of using openquery with db2 select statements but couldn't find any that called a stored procedure with input and output procedures. Is there another function that should be used? Thanks|||Are there any examples of calling a db2 stored procedure with input and output parameters? Should I use the {call format. are there any examples for that? Thanks