Showing posts with label switching. Show all posts
Showing posts with label switching. Show all posts

Wednesday, March 21, 2012

Dynamicaly switching between 2 datasources for a report in order to switch between the pro

I have 2 datasources, one for the production and one for the development
database and I use 'SetReportDataSources' to switch between the 2
datasources. Unfortunately 'SetReportDataSources' permanently switches the
datasource for the report on the Report Server. So, if before the 'Render
gets called another process switches the datasource for that report, the 1st
one would run with the wrong datasource.
Anyone know any way to do this without having to write a custom 'Data
Processing Extension'?
Thanks,
TirdadHello MSFT,
I was wondering if someone from Reporting Services or SQL Server would
address my question?
Sincerely,
Tirdad Lohrasb.
"Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> I have 2 datasources, one for the production and one for the development
> database and I use 'SetReportDataSources' to switch between the 2
> datasources. Unfortunately 'SetReportDataSources' permanently switches the
> datasource for the report on the Report Server. So, if before the 'Render
> gets called another process switches the datasource for that report, the
1st
> one would run with the wrong datasource.
> Anyone know any way to do this without having to write a custom 'Data
> Processing Extension'?
> Thanks,
> Tirdad
>|||The answer is no, dynamic connection strings are not supported. Several
workarounds have previously been proposed in the newsgroup.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
news:%23t5DmS%23VEHA.208@.TK2MSFTNGP10.phx.gbl...
> Hello MSFT,
> I was wondering if someone from Reporting Services or SQL Server would
> address my question?
> Sincerely,
> Tirdad Lohrasb.
>
> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
> news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > I have 2 datasources, one for the production and one for the development
> > database and I use 'SetReportDataSources' to switch between the 2
> > datasources. Unfortunately 'SetReportDataSources' permanently switches
the
> > datasource for the report on the Report Server. So, if before the
'Render
> > gets called another process switches the datasource for that report, the
> 1st
> > one would run with the wrong datasource.
> > Anyone know any way to do this without having to write a custom 'Data
> > Processing Extension'?
> >
> > Thanks,
> > Tirdad
> >
> >
>|||Thank you with the response back. Now, I have 2 questions, 1. Is there any
plan to support dynamic connections in the future.
2. Can you tell me what the workarounds are? The only one I can think of is
writing custom Data Processing Extension, Is this
the around?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:O22HW9BWEHA.2716@.tk2msftngp13.phx.gbl...
> The answer is no, dynamic connection strings are not supported. Several
> workarounds have previously been proposed in the newsgroup.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
> news:%23t5DmS%23VEHA.208@.TK2MSFTNGP10.phx.gbl...
> > Hello MSFT,
> >
> > I was wondering if someone from Reporting Services or SQL Server would
> > address my question?
> >
> > Sincerely,
> > Tirdad Lohrasb.
> >
> >
> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > > I have 2 datasources, one for the production and one for the
development
> > > database and I use 'SetReportDataSources' to switch between the 2
> > > datasources. Unfortunately 'SetReportDataSources' permanently switches
> the
> > > datasource for the report on the Report Server. So, if before the
> 'Render
> > > gets called another process switches the datasource for that report,
the
> > 1st
> > > one would run with the wrong datasource.
> > > Anyone know any way to do this without having to write a custom 'Data
> > > Processing Extension'?
> > >
> > > Thanks,
> > > Tirdad
> > >
> > >
> >
> >
>|||1. Yes, dynamic connection strings will be included in the SQL 2005 version.
2. You can write a stored procedure that uses other databases on the same
box, use OPENROWSET, or use linked servers
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
news:%23U2wbvGWEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Thank you with the response back. Now, I have 2 questions, 1. Is there any
> plan to support dynamic connections in the future.
> 2. Can you tell me what the workarounds are? The only one I can think of
> is
> writing custom Data Processing Extension, Is this
> the around?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:O22HW9BWEHA.2716@.tk2msftngp13.phx.gbl...
>> The answer is no, dynamic connection strings are not supported. Several
>> workarounds have previously been proposed in the newsgroup.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
>> news:%23t5DmS%23VEHA.208@.TK2MSFTNGP10.phx.gbl...
>> > Hello MSFT,
>> >
>> > I was wondering if someone from Reporting Services or SQL Server would
>> > address my question?
>> >
>> > Sincerely,
>> > Tirdad Lohrasb.
>> >
>> >
>> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> wrote in message
>> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
>> > > I have 2 datasources, one for the production and one for the
> development
>> > > database and I use 'SetReportDataSources' to switch between the 2
>> > > datasources. Unfortunately 'SetReportDataSources' permanently
>> > > switches
>> the
>> > > datasource for the report on the Report Server. So, if before the
>> 'Render
>> > > gets called another process switches the datasource for that report,
> the
>> > 1st
>> > > one would run with the wrong datasource.
>> > > Anyone know any way to do this without having to write a custom
>> > > 'Data
>> > > Processing Extension'?
>> > >
>> > > Thanks,
>> > > Tirdad
>> > >
>> > >
>> >
>> >
>>
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:uVohOYBVEHA.2360@.TK2MSFTNGP10.phx.gbl...
> why you don't change the datasource definition after your deployment?
> you can create the report in visual studio using a specific server, and,
> after the first deployment, change the server on report server. Your
> published reports will used the production server, and the development in
> visual studio used the dev. server.
>
> "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> a écrit dans le message de
> news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > I have 2 datasources, one for the production and one for the development
> > database and I use 'SetReportDataSources' to switch between the 2
> > datasources. Unfortunately 'SetReportDataSources' permanently switches
the
> > datasource for the report on the Report Server. So, if before the
'Render
> > gets called another process switches the datasource for that report, the
> 1st
> > one would run with the wrong datasource.
> > Anyone know any way to do this without having to write a custom 'Data
> > Processing Extension'?
> >
> > Thanks,
> > Tirdad
> >
> >
>|||Hi Toby,
when I try to download your sample I get the following popup:
"Operation could not be completed. Some resource(s) may have been removed."
I have installed the GOTDOTNET workspaces.
Regards,
Peter.
"Toby" wrote:
> I have released a DPE that achiveves this. It can be downloaded at
> http://workspaces.gotdotnet.com/appworld
> Regards
> Toby
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uVohOYBVEHA.2360@.TK2MSFTNGP10.phx.gbl...
> > why you don't change the datasource definition after your deployment?
> > you can create the report in visual studio using a specific server, and,
> > after the first deployment, change the server on report server. Your
> > published reports will used the production server, and the development in
> > visual studio used the dev. server.
> >
> >
> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> a écrit dans le message de
> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > > I have 2 datasources, one for the production and one for the development
> > > database and I use 'SetReportDataSources' to switch between the 2
> > > datasources. Unfortunately 'SetReportDataSources' permanently switches
> the
> > > datasource for the report on the Report Server. So, if before the
> 'Render
> > > gets called another process switches the datasource for that report, the
> > 1st
> > > one would run with the wrong datasource.
> > > Anyone know any way to do this without having to write a custom 'Data
> > > Processing Extension'?
> > >
> > > Thanks,
> > > Tirdad
> > >
> > >
> >
> >
>
>|||I go to the URL, but I don't see anything to download
"Toby" wrote:
> I have released a DPE that achiveves this. It can be downloaded at
> http://workspaces.gotdotnet.com/appworld
> Regards
> Toby
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uVohOYBVEHA.2360@.TK2MSFTNGP10.phx.gbl...
> > why you don't change the datasource definition after your deployment?
> > you can create the report in visual studio using a specific server, and,
> > after the first deployment, change the server on report server. Your
> > published reports will used the production server, and the development in
> > visual studio used the dev. server.
> >
> >
> > "Tirdad Lohrasb" <tlohrasb@.unex.ucla.edu> a écrit dans le message de
> > news:u5pydd%23UEHA.3012@.tk2msftngp13.phx.gbl...
> > > I have 2 datasources, one for the production and one for the development
> > > database and I use 'SetReportDataSources' to switch between the 2
> > > datasources. Unfortunately 'SetReportDataSources' permanently switches
> the
> > > datasource for the report on the Report Server. So, if before the
> 'Render
> > > gets called another process switches the datasource for that report, the
> > 1st
> > > one would run with the wrong datasource.
> > > Anyone know any way to do this without having to write a custom 'Data
> > > Processing Extension'?
> > >
> > > Thanks,
> > > Tirdad
> > >
> > >
> >
> >
>
>

Dynamically switching report data connection at run time

We have been presented with the problem of using Reporting Services to run
the same report against one of two databases.
We are currently using SQL Server 2000, and while 2005 has support for
dynamically building connection strings with parameters, 2000 apparently does
not.
Thus far, we have found the following solutions:
- A custom data processing extension, which wraps up a SqlConnection, and
switches database context at run-time based on an expected query parameter
- Reporting against a front-end query on the database, which in turn calls
the query from the desired database
- Installing two versions of the same report on the report server, and
having the application choose which to execute at run-time.
Each of these options has various drawbacks, the first brings with it a mess
of support and deployment issues, the second leans on the database harder
than it needs to, and the third is basically redundant.
Although the solution we need now is to switch between one of two databases,
the ideal solution would be able to manage 1-n databases.
We would appreciate your input as to which solution is the best, or if there
is functionality which would better suit our needs that we havenâ't discovered
yet.On Nov 16, 4:07 pm, breedReed <br...@.community.nospam> wrote:
> We have been presented with the problem of using Reporting Services to run
> the same report against one of two databases.
> We are currently using SQL Server 2000, and while 2005 has support for
> dynamically building connection strings with parameters, 2000 apparently does
> not.
> Thus far, we have found the following solutions:
> - A custom data processing extension, which wraps up a SqlConnection, and
> switches database context at run-time based on an expected query parameter
> - Reporting against a front-end query on the database, which in turn calls
> the query from the desired database
> - Installing two versions of the same report on the report server, and
> having the application choose which to execute at run-time.
> Each of these options has various drawbacks, the first brings with it a mess
> of support and deployment issues, the second leans on the database harder
> than it needs to, and the third is basically redundant.
> Although the solution we need now is to switch between one of two databases,
> the ideal solution would be able to manage 1-n databases.
> We would appreciate your input as to which solution is the best, or if there
> is functionality which would better suit our needs that we haven't discovered
> yet.
I would personally create a SQL Server instance that has Linked
Servers to your two other databases, then in reporting services pass
the SELECT * FROM OPENQUERY( @.ServerName, 'SELECT real SQL here' )
-- Scott

dynamically switching databases in a script

I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.

I need the correct syntax, the following won't work:

DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname

OPEN DBS

FETCH NEXT
FROM DBS
INTO
@.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN

USE @.dbname

The last line - the "USE" statement - is invalid. The following for example works:

USE master

But when supplied a declared variable a syntax error results for the use command because it expects an identifier.

So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?

Thanks for your time.

This is not possible right now since you cannot use variables in lot of statements in place of options or identifiers. You can use dynamic SQL though and below is the easiest way to do it:

declare @.sp nvarchar(500)

...

while .....

begin

-- use dbo.sp_executesql for SQL Server 2000

set @.sp = quotename(@.dbname) + N'sys.sp_executesql'

exec @.sp N'your sql string that needs to execute against db'

...

end

|||although this allowed the "use .." statement to run, it didn't have the effect I need. The remainder of the script was still running in the context of the original database.|||

So here's what I really need:

I need a way to switch the context of a script from one database to another, where I do not know the name of the databases beforehand (so they can't be hard-coded).

|||What I suggested will work provided the code that you want to run within context of the database is executed dynamically. Another approach is to pre-process the script file based on the database name and then run it. With SQL Server 2005, you can do this using SQLCMD pre-processing features.