Is this a shortcut for an outer join and if so, why can't I find any info on it?
select id, name
from table1, table2
where id *= id_tbl2
instead of
select tb1.id, tb1.name
from table1 as tb1
outer join table2 as tb2
where tb1.id = tb2.id_tbl2
and what happens in the first example if you try to join more tables?http://en.wikipedia.org/wiki/Join_(SQL)
the first is an inner join.
the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?|||http://en.wikipedia.org/wiki/Join_(SQL)
the first is an inner join.
the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?
according to sql server, the first is an outer join.
you can verify that by attempting
select id
from table1
where id *= 1
and the error message is
Msg 150, Level 15, State 1, Line 5
Both terms of an outer join must contain columns.|||*= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!|||oops, I am sorry. that was something ms sql specific. I should not have answered that. I just didn't see the little * :/
sorry about that. well.. at least I learned something :)|||*= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!
Code I inherited.
I don't think the db admin knew exactly what he was doing, other than taking a shortcut to write less code.|||Its not a shortcut. Its an "old cut". It used to be the standard coding syntax, so far back in the mists of time that only wise men such as Pat Phelan know the origins.
Showing posts with label table1. Show all posts
Showing posts with label table1. Show all posts
Monday, March 26, 2012
outer join quesiont, pls help!
I have two tables. One (table1) is look up table that has 48 records for tim
e
interval. They are:
interval
0:00 - 0:30
0:30 - 1:00
1:00 - 1:30
:
:
23:00 - 23:30
23:30 - 24:00
The other table (table2) has real data. It looks like that:
Interval user value
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
15:00 - 15:30 user2 3
17:00 - 17:30 user2 2
23:00 - 23:30 user3 2
The result I need is
interval user value
00:00 - 00:30 user1 Null (since no data for user1
in this interval)
00:30 - 01:00 user1 Null
:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
:
23:00 - 23:30 user1 Null
23:30 - 24:00 user1 Null
I am trying to use left outer join to do it but it only return me:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
my query is
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval where user='user1'
Can somebody tell me what wrong is with my query. How to modify it to get
the result I need.
Thanks in advance!>> Can somebody tell me what wrong is with my query. How to modify it to get
Most likely moving the predicate in WHERE clause to ON clause will fix it.
Other wise, please read www.aspfaq.com/5006 and post DDL, sample data &
expected results in a usable format so that others can repro your problem
scenario.
Anith|||The problem is the where clause - you basically turn the outer join into an
inner join (not literally, but in effect).
Change it like this:
where (user = 'user1' or user is null)
ML|||you could try:
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval AND t2.user='user1'
"Jean" wrote:
> I have two tables. One (table1) is look up table that has 48 records for t
ime
> interval. They are:
> interval
> 0:00 - 0:30
> 0:30 - 1:00
> 1:00 - 1:30
> :
> :
> 23:00 - 23:30
> 23:30 - 24:00
> The other table (table2) has real data. It looks like that:
> Interval user value
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> 15:00 - 15:30 user2 3
> 17:00 - 17:30 user2 2
> 23:00 - 23:30 user3 2
> The result I need is
> interval user value
> 00:00 - 00:30 user1 Null (since no data for user1
> in this interval)
> 00:30 - 01:00 user1 Null
> :
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> :
> 23:00 - 23:30 user1 Null
> 23:30 - 24:00 user1 Null
> I am trying to use left outer join to do it but it only return me:
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> my query is
> select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
> t2 on
> t1.interval = t2.itnerval where user='user1'
> Can somebody tell me what wrong is with my query. How to modify it to get
> the result I need.
> Thanks in advance!
>|||It works! Thanks to you all!!!
e
interval. They are:
interval
0:00 - 0:30
0:30 - 1:00
1:00 - 1:30
:
:
23:00 - 23:30
23:30 - 24:00
The other table (table2) has real data. It looks like that:
Interval user value
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
15:00 - 15:30 user2 3
17:00 - 17:30 user2 2
23:00 - 23:30 user3 2
The result I need is
interval user value
00:00 - 00:30 user1 Null (since no data for user1
in this interval)
00:30 - 01:00 user1 Null
:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
:
23:00 - 23:30 user1 Null
23:30 - 24:00 user1 Null
I am trying to use left outer join to do it but it only return me:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
my query is
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval where user='user1'
Can somebody tell me what wrong is with my query. How to modify it to get
the result I need.
Thanks in advance!>> Can somebody tell me what wrong is with my query. How to modify it to get
Most likely moving the predicate in WHERE clause to ON clause will fix it.
Other wise, please read www.aspfaq.com/5006 and post DDL, sample data &
expected results in a usable format so that others can repro your problem
scenario.
Anith|||The problem is the where clause - you basically turn the outer join into an
inner join (not literally, but in effect).
Change it like this:
where (user = 'user1' or user is null)
ML|||you could try:
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval AND t2.user='user1'
"Jean" wrote:
> I have two tables. One (table1) is look up table that has 48 records for t
ime
> interval. They are:
> interval
> 0:00 - 0:30
> 0:30 - 1:00
> 1:00 - 1:30
> :
> :
> 23:00 - 23:30
> 23:30 - 24:00
> The other table (table2) has real data. It looks like that:
> Interval user value
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> 15:00 - 15:30 user2 3
> 17:00 - 17:30 user2 2
> 23:00 - 23:30 user3 2
> The result I need is
> interval user value
> 00:00 - 00:30 user1 Null (since no data for user1
> in this interval)
> 00:30 - 01:00 user1 Null
> :
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> :
> 23:00 - 23:30 user1 Null
> 23:30 - 24:00 user1 Null
> I am trying to use left outer join to do it but it only return me:
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> my query is
> select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
> t2 on
> t1.interval = t2.itnerval where user='user1'
> Can somebody tell me what wrong is with my query. How to modify it to get
> the result I need.
> Thanks in advance!
>|||It works! Thanks to you all!!!
OUTER JOIN issue
Hi all,
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)
I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:
What would probably perform better for you is this:
Check both execution plans/execution time in Query Analyzer to see which one performs better.
|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||
The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).
I have two tables (for example, table1, table2) where table1 holds thesame data as table2 but also has other rows that are no contained intable2.
Now if I performed the following query...
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
... I expect to get the rows that are not contained in both tables. Butfor some reason I don't get this. I get all the rows from tabel1.
Is my thinking of what theLEFT OUTER JOIN query wrong, or is the query wrong?
To get around my problem, I had to do the following
SELECT table1.name
FROM table1
WHERE table1.table_name not IN (SELECT table_name FROM table2)
I would have preferred to have solved this with theLEFT OUTER JOIN though
Thanks
Tryst
I think you are a little confused about outer joins...
Left/Right Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions
Say I have
Table1
Id Name Age
-----
1 a 20
2 b 30
3 c 22
Table2
Id Sex
---
1 M
2 F
Say I want id,name,age,sex(if available) then I would use left join
Select id,name,age,sex from table1 left join table2 on table1.id = table2.id
This will return
Id name age sex
------
1 a 20 M
2 b 30 F
3 c 22 <null>
|||You need to add a WHERE condition to return the results you want from the LEFT OUTER JOIN:
SELECT table1.name
FROM table1
LEFT OUTER JOIN table2 ON (RTRIM(LOWER(table1..table_name)) = RTRIM(LOWER(table2.table_name)))
WHERE table2.table_name IS NULL
What would probably perform better for you is this:
SELECT table1.name
FROM table1
WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.table_name = table2)
Check both execution plans/execution time in Query Analyzer to see which one performs better.
|||Ok, thanks guys.
Why is the inner SELECT query selecting NULL from table2? Whats it use?
Tryst
|||
Tryst wrote:
Why is the inner SELECT query selecting NULL from table2? Whats it use?
The list of column names is immaterial for the EXISTS. What isimportant is the WHERE condition. I have a habit of using NULL,partly because I read somewhere that it uses less resources than a * ora specific column (I am not sure whether or not that is true).
outer join in where clause
Hi all,
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):
select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'
Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.
Regards,
hmscott
Originally posted by Lambik
Hi all,
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as
Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!
The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.
Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'
I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):
select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'
Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.
Regards,
hmscott
Originally posted by Lambik
Hi all,
I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'
The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)
=*, a.property(+) doesn't work
Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as
Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!
The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.
Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'
I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.
Monday, February 20, 2012
OS error 5 in PULL Subscription
I am getting the error:
The process could not read file
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
sch' due to OS error 5. The step failed.
when trying to start a PULL subscription in SQL Server 7.0.
The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
rights given to Everyone.
Any ideas?
-WillError 5 is Access denied. So check both, NTFS and Share permissions, ant try
to connect there using Agents account.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Will T" <unknown@.nowhere.net> wrote in message
news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> I am getting the error:
> The process could not read file
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
> sch' due to OS error 5. The step failed.
> when trying to start a PULL subscription in SQL Server 7.0.
> The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
> rights given to Everyone.
> Any ideas?
> -Will
>|||I am connecting using the sa account on the Distributor. What NT account
would that be?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OmSaYNiCFHA.1392@.tk2msftngp13.phx.gbl...
> Error 5 is Access denied. So check both, NTFS and Share permissions, ant
try
> to connect there using Agents account.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Will T" <unknown@.nowhere.net> wrote in message
> news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> > I am getting the error:
> >
> > The process could not read file
> >
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
> > sch' due to OS error 5. The step failed.
> >
> > when trying to start a PULL subscription in SQL Server 7.0.
> >
> > The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
> > rights given to Everyone.
> >
> > Any ideas?
> >
> > -Will
> >
> >
>|||Is that a hidden public share or is it the admin share? It
looks like an admin share and if it's an administrative
share, the account needs to be an admin on the box to access
the share. Or you need to create a public share with the
appropriate rights, permissions.
-Sue
On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
<unknown@.nowhere.net> wrote:
>I am getting the error:
>The process could not read file
>'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
>sch' due to OS error 5. The step failed.
>when trying to start a PULL subscription in SQL Server 7.0.
>The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
>rights given to Everyone.
>Any ideas?
>-Will
>|||Will,
Dejan is referring to the SQL Server Agent on your subscriber. That is a
service running as a domain user which should have the right to read files
from the distribution working folder. Log on to the subscriber using this
same account and try to read files from the DWF to double-check that rights
are set up correctly.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Well spotted Sue,
- Will, typically you'd share \\MYSERVER\ReplData
Rgds,
Paul Ibison|||Thanks for the clarification. I will look in to it.
Thanks,
Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||The '\\MYSERVER\ReplData' is shared to Everyone. Thanks.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b505011s7n93vo5p6cd8v6hin7e6ho8013@.4ax.com...
> Is that a hidden public share or is it the admin share? It
> looks like an admin share and if it's an administrative
> share, the account needs to be an admin on the box to access
> the share. Or you need to create a public share with the
> appropriate rights, permissions.
> -Sue
> On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
> <unknown@.nowhere.net> wrote:
> >I am getting the error:
> >
> >The process could not read file
>'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1
.
> >sch' due to OS error 5. The step failed.
> >
> >when trying to start a PULL subscription in SQL Server 7.0.
> >
> >The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
> >rights given to Everyone.
> >
> >Any ideas?
> >
> >-Will
> >
>|||Thank you all for the information. The account on the local MSDE machine was
only a local account. I changed it to an account on the network and it now
works fine.
Q: is it standard practice to make an accoutn just for this type of thing?
-Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||If you need the SQL Server to access anything outside of its own process eg
mail, reporting services, replication, then a domain user is mandatory. We
tend to have a separate domain user account for each server, although we
have some non-trusted domains so this is mandatory. This account is a local
administrator, but not a domain admin. I have also seen places where the
same account is used for each sql server. In part I guess it depends on how
securely you guard your passwords - in the one-for-all scenario, if the
password is compromised, you lose control of all your servers.
HTH,
Paul Ibison (SQL Server MVP)
The process could not read file
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
sch' due to OS error 5. The step failed.
when trying to start a PULL subscription in SQL Server 7.0.
The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
rights given to Everyone.
Any ideas?
-WillError 5 is Access denied. So check both, NTFS and Share permissions, ant try
to connect there using Agents account.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Will T" <unknown@.nowhere.net> wrote in message
news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> I am getting the error:
> The process could not read file
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
> sch' due to OS error 5. The step failed.
> when trying to start a PULL subscription in SQL Server 7.0.
> The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
> rights given to Everyone.
> Any ideas?
> -Will
>|||I am connecting using the sa account on the Distributor. What NT account
would that be?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OmSaYNiCFHA.1392@.tk2msftngp13.phx.gbl...
> Error 5 is Access denied. So check both, NTFS and Share permissions, ant
try
> to connect there using Agents account.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Will T" <unknown@.nowhere.net> wrote in message
> news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> > I am getting the error:
> >
> > The process could not read file
> >
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
> > sch' due to OS error 5. The step failed.
> >
> > when trying to start a PULL subscription in SQL Server 7.0.
> >
> > The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
> > rights given to Everyone.
> >
> > Any ideas?
> >
> > -Will
> >
> >
>|||Is that a hidden public share or is it the admin share? It
looks like an admin share and if it's an administrative
share, the account needs to be an admin on the box to access
the share. Or you need to create a public share with the
appropriate rights, permissions.
-Sue
On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
<unknown@.nowhere.net> wrote:
>I am getting the error:
>The process could not read file
>'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1.
>sch' due to OS error 5. The step failed.
>when trying to start a PULL subscription in SQL Server 7.0.
>The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
>rights given to Everyone.
>Any ideas?
>-Will
>|||Will,
Dejan is referring to the SQL Server Agent on your subscriber. That is a
service running as a domain user which should have the right to read files
from the distribution working folder. Log on to the subscriber using this
same account and try to read files from the DWF to double-check that rights
are set up correctly.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Well spotted Sue,
- Will, typically you'd share \\MYSERVER\ReplData
Rgds,
Paul Ibison|||Thanks for the clarification. I will look in to it.
Thanks,
Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||The '\\MYSERVER\ReplData' is shared to Everyone. Thanks.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b505011s7n93vo5p6cd8v6hin7e6ho8013@.4ax.com...
> Is that a hidden public share or is it the admin share? It
> looks like an admin share and if it's an administrative
> share, the account needs to be an admin on the box to access
> the share. Or you need to create a public share with the
> appropriate rights, permissions.
> -Sue
> On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
> <unknown@.nowhere.net> wrote:
> >I am getting the error:
> >
> >The process could not read file
>'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1\20050203112524\Table1
.
> >sch' due to OS error 5. The step failed.
> >
> >when trying to start a PULL subscription in SQL Server 7.0.
> >
> >The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSERVER_PUB1_PUB1' path has all
> >rights given to Everyone.
> >
> >Any ideas?
> >
> >-Will
> >
>|||Thank you all for the information. The account on the local MSDE machine was
only a local account. I changed it to an account on the network and it now
works fine.
Q: is it standard practice to make an accoutn just for this type of thing?
-Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||If you need the SQL Server to access anything outside of its own process eg
mail, reporting services, replication, then a domain user is mandatory. We
tend to have a separate domain user account for each server, although we
have some non-trusted domains so this is mandatory. This account is a local
administrator, but not a domain admin. I have also seen places where the
same account is used for each sql server. In part I guess it depends on how
securely you guard your passwords - in the one-for-all scenario, if the
password is compromised, you lose control of all your servers.
HTH,
Paul Ibison (SQL Server MVP)
OS error 5 in PULL Subscription
I am getting the error:
The process could not read file
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1.
sch' due to OS error 5. The step failed.
when trying to start a PULL subscription in SQL Server 7.0.
The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1' path has all
rights given to Everyone.
Any ideas?
-WillError 5 is Access denied. So check both, NTFS and Share permissions, ant try
to connect there using Agents account.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Will T" <unknown@.nowhere.net> wrote in message
news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> I am getting the error:
> The process could not read file
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1.
> sch' due to OS error 5. The step failed.
> when trying to start a PULL subscription in SQL Server 7.0.
> The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1' path has all
> rights given to Everyone.
> Any ideas?
> -Will
>|||I am connecting using the sa account on the Distributor. What NT account
would that be?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OmSaYNiCFHA.1392@.tk2msftngp13.phx.gbl...
> Error 5 is Access denied. So check both, NTFS and Share permissions, ant
try
> to connect there using Agents account.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Will T" <unknown@.nowhere.net> wrote in message
> news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1.
>|||Is that a hidden public share or is it the admin share? It
looks like an admin share and if it's an administrative
share, the account needs to be an admin on the box to access
the share. Or you need to create a public share with the
appropriate rights, permissions.
-Sue
On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
<unknown@.nowhere.net> wrote:
>I am getting the error:
>The process could not read file
> '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1
.
>sch' due to OS error 5. The step failed.
>when trying to start a PULL subscription in SQL Server 7.0.
>The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1' path has all
>rights given to Everyone.
>Any ideas?
>-Will
>|||Will,
Dejan is referring to the SQL Server Agent on your subscriber. That is a
service running as a domain user which should have the right to read files
from the distribution working folder. Log on to the subscriber using this
same account and try to read files from the DWF to double-check that rights
are set up correctly.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Well spotted Sue,
- Will, typically you'd share \\MYSERVER\ReplData
Rgds,
Paul Ibison|||Thanks for the clarification. I will look in to it.
Thanks,
Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||The '\\MYSERVER\ReplData' is shared to Everyone. Thanks.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b505011s7n93vo5p6cd8v6hin7e6ho8013@.
4ax.com...
> Is that a hidden public share or is it the admin share? It
> looks like an admin share and if it's an administrative
> share, the account needs to be an admin on the box to access
> the share. Or you need to create a public share with the
> appropriate rights, permissions.
> -Sue
> On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
> <unknown@.nowhere.net> wrote:
>
> '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1
.
>|||Thank you all for the information. The account on the local MSDE machine was
only a local account. I changed it to an account on the network and it now
works fine.
Q: is it standard practice to make an accoutn just for this type of thing?
-Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||If you need the SQL Server to access anything outside of its own process eg
mail, reporting services, replication, then a domain user is mandatory. We
tend to have a separate domain user account for each server, although we
have some non-trusted domains so this is mandatory. This account is a local
administrator, but not a domain admin. I have also seen places where the
same account is used for each sql server. In part I guess it depends on how
securely you guard your passwords - in the one-for-all scenario, if the
password is compromised, you lose control of all your servers.
HTH,
Paul Ibison (SQL Server MVP)
The process could not read file
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1.
sch' due to OS error 5. The step failed.
when trying to start a PULL subscription in SQL Server 7.0.
The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1' path has all
rights given to Everyone.
Any ideas?
-WillError 5 is Access denied. So check both, NTFS and Share permissions, ant try
to connect there using Agents account.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Will T" <unknown@.nowhere.net> wrote in message
news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> I am getting the error:
> The process could not read file
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1.
> sch' due to OS error 5. The step failed.
> when trying to start a PULL subscription in SQL Server 7.0.
> The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1' path has all
> rights given to Everyone.
> Any ideas?
> -Will
>|||I am connecting using the sa account on the Distributor. What NT account
would that be?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OmSaYNiCFHA.1392@.tk2msftngp13.phx.gbl...
> Error 5 is Access denied. So check both, NTFS and Share permissions, ant
try
> to connect there using Agents account.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Will T" <unknown@.nowhere.net> wrote in message
> news:O0cODFiCFHA.3280@.TK2MSFTNGP14.phx.gbl...
>
'\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1.
>|||Is that a hidden public share or is it the admin share? It
looks like an admin share and if it's an administrative
share, the account needs to be an admin on the box to access
the share. Or you need to create a public share with the
appropriate rights, permissions.
-Sue
On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
<unknown@.nowhere.net> wrote:
>I am getting the error:
>The process could not read file
> '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1
.
>sch' due to OS error 5. The step failed.
>when trying to start a PULL subscription in SQL Server 7.0.
>The '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1' path has all
>rights given to Everyone.
>Any ideas?
>-Will
>|||Will,
Dejan is referring to the SQL Server Agent on your subscriber. That is a
service running as a domain user which should have the right to read files
from the distribution working folder. Log on to the subscriber using this
same account and try to read files from the DWF to double-check that rights
are set up correctly.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Well spotted Sue,
- Will, typically you'd share \\MYSERVER\ReplData
Rgds,
Paul Ibison|||Thanks for the clarification. I will look in to it.
Thanks,
Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||The '\\MYSERVER\ReplData' is shared to Everyone. Thanks.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b505011s7n93vo5p6cd8v6hin7e6ho8013@.
4ax.com...
> Is that a hidden public share or is it the admin share? It
> looks like an admin share and if it's an administrative
> share, the account needs to be an admin on the box to access
> the share. Or you need to create a public share with the
> appropriate rights, permissions.
> -Sue
> On Thu, 3 Feb 2005 13:50:40 -0500, "Will T"
> <unknown@.nowhere.net> wrote:
>
> '\\MYSERVER\D$\MSSQL7\ReplData\unc\MYSER
VER_PUB1_PUB1\20050203112524\Table1
.
>|||Thank you all for the information. The account on the local MSDE machine was
only a local account. I changed it to an account on the network and it now
works fine.
Q: is it standard practice to make an accoutn just for this type of thing?
-Will
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uC$9qAjCFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Will,
> Dejan is referring to the SQL Server Agent on your subscriber. That is a
> service running as a domain user which should have the right to read files
> from the distribution working folder. Log on to the subscriber using this
> same account and try to read files from the DWF to double-check that
rights
> are set up correctly.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||If you need the SQL Server to access anything outside of its own process eg
mail, reporting services, replication, then a domain user is mandatory. We
tend to have a separate domain user account for each server, although we
have some non-trusted domains so this is mandatory. This account is a local
administrator, but not a domain admin. I have also seen places where the
same account is used for each sql server. In part I guess it depends on how
securely you guard your passwords - in the one-for-all scenario, if the
password is compromised, you lose control of all your servers.
HTH,
Paul Ibison (SQL Server MVP)
Subscribe to:
Posts (Atom)