MSSQL 2000: How can we track table/sp/function changes?

My company would like to start keeping track of everytime a table, stored procedure, or function is changed.
What is the best way to do this?Option 1)
Create a database for storing metadata on all the other databases. Nightly, run a query that loops across all database and records all the objects along with their checksum values, and notes any objects who's checksum value has changed.

Option 2)
Upgrade to SQL Server 2005 and create database triggers to log changes to objects.|||sourcegears vault, visual sourcesafe. nothing makes it into our software\database builds that is not in our sourcecode management software.|||Revoke sysadmin/DBO rights from everyone but the DBAs.|||As suggested you cna take help of Visual Sourcesafe or PVCS in order to take care of object changes.

MSSMS 05 generates all stored procedures as dynamic sql

Hi,
For some odd reason my MSSMS05 has started generating all stored
procedure scripts as dynamic sql. I cannot seem to find any options
under options-->scripting that seem related to this.
To clarify, when right-clicking a stored procedure and selecting
'modify' or any of the options under 'Script stored procedure as', the
output is wrapped as a dynamic sql statement, wrapped in exec
sp_executesql statements.
What option have I foolishly turned on, and where can I turn it off
again?
best regards,
Henrik
Try the "Include IF NOT EXISTS clause" option under Tools | Options /
Scripting. Since this requires an IF block, and you can't have a GO inside
of this, you need to use dynamic SQL.
"Henrik" <nielsen.henrik@.gmail.com> wrote in message
news:185b136f-b493-4047-bb95-e96b44713c2c@.j44g2000hsj.googlegroups.com...
> Hi,
> For some odd reason my MSSMS05 has started generating all stored
> procedure scripts as dynamic sql. I cannot seem to find any options
> under options-->scripting that seem related to this.
> To clarify, when right-clicking a stored procedure and selecting
> 'modify' or any of the options under 'Script stored procedure as', the
> output is wrapped as a dynamic sql statement, wrapped in exec
> sp_executesql statements.
> What option have I foolishly turned on, and where can I turn it off
> again?
> best regards,
> Henrik
|||That did it, thank you very much Aaron!
On Dec 4, 1:18 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:[vbcol=seagreen]
> Try the "Include IF NOT EXISTS clause" option under Tools | Options /
> Scripting. Since this requires an IF block, and you can't have a GO inside
> of this, you need to use dynamic SQL.
> "Henrik" <nielsen.hen...@.gmail.com> wrote in message
> news:185b136f-b493-4047-bb95-e96b44713c2c@.j44g2000hsj.googlegroups.com...
>
>
sql

MSMQ

Can SQL server from a trigger or Stored procedure, post a
message to MSMQ?
Or can SQL server from a trigger or stored procedure, run
or fire a VBscript to do the same?Yes, using the sp_oamethod, haven't done it but there are a few sites out
there that walk through doing it, here's one.
http://www.tagconsulting.com/Show.asp?Id=1000
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Peter Upfold" <peter@.statmon.com> wrote in message
news:054001c36767$98c11930$a401280a@.phx.gbl...
> Can SQL server from a trigger or Stored procedure, post a
> message to MSMQ?
> Or can SQL server from a trigger or stored procedure, run
> or fire a VBscript to do the same?
>

Msgbox using stored procedure

Is there a way to throw a message Box using a stored procedure?I'm not aware of any way of doing it through T-SQL, I would imagine you'd have to do something in VB or VBS in order to get a MsgBox to pop up for you. How it would be called from the Stored Proc, I'm not aware of (unless you exit out using xp_cmdshell and just have that call your exe or vbs file)

So something like:

Create proc msg_proc
as
(--any code--)
exec master..xp_cmdshell 'c:\vbs\MessageBox.vbs'
(--any other code--)
return

Hope this helps, its crude, but in my lack of expertise I'm not aware of a more elegant way.

-Greg

Msgbox from Stored Procedure

Hi,
Can I Call MsgBox Function From SP ?No. Transact SQL doesn't have any UI functionality. For that you need to use
a host language such as VB or C# for example.
David Portas
SQL Server MVP
--|||As long as Procedures are executed on the server that would make no sense to
display a Msgbox. If ou want to debug your sp, fill in some PRINT commands
to get some information.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DMP" <debdulal.mahapatra@.fi-tek.co.in> schrieb im Newsbeitrag
news:OVQx1XXRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can I Call MsgBox Function From SP ?
>|||Hi,
What are you trying to achieve, are you trying to debug something or
are you trying to display something to the end user.
If you are trying to debug something in a stored procedure then I
personally use a debug table posting results to the table as I go
through the stored procedure. This only any good for debuging.
If you are trying to display something to the end user then use an
OUTPUT parameter in the stored procedure and get the value on the
end-users computer and display that throught the msgbox interface.
Regards
Alex|||Thanks to everybody,
Basically I have three sp let sp1,sp2,sp3
From VB I call sp1,
sp1 call to sp2 and dpending the return value of sp2 , End user will take
dicission that
sp3 will be called or not like VB msgbox("do you want to continue ?",
vbYesNo),
If vbYes then calls to sp3 else return from sp2,sp1
This is my goal...
"Alex" <alex@.intralan.co.uk> wrote in message
news:1113985604.270498.57780@.l41g2000cwc.googlegroups.com...
> Hi,
> What are you trying to achieve, are you trying to debug something or
> are you trying to display something to the end user.
> If you are trying to debug something in a stored procedure then I
> personally use a debug table posting results to the table as I go
> through the stored procedure. This only any good for debuging.
> If you are trying to display something to the end user then use an
> OUTPUT parameter in the stored procedure and get the value on the
> end-users computer and display that throught the msgbox interface.
> Regards
> Alex
>|||you either need to design out the decision or:
All in VB:
Call SP1
Processes results
Ask User Continue?
Yes? Call SP3
SP's run not just "on" the server, but "in" the server - the only ways that
an SP can communicate is via return codes, output parameters, result sets,
print statements (rare IMHO), exceptions (raiserror), or indirectly via COM
objects - see sp_oa* sp's. (there are more methods, but I think you get the
point). IE the pre-existing communications channels via the comms protocol
in use which is normally TDS (tabular data stream) over TCP/IP.
Whats more, if you call a COM interface it really should be prompt -
stalling SQL Server for some external process of indefinite length falls
into the really really really bad technique category.
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:uKw%23dWYRFHA.244@.TK2MSFTNGP12.phx.gbl...
> Thanks to everybody,
> Basically I have three sp let sp1,sp2,sp3
> From VB I call sp1,
> sp1 call to sp2 and dpending the return value of sp2 , End user will take
> dicission that
> sp3 will be called or not like VB msgbox("do you want to continue ?",
> vbYesNo),
> If vbYes then calls to sp3 else return from sp2,sp1
> This is my goal...
> "Alex" <alex@.intralan.co.uk> wrote in message
> news:1113985604.270498.57780@.l41g2000cwc.googlegroups.com...
>sql

Msg 7404

Can anyone point me to a good resource regarding SQL Server Msg 7404? I've
got a scheduled job that runs a stored procedure that started showing the
following after the last server reboot:
Msg 7404, Sev 16: The server could not load DCOM. [SQLSTATE 42000]
A reboot usually corrects this but I only have a small, weekly window to do
this. Also need to explain to the powers at be why & what this is.
Thanks.
Paul
Hi
Unfortunately I don't think there is a good source, searching Google for
this error doesn't throw up much. You may want to check the SQL Server log
and Windows Event log to try and find out more background to this. What does
the stored procedure do?
John
"Paul" <dbapaul@.noemail.noeamil> wrote in message
news:20201A44-B46F-4F09-89D4-F44DAFB4810F@.microsoft.com...
> Can anyone point me to a good resource regarding SQL Server Msg 7404? I've
> got a scheduled job that runs a stored procedure that started showing the
> following after the last server reboot:
> Msg 7404, Sev 16: The server could not load DCOM. [SQLSTATE 42000]
> A reboot usually corrects this but I only have a small, weekly window to
> do
> this. Also need to explain to the powers at be why & what this is.
> Thanks.
> Paul

Msg 7404

Can anyone point me to a good resource regarding SQL Server Msg 7404? I've
got a scheduled job that runs a stored procedure that started showing the
following after the last server reboot:
Msg 7404, Sev 16: The server could not load DCOM. [SQLSTATE 42000]
A reboot usually corrects this but I only have a small, weekly window to do
this. Also need to explain to the powers at be why & what this is.
Thanks.
PaulHi
Unfortunately I don't think there is a good source, searching Google for
this error doesn't throw up much. You may want to check the SQL Server log
and Windows Event log to try and find out more background to this. What does
the stored procedure do?
John
"Paul" <dbapaul@.noemail.noeamil> wrote in message
news:20201A44-B46F-4F09-89D4-F44DAFB4810F@.microsoft.com...
> Can anyone point me to a good resource regarding SQL Server Msg 7404? I've
> got a scheduled job that runs a stored procedure that started showing the
> following after the last server reboot:
> Msg 7404, Sev 16: The server could not load DCOM. [SQLSTATE 42000]
> A reboot usually corrects this but I only have a small, weekly window to
> do
> this. Also need to explain to the powers at be why & what this is.
> Thanks.
> Paul

Msg 7404

Can anyone point me to a good resource regarding SQL Server Msg 7404? I've
got a scheduled job that runs a stored procedure that started showing the
following after the last server reboot:
Msg 7404, Sev 16: The server could not load DCOM. [SQLSTATE 42000]
A reboot usually corrects this but I only have a small, weekly window to do
this. Also need to explain to the powers at be why & what this is.
Thanks.
PaulHi
Unfortunately I don't think there is a good source, searching Google for
this error doesn't throw up much. You may want to check the SQL Server log
and Windows Event log to try and find out more background to this. What does
the stored procedure do?
John
"Paul" <dbapaul@.noemail.noeamil> wrote in message
news:20201A44-B46F-4F09-89D4-F44DAFB4810F@.microsoft.com...
> Can anyone point me to a good resource regarding SQL Server Msg 7404? I've
> got a scheduled job that runs a stored procedure that started showing the
> following after the last server reboot:
> Msg 7404, Sev 16: The server could not load DCOM. [SQLSTATE 42000]
> A reboot usually corrects this but I only have a small, weekly window to
> do
> this. Also need to explain to the powers at be why & what this is.
> Thanks.
> Paul

Msg 7391 / JoinTransaction returned 0x8004d00a

I'm having trouble doing distributed transactions in a stored procedure. It
boils down to this example.
This works in Query Analyzer:
--begin distributed transaction
insert into [39.80.0.26\x3].[sm].dbo.events
select * from events
where time = @.time and seq = @.seq
--commit
But it does not work in Query Analyzer when I uncomment the distributed
xaction and commit.
I get this error:
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
I'm using sqlserver 8.0.818 on Win2003 server on both sides.
I tried several things to fix it, but to no avail:
@.@.servername matches the %computername% on each machine.
I enabled DTC Network Access using dcomcnfg.exe (all four options now
enabled). I then restarted DTC and sqlserver.
I verified DTC runs as NetworkService.
I even used telnet to verify the two machines can connect to each other's
port 135.
** WHAT HAVE I MISSED ? **
How do I get distributed transactions to work on these servers?
--MikeHi
have you checked out:
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
John
"Mike" wrote:

> I'm having trouble doing distributed transactions in a stored procedure.
It
> boils down to this example.
> This works in Query Analyzer:
> --begin distributed transaction
> insert into [39.80.0.26\x3].[sm].dbo.events
> select * from events
> where time = @.time and seq = @.seq
> --commit
> But it does not work in Query Analyzer when I uncomment the distributed
> xaction and commit.
> I get this error:
> Server: Msg 7391, Level 16, State 1, Line 12
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I'm using sqlserver 8.0.818 on Win2003 server on both sides.
> I tried several things to fix it, but to no avail:
> @.@.servername matches the %computername% on each machine.
> I enabled DTC Network Access using dcomcnfg.exe (all four options now
> enabled). I then restarted DTC and sqlserver.
> I verified DTC runs as NetworkService.
> I even used telnet to verify the two machines can connect to each other's
> port 135.
> ** WHAT HAVE I MISSED ? **
> How do I get distributed transactions to work on these servers?
> --Mike

Msg 512 but no duplicates!?!

I am attempting to execute the Stored Procedure at the foot of this
message. The Stored Procedure runs correctly about 1550 times, but
receive the following error three times:

Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,
Line 68
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

I've done some digging, and the error message is moderately
self-explanatory.

The problem is that there is no Line 68 in the Stored Procedure. It's
the comment line:

-- Need to find out how many hours the employee is scheduled etc.

Also, there are no duplicate records in the Employee table nor the
WeeklyProfile table. At least I assume so - if the following SQL to
detect duplicates is correct!

SELECT E.*
FROM
Employee E
join
(select EmployeeID
from
Employee
Group by EmployeeID
having count(*) > 1) as E2
On
(E.EmployeeID = E2.EmployeeID)

SELECT
W.*
FROM
WeekProfile W
join
(Select
WeekProfileID
FROM
WeekProfile
GROUP BY
EmployeeID, MondayHours, WeekProfileID
HAVING COUNT(*) > 1) AS W2
ON
W.WeekProfileID = W2.WeekProfileID

NOTE: In the second statement, I have tried for MondayHours thru
FridayHours.

Anyone got any ideas? The TableDefs are set up in this thread:

<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3
The Stored Procedure that causes the error is here:

--************************************************** ***********

CREATE PROCEDURE BackFillNetworkHours

AS

DECLARE @.EmployeeID int
DECLARE @.TimesheetDate DateTime
DECLARE @.NumMinutes int
DECLARE @.NetworkCode int

-- Get the WorkID corresponding to Project Code 2002
SELECT
@.NetworkCode = WorkID
FROM
[Work]
WHERE
(WorkCode = '2002')

-- Open a cursor on a SELECT for all Network Support Employees where
any single workday comprises fewer than 7.5 hours
DECLARE TooFewHours CURSOR FOR

SELECT
EmployeeID,
CONVERT(CHAR(8), Start, 112) AS TimesheetDate,
SUM(NumMins) AS TotalMins
FROM
(SELECT
TI.EmployeeID,
W.WorkCode,
TI.Start AS Start,
SUM(TI.DurationMins) AS NumMins
FROM
TimesheetItem TI LEFT JOIN
[Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS
(SELECT
*
FROM
Employee E
WHERE
((TI.EmployeeID = E.EmployeeID) AND
(E.DepartmentID = 2)))
GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS x
GROUP BY
EmployeeID,
CONVERT(char(8), Start, 112)
HAVING
SUM(NumMins) < 450
ORDER BY
EmployeeID,
CONVERT(CHAR(8), Start, 112)

-- Get the EmployeeID, Date and Number of Minutes from the cursor
OPEN TooFewHours
FETCH NEXT FROM TooFewHours INTO @.EmployeeID, @.TimesheetDate,
@.NumMinutes
WHILE (@.@.FETCH_STATUS=0)
BEGIN

DECLARE @.NewWorkTime datetime
DECLARE @.TimesheetString varchar(50)
DECLARE @.Duration int
DECLARE @.RequiredDuration int

-- Set the correct date to 08:30 - by default the cast from the
cursor's select statement is midday
SET @.TimesheetString = @.TimesheetDate + ' 08:30'
SET @.NewWorkTime = CAST(@.TimesheetString AS Datetime)

-- Need to find out how many hours the employee is scheduled to work
that day.
SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
WHEN 1 THEN
(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 2 THEN
(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 3 THEN
(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 4 THEN
(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 5 THEN
(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 6 THEN
(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 7 THEN
(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
END

IF @.NumMinutes < @.RequiredDuration
BEGIN

-- Set the Start for the dummy work block to 08:30 + the number of
minutes the employee has already worked that day
SET @.NewWorkTime = DateAdd(minute, @.NumMinutes, @.NewWorkTime)

-- Set the duration for the dummy work block to be required duration
less the amount they've already worked
SET @.Duration = @.RequiredDuration - @.NumMinutes

-- Now we have the correct data - insert into table.
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(@.EmployeeID,
@.NewWorkTime,
@.Duration,
@.NetworkCode)
END

FETCH NEXT FROM TooFewHours INTO @.EmployeeID, @.TimesheetDate,
@.NumMinutes
END

CLOSE TooFewHours
DEALLOCATE TooFewHours

GO

--************************************************** ***********

Thanks

EdwardLine 68 refers to the 68th line of text in a batch after a GO command;
this may cause you to miss where the failing subquery is. Obviously
it's the section where you set @.RequiredDuration; you mentioned that
you tested Monday-Friday, what about Saturday and Sunday?

Stu|||On 28 Jul 2005 07:03:11 -0700, Will wrote:

>I am attempting to execute the Stored Procedure at the foot of this
>message. The Stored Procedure runs correctly about 1550 times, but
>receive the following error three times:
>Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,
>Line 68
>Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as
>an expression.
(snip)

Hi Will/Edward,

Before I get to some other issues, let's first tackle this error. I
can't trace back where your line 68 is due to line breaks inserted by
either your Usenet posting software or my reader, but I'm willing to bet
that it is one of the seven subqueries in this part:

>SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
>WHEN 1 THEN
>(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
>(EmployeeID = @.EmployeeID))
(snippety)
>WHEN 7 THEN
>(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
>(EmployeeID = @.EmployeeID))

Checking the thread you refered to for the table definitions, I see that
WeekProfile has a compound PRIMARY KEY constraint on EmployeeID plus
PeriodStart. The subselects above are only guaranteed to bring back one
value if the PK is on EmployeeID only. Since changes to the work profile
of employees tend to be rare, the subquery would still return 1 row for
most of your employees, but apparently, 3 out of your 1550 employees now
have a new WeekProfile and your subquery brings up both. Run this to
find the offenders:

SELECT EmployeeID, COUNT(*)
FROM WeekProfile
GROUP BY EmployeeID
HAVING COUNT(*) > 1

The thread you refered to was an interesting read in itself. This was
the first time I saw it, since I was on holiday when you first posted
it, and I decided to skip most unread messages when I came back from the
holiday and found well over a thousand new messages in the groups I
frequent.

Anyway, several things bother me. You asked for a way to avoid the
cursor in this code, Erland supplied you with one - and now, you are
busy solving bugs in the same cursor-based code you said you wanted to
replace. Why didn't you implement Erland's suggestion?

Also, Joe Celko has already given you his usual treatment so I won't
comment on the database design too much (and yes, I did read that you
are only the poor guy who inherited this DB). But I do agree with Joe:
the design has much room for improvement.

Finally:
>-- Set the correct date to 08:30 - by default the cast from the
>cursor's select statement is midday
>SET @.TimesheetString = @.TimesheetDate + ' 08:30'

Not midday, but mignight. I know it's "only" the comment, but small
errors in comments will waste many hours of your successor's time.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||
Hugo Kornelis wrote:

> (snip)
> Hi Will/Edward,
> Before I get to some other issues, let's first tackle this error. I
> can't trace back where your line 68 is due to line breaks inserted by
> either your Usenet posting software or my reader, but I'm willing to bet
> that it is one of the seven subqueries in this part:
> >SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
> >WHEN 1 THEN
> >(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
> >(EmployeeID = @.EmployeeID))
> (snippety)
> >WHEN 7 THEN
> >(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
> >(EmployeeID = @.EmployeeID))
> Checking the thread you refered to for the table definitions, I see that
> WeekProfile has a compound PRIMARY KEY constraint on EmployeeID plus
> PeriodStart. The subselects above are only guaranteed to bring back one
> value if the PK is on EmployeeID only. Since changes to the work profile
> of employees tend to be rare, the subquery would still return 1 row for
> most of your employees, but apparently, 3 out of your 1550 employees now
> have a new WeekProfile and your subquery brings up both. Run this to
> find the offenders:
> SELECT EmployeeID, COUNT(*)
> FROM WeekProfile
> GROUP BY EmployeeID
> HAVING COUNT(*) > 1

You're right - there was an offender. Thank you.

> Anyway, several things bother me. You asked for a way to avoid the
> cursor in this code, Erland supplied you with one - and now, you are
> busy solving bugs in the same cursor-based code you said you wanted to
> replace. Why didn't you implement Erland's suggestion?

Well, the problem was (and is) that in order to have any hope of
getting an answer here, I felt it necessary to simplify matters
greatly. When it came to solving the actual problem, I couldn't work
out a way to do it with "raw" SQL. Plus, this is a one-time only data
cleansing exercise, so it can run in a batch over the weekend and
performance isn't an issue. I asked the question because I was
interested in an abstract way whether the problem could be solved.

> Also, Joe Celko has already given you his usual treatment so I won't
> comment on the database design too much (and yes, I did read that you
> are only the poor guy who inherited this DB). But I do agree with Joe:
> the design has much room for improvement.

What he probably doesn't appreciate (and why should he - I didn't tell
him!) is that this is purely an internal system, that is modelled on at
least two existing systems from which data must be extracted. Sure
there are kludges, but it does work (it's in Beta test at the moment
and seems remarkably stable and reliable, pace the legacy data which I
am in the process of cleaning up.)

> Finally:
> >-- Set the correct date to 08:30 - by default the cast from the
> >cursor's select statement is midday
> >SET @.TimesheetString = @.TimesheetDate + ' 08:30'
> Not midday, but mignight. I know it's "only" the comment, but small
> errors in comments will waste many hours of your successor's time.

I couldn't agree more - the comments have been changed. However, vide
supra, this is a one-time, throw away operation and the comments are
more for the benefit of the group than any later developer.

But many thanks to you and the others for your kind and helpful
suggestions.

Edward

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3910 Transaction context in use by another session.

We are testing SQL 2005 SP1 in a multi-server environment. Stored Procedure 'A' is executed on server.database 'A'. It calls SP 'B' on server.database 'B'. SP 'B' in turn calls SP 'C' which is back on server.database 'A'. So in essence A calls an SP on B, which calls an SP back on A. Linked servers are present on both servers to enable these cross-server calls. This scenario works in our SQL 2000 environment.

In the SQL 2005 environment, we get the Msg 3910 message stated in the subject. Is this truly a 'loopback server' situation and if so, why does it work in SQL 2000? Did this functionality change in SQL 2005? In SQL 2005 SP1? This may seem to be a klugy way to do things but unfortunately, it is a core part of our application security system and won't be easily undone. Any ideas would be appreciated. Thanks.

The problem was not introduced in SQL 2005 SP1. The error still occurs when run using two servers with the base SQL 2005 install.|||

We have an application that uses link servers and is running into the same error.

Looks like an undocumented feature in SS2005 or a BUG.

Anyone have any ideas on how to get around it?

Eric

|||

We worked the issue through with Microsoft support and I've included their comments and resolution below. The problem with their answer is that if a consistent data view is their goal, then this should also fail when all the databases are on a single server. Perhaps they'll make this consistent in a future service pack. Anyway, such as it is, here's the answer.

Beginning of Microsoft response:

Yes, this is a known problem and we change the behavior for good reason in Yukon. We’d like you to work around this issue.

The reason we change to current SQL Server 2005 behavior is that it can guarantee consistent data view, while SQL Server 2000 behavior may expose inconsistent data if the loopback tries to access the table that is been changed.

other ways to avoid this error:

1. Change the application logic to not use loopback.

2. Separate the INSERT EXEC into two steps -- Run remote EXEC first(which will succeed for loopback) and put it into a remote table and then run a SELECT from the remote table

But in general, SQL Server doesn’t support loopback server, so you are still at risk that you might get impacted in future release. So the best way is to avoid using the loopback.

End of Microsoft response.

Hope this helps.

|||I was facing a similar problem wherein I was using loopback linked servers for synchronizing multiple databases on same server as well as remote servers. The code was generically handling this. Will have to look for alternate way as support for loopback server is not guaranteed.

Thanks

Chandrakant Karale.

Msg 3616 Transaction doomed, Triggers and Error logging

I am trying to use the uspLogError and uspPrintError stored procedures found in AdventureWorks and I have a problem. I have a stored procedure called Is_uspProcessLeads that processes some customer information that is in xml format. Part of the process is to check to make sure that the xml is well formed and if it is validated. Each step along the way, Is_uspProcessLeads updates a status field to indicate how far along in the process the xml file got, and if there is an error, to capture that error in a log file. The error handling process uses a try..catch logic.

Is_uspProcessLeads does it's processing against a table called [Is.Leads] which contains the following fields...
IsLead_Id Primary Key, int
IsLead_XmlRaw varchar(max)
IsLead_XmlWellFormed untyped Xml data type
IsLead_XmlValidated typed Xml data type with a related xsd file
IsLead_LeadStatus_Id Status code field

So here's the problem. If I run Is_uspProcessLeads manually and I get an error (like not well formed) then it blows up as I would expect and logs the error correctly. But what I want to do is run Is_uspProcessLeads from the Insert trigger of [Is.Leads]. When I try that, I get an unexpected error in uspLogError when it tries to insert error information in the Error log table, therefore preventing me from logging my errors.

Here is the errors I get
V
Error: 3930
Severity: 16
State: 1
Procedure: IsuspLogError
Line: 43
ProcessLocationMsg: Confirm that the raw xml is well formed
Lead_Id: 20
ERROR_MESSAGE...
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
^
Msg 3616, Level 16, State 1, Line 12
Transaction doomed in trigger. Batch has been aborted.

Got any Ideas why this is happening?

I searched for the 3616 error message, and got nothing (I do however like the message about being doomed). It looks like SQL Server is treating the trigger event as a transaction

FYI: The error message inside the dashed lines comes from uspPrintError, which is executed inside the catch block of uspLogError

This error occurs when you try to make any modifications after a transaction that needs to be rolled back. You can detect this using XACT_STATE function in the CATCH block. If the value is -1 it indicates that the transaction cannot be committed and you need to issue a rollback so subsequent transactions cannot proceed. If you can post a simple repro of the problem it will be easier to suggest the modification.|||

The stored procedured Is_uspLogError already checks XACT_STATE Function, see below. Below are the relative three stored procs.

1) Is_trgLead_InsertTrigger is the trigger,

2) Is_uspProcessLeads has the try catch block of code and

3) Is_uspLogError logs the error

ALTER TRIGGER [Is_trgLead_InsertTrigger]
ON [dbo].[Is.Lead]
AFTER INSERT --FOR INSERT ?
AS
BEGIN
SET NOCOUNT ON; -- prevent extra result sets from interfering with SELECT statements.

-- =============================================
-- Declaration
-- =============================================
DECLARE @.ReturnCode int
DECLARE @.Lead_Id int
DECLARE @.FTP_Id int
DECLARE @.ProcessLocationMsg varchar(255)

Set @.ProcessLocationMsg = 'Initialization'
If (select Skip_IsLead_Trigger from dbo.[Is.Environment]) = 1
Return

SELECT
@.Lead_Id = IsLead_Id,
@.FTP_Id = IsLead_FTP_Id
from Inserted

Set @.ProcessLocationMsg = 'Call sproc IsProcessLeads'
EXEC @.ReturnCode = [Is_uspProcessLeads]
@.IsLead_Id = @.Lead_Id,
@.IsFTP_Id = @.FTP_Id

END

ALTER PROC [dbo].[Is_uspProcessLeads]
@.IsLead_Id int,
@.IsFTP_Id int
AS
/*

USE [Integration]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[IsProcessLeads]
@.IsLead_Id = 1,
@.IsFTP_Id = 1

SELECT 'Return Value' = @.return_value
GO

select * from dbo.[Is.ErrorLog]
Go

*/
BEGIN

--Declarations.

DECLARE @.FTP_TYPE_ID_AHG int -- American Home Guides
DECLARE @.FTP_TYPE_ID_BHI int -- Builder Homesite Inc
DECLARE @.FTP_TYPE_ID_HB int -- Homebuilder
DECLARE @.FTP_TYPE_ID_INEST int -- iNest
DECLARE @.FTP_TYPE_ID_RYLAND int -- Ryland.Com

DECLARE @.LEAD_STATUS_ID_START int
DECLARE @.LEAD_STATUS_ID_Completed int
DECLARE @.LEAD_STATUS_ID_XmlWellFormed int
DECLARE @.LEAD_STATUS_ID_XmlValid int
--SET @.LEAD_STATUS_ID_RaisedError int
DECLARE @.LEAD_STATUS_ID_EmailedCLEAR int
DECLARE @.LEAD_STATUS_ID_EmailedCustomer int

Declare @.CLEAREmail nvarchar(255)
Declare @.CLEAREmailMsg nvarchar(255)

Declare @.AutoResponseEmail nvarchar(255)
Declare @.AutoResponseEmailMsg nvarchar(255)

DECLARE @.ReturnCode int
Declare @.ProcessLocationMsg nvarchar(255)

--Initialization.
SET @.FTP_TYPE_ID_AHG = 1
SET @.FTP_TYPE_ID_BHI = 4
SET @.FTP_TYPE_ID_HB = 6
SET @.FTP_TYPE_ID_INEST = 7
SET @.FTP_TYPE_ID_RYLAND = 0

SET @.LEAD_STATUS_ID_START = 1
SET @.LEAD_STATUS_ID_Completed = 2
SET @.LEAD_STATUS_ID_XmlWellFormed = 3
SET @.LEAD_STATUS_ID_XmlValid = 4
--SET @.LEAD_STATUS_ID_RaisedError
SET @.LEAD_STATUS_ID_EmailedCLEAR = 6
SET @.LEAD_STATUS_ID_EmailedCustomer = 7

-- Confirm that the raw xml is well formed and valid, by updating the specific xml field
-- which is determined by the FTP_TYPE_ID, with the Raw xml

BEGIN TRY

Set @.ProcessLocationMsg = 'Confirm that the raw xml is well formed'
UPDATE [Is.Lead]
SET IsLead_XmlWellFormed = IsLead_XmlRaw,
IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id

Set @.ProcessLocationMsg = 'Confirm that the well formed xml is valid'
IF @.IsFTP_Id = @.FTP_TYPE_ID_AHG
UPDATE [Is.Lead]
SET IsLead_XmlAHG = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_BHI
UPDATE [Is.Lead]
SET IsLead_XmlBHI = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_HB
UPDATE [Is.Lead]
SET IsLead_XmlHB = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id
Else IF @.IsFTP_Id = @.FTP_TYPE_ID_INest
UPDATE [Is.Lead]
SET IsLead_XmliNest = IsLead_XmlWellFormed
WHERE IsLead_Id = @.IsLead_Id

-- Update status to show the xml is valid
Set @.ProcessLocationMsg = 'Update status to show the xml is valid'
UPDATE [Is.Lead]
SET IsLead_LeadStatus_Id = @.LEAD_STATUS_ID_XmlValid
WHERE IsLead_Id = @.IsLead_Id

-- Update status to show the xml is valid
Set @.ProcessLocationMsg = 'Get the count of leads'
UPDATE [Is.Lead]
SET IsLead_CountOfLeads = ([dbo].[Is_ufnCountOfLeads](@.IsLead_Id, @.IsFTP_Id))
WHERE IsLead_Id = @.IsLead_Id

Proc_Exit:

END TRY
BEGIN CATCH
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @.IsLead_Id, @.ProcessLocationMsg;
END CATCH;

RETURN --@.@.ERROR

End

/*
IsuspLogError logs error information in the ErrorLog table about the
error that caused execution to jump to the CATCH block of a
TRY...CATCH construct. This should be executed from within the scope
of a CATCH block otherwise it will return without inserting error
information.
*/
ALTER PROCEDURE [dbo].[Is_uspLogError]
@.Lead_Id int,
@.ProcessLocationMsg varchar(255) = '',
@.ErrorLogID int = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by IsuspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;

-- Output parameter value of 0 indicates that error
-- information was not logged
SET @.ErrorLogID = 0;

BEGIN TRY
-- Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;

-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing IsuspLogError in order to successfully log error information.';
RETURN;
END

PRINT 'Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]'
INSERT [dbo].[Is.ErrorLog]
(
[IsErrorLog_UserName],
[IsErrorLog_Number],
[IsErrorLog_Severity],
[IsErrorLog_State],
[IsErrorLog_Procedure],
[IsErrorLog_Line],
[IsErrorLog_ProcessLocationMsg],
[IsErrorLog_Message],
[IsErrorLog_Lead_Id]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
@.ProcessLocationMsg,
ERROR_MESSAGE(),
@.Lead_Id);
-- Pass back the ErrorLogID of the row inserted
SET @.ErrorLogID = @.@.IDENTITY;
END TRY
BEGIN CATCH
PRINT 'Inside Isusp_LogError!Begin Catch ';
EXECUTE [dbo].[Is_uspPrintError] @.Lead_Id, @.ProcessLocationMsg;
RETURN -1;
END CATCH
END;

|||

FYI: I did some more testing to see what the value of XACT_STATE() is, and it doesn't change, it's always zero. Inside Is_uspLogError, I did a print before the Insert statement, and inside the Catch statement, and it was always 0.

Error inside Is_uspProcessLeads; calling Is_uspLogError

1 XACT_STATE(): 0

Inside Is_uspLogError, Before the insertion of [Is.ErrorLog]

2 XACT_STATE(): 0

Inside Isusp_LogError!Begin Catch

|||

Since you are calling the SP inside the trigger all errors needs to be rolled back before you performing any writes. So in the Is_uspProcessLeads CATCH block you are missing the XACT_STATE() check and rollback. See below lines:

BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK - add this line here:
PRINT 'Error inside Is_uspProcessLeads; calling Is_uspLogError'
EXECUTE Is_uspLogError @.IsLead_Id, @.ProcessLocationMsg;
END CATCH;

Or you may just need to rollback the transaction without checking for XACT_STATE().

|||

Thanks for the response,

The problem with your solution is that I don't want to roll back the data that gets inserted in table [Is.Lead]. I want it there, so I can report what went wrong. If do a rollback, I will have an entry in the [Is.ErrorLog] table but nothing in [Is.Lead].

I guess my design is flawed, do you have any suggestions?

I think I need to remove the insert trigger, and have Is_uspProcessLeads executed some other way. I suppose I could do this by putting Is_uspProcessLeads on the scheduler, or do something with Services Broker.

Thanks

|||The issue is that when errors happen inside trigger most of them are of the transaction abort nature and the only recourse is to rollback the transaction. Since there is no support for autonomous transactions in SQL Server there is really no way to do what you are trying. Best is to move this logic to a stored procedure so that in case of an error you can catch it appropriately and take action. Note that this still depends on the setting of XACT_ABORT.|||

Im not seeing it, I need help. ANY help would be greatly appreciated

How can I get the try block in a trigger to stop throwing errors that im trying to skip over?

here is my example:

IF (OBJECT_ID ('cwtest') IS NOT NULL) drop table cwtest
go
create table cwtest (test varchar(20))
go
IF (OBJECT_ID ('cwtest_tr_IUD') IS NOT NULL) DROP trigger cwtest_tr_IUD
go
create trigger cwtest_tr_IUD on cwtest
for insert, update, delete
as
DECLARE
@.dml char(1)
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH

IF XACT_STATE() = -1 ROLLBACK - add this line here:
END CATCH
select 'why is the query completed with errors? Is this a bug?,This works fine anywhere else!'
end
go

insert into cwtest values('dd')
go

-- here is the base test works wonderfully!
begin
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
IF XACT_STATE() = -1 ROLLBACK - add this line here:
END CATCH;
select 'error is not returned,query executed successfully, whats up? '
end
go

and the output :

--

(0 row(s) affected)

why is the query completed with errors?

(1 row(s) affected)

Msg 3616, Level 16, State 1, Line 2

Transaction doomed in trigger. Batch has been aborted.

--

(0 row(s) affected)

-

error is not returned,query executed successfully, whats up?

(1 row(s) affected)

MSDTC Unavailable Windows 2003

Hi
I have a VB6 windows app which calls a VB6 COM+ application (both running on
machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
machine CANSUR001 but it keeps failing with error "MSDTC on server
'CANSUR001' is unavailable". The COM+ application is configured with
Transaction Support=Required and isolation level set to the default of
serialised (the COM+
app changes it to Read Committed)
* CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
is running SQL Server 2000.
* MSDTC is started on both machines
* I have Installed/enabled windows components "Enable network COM+ access"
and "Enable network DTC access" on both machines
* I have configured MSDTC in Component services to use "Network DTC access",
allow outbound and inbound on "Transaction Manager Communication" and set
to "No authentication required"
* Both machines are in the same domain
* No local firewalls are installed
* I've rebooted both machines but same result
* CANSUR001 is NOT part of a cluster
* I've tried running
BEGIN distributed transaction
select * from cansur001.leisure.dbo.member
from SQL Analyser in CHOPCOM001 but get the same result
Any help would be gratefully received
God Bless
RonanMSDTC is turned off by default in Windows 2003. Have you enabled it in
Windows?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:F77ECB86-A5E1-42CC-9D02-51CEBFDED779@.microsoft.com...
> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
> on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC
> access",
> allow outbound and inbound on "Transaction Manager Communication" and
> set
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||Distributed Transaction Coordinator service is enabled and started on both
machines
--
Ronan
"Ronan" wrote:

> Hi
> I have a VB6 windows app which calls a VB6 COM+ application (both running
on
> machine CHOPGBCOM001) which in turn calls a stored procedure on a remote
> machine CANSUR001 but it keeps failing with error "MSDTC on server
> 'CANSUR001' is unavailable". The COM+ application is configured with
> Transaction Support=Required and isolation level set to the default of
> serialised (the COM+
> app changes it to Read Committed)
>
> * CHOPCOM001 and CANSUR001 both have Windows 2003 SP1 and CANSUR001
> is running SQL Server 2000.
> * MSDTC is started on both machines
> * I have Installed/enabled windows components "Enable network COM+ access"
> and "Enable network DTC access" on both machines
> * I have configured MSDTC in Component services to use "Network DTC access
",
> allow outbound and inbound on "Transaction Manager Communication" and s
et
> to "No authentication required"
> * Both machines are in the same domain
> * No local firewalls are installed
> * I've rebooted both machines but same result
> * CANSUR001 is NOT part of a cluster
> * I've tried running
> BEGIN distributed transaction
> select * from cansur001.leisure.dbo.member
> from SQL Analyser in CHOPCOM001 but get the same result
>
> Any help would be gratefully received
> God Bless
> Ronan|||One thing I forgot to mention is that the remote server CANSUR001 is a
Domain Contoller, anyone come across problems running distributed
transactions against
servers which are also doiman controllers?
Ronan
"Ronan" wrote:
> Distributed Transaction Coordinator service is enabled and started on both
> machines
> --
> Ronan
>
> "Ronan" wrote:
>

MSDTC unavailable / JoinTransaction returned 0x8004d01c

Hi! We have 2 database server. 1 at our company, while the other with a web
hosting company.
We wrote stored procedures to update data from tables in our company's
server to the web hosting company's server. Out of 13 of these stored
procedures, 5 failed, giving me the error message that looks like the one
below,
Server: Msg 8501, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
MSDTC on server 'ORION' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure sp_ExportCalendar, Line 9
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
Can anyone help me explain what the error mean? I can post stored procedure
if required. Thanks.Hi this is one of the stored procedures that doesn't work. tsp is my user
name, server name.
CREATE PROCEDURE sp_ExportSysConf AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS (SELECT * FROM [SysConf])
BEGIN
--Step1: Remove records that do not exist in CIM
DELETE FROM [tsp].[tsp].[tsp].[SysConf]
FROM [tsp].[tsp].[tsp].[SysConf] as C2
WHERE NOT EXISTS (SELECT [C1].[ConfName], [C1].[ConfValue],
[C2].[LastUpdate] FROM [SMSBASE].[DBO].[SysConf] as [C1] where
[C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] = [C2].[ConfValue])
IF (@.@.error = 0)
BEGIN
-- no error --> Step 2: Insert new records from CIM
INSERT [tsp].[tsp].[tsp].[SysConf]([ConfName], [ConfValue])
SELECT [ConfName],[ConfValue]
FROM [SMSBASE].[DBO].[SysConf] as C2
WHERE NOT EXISTS (SELECT * FROM [tsp].[tsp].[tsp].[SysConf] as [C1]
where [C1].[ConfName] = [C2].[ConfName] and [C1].[ConfValue] =
[C2].[ConfValue])
IF (@.@.error = 0)
-- no error -> commit
COMMIT TRANSACTION
ELSE
-- error occurred -> rollback
ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
END
ELSE ROLLBACK TRANSACTION
GO

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I woul
d
>be interested in an explanation.
>"TomPearson" wrote:
>

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
Tom
Have just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:

> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom
|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:

MSDTC on server 'SERVER_NAME' is unavailable

Hi
I have two stored procedures that use a linked server to access an Access
database from SQL Server 2000.
The first takes no input:
CREATE PROCEDURE GetModules
AS
INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
OPENQUERY(ProjAForcBCase0,'select * from datCase')
GO
This procedure runs fine and does exactly as expected.
The second parameterises the linked server name (the idea being that these
will be created dynamically in code) so that it can be passed in to the
stored procedure.
CREATE PROCEDURE GetModulesDynamic
@.LinkedServer nvarchar(4000)
AS
DECLARE @.TSQL nvarchar(4000)
DECLARE @.OPENQUERY nvarchar(4000)
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'select * from datCase'')'
Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
EXEC(@.OPENQUERY+@.TSQL)
GO
However if I run this second stored procedure I am met with the error
message "MSDTC on server 'SERVER_NAME' is unavailable".
Both these stored procedures were executed on my PC (win 2K) using a local
SQL Server 2000 instance and a localo access database. As such I do not
understand why I should require MSDTC for the second procedure and not for
the first.
Any explanation would be extremely useful.
Also can MSDTC be installed on Win 2K and if so where can I get it from?
Thanks
TomHave just dicovered that I didn't have DTC running (had been looking for a
service called MSDTC as defined in the error message)
As such this now works as expected for both procedures, but if anyone knows
why it worked without DTC for the first procedure and not the second I would
be interested in an explanation.
"TomPearson" wrote:
> Hi
> I have two stored procedures that use a linked server to access an Access
> database from SQL Server 2000.
> The first takes no input:
> CREATE PROCEDURE GetModules
> AS
> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
> OPENQUERY(ProjAForcBCase0,'select * from datCase')
> GO
> This procedure runs fine and does exactly as expected.
> The second parameterises the linked server name (the idea being that these
> will be created dynamically in code) so that it can be passed in to the
> stored procedure.
> CREATE PROCEDURE GetModulesDynamic
> @.LinkedServer nvarchar(4000)
> AS
> DECLARE @.TSQL nvarchar(4000)
> DECLARE @.OPENQUERY nvarchar(4000)
> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
> SET @.TSQL = 'select * from datCase'')'
> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
> EXEC(@.OPENQUERY+@.TSQL)
> GO
> However if I run this second stored procedure I am met with the error
> message "MSDTC on server 'SERVER_NAME' is unavailable".
> Both these stored procedures were executed on my PC (win 2K) using a local
> SQL Server 2000 instance and a localo access database. As such I do not
> understand why I should require MSDTC for the second procedure and not for
> the first.
> Any explanation would be extremely useful.
> Also can MSDTC be installed on Win 2K and if so where can I get it from?
> Thanks
> Tom|||Most likely because in the second procedure, you used an
insert exec which would promote the transaction to a
distributed transaction. The first procedure used an insert
select so was a local transaction.
-Sue
On Mon, 31 Jan 2005 07:45:03 -0800, "TomPearson"
<TomPearson@.discussions.microsoft.com> wrote:
>Have just dicovered that I didn't have DTC running (had been looking for a
>service called MSDTC as defined in the error message)
>As such this now works as expected for both procedures, but if anyone knows
>why it worked without DTC for the first procedure and not the second I would
>be interested in an explanation.
>"TomPearson" wrote:
>> Hi
>> I have two stored procedures that use a linked server to access an Access
>> database from SQL Server 2000.
>> The first takes no input:
>> CREATE PROCEDURE GetModules
>> AS
>> INSERT INTO datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime) SELECT * FROM
>> OPENQUERY(ProjAForcBCase0,'select * from datCase')
>> GO
>> This procedure runs fine and does exactly as expected.
>> The second parameterises the linked server name (the idea being that these
>> will be created dynamically in code) so that it can be passed in to the
>> stored procedure.
>> CREATE PROCEDURE GetModulesDynamic
>> @.LinkedServer nvarchar(4000)
>> AS
>> DECLARE @.TSQL nvarchar(4000)
>> DECLARE @.OPENQUERY nvarchar(4000)
>> SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
>> SET @.TSQL = 'select * from datCase'')'
>> Insert into datCase( CaseNumber, DateCreated, DateLastRun, VisoObject,
>> UserVBA, VisoDiagram, CF, CaseDescription, LastRunTime)
>> EXEC(@.OPENQUERY+@.TSQL)
>> GO
>> However if I run this second stored procedure I am met with the error
>> message "MSDTC on server 'SERVER_NAME' is unavailable".
>> Both these stored procedures were executed on my PC (win 2K) using a local
>> SQL Server 2000 instance and a localo access database. As such I do not
>> understand why I should require MSDTC for the second procedure and not for
>> the first.
>> Any explanation would be extremely useful.
>> Also can MSDTC be installed on Win 2K and if so where can I get it from?
>> Thanks
>> Tom

MSDTC not available

It is possible to copy the data from table to Excel file with
OpenRowset command
When I tried to copy the result set of stored procedure to Excel file
by using this query
Insert into
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\v1.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Exec mysp 'test','testing'
I got the error
Server: Msg 8501, Level 16, State 3, Line 3
MSDTC on server 'SYS7' is unavailable.
What am I missing?
MadhivananFirst, I would check that the Distributed Transaction Controller is on. The
service can be disabled and is, by default, in certain scenarios. I will see
if I can find anything else to help in this situation.
In response, give an idea of the setup you are running under, as that could
affect the answer. Any additional information on what you are doing would
also be useful.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Madhivanan" wrote:

> It is possible to copy the data from table to Excel file with
> OpenRowset command
> When I tried to copy the result set of stored procedure to Excel file
> by using this query
> Insert into
> OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=F:\v1.xls;HDR=YES',
> 'SELECT * FROM [Sheet1$]')
> Exec mysp 'test','testing'
> I got the error
> Server: Msg 8501, Level 16, State 3, Line 3
> MSDTC on server 'SYS7' is unavailable.
> What am I missing?
>
> Madhivanan
>|||I just want to copy the result set of sp to Excel
I tried it with table and it is working perfectly
I am using SQL Server2000 with Service Pack 4 and Excel version is 2000
Madhivanan|||Still I didnot solve this problem
Any other ideas?
Madhivanan