known, expected or weird behavior ?

Hi all,
Please have a look at a script below:
USE tempdb
DECLARE @.t TABLE (c1 uniqueidentifier, c2 uniqueidentifier)
INSERT @.t (c1, c2)
SELECT y.id, NULL
FROM ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5
) x (id)
CROSS JOIN
( SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID()
) y (id)
UPDATE t
SET c2 = y.c2
FROM @.t t,
( SELECT c1, NEWID()
FROM ( SELECT DISTINCT c1
FROM @.t
) x (c1)
) y (c1, c2)
WHERE t.c1 = y.c1
SELECT * FROM @.T
I would expect c2 column value to be the same accross all records where c1
column value is the same.
But in fact c2 column is unique accross the table. I looked at plan and can
see what it's doing and I can rewrite it in a proper way but question
remains, - why is that? Can comeone explain that behavior?
Thank a lot in advance
AlexThat's the way functions work. They are executed for each row of the final
result.
What are you trying to do? If you need uniqueidentifier values for each
integer value, insert them into a temporary table (i.e. table variable)
before issuing the update.
ML
http://milambda.blogspot.com/|||my colleague came accros this piece of code. She reworked it with use of
temp table, and I could find a workaround with derived table. But why the
function is executed in the final set? The code imlies newid() should be
called inside derived table y, and then derived table y joins the table var?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BBDCFC04-63E4-4408-B153-26A1C5A9EB2B@.microsoft.com...
> That's the way functions work. They are executed for each row of the final
> result.
> What are you trying to do? If you need uniqueidentifier values for each
> integer value, insert them into a temporary table (i.e. table variable)
> before issuing the update.
>
> ML
> --
> http://milambda.blogspot.com/|||We'll know this for sure as soon as we find another system function that
produces as random results as NEWID(). :)
Have you tried using a user-defined function that returns a random result?
ML
http://milambda.blogspot.com/|||Interesting, I used RAND() and float instead of NEWID() and
uniqueidentifier, and in this case RAND() was applied to the final result
set too, but the difference seems to be that RAND() was called only once
since ALL records have the same float value.
No, I didn't used UDF yet, maybe later today when I have time.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
> We'll know this for sure as soon as we find another system function that
> produces as random results as NEWID(). :)
> Have you tried using a user-defined function that returns a random result?
>
> ML
> --
> http://milambda.blogspot.com/|||NEWID is special. It is called for every row in a query. All other function
(the I know of) are
called only once in a query. Hence the difference between RAND and NEWID.
USE northwind
SELECT
NEWID() AS myNEWID
,RAND() AS myRand
,CURRENT_TIMESTAMP AS myTS
FROM "Order Details"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <alex_remove_this_mak@.telus.net> wrote in message news:dB6Ef.153384$6K2.43614@.edtnps
90...
> Interesting, I used RAND() and float instead of NEWID() and uniqueidentifi
er, and in this case
> RAND() was applied to the final result set too, but the difference seems t
o be that RAND() was
> called only once since ALL records have the same float value.
> No, I didn't used UDF yet, maybe later today when I have time.
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
>|||This is the expected behaviour of RAND.
ML
http://milambda.blogspot.com/|||I was wondering whether getdate() is called for each row or for the set...
Would it show on a big enough set?
ML
http://milambda.blogspot.com/|||thanks guys a lot
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D44C180C-8F26-461D-A552-9365B4DA27C9@.microsoft.com...
> This is the expected behaviour of RAND.
>
> ML
> --
> http://milambda.blogspot.com/|||Getdate() is normally only called once.
However, Itzik Ben-Gan came up with a really clever workaround for both
rand() and getdate().
Normally, you can't put getdate() or rand() in a User Defined Function, but
you can put them in a view, and then have your function select from the
view. You can then put your function in the select list, to have the
getdate() or rand() regenerated for each row.
Note that it might not look like getdate() is called for every single row,
because of the precision of the datatype. The function might be called
repeatedly more quickly than the getdate() value changes. But if you have
enough rows, you'll see that they aren't ALL the same, even though there
could be duplication.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:30C35A84-706B-48F3-8C41-69ECB598DA13@.microsoft.com...
>I was wondering whether getdate() is called for each row or for the set...
> Would it show on a big enough set?
>
> ML
> --
> http://milambda.blogspot.com/
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIA
Hi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>
|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
|||Hi Dan,
I wrote the following script and tested it:
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:

> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

killing a process with a variable

I wish to kill all the processes for a given database.
have written the script that gives me all the spids for the database,
however I get an error when trying to execute;
KILL @.spid;
(Incorrect syntax near @.spid)
where @.spid is declared as a smallint.
Can anybody help?
TIAHi
You can't pass a variable. You need to create dynamic sql to execute that:
DECLARE @.exstring VARCHAR(20)
SELECT @.exstring = 'KILL ' + @.spid
executesql @.exstring
Regards
Mike
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>|||Hi Dan - I think this works.
DECLARE @.i INT
DECLARE @.strSQL NVARCHAR(255)
SET @.i = 73
SET @.strSQL = 'KILL ' + CAST(@.i AS CHAR (2))
--PRINT @.strSQL
EXEC sp_executesql @.strSQL
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi,
If your Sql server version is 2000 then go for ALTER Database command rather
than KILL command.
ALTER Database <dbname> set single_user with rollback immediate
The above command will remove all the connected users to that database
immediately. After the activity u can change the db the multiuser.
ALTER Database <dbname> set multi_user
Thanks
Hari
MCDBA
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:eg10JwhpEHA.4008@.TK2MSFTNGP14.phx.gbl...
>I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>|||Hi Dan,
I wrote the following script and tested it:
--
Use master
go
SET NOCOUNT ON
DECLARE @.strSQL varchar(255)
PRINT 'Killing Users'
PRINT '--'
CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DATABASE NAME
HERE'
DECLARE @.spid varchar(10)
DECLARE @.dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'Killing ' + @.spid
SET @.strSQL = 'KILL ' + @.spid
EXEC (@.strSQL)
END
FETCH NEXT FROM LoginCursor INTO @.spid, @.dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
PRINT 'Done'
go
--
Just replace 'YOUR DATABASE NAME HERE' with your database name.
Sasan
"Dan" wrote:
> I wish to kill all the processes for a given database.
> have written the script that gives me all the spids for the database,
> however I get an error when trying to execute;
> KILL @.spid;
> (Incorrect syntax near @.spid)
> where @.spid is declared as a smallint.
> Can anybody help?
> TIA
>
>

kill process id with host

I need a script to kill several process ids by a certain host....need help
ASAP...thanksUse the below command:-
KILL SPID
SPID you can get from master..sysprocesses table
Thanks
Hari
SQL Server MVP
"Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
news:361BFBE0-E309-430E-8B34-E8D96B906AD2@.microsoft.com...
>I need a script to kill several process ids by a certain host....need help
> ASAP...thanks|||There are about 100 dead process ids from 1 host, are you saying this will
kill all the process ids from this single host?
"Hari Pra" wrote:

> Use the below command:-
> KILL SPID
> SPID you can get from master..sysprocesses table
> Thanks
> Hari
> SQL Server MVP
>
> "Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
> news:361BFBE0-E309-430E-8B34-E8D96B906AD2@.microsoft.com...
>
>|||Declare @.Host VarChar(100)
Set @.Host = '<Put Computer HostName Here>'
Declare @.Sql VarChar(20)
While Exists
(Select * From master..SysProcesses
Where HostName = @.Host)
Begin
Select @.Sql = 'Kill ' +
LTrim*Str(Max(Spid), 5,0))
From master..sysProcesses
Where HostName = @.Host
-- --
Exec(@.Sql)
End
"Jamie Elliott" wrote:
> There are about 100 dead process ids from 1 host, are you saying this will
> kill all the process ids from this single host?
> "Hari Pra" wrote:
>

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database
> before daily restore job in DR machine. >
> For testing, the script is getting the spid from sysprocesses and
> sysdatabases and store the specific spid which accessing the user DB
> into cursor and sp_who spid. You're making this way harder than it has to be: USE master GO ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill an user process with script

Hi all,
I am writing a script to kill any process connect to the user database before daily restore job in DR machine.

For testing, the script is getting the spid from sysprocesses and sysdatabases and store the specific spid which accessing the user DB into cursor and sp_who spid.
HOwever, if I change the sp_who to kill, it shows error. Anything wrong in such script? Thanks in advance

> I am writing a script to kill any process connect to the user database

> before daily restore job in DR machine.

>

> For testing, the script is getting the spid from sysprocesses and

> sysdatabases and store the specific spid which accessing the user DB

> into cursor and sp_who spid.

You're making this way harder than it has to be:

USE master

GO

ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

|||

Hi,

I have the same problem, but suggested solution wouldn't resolve it.

What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

Thanks,

Leonid

|||

Try this

declare @.kill_stmt nvarchar(10), @.cntr int

declare @.kill_tbl table

(ident int identity(1,1),

spid int,

loginame nvarchar(128),

dbid int)

insert into @.kill_tbl(spid, loginame, dbid)

Select sp.spid, sp.loginame, sb.dbid

from master..sysprocesses sp

inner join master..sysdatabases sb on sp.dbid = sb.dbid

where sb.name = 'Test' --replace the database name with desired database name

set @.cntr = 1

while @.cntr <= (select max(ident) from @.kill_tbl)

begin

Select @.kill_stmt = 'KILL ' + convert(varchar, spid) from @.kill_tbl where ident = @.cntr

exec (@.kill_stmt)

select @.cntr = @.cntr + 1

end

This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

Hope this works for you!

|||

This query can be used to get the list of connections that need to be killed except your connection

SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@.@.SPID

|||

SELECT * FROM SYS.DM_EXEC_CONNECTIONS

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.

|||Thanks! This works just fine!

Kill

I need to let a third party security app run a script as a
system admin to drop and recreate a database. Before this
will run, of course I need to make sure all connections to
that database are dropped.
Is there a command that will kill all connections to a
database?Sometimes you just have to trace other programs that can do this. I traced
what happens when you disconnect a database and someone is using it.
select spid from master..sysprocesses where dbid=db_id('<database name>')
Then that spid result is fed to a kill statement.
Should warn you that this is a tricky thing that you are doing. Certain
kinds of connections, such as those with SQL Query Analyzer and Enterprise
Manager, do not drop very easily. Sometimes connections keep going. A
drastic step might be to use a net stop/net start to restart MSSQLserver.
That will certainly free up all the connections, though the database might
go into recovery.
But no, if the Clear connection button on the Detach Database function in
SQL EM doesn't call a command, I doubt you are going to find one.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||Add these lines to the top of the script.
ALTER DATABASE 'MyDBName' SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE 'MyDBName' SET ONLINE
GO
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||I know that I've seen a stored procedure on the net that will kill all
user connections. Try doing a search in google for something like "sp
kill all users" without the quotes.
Aaron
Andy Svendsen wrote:
> Sometimes you just have to trace other programs that can do this. I traced
> what happens when you disconnect a database and someone is using it.
> select spid from master..sysprocesses where dbid=db_id('<database name>')
> Then that spid result is fed to a kill statement.
> Should warn you that this is a tricky thing that you are doing. Certain
> kinds of connections, such as those with SQL Query Analyzer and Enterprise
> Manager, do not drop very easily. Sometimes connections keep going. A
> drastic step might be to use a net stop/net start to restart MSSQLserver.
> That will certainly free up all the connections, though the database might
> go into recovery.
> But no, if the Clear connection button on the Detach Database function in
> SQL EM doesn't call a command, I doubt you are going to find one.
>sql

Kicking off replication agents with Script

Hi all,

I have 5 laptops, 1 laptop with SQL Server 2000 and the other 4 with SQL Server MSDE Rel A. I had a "Merge Replication" scheme that I had to set up. The laptop with 2000 will be the main Publisher/distributor and publishes/merges to two of the MSDE laptops(these laptops have pull subscriptions that pull from the 2000 laptop). These 2 MSDE laptops in turn Re-publish their data to the last 2 laptops and my scheme ends there. I have succesfully been able to Script this entire "Merge Replication" scheme, and I ran it. It creates all Components needed for my scheme to work.

1 issue though. It doesnt kick start the agents :eek: , so the replication scheme is just dormant.

Does anyone have any examples of scripts that I could use to kick off all the agents (snapshot,merge agents etc) and maybe to stop them too. I seriously need this, because we will need to have the entire process scripted.

Thanks

'Wale

p.s.
Also does anyone have sample scripts to alter settings for the SQL SERVER AGENT, for example altering its "Log On" parameter to allow a particular user with Admin privileges and also to switch its start mode to "Automatic"Has anyone used the "sp_startpublication_snapshot" procedure. When the Snapshot Agent is started, does this mean all other Agents automatically start - up?

Keeping Domain & SQL Access In Sync

Does anyone know of a sample script I could get access to that would
automatically compare disabled/deleted domain accounts to the SQL Security
Logins and keep them "in sync" without any interaction from the SQL
administrator? We're wanting to eliminate the need of sending manual
notifications to the SQL administrator of terminated employees and running
the sp_denylogin. Instead, we want to just create a script that would go ou
t
to all of our SQL servers and automatically remove accounts no longer active
in the domain itself. Does Active Directory provide any tools to do this?
Any help would be greatly appreciated.
Thanks.
ColetteHi
Why don't you rather use Domain Groups?
Give a domain group the correct access, and add the user to the group. A
user can be in multiple groups and when the NT accounts gets added or
removed, there is no maintenance from the DBA side.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Colette" wrote:

> Does anyone know of a sample script I could get access to that would
> automatically compare disabled/deleted domain accounts to the SQL Security
> Logins and keep them "in sync" without any interaction from the SQL
> administrator? We're wanting to eliminate the need of sending manual
> notifications to the SQL administrator of terminated employees and running
> the sp_denylogin. Instead, we want to just create a script that would go
out
> to all of our SQL servers and automatically remove accounts no longer acti
ve
> in the domain itself. Does Active Directory provide any tools to do this?
> Any help would be greatly appreciated.
> Thanks.
> Colette|||That's what I've suggested but they do not want to add additional groups to
Active Directory. Strange...but true. I have instructed them we need to g
o
this route or they need to manually notify the SQL Admins based off the
"security form" of a termination involving SQL access.
P.S. The servers were already set up this way prior to my hire. I'm trying
to fix it. Just wanted to throw that out there...
Thanks again.
Colette
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Why don't you rather use Domain Groups?
> Give a domain group the correct access, and add the user to the group. A
> user can be in multiple groups and when the NT accounts gets added or
> removed, there is no maintenance from the DBA side.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Colette" wrote:
>|||Hi
Then you will need to write some code for this.
Call sp_validatelogins, this will give you a list of all NT Logins that are
no longer valid.
Then based on that result, call sp_revokelogin to remove the user from the
server.
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/
"Colette" <Colette@.discussions.microsoft.com> wrote in message
news:4AE2F5A1-DA00-4591-BC54-76DEC19D08CA@.microsoft.com...[vbcol=seagreen]
> That's what I've suggested but they do not want to add additional groups
> to
> Active Directory. Strange...but true. I have instructed them we need to
> go
> this route or they need to manually notify the SQL Admins based off the
> "security form" of a termination involving SQL access.
> P.S. The servers were already set up this way prior to my hire. I'm
> trying
> to fix it. Just wanted to throw that out there...
> Thanks again.
> Colette
> "Mike Epprecht (SQL MVP)" wrote:
>