Multiple joins in SQL Server

Hi I have a requirement where in i haev to convert the SQL from Oracle
to the one which will run on the SQL server.
in the Oracle Query i am doing multiple joins, between some 13 tables.
and some of these joins are inner joins and some are Left outer joins.
table1 inner joined with table 2
table2 inner join with table3
table2 inner join with table4
table2 left join with table5
table5 left jin with table6
table6 left jin with table7
table7 left jin with table8
table8 left jin with table9
Any idea how to achieve this'
Tia
naren"narendra vuradi" <nvuradi@.gmail.com> wrote in message
news:aad31742-b563-4da4-8a37-4be71ebf81c7@.e23g2000prf.googlegroups.com...
> Hi I have a requirement where in i haev to convert the SQL from Oracle
> to the one which will run on the SQL server.
> in the Oracle Query i am doing multiple joins, between some 13 tables.
> and some of these joins are inner joins and some are Left outer joins.
> table1 inner joined with table 2
> table2 inner join with table3
> table2 inner join with table4
> table2 left join with table5
> table5 left jin with table6
> table6 left jin with table7
> table7 left jin with table8
> table8 left jin with table9
> Any idea how to achieve this'
> Tia
> naren
Using standard SQL (rather than the proprietary Oracle syntax) it should
look pretty close to the way you wrote it:
SELECT ... /* columns */
FROM table1
INNER JOIN table2
ON table1.col = table2.col
INNER JOIN table3
ON table2.col = table3.col
INNER JOIN table4
ON table2.col = table4.col
LEFT OUTER JOIN table5
ON table2.col = table5.col
LEFT OUTER JOIN table6
ON table5.col = table6.col
LEFT OUTER JOIN table7
ON table6.col = table7.col
LEFT OUTER JOIN table8
ON table7.col = table8.col
LEFT OUTER JOIN table9
ON table8.col = table9.col ;
--
David Portas|||Hi
In addition to David's suggestion , I'd note you that JOIN 13 tables does
not look good in terms of database/application design as well as performance
"narendra vuradi" <nvuradi@.gmail.com> wrote in message
news:aad31742-b563-4da4-8a37-4be71ebf81c7@.e23g2000prf.googlegroups.com...
> Hi I have a requirement where in i haev to convert the SQL from Oracle
> to the one which will run on the SQL server.
> in the Oracle Query i am doing multiple joins, between some 13 tables.
> and some of these joins are inner joins and some are Left outer joins.
> table1 inner joined with table 2
> table2 inner join with table3
> table2 inner join with table4
> table2 left join with table5
> table5 left jin with table6
> table6 left jin with table7
> table7 left jin with table8
> table8 left jin with table9
> Any idea how to achieve this'
> Tia
> naren

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brin
bin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen
|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedu
le
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:

> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 database
s
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
>
>sql

Multiple jobs on one schedule

Hi
I am wondering if it's possible to run multiple backup jobs from one shedule
in SQL Server 2000, so all backups are triggered simultaneously at a given
time.
Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
they have a 1 to 1 relationship and this leads me to believe this is not
possible. Has anyone else ever achieved this, and if so, how?
Thanks in advance
Brinbin
The second backup will wait till the first one completed.
What is the purpose?
"brin" <brin_z{nospam}@.hotmail.com> wrote in message
news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am wondering if it's possible to run multiple backup jobs from one
> shedule in SQL Server 2000, so all backups are triggered simultaneously at
> a given time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>|||brin wrote:
> Hi
> I am wondering if it's possible to run multiple backup jobs from one shedule
> in SQL Server 2000, so all backups are triggered simultaneously at a given
> time.
> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> they have a 1 to 1 relationship and this leads me to believe this is not
> possible. Has anyone else ever achieved this, and if so, how?
> Thanks in advance
> Brin
>
I think the closest you can get, it to create multiple jobs that starts
at the same time. I don't quite see the need for it though, but I assume
you have a reason for it?
Regards
Steen|||It just seemed a bit more organised to use the one schedule as all we are
doing is repeating data in sysjobschedules, this is for around 25 databases
each with 2 backup schedules. We are about to rescheule the time they run
anyway so thought it may be a worthwhile excercise.
Thanks again
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> bin
> The second backup will wait till the first one completed.
> What is the purpose?
>
>
> "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I am wondering if it's possible to run multiple backup jobs from one
>> shedule in SQL Server 2000, so all backups are triggered simultaneously
>> at a given time.
>> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
>> they have a 1 to 1 relationship and this leads me to believe this is not
>> possible. Has anyone else ever achieved this, and if so, how?
>> Thanks in advance
>> Brin
>|||Hi
If you schedule mutiple jobs that hit the same discs you may see a
performance bottleneck. You may want to look at having one job with multiple
steps!
John
"brin" wrote:
> It just seemed a bit more organised to use the one schedule as all we are
> doing is repeating data in sysjobschedules, this is for around 25 databases
> each with 2 backup schedules. We are about to rescheule the time they run
> anyway so thought it may be a worthwhile excercise.
> Thanks again
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23TM$8QbSGHA.5780@.TK2MSFTNGP10.phx.gbl...
> > bin
> >
> > The second backup will wait till the first one completed.
> > What is the purpose?
> >
> >
> >
> >
> > "brin" <brin_z{nospam}@.hotmail.com> wrote in message
> > news:uZV$LObSGHA.1204@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I am wondering if it's possible to run multiple backup jobs from one
> >> shedule in SQL Server 2000, so all backups are triggered simultaneously
> >> at a given time.
> >>
> >> Having looked at the sysjobs and sysjobsschedules tables in msdb it seems
> >> they have a 1 to 1 relationship and this leads me to believe this is not
> >> possible. Has anyone else ever achieved this, and if so, how?
> >>
> >> Thanks in advance
> >>
> >> Brin
> >>
> >
> >
>
>

Multiple Job Steps SQL 2000

Hi,
I have 3 DTS packages I want to run after each other, in no particular
order.
I have x3 jobs, each running one of the DTS using CmdExec.
I don't want to run these jobs on individual schedules. I also don't want a
single DTS package with x3 steps to achieve the same thing.
I want visibility of Status etc available in the SQL EM Job view for each of
the jobs.
I created a single multi-step job, with each step invoking a DTS package
using CmdExec. This works but then the individual jobs are redundant and the
level of job status visibility is lost.
I created a single multi-step job, with each step using T-SQL and
sp_start_job. Now this ran the jobs and gave visibility of their individual
status but the jobs did not run synchronously. They all started together.
How can I write a single 'wrapper' job to start one job and wait for it to
complete before starting the next job? This allows me to maintain a single
schedule and yet see x3 status indicators?
ThanksCreate an another package , with three Execute Sql Task, and use work flow to
execute each task only after success so that it wont run synchronously.
Thanks,
Sree
"Gramps" wrote:
> Hi,
> I have 3 DTS packages I want to run after each other, in no particular
> order.
> I have x3 jobs, each running one of the DTS using CmdExec.
> I don't want to run these jobs on individual schedules. I also don't want a
> single DTS package with x3 steps to achieve the same thing.
> I want visibility of Status etc available in the SQL EM Job view for each of
> the jobs.
>
> I created a single multi-step job, with each step invoking a DTS package
> using CmdExec. This works but then the individual jobs are redundant and the
> level of job status visibility is lost.
> I created a single multi-step job, with each step using T-SQL and
> sp_start_job. Now this ran the jobs and gave visibility of their individual
> status but the jobs did not run synchronously. They all started together.
>
> How can I write a single 'wrapper' job to start one job and wait for it to
> complete before starting the next job? This allows me to maintain a single
> schedule and yet see x3 status indicators?
> Thanks
>
>

Multiple Job Steps SQL 2000

Hi,
I have 3 DTS packages I want to run after each other, in no particular
order.
I have x3 jobs, each running one of the DTS using CmdExec.
I don't want to run these jobs on individual schedules. I also don't want a
single DTS package with x3 steps to achieve the same thing.
I want visibility of Status etc available in the SQL EM Job view for each of
the jobs.
I created a single multi-step job, with each step invoking a DTS package
using CmdExec. This works but then the individual jobs are redundant and the
level of job status visibility is lost.
I created a single multi-step job, with each step using T-SQL and
sp_start_job. Now this ran the jobs and gave visibility of their individual
status but the jobs did not run synchronously. They all started together.
How can I write a single 'wrapper' job to start one job and wait for it to
complete before starting the next job? This allows me to maintain a single
schedule and yet see x3 status indicators?
Thanks
Create an another package , with three Execute Sql Task, and use work flow to
execute each task only after success so that it wont run synchronously.
Thanks,
Sree
"Gramps" wrote:

> Hi,
> I have 3 DTS packages I want to run after each other, in no particular
> order.
> I have x3 jobs, each running one of the DTS using CmdExec.
> I don't want to run these jobs on individual schedules. I also don't want a
> single DTS package with x3 steps to achieve the same thing.
> I want visibility of Status etc available in the SQL EM Job view for each of
> the jobs.
>
> I created a single multi-step job, with each step invoking a DTS package
> using CmdExec. This works but then the individual jobs are redundant and the
> level of job status visibility is lost.
> I created a single multi-step job, with each step using T-SQL and
> sp_start_job. Now this ran the jobs and gave visibility of their individual
> status but the jobs did not run synchronously. They all started together.
>
> How can I write a single 'wrapper' job to start one job and wait for it to
> complete before starting the next job? This allows me to maintain a single
> schedule and yet see x3 status indicators?
> Thanks
>
>
sql

Multiple Job Steps SQL 2000

Hi,
I have 3 DTS packages I want to run after each other, in no particular
order.
I have x3 jobs, each running one of the DTS using CmdExec.
I don't want to run these jobs on individual schedules. I also don't want a
single DTS package with x3 steps to achieve the same thing.
I want visibility of Status etc available in the SQL EM Job view for each of
the jobs.
I created a single multi-step job, with each step invoking a DTS package
using CmdExec. This works but then the individual jobs are redundant and the
level of job status visibility is lost.
I created a single multi-step job, with each step using T-SQL and
sp_start_job. Now this ran the jobs and gave visibility of their individual
status but the jobs did not run synchronously. They all started together.
How can I write a single 'wrapper' job to start one job and wait for it to
complete before starting the next job? This allows me to maintain a single
schedule and yet see x3 status indicators?
ThanksCreate an another package , with three Execute Sql Task, and use work flow t
o
execute each task only after success so that it wont run synchronously.
Thanks,
Sree
"Gramps" wrote:

> Hi,
> I have 3 DTS packages I want to run after each other, in no particular
> order.
> I have x3 jobs, each running one of the DTS using CmdExec.
> I don't want to run these jobs on individual schedules. I also don't want
a
> single DTS package with x3 steps to achieve the same thing.
> I want visibility of Status etc available in the SQL EM Job view for each
of
> the jobs.
>
> I created a single multi-step job, with each step invoking a DTS package
> using CmdExec. This works but then the individual jobs are redundant and t
he
> level of job status visibility is lost.
> I created a single multi-step job, with each step using T-SQL and
> sp_start_job. Now this ran the jobs and gave visibility of their individua
l
> status but the jobs did not run synchronously. They all started together.
>
> How can I write a single 'wrapper' job to start one job and wait for it to
> complete before starting the next job? This allows me to maintain a single
> schedule and yet see x3 status indicators?
> Thanks
>
>

Multiple instances...

Is it possible to run multiple instances of SQL server on the same computer?
SQL 2K.
Thanks,
JessySure, it supports up to 16 instances in SQL 2000. See "multiple instances
of SQL Server" in BooksOnLine for more details.
--
Andrew J. Kelly SQL MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:ugoXkPxZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Is it possible to run multiple instances of SQL server on the same
> computer?
> SQL 2K.
> Thanks,
> Jessy
>

Multiple instances...

Is it possible to run multiple instances of SQL server on the same computer?
SQL 2K.
Thanks,
Jessy
Sure, it supports up to 16 instances in SQL 2000. See "multiple instances
of SQL Server" in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:ugoXkPxZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Is it possible to run multiple instances of SQL server on the same
> computer?
> SQL 2K.
> Thanks,
> Jessy
>

Multiple instances...

Is it possible to run multiple instances of SQL server on the same computer?
SQL 2K.
Thanks,
JessySure, it supports up to 16 instances in SQL 2000. See "multiple instances
of SQL Server" in BooksOnLine for more details.
Andrew J. Kelly SQL MVP
"Jessy Martin" <Jessy_Smith79@.hotmail.com> wrote in message
news:ugoXkPxZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Is it possible to run multiple instances of SQL server on the same
> computer?
> SQL 2K.
> Thanks,
> Jessy
>

Multiple Instance of DTS

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

javawaba wrote:

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

This isn't a DTS forum. I recommend you direct your question to the DTS newsgroup microsoft.public.sqlserver.dts

Multiple Foreign Keys

What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?

The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.

My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?

Let me know! Thanks!!My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right?

I'd suggest you don't even delete the participant much less the replies. Forum threads would be rendered useless if you start deleting replies. Why not just add a flag to the participant table to indicate active/inactive status?

multiple exports files from one report?

Hi
I have a simple report I would like to run for 60 users. Is there a way to
have one report cycle though a dataset and create 60 exported .mhtml files? I
really don't want to create 60 reports and 60 subscriptions :(
All the exported files would reside in the same folder.
Any help would be much appreciated!
Thanks,
MarcusHi,
You should take a look at the Data Driven subscription. This will enable to
generate multiple reports, with different parameters, just the way you want.
For more info:
http://msdn.microsoft.com/library/en-us/rswork/htm/rms_subscribing_v1_7oj9.asp
--
| Jan Pieter Posthuma
--
"Marcus K" wrote:
> Hi
> I have a simple report I would like to run for 60 users. Is there a way to
> have one report cycle though a dataset and create 60 exported .mhtml files? I
> really don't want to create 60 reports and 60 subscriptions :(
> All the exported files would reside in the same folder.
> Any help would be much appreciated!
> Thanks,
> Marcus|||Thanks, I'm not sure what I've done, but I can't get the data-driven
subscrition option to show up. I've already stored the credentials in the
report. It must be something with the security on the site, but I've got
everyone as Site Administration during our testing... weird
"Jan Pieter Posthuma" wrote:
> Hi,
> You should take a look at the Data Driven subscription. This will enable to
> generate multiple reports, with different parameters, just the way you want.
> For more info:
> http://msdn.microsoft.com/library/en-us/rswork/htm/rms_subscribing_v1_7oj9.asp
> --
> | Jan Pieter Posthuma
> --
>
> "Marcus K" wrote:
> > Hi
> >
> > I have a simple report I would like to run for 60 users. Is there a way to
> > have one report cycle though a dataset and create 60 exported .mhtml files? I
> > really don't want to create 60 reports and 60 subscriptions :(
> >
> > All the exported files would reside in the same folder.
> >
> > Any help would be much appreciated!
> >
> > Thanks,
> > Marcus

Multiple databases

Hi
I need to run a masive script on multiple databases (about 20)
I only want to execute the script once to then loop and run on all databases
one by one. Is there a clean way of doing so?
Thankscreate a third party application with dot net
put the server name in array of connection string
with in a for-next loop
call each connection string and then
call the execute method
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Jaco" wrote:

> Hi
> I need to run a masive script on multiple databases (about 20)
> I only want to execute the script once to then loop and run on all databas
es
> one by one. Is there a clean way of doing so?
> Thanks|||Yup
use undocumented sp_MSforeachdb. Google for that
Regards
R.D
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> create a third party application with dot net
> put the server name in array of connection string
> with in a for-next loop
> call each connection string and then
> call the execute method
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Jaco" wrote:
>|||Thanks.
"R.D" wrote:
> Yup
> use undocumented sp_MSforeachdb. Google for that
> Regards
> R.D
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||thats
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"R.D" wrote:
> Yup
> use undocumented sp_MSforeachdb. Google for that
> Regards
> R.D
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>

Multiple Database Parameter

I have a report that works well, but would like to run it for other
databases. How would I go about this? Could I create some sort of parameter
in the report to call upon a different db?
Thanks,
RyanIf you are on RS 2005 you can have your data source be expression based. It
uses a parameter to determine which database to use. Search index of books
online for the word expressions and then click on data sources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
>I have a report that works well, but would like to run it for other
> databases. How would I go about this? Could I create some sort of
> parameter
> in the report to call upon a different db?
> Thanks,
> Ryan|||Bruce,
I am found the following expression; "="data source="
&Parameters!ServerName.Value& ";initial
catalog="Parameters!DatabaseName.Value".
When I go to run the report, I am getting an error that says "The
ConnectString expression for the data source â'Dataâ' contains an error:
[BC30277] Type character '&' does not match declared data type 'Object'"
Any thoughts on why I would get this? The connection string is exactly what
i pulled from the microsoft site.
Thanks,
Ryan
"Bruce L-C [MVP]" wrote:
> If you are on RS 2005 you can have your data source be expression based. It
> uses a parameter to determine which database to use. Search index of books
> online for the word expressions and then click on data sources.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
> >I have a report that works well, but would like to run it for other
> > databases. How would I go about this? Could I create some sort of
> > parameter
> > in the report to call upon a different db?
> >
> > Thanks,
> > Ryan
>
>|||I suggest creating a report with just a textbox and the parameter and set
this expression to the textbox. That way you can see what your string is
evaluating to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:A8F14883-6F1B-4E97-90FD-5DCAB8B15F64@.microsoft.com...
> Bruce,
> I am found the following expression; "="data source="
> &Parameters!ServerName.Value& ";initial
> catalog="Parameters!DatabaseName.Value".
> When I go to run the report, I am getting an error that says "The
> ConnectString expression for the data source 'Data' contains an error:
> [BC30277] Type character '&' does not match declared data type 'Object'"
> Any thoughts on why I would get this? The connection string is exactly
> what
> i pulled from the microsoft site.
> Thanks,
> Ryan
> "Bruce L-C [MVP]" wrote:
>> If you are on RS 2005 you can have your data source be expression based.
>> It
>> uses a parameter to determine which database to use. Search index of
>> books
>> online for the word expressions and then click on data sources.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
>> >I have a report that works well, but would like to run it for other
>> > databases. How would I go about this? Could I create some sort of
>> > parameter
>> > in the report to call upon a different db?
>> >
>> > Thanks,
>> > Ryan
>>|||Bruce,
I tried this and no luck. Any other thoughts?
Ryan
"Bruce L-C [MVP]" wrote:
> I suggest creating a report with just a textbox and the parameter and set
> this expression to the textbox. That way you can see what your string is
> evaluating to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> news:A8F14883-6F1B-4E97-90FD-5DCAB8B15F64@.microsoft.com...
> > Bruce,
> > I am found the following expression; "="data source="
> > &Parameters!ServerName.Value& ";initial
> > catalog="Parameters!DatabaseName.Value".
> >
> > When I go to run the report, I am getting an error that says "The
> > ConnectString expression for the data source 'Data' contains an error:
> > [BC30277] Type character '&' does not match declared data type 'Object'"
> >
> > Any thoughts on why I would get this? The connection string is exactly
> > what
> > i pulled from the microsoft site.
> >
> > Thanks,
> >
> > Ryan
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> If you are on RS 2005 you can have your data source be expression based.
> >> It
> >> uses a parameter to determine which database to use. Search index of
> >> books
> >> online for the word expressions and then click on data sources.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> >> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
> >> >I have a report that works well, but would like to run it for other
> >> > databases. How would I go about this? Could I create some sort of
> >> > parameter
> >> > in the report to call upon a different db?
> >> >
> >> > Thanks,
> >> > Ryan
> >>
> >>
> >>
>
>|||="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks"
Above is from the books online. It is not what you are doing. You are
assembling a string that will eventually look like this.
data source=myservername;initial catalog=AdventureWorks
You've got double quotes all over the place. Again, start this with a text
box. Do not put quotes around the = sign. Just off the top of my head I
think this is what you want:
="data source=" & Parameters!ServerName.Value & ";initial catalog=" &
Parameters!DatabaseName.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:C8F61505-7D51-43D4-AB32-5D40A963788B@.microsoft.com...
> Bruce,
> I tried this and no luck. Any other thoughts?
> Ryan
> "Bruce L-C [MVP]" wrote:
>> I suggest creating a report with just a textbox and the parameter and set
>> this expression to the textbox. That way you can see what your string is
>> evaluating to.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> news:A8F14883-6F1B-4E97-90FD-5DCAB8B15F64@.microsoft.com...
>> > Bruce,
>> > I am found the following expression; "="data source="
>> > &Parameters!ServerName.Value& ";initial
>> > catalog="Parameters!DatabaseName.Value".
>> >
>> > When I go to run the report, I am getting an error that says "The
>> > ConnectString expression for the data source 'Data' contains an error:
>> > [BC30277] Type character '&' does not match declared data type
>> > 'Object'"
>> >
>> > Any thoughts on why I would get this? The connection string is exactly
>> > what
>> > i pulled from the microsoft site.
>> >
>> > Thanks,
>> >
>> > Ryan
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> If you are on RS 2005 you can have your data source be expression
>> >> based.
>> >> It
>> >> uses a parameter to determine which database to use. Search index of
>> >> books
>> >> online for the word expressions and then click on data sources.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> >> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
>> >> >I have a report that works well, but would like to run it for other
>> >> > databases. How would I go about this? Could I create some sort of
>> >> > parameter
>> >> > in the report to call upon a different db?
>> >> >
>> >> > Thanks,
>> >> > Ryan
>> >>
>> >>
>> >>
>>