Datasets that rely on parameters

Hi,
I'm trying to create a report under the following conditions:
1. I have a central database that has stored procedures that require @.db as
the parameter so you can specify where the data is coming from.
2. The report I have only wants data from a particular record, so I created
a second dataset to use in a dropdown list.
3.That second dataset uses a stored proc that requires a parameters @.id and
@.db
The problem is that the columns returned from the stored proc has the @.id
are not recognized when I try to specify them as the "value field" and the
"label field" in the Report Parameters Dialog box.
I suspect this is because it needs to know what the @.db is.
1. How do I get the second stored proc to be recognized by the Report
Parameters Dialog box?
2. How do I pass the @.db to the second stored proc without user intervention?
thanks,
-TrishIf I understand you correctly, what you are trying to do is called cascading
parameters. Search books on line for that and see if it answers you
question.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:FCF8157D-2E15-4FF7-B70D-DFB3955C9FE8@.microsoft.com...
> Hi,
> I'm trying to create a report under the following conditions:
> 1. I have a central database that has stored procedures that require @.db
> as
> the parameter so you can specify where the data is coming from.
> 2. The report I have only wants data from a particular record, so I
> created
> a second dataset to use in a dropdown list.
> 3.That second dataset uses a stored proc that requires a parameters @.id
> and
> @.db
> The problem is that the columns returned from the stored proc has the @.id
> are not recognized when I try to specify them as the "value field" and the
> "label field" in the Report Parameters Dialog box.
> I suspect this is because it needs to know what the @.db is.
> 1. How do I get the second stored proc to be recognized by the Report
> Parameters Dialog box?
> 2. How do I pass the @.db to the second stored proc without user
> intervention?
> thanks,
> -Trish

DataSet works... DataTable doesnt... (ODBC)

I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example:

'oConn is defined elsewhere...
Dim sErr as String = ""
Dim dt As New DataTable
If Not oConn Is Nothing Then
Try
Dim sSQL as String = "select 1"
Dim oCommand As New OdbcDataAdapter(sSQL, oConn)
oCommand.Fill(dt)
Catch ex As Exception
sErr = "Database Error: " & ex.Message
Finally
sqlCloseConnection(oConn)
End Try
End If

this works fine and my dt DataTable object gets one row. However using this as the SQL:

Dim sSQL as String = "declare @.foo table(mycol integer);insert @.foo select 1;select mycol from @.foo;"

does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with:

Dim ds as DataSet

I can then get the data in ds.Tables(0) no problem.

So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?

When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic.

Any ideas?

Hello my friend,

I tried your code in my application and it worked for me, but I am using the SqlDataAdapter. Why are you using Odbc? Try using the Connection, Command, DataAdapter, etc classes from the System.Data.SqlClient namespace and I think it will work.

Kind regards

Scotty

Dataset problems with Stored Procedures.

I've noticed that SP:s that are more complex than just a simple
SELECT-statement don't work correctly when creating new datasets. The dataset
can't seem to populate the Fields when the SP does some additional work
before the SELECT. I know it's possible to create all the Fields manually,
but that isn't an option for us since we have a ton of reports with lots and
lots of fields. Is there another workaround for this? Maybe in SQL Server
2005?No answers? Is there really no way to solve this? This problem could be a
real show stopper for our current project...
"Patrik Ylén" wrote:
> I've noticed that SP:s that are more complex than just a simple
> SELECT-statement don't work correctly when creating new datasets. The dataset
> can't seem to populate the Fields when the SP does some additional work
> before the SELECT. I know it's possible to create all the Fields manually,
> but that isn't an option for us since we have a ton of reports with lots and
> lots of fields. Is there another workaround for this? Maybe in SQL Server
> 2005?|||Have you tried the refresh fields button (to the right of the ...)?
I have SP that do a lot (put data into multiple temp tables etc). The very
last statement is the select. So having stuff above the select matters not
at all. Sometimes I have to click on the refresh fields.
How are you calling the SP (i.e. are you doing an exec or have you set the
command type to stored procedure).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> No answers? Is there really no way to solve this? This problem could be a
> real show stopper for our current project...
> "Patrik Ylén" wrote:
>> I've noticed that SP:s that are more complex than just a simple
>> SELECT-statement don't work correctly when creating new datasets. The
>> dataset
>> can't seem to populate the Fields when the SP does some additional work
>> before the SELECT. I know it's possible to create all the Fields
>> manually,
>> but that isn't an option for us since we have a ton of reports with lots
>> and
>> lots of fields. Is there another workaround for this? Maybe in SQL Server
>> 2005?|||Hmm, it works now. I don't know what I did when I tried it the last time, but
it must've been wrong! :-)
Thanks,
Patrik
"Bruce L-C [MVP]" wrote:
> Have you tried the refresh fields button (to the right of the ...)?
> I have SP that do a lot (put data into multiple temp tables etc). The very
> last statement is the select. So having stuff above the select matters not
> at all. Sometimes I have to click on the refresh fields.
> How are you calling the SP (i.e. are you doing an exec or have you set the
> command type to stored procedure).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> > No answers? Is there really no way to solve this? This problem could be a
> > real show stopper for our current project...
> >
> > "Patrik Ylén" wrote:
> >
> >> I've noticed that SP:s that are more complex than just a simple
> >> SELECT-statement don't work correctly when creating new datasets. The
> >> dataset
> >> can't seem to populate the Fields when the SP does some additional work
> >> before the SELECT. I know it's possible to create all the Fields
> >> manually,
> >> but that isn't an option for us since we have a ton of reports with lots
> >> and
> >> lots of fields. Is there another workaround for this? Maybe in SQL Server
> >> 2005?
>
>|||"Bruce L-C [MVP]" wrote:
> Have you tried the refresh fields button (to the right of the ...)?
> I have SP that do a lot (put data into multiple temp tables etc). The very
> last statement is the select. So having stuff above the select matters not
> at all. Sometimes I have to click on the refresh fields.
> How are you calling the SP (i.e. are you doing an exec or have you set the
> command type to stored procedure).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> > No answers? Is there really no way to solve this? This problem could be a
> > real show stopper for our current project...
> >
> > "Patrik Ylén" wrote:
> >
> >> I've noticed that SP:s that are more complex than just a simple
> >> SELECT-statement don't work correctly when creating new datasets. The
> >> dataset
> >> can't seem to populate the Fields when the SP does some additional work
> >> before the SELECT. I know it's possible to create all the Fields
> >> manually,
> >> but that isn't an option for us since we have a ton of reports with lots
> >> and
> >> lots of fields. Is there another workaround for this? Maybe in SQL Server
> >> 2005?
> HI!
>I have a related problem. My stored procedure returns a different dataset based on an entered parameter - it returns a particular set of data for display in the report detail (@.showdetail) and another set for display in the report footer (@.showfooter)
When I call the stored procedure from within reporting services (either by
exec spname @.parameter as a text entry, or by referencing it's name) I can
retrieve the 2 separate datasets fine (ie the results sets show up in the
grid). HOWEVER, changing the parameter to 'showfooter' instead of
'showdetail' does not update the associated field list. Which means I can't
drag the second dataset's fields onto my layout.
Any ideas?|||RS only supports one dataset being returned from the SP. If you have a
single stored procedure that returns multiple datasets then you either need
to modify the stored procedure to return the appropriate dataset based on a
parameter or you need to split it into two stored procedure.
Note that you can have many many datasets in your report. You just cannot
have a stored procedure return more than one dataset. And, no, this has not
changed with 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:28DDEFA3-2903-4E8B-B56E-15757E303832@.microsoft.com...
>
> "Bruce L-C [MVP]" wrote:
>> Have you tried the refresh fields button (to the right of the ...)?
>> I have SP that do a lot (put data into multiple temp tables etc). The
>> very
>> last statement is the select. So having stuff above the select matters
>> not
>> at all. Sometimes I have to click on the refresh fields.
>> How are you calling the SP (i.e. are you doing an exec or have you set
>> the
>> command type to stored procedure).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
>> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
>> > No answers? Is there really no way to solve this? This problem could be
>> > a
>> > real show stopper for our current project...
>> >
>> > "Patrik Ylén" wrote:
>> >
>> >> I've noticed that SP:s that are more complex than just a simple
>> >> SELECT-statement don't work correctly when creating new datasets. The
>> >> dataset
>> >> can't seem to populate the Fields when the SP does some additional
>> >> work
>> >> before the SELECT. I know it's possible to create all the Fields
>> >> manually,
>> >> but that isn't an option for us since we have a ton of reports with
>> >> lots
>> >> and
>> >> lots of fields. Is there another workaround for this? Maybe in SQL
>> >> Server
>> >> 2005?
>> HI!
>>I have a related problem. My stored procedure returns a different dataset
>>based on an entered parameter - it returns a particular set of data for
>>display in the report detail (@.showdetail) and another set for display in
>>the report footer (@.showfooter)
> When I call the stored procedure from within reporting services (either by
> exec spname @.parameter as a text entry, or by referencing it's name) I can
> retrieve the 2 separate datasets fine (ie the results sets show up in the
> grid). HOWEVER, changing the parameter to 'showfooter' instead of
> 'showdetail' does not update the associated field list. Which means I
> can't
> drag the second dataset's fields onto my layout.
> Any ideas?|||Thanks Bruce
My stored procedure is actually coded to return an appropriate dataset based
on a parameter which is supplied when calling the sp in RS. It returns the
appropriate dataset to the results grid fine in RS using the supplier
parameters. The fields returned in both datasets are completely different.
The problem is that the field list is not updated when I change the
parameter, it remains the list associated with the first parameter supplied
even if click 'refresh fields' after changing the parameter
I need my report to use the two separate datasets without having to re-write
the sp which is pages long.
Is this related to the fact that you can't have a sp return more than one
dataset in rs'|||Logically, you are returning two datasets. Depending on the parameter you
get a different field list. What you need to do is call the SP two times in
design mode with different parameters to get the different field list. Then
design your form with these and hide the one you don't want based on the
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F34F84BB-226F-4A34-A0ED-551137DCA065@.microsoft.com...
> Thanks Bruce
> My stored procedure is actually coded to return an appropriate dataset
> based
> on a parameter which is supplied when calling the sp in RS. It returns
> the
> appropriate dataset to the results grid fine in RS using the supplier
> parameters. The fields returned in both datasets are completely different.
> The problem is that the field list is not updated when I change the
> parameter, it remains the list associated with the first parameter
> supplied
> even if click 'refresh fields' after changing the parameter
> I need my report to use the two separate datasets without having to
> re-write
> the sp which is pages long.
> Is this related to the fact that you can't have a sp return more than one
> dataset in rs'sql

DataSet - Inserted row ID

I have a dataset that uses generated stored procedures to do its select, insert, update, delete.

I am inserting a row to that dataset, and after the update, using the ID of newly created row.

This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0)

Any idea what happened / what I have to do to fix this?

Thnx!

Hi,

The SQL Server uses SELECT SCOPE_IDENTITY() to get the last ID of the table. I'd like to know if you're using this to update your data.

Also, could you let me know what is the newly added trigger doing. It might be preventing the scope identity from returning.

Dataset - Command type question

Hi to all
I need to use a set of stored procedures as source of my report datasets, my
problem is that I have several reports already created some of them using
StoredProcedure as command type, and others using Text and using "EXEC
MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
the same way, change one of the sets of reports can cost some resources of
my team.
My question is if no exist differences or I can have problems with this
second type of datasets?
ThanksFor "SQL Server" connections they are for all practical purposes identical.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:#XmzhXedEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> I need to use a set of stored procedures as source of my report datasets, my
> problem is that I have several reports already created some of them using
> StoredProcedure as command type, and others using Text and using "EXEC
> MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
> the same way, change one of the sets of reports can cost some resources of
> my team.
> My question is if no exist differences or I can have problems with this
> second type of datasets?
> Thanks
>sql

Databse role to Create Stored Procedures

Hi,
With SQL2005, I'd like to create a database role whose members could
Create/Drop stored procedures (even new ones), Select/Insert/Delete/Update
from any table of the Database, but couldn't modifiy the tables' structure.
Could someone suggest me a script?
Thanks for your help.
JN.Hi,
Assign below roles to user to read and write on tables
DB_DATAREADER
DB_DATAWRITER
This will allow the user to create procedure
GRANT CREATE PROCEDURE TO <UserName>
The createor can drop the procedure
Thanks
Hari
SQL Server MVP
"Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote in
message news:203A9A00-5976-4B45-82FE-B9E31B32AEDB@.microsoft.com...
> Hi,
> With SQL2005, I'd like to create a database role whose members could
> Create/Drop stored procedures (even new ones), Select/Insert/Delete/Update
> from any table of the Database, but couldn't modifiy the tables'
> structure.
> Could someone suggest me a script?
> Thanks for your help.
> JN.

Database versioning SQL Server 2000

Where can we find a Database Version Control Tool for all the database objects, not only for stored procedures like Visual SourceSafe.I am not sure exactly what you are after but if you want to be able to capture scripts of all objects in a database into sourcesafe then try:

http://www.sqlservercentral.com/products/bwunder/archiveutility/

for a neat little freeware utility.

P.S. You may need to register on sqlservercentral.com|||There is SQL Source Control 2003 http://www.skilledsoftware.com/sqlsourcecontrol.htm and Embarcadero Change Manager. The first uses integration with MS Visual Source Safe, the second stores scripted versions locally or on the server. I never worked with any of them though.|||check out these links, may be they'll be helpful.

http://www.nigelrivett.net/DMOScriptAllDatabases.html
http://www.nigelrivett.net/SQLServerReleaseControl.htm

Database Tuning Advisor

Hi All,

Can anyone tell me if it makes any difference in Database Tuning advisor when is used against stored procedures workload and simple batch statements workload.

I used it against stored procedures first and got no recommendations for it, and when I used the statements of same stored procedures outside the procedure and used the worload against the DTA, I got some recommendations.

Also, in the reports section of DTA, the Database Access Report shows no database details, whereas in the other case, I got all the details for database, tables and columns accessed.

When you use DTA with a given workload of SQL queries and updates, DTA recommends an appropriate physical design, and generates a script to implement the recommended physical design.

There is no direct connection between VSTS for DB Pro and DTA. After you use DTA you can change your DB project and DB objects as the DTA recommended.

You can read great article about DTA and get more information from: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQL2005DTA.doc

Maor

|||

DTA does not get inside my database for the stored procedures where there's a 'if' condition inside the SP.

For example,

I created a stored procedure as,

createproc testSP2

@.Group_ID_id int

as

begin

select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from

PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)

end

GO

exec testSP2

GO

The Database, Table, column Access reports shows the details of database,tables and columns accessed in the DTA and a set of recommendations are recieved.

But when the same procedure is modifies as,

create proc testSP2

@.Group_id int

as

begin

if(1=1)

begin

select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from

PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)

end

end

GO

exec testSP2

GO

The Database, Table, column Access reports don't give any details and no recommendations are made.