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 joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards."Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000
,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>sql

Multiple joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards.
"Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.
|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>
|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>

Multiple joins in a filter

I'm using Sql 2005 and merge replication.

I want to create a dynamic merge filter on a table but the data I filter on is only reachable by a few extra joins. For example:

I want to filter Table3. Table3 is joined to Table2 which in joined to Table1. Table1 contains the host_name column that I use to filter i.e. host_name = HOSTNAME().

In order to get filtered data in Table3 do I have to also filter Table 2 and Table1, or in my filter clause for Table3 can I say something like: Table3.FKid = Table2.Id and Table2.FKId = Table1.Id and Table1.host_name = HOSTNAME()?

I'm pretty sure I can't use AND in my dynamic filter clause but I just wanted to make sure.

Thanks for your help

Graham

The recommended way would be:

1. On T1: myFilterColumn=HOST_NAME()

2. On T2: add a join filter with sp_addmergefilter - T2.c1=T1.c1

3. On T3: add a join filter with sp_addmergefilter - T3.c3=T2.c2

Multiple Joins - Need Help

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields... can you help me with the syntax:

SELECT DISTINCT

CTR.ReqID, CTR.SpecimenID, CTR.LabID, CTR.ProcedureID, CTR.TestID, CTR.Isolate, CTR.Problem,

CTR.ProblemComments, CP.ProcedureID, CP.Description AS CPProcedureDescription

FROM ClinicalTestsRequested CTR inner join ClinicalProcedures CP

ON CTR.ProcedureID=CP.ProcedureID

WHERE (CTR.SpecimenID = @.Accession)

I want to add another Join to the above where:

Table = ClinicalTests CT
Fields = CT.TestID, CT.Description AS CTTestDescription

and Compare = CTR.TestID to CT.TestID

Thanks !!

after your on clause for the first inner join add:

inner join clinicalTests ct on ctr.testid = ct.testid

add your columns to the list in the select.

Better yet, use sql server management studio express to design the query for you (it doensn't matter if your database is sql2005 or 2000). Create a new query for one of your tables and then use the query designer. This will allow you to drag and drop joins, make them into outer joins, add columns, etc, using a design tool.

--JJ

Multiple Joins

Hello,

I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
ColinIf there are no detail Product & Years which are not present in the Header table, then this sounds like a left outer join. The rest sound like inner joins.

If I'm understanding your intention, the following (untested) should work :

select h.*,
d.*,
s.*,
de.*
from Header h left outer join
Detail on h.product = d.product and h.year = d.year inner join
sales s on s.product = d.product and s.year = d.year and s.week = d.week inner join
Orders o on o.product = d.product and o.year = d.year and o.week = d.week inner join
Deliveries de on de.product = d.product and de.year = d.year and de.week = d.week

Perhaps I'm misunderstanding what you're wanting to see...

Multiple Join Query Problem

I am trying to update a field in TblA based on the contents of a field in
TblB where 4 conditions match but I can't get it to work. ere is what I
tried:
Update TblA t Set [Show Name] =
(Select Contest From TblB s
Where s.Date = t.Date,
s.RegionalCode = t.Regional,
s.unit = t.unit,
s.PF = t.[P or F])
How should that be coded?Update TblA Set [Show Name] =
(Select Contest From TblB s
Where s.Date = TblA.Date and
s.RegionalCode = TblA.Regional and
s.unit = TblA.unit and
s.PF = TblA.[P or F])|||Thanks. I was off on the wrong track.
Wayne
<markc600@.hotmail.com> wrote in message
news:1144491686.935456.163560@.v46g2000cwv.googlegroups.com...
> Update TblA Set [Show Name] =
> (Select Contest From TblB s
> Where s.Date = TblA.Date and
> s.RegionalCode = TblA.Regional and
> s.unit = TblA.unit and
> s.PF = TblA.[P or F])
>|||You might want a where clause in your query:
Update TblA Set [Show Name] =
(Select Contest From TblB s
Where s.Date = TblA.Date and
s.RegionalCode = TblA.Regional and
s.unit = TblA.unit and
s.PF = TblA.[P or F])
where exists
(Select Contest From TblB s
Where s.Date = TblA.Date and
s.RegionalCode = TblA.Regional and
s.unit = TblA.unit and
s.PF = TblA.[P or F])sql

Multiple Join Clause

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
MosheYou can have multiple JOIN clauses. I find it helpful to put them on separate lines, plus I always indicate the JOIN type since I never can remember which type is the default, and it is liable to change on someone's whim in the future. Plus normally I'd want to return the User's information whether or not I was able to successfully find the corresponding Group or Company -- which means OUTER JOINs:

SELECT
 Users.UserId,
 Users.GroupId,
 ISNULL(Groups.Name,'**unknown**'),
 Users.CompanyId,
 ISNULL(Companies.Name,'**unknown**')
FROM
 Users
LEFT OUTER JOIN
 Groups ON Users.GroupId = Groups.Id
LEFT OUTER JOIN
 Companies ON Users.CompanyID = Companies.CompanyID

Terri|||Thanks,
It was helpfull

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 using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?
A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?
|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:

> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>
>
|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.

> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...[vbcol=seagreen]
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:

Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:
> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
> >I need a constant to be available in multiple steps of a job (more
> > specifically, getdate() from step one). I tried storing it in a variable or
> > temp table but when I get to step two of the job and try to retrieve the
> > value, I get "must declare variable..." or "table does not exist" (depending
> > on which method I am testing).
> >
> > I did a little test and the SPID in each one of the steps is the same so
> > shouldn't the temp table be accessible from all steps?
>
>|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:
>> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
>> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
>> table?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
>> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>> >I need a constant to be available in multiple steps of a job (more
>> > specifically, getdate() from step one). I tried storing it in a variable or
>> > temp table but when I get to step two of the job and try to retrieve the
>> > value, I get "must declare variable..." or "table does not exist" (depending
>> > on which method I am testing).
>> >
>> > I did a little test and the SPID in each one of the steps is the same so
>> > shouldn't the temp table be accessible from all steps?
>>

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 job steps

I am creating jobs using SQL Server Agent, the job has multiple steps.
Example
Step1 creates tables
Step2 creates group of reports to be executed only when step 1 is successful
l
Step3 creates group of reports to be executed only when step 1 is successful
l
I would like to execute step2 and step3 at the same time, step 3 is not depe
nding on step2.
How I do that? I am thinking of creating another job to execute step3 after
evaluating the status of step1,
somebody has more efficient ideas?
Thanks,One way would be to use DTS and it's workflow engine. You can run sql tasks
conditionally or at the same time.
Ray Higdon MCSE, MCDBA, CCNA
--
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:CCB3CD33-DDFC-4E8B-9324-774A90F7A5EB@.microsoft.com...
> I am creating jobs using SQL Server Agent, the job has multiple steps.
> Example
> Step1 creates tables
> Step2 creates group of reports to be executed only when step 1 is
successfull
> Step3 creates group of reports to be executed only when step 1 is
successfull
> I would like to execute step2 and step3 at the same time, step 3 is not
depending on step2.
> How I do that? I am thinking of creating another job to execute step3
after evaluating the status of step1,
> somebody has more efficient ideas?
> Thanks,
>|||Thanks Ray...I will try that.

Multiple items in one row

Okay, I am pretty new at this, so this may be obvious, but here's what I want to do.

I have an Access database that can be used to reserve resources. The user inputs a date range and then checks off what they want to reserve. It looks like this:

StartDate .............EndDate .........Laptop......Camera........Projector

10/29/2007...........10/29/2007........................X
10/29/2007...........10/31/2007..........X
10/31/2007.......... 11/01/2007........................X..................X

Of course there is other info, and a return date, but none of that is needed for this report.

I want the report to show what is available. A blank means the item is available and the X means it is unavailable. It should look like this:

Date...............Laptop.............Camera..........Projector
10/29/07.............X.....................X
10/30/07.............X
10/31/07.............X.....................X..................X
11/1/07...............X.....................X

The dates in the report come from a date table which make up Group Header 1.

This is a sample of the formula I used to get the X's to show up in the Detail section.

if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"

This works but the x's from different reservations show up on different lines. I was able to get the first detail line to match up with the date, by choosing Underlay Following Sections, but the others still show up in a different line. For example 10/31/07 looks like this:

Date.........Laptop.........Camera.............Projector
10/31/07.........X
....................................X.....................X

Your help is much appreciated!Try to create 1 formula for each of the fields you need to be shown as 'X',

for example:

@.Laptop:
if isnull(your_table.Laptop) then 'X'

@.Camera:
if isnull(your_table.Camera) then 'X'
.
.
.

Use them instead of the fields.|||I am not using fields. I am using a formula for each item. For example @.laptop looks like this:

if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"

I just want all the x's to appear on the same row as the date so if one person reserves something on 10/31 and someone else has reserved something else on the same day, (which is represented on two rows in the database) I want those items to have an X under them on the 10/31 row. I can get the first one to get up to the date row by using the Underlay, but I can not get the other rows up.

I've tried putting the details in with the group header, but everything disappears.

Maybe I should change the database? I don't know. Let me know what you think!

multiple items

the key is in your post above my first post where you ask "how can I build UI
for it" - that is the key - you have to build your own custom user interface
for the report parameters. Instead of using a drop down list to display the
allowable parameter values, use a list box, which supports selecting of
multiple values. You have to go the route of using the web services, not url
access (for my solution) - which direction are you currently headed?
It is a lot of work to create the custom ui, but it does give you the
ability to pass multiple values into a stored procedure and use dynamic SQL
to fuel the dataset for the report.
I hope this helps,Actually you could still use the URL approach and code a function that parses
your parameter string. Simply encode a delimiter between the multi-select
values and use a UDF to parse the parameter in your stored procedure. Unless
the URL will be too large to pass.
--
Keith Powers, MCDBA
"Myles" wrote:
> the key is in your post above my first post where you ask "how can I build UI
> for it" - that is the key - you have to build your own custom user interface
> for the report parameters. Instead of using a drop down list to display the
> allowable parameter values, use a list box, which supports selecting of
> multiple values. You have to go the route of using the web services, not url
> access (for my solution) - which direction are you currently headed?
> It is a lot of work to create the custom ui, but it does give you the
> ability to pass multiple values into a stored procedure and use dynamic SQL
> to fuel the dataset for the report.
>
> I hope this helps,
>|||thanks Keith - would you be able to cut and paste your response into the
correct thread for mvp? I somehow posted this to the wrong area (oops!) The
correct post is 'Select Multiple Items from the Drop Down' - a couple of
threads below.
I was just trying to explain how we did it, but you are entirely correct.
"Keith Powers" wrote:
> Actually you could still use the URL approach and code a function that parses
> your parameter string. Simply encode a delimiter between the multi-select
> values and use a UDF to parse the parameter in your stored procedure. Unless
> the URL will be too large to pass.
> --
> Keith Powers, MCDBA
>
> "Myles" wrote:
> > the key is in your post above my first post where you ask "how can I build UI
> > for it" - that is the key - you have to build your own custom user interface
> > for the report parameters. Instead of using a drop down list to display the
> > allowable parameter values, use a list box, which supports selecting of
> > multiple values. You have to go the route of using the web services, not url
> > access (for my solution) - which direction are you currently headed?
> >
> > It is a lot of work to create the custom ui, but it does give you the
> > ability to pass multiple values into a stored procedure and use dynamic SQL
> > to fuel the dataset for the report.
> >
> >
> > I hope this helps,
> >
> >sql

Multiple item selection from a queried report parameter list

I am trying to mirror a report originally published through another non MS
reporting application which allows multiple selection from a query parameter
list by use of the normal Windows 'Ctrl' or 'Shift' keys in cojunction with
the mouse click.
This method does not appear to function with SQL Reporting Services. The
expanded list automatically closes on selection of one item.
Is there another method of performing multiple selection from a list.This is something which needs to be added reporting services. You can NOT do
a multi selection... About the best thing you can do is allow your user to
enter a delimited list... It's really a good option, but about the only
option right now...
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
"RW" <RW@.discussions.microsoft.com> wrote in message
news:63B4D8FB-A36B-485B-B251-9D07D810A513@.microsoft.com...
> I am trying to mirror a report originally published through another non MS
> reporting application which allows multiple selection from a query
parameter
> list by use of the normal Windows 'Ctrl' or 'Shift' keys in cojunction
with
> the mouse click.
> This method does not appear to function with SQL Reporting Services. The
> expanded list automatically closes on selection of one item.
> Is there another method of performing multiple selection from a list.

Multiple IP's. Only want 1 for SQL 1433

Hi,
I've got an SQL server 2000 (sp3).
This box has multiple IP addresses. These are for web pages
But i only want the SQL server (port 1433) to listen on one of these addressses.
It seems to listen on all the addresses which i don't want it to. for sercuirty reasons as well
as for it's resource sake.
I've poked around in the properties and connection settings and it doesn't seem to let you change IP's only ports etc.
Please let me know how i can work this out..
Thankyou
There is no way to prevent SQL Server from listening on all the ip
addresses that are bound to NIC cards on the machine. If you want to limit
the ones that clients can connect to then that will have to be impletmented
at the client level.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Multiple IP's. Only want 1 for SQL 1433

Hi,
I've got an SQL server 2000 (sp3).
This box has multiple IP addresses. These are for web pages
But i only want the SQL server (port 1433) to listen on one of these address
ses.
It seems to listen on all the addresses which i don't want it to. for sercui
rty reasons as well
as for it's resource sake.
I've poked around in the properties and connection settings and it doesn't s
eem to let you change IP's only ports etc.
Please let me know how i can work this out..
ThankyouThere is no way to prevent SQL Server from listening on all the ip
addresses that are bound to NIC cards on the machine. If you want to limit
the ones that clients can connect to then that will have to be impletmented
at the client level.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Multiple IP's (from 2 different networks) on a single SQL 2005 Cluster

Hello there,
I'm currently building up a SQL 2005 Active/Standby cluster in a DMZ. I have three NIC's in each server.
Each NIC is connected to a different network:
192.168.100.1 is the public NIC
10.0.0.1 is the NIC used for communication betwen the cluster nodes (heartbeat)
192.168.200.1 is the admin NIC
I have installed my cluster using the 192.168.100.0 network for public access. This means that my SQL virtual ip is 192.168.100.10
Each server can be administered over the 192.168.200.0 network (admin) and the cluster/sql sever ip is available from the 192.168.100.0 (public) network.
Now for my question: How can I assign a ip address from my admin network (e.g.192.168.200.10) to the existing SQL server cluster to make it available from my admin network while keeping the public ip.
Thanx in advance!
Chris
From http://www.developmentnow.com/g/99_2004_12_1_0_0/sql-server-clustering.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
"chrismk" <Multiple IP's (from 2 different networks) on a single SQL 2005
Cluster> wrote in message
news:59d2db33-7eb3-4b9e-af0f-fd54c5d3d72b@.developmentnow.com...
> I'm currently building up a SQL 2005 Active/Standby cluster in a DMZ. I
> have three NIC's in each server.
BTW, I am not trying to be a pain, but some of the issues here have me
extremely curious. First, why are you putting it in the perimeter network?
You should have it behind the internal firewall so the data is fully
protected and have anything that runs in the perimeter network to make its
calls through the internal firewall using a non-standard port. Data needs to
be protected.

> Each NIC is connected to a different network:
> 192.168.100.1 is the public NIC
> 10.0.0.1 is the NIC used for communication betwen the cluster nodes
> (heartbeat)
> 192.168.200.1 is the admin NIC
Why do you need an admin NIC. There is this concept of IP routing that
should make this requirement go away. <G>

> I have installed my cluster using the 192.168.100.0 network for public
> access. This means that my SQL virtual ip is 192.168.100.10
> Each server can be administered over the 192.168.200.0 network (admin) and
> the cluster/sql sever ip is available from the 192.168.100.0 (public)
> network.
> Now for my question: How can I assign a ip address from my admin network
> (e.g.192.168.200.10) to the existing SQL server cluster to make it
> available from my admin network while keeping the public ip.
It should work. Add the new IP Address resource and set the dependencies on
all the other resources as needed.
However, just because it should work does not mean it is your best
alternative.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Dec 10 - 13 in Denver
Jan 18 - 31 in Denver
|||Check out the following KB articles:
http://msdn2.microsoft.com/en-us/library/ms190460.aspx
http://msdn2.microsoft.com/en-us/library/ms177447.aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"chrismk" <Multiple IP's (from 2 different networks) on a single SQL 2005
Cluster> wrote in message
news:59d2db33-7eb3-4b9e-af0f-fd54c5d3d72b@.developmentnow.com...
Hello there,
I'm currently building up a SQL 2005 Active/Standby cluster in a DMZ. I have
three NIC's in each server.
Each NIC is connected to a different network:
192.168.100.1 is the public NIC
10.0.0.1 is the NIC used for communication betwen the cluster nodes
(heartbeat)
192.168.200.1 is the admin NIC
I have installed my cluster using the 192.168.100.0 network for public
access. This means that my SQL virtual ip is 192.168.100.10
Each server can be administered over the 192.168.200.0 network (admin) and
the cluster/sql sever ip is available from the 192.168.100.0 (public)
network.
Now for my question: How can I assign a ip address from my admin network
(e.g.192.168.200.10) to the existing SQL server cluster to make it
available from my admin network while keeping the public ip.
Thanx in advance!
Chris
From
http://www.developmentnow.com/g/99_2004_12_1_0_0/sql-server-clustering.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

MULTIPLE INTERSECT in MDX Query - Reg

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

DimUser 101

102

103

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

Transact SQL Query:

Select Count(*) from

(

Select userid from FactPlayer where productId = 01

INTERSECT

Select userid from FactPlayer where productId= 02

)

PlayerCount

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

Hi Vijay,

You could solve this using the Intersect function, but you don't need to. Here's an example from Adventure Works showing all the Customers who bought products from two different subcategories (mountain bikes and caps):

select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
, ([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
on 1
from [Adventure Works]

What it's doing is using the nonempty function to return a list of Customers who bought products in subcategory 1, and then using another nonempty function to filter that list by those who bought products from subcategory 19. This, I think, will be more efficient than using the Intersect function although for the record here's the same query rewritten to use Intersect:

select {[Measures].[Internet Sales Amount]} on 0,
intersect(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
,nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
)
on 1
from [Adventure Works]

HTH,

Chris

|||

Hi Chris,

Thank you very much. It is working perfectly.

Vijay

|||

Hi Everyone,

We are facing some problem in the cube particularly in INTERSECT function.

Here are the details.

Dimension Tables:

DimTime 200601

200602

DimProduct 01

02

03

DimUser 101

102

FactTables:

FactPlayer

Time Product User

200601 01 101

200601 02 101

200601 01 102

200601 03 101

Transact SQL Query:

Select Count(*) from

(

Select user from FactPlayer where productId = 01

INTERSECT

Select user from FactPlayer where productId= 02

INTERSECT

Select user from FactPlayer where productId= 03

)

PlayerCount

RESULT: 1 (UserId: 101)

We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.

Expecting your valuable reply.

Regards

Vijay

|||

I found the solution below.

SELECT NON EMPTY{[Measures].[User ID Distinct Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

INTERSECT

(

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[3])

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[11])

)

)

),

NONEMPTY

(

[DIM USER].[DIM USER].CHILDREN,

([Dim Time].[TimeKey].&[200602],

[Measures].[User ID Distinct Count],

[DIM PRODUCT].[DIM PRODUCT].&[12])

)

)

ON ROWS

FROM [DSV KPI]

Please reply me if there any changes in the query.

Thank You

Vijay

sql

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 instances with different TCP ports

I was wondering whether I can configure SQL instances with different TCP
ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able to
assign only one port to TCP at the client machines using "Client Network
Utility". Is there a way to set different TCP port to each instance?
Have you tried using an alias? See How to configure a client to use TCP/IP
(Client Network Utility) in SQL BOL.
HTH
Jerry
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?
|||Actually, I tried usng Alias. If I use port 1433, it works for both
instances. However, if I change the port to 1432 for SQL2K5 instance (in
alias), it fails. I have a feeling that the tcp port setting at the server
may not be working. I am setting Microsoft SQL Server 2006 --> SQL Computer
Manager --> Protocols fpr SQLB --> TCP --> IP1 to port 1432.
"Jerry Spivey" wrote:

> Have you tried using an alias? See How to configure a client to use TCP/IP
> (Client Network Utility) in SQL BOL.
> HTH
> Jerry
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>
>
|||I dont know about SQL2K5
But in SQL 2000 by using server network utility, under enabled protocols,
click on TCP/IP then click Properties button and you can change port SQL2K
listens on. Then use and alias in Client Net Util to go to dif port.
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?
|||I have no problem with SQL2K. Thanks.
"David J. Cartwright" wrote:

> I dont know about SQL2K5
> But in SQL 2000 by using server network utility, under enabled protocols,
> click on TCP/IP then click Properties button and you can change port SQL2K
> listens on. Then use and alias in Client Net Util to go to dif port.
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>
>
|||Hi,
Use Server Network utlity to change ports. After changing restart sql server
service.
Thanks
Hari
SQL Server MVP
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?
|||Hi Hari,
Server Network Utility is in SQL 2000. However, it is
'SQLComputer Manager' in SQL server 2005.
To recape the issue, I have one SQL 2000 instance (SQLA) and one
SQL 2005 instance (SQLB). I am trying to assign different ports. Both are
working if I assign the same port. If I assign different ports, SQLB is
failing to respond to client (running enterprise manager or management
studio). Is there a conflict in registry?
"Hari Prasad" wrote:

> Hi,
> Use Server Network utlity to change ports. After changing restart sql server
> service.
> Thanks
> Hari
> SQL Server MVP
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>
>
|||Hi Hari,
I could find a way to do this at client. I installed "workstation"
components (of SQ 2005) at the client. Then, I defined alias for SQLserrver
2005 instance with port (ez., <server name>\SQLB,1432) using SQL computer
manager of 2005. Then, I used SQL Server Management Studio to conect to both
instances (SQLA (2000) and SQLB (2005) ) on the server. It seems that SQL
server 2000 Enterprise Manager does not work for SQL server 2005.
Please let me know if there is a clean procedure.
"doc pisapati" wrote:

> I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?

Multiple instances with different TCP ports

I was wondering whether I can configure SQL instances with different TCP
ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able t
o
assign only one port to TCP at the client machines using "Client Network
Utility". Is there a way to set different TCP port to each instance?Have you tried using an alias? See How to configure a client to use TCP/IP
(Client Network Utility) in SQL BOL.
HTH
Jerry
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?|||Actually, I tried usng Alias. If I use port 1433, it works for both
instances. However, if I change the port to 1432 for SQL2K5 instance (in
alias), it fails. I have a feeling that the tcp port setting at the server
may not be working. I am setting Microsoft SQL Server 2006 --> SQL Computer
Manager --> Protocols fpr SQLB --> TCP --> IP1 to port 1432.
"Jerry Spivey" wrote:

> Have you tried using an alias? See How to configure a client to use TCP/I
P
> (Client Network Utility) in SQL BOL.
> HTH
> Jerry
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>
>|||I dont know about SQL2K5
But in SQL 2000 by using server network utility, under enabled protocols,
click on TCP/IP then click Properties button and you can change port SQL2K
listens on. Then use and alias in Client Net Util to go to dif port.
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?|||I have no problem with SQL2K. Thanks.
"David J. Cartwright" wrote:

> I dont know about SQL2K5
> But in SQL 2000 by using server network utility, under enabled protocols,
> click on TCP/IP then click Properties button and you can change port SQL2K
> listens on. Then use and alias in Client Net Util to go to dif port.
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>
>|||Hi,
Use Server Network utlity to change ports. After changing restart sql server
service.
Thanks
Hari
SQL Server MVP
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?|||Hi Hari,
Server Network Utility is in SQL 2000. However, it is
'SQLComputer Manager' in SQL server 2005.
To recape the issue, I have one SQL 2000 instance (SQLA) and one
SQL 2005 instance (SQLB). I am trying to assign different ports. Both are
working if I assign the same port. If I assign different ports, SQLB is
failing to respond to client (running enterprise manager or management
studio). Is there a conflict in registry?
"Hari Prasad" wrote:

> Hi,
> Use Server Network utlity to change ports. After changing restart sql serv
er
> service.
> Thanks
> Hari
> SQL Server MVP
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>
>|||Hi Hari,
I could find a way to do this at client. I installed "workstation"
components (of SQ 2005) at the client. Then, I defined alias for SQLserrver
2005 instance with port (ez., <server name>\SQLB,1432) using SQL computer
manager of 2005. Then, I used SQL Server Management Studio to conect to both
instances (SQLA (2000) and SQLB (2005) ) on the server. It seems that SQL
server 2000 Enterprise Manager does not work for SQL server 2005.
Please let me know if there is a clean procedure.
"doc pisapati" wrote:

> I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?sql

Multiple instances with different TCP ports

I was wondering whether I can configure SQL instances with different TCP
ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able to
assign only one port to TCP at the client machines using "Client Network
Utility". Is there a way to set different TCP port to each instance?Have you tried using an alias? See How to configure a client to use TCP/IP
(Client Network Utility) in SQL BOL.
HTH
Jerry
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?|||Actually, I tried usng Alias. If I use port 1433, it works for both
instances. However, if I change the port to 1432 for SQL2K5 instance (in
alias), it fails. I have a feeling that the tcp port setting at the server
may not be working. I am setting Microsoft SQL Server 2006 --> SQL Computer
Manager --> Protocols fpr SQLB --> TCP --> IP1 to port 1432.
"Jerry Spivey" wrote:
> Have you tried using an alias? See How to configure a client to use TCP/IP
> (Client Network Utility) in SQL BOL.
> HTH
> Jerry
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
> >I was wondering whether I can configure SQL instances with different TCP
> > ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> > to
> > assign only one port to TCP at the client machines using "Client Network
> > Utility". Is there a way to set different TCP port to each instance?
>
>|||I dont know about SQL2K5
But in SQL 2000 by using server network utility, under enabled protocols,
click on TCP/IP then click Properties button and you can change port SQL2K
listens on. Then use and alias in Client Net Util to go to dif port.
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?|||I have no problem with SQL2K. Thanks.
"David J. Cartwright" wrote:
> I dont know about SQL2K5
> But in SQL 2000 by using server network utility, under enabled protocols,
> click on TCP/IP then click Properties button and you can change port SQL2K
> listens on. Then use and alias in Client Net Util to go to dif port.
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
> >I was wondering whether I can configure SQL instances with different TCP
> > ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> > to
> > assign only one port to TCP at the client machines using "Client Network
> > Utility". Is there a way to set different TCP port to each instance?
>
>|||Hi,
Use Server Network utlity to change ports. After changing restart sql server
service.
Thanks
Hari
SQL Server MVP
"doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
>I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?|||Hi Hari,
Server Network Utility is in SQL 2000. However, it is
'SQLComputer Manager' in SQL server 2005.
To recape the issue, I have one SQL 2000 instance (SQLA) and one
SQL 2005 instance (SQLB). I am trying to assign different ports. Both are
working if I assign the same port. If I assign different ports, SQLB is
failing to respond to client (running enterprise manager or management
studio). Is there a conflict in registry?
"Hari Prasad" wrote:
> Hi,
> Use Server Network utlity to change ports. After changing restart sql server
> service.
> Thanks
> Hari
> SQL Server MVP
> "doc pisapati" <docpisapati@.discussions.microsoft.com> wrote in message
> news:F1A78A23-3398-4CA5-8F98-00CF66B0F023@.microsoft.com...
> >I was wondering whether I can configure SQL instances with different TCP
> > ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able
> > to
> > assign only one port to TCP at the client machines using "Client Network
> > Utility". Is there a way to set different TCP port to each instance?
>
>|||Hi Hari,
I could find a way to do this at client. I installed "workstation"
components (of SQ 2005) at the client. Then, I defined alias for SQLserrver
2005 instance with port (ez., <server name>\SQLB,1432) using SQL computer
manager of 2005. Then, I used SQL Server Management Studio to conect to both
instances (SQLA (2000) and SQLB (2005) ) on the server. It seems that SQL
server 2000 Enterprise Manager does not work for SQL server 2005.
Please let me know if there is a clean procedure.
"doc pisapati" wrote:
> I was wondering whether I can configure SQL instances with different TCP
> ports. I did install SQL 2000 and SQL 2005 on one box. However, I was able to
> assign only one port to TCP at the client machines using "Client Network
> Utility". Is there a way to set different TCP port to each instance?

Multiple Instances SQL2K and 2005

Hi, we have a 2 node cluster running Server 2003 with a single instance of
SQL 2005. We are looking to migrate as many apps as possible to SQL 2005,
but will likely need to keep a SQL2000 instance. In addition, we want to
isolate some of the SQL databases that are web accessible and will setup an
instance for that. So, are there any reservations running this:
(2) SQL 2005 instances
(1) SQL 2000 instance
on the same cluster? We have Enterprise edition of the OS and SQL Server.
We plan to run a multi instance setup with an active and passive node so all
3 instances would run on the same node. The hardware should be able to
handle all 3 instances.
Also, any special steps to install SQL service packs in this scenario?
Thanks,
TJ
TJ,
There is no issue running SQL 2000 and SQL 2005 on the same machine. I have
done so in Production with no issues. Infact I currently have SQL 2000, SQL
2005 and SQL 2008 installed on my laptop side by side.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"TJ" wrote:

> Hi, we have a 2 node cluster running Server 2003 with a single instance of
> SQL 2005. We are looking to migrate as many apps as possible to SQL 2005,
> but will likely need to keep a SQL2000 instance. In addition, we want to
> isolate some of the SQL databases that are web accessible and will setup an
> instance for that. So, are there any reservations running this:
> (2) SQL 2005 instances
> (1) SQL 2000 instance
> on the same cluster? We have Enterprise edition of the OS and SQL Server.
> We plan to run a multi instance setup with an active and passive node so all
> 3 instances would run on the same node. The hardware should be able to
> handle all 3 instances.
> Also, any special steps to install SQL service packs in this scenario?
> Thanks,
> TJ
>

Multiple instances SQL server 2000 - destination folders

Can someone clarify for me, if I were to install 2 named instances of
SQL server 2000, whether I must define 2 different destination folders
for the program files ?
Or can I leave the Program Files destination folder the same for both
instances [c:\program files\Microsoft SQL Server] ?
I am separating the data files in their own data file destination
directory [d:\...\microsoft sql server\data\name_of_instance1 and
d:\...\microsoft sql server\data\name_of_instance2
And also, will I need to run service packs for each named instance
separately ?
Yes, specify a different installation folder. There will be some shared
components though.
You have to install service packs/security patches/hot fixes for each
instance separately.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:hu8ie014lns9k5lrcgldb8erth0i2c0rn7@.4ax.com...
Can someone clarify for me, if I were to install 2 named instances of
SQL server 2000, whether I must define 2 different destination folders
for the program files ?
Or can I leave the Program Files destination folder the same for both
instances [c:\program files\Microsoft SQL Server] ?
I am separating the data files in their own data file destination
directory [d:\...\microsoft sql server\data\name_of_instance1 and
d:\...\microsoft sql server\data\name_of_instance2
And also, will I need to run service packs for each named instance
separately ?
|||just specify the \Program files\Microsoft SQL Server will be enough , it
will create a directory below there called MSSQL$<INSTANCEName> (default
will go in MSSQL).
re Service Packs , yes you need to apply seperately for each instance,
although certain components asre common across instances - ie MDAC and Full
Text Search
cheers
Andy
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:hu8ie014lns9k5lrcgldb8erth0i2c0rn7@.4ax.com...
> Can someone clarify for me, if I were to install 2 named instances of
> SQL server 2000, whether I must define 2 different destination folders
> for the program files ?
> Or can I leave the Program Files destination folder the same for both
> instances [c:\program files\Microsoft SQL Server] ?
> I am separating the data files in their own data file destination
> directory [d:\...\microsoft sql server\data\name_of_instance1 and
> d:\...\microsoft sql server\data\name_of_instance2
> And also, will I need to run service packs for each named instance
> separately ?
|||Okay, Andy's response is precise, for your first question :-)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGyegcnYEHA.2456@.TK2MSFTNGP10.phx.gbl...
Yes, specify a different installation folder. There will be some shared
components though.
You have to install service packs/security patches/hot fixes for each
instance separately.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:hu8ie014lns9k5lrcgldb8erth0i2c0rn7@.4ax.com...
Can someone clarify for me, if I were to install 2 named instances of
SQL server 2000, whether I must define 2 different destination folders
for the program files ?
Or can I leave the Program Files destination folder the same for both
instances [c:\program files\Microsoft SQL Server] ?
I am separating the data files in their own data file destination
directory [d:\...\microsoft sql server\data\name_of_instance1 and
d:\...\microsoft sql server\data\name_of_instance2
And also, will I need to run service packs for each named instance
separately ?
|||On Mon, 5 Jul 2004 11:03:05 +0100, "Andy Ball"
<andy.ball@.remove4spam_greenfell.com> wrote:

>just specify the \Program files\Microsoft SQL Server will be enough , it
>will create a directory below there called MSSQL$<INSTANCEName> (default
>will go in MSSQL).
>re Service Packs , yes you need to apply seperately for each instance,
>although certain components asre common across instances - ie MDAC and Full
>Text Search
>cheers
>Andy
>
That's correct! Thanks for such a fast reply. I just finished checking
it on a test server! SQL server handles this just fine.

Multiple instances SQL server 2000 - destination folders

Can someone clarify for me, if I were to install 2 named instances of
SQL server 2000, whether I must define 2 different destination folders
for the program files ?
Or can I leave the Program Files destination folder the same for both
instances [c:\program files\Microsoft SQL Server] ?
I am separating the data files in their own data file destination
directory [d:\...\microsoft sql server\data\name_of_instance1 and
d:\...\microsoft sql server\data\name_of_instance2
And also, will I need to run service packs for each named instance
separately ?Yes, specify a different installation folder. There will be some shared
components though.
You have to install service packs/security patches/hot fixes for each
instance separately.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:hu8ie014lns9k5lrcgldb8erth0i2c0rn7@.
4ax.com...
Can someone clarify for me, if I were to install 2 named instances of
SQL server 2000, whether I must define 2 different destination folders
for the program files ?
Or can I leave the Program Files destination folder the same for both
instances [c:\program files\Microsoft SQL Server] ?
I am separating the data files in their own data file destination
directory [d:\...\microsoft sql server\data\name_of_instance1 and
d:\...\microsoft sql server\data\name_of_instance2
And also, will I need to run service packs for each named instance
separately ?|||just specify the \Program files\Microsoft SQL Server will be enough , it
will create a directory below there called MSSQL$<INSTANCEName> (default
will go in MSSQL).
re Service Packs , yes you need to apply seperately for each instance,
although certain components asre common across instances - ie MDAC and Full
Text Search
cheers
Andy
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:hu8ie014lns9k5lrcgldb8erth0i2c0rn7@.
4ax.com...
> Can someone clarify for me, if I were to install 2 named instances of
> SQL server 2000, whether I must define 2 different destination folders
> for the program files ?
> Or can I leave the Program Files destination folder the same for both
> instances [c:\program files\Microsoft SQL Server] ?
> I am separating the data files in their own data file destination
> directory [d:\...\microsoft sql server\data\name_of_instance1 and
> d:\...\microsoft sql server\data\name_of_instance2
> And also, will I need to run service packs for each named instance
> separately ?|||Okay, Andy's response is precise, for your first question :-)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGyegcnYEHA.2456@.TK2MSFTNGP10.phx.gbl...
Yes, specify a different installation folder. There will be some shared
components though.
You have to install service packs/security patches/hot fixes for each
instance separately.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Citizen" <citizen_NOSPAM@.hotmail.com> wrote in message
news:hu8ie014lns9k5lrcgldb8erth0i2c0rn7@.
4ax.com...
Can someone clarify for me, if I were to install 2 named instances of
SQL server 2000, whether I must define 2 different destination folders
for the program files ?
Or can I leave the Program Files destination folder the same for both
instances [c:\program files\Microsoft SQL Server] ?
I am separating the data files in their own data file destination
directory [d:\...\microsoft sql server\data\name_of_instance1 and
d:\...\microsoft sql server\data\name_of_instance2
And also, will I need to run service packs for each named instance
separately ?|||On Mon, 5 Jul 2004 11:03:05 +0100, "Andy Ball"
<andy.ball@.remove4spam_greenfell.com> wrote:

>just specify the \Program files\Microsoft SQL Server will be enough , it
>will create a directory below there called MSSQL$<INSTANCEName> (default
>will go in MSSQL).
>re Service Packs , yes you need to apply seperately for each instance,
>although certain components asre common across instances - ie MDAC and Full
>Text Search
>cheers
>Andy
>
That's correct! Thanks for such a fast reply. I just finished checking
it on a test server! SQL server handles this just fine.