Display date: connection

显示标签为“connection”的博文。显示所有博文

2012年3月20日星期二

Disconnects from Sql Server

I have a VPN connection to my Sql Server at work.
It works fine most of the time. I actually use my Windows 2000 Server as my
client from home.
What happens is that I will usually have 2 instances of Sql Server EM ,
Query Analyser and the Profiler running at one time. Normally, there is no
problem. But then I will lose all my connections. Even if I restart the
programs it won't reconnect for about 3 or 4 minutes. Then it does and
everything is fine for awhile.
I find that on XP, I can't seem to get it to reconnect until I reboot the
machine.
It has nothing to do with actual connection between my machine at home and
the work machines through the VPN, as I can ping the machines at work fine.
Anyone know what could be causing this problem?
Thanks,
TomHi
Check the SQL Server Event log for any entries at the time.
What happens when you open a terminal server session against your server,
keep it open and use the SQL Server tools on your machine?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tshad" <tfs@.dslextreme.com> wrote in message
news:%23UvrGF7oFHA.1048@.tk2msftngp13.phx.gbl...
>I have a VPN connection to my Sql Server at work.
> It works fine most of the time. I actually use my Windows 2000 Server as
> my
> client from home.
> What happens is that I will usually have 2 instances of Sql Server EM ,
> Query Analyser and the Profiler running at one time. Normally, there is
> no
> problem. But then I will lose all my connections. Even if I restart the
> programs it won't reconnect for about 3 or 4 minutes. Then it does and
> everything is fine for awhile.
> I find that on XP, I can't seem to get it to reconnect until I reboot the
> machine.
> It has nothing to do with actual connection between my machine at home and
> the work machines through the VPN, as I can ping the machines at work
> fine.
> Anyone know what could be causing this problem?
> Thanks,
> Tom
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OLwc%23N7oFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check the SQL Server Event log for any entries at the time.
> What happens when you open a terminal server session against your server,
> keep it open and use the SQL Server tools on your machine?
Actually, I have another program (or 2) also accessing the server fine, just
Sql Server seems to have a problem.
Tom

> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:%23UvrGF7oFHA.1048@.tk2msftngp13.phx.gbl...
as
the
the
and
>sql

disconnection sessions

Hi,

Here is my code on how i connect to my database

Dim rst As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim vresponse As String
' On Error GoTo err_proc
Try
conn.Open(GetConn(getconnid))
Select Case getmode
Case 0 'execute then return first field
rst = conn.Execute(getsql)
If rst.EOF = False And rst.BOF = False Then
Try
vresponse = IIf(IsDBNull(rst.Fields.Item(0).Value), "", rst.Fields.Item(0).Value)
' vresponse = rst.Fields.Count
Catch ex As Exception
vresponse = "Error :" + ex.Message
End Try
End If
Case 1 'execute only
If bTransactional = True Then
conn.BeginTrans()
End If
conn.Execute(getsql)
vresponse = "OK"
If bTransactional = True Then
conn.CommitTrans()
End If
End Select

Return vresponse.Trim

* line 1)

rst = Nothing
conn = Nothing

Exit Function

supposed to be *line 1) terminates the connection/session form SQL server. But when i check to the SQL Query Analyzer, there is still some connections related to this command. So when i run again this commands, it creates another connection. How can i terminate/end my sessions in SQL.

Thanks

You can use KILL command, take a look at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Is there a way to drop your connection to a database, specifically after
> > you
> > have set the database to single user mode?
> >
> > What I'm trying to do is run a test restore of a SQL Database. What I'm
> > doing is using the command :
> >
> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> >
> > to put the db into single user mode, however the problem is that in the
> > process of doing this it establishes a connection to the db, therefore
> > taking the one and only available connection to the db. Currently the
only
> > way I can disconnect the connection is to re-start SQL, so I can then
run
> > the restore, and the backup server can successfully connect to the db
> > (otherwise it complains that it cannot obtain exclusive access to the
> > database as it is in use).
> >
> > Needless to say I'd prefer to not need to kill all SQL connections to
the
> > entire server just so I can test the backups are being successful.
> >
> > Keith
> >
> >
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
>> Why not grab the SINGLE_USER for yourself and do the restore.
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> go
>> USE <dbname>
>> go
>> RESTORE ...
>>
>> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
>> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Is there a way to drop your connection to a database, specifically
>> > after
>> > you
>> > have set the database to single user mode?
>> >
>> > What I'm trying to do is run a test restore of a SQL Database. What I'm
>> > doing is using the command :
>> >
>> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
>> >
>> > to put the db into single user mode, however the problem is that in the
>> > process of doing this it establishes a connection to the db, therefore
>> > taking the one and only available connection to the db. Currently the
> only
>> > way I can disconnect the connection is to re-start SQL, so I can then
> run
>> > the restore, and the backup server can successfully connect to the db
>> > (otherwise it complains that it cannot obtain exclusive access to the
>> > database as it is in use).
>> >
>> > Needless to say I'd prefer to not need to kill all SQL connections to
> the
>> > entire server just so I can test the backups are being successful.
>> >
>> > Keith
>> >
>> >
>>
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> > Because I'm using Backup Exec to do the backup and restore, which is a
> > different connection to the one that I set the db to single user with.
The
> > error below even happens if I set the db to single user from the backup
> > server itself, rather than on the SQL Server box.
> >
> > Keith
> >
> > "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> > news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> >> Why not grab the SINGLE_USER for yourself and do the restore.
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> go
> >> USE <dbname>
> >> go
> >> RESTORE ...
> >>
> >>
> >> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> >> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Is there a way to drop your connection to a database, specifically
> >> > after
> >> > you
> >> > have set the database to single user mode?
> >> >
> >> > What I'm trying to do is run a test restore of a SQL Database. What
I'm
> >> > doing is using the command :
> >> >
> >> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK
IMMEDIATE.
> >> >
> >> > to put the db into single user mode, however the problem is that in
the
> >> > process of doing this it establishes a connection to the db,
therefore
> >> > taking the one and only available connection to the db. Currently the
> > only
> >> > way I can disconnect the connection is to re-start SQL, so I can then
> > run
> >> > the restore, and the backup server can successfully connect to the db
> >> > (otherwise it complains that it cannot obtain exclusive access to the
> >> > database as it is in use).
> >> >
> >> > Needless to say I'd prefer to not need to kill all SQL connections to
> > the
> >> > entire server just so I can test the backups are being successful.
> >> >
> >> > Keith
> >> >
> >> >
> >>
> >>
> >
> >
>

disconnecting yourself after setting db to single user

2012年3月19日星期一

Disconnect Client Connection via T-SQL/Store Proce

Hi Gents,
If there are some client connections on the database and I would like to
detach the file, it's failed when execute the store proc "sp_detach_db". Any
SQL statement or Store Procedure on clearing/kicking out the current client
connections? Thanks in advance.
Regards,
CurtisLYou can use the KILL command.|||Moreover, using kill <id> with statusonly. So this way I can see the rollbac
k
progression out of danger.
"markc600@.hotmail.com" wrote:

> You can use the KILL command.
>

2012年3月7日星期三

Disapearing Records

The first line in my global.asax is the creation of an ActiveX object that will hold a data connection to a SQL2000 Server database for the lifespan of an "Application" scope.

< OBJECT RUNAT="Server" SCOPE="Application" ID="Conn" name="Conn" PROGID="ADODB.Connection" VIEWASTEXT >< /OBJECT >

With that done, every time a new user invokes global.asax, a routine will verify rather it needs to open the connection or if the connection is already open:

If Conn.State <> adStateOpen Then
Conn.ConnectionTimeOut = 999999
Conn.ConnectionString = strConnStr
Conn.CursorLocation = adUseClient
Conn.Open
End If

The purpose of this is to avoid 400 simultaneous users from having to wait for their connections to estabilish communication to the server before initiating transaction.

Surprisingly enough, this works perfectly (for a massively shared, perpetual Connection object), except that once or twice a month, a record disapears from the main database and takes with it all relevant records from supporting tables.

Yes, it seems like a Forced Roolback is being issued...

Do you see Why?

Aleks,

I don't see anything that would cause that behavior based on the code you've provided.

Logging deletions through a trigger is one possible first step to figuring out when, where and why the behavior is occurring.

David Sceppa
Microsoft

Disallow connection for a second or two

Hi
Is it possible to prevent users of a database in SQL Server 2005 from
doing anything for a second or two.
Can this be done using a stored procedure.
thanks
Newish
You can put the database into readonly mode. In the proc do a
ALTER DATABASE AdventureWorks SET READ_ONLY
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
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160218524.169852.196200@.i3g2000cwc.googlegro ups.com...
> Hi
> Is it possible to prevent users of a database in SQL Server 2005 from
> doing anything for a second or two.
> Can this be done using a stored procedure.
> thanks
> Newish
>
|||Thanks Hilary.
Would this scenario work. Use a stored procedure to issue this
command. Then the same procedure writes a couple of pieces of info to
a table and then revoke this statement.
Hence, during the readonly state will the stored procedure be able to
write.
How do you revert back to read and write status.
thanks once again.
Newish
Hilary Cotter wrote:[vbcol=seagreen]
> You can put the database into readonly mode. In the proc do a
> ALTER DATABASE AdventureWorks SET READ_ONLY
> --
> 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
>
> "Newish" <ahussain3@.gmail.com> wrote in message
> news:1160218524.169852.196200@.i3g2000cwc.googlegro ups.com...

Disallow connection for a second or two

Hi
Is it possible to prevent users of a database in SQL Server 2005 from
doing anything for a second or two.
Can this be done using a stored procedure.
thanks
NewishYou can put the database into readonly mode. In the proc do a
ALTER DATABASE AdventureWorks SET READ_ONLY
--
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
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160218524.169852.196200@.i3g2000cwc.googlegroups.com...
> Hi
> Is it possible to prevent users of a database in SQL Server 2005 from
> doing anything for a second or two.
> Can this be done using a stored procedure.
> thanks
> Newish
>|||Thanks Hilary.
Would this scenario work. Use a stored procedure to issue this
command. Then the same procedure writes a couple of pieces of info to
a table and then revoke this statement.
Hence, during the readonly state will the stored procedure be able to
write.
How do you revert back to read and write status.
thanks once again.
Newish
Hilary Cotter wrote:
> You can put the database into readonly mode. In the proc do a
> ALTER DATABASE AdventureWorks SET READ_ONLY
> --
> 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
>
> "Newish" <ahussain3@.gmail.com> wrote in message
> news:1160218524.169852.196200@.i3g2000cwc.googlegroups.com...
> > Hi
> >
> > Is it possible to prevent users of a database in SQL Server 2005 from
> > doing anything for a second or two.
> >
> > Can this be done using a stored procedure.
> >
> > thanks
> >
> > Newish
> >

Disallow connection for a second or two

Hi
Is it possible to prevent users of a database in SQL Server 2005 from
doing anything for a second or two.
Can this be done using a stored procedure.
thanks
NewishYou can put the database into readonly mode. In the proc do a
ALTER DATABASE AdventureWorks SET READ_ONLY
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
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160218524.169852.196200@.i3g2000cwc.googlegroups.com...
> Hi
> Is it possible to prevent users of a database in SQL Server 2005 from
> doing anything for a second or two.
> Can this be done using a stored procedure.
> thanks
> Newish
>|||Thanks Hilary.
Would this scenario work. Use a stored procedure to issue this
command. Then the same procedure writes a couple of pieces of info to
a table and then revoke this statement.
Hence, during the readonly state will the stored procedure be able to
write.
How do you revert back to read and write status.
thanks once again.
Newish
Hilary Cotter wrote:[vbcol=seagreen]
> You can put the database into readonly mode. In the proc do a
> ALTER DATABASE AdventureWorks SET READ_ONLY
> --
> 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
>
> "Newish" <ahussain3@.gmail.com> wrote in message
> news:1160218524.169852.196200@.i3g2000cwc.googlegroups.com...

2012年2月24日星期五

disabled fields, such as Upload File, in Report Manager

When I open Report Manager through IE on server 2003 installed with reporting
service and login as an admin through remote desktop connection, I can see
all the features but disabled. I can not Upload File, Add New Data Source
etc.. What is wrong? Please help.See what role-based security you have.
At the "Home" folder, go to Properties --> Security and see the roles
assigned to the admin. If you don't have "Content Manager" role
assigned to you, you can not upload the files.
Shang wrote:
> When I open Report Manager through IE on server 2003 installed with reporting
> service and login as an admin through remote desktop connection, I can see
> all the features but disabled. I can not Upload File, Add New Data Source
> etc.. What is wrong? Please help.|||Well, what i m used to do to upload rdls, is -
1 - go to the properties of reports virtual folder under IIS
2 - click directory security tab
3 - click edit button
4 - turn of anoymous access
it works for me|||I agree, it seems like the website has anonymous turned on. When anonymous
is turned on, even logged in as administrator does not give you the
administrators rights to do things in RS. This is because with anonymous
turned on everybody is treated the same, because they are anonymous.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Techotsav" <Utsav.Verma@.gmail.com> wrote in message
news:1134195509.900311.33640@.o13g2000cwo.googlegroups.com...
> Well, what i m used to do to upload rdls, is -
> 1 - go to the properties of reports virtual folder under IIS
> 2 - click directory security tab
> 3 - click edit button
> 4 - turn of anoymous access
> it works for me
>|||Hi Bruce,
Having similar problem.
Fresh install of Windows 2003 Standard, and IIS, SP1
Fresh Install of SQL 2005 w/ Reporting Services
Fresh install of Visual Studio 2005
Can not create new folder etc... Logged on as user with Administrative
privileges or Local Administrator or Domain Administrator, no difference?
Thanx in advance,
Greg Rowland|||Hi Siva,
It is Content Manager role for the admin login. Anonymous access is disabled
and Integrated Windows authentication is checked.
The server 2003 is just installed with IS6.0 and reporting services only.
Any hints?
"siva.jasthi@.gmail.com" wrote:
> See what role-based security you have.
> At the "Home" folder, go to Properties --> Security and see the roles
> assigned to the admin. If you don't have "Content Manager" role
> assigned to you, you can not upload the files.
>
> Shang wrote:
> > When I open Report Manager through IE on server 2003 installed with reporting
> > service and login as an admin through remote desktop connection, I can see
> > all the features but disabled. I can not Upload File, Add New Data Source
> > etc.. What is wrong? Please help.
>|||Greg,
Please inform me of any possible solutions since we share a similar problem.
"Greg Rowland" wrote:
> Hi Bruce,
>
> Having similar problem.
> Fresh install of Windows 2003 Standard, and IIS, SP1
> Fresh Install of SQL 2005 w/ Reporting Services
> Fresh install of Visual Studio 2005
>
> Can not create new folder etc... Logged on as user with Administrative
> privileges or Local Administrator or Domain Administrator, no difference?
>
>
> Thanx in advance,
>
> Greg Rowland
>
>
>|||I found a change to http://localhost/reports instead of
http://computername/reports
made the difference. But why?
"Greg Rowland" wrote:
> OK
>
>

Disable web connection in Microsoft Document Explorer

How do I disable Internet connectivity in the Microsoft Document Explorer - the help system?

Thanks,

Tom Morris

Use "Try local only, not online" option in Menu->Tools->Option->Online->When loading Help Content

Disable updates via triggers - how?

Hi,
We use push transactional replication successfully and are about to bring a
few remote on-sites into the fold. The connection speed will be low - in the
64k/128k range. As I understand it "Not for replication" means that a trigger
on the subcriber will not fire if the update is generated via a replication
process, i.e. all updates are pushed from the publisher. If this is correct
is there any way to reverse the logic, i.e. don't replicate updates on the
publisher that are caused by triggers and assume the subscriber will have the
appropriate triggers in place to do the update.
We are interested in doing this to reduce the amount of network bandwidth
consummed be replication (this will be critical when the remote sites come
on-line).
Can this be done?
Cheers, Peter
The not for replication switch means that replication related activity
will not cause the trigger, constraint, identity property to be enforced
if the triggering activity occurs by a replication process. This could
happen on the publisher or subscriber.
There is a way to bypass the replication process. What you do is add
filters to your tables which look like this
select * from published tables where 1=1.
A filter proc will be created which the log reader agent uses when
figuring out what commands are to be written to the distribution agent.
Change this filter to always return 0 when you want the commands to be
ignored. Change it back to 1 when you want the commands to be processed.
The problem is you can't alter procs via a trigger.
The way I handle things like this is to build the logic into the
replication proc that applies these transactions on the subscriber(s).
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
|||Hi Hillary,
Thanks for the response.
Where do I go to add the filter: "select * from published tables where 1=1."?
When and where would I change the filter?
I do have a copy of your fine book - what sections are relevent to this
particular issue.
I presume the following isn't relevent to my circumstance as what I'm trying
to do reduce network traffice. Or I have I misunderstood?

> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
Cheers, Peter
"Hilary Cotter" wrote:

> The not for replication switch means that replication related activity
> will not cause the trigger, constraint, identity property to be enforced
> if the triggering activity occurs by a replication process. This could
> happen on the publisher or subscriber.
> There is a way to bypass the replication process. What you do is add
> filters to your tables which look like this
>
> A filter proc will be created which the log reader agent uses when
> figuring out what commands are to be written to the distribution agent.
> Change this filter to always return 0 when you want the commands to be
> ignored. Change it back to 1 when you want the commands to be processed.
> The problem is you can't alter procs via a trigger.
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
> --
> 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
>
|||You are correct, making these change to the replication procs used to
synchronize your data will not minimize network traffic. Replicating the
execution of stored procedures will, if the majority of your transactions
occurring on the publisher affect more than one row.
Have a look at this post for an example of how to make the log reader agent
bypass transactions.
http://groups-beta.google.com/group/...c?dmode=source
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:87E55BFF-BCB2-4C20-B5F5-9C48B3B8064D@.microsoft.com...[vbcol=seagreen]
> Hi Hillary,
> Thanks for the response.
> Where do I go to add the filter: "select * from published tables where
> 1=1."?
> When and where would I change the filter?
> I do have a copy of your fine book - what sections are relevent to this
> particular issue.
> I presume the following isn't relevent to my circumstance as what I'm
> trying
> to do reduce network traffice. Or I have I misunderstood?
>
> Cheers, Peter
>
> "Hilary Cotter" wrote:

订阅：博文 (Atom)

2012年3月20日星期二

Disconnects from Sql Server

disconnection sessions

disconnecting yourself after setting db to single user

disconnecting yourself after setting db to single user

disconnecting yourself after setting db to single user

2012年3月19日星期一

Disconnect Client Connection via T-SQL/Store Proce

2012年3月7日星期三

Disapearing Records

Disallow connection for a second or two

Disallow connection for a second or two

Disallow connection for a second or two

2012年2月24日星期五

disabled fields, such as Upload File, in Report Manager

Disable web connection in Microsoft Document Explorer

Disable updates via triggers - how?

Display date

Blog Archive

About Me