it's about the Maintance plan clean up task...in sql2k, you can just
specify all system databases in maintenance plan and purge files older
than a specified time (i.e. 2 days old), but in 2k5, there is a
seperate maintenance clean up task, you can't specify the database
(i.e. all system dbs) or directory (and include sub folders). does that
mean I have to create 3 tasks just to delete old files for master,
msdb, and model? that would be really dumb, or am I missing something?
Thank you.I have the exact same question.
Anybody can help ?
=== Steve L === wrote:
> it's about the Maintance plan clean up task...in sql2k, you can just
> specify all system databases in maintenance plan and purge files older
> than a specified time (i.e. 2 days old), but in 2k5, there is a
> seperate maintenance clean up task, you can't specify the database
> (i.e. all system dbs) or directory (and include sub folders). does that
> mean I have to create 3 tasks just to delete old files for master,
> msdb, and model? that would be really dumb, or am I missing something?
> Thank you.|||That is the way it is designed. Consider proposing a change at
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<xiaoyuh.huang@.gmail.com> wrote in message
news:1143835327.889394.267740@.z34g2000cwc.googlegroups.com...
>I have the exact same question.
> Anybody can help ?
> === Steve L === wrote:
>
Friday, March 30, 2012
Question about SQL 2000 TLOG replication
Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!?! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.
I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!?! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.
|||answers inline.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
> Sorry the questions are 4 :-)
>
;)
>
> No i would schedule the agent for example every week at 02:00
>
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
> Ok thanks.
>
> Ok thanks.
> Thanks in advance.
>
sql
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!?! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.
I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!?! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.
|||answers inline.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
> Sorry the questions are 4 :-)
>
;)
>
> No i would schedule the agent for example every week at 02:00
>
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
> Ok thanks.
>
> Ok thanks.
> Thanks in advance.
>
sql
Question about SQL 2000 TLOG replication
Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>>I count 4 questions not 2, but whose counting?
> Sorry the questions are 4 :-)
>
;)
>> 1) This won't work unless you set the history retention to 14 days or so.
>> Once you do this you will find performance degrades after several days
>> depending on your load. Ideally you would run the agents continually.
> No i would schedule the agent for example every week at 02:00
>> 2) What do you mean by the tail of replication?
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
>> 3) if the data you enter on the subscriber conflicts with the data
>> entered on the publisher it will cause a problem. Use the continue on
>> data consistency error profiler to skip this.
> Ok thanks.
>> 4) This should work ok. Replication will work fine after a truncate.
> Ok thanks.
> Thanks in advance.
>
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>>I count 4 questions not 2, but whose counting?
> Sorry the questions are 4 :-)
>
;)
>> 1) This won't work unless you set the history retention to 14 days or so.
>> Once you do this you will find performance degrades after several days
>> depending on your load. Ideally you would run the agents continually.
> No i would schedule the agent for example every week at 02:00
>> 2) What do you mean by the tail of replication?
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
>> 3) if the data you enter on the subscriber conflicts with the data
>> entered on the publisher it will cause a problem. Use the continue on
>> data consistency error profiler to skip this.
> Ok thanks.
>> 4) This should work ok. Replication will work fine after a truncate.
> Ok thanks.
> Thanks in advance.
>
Question about SQL 2000 TLOG replication
Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
> Sorry the questions are 4 :-)
>
;)
>
> No i would schedule the agent for example every week at 02:00
>
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
> Ok thanks.
>
> Ok thanks.
> Thanks in advance.
>
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
> Sorry the questions are 4 :-)
>
;)
>
> No i would schedule the agent for example every week at 02:00
>
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
> Ok thanks.
>
> Ok thanks.
> Thanks in advance.
>
Question about SQL
Hi all,
I have created a photo album in my local computer with [Microsoft Visual Web Developer 2005 Express Edition] my data base is SQL I built it with the same program. Also, I used the ASP.net Configuration to setup the user's account, every thing working 100% in my local computer.
I have an account with www. Hostonce .com they offer and support MS SQL 2005 and ASP.net, and they gave me an IP number to access to my MSSQL server with [SQL Server Management Studio Express], I do access when I put the IP address and the password, but I have no idea how to move the entire project with the SQL files to my server. I tried to transfer all my files with FTP, but it didn't work, they told me I have to transfer my SQL to the same IP address and then I have to do the connection things in my pages.
Please Help.
Thank You
I think they support only MS SQL 2005 , but would you mind to tell me how i can solve this problem and how i can migrate the database from Express Edition to the MS SQL 2005 ?
Thank You,
Question about sproc performance?
whats the difference in performance between these two scenarioes...
Suppose if i build a sproc to insert or update into one table and then take its primary key and insert and update subsequent tables may one or 2 or more take more time in exceuting...
or
writing indivdual sproc for insert and update for particular table in different sproc and then calling all the sprocs in a master proc using exec sproc command...
Which scenario would better if they are around 230 or 300 or more than records.
Hope some would shed some light into this..
Any help is appreciated...
Regards
Karen
The best advise I can give is to build both of them. You can then look at the individual execution plans of them both and determine which is the better approach for your database. It should also help to highlight if any indexes need to be added to your tables.
I dont think it will be too much of a noticeable difference.. unless you have some very complex logic involving several IF loops which radically changes the WHERE conditions in the query.
|||Thanks Dinakar...
I just plain inserts and updates...to different tables... and in the above scenario which is best approach to take?
Regards
Karen
|||
If its plain INSERTs/UPDATEs its more of a convenience and easier maintenance that you should decide based on rather than performance. I would create one proc for each table that takes care of INSERT and UPDATE (Depending on whether record exists or not) so you can re-use the procs. If you put multiple tables related INSERTs into one proc and later if you had to do an INSERTt/UPDATE into one of those 2 tables, you'd be re-writing the same logic into another proc again. If you created a separate proc for the table now, you could just call the proc later.
question about sp_generate_inserts (Vyas's SP)
Hello,
I compiled Vyas's SP sp_generate_inserts
(http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectly
on my test servers - all except one server. On this server, I get the
following errors
Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 332
Invalid length parameter passed to the substring function.
Server: Msg 536, Level 16, State 1, Procedure sp_generate_inserts1, Line 333
Invalid length parameter passed to the substring function.
Server: Msg 50000, Level 16, State 1, Procedure sp_generate_inserts1, Line 3
37
No columns to select. There should at least be one column to generate the
output
(I compiled the SP onto the master database and am executing the SP as
EXEC sp_generate_inserts1 @.table_name='MyTableName') in the context of the
database where "MytableName" exists
The following statement seems to be returning a NULL (I modified the SP to
display @.Column_ID)
SELECT @.Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @.table_name AND
(@.owner IS NULL OR TABLE_SCHEMA = @.owner)
In fact, when I put the following SELECT
SELECT top 10 * from INFORMATION_SCHEMA.COLUMNS (NOLOCK)
in place of the original SELECT (above), I get rows with
TABLE_CATALOG='Master'
It almost looks like the SP is selecting from the Master database instead of
the user database that I have selected.
If I change the SELECT to hardcode the database name (as shown below), the
insert statements are generated correctly!
SELECT @.Column_ID = MIN(ORDINAL_POSITION)
FROM <myDBNAME>.INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @.table_name AND
(@.owner IS NULL OR TABLE_SCHEMA = @.owner)
I have double checked to verify that I am running the EXEC in the correct
database (not the master database) and have also confirmed that the
sp_generate_inserts doesn't exist in any other database.
This SP works perfectly on two other servers that I have tried this on, so I
am baffled!
Any suggestions on how to debug this?
Thanks!Hi
This sounds like you need to call
EXEC sp_MS_marksystemobject sp_generate_inserts1
Have you checked
SELECT OBJECTPROPERTY ( OBJECT_ID(sp_generate_inserts1), 'IsMSShipped' )
John
"Bob" wrote:
> Hello,
> I compiled Vyas's SP sp_generate_inserts
> (http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectl
y
> on my test servers - all except one server. On this server, I get the
> following errors
> Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 3
32
> Invalid length parameter passed to the substring function.
> Server: Msg 536, Level 16, State 1, Procedure sp_generate_inserts1, Line 3
33
> Invalid length parameter passed to the substring function.
> Server: Msg 50000, Level 16, State 1, Procedure sp_generate_inserts1, Line
337
> No columns to select. There should at least be one column to generate the
> output
> (I compiled the SP onto the master database and am executing the SP as
> EXEC sp_generate_inserts1 @.table_name='MyTableName') in the context of the
> database where "MytableName" exists
> The following statement seems to be returning a NULL (I modified the SP to
> display @.Column_ID)
> SELECT @.Column_ID = MIN(ORDINAL_POSITION)
> FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> WHERE TABLE_NAME = @.table_name AND
> (@.owner IS NULL OR TABLE_SCHEMA = @.owner)
> In fact, when I put the following SELECT
> SELECT top 10 * from INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> in place of the original SELECT (above), I get rows with
> TABLE_CATALOG='Master'
> It almost looks like the SP is selecting from the Master database instead
of
> the user database that I have selected.
> If I change the SELECT to hardcode the database name (as shown below), the
> insert statements are generated correctly!
> SELECT @.Column_ID = MIN(ORDINAL_POSITION)
> FROM <myDBNAME>.INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> WHERE TABLE_NAME = @.table_name AND
> (@.owner IS NULL OR TABLE_SCHEMA = @.owner)
> I have double checked to verify that I am running the EXEC in the correct
> database (not the master database) and have also confirmed that the
> sp_generate_inserts doesn't exist in any other database.
> This SP works perfectly on two other servers that I have tried this on, so
I
> am baffled!
> Any suggestions on how to debug this?
> Thanks!|||Thank you John! That resolved it! I now renamed the SP back to
sp_generate_insert (I had added the 1 at the end to make sure that the SP
didn't exist anywhere else).
and it is working!
Just curious - Normally, shouldn't this be set to a system object when I
compile it into the master database? (it worked on my other servers without
having to call sp_MS_marksystemobject)
Thanks again!
"John Bell" wrote:
> Hi
> This sounds like you need to call
> EXEC sp_MS_marksystemobject sp_generate_inserts1
> Have you checked
> SELECT OBJECTPROPERTY ( OBJECT_ID(sp_generate_inserts1), 'IsMSShipped' )
> John
> "Bob" wrote:
>|||Hi
The install script uses another undocumented procedure
sp_MS_upd_sysobj_category
see http://tinyurl.com/bxpyp for an explanation.
sp_MS_upd_sysobj_category uses trace flag 1717 so that when you create the
procedure the MSShipped bit is automagically set, sp_MS_marksystemobject
updates sysobjects directly.
John
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:2AA1210D-1636-435C-AAE0-D4D0C8C243DE@.microsoft.com...
> Thank you John! That resolved it! I now renamed the SP back to
> sp_generate_insert (I had added the 1 at the end to make sure that the SP
> didn't exist anywhere else).
> and it is working!
> Just curious - Normally, shouldn't this be set to a system object when I
> compile it into the master database? (it worked on my other servers
> without
> having to call sp_MS_marksystemobject)
> Thanks again!
> "John Bell" wrote:
>sql
I compiled Vyas's SP sp_generate_inserts
(http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectly
on my test servers - all except one server. On this server, I get the
following errors
Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 332
Invalid length parameter passed to the substring function.
Server: Msg 536, Level 16, State 1, Procedure sp_generate_inserts1, Line 333
Invalid length parameter passed to the substring function.
Server: Msg 50000, Level 16, State 1, Procedure sp_generate_inserts1, Line 3
37
No columns to select. There should at least be one column to generate the
output
(I compiled the SP onto the master database and am executing the SP as
EXEC sp_generate_inserts1 @.table_name='MyTableName') in the context of the
database where "MytableName" exists
The following statement seems to be returning a NULL (I modified the SP to
display @.Column_ID)
SELECT @.Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @.table_name AND
(@.owner IS NULL OR TABLE_SCHEMA = @.owner)
In fact, when I put the following SELECT
SELECT top 10 * from INFORMATION_SCHEMA.COLUMNS (NOLOCK)
in place of the original SELECT (above), I get rows with
TABLE_CATALOG='Master'
It almost looks like the SP is selecting from the Master database instead of
the user database that I have selected.
If I change the SELECT to hardcode the database name (as shown below), the
insert statements are generated correctly!
SELECT @.Column_ID = MIN(ORDINAL_POSITION)
FROM <myDBNAME>.INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @.table_name AND
(@.owner IS NULL OR TABLE_SCHEMA = @.owner)
I have double checked to verify that I am running the EXEC in the correct
database (not the master database) and have also confirmed that the
sp_generate_inserts doesn't exist in any other database.
This SP works perfectly on two other servers that I have tried this on, so I
am baffled!
Any suggestions on how to debug this?
Thanks!Hi
This sounds like you need to call
EXEC sp_MS_marksystemobject sp_generate_inserts1
Have you checked
SELECT OBJECTPROPERTY ( OBJECT_ID(sp_generate_inserts1), 'IsMSShipped' )
John
"Bob" wrote:
> Hello,
> I compiled Vyas's SP sp_generate_inserts
> (http://vyaskn.tripod.com/code/generate_inserts.txt) and it works perfectl
y
> on my test servers - all except one server. On this server, I get the
> following errors
> Server: Msg 536, Level 16, State 3, Procedure sp_generate_inserts1, Line 3
32
> Invalid length parameter passed to the substring function.
> Server: Msg 536, Level 16, State 1, Procedure sp_generate_inserts1, Line 3
33
> Invalid length parameter passed to the substring function.
> Server: Msg 50000, Level 16, State 1, Procedure sp_generate_inserts1, Line
337
> No columns to select. There should at least be one column to generate the
> output
> (I compiled the SP onto the master database and am executing the SP as
> EXEC sp_generate_inserts1 @.table_name='MyTableName') in the context of the
> database where "MytableName" exists
> The following statement seems to be returning a NULL (I modified the SP to
> display @.Column_ID)
> SELECT @.Column_ID = MIN(ORDINAL_POSITION)
> FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> WHERE TABLE_NAME = @.table_name AND
> (@.owner IS NULL OR TABLE_SCHEMA = @.owner)
> In fact, when I put the following SELECT
> SELECT top 10 * from INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> in place of the original SELECT (above), I get rows with
> TABLE_CATALOG='Master'
> It almost looks like the SP is selecting from the Master database instead
of
> the user database that I have selected.
> If I change the SELECT to hardcode the database name (as shown below), the
> insert statements are generated correctly!
> SELECT @.Column_ID = MIN(ORDINAL_POSITION)
> FROM <myDBNAME>.INFORMATION_SCHEMA.COLUMNS (NOLOCK)
> WHERE TABLE_NAME = @.table_name AND
> (@.owner IS NULL OR TABLE_SCHEMA = @.owner)
> I have double checked to verify that I am running the EXEC in the correct
> database (not the master database) and have also confirmed that the
> sp_generate_inserts doesn't exist in any other database.
> This SP works perfectly on two other servers that I have tried this on, so
I
> am baffled!
> Any suggestions on how to debug this?
> Thanks!|||Thank you John! That resolved it! I now renamed the SP back to
sp_generate_insert (I had added the 1 at the end to make sure that the SP
didn't exist anywhere else).
and it is working!
Just curious - Normally, shouldn't this be set to a system object when I
compile it into the master database? (it worked on my other servers without
having to call sp_MS_marksystemobject)
Thanks again!
"John Bell" wrote:
> Hi
> This sounds like you need to call
> EXEC sp_MS_marksystemobject sp_generate_inserts1
> Have you checked
> SELECT OBJECTPROPERTY ( OBJECT_ID(sp_generate_inserts1), 'IsMSShipped' )
> John
> "Bob" wrote:
>|||Hi
The install script uses another undocumented procedure
sp_MS_upd_sysobj_category
see http://tinyurl.com/bxpyp for an explanation.
sp_MS_upd_sysobj_category uses trace flag 1717 so that when you create the
procedure the MSShipped bit is automagically set, sp_MS_marksystemobject
updates sysobjects directly.
John
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:2AA1210D-1636-435C-AAE0-D4D0C8C243DE@.microsoft.com...
> Thank you John! That resolved it! I now renamed the SP back to
> sp_generate_insert (I had added the 1 at the end to make sure that the SP
> didn't exist anywhere else).
> and it is working!
> Just curious - Normally, shouldn't this be set to a system object when I
> compile it into the master database? (it worked on my other servers
> without
> having to call sp_MS_marksystemobject)
> Thanks again!
> "John Bell" wrote:
>sql
Question about sp_executesql
Hi all,
I just wanted to know why this doesn't work: if @.1's values is computer
--------------------------------
BEGIN
FETCH NEXT FROM keyword_cursor into @.1
SELECT @.sql = @.sql + 'where title LIKE ' +'''%@.x1%''' + ' OR notes like ' +'''%@.x1%'''
SELECT @.paramlist ='@.x1 nvarchar(200)'
print @.sql
EXEC sp_executesql @.sql, @.paramlist, @.1
RETURN 0
END
The @.sql string evaluates to:
select title, notes from pubs..titles where title LIKE'%@.x1%' OR notes like'%@.x1%'
--------------------------------
But this works:
BEGIN
FETCH NEXT FROM keyword_cursor into @.1
SELECT @.sql = @.sql + 'where title LIKE ''%''+ @.x1 + ''%'' OR notes like ''%'' + @.x1 + ''%'''
SELECT @.paramlist ='@.x1 nvarchar(200)'
print @.sql
EXEC sp_executesql @.sql, @.paramlist, @.1
RETURN 0
END
The @.sql string evaluates to:
select title, notes from pubs..titles where title LIKE '%'+ @.x1 + '%' OR notes like '%' + @.x1 + '%'
----------------------------------
I just don't get it ?? Doesn't sp_executesql just replaces the @.x1 with @.1?
select title, notes from pubs..titles where title LIKE'%@.x1%' OR notes like'%@.x1%'
look at that for instance. If you ran that in query analyzer, itwould not replace '%@.x1%' with anything because it's infact in thestring. @.x1 should be treated as a variable, and must be outsideof any string delimiter such as '.
Another example just in case is..
Dim strName as String = "KraGiE"
Response.Write("hello strName")
That would not replace strName with "KraGiE" because it's not being used as a variable.
|||Hi KraGie,
Thanks for your reply and explaination. Now i understand why the string "select title, notes from pubs..titles where title LIKE'%@.x1%' OR notes like'%@.x1%'". But i still don't get why the second string "select title, notes from pubs..titles where title LIKE'%' + @.x1 + '%' OR notes like'%' + @.x1 + '%'" will work. Because after the replacement of the variable @.x1 with "computer" then it will evaluate to select title, notes from pubs..titles where title LIKE %computer% OR notes like %computer% right? without the single quotes outside the percentage signs won't the statement generate an error??
Question about sp_addscriptexec
Hi,
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:
> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]
I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
I have multiple named pull subscribers to a Transactional replication model.
I have written a script that adds and drops fields from several tables using
sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
script that are removing tables from the replication model. I also have some
commands that are adding new tables to the replication model.
After the commands that are adding and removing the fields and tables, I am
issuing a sp_refreshsubscriptions.
I am then starting the snapshot agent so that the subscribers will get the
new tables that I have added.
I then issue an sp_addscriptexec to run a script on the subscriber.
My question is why does this script that I am executing via the
sp_addscriptexec run on the subscriber before the snapshot ever gets applied
to the subscriber? I need to have the script run after the snapshot has been
created and delivered to the subscriber.
Thanks in advance,
Stephen
Probably because the sp_addscriptexec is added to the distribution database
before the snapshot is generated and the sync commands make it there, You
should perhaps use the post snapshot command for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> Hi,
> I'm running SQL Server 2000 EE SP3 on Windows Server 2003.
> I have multiple named pull subscribers to a Transactional replication
model.
> I have written a script that adds and drops fields from several tables
using
> sp_repladdcolumn and sp_repldropcolumn. I also have some commands in my
> script that are removing tables from the replication model. I also have
some
> commands that are adding new tables to the replication model.
> After the commands that are adding and removing the fields and tables, I
am
> issuing a sp_refreshsubscriptions.
> I am then starting the snapshot agent so that the subscribers will get the
> new tables that I have added.
> I then issue an sp_addscriptexec to run a script on the subscriber.
> My question is why does this script that I am executing via the
> sp_addscriptexec run on the subscriber before the snapshot ever gets
applied
> to the subscriber? I need to have the script run after the snapshot has
been
> created and delivered to the subscriber.
> Thanks in advance,
> Stephen
|||I already have a post snapshot command tied to my replication publication.
Since I am already replicating to the subscriber and just adding new tables
and fields, it seems as though the post snapshot command that was orginally
tied to the publication does not get run. I'm saying this as I already have
a post snapshot script applied to my publication and it is not getting run
when I regen the snapshot to create the definitions of the new tables.
So, I do not think that adding the additional script as a post script will
get run either, but in any case, I do not want to tie this script to the
publication definition as I do not want this script to be run whenever a
snapshot has been applied.
Thanks again,
Stephen
"Hilary Cotter" wrote:
> Probably because the sp_addscriptexec is added to the distribution database
> before the snapshot is generated and the sync commands make it there, You
> should perhaps use the post snapshot command for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
> message news:9CD0B3BB-6EE6-46DF-8CDE-C8B860890EE1@.microsoft.com...
> model.
> using
> some
> am
> applied
> been
>
>
|||You might want to set your distribution job to run scheduled as opposed to
continuous, stop the log reader agent, and then have the sp_addscriptexec
command run as your final job step. Stop and start your distribution agent.
After the distribution agent has stopped, remove this last step, start up
your log reader agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stephen Schissler" <StephenSchissler@.discussions.microsoft.com> wrote in
message news:E7C9D59C-D82B-412E-9B5B-55DA93574C79@.microsoft.com...
> I already have a post snapshot command tied to my replication publication.
> Since I am already replicating to the subscriber and just adding new
tables
> and fields, it seems as though the post snapshot command that was
orginally
> tied to the publication does not get run. I'm saying this as I already
have[vbcol=seagreen]
> a post snapshot script applied to my publication and it is not getting run
> when I regen the snapshot to create the definitions of the new tables.
> So, I do not think that adding the additional script as a post script will
> get run either, but in any case, I do not want to tie this script to the
> publication definition as I do not want this script to be run whenever a
> snapshot has been applied.
> Thanks again,
> Stephen
> "Hilary Cotter" wrote:
database[vbcol=seagreen]
You[vbcol=seagreen]
in[vbcol=seagreen]
my[vbcol=seagreen]
have[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]
Question about snapshot replication and monitoring of msrepl_transactions in the dist.db
OK.
We have this 1 publisher db that is pushing out to two separate
subscribers (one is transactional the other is snapshot). In the
distribution db the publisher_database_id is going to be the same. We
monitor this transaction count by publisher id with the following (
Select count(1) from dbo.MSrepl_transactions where
publisher_database_id = 17). Now that we have added snapshot
replication to one subscriber and transactional to another we have
noticed that this transaction count is not correct. It always says that
there are like 100+ in there waiting, but this is not true. The
transactional publications are always up to date with 0 latency.
It was my understanding that snapshot replication literally just makes a
bcp and push's it over at whatever time you tell it to push. However,
it looks like these extra transactions sitting in my distribution
database have everything to do with the snapshot replication and not the
transactional replication. Does snapshot replication work differently
then i thought? Does snapshot replication actually post transactions to
the distribution db?
Thanks
-comb
what shows up in sp_browsereplcmds. I suspect what you will see there are
the commands which are used for the sync - i.e. to build the tables and
other objects on the subscriber, and the bcp the data there. IIRC the
distribution clean up agent does not clean these up until you are past the
retention period.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e4953894a6972969896eb@.news.newsreader.co m...
> OK.
> We have this 1 publisher db that is pushing out to two separate
> subscribers (one is transactional the other is snapshot). In the
> distribution db the publisher_database_id is going to be the same. We
> monitor this transaction count by publisher id with the following (
> Select count(1) from dbo.MSrepl_transactions where
> publisher_database_id = 17). Now that we have added snapshot
> replication to one subscriber and transactional to another we have
> noticed that this transaction count is not correct. It always says that
> there are like 100+ in there waiting, but this is not true. The
> transactional publications are always up to date with 0 latency.
> It was my understanding that snapshot replication literally just makes a
> bcp and push's it over at whatever time you tell it to push. However,
> it looks like these extra transactions sitting in my distribution
> database have everything to do with the snapshot replication and not the
> transactional replication. Does snapshot replication work differently
> then i thought? Does snapshot replication actually post transactions to
> the distribution db?
> Thanks
> -comb
|||In article <Oq2WvQxJGHA.3064@.TK2MSFTNGP10.phx.gbl>,
hilary.cotter@.gmail.com says...
> what shows up in sp_browsereplcmds. I suspect what you will see there are
> the commands which are used for the sync - i.e. to build the tables and
> other objects on the subscriber, and the bcp the data there. IIRC the
> distribution clean up agent does not clean these up until you are past the
> retention period.
>
why would the bcp data be in the distribution db? wouldn't it just push
that across the net to the subscriber straight up as a .bcp file? why
would a snapshot need to put anything in the distribution db other then
"hey you need to push this .bcp across".
ok so what you are saying is that snapshot replication DOES actually
post transactions to the distribution db? I thought it just generated
the .bcp, idx and whatever that 3rd file is and pushed those across the
net with just a few instructions on where to put them.?
sp_browsereplcmds times out for me. We have a lot of subscribers on
this box. Is there anyway I can use that sp and just look at a certain
db id?
thanks.
We have this 1 publisher db that is pushing out to two separate
subscribers (one is transactional the other is snapshot). In the
distribution db the publisher_database_id is going to be the same. We
monitor this transaction count by publisher id with the following (
Select count(1) from dbo.MSrepl_transactions where
publisher_database_id = 17). Now that we have added snapshot
replication to one subscriber and transactional to another we have
noticed that this transaction count is not correct. It always says that
there are like 100+ in there waiting, but this is not true. The
transactional publications are always up to date with 0 latency.
It was my understanding that snapshot replication literally just makes a
bcp and push's it over at whatever time you tell it to push. However,
it looks like these extra transactions sitting in my distribution
database have everything to do with the snapshot replication and not the
transactional replication. Does snapshot replication work differently
then i thought? Does snapshot replication actually post transactions to
the distribution db?
Thanks
-comb
what shows up in sp_browsereplcmds. I suspect what you will see there are
the commands which are used for the sync - i.e. to build the tables and
other objects on the subscriber, and the bcp the data there. IIRC the
distribution clean up agent does not clean these up until you are past the
retention period.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e4953894a6972969896eb@.news.newsreader.co m...
> OK.
> We have this 1 publisher db that is pushing out to two separate
> subscribers (one is transactional the other is snapshot). In the
> distribution db the publisher_database_id is going to be the same. We
> monitor this transaction count by publisher id with the following (
> Select count(1) from dbo.MSrepl_transactions where
> publisher_database_id = 17). Now that we have added snapshot
> replication to one subscriber and transactional to another we have
> noticed that this transaction count is not correct. It always says that
> there are like 100+ in there waiting, but this is not true. The
> transactional publications are always up to date with 0 latency.
> It was my understanding that snapshot replication literally just makes a
> bcp and push's it over at whatever time you tell it to push. However,
> it looks like these extra transactions sitting in my distribution
> database have everything to do with the snapshot replication and not the
> transactional replication. Does snapshot replication work differently
> then i thought? Does snapshot replication actually post transactions to
> the distribution db?
> Thanks
> -comb
|||In article <Oq2WvQxJGHA.3064@.TK2MSFTNGP10.phx.gbl>,
hilary.cotter@.gmail.com says...
> what shows up in sp_browsereplcmds. I suspect what you will see there are
> the commands which are used for the sync - i.e. to build the tables and
> other objects on the subscriber, and the bcp the data there. IIRC the
> distribution clean up agent does not clean these up until you are past the
> retention period.
>
why would the bcp data be in the distribution db? wouldn't it just push
that across the net to the subscriber straight up as a .bcp file? why
would a snapshot need to put anything in the distribution db other then
"hey you need to push this .bcp across".
ok so what you are saying is that snapshot replication DOES actually
post transactions to the distribution db? I thought it just generated
the .bcp, idx and whatever that 3rd file is and pushed those across the
net with just a few instructions on where to put them.?
sp_browsereplcmds times out for me. We have a lot of subscribers on
this box. Is there anyway I can use that sp and just look at a certain
db id?
thanks.
Question about snapshot article defaults and indexes
I have recently been comparing the size of the source and replicated
tables and realized that indexes are not being replicated. The GLOBAL
(Applies to all) article defaults for the snapshot are set as follows:
Copy objects to destination
Indexes for primary keys are always copied
Include declared referential integrityCHECKED
Clustered indexesCHECKED
Nonclustered indexesCHECKED
User triggersNOT CHECKED
Extended propertiesNOT CHECKED
CollationNOT CHECKED
I these defaults should transfer the indexes correctly so I had to look
for another culprit. We have a script which creates three tables daily
so I decided to look at the article defaults for those tables and found
that only the 'Include declared referential integrity box is CHECKED.
QUESTION: What is the stored procedure where these variables can be
set? Is it sp_addarticle and if so what fields?
If we need to use the replicated db what would have been the
implication of not having the proper indexing. Performance?
to set them use the schemaoption parameter of sp_addarticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
tables and realized that indexes are not being replicated. The GLOBAL
(Applies to all) article defaults for the snapshot are set as follows:
Copy objects to destination
Indexes for primary keys are always copied
Include declared referential integrityCHECKED
Clustered indexesCHECKED
Nonclustered indexesCHECKED
User triggersNOT CHECKED
Extended propertiesNOT CHECKED
CollationNOT CHECKED
I these defaults should transfer the indexes correctly so I had to look
for another culprit. We have a script which creates three tables daily
so I decided to look at the article defaults for those tables and found
that only the 'Include declared referential integrity box is CHECKED.
QUESTION: What is the stored procedure where these variables can be
set? Is it sp_addarticle and if so what fields?
If we need to use the replicated db what would have been the
implication of not having the proper indexing. Performance?
to set them use the schemaoption parameter of sp_addarticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
Question about simple query..
How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.
deleted (that is another column or combination of columns that is unique).
Here is just an example based on your sample data (in this case the row with
the MIN name will be kept, but this assumes no duplicate names with the same
phone):
It's been bothering me for three days. I'm new SQL user.
I have the following table, which has name, address, city,state,zip
code, and phone number.
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.
How can I erase that extra data, and get the table like this ? :
kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
First, I used this query, but it turned out all of my data was gone. :
(
delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);
Please help !What is the criteria that you would use to pic which row to keep?
jaehwang wrote:
Quote:
Originally Posted by
How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.
>
I have the following table, which has name, address, city,state,zip
code, and phone number.
>
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
>
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.
>
How can I erase that extra data, and get the table like this ? :
>
kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbiamd21044 410-111-1358
>
First, I used this query, but it turned out all of my data was gone. :
(
>
delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);
>
Please help !
>
deleted (that is another column or combination of columns that is unique).
Here is just an example based on your sample data (in this case the row with
the MIN name will be kept, but this assumes no duplicate names with the same
phone):
DELETE FROM Address
WHERE EXISTS (
SELECT *
FROM Address AS A
WHERE A.phone = Address.phone
AND A.name < Address.name)
You can easily reverse the above condition to A.name Address.name to keep
the MAX name.
After you are done you can alter the table and add UNIQUE constraint on the
phone column to prevent duplicate data in the future, something like this:
ALTER TABLE Address ADD CONSTRAINT uphone UNIQUE (phone)
HTH,
Plamen Ratchev
http://www.SQLStudio.comsql
Question about setuping replication
Hi Guys,
I have two questions regarging setuping replication.
1. Do I need to configure Recovery Model as full when I want use
transactional replication.
2. Do I need to setup linked server when I use more than two servers for
replication.
3. Is there any doc about how to setup replication?
Thanks.
(1) No
(2) No but look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274098
(3) BOL, Hilary's book and some general links on my site (eg
http://www.mssqlcity.com/Articles/Replic/Replic.htm)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I have two questions regarging setuping replication.
1. Do I need to configure Recovery Model as full when I want use
transactional replication.
2. Do I need to setup linked server when I use more than two servers for
replication.
3. Is there any doc about how to setup replication?
Thanks.
(1) No
(2) No but look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274098
(3) BOL, Hilary's book and some general links on my site (eg
http://www.mssqlcity.com/Articles/Replic/Replic.htm)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Question about setup
what accounts are required for SQL cluster setup?
I was trying to install SQL Server and ran into some prompts that I did not
see in the books that I read about SQL cluster setup.
I know about SQLservice account. However, I didn't know it requires a domain
group for each service, SQL Server, Agent and Full text search. Can someone
explain? And are there other accounts that I need to be aware that are needed?
Also, can both nodes access the database file at the same time? I read that
if you use database mirror it is not recommended. I didn't think having both
db servers accessing the database files at the same time was a good idea.
We are going to use SQL Cluster and database mirror for SQL 2005.
Cluster Service Account - must be in a domain.
SQL Service Account - must be in a domain.
3 Groups for SQL 2005 install.
http://support.microsoft.com/kb/910228 Section 2.5.11.
I will let a SQL MVP/DBA answer the can a DB that is mirrored be accessed
from 2 nodes at a time, I know I would not ever recommend it.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:AE8105A3-C2E7-41C0-A4E1-7189399BC548@.microsoft.com...
> what accounts are required for SQL cluster setup?
> I was trying to install SQL Server and ran into some prompts that I did
> not
> see in the books that I read about SQL cluster setup.
> I know about SQLservice account. However, I didn't know it requires a
> domain
> group for each service, SQL Server, Agent and Full text search. Can
> someone
> explain? And are there other accounts that I need to be aware that are
> needed?
> Also, can both nodes access the database file at the same time? I read
> that
> if you use database mirror it is not recommended. I didn't think having
> both
> db servers accessing the database files at the same time was a good idea.
> We are going to use SQL Cluster and database mirror for SQL 2005.
|||Hi Rodney, thanks for helpful. Another question, the account I used to
install sql, this account needs to have privileges to create these group
accounts, right?
"Rodney R. Fournier [MVP]" wrote:
> Cluster Service Account - must be in a domain.
> SQL Service Account - must be in a domain.
> 3 Groups for SQL 2005 install.
> http://support.microsoft.com/kb/910228 Section 2.5.11.
> I will let a SQL MVP/DBA answer the can a DB that is mirrored be accessed
> from 2 nodes at a time, I know I would not ever recommend it.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:AE8105A3-C2E7-41C0-A4E1-7189399BC548@.microsoft.com...
>
>
|||I like to have them pre-staged. So no it doesn't. You could have an admin
create the groups for you.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:EC84C8C7-D22C-4380-BFF8-7EE9232E8CFD@.microsoft.com...[vbcol=seagreen]
> Hi Rodney, thanks for helpful. Another question, the account I used to
> install sql, this account needs to have privileges to create these group
> accounts, right?
> "Rodney R. Fournier [MVP]" wrote:
|||Actually, the SQL install account will need to set permissions on those
groups locally for the host nodes. Your install account must be a
domain-level account and should be local admin on all host nodes.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O2GCoBV7GHA.3412@.TK2MSFTNGP04.phx.gbl...
>I like to have them pre-staged. So no it doesn't. You could have an admin
>create the groups for you.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:EC84C8C7-D22C-4380-BFF8-7EE9232E8CFD@.microsoft.com...
>
|||Thanks Rodney. I was able to install SQL 2005 on the nodes.
I want to install Analysis Services on the second node to be our Analysis
Server. Is there any reason that I shouldn't? If I install it on the second
node would it cause a problem for anything?
"Rodney R. Fournier [MVP]" wrote:
> I like to have them pre-staged. So no it doesn't. You could have an admin
> create the groups for you.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:EC84C8C7-D22C-4380-BFF8-7EE9232E8CFD@.microsoft.com...
>
>
|||Thanks, I was able to get the correct groups and permissions set.
"Geoff N. Hiten" wrote:
> Actually, the SQL install account will need to set permissions on those
> groups locally for the host nodes. Your install account must be a
> domain-level account and should be local admin on all host nodes.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O2GCoBV7GHA.3412@.TK2MSFTNGP04.phx.gbl...
>
>
|||It does not create local groups, it assigns local permissions to domain
groups. Slight but distinct difference.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%236bd$$W7GHA.2380@.TK2MSFTNGP02.phx.gbl...
> Really? Not the SQL Service account, but the installation user creates
> local groups on the clustered nodes? Hmm, did not know that, time to do
> more research.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%231iWp3W7GHA.728@.TK2MSFTNGP04.phx.gbl...
>
|||I see, that is why I missed it. Cool. Thanks!
Rod
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:uSQlYqZ7GHA.3644@.TK2MSFTNGP03.phx.gbl...
> It does not create local groups, it assigns local permissions to domain
> groups. Slight but distinct difference.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%236bd$$W7GHA.2380@.TK2MSFTNGP02.phx.gbl...
>
|||Client tools install (including SSMS) is not cluster aware. It only
installs to the local node.
The installer is pretty smart and will only add or remove stuff that you
mark different on each run. You can install just the client tools on the
"other" nodes any time. Personally, I prefer to do it before the
cluster-aware elements.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:7BFA9BFF-1EDA-49BB-B18E-F7018A2E8B4A@.microsoft.com...[vbcol=seagreen]
> Rodney,
> I completed the install; however, when I launched SQL 2005 - I do not see
> Management Studio. Do I need to run the install again?
> When I installed 2005 on the test system, I did not have a problem but
> they
> were not configured with clustering so the setup option is different.
> "Rodney R. Fournier [MVP]" wrote:
I was trying to install SQL Server and ran into some prompts that I did not
see in the books that I read about SQL cluster setup.
I know about SQLservice account. However, I didn't know it requires a domain
group for each service, SQL Server, Agent and Full text search. Can someone
explain? And are there other accounts that I need to be aware that are needed?
Also, can both nodes access the database file at the same time? I read that
if you use database mirror it is not recommended. I didn't think having both
db servers accessing the database files at the same time was a good idea.
We are going to use SQL Cluster and database mirror for SQL 2005.
Cluster Service Account - must be in a domain.
SQL Service Account - must be in a domain.
3 Groups for SQL 2005 install.
http://support.microsoft.com/kb/910228 Section 2.5.11.
I will let a SQL MVP/DBA answer the can a DB that is mirrored be accessed
from 2 nodes at a time, I know I would not ever recommend it.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:AE8105A3-C2E7-41C0-A4E1-7189399BC548@.microsoft.com...
> what accounts are required for SQL cluster setup?
> I was trying to install SQL Server and ran into some prompts that I did
> not
> see in the books that I read about SQL cluster setup.
> I know about SQLservice account. However, I didn't know it requires a
> domain
> group for each service, SQL Server, Agent and Full text search. Can
> someone
> explain? And are there other accounts that I need to be aware that are
> needed?
> Also, can both nodes access the database file at the same time? I read
> that
> if you use database mirror it is not recommended. I didn't think having
> both
> db servers accessing the database files at the same time was a good idea.
> We are going to use SQL Cluster and database mirror for SQL 2005.
|||Hi Rodney, thanks for helpful. Another question, the account I used to
install sql, this account needs to have privileges to create these group
accounts, right?
"Rodney R. Fournier [MVP]" wrote:
> Cluster Service Account - must be in a domain.
> SQL Service Account - must be in a domain.
> 3 Groups for SQL 2005 install.
> http://support.microsoft.com/kb/910228 Section 2.5.11.
> I will let a SQL MVP/DBA answer the can a DB that is mirrored be accessed
> from 2 nodes at a time, I know I would not ever recommend it.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:AE8105A3-C2E7-41C0-A4E1-7189399BC548@.microsoft.com...
>
>
|||I like to have them pre-staged. So no it doesn't. You could have an admin
create the groups for you.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:EC84C8C7-D22C-4380-BFF8-7EE9232E8CFD@.microsoft.com...[vbcol=seagreen]
> Hi Rodney, thanks for helpful. Another question, the account I used to
> install sql, this account needs to have privileges to create these group
> accounts, right?
> "Rodney R. Fournier [MVP]" wrote:
|||Actually, the SQL install account will need to set permissions on those
groups locally for the host nodes. Your install account must be a
domain-level account and should be local admin on all host nodes.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O2GCoBV7GHA.3412@.TK2MSFTNGP04.phx.gbl...
>I like to have them pre-staged. So no it doesn't. You could have an admin
>create the groups for you.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:EC84C8C7-D22C-4380-BFF8-7EE9232E8CFD@.microsoft.com...
>
|||Thanks Rodney. I was able to install SQL 2005 on the nodes.
I want to install Analysis Services on the second node to be our Analysis
Server. Is there any reason that I shouldn't? If I install it on the second
node would it cause a problem for anything?
"Rodney R. Fournier [MVP]" wrote:
> I like to have them pre-staged. So no it doesn't. You could have an admin
> create the groups for you.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:EC84C8C7-D22C-4380-BFF8-7EE9232E8CFD@.microsoft.com...
>
>
|||Thanks, I was able to get the correct groups and permissions set.
"Geoff N. Hiten" wrote:
> Actually, the SQL install account will need to set permissions on those
> groups locally for the host nodes. Your install account must be a
> domain-level account and should be local admin on all host nodes.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O2GCoBV7GHA.3412@.TK2MSFTNGP04.phx.gbl...
>
>
|||It does not create local groups, it assigns local permissions to domain
groups. Slight but distinct difference.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%236bd$$W7GHA.2380@.TK2MSFTNGP02.phx.gbl...
> Really? Not the SQL Service account, but the installation user creates
> local groups on the clustered nodes? Hmm, did not know that, time to do
> more research.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%231iWp3W7GHA.728@.TK2MSFTNGP04.phx.gbl...
>
|||I see, that is why I missed it. Cool. Thanks!
Rod
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:uSQlYqZ7GHA.3644@.TK2MSFTNGP03.phx.gbl...
> It does not create local groups, it assigns local permissions to domain
> groups. Slight but distinct difference.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%236bd$$W7GHA.2380@.TK2MSFTNGP02.phx.gbl...
>
|||Client tools install (including SSMS) is not cluster aware. It only
installs to the local node.
The installer is pretty smart and will only add or remove stuff that you
mark different on each run. You can install just the client tools on the
"other" nodes any time. Personally, I prefer to do it before the
cluster-aware elements.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:7BFA9BFF-1EDA-49BB-B18E-F7018A2E8B4A@.microsoft.com...[vbcol=seagreen]
> Rodney,
> I completed the install; however, when I launched SQL 2005 - I do not see
> Management Studio. Do I need to run the install again?
> When I installed 2005 on the test system, I did not have a problem but
> they
> were not configured with clustering so the setup option is different.
> "Rodney R. Fournier [MVP]" wrote:
Question about Service Pack 1
Maybe my question is stupid, but I still post it here. Hope somebody can give
me an answer. MS released Service Pack 1 for Reporting services. Should I
only install the SP 1 into the server machine, or should I ask each user
install the SP 1 in his/her computer to run reports?
Thanks!server only .
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> Maybe my question is stupid, but I still post it here. Hope somebody can
give
> me an answer. MS released Service Pack 1 for Reporting services. Should I
> only install the SP 1 into the server machine, or should I ask each user
> install the SP 1 in his/her computer to run reports?
> Thanks!|||But why my report runs very different in different computers? The same report
runs well in some users' computers, but doesn't work in other users'
computers? That makes me so frustrating.
Thanks!
"Dennis Redfield" wrote:
> server only .
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > Maybe my question is stupid, but I still post it here. Hope somebody can
> give
> > me an answer. MS released Service Pack 1 for Reporting services. Should I
> > only install the SP 1 into the server machine, or should I ask each user
> > install the SP 1 in his/her computer to run reports?
> >
> > Thanks!
>
>|||You should upgrade all development machines as well. And you definitely do
not do anything at the clients. If the users computers are acting
differently the first thing I would do is see what version of IE they are
on.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> But why my report runs very different in different computers? The same
report
> runs well in some users' computers, but doesn't work in other users'
> computers? That makes me so frustrating.
>
> Thanks!
> "Dennis Redfield" wrote:
> > server only .
> >
> > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > Maybe my question is stupid, but I still post it here. Hope somebody
can
> > give
> > > me an answer. MS released Service Pack 1 for Reporting services.
Should I
> > > only install the SP 1 into the server machine, or should I ask each
user
> > > install the SP 1 in his/her computer to run reports?
> > >
> > > Thanks!
> >
> >
> >|||How different? Define "doesn't work"
Jeff
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> But why my report runs very different in different computers? The same
report
> runs well in some users' computers, but doesn't work in other users'
> computers? That makes me so frustrating.
>
> Thanks!
> "Dennis Redfield" wrote:
> > server only .
> >
> > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > Maybe my question is stupid, but I still post it here. Hope somebody
can
> > give
> > > me an answer. MS released Service Pack 1 for Reporting services.
Should I
> > > only install the SP 1 into the server machine, or should I ask each
user
> > > install the SP 1 in his/her computer to run reports?
> > >
> > > Thanks!
> >
> >
> >|||Actually, I posted the question this morning.
I have a report generated in Matrix. One of data field is a Hyperlink. I
used rs:LinkTarget=_blank to open a new window for a subreport. It works in
some computers, but not in other some computers. It really looks like work in
Win. 2000, but not Win XP. All of us use the updated IE.
Any suggestion?
Thanks to everyone!
"Jeff Dillon" wrote:
> How different? Define "doesn't work"
> Jeff
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> > But why my report runs very different in different computers? The same
> report
> > runs well in some users' computers, but doesn't work in other users'
> > computers? That makes me so frustrating.
> >
> >
> > Thanks!
> >
> > "Dennis Redfield" wrote:
> >
> > > server only .
> > >
> > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > > Maybe my question is stupid, but I still post it here. Hope somebody
> can
> > > give
> > > > me an answer. MS released Service Pack 1 for Reporting services.
> Should I
> > > > only install the SP 1 into the server machine, or should I ask each
> user
> > > > install the SP 1 in his/her computer to run reports?
> > > >
> > > > Thanks!
> > >
> > >
> > >
>
>|||Again, define "not works"!!
What happens' It doesn't open a new window? It opens a new window but
doesn't display? ?
Jeff
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:1393708D-AAD3-4807-9575-414EF2CE44CF@.microsoft.com...
> Actually, I posted the question this morning.
> I have a report generated in Matrix. One of data field is a Hyperlink. I
> used rs:LinkTarget=_blank to open a new window for a subreport. It works
in
> some computers, but not in other some computers. It really looks like work
in
> Win. 2000, but not Win XP. All of us use the updated IE.
> Any suggestion?
>
> Thanks to everyone!
> "Jeff Dillon" wrote:
> > How different? Define "doesn't work"
> >
> > Jeff
> > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> > > But why my report runs very different in different computers? The same
> > report
> > > runs well in some users' computers, but doesn't work in other users'
> > > computers? That makes me so frustrating.
> > >
> > >
> > > Thanks!
> > >
> > > "Dennis Redfield" wrote:
> > >
> > > > server only .
> > > >
> > > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > > > Maybe my question is stupid, but I still post it here. Hope
somebody
> > can
> > > > give
> > > > > me an answer. MS released Service Pack 1 for Reporting services.
> > Should I
> > > > > only install the SP 1 into the server machine, or should I ask
each
> > user
> > > > > install the SP 1 in his/her computer to run reports?
> > > > >
> > > > > Thanks!
> > > >
> > > >
> > > >
> >
> >
> >|||It doesn't open a new window. It always open the subreport in the old window.
"Jeff Dillon" wrote:
> Again, define "not works"!!
> What happens' It doesn't open a new window? It opens a new window but
> doesn't display? ?
> Jeff
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:1393708D-AAD3-4807-9575-414EF2CE44CF@.microsoft.com...
> > Actually, I posted the question this morning.
> >
> > I have a report generated in Matrix. One of data field is a Hyperlink. I
> > used rs:LinkTarget=_blank to open a new window for a subreport. It works
> in
> > some computers, but not in other some computers. It really looks like work
> in
> > Win. 2000, but not Win XP. All of us use the updated IE.
> >
> > Any suggestion?
> >
> >
> > Thanks to everyone!
> >
> > "Jeff Dillon" wrote:
> >
> > > How different? Define "doesn't work"
> > >
> > > Jeff
> > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> > > > But why my report runs very different in different computers? The same
> > > report
> > > > runs well in some users' computers, but doesn't work in other users'
> > > > computers? That makes me so frustrating.
> > > >
> > > >
> > > > Thanks!
> > > >
> > > > "Dennis Redfield" wrote:
> > > >
> > > > > server only .
> > > > >
> > > > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > > > > Maybe my question is stupid, but I still post it here. Hope
> somebody
> > > can
> > > > > give
> > > > > > me an answer. MS released Service Pack 1 for Reporting services.
> > > Should I
> > > > > > only install the SP 1 into the server machine, or should I ask
> each
> > > user
> > > > > > install the SP 1 in his/her computer to run reports?
> > > > > >
> > > > > > Thanks!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Did you look at the URLs generated? If they are correct, then something else
is wrong.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:87B7A2BE-866E-4C3E-8835-E0129918D2B2@.microsoft.com...
> It doesn't open a new window. It always open the subreport in the old
> window.
> "Jeff Dillon" wrote:
>> Again, define "not works"!!
>> What happens' It doesn't open a new window? It opens a new window but
>> doesn't display? ?
>> Jeff
>> "Jean" <Jean@.discussions.microsoft.com> wrote in message
>> news:1393708D-AAD3-4807-9575-414EF2CE44CF@.microsoft.com...
>> > Actually, I posted the question this morning.
>> >
>> > I have a report generated in Matrix. One of data field is a Hyperlink.
>> > I
>> > used rs:LinkTarget=_blank to open a new window for a subreport. It
>> > works
>> in
>> > some computers, but not in other some computers. It really looks like
>> > work
>> in
>> > Win. 2000, but not Win XP. All of us use the updated IE.
>> >
>> > Any suggestion?
>> >
>> >
>> > Thanks to everyone!
>> >
>> > "Jeff Dillon" wrote:
>> >
>> > > How different? Define "doesn't work"
>> > >
>> > > Jeff
>> > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
>> > > news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
>> > > > But why my report runs very different in different computers? The
>> > > > same
>> > > report
>> > > > runs well in some users' computers, but doesn't work in other
>> > > > users'
>> > > > computers? That makes me so frustrating.
>> > > >
>> > > >
>> > > > Thanks!
>> > > >
>> > > > "Dennis Redfield" wrote:
>> > > >
>> > > > > server only .
>> > > > >
>> > > > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
>> > > > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
>> > > > > > Maybe my question is stupid, but I still post it here. Hope
>> somebody
>> > > can
>> > > > > give
>> > > > > > me an answer. MS released Service Pack 1 for Reporting
>> > > > > > services.
>> > > Should I
>> > > > > > only install the SP 1 into the server machine, or should I ask
>> each
>> > > user
>> > > > > > install the SP 1 in his/her computer to run reports?
>> > > > > >
>> > > > > > Thanks!
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
me an answer. MS released Service Pack 1 for Reporting services. Should I
only install the SP 1 into the server machine, or should I ask each user
install the SP 1 in his/her computer to run reports?
Thanks!server only .
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> Maybe my question is stupid, but I still post it here. Hope somebody can
give
> me an answer. MS released Service Pack 1 for Reporting services. Should I
> only install the SP 1 into the server machine, or should I ask each user
> install the SP 1 in his/her computer to run reports?
> Thanks!|||But why my report runs very different in different computers? The same report
runs well in some users' computers, but doesn't work in other users'
computers? That makes me so frustrating.
Thanks!
"Dennis Redfield" wrote:
> server only .
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > Maybe my question is stupid, but I still post it here. Hope somebody can
> give
> > me an answer. MS released Service Pack 1 for Reporting services. Should I
> > only install the SP 1 into the server machine, or should I ask each user
> > install the SP 1 in his/her computer to run reports?
> >
> > Thanks!
>
>|||You should upgrade all development machines as well. And you definitely do
not do anything at the clients. If the users computers are acting
differently the first thing I would do is see what version of IE they are
on.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> But why my report runs very different in different computers? The same
report
> runs well in some users' computers, but doesn't work in other users'
> computers? That makes me so frustrating.
>
> Thanks!
> "Dennis Redfield" wrote:
> > server only .
> >
> > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > Maybe my question is stupid, but I still post it here. Hope somebody
can
> > give
> > > me an answer. MS released Service Pack 1 for Reporting services.
Should I
> > > only install the SP 1 into the server machine, or should I ask each
user
> > > install the SP 1 in his/her computer to run reports?
> > >
> > > Thanks!
> >
> >
> >|||How different? Define "doesn't work"
Jeff
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> But why my report runs very different in different computers? The same
report
> runs well in some users' computers, but doesn't work in other users'
> computers? That makes me so frustrating.
>
> Thanks!
> "Dennis Redfield" wrote:
> > server only .
> >
> > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > Maybe my question is stupid, but I still post it here. Hope somebody
can
> > give
> > > me an answer. MS released Service Pack 1 for Reporting services.
Should I
> > > only install the SP 1 into the server machine, or should I ask each
user
> > > install the SP 1 in his/her computer to run reports?
> > >
> > > Thanks!
> >
> >
> >|||Actually, I posted the question this morning.
I have a report generated in Matrix. One of data field is a Hyperlink. I
used rs:LinkTarget=_blank to open a new window for a subreport. It works in
some computers, but not in other some computers. It really looks like work in
Win. 2000, but not Win XP. All of us use the updated IE.
Any suggestion?
Thanks to everyone!
"Jeff Dillon" wrote:
> How different? Define "doesn't work"
> Jeff
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> > But why my report runs very different in different computers? The same
> report
> > runs well in some users' computers, but doesn't work in other users'
> > computers? That makes me so frustrating.
> >
> >
> > Thanks!
> >
> > "Dennis Redfield" wrote:
> >
> > > server only .
> > >
> > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > > Maybe my question is stupid, but I still post it here. Hope somebody
> can
> > > give
> > > > me an answer. MS released Service Pack 1 for Reporting services.
> Should I
> > > > only install the SP 1 into the server machine, or should I ask each
> user
> > > > install the SP 1 in his/her computer to run reports?
> > > >
> > > > Thanks!
> > >
> > >
> > >
>
>|||Again, define "not works"!!
What happens' It doesn't open a new window? It opens a new window but
doesn't display? ?
Jeff
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:1393708D-AAD3-4807-9575-414EF2CE44CF@.microsoft.com...
> Actually, I posted the question this morning.
> I have a report generated in Matrix. One of data field is a Hyperlink. I
> used rs:LinkTarget=_blank to open a new window for a subreport. It works
in
> some computers, but not in other some computers. It really looks like work
in
> Win. 2000, but not Win XP. All of us use the updated IE.
> Any suggestion?
>
> Thanks to everyone!
> "Jeff Dillon" wrote:
> > How different? Define "doesn't work"
> >
> > Jeff
> > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> > > But why my report runs very different in different computers? The same
> > report
> > > runs well in some users' computers, but doesn't work in other users'
> > > computers? That makes me so frustrating.
> > >
> > >
> > > Thanks!
> > >
> > > "Dennis Redfield" wrote:
> > >
> > > > server only .
> > > >
> > > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > > > Maybe my question is stupid, but I still post it here. Hope
somebody
> > can
> > > > give
> > > > > me an answer. MS released Service Pack 1 for Reporting services.
> > Should I
> > > > > only install the SP 1 into the server machine, or should I ask
each
> > user
> > > > > install the SP 1 in his/her computer to run reports?
> > > > >
> > > > > Thanks!
> > > >
> > > >
> > > >
> >
> >
> >|||It doesn't open a new window. It always open the subreport in the old window.
"Jeff Dillon" wrote:
> Again, define "not works"!!
> What happens' It doesn't open a new window? It opens a new window but
> doesn't display? ?
> Jeff
> "Jean" <Jean@.discussions.microsoft.com> wrote in message
> news:1393708D-AAD3-4807-9575-414EF2CE44CF@.microsoft.com...
> > Actually, I posted the question this morning.
> >
> > I have a report generated in Matrix. One of data field is a Hyperlink. I
> > used rs:LinkTarget=_blank to open a new window for a subreport. It works
> in
> > some computers, but not in other some computers. It really looks like work
> in
> > Win. 2000, but not Win XP. All of us use the updated IE.
> >
> > Any suggestion?
> >
> >
> > Thanks to everyone!
> >
> > "Jeff Dillon" wrote:
> >
> > > How different? Define "doesn't work"
> > >
> > > Jeff
> > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
> > > > But why my report runs very different in different computers? The same
> > > report
> > > > runs well in some users' computers, but doesn't work in other users'
> > > > computers? That makes me so frustrating.
> > > >
> > > >
> > > > Thanks!
> > > >
> > > > "Dennis Redfield" wrote:
> > > >
> > > > > server only .
> > > > >
> > > > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
> > > > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
> > > > > > Maybe my question is stupid, but I still post it here. Hope
> somebody
> > > can
> > > > > give
> > > > > > me an answer. MS released Service Pack 1 for Reporting services.
> > > Should I
> > > > > > only install the SP 1 into the server machine, or should I ask
> each
> > > user
> > > > > > install the SP 1 in his/her computer to run reports?
> > > > > >
> > > > > > Thanks!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Did you look at the URLs generated? If they are correct, then something else
is wrong.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:87B7A2BE-866E-4C3E-8835-E0129918D2B2@.microsoft.com...
> It doesn't open a new window. It always open the subreport in the old
> window.
> "Jeff Dillon" wrote:
>> Again, define "not works"!!
>> What happens' It doesn't open a new window? It opens a new window but
>> doesn't display? ?
>> Jeff
>> "Jean" <Jean@.discussions.microsoft.com> wrote in message
>> news:1393708D-AAD3-4807-9575-414EF2CE44CF@.microsoft.com...
>> > Actually, I posted the question this morning.
>> >
>> > I have a report generated in Matrix. One of data field is a Hyperlink.
>> > I
>> > used rs:LinkTarget=_blank to open a new window for a subreport. It
>> > works
>> in
>> > some computers, but not in other some computers. It really looks like
>> > work
>> in
>> > Win. 2000, but not Win XP. All of us use the updated IE.
>> >
>> > Any suggestion?
>> >
>> >
>> > Thanks to everyone!
>> >
>> > "Jeff Dillon" wrote:
>> >
>> > > How different? Define "doesn't work"
>> > >
>> > > Jeff
>> > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
>> > > news:567BAAD6-6C11-4C2E-9548-46DE8A3814FA@.microsoft.com...
>> > > > But why my report runs very different in different computers? The
>> > > > same
>> > > report
>> > > > runs well in some users' computers, but doesn't work in other
>> > > > users'
>> > > > computers? That makes me so frustrating.
>> > > >
>> > > >
>> > > > Thanks!
>> > > >
>> > > > "Dennis Redfield" wrote:
>> > > >
>> > > > > server only .
>> > > > >
>> > > > > "Jean" <Jean@.discussions.microsoft.com> wrote in message
>> > > > > news:4069EC0F-4D3B-46B6-A3F5-2D91CC0F4C12@.microsoft.com...
>> > > > > > Maybe my question is stupid, but I still post it here. Hope
>> somebody
>> > > can
>> > > > > give
>> > > > > > me an answer. MS released Service Pack 1 for Reporting
>> > > > > > services.
>> > > Should I
>> > > > > > only install the SP 1 into the server machine, or should I ask
>> each
>> > > user
>> > > > > > install the SP 1 in his/her computer to run reports?
>> > > > > >
>> > > > > > Thanks!
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
Question about Service Pack
In query analyzer
If I run this
SELECT @.@.version
I get this result
-----------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)
If I run this
SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
I get this result
-------
SP4
(1 row(s) affected)
What's the deal am I at SP2 or SP4?You are running SQL Server 2000 SP4. The SP2 in the @.@.VERSION string is the
OS service pack level.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Server" <alderran666@.gmail.com> wrote in message
news:1125853679.498850.278490@.g44g2000cwa.googlegr oups.com...
> In query analyzer
> If I run this
> SELECT @.@.version
> I get this result
> -----------------
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> (1 row(s) affected)
> If I run this
> SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
> I get this result
> -------
> SP4
> (1 row(s) affected)
> What's the deal am I at SP2 or SP4?|||Hi
To add to Dan's Reply:
Windows NT 5.1 (Build 2600: Service Pack 2)
Is Windows XP Service Pack 2.
A SQL Server version list can be found at:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37
John
"SQL Server" <alderran666@.gmail.com> wrote in message
news:1125853679.498850.278490@.g44g2000cwa.googlegr oups.com...
> In query analyzer
> If I run this
> SELECT @.@.version
> I get this result
> -----------------
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> (1 row(s) affected)
> If I run this
> SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'))
> I get this result
> -------
> SP4
> (1 row(s) affected)
> What's the deal am I at SP2 or SP4?
Question about service configuration on SQL Server 2005 IA 64....
While perusing Configuration Manager, I noticed on the properties for the services there is a parameter to indicate whether or not the service is running on a 64-bit operating system. If I'm running on a 64 bit Itanium server, shouldn't this be checked "Yes"?
I'd ask the server admin who did the install, but he is currently awol.
Yes, it should be, assuming you 64bit Itanium server is running a 64bit OS, and the IA64 version of SQL was installed...you'd need to verify those 2 things...
What version of SQL is running? (run select @.@.version to get this)
What Edition is running? (run select serverproperty('Edition') to get this)
What OS is running, what edition, and what SKU? (you can get this from MSINFO, or right-click My Computer and select properties... the @.@.version output will also give you some of the info)
|||Only 64-bit SQL Server 2005 is available for IA64 machines.sqlQuestion about sending a large db backup/restore then replicating
OK.
lets say i have a 100gb db. I really just want to replicate 1 table
within that db that is 65gb at like 100,000,000 rows(but who cares, we
will use the entire db for this example). This is a production db that
is constantly being updated. How would I be able to do something like a
no sync or something of that nature on a db that is constantly being
updated?
Wouldn't I need to shut off access of anything entering that db from the
point I started the backup to the point the subscriber does the restore,
and I can then setup the publication with "nosync"?? I guess I could
setup no sync and then manually dts the missing 1 day of information
over some other way and then we can manually sync items from there?
The other option i was thinking about was starting the 100gb snapshot,
disabling the distribution agent from starting to sync. Copy those
..bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
the mean time let the log file for the db grow like crazy since their
are articles marked for replication that cannot be delivered because the
agent is disabled. Then once they do a restore enable the distribution
agent and tell it to skip looking for the snapshot that was once there
and just deliver the transactions that are waiting in the log? Does
that make sense.
The short of this, Is that I am trying to figure out how to replicate a
100gb db without shutting off access to it. This db is updated 24/7.
tia, and sorry to be so long winded
-comb
Ideally you will kick off all of your users and backup and restore, bcp the
data out, or use DTS.
What I do is create a no-sync subscription at lets say midnight. Then I get
my data over, usually using bcp (if you can use snapshot isolation within
SQL 2005), and the deploy it on my subscriber. Then I start up my
distribution agent with the allow data consistency errors and I try to fix
the consistency issues.
Its very difficult but it seems to be the best option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e3843bad612d2a99896e4@.news.newsreader.co m...
> OK.
> lets say i have a 100gb db. I really just want to replicate 1 table
> within that db that is 65gb at like 100,000,000 rows(but who cares, we
> will use the entire db for this example). This is a production db that
> is constantly being updated. How would I be able to do something like a
> no sync or something of that nature on a db that is constantly being
> updated?
> Wouldn't I need to shut off access of anything entering that db from the
> point I started the backup to the point the subscriber does the restore,
> and I can then setup the publication with "nosync"?? I guess I could
> setup no sync and then manually dts the missing 1 day of information
> over some other way and then we can manually sync items from there?
> The other option i was thinking about was starting the 100gb snapshot,
> disabling the distribution agent from starting to sync. Copy those
> .bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
> the mean time let the log file for the db grow like crazy since their
> are articles marked for replication that cannot be delivered because the
> agent is disabled. Then once they do a restore enable the distribution
> agent and tell it to skip looking for the snapshot that was once there
> and just deliver the transactions that are waiting in the log? Does
> that make sense.
> The short of this, Is that I am trying to figure out how to replicate a
> 100gb db without shutting off access to it. This db is updated 24/7.
> tia, and sorry to be so long winded
> -comb
lets say i have a 100gb db. I really just want to replicate 1 table
within that db that is 65gb at like 100,000,000 rows(but who cares, we
will use the entire db for this example). This is a production db that
is constantly being updated. How would I be able to do something like a
no sync or something of that nature on a db that is constantly being
updated?
Wouldn't I need to shut off access of anything entering that db from the
point I started the backup to the point the subscriber does the restore,
and I can then setup the publication with "nosync"?? I guess I could
setup no sync and then manually dts the missing 1 day of information
over some other way and then we can manually sync items from there?
The other option i was thinking about was starting the 100gb snapshot,
disabling the distribution agent from starting to sync. Copy those
..bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
the mean time let the log file for the db grow like crazy since their
are articles marked for replication that cannot be delivered because the
agent is disabled. Then once they do a restore enable the distribution
agent and tell it to skip looking for the snapshot that was once there
and just deliver the transactions that are waiting in the log? Does
that make sense.
The short of this, Is that I am trying to figure out how to replicate a
100gb db without shutting off access to it. This db is updated 24/7.
tia, and sorry to be so long winded
-comb
Ideally you will kick off all of your users and backup and restore, bcp the
data out, or use DTS.
What I do is create a no-sync subscription at lets say midnight. Then I get
my data over, usually using bcp (if you can use snapshot isolation within
SQL 2005), and the deploy it on my subscriber. Then I start up my
distribution agent with the allow data consistency errors and I try to fix
the consistency issues.
Its very difficult but it seems to be the best option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e3843bad612d2a99896e4@.news.newsreader.co m...
> OK.
> lets say i have a 100gb db. I really just want to replicate 1 table
> within that db that is 65gb at like 100,000,000 rows(but who cares, we
> will use the entire db for this example). This is a production db that
> is constantly being updated. How would I be able to do something like a
> no sync or something of that nature on a db that is constantly being
> updated?
> Wouldn't I need to shut off access of anything entering that db from the
> point I started the backup to the point the subscriber does the restore,
> and I can then setup the publication with "nosync"?? I guess I could
> setup no sync and then manually dts the missing 1 day of information
> over some other way and then we can manually sync items from there?
> The other option i was thinking about was starting the 100gb snapshot,
> disabling the distribution agent from starting to sync. Copy those
> .bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
> the mean time let the log file for the db grow like crazy since their
> are articles marked for replication that cannot be delivered because the
> agent is disabled. Then once they do a restore enable the distribution
> agent and tell it to skip looking for the snapshot that was once there
> and just deliver the transactions that are waiting in the log? Does
> that make sense.
> The short of this, Is that I am trying to figure out how to replicate a
> 100gb db without shutting off access to it. This db is updated 24/7.
> tia, and sorry to be so long winded
> -comb
Question about selling license
Hello,
After one of my clients had to close down his business, he has asked me to
liquidate some items to cover my unpaid invoices. One of those items is a
copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
installed/activated. This is OEM and it was purchased with a new server. Am
I correct in my assumption that I have to sell the software and the original
server together? Can I even sell it at all?
Any help appreciated.
Thanks,
ATL IT Guy
I'm pretty sure you can sell the server along with the OEM software that came
with it. At least I've worked with companies that have done so. But you are
correct, you can't sell that copy of SQL Server without the server it came
on. I would verify with the server vendor or a MS license reseller like CDW
or someone simliar before you do anything though.
"ATL IT Guy" wrote:
> Hello,
> After one of my clients had to close down his business, he has asked me to
> liquidate some items to cover my unpaid invoices. One of those items is a
> copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
> installed/activated. This is OEM and it was purchased with a new server. Am
> I correct in my assumption that I have to sell the software and the original
> server together? Can I even sell it at all?
> Any help appreciated.
> Thanks,
> ATL IT Guy
After one of my clients had to close down his business, he has asked me to
liquidate some items to cover my unpaid invoices. One of those items is a
copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
installed/activated. This is OEM and it was purchased with a new server. Am
I correct in my assumption that I have to sell the software and the original
server together? Can I even sell it at all?
Any help appreciated.
Thanks,
ATL IT Guy
I'm pretty sure you can sell the server along with the OEM software that came
with it. At least I've worked with companies that have done so. But you are
correct, you can't sell that copy of SQL Server without the server it came
on. I would verify with the server vendor or a MS license reseller like CDW
or someone simliar before you do anything though.
"ATL IT Guy" wrote:
> Hello,
> After one of my clients had to close down his business, he has asked me to
> liquidate some items to cover my unpaid invoices. One of those items is a
> copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
> installed/activated. This is OEM and it was purchased with a new server. Am
> I correct in my assumption that I have to sell the software and the original
> server together? Can I even sell it at all?
> Any help appreciated.
> Thanks,
> ATL IT Guy
Question about selling license
Hello,
After one of my clients had to close down his business, he has asked me to
liquidate some items to cover my unpaid invoices. One of those items is a
copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
installed/activated. This is OEM and it was purchased with a new server. A
m
I correct in my assumption that I have to sell the software and the original
server together? Can I even sell it at all?
Any help appreciated.
Thanks,
ATL IT GuyI'm pretty sure you can sell the server along with the OEM software that cam
e
with it. At least I've worked with companies that have done so. But you are
correct, you can't sell that copy of SQL Server without the server it came
on. I would verify with the server vendor or a MS license reseller like CDW
or someone simliar before you do anything though.
"ATL IT Guy" wrote:
> Hello,
> After one of my clients had to close down his business, he has asked me to
> liquidate some items to cover my unpaid invoices. One of those items is a
> copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
> installed/activated. This is OEM and it was purchased with a new server.
Am
> I correct in my assumption that I have to sell the software and the origin
al
> server together? Can I even sell it at all?
> Any help appreciated.
> Thanks,
> ATL IT Guy
After one of my clients had to close down his business, he has asked me to
liquidate some items to cover my unpaid invoices. One of those items is a
copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
installed/activated. This is OEM and it was purchased with a new server. A
m
I correct in my assumption that I have to sell the software and the original
server together? Can I even sell it at all?
Any help appreciated.
Thanks,
ATL IT GuyI'm pretty sure you can sell the server along with the OEM software that cam
e
with it. At least I've worked with companies that have done so. But you are
correct, you can't sell that copy of SQL Server without the server it came
on. I would verify with the server vendor or a MS license reseller like CDW
or someone simliar before you do anything though.
"ATL IT Guy" wrote:
> Hello,
> After one of my clients had to close down his business, he has asked me to
> liquidate some items to cover my unpaid invoices. One of those items is a
> copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
> installed/activated. This is OEM and it was purchased with a new server.
Am
> I correct in my assumption that I have to sell the software and the origin
al
> server together? Can I even sell it at all?
> Any help appreciated.
> Thanks,
> ATL IT Guy
Question about selling license
Hello,
After one of my clients had to close down his business, he has asked me to
liquidate some items to cover my unpaid invoices. One of those items is a
copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
installed/activated. This is OEM and it was purchased with a new server. Am
I correct in my assumption that I have to sell the software and the original
server together? Can I even sell it at all?
Any help appreciated.
Thanks,
ATL IT GuyI'm pretty sure you can sell the server along with the OEM software that came
with it. At least I've worked with companies that have done so. But you are
correct, you can't sell that copy of SQL Server without the server it came
on. I would verify with the server vendor or a MS license reseller like CDW
or someone simliar before you do anything though.
"ATL IT Guy" wrote:
> Hello,
> After one of my clients had to close down his business, he has asked me to
> liquidate some items to cover my unpaid invoices. One of those items is a
> copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
> installed/activated. This is OEM and it was purchased with a new server. Am
> I correct in my assumption that I have to sell the software and the original
> server together? Can I even sell it at all?
> Any help appreciated.
> Thanks,
> ATL IT Guy
After one of my clients had to close down his business, he has asked me to
liquidate some items to cover my unpaid invoices. One of those items is a
copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
installed/activated. This is OEM and it was purchased with a new server. Am
I correct in my assumption that I have to sell the software and the original
server together? Can I even sell it at all?
Any help appreciated.
Thanks,
ATL IT GuyI'm pretty sure you can sell the server along with the OEM software that came
with it. At least I've worked with companies that have done so. But you are
correct, you can't sell that copy of SQL Server without the server it came
on. I would verify with the server vendor or a MS license reseller like CDW
or someone simliar before you do anything though.
"ATL IT Guy" wrote:
> Hello,
> After one of my clients had to close down his business, he has asked me to
> liquidate some items to cover my unpaid invoices. One of those items is a
> copy of MS SQL 2005 Workgroup 32 bit. Still shrink wrapped and never
> installed/activated. This is OEM and it was purchased with a new server. Am
> I correct in my assumption that I have to sell the software and the original
> server together? Can I even sell it at all?
> Any help appreciated.
> Thanks,
> ATL IT Guy
Question about self referencing delete
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. (Isn't
it?) And then SQLServer is supposed to give an error ; as empid = 1 is being
referred as foreign key in record where empid is 2. But it doesn't happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where empid
is 1? (In short, deletes all cascading child records first and then parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
PravinThis is a multi-part message in MIME format.
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"= wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE empid ==3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin
--=_NextPart_000_0030_01C3745C.97B12C90--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should avoid =using EM for creating tables. I stick with scripting it out and then saving =the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"= wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE empid ==3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin
--=_NextPart_000_000F_01C37454.B43BD200--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok Tom. That clears the doubt. Thanks.
-- Regards,
Pravin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#aD7FZHdDHA.4020@.tk2msftngp13.phx.gbl...
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did =not specify ON DELETE CASCADE in your foreign key". Does that mean =SQLServer allows setting the option 'on delete cascade' for self =referencing keys? If yes, then please inform me how. If no, then your =answer that "In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation." is =just the answer for my question. Right?
Note: I was not able to set this option from enterprize manager. =Please guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D =1 is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer =first
deletes record where empid is 2. And then it deletes the record =where empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is =not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ok Tom. That clears the doubt. Thanks.
-- Regards,Pravin
"Tom Moreau"= wrote in message news:#aD7FZHdDHA.4020=@.tk2msftngp13.phx.gbl...
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should =avoid using EM for creating tables. I stick with scripting it out and =then saving the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting =the option 'on delete cascade' for self referencing keys? If yes, then =please inform me how. If no, then your answer that "In =your DELETE, you specified that you were deleting both the child and the related =parent, so there would be no RI violation." is just the answer for my =question. Right?
Note: I was not able to set =this option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"= wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify =ON DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that =you were deleting both the child and the related parent, so there would =be no RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE =empid =3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin" wrote in =message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary =key for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM =emp_1 WHERE empid in (1, 2)So SQLSerever, will first attempt to =delete record where empid is 1. (Isn'tit?) And then SQLServer is =supposed to give an error ; as empid =3D 1 is beingreferred as foreign key =in record where empid is 2. But it doesn't happenso. SQLServer doesn't =give an error. So does that mean SQLServer firstdeletes record where =empid is 2. And then it deletes the record where empidis 1? (In short, =deletes all cascading child records first and then parentrecords) Is it =SQLServer's normal and expected behavior? Or it is notguaranteed? Or =anything else? Please guide. Thanks.Regards,Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0--sql
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. (Isn't
it?) And then SQLServer is supposed to give an error ; as empid = 1 is being
referred as foreign key in record where empid is 2. But it doesn't happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where empid
is 1? (In short, deletes all cascading child records first and then parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
PravinThis is a multi-part message in MIME format.
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE empid ==3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin"
--=_NextPart_000_0030_01C3745C.97B12C90--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should avoid =using EM for creating tables. I stick with scripting it out and then saving =the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin"
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE empid ==3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin"
--=_NextPart_000_000F_01C37454.B43BD200--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok Tom. That clears the doubt. Thanks.
-- Regards,
Pravin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#aD7FZHdDHA.4020@.tk2msftngp13.phx.gbl...
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did =not specify ON DELETE CASCADE in your foreign key". Does that mean =SQLServer allows setting the option 'on delete cascade' for self =referencing keys? If yes, then please inform me how. If no, then your =answer that "In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation." is =just the answer for my question. Right?
Note: I was not able to set this option from enterprize manager. =Please guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D =1 is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer =first
deletes record where empid is 2. And then it deletes the record =where empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is =not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ok Tom. That clears the doubt. Thanks.
-- Regards,Pravin
"Tom Moreau"
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should =avoid using EM for creating tables. I stick with scripting it out and =then saving the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin"
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting =the option 'on delete cascade' for self referencing keys? If yes, then =please inform me how. If no, then your answer that "In =your DELETE, you specified that you were deleting both the child and the related =parent, so there would be no RI violation." is just the answer for my =question. Right?
Note: I was not able to set =this option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"
In your case, you did not specify =ON DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that =you were deleting both the child and the related parent, so there would =be no RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE =empid =3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin"
--=_NextPart_000_000D_01C374C4.4266C7F0--sql
Subscribe to:
Posts (Atom)