calling stored procedure with parameter

Does anyone know how to pass a parameter from a stored
procedure to a DataAdapter?
the following is my code:
i used the DataAdapter wizard to set its properties with
my stored procedure.
//create a data adapter
SqlDataAdapter da = sqlDataAdapter1;
//create a data set and fill it by
calling Fill method
DataSet ds = new DataSet("Cust");
da.Fill(ds,"Customers");
//attach data set's default view
to the data grid control
DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Customers";
DataGrid1.DataBind();
**When i run it, it asks for the stored procedure's
parameter which is the value of a text field in my asp.net
application.For performance reasons, you don't want to use the wizards to generate
code in ADO.NET -- you'd be much better off just deleting all the
wizard-generated stuff and starting from scratch. I'd recommend
getting a copy of ADO.NET by David Sceppa, Microsoft Press.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Wed, 12 Nov 2003 14:33:58 -0800, "zoe"
<anonymous@.discussions.microsoft.com> wrote:
>Does anyone know how to pass a parameter from a stored
>procedure to a DataAdapter?
>the following is my code:
>i used the DataAdapter wizard to set its properties with
>my stored procedure.
> //create a data adapter
> SqlDataAdapter da =>sqlDataAdapter1;
> //create a data set and fill it by
>calling Fill method
> DataSet ds = new DataSet("Cust");
> da.Fill(ds,"Customers");
>
> //attach data set's default view
>to the data grid control
> DataGrid1.DataSource = ds;
> DataGrid1.DataMember = "Customers";
> DataGrid1.DataBind();
>
>**When i run it, it asks for the stored procedure's
>parameter which is the value of a text field in my asp.net
>application.

Calling store procedure in Report throu VB

Dears
How to call store procedure in Crystal Report through VISUAL BASIC. i have Sp which has 3 parameters one is string others are dates parameter ,
how to to in the visual basic coding.
can any one help me passing paramerters .
Thanks
AnnuFor Vb 6:

Get rs (Recordset) calling stored procedure with parameters

After:

Dim report As CRAXDRT.report

Set report = crystal.OpenReport(App.Path & _
IIf(Right(App.Path, 1) = "\", "", "\") & _
s_Rpt & GetAdditionalNamePart(s_Rpt) & ".rpt") 'OPEN REPORT

report.Database.LogOnServerEx _
"crdb_odbc.dll", _
c_Settings.GetVal(CRYSTALDSN), _
c_Settings.GetVal(DBNAME), _
c_User.Name, _
c_User.Password, , _
c_Db.ConnectionStr
report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET

Load frmReport
'ShowSendButton s_Rpt, frmReport.crV, frmReport.cmdSend

'crV - crystal viewer control
frmReport.crV.ReportSource = report 'LINK VIEWER TO REPORT
frmReport.crV.ViewReport

Best,

ika|||This is using VB 6 and CR 8.5 with RDC...

You can get your data from the Stored Proc through ADO and then pass the ADO recordset to Crystal:

'dsrReport is the name of your dsr file
Dim Report As New dsrReport

Set cmdData = New ADODB.Command
Set rsData = New ADODB.Recordset
cmdData.ActiveConnection = cnConnection
cmdData.CommandText = "sp_StoredProcName"
cmdData.CommandType = adCmdStoredProc
cmdData.Parameters.Refresh
cmdData.Parameters("@.Param1") = strParam1
cmdData.Parameters("@.Param2") = dtmParam2
cmdData.Parameters("@.Param3") = dtmParam3

'Required for RecordCount to return an accurate count
rsData.CursorType = adOpenDynamic
rsData.CursorLocation = adUseClient
rsData.Open cmdData

If Not rsData.EOF Then
Report.DiscardSavedData
Report.Database.SetDataSource rsData, 3, 1
End Ifsql

calling sp with datetime parameter from ole db

Hi folks,
I have a huge problem calling a stored proc with a datetime parameter using
ole db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Exec
ute Stored Procedure Using CDynamicParmeterAccesor) as a starting point and
modified the sp "sp_getStudent" so that it gets another parameter @.d datetim
e. But now I'm not able to execute this proc any more: rs.open() returns 0x8
0040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
Kind regards
Thomas
P.S. Any other kind of third parameter (int, float, ...) works well but date
time (smalldatetime) doesn'tHi Thomas
Your errror seems to be:
DB_E_DATAFIELD_OVERFLOW 80040E21 OLE DB Specific:
The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.
Check the values of the parameters you are passing and (possibly) if you are
mixing the values/parameters up (it is better to add the parameters in the
order in which they are declared!)
John
"Thomas Hein" wrote:

> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter using ole db
(CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Execute Stored Pro
cedure Using CDynamicParmeterAccesor) as a starting point and modified the sp "sp_ge
tSt
udent" so that it gets another parameter @.d datetime. But now I'm not able to execute this
proc any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?rkred">
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but da
tetime (smalldatetime) doesn't
>|||The error number refers to an overflow error - since the target column
is smalldatetime, I'd check the date value being put in and make sure
it's a valid smalldatetime (which is quite different than datetime).
valid smalldatetime values are Jan 1, 1900 to June 6, 2079.
Thomas Hein wrote:
> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter
> using ole db (CDynamicParameterAccessor). I used the sample from MSDN
> (HOWTO: Execute Stored Procedure Using CDynamicParmeterAccesor) as a
> starting point and modified the sp "sp_getStudent" so that it gets
> another parameter @.d datetime. But now I'm not able to execute this proc
> any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's
> wrong?!?
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but
> datetime (smalldatetime) doesn't
>|||Hi John, hi Trey,
thank's for the info - the problem is that I do not make any parameter-bindi
ng on my own, I just call
rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
rs.Prepare();
rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
the same way as I do without the datetime parameter, but the next call to
rs.Open(NULL, NULL, false);
returns the error stated below...
Kind regards
Thomas
"Thomas Hein" <thomas.hein@.%nospam%inform-ac.com> schrieb im Newsbeitrag new
s:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
Hi folks,
I have a huge problem calling a stored proc with a datetime parameter using
ole db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Exec
ute Stored Procedure Using CDynamicParmeterAccesor) as a starting point and
modified the sp "sp_getStudent" so that it gets another parameter @.d datetim
e. But now I'm not able to execute this proc any more: rs.open() returns 0x8
0040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
Kind regards
Thomas
P.S. Any other kind of third parameter (int, float, ...) works well but date
time (smalldatetime) doesn't|||Hi
I assume that you are setting the first parameter as in the example code?
Does SQL profiler show anything?
John
"Thomas Hein" wrote:

> Hi John, hi Trey,
> thank's for the info - the problem is that I do not make any parameter-bin
ding on my own, I just call
> rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
> rs.Prepare();
> rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
> the same way as I do without the datetime parameter, but the next call to
> rs.Open(NULL, NULL, false);
> returns the error stated below...
> Kind regards
> Thomas
> "Thomas Hein" <thomas.hein@.%nospam%inform-ac.com> schrieb im Newsbeitrag
news:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
> Hi folks,
> I have a huge problem calling a stored proc with a datetime parameter using ole
db (CDynamicParameterAccessor). I used the sample from MSDN (HOWTO: Execute Stored P
rocedure Using CDynamicParmeterAccesor) as a starting point and modified the sp "sp_
get
Student" so that it gets another parameter @.d datetime. But now I'm not able to execute thi
s proc any more: rs.open() returns 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?darkred">
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well but
datetime (smalldatetime) doesn't
>|||I concur with John - you'll need to run Profiler to see what's acutally
being passed to sql server.
Since you're getting the parameter values from the UI, I even more
confident that you're getting an invalid smalldatetime value.
Thomas Hein wrote:
> Hi John, hi Trey,
> thank's for the info - the problem is that I do not make any
> parameter-binding on my own, I just call
> rs.Create(session, "exec sp_getstudent ?, ? out, ? out");
> rs.Prepare();
> rs.BindParameters(&rs.m_hParameterAccessor, rs.m_spCommand, &pDummy);
> the same way as I do without the datetime parameter, but the next call to
> rs.Open(NULL, NULL, false);
> returns the error stated below...
> Kind regards
> Thomas
>
> "Thomas Hein" <thomas.hein@.%nospam%inform-ac.com
> <mailto:thomas.hein@.%nospam%inform-ac.com>> schrieb im Newsbeitrag
> news:uledgRAAGHA.2092@.TK2MSFTNGP10.phx.gbl...
> Hi folks,
> I have a huge problem calling a stored proc with a datetime
> parameter using ole db (CDynamicParameterAccessor). I used the
> sample from MSDN (HOWTO: Execute Stored Procedure Using
> CDynamicParmeterAccesor) as a starting point and modified the sp
> "sp_getStudent" so that it gets another parameter @.d datetime. But
> now I'm not able to execute this proc any more: rs.open() returns
> 0x80040e21 (DB_E_ERRORSOCCURRED). What's wrong?!?
> Kind regards
> Thomas
> P.S. Any other kind of third parameter (int, float, ...) works well
> but datetime (smalldatetime) doesn't
>

calling server and db name from a metadata table

I would like to be able to have the Server name and
Database name query from a metadata table so I can use
that as the parameter for my stored procedure. So I don't
have to changes these parameters each time I move my
script to a different server server and/or databases. Is
that possible? Thanks in advance for your help. Daizy
exec
master.dbo.xp_cmdshell 'dtsrun /Smy_server /Umy_login /Pmyp
assword /Nmy_dts_pkg /Mdtspassword'Daizy,
For one possible meaning of your question, the only way to do this is to use
dynamic SQL, which pretty much abandons the security and performance reasons
for using store procedures. (Rule: There is no indirection in elements of
a SQL Server (database, table, column, etc) but they must be explicitly
coded.) In this case you would be trying to do something like:
Select * from @.MyServer.@.MyDatabase.dbo.Tablename
On the other hand, since you are showing an xp_cmdshell line, are you trying
to fill out that line with the name of the server and database that you are
currently running in? In that case, examine:
SERVERPROPERTY('servername')
DB_NAME()
These will give you the name of your current server and database.
Russell Fields
"Daizy" <anonymous@.discussions.microsoft.com> wrote in message
news:2cc7201c39404$db9ff860$a601280a@.phx.gbl...
> I would like to be able to have the Server name and
> Database name query from a metadata table so I can use
> that as the parameter for my stored procedure. So I don't
> have to changes these parameters each time I move my
> script to a different server server and/or databases. Is
> that possible? Thanks in advance for your help. Daizy
> exec
> master.dbo.xp_cmdshell 'dtsrun /Smy_server /Umy_login /Pmyp
> assword /Nmy_dts_pkg /Mdtspassword'

calling one stored proceedure from another

yet another question unfortunately

I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,

ie
say i have select projectid, project name from projects into temp from projects.

how can i then loop around all the rows in temp, to call my stored proceedure for each record?

in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.

can this be done for sql?

I am trying to do something like

for each record in #temp (projectid, project name) find the stored sprceedure value

so my end result will look like

projectid, project name, @.storedproceedure return value
lprojectid, project name, @.storedproceedure return value
projectid, project name, @.storedproceedure return value
projectid, project name, @.storedproceedure return value

any help appreciatedLoops are inneficient and should be avoided and replaced with a set-based solution wherever possible.
I think what's really needed here is a user defined function (or UDF for short)!
I've knocked a quick example up for you to give you the idea.

IF EXISTS (SELECT type FROM sysobjects WHERE type = 'FN' AND name = 'gvFunc') BEGIN
DROP FUNCTION dbo.gvFunc
END
GO

CREATE FUNCTION dbo.gvFunc (
@.dob datetime
, @.curDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @.age int
SET @.age = DateDiff(yy, @.dob, @.curDate)
RETURN @.age
END
GO

DECLARE @.myTable table (
employeeID int IDENTITY(1,1) PRIMARY KEY
, birthDate datetime
, fName varchar(10)
)

INSERT INTO @.myTable (birthDate, fName)
SELECT '1985-05-01', 'George' UNION
SELECT '1954-05-02', 'Timothy' UNION
SELECT '1968-01-29', 'Julie' UNION
SELECT '1986-11-25', 'Adam'

SELECT employeeId
, fName
, dbo.gvFunc(birthDate, GetDate()) As [Age]
FROM @.myTable

DROP FUNCTION dbo.gvFunc

Note to others... I know that I can't use GetDate() within a function - but why is that?

Calling my RS "Invoice Report" from c# app question...

I have a parameter to bring up my report now, which works fine.
But I need to add the functionality of bringing up my report from c#
now.
Any help is appreciated.
Thanks,
TrintYou have two options. One is URL integration. For that you embed the IE
control. Then you set the url property to the appropriate value. You can
pass parameters, rendering options etc. Just check out URL Integration in
books on-line. The other option is to use the new reportviewer control that
ships with VS 2005. It has a winform control that is quite easy to use.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trint" <trinity.smith@.gmail.com> wrote in message
news:1183646995.350764.176420@.w5g2000hsg.googlegroups.com...
>I have a parameter to bring up my report now, which works fine.
> But I need to add the functionality of bringing up my report from c#
> now.
> Any help is appreciated.
> Thanks,
> Trint
>|||On Jul 5, 12:13 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You have two options. One is URL integration. For that you embed the IE
> control. Then you set the url property to the appropriate value. You can
> pass parameters, rendering options etc. Just check out URL Integration in
> books on-line. The other option is to use the new reportviewer control that
> ships with VS 2005. It has a winform control that is quite easy to use.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "trint" <trinity.sm...@.gmail.com> wrote in message
> news:1183646995.350764.176420@.w5g2000hsg.googlegroups.com...
>
> >I have a parameter to bring up my report now, which works fine.
> > But I need to add the functionality of bringing up my report from c#
> > now.
> > Any help is appreciated.
> > Thanks,
> >Trint- Hide quoted text -
> - Show quoted text -
Can you point me to an example please?|||For URL Integration read up on URL Access in Books On Line(there are a whole
lot of topics and examples)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsprg9/html/52c3f2a3-3d6d-4fee-9c46-83f366919398.htm
As far as using the IE control in C# you should just google that and get an
example from C#.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trint" <trinity.smith@.gmail.com> wrote in message
news:1183661710.196713.43980@.w5g2000hsg.googlegroups.com...
> On Jul 5, 12:13 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> You have two options. One is URL integration. For that you embed the IE
>> control. Then you set the url property to the appropriate value. You can
>> pass parameters, rendering options etc. Just check out URL Integration in
>> books on-line. The other option is to use the new reportviewer control
>> that
>> ships with VS 2005. It has a winform control that is quite easy to use.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "trint" <trinity.sm...@.gmail.com> wrote in message
>> news:1183646995.350764.176420@.w5g2000hsg.googlegroups.com...
>>
>> >I have a parameter to bring up my report now, which works fine.
>> > But I need to add the functionality of bringing up my report from c#
>> > now.
>> > Any help is appreciated.
>> > Thanks,
>> >Trint- Hide quoted text -
>> - Show quoted text -
> Can you point me to an example please?
>

Calling MDX query in TSQL Stored Procedure to Pass Parameter

Hi;

I would like to know if it is possible to call MDX query from TSQL Stored Procedure to pass parameter? Can I create MDX query dynamically? If it is possible then can anyone provide some examples. For more details; let say I have one MDX query which has parameters which I would like to call from a TSQL stored procedure and pass that parameter from Stored procedure.

Any help would be apprecaited.

Thanks

M. Essa Mughal

Yes...you can send MDX from SQL

but for this one method that I know is to create a linked server to the OLAP database.

after that you can use openquery to pass the MDX.

ex:

Select *

from Openquery(<Linked_server_name>,

'select [measures].[value] on columns

from cube

where ([Dimension].[member])'

)

you can create the MDX dynamically also.

declare @.qury varchar(2000)

set @.query = 'Select *

from Openquery(<Linked_server_name>,

'select [measures].[value] on columns

from cube

where ([Dimension].[member])'

)

exec master.dbo.executesql @.query

try it.....

Calling GetReportParameters within a report Code block?

I want to write a Code block routine that returns the parameter Prompts
and ValidValue that was selected from within the report. Can I access
the ReportParameters collection (not the parameters collection, but the
actual parameter definitions collection) from within a Code block?
Thanks,
--jasonDo you know that you can get both of the things you want without doing
anything special? Parameters!parametername.Value and
Parameters!parametername.Label Lots of people don't realize you can use
the .Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<burt4684@.gmail.com> wrote in message
news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> I want to write a Code block routine that returns the parameter Prompts
> and ValidValue that was selected from within the report. Can I access
> the ReportParameters collection (not the parameters collection, but the
> actual parameter definitions collection) from within a Code block?
> Thanks,
> --jason
>|||Thanks, Bruce. I didn't know about the .Label property. That's good to
know. My only issue with .Value is some of my parameters are primary keys
and I need a good way to display the lookup values instead of the actual key
values. I was thinking I could iterate through the parameter.ValidValues
collection and display the ValidValue.Name that corresponds to the
parameter.Value that was selected. Do you know if this is possible?
Thanks,
--jason
"Bruce L-C [MVP]" wrote:
> Do you know that you can get both of the things you want without doing
> anything special? Parameters!parametername.Value and
> Parameters!parametername.Label Lots of people don't realize you can use
> the .Label
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <burt4684@.gmail.com> wrote in message
> news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > I want to write a Code block routine that returns the parameter Prompts
> > and ValidValue that was selected from within the report. Can I access
> > the ReportParameters collection (not the parameters collection, but the
> > actual parameter definitions collection) from within a Code block?
> > Thanks,
> >
> > --jason
> >
>
>|||Create another dataset that accepts the parameter as it's queryparameter.
You will end up with a single record (i.e. it has done your lookup), then
anywhere you need to use this value use this expression: =First(Fields!fieldname.value,"DatasetName")
Note that when you create a queryparameter RS adds a report parameter. If
you have the queryparameter with the same name as the reportparameter you
already have (must match case exactly since it is case sensitive) then your
query parameter and report parameter will already be mapped correctly.
This should do exactly what you need to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> Thanks, Bruce. I didn't know about the .Label property. That's good to
> know. My only issue with .Value is some of my parameters are primary keys
> and I need a good way to display the lookup values instead of the actual
key
> values. I was thinking I could iterate through the parameter.ValidValues
> collection and display the ValidValue.Name that corresponds to the
> parameter.Value that was selected. Do you know if this is possible?
> Thanks,
> --jason
> "Bruce L-C [MVP]" wrote:
> > Do you know that you can get both of the things you want without doing
> > anything special? Parameters!parametername.Value and
> > Parameters!parametername.Label Lots of people don't realize you can
use
> > the .Label
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > <burt4684@.gmail.com> wrote in message
> > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > I want to write a Code block routine that returns the parameter
Prompts
> > > and ValidValue that was selected from within the report. Can I access
> > > the ReportParameters collection (not the parameters collection, but
the
> > > actual parameter definitions collection) from within a Code block?
> > > Thanks,
> > >
> > > --jason
> > >
> >
> >
> >|||As always, there's more than one way to skin a cat. Great idea. Thanks for
the help!
--jason
"Bruce L-C [MVP]" wrote:
> Create another dataset that accepts the parameter as it's queryparameter.
> You will end up with a single record (i.e. it has done your lookup), then
> anywhere you need to use this value use this expression: => First(Fields!fieldname.value,"DatasetName")
> Note that when you create a queryparameter RS adds a report parameter. If
> you have the queryparameter with the same name as the reportparameter you
> already have (must match case exactly since it is case sensitive) then your
> query parameter and report parameter will already be mapped correctly.
> This should do exactly what you need to do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jason Burton" <Jason Burton@.discussions.microsoft.com> wrote in message
> news:7130300D-8A2E-40A3-9248-34E2EBDE40EB@.microsoft.com...
> > Thanks, Bruce. I didn't know about the .Label property. That's good to
> > know. My only issue with .Value is some of my parameters are primary keys
> > and I need a good way to display the lookup values instead of the actual
> key
> > values. I was thinking I could iterate through the parameter.ValidValues
> > collection and display the ValidValue.Name that corresponds to the
> > parameter.Value that was selected. Do you know if this is possible?
> >
> > Thanks,
> >
> > --jason
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Do you know that you can get both of the things you want without doing
> > > anything special? Parameters!parametername.Value and
> > > Parameters!parametername.Label Lots of people don't realize you can
> use
> > > the .Label
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > <burt4684@.gmail.com> wrote in message
> > > news:1104422606.388085.162050@.z14g2000cwz.googlegroups.com...
> > > > I want to write a Code block routine that returns the parameter
> Prompts
> > > > and ValidValue that was selected from within the report. Can I access
> > > > the ReportParameters collection (not the parameters collection, but
> the
> > > > actual parameter definitions collection) from within a Code block?
> > > > Thanks,
> > > >
> > > > --jason
> > > >
> > >
> > >
> > >
>
>

Calling a stored procedure that inserts records and generates an output parameter

I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.

How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.

The Execute SQL Task sounds best suited to this scenario: http://www.sqlis.com/default.aspx?58

-Jamie

|||I've already tried that but I need to pass multiple records in the Execute SQL task, not just one.|||

So you're going to be executing the SP for every record in the pipeline? If that is the case then the OLE DB Command transform will be of help but I would recommend you find an alternate means of doing this. Executing a seperate query for each row is not considered great practice.

-Jamie

|||How do you use the OLEDB Command Transform? Actually, I just created a simple app to do the trick. The thing is, I cannot do anything about it. I just need to migrate existing records into a new database. I need to use the existing SP to insert the records from an Oracle database into the new SQL Server database because they are of different schemas and design.

Calling a stored procedure and pass parameter one by one

Hi,

I need to create a batch process which calls a stored procedure.

Here's the scenario.

I have 3 tables

Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

There is a stored procedure spoc_updateTheater that accepts the state and location id and runs a set of sql statements against the theater table. However i want this to run for all the locations in a state one by one. There are some 700 locations in 45 states. How do i pass the location and state id one by one to the stored proc. Can i call this from a commandline or run it as batch process?

vidkshi

You will have iterate through the filtered data of the first procedure and execute the procedure one by one, the is no command to do this you will have to do that manually. Would look like the following pseudo code.

TableContent = YouSelectquery

Loop throught the data and pick one row per iteration
{

Execute your procedure with the values from the picked rows.

}

You can either do this using a cursor (preferable fast forward / read ony if you do not want to change anything) or use a tamp table.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

I will appreciate if someone could tell me the sql for it :

Here's the Theater table and the data. I want to loop through every state and location in the state/location tables and pass that stateid locationid to the stored proc.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Theater]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Theater](
[TheaterId] [int] NULL,
[TheaterName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Revenues] [bigint] NULL,
[StateId] [int] NULL,
[LocationId] [int] NULL
)
END
GO
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (1, N'T1 ', 10000, 1, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (2, N'T2 ', 2333, 1, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (3, N'T3 ', 234234, 1, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (4, N'T4 ', 43454, 1, 2)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (5, N'T5 ', 4454, 1, 2)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (6, N'T6 ', 11132, 2, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (7, N'T7 ', 345, 2, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (8, N'T8 ', 675445, 2, 2)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (9, N'T9 ', 344556, 3, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (10, N'T10 ', 23234, 3, 1)

vidkshi|||

What about:

Code Snippet

CREATE PROCEDURE spUpdateTheater

/*

Written by Jens K. Suessmeyer (c) http://www.sqlserver2005.de

*/

AS

BEGIN

SET NOCOUNT ON

DECLARE @.TheaterData Table

(

COUNTER INT IDENTITY(1,1),

Theaterid INT NULL,

StateId INT NULL

)

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

SET @.I = 1

DECLARE @.Theaterid INT

DECLARE @.StateId INT

INSERT INTO @.TheaterData

(

Theaterid,

StateId

)

SELECT DISTINCT

TheaterId,

Stateid

FROM Theather

SET @.ROWCOUNT = @.@.ROWCOUNT

WHILE @.I <= @.ROWCOUNT

BEGIN

SELECT

@.Theaterid = TheaterId,

@.StateId = Stateid

FROM @.TheaterData

WHERE COUNTER = @.ROWCOUNT

EXEC spExecuteYourUpdateProcedure @.Theaterid,@.StateId

SET @.I = @.I +1

END

END

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

Thanks for the code. I was wondering why you added the theater id and stateid in the temp table whereas it had to be stateid and locationid. Anyways i changd your query to look like this. I also tried running your stored proc as is but with no results.

Code Snippet

ALTER PROCEDURE [dbo].[spUpdateTheater]
/*

Written by Jens K. Suessmeyer (c) http://www.sqlserver2005.de

*/

AS

BEGIN

SET NOCOUNT ON

DECLARE @.TheaterData Table

(

COUNTER INT IDENTITY(1,1),

Locationid INT NULL,

StateId INT NULL

)

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

SET @.I = 1

DECLARE @.Locationid INT

DECLARE @.StateId INT

INSERT INTO @.TheaterData

(

Locationid,

StateId

)

SELECT DISTINCT

LocationId,

Stateid

FROM Theater

select * from @.TheaterData

SET @.ROWCOUNT = @.@.ROWCOUNT

WHILE @.I <= @.ROWCOUNT

BEGIN

SELECT

@.Locationid = LocationId,

@.StateId = Stateid

FROM @.TheaterData

WHERE COUNTER = @.ROWCOUNT

EXEC spoc_Theater @.StateId, @.Locationid

SET @.I = @.I +1

END

END

My store proc spoc_updatetheater looks like this

Code Snippet

ALTER proc [dbo].[spoc_Theater]
@.stateid int,
@.locid int
as
update theater
set revenues = 20000
where stateid = @.stateid and locationid = @.locid
and revenues > 10000

I tried running it however I see there is no change in the Theater table after the query executes.

Any reasons?

Vids

|||

Yes, you are right and in addition you should change the

Code Snippet

WHERE COUNTER = @.ROWCOUNT

to

Code Snippet

WHERE COUNTER = @.I

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Cool. thanks.

you also mentioned that the same query could be written using a cursor. What advantage will i get.

In this query we are creating a temporary table, filling data and looping through it. I expect around 5000 different combinations of state and location id in this temporary table. Will it be ok from a performance point to use the temporary table?

Cant i just use the location table as it will contain a distinct location and state combination. So instead of the temp table, i just loop through the location table and execute the proc for each combination of location and state. How will we do this using a cursor and how will it improve performance?

Vidkshi.

|||

I guess you won′t recognize the difference with 5000 rows. You can sure switch over to the locations tables.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I don't have an answer to your question but you might want to consider removing the stateid from your Theater table as it violates 3rd normal form.

Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

Callback Function in C#

Hello,
Does anyone know if a C# (or other managed language) DLL can be used as a
callback library for the Callback parameter during setup? I have experience
writing C# applications/libraries but not C++. If it can't be done in C#
then does anyone know of any good C++ templates that I could use as a basis
for creating a callback DLL?
-- Thanks, Jeff
C# would not be an appropriate language for writing a Windows callbackup
function. Actually the best language would be C since it does not mangle the
function names, but C++ will work. The Windows SDK has numerous examples of
how to write a callback function in a DLL. Visual Studio 6 has a wizard for
creating a DLL project and it would be a simple matter of including your
callback declaration and function in the DLL.
Jim
"Jeff B." <jsb@.community.nospam> wrote in message
news:%23Hj9DasJFHA.2980@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Does anyone know if a C# (or other managed language) DLL can be used as a
> callback library for the Callback parameter during setup? I have
> experience writing C# applications/libraries but not C++. If it can't be
> done in C# then does anyone know of any good C++ templates that I could
> use as a basis for creating a callback DLL?
> -- Thanks, Jeff
>

Calendar parameter input

Hi,
We have several challenges because of lack of calendar parameter input for
report parameters in Reporting Service 2000
Do we have Calendar parameter input in Reporting Services 2005?
Thank you,
Alanyes,you have datepicker.
"A.M-SG" wrote:
>
> Hi,
>
> We have several challenges because of lack of calendar parameter input for
> report parameters in Reporting Service 2000
>
> Do we have Calendar parameter input in Reporting Services 2005?
>
> Thank you,
> Alan
>
>

Calendar parameter in OLAP report

Hi,

I am trying to find the best way to use a date parameter on a OLAP report and get the calendar pop to be used instead of a dropdown.

Whenever I change from string to datetime for the parameter I (of course) get an error about different data types, since OLAP is returning the fields as strings and formatted.

Any easy (or best) approach to use to get a calendar pop to work with an OLAP report? I am mainly doing this for date range searches and do not want people to pick from huge dropdowns for dates.

Thanks.

I have made a few posts explaining how to use a calendar control with MDX, here is a link to probably the most helpful (as it has examples): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1506211&SiteID=1

Basically, you need to take the output of the calendar control, CDate() it, then use Format() to generate a string that is compatable with how you have set up your heirarchy members.

Calendar in parameter drop down

Hi all
Is it possiable to have a calendar drop down in a date parameter field? I
am not a programmer so be kind. This would be much easier for users than
typing in a date.
thanksThis, for me, was one of the more shocking revelations when I started
to use RS, but the answer is "no". It's pretty amazing to me that for
a tool that has so much promise, some very fundamental things like
this are lacking. In addition to the calendar control, another one
that we needed but couldn't use is a simple checkbox for a boolean.
And going a small step further, it is also surprising to realize that
you can't easily save the parameters a user enters so that they'll be
there for next time.
Still, hopefully these features are coming soon! :-)
Brad.
On Thu, 2 Sep 2004 10:44:39 -0700, "darwin" <dguillory @.
goodwillaz.org> wrote:
>Hi all
>Is it possiable to have a calendar drop down in a date parameter field? I
>am not a programmer so be kind. This would be much easier for users than
>typing in a date.
>thanks
>|||I think there us a work around for the problem of saving
user's input.
Write your own component which has functions that can take
in user's parameters as input and write code to save those
values in the database. Add a reference to this component
in your report and call the specific function to save
user's input.
Alternatively just write an SP which takes in these
parameters and write them into a table.
About the other points , even I would like to see those
features.
>--Original Message--
>This, for me, was one of the more shocking revelations
when I started
>to use RS, but the answer is "no". It's pretty amazing
to me that for
>a tool that has so much promise, some very fundamental
things like
>this are lacking. In addition to the calendar control,
another one
>that we needed but couldn't use is a simple checkbox for
a boolean.
>And going a small step further, it is also surprising to
realize that
>you can't easily save the parameters a user enters so
that they'll be
>there for next time.
>Still, hopefully these features are coming soon! :-)
>Brad.
>On Thu, 2 Sep 2004 10:44:39 -0700, "darwin" <dguillory @.
>goodwillaz.org> wrote:
>>Hi all
>>Is it possiable to have a calendar drop down in a date
parameter field? I
>>am not a programmer so be kind. This would be much
easier for users than
>>typing in a date.
>>thanks
>.
>

Calendar in parameter

This is probably pretty simple, but i've never done it before.
Im trying to put a Calendar up for the parameter, So they can click a date
on the calendar, and get it quiered. Is this done in the dataset? or in the
report Parameters? and what do i need to do?Make sure your parameter is a Date time parameter, and you get the Calendar
control when you run the report.
Kaisa M. Lindahl Lervik
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:064A832C-EB50-44E9-AAB6-57F4FFA87C38@.microsoft.com...
> This is probably pretty simple, but i've never done it before.
> Im trying to put a Calendar up for the parameter, So they can click a date
> on the calendar, and get it quiered. Is this done in the dataset? or in
> the
> report Parameters? and what do i need to do?

calendar format

Hi, everybody.
I have a problem with the calendar.
I have a parameter from datetime type.
But I want to change this calendar's format.
When I click the calendar button near the parameter textbox and select a date like 14.3.2001, 3.14.2001 is written to the textbox. And this causes to an error.(Because 14 can't be a month number)
How can I change that parameter's format or calendar's format?
Any idea about this?The date supplied to the calendar is based on the accept language for the browser. In IE look at Tools->Internet Options->Languages and make sure the top language is what you expect.|||

Daniel Reib wrote:

The date supplied to the calendar is based on the accept language for the browser. In IE look at Tools->Internet Options->Languages and make sure the top language is what you expect.

Thanks I did what you said but it didn't work.
Because I am not working on IE now .
I am working on Reporting Services side on .NET.
I changed regional settings from control panel too but nothing different happened.
I couldn't see the calendar's properties.
What can I do else?

Calendar control in SP2

I know that the SP2 Beta is around for testing. Does anyone know if in
this version the Calendar control is made available for use for date
parameter input ? I believe the Calendar control is the most needed
control in any kind of reporting. Comments Please.
thanks,
Anand Sagar
IN4VelocityAnand Sagar wrote:
> I know that the SP2 Beta is around for testing. Does anyone know if in
> this version the Calendar control is made available for use for date
> parameter input ? I believe the Calendar control is the most needed
> control in any kind of reporting. Comments Please.
For what?
I use a calendar-control in a normal aspx-page and open the report in my
aspx-page
regards
Frank|||It is not SP2. Look for it in the upcoming version Yukon of SQL Server and
Reporting Services.
--
| From: anandsagar@.gmail.com (Anand Sagar)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Calendar control in SP2
| Date: 27 Jan 2005 01:50:02 -0800
| Organization: http://groups.google.com
| Lines: 8
| Message-ID: <3f76a771.0501270150.17a13aea@.posting.google.com>
| NNTP-Posting-Host: 61.95.202.80
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1106819402 1467 127.0.0.1 (27 Jan 2005
09:50:02 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Thu, 27 Jan 2005 09:50:02 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08|||Oh, and I was so wanting it in SP2 :(
--
Adrian M.
MCP
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:ZHvXhpJBFHA.1092@.cpmsftngxa10.phx.gbl...
> It is not SP2. Look for it in the upcoming version Yukon of SQL Server and
> Reporting Services.
> --
> | From: anandsagar@.gmail.com (Anand Sagar)
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Subject: Calendar control in SP2
> | Date: 27 Jan 2005 01:50:02 -0800
> | Organization: http://groups.google.com
> | Lines: 8
> | Message-ID: <3f76a771.0501270150.17a13aea@.posting.google.com>
> | NNTP-Posting-Host: 61.95.202.80
> | Content-Type: text/plain; charset=ISO-8859-1
> | Content-Transfer-Encoding: 8bit
> | X-Trace: posting.google.com 1106819402 1467 127.0.0.1 (27 Jan 2005
> 09:50:02 GMT)
> | X-Complaints-To: groups-abuse@.google.com
> | NNTP-Posting-Date: Thu, 27 Jan 2005 09:50:02 +0000 (UTC)
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08
> phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news.glorb.com!postnews.goog
> le.com!not-for-mail
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:41015
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I know that the SP2 Beta is around for testing. Does anyone know if in
> | this version the Calendar control is made available for use for date
> | parameter input ? I believe the Calendar control is the most needed
> | control in any kind of reporting. Comments Please.
> |
> | thanks,
> | Anand Sagar
> | IN4Velocity
> |
>

Calendar control for date selection ?

Hello,
I have a very simple report...
SELECT AmountDue from Table where DueDate Between @.Dt1 and @.Dt2...
So in above case i will see two parameter in my report. Is there any way so
that i can select dates from calendar control in Reporting Service ?
ThxHi,
Not yet. In SQL RS 2000, the date parameters default to using a text box for
input.
In SQL RS 2005, you will be able to use date pickers for date parameters.
In the meantime, it is possible to write your own ASP-type front ends to
allow users to pick parameters. These parameters can then be passed to your
report using query string parameters. (See RS books online for details...)
"mvp" wrote:
> Hello,
> I have a very simple report...
> SELECT AmountDue from Table where DueDate Between @.Dt1 and @.Dt2...
>
> So in above case i will see two parameter in my report. Is there any way so
> that i can select dates from calendar control in Reporting Service ?
> Thx

Calculating a dataset depending on a parameter in a report

Dear all
I have a SSRS report getting data from a cube. It has 3 tables and 3
different datasets linked to these tables. One table shows all members of one
dimension. Therefore this dataset requires most of the processing time.
I want to give the user a possibility inside this report to choose if he
wants to see all the members or not. Well one easy way would be to make 2
Reports. Another way more elegant way would be to define a report parameter
which will hold a part of a mdx querry that will be used in this dataset:
- [Suppliers].[Key].[All]
- [Suppliers].[Key].members
Inside the dataset I use the STRTOSET function with this parameter.
My problem is now that the first mdx querry let the column Key disappear
inside the dataset and therefore I get a error message while displaying the
report one table is referring an inexistent field.
Do you have an idea to solve this problem or do you see another approach?
Regards,
MarcHello Marc,
My understanding is that your customer wants to have an option to show the
Key field in the Report or not. And you wants to use a parameter in the
dataset MDX query to control the dataset. If I misunderstood your concern,
please feel free to let me know.
Well, since your dataset definition have been modified by the parameter,
you will lost the Key field in the dataset. That of course will cause an
error.
One workaround is using the subreport in the report and include 2 subreport
and then use the parameter to control which one to show up.
Also, I think the drilldown feature is another workaround for your
situation.
Hiding Report Items
http://msdn2.microsoft.com/en-us/library/ms157454.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. Can you explain me your workarounds more in detail?
My problem with one dataset showing all members is that it takes too long to
execute. So I have to give the user control if he wants either more
information but wait or less information and have quick report execution.
Your first workaround using a subreport sounds fine. But I don't know how I
can give a parameter value to the subreport (no function button there).
Hiding report items will not help because the dataset will be filled even
though the report item is hidden.
How does the drilldown feature work?
Thanks for your help,
Regards,
Marc|||Hello Marc,
For the Subreport, you could use the parameter to control its visability
property.
The drilldown feature works like a toggle item.
You could accomplish this by the article I provided in the previous post.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei
Thanks for your answer. Just hiding a report items does not solve my problem
because a filling of a dataset causes a long execution time. This happens
when a report item is visible or not.
To solve this issue I think I need a way to control which mdx is used to
fill a dataset or which dataset gets executed, both depending on a parameter.
Is there a way to achieve this?
Regards, Marc|||Hello Marc,
Unfortunatly, you could not use the parameter to control which dataset the
report item use.
This is because the layout will refer the fields in the dataset. If the
fields are not match, the layout could not render correctly.
My suggestion is to use the cache to have a better performance of the
report.
Report Caching in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms155927.aspx
Also, if you are developing a ASP.NET web application, you could use the
Report Viewer Control to refer different report in your Web Page.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. I solved this problem like you said with a
parameter. This parameter determines if a report item is visible or not.
In the dataset bound to this report item I defined members which get
calculated when the table is shown or just show 0 when the report item is
hidden (case â?¦ when â?¦ thenâ?¦ end).
But I still think this is complicated to achieve and it would be nice to
have a feature that does determine if a dataset gets calculated or not.
And it would be also nice if reporting services would show the all member as
a separate column - right now it gets hidden.
Kind regards,
Marc|||Hello Marc,
Thank you for your reply.
You could send your feedback directly to the product team via the following
web site:
http://connect.microsoft.com/SQLServer
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support