DMO reports product level as sp3 instead of sp3a

Hi,
1. I am using the "ProductLevel" property of the "SQLServer2" SQL DMO object
to determine the product level of my SQL server 2000 installation. However
even when I have installed "SQL Server 2000 sp3a" on my machine, this
property still reports the server product level as sp3 (instead of sp3a). Is
there a workaround for this using which I can get the exact product level?
2. I am using the "xp_msver" sproc to get the SQL server version. The SQL
server 2000 installation with sp3 installed has version no= 8.0.760. This
version is reported by xp_msver. However when "sp3a" is installed on this,
the version is still reported as "8.0.760" when it should be "8.0.761". Is
this a bug in the execution of xp_msver?
Plz reply ASAP.
ThanxOn Mon, 14 Mar 2005 12:42:16 +0530, Onkar Walavalkar wrote:
(snip)
Hi Onkar,
As far as I know, SP3a and SP3 are the same WRT the server part; the
only differences are in the client tools. That's why the server will
never report SP3a, and that's why both SP3 and SP3a correspond to
version 8.00.760.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."

I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.

SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))

END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'zod91@.yahoo.com (zod91@.yahoo.com) writes:
> I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

I guess that you get the error, because SQL Server computes both SUMs
when traversing the rows. If it were to do the statement literaly,
it would first have to traverse the rows to compute the first sum,
and if that sum is not 0, traverse once more. So you would need:

SELECT CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)) = 0 THEN 1
ELSE SUM(REV_ORDER_QTY /
CASE WHEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14) <> 0
THEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)
ELSE 1 -- or whatever
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

This still looks funny to me. As soon as any of the columns is 0 for a
row, the outcome is always 1. Then again, I don't anything about the
business, so maybe this is OK.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Distributed Transaction Persistent Error

When I try to make a distributed transaction, I always get this error:

Server: Msg 7391, Level 16, State 1, Line 14
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].

That is although, I am sure that both servers have active DTC.

Have you verified from Control Panel->Component Services->Computers->My Computer->Properties->Security Configuration that Network DTC access is allowed and other settings on the same tab?

|||

No, I didn't verify that.

Now I checked the Network DTC Access check box in the place you mentioned, but still the same error persistent.

What are the other settings in the same tab that I should check?

Thanks

|||

You may want to check the following to trouble shoot the Distributed transaction errors:

1. Verify there are no MS DTC firewall issues. Please see link: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843 on how to trouble shoot them.
2. In case the two computers are running in domains or workgroups that do not trust each other, the MSDTC may fail to mutually authenticate. Please see link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/dc45d41e-58f7-412f-834d-fec72e00fc01.asp for more details.

Thanks

Suroor

|||

The matter is very sophisticated.

Isn't there an easier way than that whole sophistication?

What are the settings to allow under MSDTC "Allow Network Access"?

Thanks

|||

http://support.microsoft.com/kb/827805#kb3

http://support.microsoft.com/kb/329332/

HTH

distributed transaction failure

Im attempting to insert the result set from a remote query into a local table and Im getting the following error:

Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'REMOTESVR' is unavailable.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
Msg 7391, Level 16, State 1, Line 1

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

MSDTC is running on both servers. Has anyone ever seen this or have any insight into the cause?


The code that Im trying to run is:

create table msver
(
[Index] int,
[Name] varchar(30),
Internal_Value varchar(20),
Character_Value varchar(512)
)

insert into msver
exec ('exec [REMOTESVR].master.dbo.xp_msver')

Note that the remote query works fine.

Thanks!
EricMake sure MSDTC is started on both the servers.|||I might be wrong, but I don't think you're going to get xp_msver to work this way. Run it as:

insert msver
exec [REMOTESVR].sp_executesql 'exec master.dbo.xp_msver'

See if that works. I don't have my laptop hooked up, or I would test it really quick at the lab. :)|||Check on the 'remoteserver' SQL server error log for #
"... server Attempting to initialize Distributed Transaction Coordinator..."