Help! SQL Server services and Agent Service Keep restarting

There are no indications as to why. Every 8 minutes or so the event log
records:
Event Type:Information
Event Source:Service Control Manager
Event Category:None
Event ID:7035
Date:5/29/2007
Time:10:15:49 AM
User:MEMOPROD\cluster
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
control.
THEN...
Event Type:Error
Event Source:ClusSvc
Event Category:Failover Mgr
Event ID:1069
Date:5/29/2007
Time:10:15:49 AM
User:N/A
Computer:MEMOSQL1
Description:
Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7034
Date:5/29/2007
Time:10:16:50 AM
User:N/A
Computer:MEMOSQL1
Description:
The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
done this 5 time(s).
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7011
Date:5/29/2007
Time:10:17:46 AM
User:N/A
Computer:MEMOSQL1
Description:
Timeout (30000 milliseconds) waiting for a transaction response from the
MSSQLSERVER service.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Any Ideas?
Hello Paul is there anything in the cluster log?
John Vandervliet
"Paul Riker" wrote:

> There are no indications as to why. Every 8 minutes or so the event log
> records:
> Event Type:Information
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7035
> Date:5/29/2007
> Time:10:15:49 AM
> User:MEMOPROD\cluster
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service was successfully sent a stop
> control.
> THEN...
>
> Event Type:Error
> Event Source:ClusSvc
> Event Category:Failover Mgr
> Event ID:1069
> Date:5/29/2007
> Time:10:15:49 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Cluster resource 'SQL Server' in Resource Group 'Disks' failed.
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7034
> Date:5/29/2007
> Time:10:16:50 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly. It has
> done this 5 time(s).
> Event Type:Error
> Event Source:Service Control Manager
> Event Category:None
> Event ID:7011
> Date:5/29/2007
> Time:10:17:46 AM
> User:N/A
> Computer:MEMOSQL1
> Description:
> Timeout (30000 milliseconds) waiting for a transaction response from the
> MSSQLSERVER service.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> Any Ideas?

Help! Removing SQLServer builtin/Administrators

Hi,
I'm having trouble maintaining security on SQLServer as
everyone who is a member of the Local Administrators (on
the system) has full control by default as SQLServer has
builtin/Administrators added by default to its System
Administrators List.
Last time I removed this group, so many things went
wrong. I dont' want the entire local administrators to be
the SQL Admins. So please suggest a way where I can
safely remove the default Built-in\Adminstrators from the
SQLServer security. Any article will be helpful.
ThanksIf you subscribe to SQL Server Professional, I wrote a piece on this:
http://www.pinpub.com/html/main.isx?sub=64&story=783
Briefly, you can add a domain account to the sysadmin role first and then
remove the BUILTIN\Administrators role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:1475301c3c352$a5d28e00$a601280a@.phx
.gbl...
Hi,
I'm having trouble maintaining security on SQLServer as
everyone who is a member of the Local Administrators (on
the system) has full control by default as SQLServer has
builtin/Administrators added by default to its System
Administrators List.
Last time I removed this group, so many things went
wrong. I dont' want the entire local administrators to be
the SQL Admins. So please suggest a way where I can
safely remove the default Built-in\Adminstrators from the
SQLServer security. Any article will be helpful.
Thanks|||Adding to Tom's suggestion, you could also add a domain local group (with
only members you wish to have sysadmin equivalence) and grant that group
sysadmin permission -- then remove the builtin\administrators group. It's
also not a bad idea to reset the 'sa' account password at the same time in
case you need that account to log back in (in mixed mode).
Steve
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:1475301c3c352$a5d28e00$a601280a@.phx
.gbl...

quote:

---

> Hi,
> I'm having trouble maintaining security on SQLServer as
> everyone who is a member of the Local Administrators (on
> the system) has full control by default as SQLServer has
> builtin/Administrators added by default to its System
> Administrators List.
> Last time I removed this group, so many things went
> wrong. I dont' want the entire local administrators to be
> the SQL Admins. So please suggest a way where I can
> safely remove the default Built-in\Adminstrators from the
> SQLServer security. Any article will be helpful.

---

Help! Don't understand transactions

Hi guys,
I'm having troubles with transaction control.
I recently decided to add transaction control to some operations on an
app I am developing (Dreamweaver MX 2004, ASP, VBScript, ADO and
SQLServer 2000).
I started by doing a test web page with an update of up to 3 different
tables driven by simple pushbuttons, and more buttonds for BEGIN TRAN,
COMMIT and ROLLBACK.
Worked perfectly. It did exactly what I wanted, and even though I was
using separate record set variable for each of the commands and tables,
and closing my connection after every BEGIN TRAN.
So I decided to build that same kind of logic in my app, and test it. I
built in an error in a chain of inserts and updates I am doing, and I
put an error trap around each of the possible error-producing actions.
When an error occur, I check if a transaction has been started (which is
indicated by a flag set by my code when calling up the BEGIN TRAN), and
if yes, I rollback.
The results are very unimpressive: it doesn't work. The error that I
built in is to insert a duplicate row, but before that, I have inserted
proper rows. The code detects the error, executes the roll back, but the
rows inserted after the BEGIN TRAN and before the error are in the DB !
I.e. it looks like the BEGIN TRAN does not work. How can I check this,
and what could be the reasons that my test app works fine, and my real
app doesn't ? In the real app, the error is happening on a stored
procedure call, which is the one inserting the rows. Is the behaviour of
an SP different than a normal SQL statement sent over ADO ?
I have attached my test page.
Can you tell me:
- when should I issue the BEGIN TRAN ? Before the very first INSERT /
UPDATE, or can it be even before that (I do quite a bit of reading
(SELECT) before doing the first INSERT)
- on which connection
- should I have all the updating / inserting SQL statements going on one
and the same connection, or can I use different connections ?
- somebody told me there was a difference if I was using the
ActiveConnection object as opposed to just a connection object.
- I changed my routine that does the BEGIN / COMMIT / ROLLBACK to this
in my real app:
sub TransControl(action)
Set conn= Server.CreateObject("ADODB.Connection")
conn.Open MM_SDS_STRING
select case action
case "BEGIN"
sql= "BEGIN TRANSACTION"
Response.Write("Starting transaction <br>")
case "COMMIT"
sql= "COMMIT TRANSACTION"
Response.Write("Committing transaction <br>")
case "ROLLBACK"
sql= "ROLLBACK TRANSACTION"
Response.Write("Rolling back transaction <br>")
case else
end select
Set rs= conn.Execute(sql)
conn.Close
set conn= nothing
set rs = nothing
end sub
Does this make any difference to my DoIt routine in the test page ?
- and finally: I just noticed that I use the follwing 2 syntaxes:
conn.Open <connection string>
and also:
conn.Open = <connection string>
What's the right syntax ? Starngely, both seem to work
I am a bit lost, and would appreciate a lot if you could point me in the
right direction.
Thanks
Bernard
bthouin wrote:[vbcol=seagreen]
The transaction must always be on the same connection. You want a
transaction to run as quickly as possible, so issue if you are executing
multiple procedures and they must be a part of the same transaction,
issue the begin tran, execute the procedures, and then rollback or
commit the transaction.
For example:
create table tran_test( col1 int)
go
create proc dbo.tran_test_insert (@.i int)
as
Insert into dbo.tran_test (col1) values (@.i)
go
-- test 1
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Rollback
Select * from dbo.tran_test
go
-- test 2
Select * from dbo.tran_test
Begin Tran
Exec dbo.tran_test_insert 1
Exec dbo.tran_test_insert 2
Exec dbo.tran_test_insert 3
Commit Tran
Select * from dbo.tran_test
go
drop proc dbo.tran_test_insert
go
drop table dbo.tran_test
go
David Gugick
Imceda Software
www.imceda.com
|||Hi David,
Thanks for answer. I'm aware of the need to have quick transactions. But
I'm not working directly at the DB level, i.e. I'm not only using stored
procedures, I'm mostly building my SQL statements and executing them
through ADO. So your example is clear, but I can't apply it directly.
For one, there is no explicit connection in stored procedures. So that
makes it harder for me, as I am using the typical VB-like ASP ADO
commands. And Dreamweaver, when you let it generate all the data access
stuff, builds by default a new connection for every access, and closes
it after the access, plus it destroys the connection object. Although I
am not using Dreamweaver-generating capabilities, I have "inherited"
that usage concept of - constructing the access object; - accessing the
data; - closing the connection and destroying the access object, just to
keep memory clean.
And, what IS a connection ? Is it that which is established by saying:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open <connection string>
Or the alternative:
Set rs_T = Server.CreateObject("ADODB.Command")
rs.ActiveConnection = <connection string>
?
Is there any difference between the 2 possibilities ?
What's the difference between the "conn" connection object in the 1st
possibility and the "rs" command object (also called record set) in the
2nd possibility ?
All this is not clear to me, and I'd be VERY thankful for some answers.
Regards
Bernard
David Gugick wrote:
> bthouin wrote:
>
> The transaction must always be on the same connection. You want a
> transaction to run as quickly as possible, so issue if you are executing
> multiple procedures and they must be a part of the same transaction,
> issue the begin tran, execute the procedures, and then rollback or
> commit the transaction.
> For example:
> create table tran_test( col1 int)
> go
> create proc dbo.tran_test_insert (@.i int)
> as
> Insert into dbo.tran_test (col1) values (@.i)
> go
> -- test 1
> Select * from dbo.tran_test
> Begin Tran
> Exec dbo.tran_test_insert 1
> Exec dbo.tran_test_insert 2
> Exec dbo.tran_test_insert 3
> Rollback
> Select * from dbo.tran_test
> go
> -- test 2
> Select * from dbo.tran_test
> Begin Tran
> Exec dbo.tran_test_insert 1
> Exec dbo.tran_test_insert 2
> Exec dbo.tran_test_insert 3
> Commit Tran
> Select * from dbo.tran_test
> go
> drop proc dbo.tran_test_insert
> go
> drop table dbo.tran_test
> go
>
>
|||bthouin wrote:
> Hi David,
> Thanks for answer. I'm aware of the need to have quick transactions.
> But I'm not working directly at the DB level, i.e. I'm not only using
> stored procedures, I'm mostly building my SQL statements and
> executing them through ADO. So your example is clear, but I can't
> apply it directly. For one, there is no explicit connection in stored
> procedures. So that makes it harder for me, as I am using the typical
> VB-like ASP ADO commands. And Dreamweaver, when you let it generate
> all the data access stuff, builds by default a new connection for
> every access, and closes it after the access, plus it destroys the
> connection object. Although I am not using Dreamweaver-generating
> capabilities, I have "inherited" that usage concept of - constructing
> the access object; - accessing the data; - closing the connection and
> destroying the access object, just to keep memory clean.
>
[vbcol=seagreen]
That's not true. It may be that the way the code is generated, the
procedure executes on a short-lived connection, but one is there. I
don't know if this is a web app or not, but in any case, using that
paradigm does not allow you to use a transaction the way you want. Every
time you create a new connection, by using Conn.Open, you get a "new"
connection. It's possible if you are using connection pooling (and you
should be with that type of design) that you "could" get the same
connection twice, but you can never depend on it.
If you need to mix procedures and SQL in any combination and have those
statements be in the same transaction, you need to use the same
connection.
David Gugick
Imceda Software
www.imceda.com

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!
|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

You should be able to with a where clause on some accumulator. For example, take the level column in the following query:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID
)
SELECT *
FROM Employee_CTE
WHERE level <= 2
ORDER BY level desc

Just vary the value in the where clause to vary the number of times it recurses.

|||

Thank you Louis. But that denpends on the recursion would end properly, and it'll calculate all the levels. If the number of the levels is too large, or there's a loop, there would be a problem. I tried to use the MAXRECURSION hint, but it gave me a syntax error. What I have is:

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

option (MAXRECURSION 10)
)

Then it told me the syntax for option (MAXRECURSION 10) is wrong. You have any ideas what the right way is?

Thanks!

|||

Yeah, it goes on the query, not the CTE (kind of wierd syntax, but anyhow)

WITH Employee_CTE(EmployeeID, ManagerID, level)
AS
(
SELECT EmployeeID, ManagerID, 1 AS level
FROM HumanResources.Employee as Employee

UNION ALL

SELECT Employee.EmployeeID, Employee.ManagerID, level + 1
FROM HumanResources.Employee as Employee
INNER JOIN Employee_CTE
on Employee.ManagerID= Employee_CTE.EmployeeID

)

SELECT *
FROM Employee_CTE
Option (MAXRECURSION 10)

Help! Can I control the recursion times of a recursive CTE?

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

Sorry, wrong format in last post. Here's the question:

I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!

|||Check out maxrecursion here http://msdn2.microsoft.com/en-us/library/ms175972.aspx|||Thanks. I tried it but got some error:

CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 1)
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
OPTION (MAXRECURSION 1000)
)

It says Incorrect syntax near the keyword 'OPTION'. What's wrong with it?
Thanks!|||

You need to use maxrecursion in the projection out of the CTE, the below works for me in SP1;

use tempdb

go

CREATE TABLE Table1 ( fr int, t int)

go

INSERT Table1 VALUES (1, 2)

INSERT Table1 VALUES (2, 3)

INSERT Table1 VALUES (3, 4)

INSERT Table1 VALUES (1, 3)

INSERT Table1 VALUES (1, 4)

INSERT Table1 VALUES (4, 1)

go

WITH CTE_Sample (fr, t, level) AS

(

SELECT Table1.fr, Table1.t, 1 AS level

FROM Table1

WHERE fr=1

UNION ALL

SELECT Table1.fr, Table1.t, level+1

FROM Table1

INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t

)

select *

from CTE_Sample

OPTION (MAXRECURSION 2);

go

|||

Thank you Euan! It works!

btw. Do you know if I can join CTE_Sample with other tables or other CTEs? I tried but got some errors.

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
)

WITH CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length

It says "Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
" Then I added a semicolon at the end of CTE_Sample, then it says "Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ';'.
".

What should I do?

|||

I'm not 100% clear on what yuo are actually trying to do but in principle you can use CTEs very flexibly, I suggest reviewing Boks On Line and doing some online searching for them, there are lots of examples.

You can also try looking in the T-SQL forum for examples in there.

|||

You should separate the With statements with a comma and remove the second "with".

WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
) ,

CTE_length (t, length) AS
(
SELECT t, min(level) AS length
FROM CTE_Sample
where fr=1
group by t
--OPTION (MAXRECURSION 2)
)
select fr, t, level
from CTE_Sample
where CTE_Sample.t=CTE_Path.t and CTE_Sample.level=CTE_path.length