How to exec stored proc dynamically

Hello
I have 2 procedures setup in master database, sp_RebuildIndexesMain and
sp_RebuildIndexesSub

The Sub just shows and execute DBCC commands for passed database
context

sp_RebuildIndexesSub(@.listOnly bit=0, @.maxfrag Decimal=30.0)

This runs fine if I do pubs..sp_RebuildIndexesSub
However when run thru. the Main proc, I get Incorrect syntax near
'pubs'.
The main proc is

Create Proc sp_RebuildIndexesMain(@.dbName sysname, @.listOnly bit=0,
@.maxFrag Decimal=30.0)
As
Begin
Set NOCOUNT ON

Declare crDbs CURSOR For
Select CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATA
Where CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model',
'distribution', 'Northwind', 'pubs')
And CATALOG_NAME Like @.dbName

Declare @.execstr nvarchar(2000)

Open crDbs
Fetch crDbs INTO @.dbName
If (@.@.FETCH_STATUS<>0) --Then no matching databases
Begin
Close crDbs
Deallocate CrDbs
Print 'No databases were found that match ''' + @.dbName + ''''
Return -1
End

While(@.@.FETCH_STATUS=0)
Begin
Print Char(13) + 'Rebuilding indexes on ' + @.dbName
Print Char(13)
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
EXEC sp_executesql @.execstr, N'@.listOnly bit, @.maxFrag Decimal',
@.listOnly, @.maxFrag
Fetch crDbs INTO @.dbName
End
Close crDbs
Deallocate CrDbs
Return 0
End

thanks
Sunit
sunitjoshi@.netzero.comI believe if you change:
Set @.execstr = @.dbName + '..sp_RebuildIndexesSub '
to
Set @.execstr = '[' + @.dbName + '..sp_RebuildIndexesSub] '

it should work.

Personally, instead of creating sp_RebuildIndexesSub in each database,
you should just create it in the master database. Then run a job like
so:

sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
Print Char(13) + 'Rebuilding indexes on ' + ?
exec sp_RebuildIndexesSub 0, 30.0
END'

Be sure not to run "exec master..sp_RebuildIndexesSub 0, 30.0" or else
it will only run the master database during each loop.

Modify to your heart's content.|||Now it says
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SPlant5_MODEL..sp_RebuildIndexesSub'.

The stored procedure are setup in the master db. That's why I'm using
the dbname..spname to change db context.

thanks
Sunit

*** Sent via Developersdex http://www.developersdex.com ***|||Don't use sp_executesql. The problem stems from you trying to run a
stored procedure through a stored procedure. So instead, build your
string first and run it by using EXEC(@.execstr).

SET @.execstr = 'USE ' + @.dbname + ' exec sp_RebuildIndexesSub ' +
RTRIM(@.listOnly) + ',' + RTRIM(@.maxFrag)
EXEC (@.execstr)|||Got it. Had to change to this

Set @.execstr = @.dbName + '..sp_RebuildIndexesSub'
Exec @.execstr @.listOnly, @.maxFrag

thanks
Sunit|||You are right. Your code is much cleaner :)

How To Dynamically Switch Between Databases In Report?

I have a situation where I have about 5 different customers. Eachcustomer's database is structurally the same, though the actual datawill vary. Each customer will run exactly the same reports as othercustomers. What I'm wondering is... is there a way to share reportsbetween these companies, but switch the data sources dynamically (fromthe web app)? From what I understand I can only have one data sourceconnection. By the way I'm using SQL Server 2000.
I'm pretty new to this RS stuff.
Thanks,
csdietrich
There may be a way to switch the datasource dynamicaly but I don't know how...
You can however have your report hit a stored proc which can in turn hit the correct database depending on the parameters passed to it.

How To Dynamically Switch Between Databases In Report

Let me preface this by stating that I am a Reporting Services newbie...
I need to design a report that displays financial data for 2 companies,
Company A and Company B. The problem is that each company has their own
distinct database. The schema is identical between them, just different data
in each.
The report is identical for each company, the only different is that the
report needs to pull from Database A to display the report data for Company
A, and alternatively pull from Database B to display the report data for
Company B. The user wants to be able to specify whether they want to run the
report for Company A, or Company B.
How can I design the report or setup the data source to switch databases
depending on if the user wants to see the report for Company A or Company B?
Can this be done with a report parameter? If so, how?
I really don't want to create and maintain 2 identical reports, the only
difference being the data source.
One possible option I guess would be to create a view that combines data
from identical tables in both databases, and use that view as the datasource
in the report.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!Here is one way. It is an interesting technique in that it uses the ability
to run a batch of SQL statement. Use the generic query designer. Then paste
in the following (as an example).
declare @.SQL varchar(255)
select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
= ''U'' order by name'
exec (@.SQL)
You can also use an expression but one thing that is nice about the above
method is it will still fill in the field names (sometimes you have to click
on the refresh fields button but it all works). In your case you would have
the @.Database parameter be based on a list they choose from CompanyA,
CompanyB where the value for the selection would be the database name.
To do what you want will require a little more messing around. You could
first develop against one database to make sure you have the query correct
and then change it to be dynamic.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> Let me preface this by stating that I am a Reporting Services newbie...
> I need to design a report that displays financial data for 2 companies,
> Company A and Company B. The problem is that each company has their own
> distinct database. The schema is identical between them, just different
data
> in each.
> The report is identical for each company, the only different is that the
> report needs to pull from Database A to display the report data for
Company
> A, and alternatively pull from Database B to display the report data for
> Company B. The user wants to be able to specify whether they want to run
the
> report for Company A, or Company B.
> How can I design the report or setup the data source to switch databases
> depending on if the user wants to see the report for Company A or Company
B?
> Can this be done with a report parameter? If so, how?
> I really don't want to create and maintain 2 identical reports, the only
> difference being the data source.
> One possible option I guess would be to create a view that combines data
> from identical tables in both databases, and use that view as the
datasource
> in the report.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!|||Thanks Bruce... Looks like 1 of many possible approaches to this problem.
I just found out that it is likely that the customer will be adding more
companies, hence addtional databases that the report needs to be run against.
I guess I need to go figure out how to setup and pass parameters to the
report to allow the end-user to specify the "root" database name of the
company at runtime.
"Bruce L-C [MVP]" wrote:
> Here is one way. It is an interesting technique in that it uses the ability
> to run a batch of SQL statement. Use the generic query designer. Then paste
> in the following (as an example).
> declare @.SQL varchar(255)
> select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
> = ''U'' order by name'
> exec (@.SQL)
> You can also use an expression but one thing that is nice about the above
> method is it will still fill in the field names (sometimes you have to click
> on the refresh fields button but it all works). In your case you would have
> the @.Database parameter be based on a list they choose from CompanyA,
> CompanyB where the value for the selection would be the database name.
> To do what you want will require a little more messing around. You could
> first develop against one database to make sure you have the query correct
> and then change it to be dynamic.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > Let me preface this by stating that I am a Reporting Services newbie...
> >
> > I need to design a report that displays financial data for 2 companies,
> > Company A and Company B. The problem is that each company has their own
> > distinct database. The schema is identical between them, just different
> data
> > in each.
> >
> > The report is identical for each company, the only different is that the
> > report needs to pull from Database A to display the report data for
> Company
> > A, and alternatively pull from Database B to display the report data for
> > Company B. The user wants to be able to specify whether they want to run
> the
> > report for Company A, or Company B.
> >
> > How can I design the report or setup the data source to switch databases
> > depending on if the user wants to see the report for Company A or Company
> B?
> > Can this be done with a report parameter? If so, how?
> >
> > I really don't want to create and maintain 2 identical reports, the only
> > difference being the data source.
> >
> > One possible option I guess would be to create a view that combines data
> > from identical tables in both databases, and use that view as the
> datasource
> > in the report.
> >
> > Just looking for what others have done in similar circumstances so I don't
> > spend multiple days architecting the wrong approach.
> >
> > Thanks!
>
>|||Hi,
What i'm currently doing is creating a reporting database that will include
all my reports stored procedure and have those stored procedure accessing my
OLTP data through a linked server. This way, if i need to install my
reporting solution at a new customer site, I only need to modify my linked
server parameters.
Hope this helps,
Eric
"Smit-Dog" wrote:
> Thanks Bruce... Looks like 1 of many possible approaches to this problem.
> I just found out that it is likely that the customer will be adding more
> companies, hence addtional databases that the report needs to be run against.
> I guess I need to go figure out how to setup and pass parameters to the
> report to allow the end-user to specify the "root" database name of the
> company at runtime.
> "Bruce L-C [MVP]" wrote:
> > Here is one way. It is an interesting technique in that it uses the ability
> > to run a batch of SQL statement. Use the generic query designer. Then paste
> > in the following (as an example).
> > declare @.SQL varchar(255)
> > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where xtype
> > = ''U'' order by name'
> > exec (@.SQL)
> >
> > You can also use an expression but one thing that is nice about the above
> > method is it will still fill in the field names (sometimes you have to click
> > on the refresh fields button but it all works). In your case you would have
> > the @.Database parameter be based on a list they choose from CompanyA,
> > CompanyB where the value for the selection would be the database name.
> >
> > To do what you want will require a little more messing around. You could
> > first develop against one database to make sure you have the query correct
> > and then change it to be dynamic.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > Let me preface this by stating that I am a Reporting Services newbie...
> > >
> > > I need to design a report that displays financial data for 2 companies,
> > > Company A and Company B. The problem is that each company has their own
> > > distinct database. The schema is identical between them, just different
> > data
> > > in each.
> > >
> > > The report is identical for each company, the only different is that the
> > > report needs to pull from Database A to display the report data for
> > Company
> > > A, and alternatively pull from Database B to display the report data for
> > > Company B. The user wants to be able to specify whether they want to run
> > the
> > > report for Company A, or Company B.
> > >
> > > How can I design the report or setup the data source to switch databases
> > > depending on if the user wants to see the report for Company A or Company
> > B?
> > > Can this be done with a report parameter? If so, how?
> > >
> > > I really don't want to create and maintain 2 identical reports, the only
> > > difference being the data source.
> > >
> > > One possible option I guess would be to create a view that combines data
> > > from identical tables in both databases, and use that view as the
> > datasource
> > > in the report.
> > >
> > > Just looking for what others have done in similar circumstances so I don't
> > > spend multiple days architecting the wrong approach.
> > >
> > > Thanks!
> >
> >
> >|||In this case he has two databases that the customer wants to pick which one
to report off of.
Also, I would be very very careful with linked servers, especially if you
are using the four part naming. You could easily get burned. It takes very
little for SQL Server to decide to bring the whole table over and process it
locally. It is not doing a passthrough query. It seems like it would just
send the SQL to the remote server for processing but that is not what
happens with 4 part naming. This is what happens with OpenQuery but if you
are using 4 part naming then you could easily find yourself with a major
performance headache when you roll out to production.
Just a heads up on the dangers of linked servers.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
news:2C43C2F9-BC10-4951-87FD-428CDF9D1D69@.microsoft.com...
> Hi,
> What i'm currently doing is creating a reporting database that will
include
> all my reports stored procedure and have those stored procedure accessing
my
> OLTP data through a linked server. This way, if i need to install my
> reporting solution at a new customer site, I only need to modify my linked
> server parameters.
> Hope this helps,
> Eric
> "Smit-Dog" wrote:
> > Thanks Bruce... Looks like 1 of many possible approaches to this
problem.
> >
> > I just found out that it is likely that the customer will be adding more
> > companies, hence addtional databases that the report needs to be run
against.
> >
> > I guess I need to go figure out how to setup and pass parameters to the
> > report to allow the end-user to specify the "root" database name of the
> > company at runtime.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Here is one way. It is an interesting technique in that it uses the
ability
> > > to run a batch of SQL statement. Use the generic query designer. Then
paste
> > > in the following (as an example).
> > > declare @.SQL varchar(255)
> > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
xtype
> > > = ''U'' order by name'
> > > exec (@.SQL)
> > >
> > > You can also use an expression but one thing that is nice about the
above
> > > method is it will still fill in the field names (sometimes you have to
click
> > > on the refresh fields button but it all works). In your case you would
have
> > > the @.Database parameter be based on a list they choose from CompanyA,
> > > CompanyB where the value for the selection would be the database name.
> > >
> > > To do what you want will require a little more messing around. You
could
> > > first develop against one database to make sure you have the query
correct
> > > and then change it to be dynamic.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > > Let me preface this by stating that I am a Reporting Services
newbie...
> > > >
> > > > I need to design a report that displays financial data for 2
companies,
> > > > Company A and Company B. The problem is that each company has their
own
> > > > distinct database. The schema is identical between them, just
different
> > > data
> > > > in each.
> > > >
> > > > The report is identical for each company, the only different is that
the
> > > > report needs to pull from Database A to display the report data for
> > > Company
> > > > A, and alternatively pull from Database B to display the report data
for
> > > > Company B. The user wants to be able to specify whether they want to
run
> > > the
> > > > report for Company A, or Company B.
> > > >
> > > > How can I design the report or setup the data source to switch
databases
> > > > depending on if the user wants to see the report for Company A or
Company
> > > B?
> > > > Can this be done with a report parameter? If so, how?
> > > >
> > > > I really don't want to create and maintain 2 identical reports, the
only
> > > > difference being the data source.
> > > >
> > > > One possible option I guess would be to create a view that combines
data
> > > > from identical tables in both databases, and use that view as the
> > > datasource
> > > > in the report.
> > > >
> > > > Just looking for what others have done in similar circumstances so I
don't
> > > > spend multiple days architecting the wrong approach.
> > > >
> > > > Thanks!
> > >
> > >
> > >|||Hi Bruce,
Thanks for the heads up.
I'm actually using OpenQuery since it looks like the only way to abstract
the database name when using a linked server.
Is there a better way to do this ?
Thanks,
Eric
"Bruce L-C [MVP]" wrote:
> In this case he has two databases that the customer wants to pick which one
> to report off of.
> Also, I would be very very careful with linked servers, especially if you
> are using the four part naming. You could easily get burned. It takes very
> little for SQL Server to decide to bring the whole table over and process it
> locally. It is not doing a passthrough query. It seems like it would just
> send the SQL to the remote server for processing but that is not what
> happens with 4 part naming. This is what happens with OpenQuery but if you
> are using 4 part naming then you could easily find yourself with a major
> performance headache when you roll out to production.
> Just a heads up on the dangers of linked servers.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
> news:2C43C2F9-BC10-4951-87FD-428CDF9D1D69@.microsoft.com...
> > Hi,
> >
> > What i'm currently doing is creating a reporting database that will
> include
> > all my reports stored procedure and have those stored procedure accessing
> my
> > OLTP data through a linked server. This way, if i need to install my
> > reporting solution at a new customer site, I only need to modify my linked
> > server parameters.
> >
> > Hope this helps,
> > Eric
> >
> > "Smit-Dog" wrote:
> >
> > > Thanks Bruce... Looks like 1 of many possible approaches to this
> problem.
> > >
> > > I just found out that it is likely that the customer will be adding more
> > > companies, hence addtional databases that the report needs to be run
> against.
> > >
> > > I guess I need to go figure out how to setup and pass parameters to the
> > > report to allow the end-user to specify the "root" database name of the
> > > company at runtime.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Here is one way. It is an interesting technique in that it uses the
> ability
> > > > to run a batch of SQL statement. Use the generic query designer. Then
> paste
> > > > in the following (as an example).
> > > > declare @.SQL varchar(255)
> > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects where
> xtype
> > > > = ''U'' order by name'
> > > > exec (@.SQL)
> > > >
> > > > You can also use an expression but one thing that is nice about the
> above
> > > > method is it will still fill in the field names (sometimes you have to
> click
> > > > on the refresh fields button but it all works). In your case you would
> have
> > > > the @.Database parameter be based on a list they choose from CompanyA,
> > > > CompanyB where the value for the selection would be the database name.
> > > >
> > > > To do what you want will require a little more messing around. You
> could
> > > > first develop against one database to make sure you have the query
> correct
> > > > and then change it to be dynamic.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > > > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > > > Let me preface this by stating that I am a Reporting Services
> newbie...
> > > > >
> > > > > I need to design a report that displays financial data for 2
> companies,
> > > > > Company A and Company B. The problem is that each company has their
> own
> > > > > distinct database. The schema is identical between them, just
> different
> > > > data
> > > > > in each.
> > > > >
> > > > > The report is identical for each company, the only different is that
> the
> > > > > report needs to pull from Database A to display the report data for
> > > > Company
> > > > > A, and alternatively pull from Database B to display the report data
> for
> > > > > Company B. The user wants to be able to specify whether they want to
> run
> > > > the
> > > > > report for Company A, or Company B.
> > > > >
> > > > > How can I design the report or setup the data source to switch
> databases
> > > > > depending on if the user wants to see the report for Company A or
> Company
> > > > B?
> > > > > Can this be done with a report parameter? If so, how?
> > > > >
> > > > > I really don't want to create and maintain 2 identical reports, the
> only
> > > > > difference being the data source.
> > > > >
> > > > > One possible option I guess would be to create a view that combines
> data
> > > > > from identical tables in both databases, and use that view as the
> > > > datasource
> > > > > in the report.
> > > > >
> > > > > Just looking for what others have done in similar circumstances so I
> don't
> > > > > spend multiple days architecting the wrong approach.
> > > > >
> > > > > Thanks!
> > > >
> > > >
> > > >
>
>|||declare @.SQL varchar(255)
select @.SQL = 'select max(somefield) from ' + @.Server +
'.database.dbo.tablename'
exec (@.SQL)
Four part naming works but it is dangerous. You are better off to use
OpenQuery as you are. Yukon handles 4 part naming better than it does now.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
news:D906328F-8E54-425D-8E29-7C3636EEFDFD@.microsoft.com...
> Hi Bruce,
> Thanks for the heads up.
> I'm actually using OpenQuery since it looks like the only way to abstract
> the database name when using a linked server.
> Is there a better way to do this ?
> Thanks,
> Eric
> "Bruce L-C [MVP]" wrote:
> > In this case he has two databases that the customer wants to pick which
one
> > to report off of.
> >
> > Also, I would be very very careful with linked servers, especially if
you
> > are using the four part naming. You could easily get burned. It takes
very
> > little for SQL Server to decide to bring the whole table over and
process it
> > locally. It is not doing a passthrough query. It seems like it would
just
> > send the SQL to the remote server for processing but that is not what
> > happens with 4 part naming. This is what happens with OpenQuery but if
you
> > are using 4 part naming then you could easily find yourself with a major
> > performance headache when you roll out to production.
> >
> > Just a heads up on the dangers of linked servers.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Aiwa" <Aiwa@.discussions.microsoft.com> wrote in message
> > news:2C43C2F9-BC10-4951-87FD-428CDF9D1D69@.microsoft.com...
> > > Hi,
> > >
> > > What i'm currently doing is creating a reporting database that will
> > include
> > > all my reports stored procedure and have those stored procedure
accessing
> > my
> > > OLTP data through a linked server. This way, if i need to install my
> > > reporting solution at a new customer site, I only need to modify my
linked
> > > server parameters.
> > >
> > > Hope this helps,
> > > Eric
> > >
> > > "Smit-Dog" wrote:
> > >
> > > > Thanks Bruce... Looks like 1 of many possible approaches to this
> > problem.
> > > >
> > > > I just found out that it is likely that the customer will be adding
more
> > > > companies, hence addtional databases that the report needs to be run
> > against.
> > > >
> > > > I guess I need to go figure out how to setup and pass parameters to
the
> > > > report to allow the end-user to specify the "root" database name of
the
> > > > company at runtime.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Here is one way. It is an interesting technique in that it uses
the
> > ability
> > > > > to run a batch of SQL statement. Use the generic query designer.
Then
> > paste
> > > > > in the following (as an example).
> > > > > declare @.SQL varchar(255)
> > > > > select @.SQL = 'select name from ' + @.Database + '.dbo.sysobjects
where
> > xtype
> > > > > = ''U'' order by name'
> > > > > exec (@.SQL)
> > > > >
> > > > > You can also use an expression but one thing that is nice about
the
> > above
> > > > > method is it will still fill in the field names (sometimes you
have to
> > click
> > > > > on the refresh fields button but it all works). In your case you
would
> > have
> > > > > the @.Database parameter be based on a list they choose from
CompanyA,
> > > > > CompanyB where the value for the selection would be the database
name.
> > > > >
> > > > > To do what you want will require a little more messing around. You
> > could
> > > > > first develop against one database to make sure you have the query
> > correct
> > > > > and then change it to be dynamic.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > > "Smit-Dog" <SmitDog@.discussions.microsoft.com> wrote in message
> > > > > news:43D571CF-ACF4-4BCE-B482-9F49E5817E8C@.microsoft.com...
> > > > > > Let me preface this by stating that I am a Reporting Services
> > newbie...
> > > > > >
> > > > > > I need to design a report that displays financial data for 2
> > companies,
> > > > > > Company A and Company B. The problem is that each company has
their
> > own
> > > > > > distinct database. The schema is identical between them, just
> > different
> > > > > data
> > > > > > in each.
> > > > > >
> > > > > > The report is identical for each company, the only different is
that
> > the
> > > > > > report needs to pull from Database A to display the report data
for
> > > > > Company
> > > > > > A, and alternatively pull from Database B to display the report
data
> > for
> > > > > > Company B. The user wants to be able to specify whether they
want to
> > run
> > > > > the
> > > > > > report for Company A, or Company B.
> > > > > >
> > > > > > How can I design the report or setup the data source to switch
> > databases
> > > > > > depending on if the user wants to see the report for Company A
or
> > Company
> > > > > B?
> > > > > > Can this be done with a report parameter? If so, how?
> > > > > >
> > > > > > I really don't want to create and maintain 2 identical reports,
the
> > only
> > > > > > difference being the data source.
> > > > > >
> > > > > > One possible option I guess would be to create a view that
combines
> > data
> > > > > > from identical tables in both databases, and use that view as
the
> > > > > datasource
> > > > > > in the report.
> > > > > >
> > > > > > Just looking for what others have done in similar circumstances
so I
> > don't
> > > > > > spend multiple days architecting the wrong approach.
> > > > > >
> > > > > > Thanks!
> > > > >
> > > > >
> > > > >
> >
> >
> >

How to Dynamically set the width of report body.

I have a report where we display a certain number of columns based on
some condition. If I display all the columns then the report looks okay
but if I display fewer columns then there is empty space on each row
which would have otherwise been occupied by the hidden columns of the
table. Is there any way to dynamically set the width of the table and
the report body.
Thanks and I appreciate you taking the time.
S Girase
sgirase@.gmail.comyou could try building the report with a matrix.. it'll adjust the
number of columns based on the results.

How to dynamically set the ServerPassword

I have an FTP Task where I have to set the server password at run-time. I have the ftp connection manager set up. What I normally do is configure the ftp server connection manager using a dtsConfig file but I can't do that in this case. I won't know the serverpassword until I get a parameter from the user.

That one property as you've seen can't be set through a connection expression. You could do it thorugh a script task if you wanted to do it inside the package. You could also try to set it through dtexec.exe if you're executing the package that way by using the /SET switch.

Just to elaborate on the /SET switch, here's some example syntax:

DTExec /FILE Package.dtsx /SET \Package.Connections[ConnectionID].ServerPassword;PasswordHere

Brian Knight

|||

Thanks Brian.

Yes, I do want to do it in the package because the package is being launched via dtexec from a generic web app that creates textboxes for the parameters. I would like to keep the web app generic and free of package-specific code. I created a script task and I set the password by getting the ServerPassword property from the ConnectionManager's properties collection and then using the SetValue method of the DtsProperty object.

How to dynamically set the AllMemberName property?

Hello

Is it possible to dynamically set the AllMemberName resp. the AttributeAllMemberName property? Or isn't there a way to bind this property to a table column or define an expression for that property?

Kind regards

Any ideas anyone? Or is this functionality just not implemented?

Kind regards

How To Dynamically Pull Stored Procedure Arguments

I'm sure there is a way using the system tables or maybe on the stored
procedures of the master db to pull the arguments for a given stored
procedure, and their datatypes.
So, for a stored proc like this:
CREATE StoredProc1
OrderNumber AS BIGINT,
OrderName AS VARCHAR(50)
AS ...
I would, using this magical query I am hoping exists, get back:
OrderNumber, BIGINT, 4
OrderName, VARCHAR, 50
or something in that order. Anyone have any ideas?Try,
use northwind
go
exec sp_procedure_params_rowset 'SalesByCategory'
go
AMB
"David Samson" wrote:

> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||From MS SQL Books On Line, you'll read about querying INFORMATION_SCHEMA.PAR
AMETERS:
"Contains one row for each parameter of a user-defined function or stored pr
ocedure accessible to the current user in the current
database. For functions, this view also returns one row with return value in
formation.
The INFORMATION_SCHEMA.PARAMETERS view is based on the sysobjects and syscol
umns system tables.
To retrieve information from these views, specify the fully qualified name o
f INFORMATION_SCHEMA view_name."
"David Samson" <CaptainSlock@.nospam.nospam> wrote in message news:54296B5C-F33F-426A-95A3-7
7366FF14200@.microsoft.com...
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?|||David Samson (CaptainSlock@.nospam.nospam) writes:
> I'm sure there is a way using the system tables or maybe on the stored
> procedures of the master db to pull the arguments for a given stored
> procedure, and their datatypes.
> So, for a stored proc like this:
> CREATE StoredProc1
> OrderNumber AS BIGINT,
> OrderName AS VARCHAR(50)
> AS ...
> I would, using this magical query I am hoping exists, get back:
> OrderNumber, BIGINT, 4
> OrderName, VARCHAR, 50
> or something in that order. Anyone have any ideas?
SELECT c.name, t.name, c.length, c.precision, c.scale
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.name = @.name
ORDER BY c.colid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi There,
Why not simply try
sp_help 'procedure name'
OR
Select the name of Procedure and press (Alt+F1) If your QA is
customized for that
With warm regards
Jatinder Singh

How to dynamically pull data for the past month?

I have a query that I want to schedule as a DTS package and have it run on
the first of every month to pull data for the previous month. How can I set
the SQL statement to determine what the last month was and use that for the
query parameters?
Thanks in advance for your help!
Isaac WeathersThe last day of the previous month is
select dateadd(dd, -(datepart(dd,getdate()) ), getdate())
I didn't test this but it gets the current day of the month (say the
12th, ) , then subtracts that many days from the current date, leaving you
at the last day of the prior month...You can then take that date and
subtract the the day -1 from that date, giving you the first day of the
month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I
> set
> the SQL statement to determine what the last month was and use that for
> the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>|||'====yyyymm format
if len(Month(DateAdd("M", -1, Date()))) = 1 then
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) & "0" &
Month(DateAdd("M", -1, Date()))
Else
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) &
Month(DateAdd("M", -1, Date()))
End If
'=====mm/dd/yyy format
DateString = Month(DateAdd("M", -1, Date()) ) & "/01/" &
DatePart("YYYY", DateAdd("M", -1, Date()))|||This will do the trick:
DateAdd("m",-1,
CAST(CONVERT(nvarchar(2), Month(GetDate()))+ '/1/' +
CONVERT(nvarchar(4), Year(GetDate()))
AS SmallDateTime))
AS FirstDayOfLastMonth,
CAST(CONVERT(nvarchar(12), GetDate() - Day(GetDate())) AS SmallDateTime)
AS LastDayOfLastMonth,
GeoSynch
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I set
> the SQL statement to determine what the last month was and use that for the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>

How to dynamically process a Model in a Web App

Hi,

I am a novice at Data Mining realm on SQL Server.

Scenario:

I have created a Time Series model and deployed it into SQL Server. I hope users can see forecast based on the up-to-date data residing in data source rather than the old ones used to train the model. In a addition, the interface provided for users is a .aspx.

Problems:

What ADO APIs should I exploit to dynamically process the model, perform the forecast and retrieve the results.

Any help would be appreciated.

Best Wishes,

Ricky.

You would likely use ADOMD.NET, not ADO.NEt, but the results would essentially be the same. You would likely want to create models on the fly for this solution, much the same way we do for the Excel addins. You can download the addins and use the trace mechanism to see what commands we send to the server.

Essentially, you want to use CREATE SESSION MINING MODEL, INSERT INTO (you can use an input rowset, or an openquery if your data is in a database), and then SELECT PredictTimeSeries(...) to get the forecast.

Using a session model will cause the model to automatically be deleted on disconnection. Note that you will have to turn on the server property to allow session models.

|||

Thanks a lot, Jamie.

Could you give a tutorial or exmaple code to see the deatils?

Regards,

Ricky.

|||The sample here (http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/1866.aspx) creates and trains models dynamically|||

Many Thanks, Jamie

Regards,

Ricky.

|||

mr jamie...

this link seems to be dead

please send in the link again

|||

http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/1866.aspx

|||

hi jamie..

could u look into this thread plz...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1449395&SiteID=1

;m stuck at thiz prb...

i've reinstalled the dm viewer controls.......

now here is wat i read in the readme file as to how to use the dm viewer controls:

In the Winform designer, right click on the Toolbox and select 'Choose Items...' menu item. Hit the Brows button and select file 'Microsoft.AnalysisServices.Viewers.dll'. Hit the OK button to add all the viewer controls to your toolbox.

the prob is tat i can't find this file:'Microsoft.AnalysisServices.Viewers.dll'

do u think i've gone wrong somewhr in the installation of the viewer controls?

or is there somethin else that i must do?

How to dynamically map the coloumn to a flat file destination?

Hi All,

I am struck at one point. I am trying to this operation and not able to go further.

1. I have got the dataset to a variable in the control flow.

2. I am looping through the dataset based on a coloumn.

3. Now inside my For each loop i have a dataflow task.

4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @.othervariable.

Now my question is

Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.

Thanks in advance..

Regards,

Dev.

Metadata cannot change. Will it change with your dynamic query?|||

hi phil,

yes every query produces a different result. pls. let me know the best way to handle this kind of scenario. I have total 30 different result sets and need to generate 30 different flat files based on the different out out.

Thanx in advance..

Dev

|||

Dev,

If the datasets has the same structure (column names, data types); then it is possible to do it using SSIS.

I have 2 posts in my blog that explains how to loop through a a data set and how write into different Excel files. You could modified to write into different flat files if you need.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

|||

Rafeal,

Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...

thanx in advance..

dev

|||

dev15`4534345677 wrote:

Rafeal,

Unfortunately the datasets are not same. they are different in each case. I am not bale to figure out a best solution for this. Pls let me know if u have any ideas onthis...

thanx in advance..

dev

You're going to have to setup a data flow for each format.|||

I completly agree with your approch as that was my last option. Do u think this is the only one i have.....

Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!

Thanx in advance..

Regards,

Dev

|||

dev15`4534345677 wrote:

I completly agree with your approch as that was my last option. Do u think this is the only one i have.....

Can't i do any thing dynamically as u can imagine doing 30 different data flow for 30 times !!!!

Thanx in advance..

Regards,

Dev

No, that is your option. SSIS needs to know the metadata up front. Sorry. How would you map the columns in the destination anyway, if you want it to be dynamic?

You might be able to write a program that programatically builds a data flow for you, but then you aren't really using SSIS at that point. |||

Phil,

what would be your approach if there is a scenario like this for you.....

Regards,

Dev

|||

dev15`4534345677 wrote:

Phil,

what would be your approach if there is a scenario like this for you.....

Regards,

Dev

Well, the only other option that comes to mind is to read everything in as one big string, and then use a conditional split to send the data to 30 different connection managers. (That's an overly-simplified explanation, but I hope you get the idea...)

The connection managers are going to have to be setup for each format. I'm not sure there's a way around that.|||

Hi All,

I would appreciate If any body has a different solution for this pls. post it here...

regds,

dev

|||

dev15`4534345677 wrote:

Hi All,

I would appreciate If any body has a different solution for this pls. post it here...

regds,

dev

Also please search this forum. You are not the first to desire this and you'll not be the last.

The current version is very strict on metadata rules. Perhaps SSIS isn't the best option? That is, maybe you should write views and then use another SQL utility to output the results to a file. BCP, perhaps.|||Building package programatically may be another option...more complex perhaps. BOL it a godd point ot start|||

I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?

|||

ADMariner wrote:

I apologize in advance if I misinterpreted something but wouldn't a script task be an option? Theoretically you could replace your data flow task with a script task and pass in the variable containing the data flow then within the script task you could loop through the dataset columns and rows and output it to a flat file of your chosing. Again, I apologize if a script task is not an option but I do not see it being overly difficult to implement what you are asking using one. Have you looked into this option? Are you familar with VB.NET?

I don't think it's an option as you still need to know the outputs in advance to setup the script task's outputs.

How to Dynamically Generate FileName using SSIS

Hi

I have generated a ssis package that creates a text file based on a query retrieving records from the table.

Now i want the file name to be appended with the date and time of package execution. So the filename will be something like ; Filename_MMDDYYYY.txt.

How can i do this with the help of SSIS?

Anyone having an idea would be of great help.

Regards,

Salman Shehbaz.

Have a look at expressions.|||

for me the following link did the magic;

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=932686&SiteID=1

How to dynamically create Tables

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{


String sql = String.Format(

"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()

);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{


DataTable TypedLiterals =

new DataTable(

String.Format("Literals_{0}", type.ToString()));

TypedLiterals.Columns.Add(

"ID", typeof(int), "DISTINCT NOT NULL");

TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{


CurrentDatabase.Tables.Add(TypedLiterals);

}

But this is afaik not possible. Has anybody an idea how to solve this issure?Have you considere using the new XML datatype of SqlServer2005 ? As MS documentation says, it well fits into scenarios where you have sparse data.
You could use a single table that contains variable data and types in XML format.|||The ADO.NET datatable (which you are using) is not the same as the SQL Server table. YOu either will have to use your script approach or use SMO to create objects using the current server connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I think it might be better to use strings or at least tables for the most common types as defined in XSD and handling all others simply as strings. Using XML format is ineffective, because the main reason for using different Tables for each type is because I want to get more effective sorting.|||That the ADO.NET datatable is not the same than a SQL Server table was clear. Because I'm not a fan of the script Approach using String.Format to get SQL-Commands because of the fear of SQL-Injections, so I'll take a closer look at SMO. Thanks for the hint.

How to dynamically create Tables

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{


String sql = String.Format(

"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()

);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{


DataTable TypedLiterals =

new DataTable(

String.Format("Literals_{0}", type.ToString()));

TypedLiterals.Columns.Add(

"ID", typeof(int), "DISTINCT NOT NULL");

TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{


CurrentDatabase.Tables.Add(TypedLiterals);

}

But this is afaik not possible. Has anybody an idea how to solve this issure?Have you considere using the new XML datatype of SqlServer2005 ? As MS documentation says, it well fits into scenarios where you have sparse data.
You could use a single table that contains variable data and types in XML format.|||The ADO.NET datatable (which you are using) is not the same as the SQL Server table. YOu either will have to use your script approach or use SMO to create objects using the current server connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I think it might be better to use strings or at least tables for the most common types as defined in XSD and handling all others simply as strings. Using XML format is ineffective, because the main reason for using different Tables for each type is because I want to get more effective sorting.|||That the ADO.NET datatable is not the same than a SQL Server table was clear. Because I'm not a fan of the script Approach using String.Format to get SQL-Commands because of the fear of SQL-Injections, so I'll take a closer look at SMO. Thanks for the hint.

How to dynamically create SQL inside a stored procedure?

I am having problem with 'TOP @.pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @.pageSize here so that it dynamically determines the 'n' of 'TOP n' ?

ALTER PROCEDURE dbo.spGetNextPageRecords
(
@.pageSize int,
@.previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @.pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @.previousMaxId) order by ProductId
RETURN

Try aSET @.@.ROWCOUNT instead.

Terri|||So is there a way of dynamically forming a SQL statement inside a stored procedure and then executing it inside the same stored procedure?

I guess, from what you suggested, I should remove the 'TOP @.pageSize' clause from the query, and then use just before the query the following clause - 'SET @.@.ROWCOUNT @.pageSize'.|||The rowcount suggestion works beautifully. I can always get the next 'n' records for the next page.

With this stored procedure, one can always get a fixed number of records for all pages except the last page, while implementing custom paging in a datagrid. Most books will mention an example of custom paging in a datagrid, but will also say that each page might contain a different number of records, if there are missing values of the identity column. But here, even if identity column values are missing ( example: id's = 1,2, 5 , 9, 11 where id's = 3,4,6,7,8,10 are missing ) we can still get a constant number of records per page in custom paging.

I am assuming that the query in this stored procedure is not going to create any inefficiencies on SQL Server side. If you think it will, then please give your feedback.|||SET @.@.ROWCOUNT can have some unexpected side effects if you've got a number of batches in your proc because it restricts the rows for all batches not just the one you want. So just be careful of that.

You could use sp_execute run the dynamic top N (or even wait for Yukon), but unless you get into the problem areas I'd stick with ROWCOUNT.

how to dynamically create report in web app?

Hello,
I am rather new to report service. What I want to achieve is prvide a web
page and let users to choose what fields they want to see, what table they
want to query and what formats they want to apply.
Is it something achievable? Would someone give me some tutorial or hints?
Many Thanks
--
hello, please helpAlthough possible it is not trivial. You need to know the RDL specification
(go to MSDN.microsoft.com and search on RDL specification, there are several
articles). Then there is the issue that this is a server based product, you
cannot change the RDL on the fly. You have to publish the RDL. With RS 2005
you can use the new controls and give the control the RDL and the dataset
(in local mode you do not even need the server). These controls come with VS
2005 (Not with SQL Server). So, check out the spec and see if this is really
something you want to tackle. Depending on the complexity you might be
better off to use XML and XSL instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jerry.xuddd" <jerryxuddd@.discussions.microsoft.com> wrote in message
news:78300BE1-38D8-4491-997D-2C4C0EC98AFE@.microsoft.com...
> Hello,
> I am rather new to report service. What I want to achieve is prvide a web
> page and let users to choose what fields they want to see, what table they
> want to query and what formats they want to apply.
> Is it something achievable? Would someone give me some tutorial or hints?
> Many Thanks
> --
> hello, please help

How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HPsorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.
> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP|||> > You should simply GROUP BY all columns, and if there is a single value
> > in one of those columns (due to it being used in a condition), so be
> > it.
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan

How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HP
sorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?
|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan
|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.

> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP
|||> > You should simply GROUP BY all columns, and if there is a single value
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP
|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan

How to dynamically choose whether to SUM() column or GROU BY this column?

Hi there
I'm developping an application which would deal with costs in
organization which structure is split hierarchically to four division
levels.
eg. one can have atomic unit by setting values:
division1=A3
division2=111
division3=245
division4=1234
despite their hierarchical relation, my client wants to have reports on
all of those division combinations. so the perfect solution would be
the ultraGeneric(div1,div2,div3,div4) procedure which could do:
when called with all 4 parameters
select div1,div2,div3,div4,SUM(cost)
where div1=@.div1 and div2=@.div2 and div3=@.div3 and div4=@.div4
group by div1,div2,div3,div4
while when called with only div1 set (rest would be NULL):
select div1,'all','all,'all',SUM(cost)
where div1=@.div1
group by div1
I know how I can deal with WHERE part:
WHERE (@.DIV1 IS NULL OR @.DIV1= '' OR DIV1=@.DIV1)
but I have no idea what to do with SUM / GROUP BY part.
I can use CASE in select to choose whether to put column name or some
fake string if the column parameter is null.
but what about GROUP BY? does it behave like ORDER BY, in which I'd
have to use CASE with all combinations of parameter states?
thanks a lot
HPsorry, the post title doesn't make sense. to restate the question:
How to choose whether or not GROUP by a given column depending on
whether its value in parameter is null or not?|||You should simply GROUP BY all columns, and if there is a single value
in one of those columns (due to it being used in a condition), so be
it.
You can also use dynamic SQL. Read the following article by Erland
Sommarskog, for more approaches to the dynamic search conditions
problem:
http://www.sommarskog.se/dyn-search.html
Razvan|||Razvan Socol wrote:
> You should simply GROUP BY all columns, and if there is a single value
> in one of those columns (due to it being used in a condition), so be
> it.
hey, you're right! so case in Select will do - if I don't want to group
by a column a can just put static text instead of it.

> You can also use dynamic SQL. Read the following article by Erland
> Sommarskog, for more approaches to the dynamic search conditions
> problem:
> http://www.sommarskog.se/dyn-search.html
thanks, he saved my life once (great article about passing arrays to
procs). but thanks to your observation I won't have to use EXEC().
thanks
HP|||> > You should simply GROUP BY all columns, and if there is a single value
> hey, you're right! so case in Select will do - if I don't want to group
> by a column a can just put static text instead of it.
I guess it won't do because even if I put static text into div1 column
in a select, 'group by div1' will group by original values in a
database div1 column anyway.
greetings
HP|||I read your original message again and I think I now understand what
you mean. However, the queries that you wrote there will never return
more than one row. Is this really your requirement ?
For another approach, take a look at the WITH CUBE option (see
"Sumarizing Data" in Books Online). For example:
SELECT div1,div2,div3,div4,SUM(cost)
FROM YourTable
GROUP BY div1,div2,div3,div4
WITH CUBE
I think it may return all the data you need with a single query.
Razvan

How to dynamically change width?

Folks,

We have some reports that have optional columns. We have them working very nicely, with the column showing or hiding based on values in the report -- works great.

Except -- when the columns are present, the report spans onto two pages, when exported to PDF, in width. That's understandable, as there's a lot of extra data, and exactly what we want. However, when the columns are not present, we get empty pages instead, because the report doesn't automatically contract back onto the size that fits on one page.

Changing the report to a Matrix won't work, as the hidden columns on some of these come as sets of three, where each column in the three has different formatting (different widths, format codes, etc).

Thanks!

--randy

We have the same problem, columns thats hidden or not based och which parameters user used when the report is rendered. Everything is fine exept for when printing it... then the report is as wide as in design mode even if the columns is not visible, ending up with lots of blank pages. Is there anyway I can set width during runtime? It would be nice to be able to set the width to the sum of all shown column widths.

This is|||I'm also experiencing the same problem. The width of the column does not automatically adjust depending on the content of the report. Anyone who know's how to fix this? thanks.|||i have the same problem,somebody helps,and i also have a question how to dynamically set item'position.re|||

I too spend half a day trying to come up with a solution and found that no matter what is hidden, if the size of the table, list, matrix was outside the page width boudaries in design/layout mode the printed report would cross over.

I eventually came up with having two or more replica tables in the report that showed the different width columns in each table. For example, table one had financial information and based on a toggle the visbility would switch to table two that had the contact information, or the same information bar on eor two columns were different. The effect to the user is that a toggle can switch the between columns or column size without the boudaries being exceeded.

Rgds

Darrenh

|||I am having the same problem. Looks like MS should address this one.|||

Hi,

i have not faced such prob so far. but wat i feel is to make the width of all the columns to "0" and set the "Can Grow" property of the columns to "True" so that the columns expands horizontally based on the content it holds.

plz let me know if works fine.

Thanks.

|||I have experienced same problem also. Hope Microsoft addresses this.|||

But the CanGrow property does'nt seenm to have any effect.

I have dynamically changed the column widths in code according to a particular scenario.

|||

hi vaidyak,

"can grow" property should be set to true for all the textboxes of the row.

|||"CanGrow" doesn't seem to have any visible effect in this instance, even with all other textboxes in the row set to "CanGrow".

How to dynamically change width?

Folks,

We have some reports that have optional columns. We have them working very nicely, with the column showing or hiding based on values in the report -- works great.

Except -- when the columns are present, the report spans onto two pages, when exported to PDF, in width. That's understandable, as there's a lot of extra data, and exactly what we want. However, when the columns are not present, we get empty pages instead, because the report doesn't automatically contract back onto the size that fits on one page.

Changing the report to a Matrix won't work, as the hidden columns on some of these come as sets of three, where each column in the three has different formatting (different widths, format codes, etc).

Thanks!

--randy

We have the same problem, columns thats hidden or not based och which parameters user used when the report is rendered. Everything is fine exept for when printing it... then the report is as wide as in design mode even if the columns is not visible, ending up with lots of blank pages. Is there anyway I can set width during runtime? It would be nice to be able to set the width to the sum of all shown column widths.

This is|||I'm also experiencing the same problem. The width of the column does not automatically adjust depending on the content of the report. Anyone who know's how to fix this? thanks.|||i have the same problem,somebody helps,and i also have a question how to dynamically set item'position.re|||

I too spend half a day trying to come up with a solution and found that no matter what is hidden, if the size of the table, list, matrix was outside the page width boudaries in design/layout mode the printed report would cross over.

I eventually came up with having two or more replica tables in the report that showed the different width columns in each table. For example, table one had financial information and based on a toggle the visbility would switch to table two that had the contact information, or the same information bar on eor two columns were different. The effect to the user is that a toggle can switch the between columns or column size without the boudaries being exceeded.

Rgds

Darrenh

|||I am having the same problem. Looks like MS should address this one.|||

Hi,

i have not faced such prob so far. but wat i feel is to make the width of all the columns to "0" and set the "Can Grow" property of the columns to "True" so that the columns expands horizontally based on the content it holds.

plz let me know if works fine.

Thanks.

|||I have experienced same problem also. Hope Microsoft addresses this.|||

But the CanGrow property does'nt seenm to have any effect.

I have dynamically changed the column widths in code according to a particular scenario.

|||

hi vaidyak,

"can grow" property should be set to true for all the textboxes of the row.

|||"CanGrow" doesn't seem to have any visible effect in this instance, even with all other textboxes in the row set to "CanGrow".