msrepl_transactions

Hassan,
if you have anonymous subscribers or if not all of your
subscribers have synchronized, the commands will stay
there until the cleanup agent runs and the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have SQL subscribers .. How do I know if they are setup as anonymous ?
Also all my subscribers are uptodate and why do these tables have
commands/transactions in them that are 2 days old . Its definitely cleaning
up cos today when i looked, the min entry_time has increased since yesterday
but still around 52 hrs behind. This behaviour is only on one distributor..
The other is fine and deletes data that has been subscribed
Please advice..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:170101c4e8d4$919bbcb0$a501280a@.phx.gbl...
> Hassan,
> if you have anonymous subscribers or if not all of your
> subscribers have synchronized, the commands will stay
> there until the cleanup agent runs and the retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||sp_helpsubscription will return the subscription type to
determine if it is anonymous.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

msmdredir.ini can't open of not a disk file.

Hi all,

I am having one problem about the processing cube in SQL 2005 AS.
when cube runs i get this error in log file:

File system error: Error opening file; \Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini is not a disk file or file is not accessible.

How can i solve this!!

thanks

Does any one have experienced it before with SSAS! how can i resolve lock issues on this file!!

thanks

|||

This file contains the port numbers used by the SSAS instances on the server. I've never heard of a "locking" issue with it so I'll make a couple general suggestions.

First, try cycling (bouncing) the server to see if that clears the issue. If that doesn't work, try temporarily disabling the SQL Browser service. If this second action clears the problem, I wouldn't recommend leaving that service disabled, but at least you'll have a starting point for tech support.

Bryan Smith

|||

Yes, it's real... I've logged it with PSS and have had it now confirmed as a bug introduced in SP2.

While it revolves around named instances, scheduling the processing to happen at different times per instance may not work for you.

From the testing I did prior to logging the call, I found two issues that were introduced with SP2. Together they make the issue very very painful.

1) When you process a database when SP2 is installed, the AS Instance requires Read/write access to the msdmredir.ini file. Prior to SP2 it didn't. I could even make the file read-only, and the processing would take place successfully.

2) the ini file gets updated approximately every 30 sec PER AS INSTANCE INSTALLED. You can see this by checking the timestamp of the file. So if there are two named instances running, it will update 4 times a min (but not every 15 sec, as the instances have different start times etc). Stopping one or more of the instances will reduce the frequency of the update.

Note that the behaviour described in point 2 above did not start straight away, but only after the first time I tried to synchronise a database from one instance to the next. My thought was that I could do all the processing in one instance, then sync it out to the other instances, and avoid the locking issue. But once the behaviour starts, it doesn't go away thereafter.

So, it means that even if you're only processing from one instance, this issue may get you!! If you're lucky and your ini file isn't continuously updating, scheduling each instance to a different time may work.

Regards,

Andrew

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 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

MSDTC Security

What are the correct Active Directory priviledges that should be applied to
the service account that runs the MSDTC?
Thanks,NT AUTHORITY\NetworkService
is the correct startup account.
There are no specific Active Directory settings needed.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

MSDTC - The Transaction Manager is not available

I am trying to do something simple to test TransactionOption. I have a ScriptTask (out of the box), which runs fine. To test TransactionOption, I set the Package Properties TransactionOption = Required. Straight away the package fails with the following message

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

I am running this on my Laoptop, which has XP Pro with SP2.

I checked the services and MSDTC and it is running fine. I have checked the Event Viewer and saw the following messages... One is Information and the other is Error

Information (Disk - Event ID 2444)
-
MSDTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 0,
Network Clients = 0,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0

Error (Devices - Event ID 4437)
The account that the MS DTC service is running under is invalid. This can happen if the service account information has been changed using the Services snap-in in Microsoft Management Console (MMC). MS DTC service will continue to start. Please make sure that the MS DTC service account information is updated using the Component Services Explorer.

I have checked Component Services. All looks fine from what I can see.

Anyone else came across this?

Thanks

Sutha

Sutha,

Try this: http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2339.aspx

-Jamie

|||

Jamie

Thanks. I have read most of the blogs and tried all options suggested.

I dont have Windows Firewall Turned on. Its always off. I have got Norton on. I have disabled Norton and tried it as well. No luck.

Thanks

Sutha

|||

have you tried the tools that I suggested at the bottom of the blog post?

-Jamie

|||

No Jamie, I didn't try those tools, but I have downloaded them. Before that I uninstalled Norton from my laptop and restarted the machine, all worked fine. So in your case it was Windows Firewall, in my case Norton. What a pain, atleast I have identified the problem.

Thanks Jamie

Sutha

|||

Well that makes sense. Norton has a firewall as well doesn't it?

Have you tried opening up the DTC port (can't remember which one it is) on the firewall rather than just disabling it completely?

-Jamie

|||

Jamie

I disabled the Firewall within Norton and tried still didnt work, but didnt try the port. When I uninstall fully it worked. I think it is 185 ( I could be wrong), will try next week.

Thanks

Sutha