mssql 2000 v/s raid 5ee

Hallo all
I know mssql2000 v/s raid 5 is dont work (very slowly). Who knows if i
install mssql 2000 on raid 5ee (win 2003server standard) i will have
better performance or not ? I know the best performance is no raid 10
but i dont have.
Hi,
The performance depends up on the volume of data and amount of access. Take
a look into the below URL .
http://www-03.ibm.com/support/techdo...Index/WP100350
Thanks
Hari
<ebolasty@.op.pl> wrote in message
news:1160926718.834857.267660@.k70g2000cwa.googlegr oups.com...
> Hallo all
> I know mssql2000 v/s raid 5 is dont work (very slowly). Who knows if i
> install mssql 2000 on raid 5ee (win 2003server standard) i will have
> better performance or not ? I know the best performance is no raid 10
> but i dont have.
>
sql

mssql 2000 v/s raid 5ee

Hallo all
I know mssql2000 v/s raid 5 is dont work (very slowly). Who knows if i
install mssql 2000 on raid 5ee (win 2003server standard) i will have
better performance or not ? I know the best performance is no raid 10
but i dont have.Hi,
The performance depends up on the volume of data and amount of access. Take
a look into the below URL .
http://www-03.ibm.com/support/techd...bIndex/WP100350
Thanks
Hari
<ebolasty@.op.pl> wrote in message
news:1160926718.834857.267660@.k70g2000cwa.googlegroups.com...
> Hallo all
> I know mssql2000 v/s raid 5 is dont work (very slowly). Who knows if i
> install mssql 2000 on raid 5ee (win 2003server standard) i will have
> better performance or not ? I know the best performance is no raid 10
> but i dont have.
>

mssql 2000 v/s raid 5ee

Hallo all
I know mssql2000 v/s raid 5 is dont work (very slowly). Who knows if i
install mssql 2000 on raid 5ee (win 2003server standard) i will have
better performance or not ? I know the best performance is no raid 10
but i dont have.Hi,
The performance depends up on the volume of data and amount of access. Take
a look into the below URL .
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100350
Thanks
Hari
<ebolasty@.op.pl> wrote in message
news:1160926718.834857.267660@.k70g2000cwa.googlegroups.com...
> Hallo all
> I know mssql2000 v/s raid 5 is dont work (very slowly). Who knows if i
> install mssql 2000 on raid 5ee (win 2003server standard) i will have
> better performance or not ? I know the best performance is no raid 10
> but i dont have.
>

MSSQL 2000 Standard Edition not utilizing Maximum Memory!

Hi All
Can anybody tell me why Memory is not utilized by MSSQL Service. The Environment as follows..
It is an Critical Web based Application with SQL 2000 Standard Edition + SP3 as backend
OS: Win 2000 Advanced Server + 3GB Memory + 2 CP
MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2 GB". This Server has Replication
Configuration also. During peak time or non-peak time, it always use 1 GB Memory only. Why MSSQL is not taking more than 1 GB, even though server has got 3 GB and database's max. memory configured as 2 GB
In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we will give /3GB in boot.ini. Similarly anything should be given for SQL 2000 Standard Edition. Right now, boot.ini doesn't contain this option
Appreciate your help on this
tks in advance
vasuAre you running other applications on the server besides SQL Server? SQL
Server will manage memory based on amount of free memory available.
Consequently, if other apps running on the same box (e.g. IIS) consume
significant amounts of memory, SQL Server won't acquire additional memory
because this would negatively impact performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:E2239F3D-53B5-4DA7-B7AC-280DC8B54ED7@.microsoft.com...
> Hi All,
> Can anybody tell me why Memory is not utilized by MSSQL Service. The
Environment as follows...
> It is an Critical Web based Application with SQL 2000 Standard Edition +
SP3 as backend .
> OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
> MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2
GB". This Server has Replication
> Configuration also. During peak time or non-peak time, it always use 1 GB
Memory only. Why MSSQL is not taking more than 1 GB, even though server has
got 3 GB and database's max. memory configured as 2 GB.
> In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we
will give /3GB in boot.ini. Similarly anything should be given for SQL 2000
Standard Edition. Right now, boot.ini doesn't contain this option.
> Appreciate your help on this.
> tks in advance,
> vasum
>|||Verify if your application is I/O bound or Memory Bound '
If your cpu utilization is fixed at 100%, then you should worry about
Memory.
Most of the performance problems in databases usually come from locks,
blocking, lot of IO read/writes, etc.
Check http://www.sql-server-performance.com/ for various ways to measure SQL
Server Performance.
Amol.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:E2239F3D-53B5-4DA7-B7AC-280DC8B54ED7@.microsoft.com...
> Hi All,
> Can anybody tell me why Memory is not utilized by MSSQL Service. The
Environment as follows...
> It is an Critical Web based Application with SQL 2000 Standard Edition +
SP3 as backend .
> OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
> MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2
GB". This Server has Replication
> Configuration also. During peak time or non-peak time, it always use 1 GB
Memory only. Why MSSQL is not taking more than 1 GB, even though server has
got 3 GB and database's max. memory configured as 2 GB.
> In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we
will give /3GB in boot.ini. Similarly anything should be given for SQL 2000
Standard Edition. Right now, boot.ini doesn't contain this option.
> Appreciate your help on this.
> tks in advance,
> vasum
>

MSSQL 2000 Standard Edition not utilizing Maximum Memory!

Hi All,
Can anybody tell me why Memory is not utilized by MSSQL Service. The Environment as follows...
It is an Critical Web based Application with SQL 2000 Standard Edition + SP3 as backend .
OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2 GB". This Server has Replication
Configuration also. During peak time or non-peak time, it always use 1 GB Memory only. Why MSSQL is not taking more than 1 GB, even though server has got 3 GB and database's max. memory configured as 2 GB.
In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we will give /3GB in boot.ini. Similarly anything should be given for SQL 2000 Standard Edition. Right now, boot.ini doesn't contain this option.
Appreciate your help on this.
tks in advance,
vasum
Are you running other applications on the server besides SQL Server? SQL
Server will manage memory based on amount of free memory available.
Consequently, if other apps running on the same box (e.g. IIS) consume
significant amounts of memory, SQL Server won't acquire additional memory
because this would negatively impact performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:E2239F3D-53B5-4DA7-B7AC-280DC8B54ED7@.microsoft.com...
> Hi All,
> Can anybody tell me why Memory is not utilized by MSSQL Service. The
Environment as follows...
> It is an Critical Web based Application with SQL 2000 Standard Edition +
SP3 as backend .
> OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
> MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2
GB". This Server has Replication
> Configuration also. During peak time or non-peak time, it always use 1 GB
Memory only. Why MSSQL is not taking more than 1 GB, even though server has
got 3 GB and database's max. memory configured as 2 GB.
> In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we
will give /3GB in boot.ini. Similarly anything should be given for SQL 2000
Standard Edition. Right now, boot.ini doesn't contain this option.
> Appreciate your help on this.
> tks in advance,
> vasum
>
|||Verify if your application is I/O bound or Memory Bound ?
If your cpu utilization is fixed at 100%, then you should worry about
Memory.
Most of the performance problems in databases usually come from locks,
blocking, lot of IO read/writes, etc.
Check http://www.sql-server-performance.com/ for various ways to measure SQL
Server Performance.
Amol.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:E2239F3D-53B5-4DA7-B7AC-280DC8B54ED7@.microsoft.com...
> Hi All,
> Can anybody tell me why Memory is not utilized by MSSQL Service. The
Environment as follows...
> It is an Critical Web based Application with SQL 2000 Standard Edition +
SP3 as backend .
> OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
> MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2
GB". This Server has Replication
> Configuration also. During peak time or non-peak time, it always use 1 GB
Memory only. Why MSSQL is not taking more than 1 GB, even though server has
got 3 GB and database's max. memory configured as 2 GB.
> In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we
will give /3GB in boot.ini. Similarly anything should be given for SQL 2000
Standard Edition. Right now, boot.ini doesn't contain this option.
> Appreciate your help on this.
> tks in advance,
> vasum
>

MSSQL 2000 Standard Edition not utilizing Maximum Memory!

Hi All,
Can anybody tell me why Memory is not utilized by MSSQL Service. The Environ
ment as follows...
It is an Critical Web based Application with SQL 2000 Standard Edition + SP3
as backend .
OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2 GB
". This Server has Replication
Configuration also. During peak time or non-peak time, it always use 1 GB Me
mory only. Why MSSQL is not taking more than 1 GB, even though server has go
t 3 GB and database's max. memory configured as 2 GB.
In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we w
ill give /3GB in boot.ini. Similarly anything should be given for SQL 2000 S
tandard Edition. Right now, boot.ini doesn't contain this option.
Appreciate your help on this.
tks in advance,
vasumAre you running other applications on the server besides SQL Server? SQL
Server will manage memory based on amount of free memory available.
Consequently, if other apps running on the same box (e.g. IIS) consume
significant amounts of memory, SQL Server won't acquire additional memory
because this would negatively impact performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:E2239F3D-53B5-4DA7-B7AC-280DC8B54ED7@.microsoft.com...
> Hi All,
> Can anybody tell me why Memory is not utilized by MSSQL Service. The
Environment as follows...
> It is an Critical Web based Application with SQL 2000 Standard Edition +
SP3 as backend .
> OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
> MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2
GB". This Server has Replication
> Configuration also. During peak time or non-peak time, it always use 1 GB
Memory only. Why MSSQL is not taking more than 1 GB, even though server has
got 3 GB and database's max. memory configured as 2 GB.
> In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we
will give /3GB in boot.ini. Similarly anything should be given for SQL 2000
Standard Edition. Right now, boot.ini doesn't contain this option.
> Appreciate your help on this.
> tks in advance,
> vasum
>|||Verify if your application is I/O bound or Memory Bound '
If your cpu utilization is fixed at 100%, then you should worry about
Memory.
Most of the performance problems in databases usually come from locks,
blocking, lot of IO read/writes, etc.
Check http://www.sql-server-performance.com/ for various ways to measure SQL
Server Performance.
Amol.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:E2239F3D-53B5-4DA7-B7AC-280DC8B54ED7@.microsoft.com...
> Hi All,
> Can anybody tell me why Memory is not utilized by MSSQL Service. The
Environment as follows...
> It is an Critical Web based Application with SQL 2000 Standard Edition +
SP3 as backend .
> OS: Win 2000 Advanced Server + 3GB Memory + 2 CPU
> MSSQL Memory Configured as Dynamic with Maximum Memory has been set as "2
GB". This Server has Replication
> Configuration also. During peak time or non-peak time, it always use 1 GB
Memory only. Why MSSQL is not taking more than 1 GB, even though server has
got 3 GB and database's max. memory configured as 2 GB.
> In SQL 2000 Enterprise Edition, to utilize more than 2 GB for database, we
will give /3GB in boot.ini. Similarly anything should be given for SQL 2000
Standard Edition. Right now, boot.ini doesn't contain this option.
> Appreciate your help on this.
> tks in advance,
> vasum
>

mssearch.exe easting up memory

Hi ,
Running SQL 2000 on Windows Advanced Server 2003 Standard. If I leave it up
for three days, the mssearch.exe job just continues to eat up more memory
until the machine runs out of it needing for the mssearch.exe job to be
ended. Then the mssearch.exe runs again and the cycle continues.
I observered that everytime our .net app touches the sql server database,
that is when this mssearch.exe job stays and start to eat up memory. A
google search reveals that open cursors to the database or a web user
creates a link and does nothing causes this mssearch.exe to go up and grow
and grow.
Is there a fix for this or a workaround?
Thanks,
RdR
You should contact Microsoft Product Support and have them take a look at
MSSearch.
Adrian
"RdR" <rrosario@.datamirror.com> wrote in message
news:GWRde.4408$5u4.15748@.nnrp1.uunet.ca...
> Hi ,
> Running SQL 2000 on Windows Advanced Server 2003 Standard. If I leave it
> up for three days, the mssearch.exe job just continues to eat up more
> memory until the machine runs out of it needing for the mssearch.exe job
> to be ended. Then the mssearch.exe runs again and the cycle continues.
> I observered that everytime our .net app touches the sql server database,
> that is when this mssearch.exe job stays and start to eat up memory. A
> google search reveals that open cursors to the database or a web user
> creates a link and does nothing causes this mssearch.exe to go up and grow
> and grow.
> Is there a fix for this or a workaround?
> Thanks,
> RdR
>

mssearch.exe easting up memory

Hi ,
Running SQL 2000 on Windows Advanced Server 2003 Standard. If I leave it up
for three days, the mssearch.exe job just continues to eat up more memory
until the machine runs out of it needing for the mssearch.exe job to be
ended. Then the mssearch.exe runs again and the cycle continues.
I observered that everytime our .net app touches the sql server database,
that is when this mssearch.exe job stays and start to eat up memory. A
google search reveals that open cursors to the database or a web user
creates a link and does nothing causes this mssearch.exe to go up and grow
and grow.
Is there a fix for this or a workaround?
Thanks,
RdRYou should contact Microsoft Product Support and have them take a look at
MSSearch.
Adrian
"RdR" <rrosario@.datamirror.com> wrote in message
news:GWRde.4408$5u4.15748@.nnrp1.uunet.ca...
> Hi ,
> Running SQL 2000 on Windows Advanced Server 2003 Standard. If I leave it
> up for three days, the mssearch.exe job just continues to eat up more
> memory until the machine runs out of it needing for the mssearch.exe job
> to be ended. Then the mssearch.exe runs again and the cycle continues.
> I observered that everytime our .net app touches the sql server database,
> that is when this mssearch.exe job stays and start to eat up memory. A
> google search reveals that open cursors to the database or a web user
> creates a link and does nothing causes this mssearch.exe to go up and grow
> and grow.
> Is there a fix for this or a workaround?
> Thanks,
> RdR
>

mssearch.exe easting up memory

Hi ,
Running SQL 2000 on Windows Advanced Server 2003 Standard. If I leave it up
for three days, the mssearch.exe job just continues to eat up more memory
until the machine runs out of it needing for the mssearch.exe job to be
ended. Then the mssearch.exe runs again and the cycle continues.
I observered that everytime our .net app touches the sql server database,
that is when this mssearch.exe job stays and start to eat up memory. A
google search reveals that open cursors to the database or a web user
creates a link and does nothing causes this mssearch.exe to go up and grow
and grow.
Is there a fix for this or a workaround?
Thanks,
RdRYou should contact Microsoft Product Support and have them take a look at
MSSearch.
Adrian
"RdR" <rrosario@.datamirror.com> wrote in message
news:GWRde.4408$5u4.15748@.nnrp1.uunet.ca...
> Hi ,
> Running SQL 2000 on Windows Advanced Server 2003 Standard. If I leave it
> up for three days, the mssearch.exe job just continues to eat up more
> memory until the machine runs out of it needing for the mssearch.exe job
> to be ended. Then the mssearch.exe runs again and the cycle continues.
> I observered that everytime our .net app touches the sql server database,
> that is when this mssearch.exe job stays and start to eat up memory. A
> google search reveals that open cursors to the database or a web user
> creates a link and does nothing causes this mssearch.exe to go up and grow
> and grow.
> Is there a fix for this or a workaround?
> Thanks,
> RdR
>sql

Msg 624 Could not retrieve row from page by RID...

Running:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
I have a stored procedure that is returning the following error:
Server: Msg 624, Level 20, State 2, Procedure Rpt27310Bal, Line 170
Could not retrieve row from page by RID because the requested RID has a
higher number than the last RID on the page. Rid pageid is (1:5697) and row
num is 0x47.Page pointer = 0x2b778000, PageId = (1:5697), flags = 0x8008,
objectid = 1371279390, indexid = 0, DBID 2.
Connection Broken
Line 170 of the sp is running the following update:
Update f set
LinkID = OwnerOID
From #working f
JOIN EventDetail d on d.EventDetailID = f.EventDetailID
Where f.OwnerTypeCode = 4 and d.TextBuffer1 like '%internaltrx=False%'
If I comment out that update, the error goes away. Sounds like a corrupt
EventDetail index (PK is EventDetailID nonclustered). So I recreated the
table and all indexes. Still getting the error.
I ran DBCC Checkdb with Repair_rebuild. It returned (along with all the
table summaries):
CHECKDB found 0 allocation errors and 0 consistency errors in database
'ELASAdmin'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Any idea where I should go from here?
TIA,
ScottAdditional info:
This error happens on multiple servers (production and dev)
DBID 2 is the tempdb (I did shut down SS and deleted the tempdb. After
restarting...same error.)
This SP had been running fine for about a year.|||Product support might be the next place to do, unfortunately.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_23p0.asp
The only other thing I might try first is ANSI syntax for the update:
update #working set
LinkID = (
select OwnerOID
from EventDetail d
where d.EventDetailID = #working.EventDetailID
and #working.OwnerTypeCode = 4
and EventDetail.TextBuffer1 like '%internalrx=False%'
)
where exists (
select *
from EventDetail d
where d.EventDetailID = #working.EventDetailID
and #working.OwnerTypeCode = 4
and EventDetail.TextBuffer1 like '%internalrx=False%'
)
Steve Kass
Drew University
Scott wrote:
>Running:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
>I have a stored procedure that is returning the following error:
>Server: Msg 624, Level 20, State 2, Procedure Rpt27310Bal, Line 170
>Could not retrieve row from page by RID because the requested RID has a
>higher number than the last RID on the page. Rid pageid is (1:5697) and row
>num is 0x47.Page pointer = 0x2b778000, PageId = (1:5697), flags = 0x8008,
>objectid = 1371279390, indexid = 0, DBID 2.
>Connection Broken
>Line 170 of the sp is running the following update:
>Update f set
> LinkID = OwnerOID
>From #working f
>JOIN EventDetail d on d.EventDetailID = f.EventDetailID
>Where f.OwnerTypeCode = 4 and d.TextBuffer1 like '%internaltrx=False%'
>If I comment out that update, the error goes away. Sounds like a corrupt
>EventDetail index (PK is EventDetailID nonclustered). So I recreated the
>table and all indexes. Still getting the error.
>I ran DBCC Checkdb with Repair_rebuild. It returned (along with all the
>table summaries):
>CHECKDB found 0 allocation errors and 0 consistency errors in database
>'ELASAdmin'.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>Any idea where I should go from here?
>TIA,
>Scott
>
>|||I did see and completed the steps in the MSDN article before posting.
Your ANSI update worked! Do I dare ask why? Like I said, this SP has been
in production for over a year (its for a report that's emailed daily), then
on Friday I get this error.
I did look at the contents of #working just prior to the update and it only
contained about 30 records that looked 'normal'. I'll play around with it
some more on Monday...
Thanks for your help Steve,
Scott
"Steve Kass" <skass@.drew.edu> wrote in message
news:u0rIky$aEHA.556@.tk2msftngp13.phx.gbl...
> Product support might be the next place to do, unfortunately.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_23p0.asp
> The only other thing I might try first is ANSI syntax for the update:
> update #working set
> LinkID = (
> select OwnerOID
> from EventDetail d
> where d.EventDetailID = #working.EventDetailID
> and #working.OwnerTypeCode = 4
> and EventDetail.TextBuffer1 like '%internalrx=False%'
> )
> where exists (
> select *
> from EventDetail d
> where d.EventDetailID = #working.EventDetailID
> and #working.OwnerTypeCode = 4
> and EventDetail.TextBuffer1 like '%internalrx=False%'
> )
>
> Steve Kass
> Drew University
> Scott wrote:
> >Running:
> >Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.2 (Build 3790: )
> >
> >I have a stored procedure that is returning the following error:
> >
> >Server: Msg 624, Level 20, State 2, Procedure Rpt27310Bal, Line 170
> >Could not retrieve row from page by RID because the requested RID has a
> >higher number than the last RID on the page. Rid pageid is (1:5697) and
row
> >num is 0x47.Page pointer = 0x2b778000, PageId = (1:5697), flags = 0x8008,
> >objectid = 1371279390, indexid = 0, DBID 2.
> >Connection Broken
> >
> >Line 170 of the sp is running the following update:
> >
> >Update f set
> > LinkID = OwnerOID
> >From #working f
> >JOIN EventDetail d on d.EventDetailID = f.EventDetailID
> >Where f.OwnerTypeCode = 4 and d.TextBuffer1 like '%internaltrx=False%'
> >
> >If I comment out that update, the error goes away. Sounds like a corrupt
> >EventDetail index (PK is EventDetailID nonclustered). So I recreated the
> >table and all indexes. Still getting the error.
> >
> >I ran DBCC Checkdb with Repair_rebuild. It returned (along with all the
> >table summaries):
> >CHECKDB found 0 allocation errors and 0 consistency errors in database
> >'ELASAdmin'.
> >DBCC execution completed. If DBCC printed error messages, contact your
> >system administrator.
> >
> >Any idea where I should go from here?
> >
> >TIA,
> >
> >Scott
> >
> >
> >
> >

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.nameCOLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName,Convert(varchar(1000),value)as colDescription

From(

Select table_name as colObjectName

FromOpenquery(yourservername,'yourdbname..sp_tables')

) procs

LeftJoin

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table',default,default,default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.name COLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName, Convert(varchar(1000),value) as colDescription

From (

Select table_name as colObjectName

From Openquery (yourservername,'yourdbname..sp_tables')

) procs

Left Join

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table', default, default, default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like 'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.nameCOLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName,Convert(varchar(1000),value)as colDescription

From(

Select table_name as colObjectName

FromOpenquery(yourservername,'yourdbname..sp_tables')

) procs

LeftJoin

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table',default,default,default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Latin1_

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

selectnamefrom sysusers wherenamenotin(selectname COLLATE Latin1_General_CI_AIfrom master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.nameCOLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName,Convert(varchar(1000),value)as colDescription

From(

Select table_name as colObjectName

FromOpenquery(yourservername,'yourdbname..sp_tables')

) procs

LeftJoin

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table',default,default,default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

Msg 468, Level 16, State 9, Line 1 --Cannot resolve the collation conflict between "Lat

execute the following in any user database...

So far I've been able to only reproduce this on SQL 2005 Standard instances..for some reason I don't get the error on Enterprise server.

It appears that perhaps the system objects have different collation attributes..?!!

select name from sysusers where name not in ( select name from master..syslogins )

Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

Oct 14 2005 00:35:21

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This is because your master is in a different collation than your user database.

Try adding the COLLATE to your query.

select name from sysusers where name not in ( select name COLLATE Latin1_General_CI_AI from master..syslogins )

Not sure which collation is your master's but just make sure they use the same collation for the comparison.

I personally like to keep all my collations the same over the entire server.

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Wesley- I have a similar issue but I certainly did NOT reset any collations on purpose. I'm trying to get a match between the sysobjects.name and the objname returned by FN_LISTEXTENDEDPROPERTY from the extended properties table (or view or wherever they hide that stuff in SQL 2005). I tried your suggestion using both collations mentioned in the error -- but no luck. Here's the second attempt:

select o.[id] as 'table_id', o.[name] COLLATE Latin1_General_CI_AI as 'table_name',
0 as 'column_order', NULL as 'column_name', NULL as 'column_datatype',
NULL as 'column_length', Cast(e.value as varchar(500)) as 'column_description'
from sysobjects o
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', N'MyTable', null, default) e on o.name = e.objname
where o.name = 'MyTable'

Any thoughts? Thanks in advance.

|||

Hi,

Rewrite your join like this

on o.name COLLATE Latin1_General_CI_AS = e.objname

hth

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Superb! Works like a charm. Wonder how the COLLATIONS ever got out of synch on system tables?

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Doesn't appear to be a question of out of sync.
I've got the same behaviour on my installation.

If I find some time I'll try to figure out why

Any progress on this one? I'm having a little trouble understanding when I need to add this COLLATE clause.

Dave

|||

Something odd is definitely going on here. We just moved to SQL Server 2005 and this problem is coming up. These are fresh installs of 2k5 and we didn't change any collations on the system meta-views. The default install options, I believe, were accepted on this.

BTW, I'm getting this error on a user database that we scripted as a CREATE Database from a production db. It has a compatibility of 90, not 80, and does have the collation of SQL_Latin1_CP1_CI_AS. All the system databases on SQL 2000 has that same collation. When I look at the properties of the system dbs on 2k5, the collation is also SQL_Latin1_CP1_CI_AS, so maybe it's a column collation issue?

Point is, I don't see a collation for Latin1_General_CI_AI anywhere on my SQL 2k5 server. So why am I getting this error?

Catadmin

|||

Same thing here. This SQL joins input from two system sources. Why they are coming up as two different collations I do not yet know. The objname from the extended property and the table_name (changed to colObjectName in the query) from sp_tables seem to use two different collations. Our install was a converstion from SQL 2000 to SQL 2005 but all DBs are set to Compatibility level of 90 and using SQL_Latin1_General_CP1_CI_AS. I'll keep digging but hopefully someone will post a quick answer here. This is also reproduceable on a test server that was not a conversion from SQL 2005 (was a fresh install).

Select colObjectName, Convert(varchar(1000),value) as colDescription

From (

Select table_name as colObjectName

From Openquery (yourservername,'yourdbname..sp_tables')

) procs

Left Join

::FN_LISTEXTENDEDPROPERTY (

'Description',

'User','dbo',

'Table', default, default, default

)

on colObjectName

-- collate SQL_Latin1_General_CP1_CI_AS --uncomment this line is required to work on our server

= objname

Where colObjectName like 'tbl%' --the prefix we use for in house developed tables

|||I did some research, and my guess is that fn_listextendedproperty returns the property of
mssqlsystemresource, although I have not verified that mssqlsystemresource has this collation. I suspect that this is difficult to address in terms of functionality. Remember that the resouce database is like a DLL, so it cannot be in the server collation, it must always be in one and the same collation.

What should be done is to document it. I filed
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215

MSDTC Required?

I am preparing for the installation of SQL Server 2005 Standard on a
two-node cluster. The SQL Server books Online" topic named "Before
Installing Failover Clustering" indicates that you must install MSDTC if you
are installing the Database Engine and SSIS, Notification Services or
Workstations components. I am not planning on installing SSIS or
Notification Services but would like to install the Workstations components
(specifically SSMS and the other tools) on the clustered nodes.
Is it really necessary to install MSDTC in the cluster if I don't plan on
doing distributed transactions? I want to keep the installation as simple
as possible and don't see why I need to install MSDTC in the cluster with
the Workstation components.
Any help would be appreciated.
Thanks!
Chris
SQL works a lot better with MSDTC. You can go ahead and install it in the
cluster group if you do not have a lot of distributed transactions.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
>I am preparing for the installation of SQL Server 2005 Standard on a
>two-node cluster. The SQL Server books Online" topic named "Before
>Installing Failover Clustering" indicates that you must install MSDTC if
>you are installing the Database Engine and SSIS, Notification Services or
>Workstations components. I am not planning on installing SSIS or
>Notification Services but would like to install the Workstations components
>(specifically SSMS and the other tools) on the clustered nodes.
> Is it really necessary to install MSDTC in the cluster if I don't plan on
> doing distributed transactions? I want to keep the installation as simple
> as possible and don't see why I need to install MSDTC in the cluster with
> the Workstation components.
> Any help would be appreciated.
> Thanks!
> Chris
>
|||Can you clarify "Works a lot better with..."
I always install it as a matter of procedure but if it's not necessary, how
does it help?
"Geoff N. Hiten" wrote:

> SQL works a lot better with MSDTC. You can go ahead and install it in the
> cluster group if you do not have a lot of distributed transactions.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
> news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
>
|||SQL tries to initialize a DTS connection when it starts up. When MSDTC is
not there, SQL has to wait for a timeout so you get slower startups and
failovers.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"burt_king" <burt_king@.yahoo.com> wrote in message
news:CA063DB1-C405-44E5-9628-9C10AE5C7630@.microsoft.com...[vbcol=seagreen]
> Can you clarify "Works a lot better with..."
> I always install it as a matter of procedure but if it's not necessary,
> how
> does it help?
>
> "Geoff N. Hiten" wrote:
|||MS DTC is installed on each cluster node as part of the Windows OS
installation. What is required is to make it "clustered."
If you will only be installing a single SQL Server instance, then you can
configure the MS DTC cluster resource to use the same cluster resource group
as the SQL Server installation.
Otherwise, follow the instructions to set up a dedicated cluster resource
group for MS DTC, which will require a dedicated shared cluster disk, IP
address, and Network Name resources.
Do not run MS DTC as a resource in the Quorum cluster resource group.
Sincerely,
Anthony Thomas

"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
> I am preparing for the installation of SQL Server 2005 Standard on a
> two-node cluster. The SQL Server books Online" topic named "Before
> Installing Failover Clustering" indicates that you must install MSDTC if
you
> are installing the Database Engine and SSIS, Notification Services or
> Workstations components. I am not planning on installing SSIS or
> Notification Services but would like to install the Workstations
components
> (specifically SSMS and the other tools) on the clustered nodes.
> Is it really necessary to install MSDTC in the cluster if I don't plan on
> doing distributed transactions? I want to keep the installation as simple
> as possible and don't see why I need to install MSDTC in the cluster with
> the Workstation components.
> Any help would be appreciated.
> Thanks!
> Chris
>
|||What if this were active/active sql 2000 instances. Still one MSDTC?
"Anthony Thomas" wrote:

> MS DTC is installed on each cluster node as part of the Windows OS
> installation. What is required is to make it "clustered."
> If you will only be installing a single SQL Server instance, then you can
> configure the MS DTC cluster resource to use the same cluster resource group
> as the SQL Server installation.
> Otherwise, follow the instructions to set up a dedicated cluster resource
> group for MS DTC, which will require a dedicated shared cluster disk, IP
> address, and Network Name resources.
> Do not run MS DTC as a resource in the Quorum cluster resource group.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
> news:eN61fcOZHHA.348@.TK2MSFTNGP02.phx.gbl...
> you
> components
>
>
|||Each cluster can only have a single MSDTC instance.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:D7919F72-F753-4232-80CE-DCD52FA271EA@.microsoft.com...[vbcol=seagreen]
> What if this were active/active sql 2000 instances. Still one MSDTC?
>
> "Anthony Thomas" wrote:
|||In which case, the recommended solution is to have a dedicated MS DTC
cluster resource group with dedicated dependencies (Shared Disk, IP Address,
and Network Name).
In times past, Microsoft recommended adding this resource to the Quorum
group; however, it was determined that this was a bad practices because
whenever the quorum goes offline, and DTC handles are destroyed if the two
resources are collocated.
In a dedicated group, even if the quorum group is moved, or the DTC group is
moved, pending DTC transactions and SQL Server handles are maintained.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eKAYYTxaHHA.3272@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Each cluster can only have a single MSDTC instance.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:D7919F72-F753-4232-80CE-DCD52FA271EA@.microsoft.com...
can[vbcol=seagreen]
resource[vbcol=seagreen]
IP[vbcol=seagreen]
plan
>
|||Actually, the recommendation was changed based on testing high-volume
systems where certain storage systems could be saturated by the DTC traffic
to the point where the Quorum disk appeared unresponsive, thus causing a
cluster failure. Since Microsoft is officially hardware agnostic, this led
to a blanket recommendation for a dedicated MSDTC group, which later was
modified to apply only to high volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eZDWpk7aHHA.4140@.TK2MSFTNGP06.phx.gbl...
> In which case, the recommended solution is to have a dedicated MS DTC
> cluster resource group with dedicated dependencies (Shared Disk, IP
> Address,
> and Network Name).
> In times past, Microsoft recommended adding this resource to the Quorum
> group; however, it was determined that this was a bad practices because
> whenever the quorum goes offline, and DTC handles are destroyed if the two
> resources are collocated.
> In a dedicated group, even if the quorum group is moved, or the DTC group
> is
> moved, pending DTC transactions and SQL Server handles are maintained.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eKAYYTxaHHA.3272@.TK2MSFTNGP03.phx.gbl...
> can
> resource
> IP
> plan
>

MSDTC connection problem

Hello, I have a very annoying problem:
The Lan network comprises:
A) SQL Server 2005 Express Edition (WindowsXP Sp2)
B) SQL Server 2005 Standard Edition (Windows 2003 Server Sp2)
When I try to run distributed transaction from *A* to *B*, I receive
the error message:
"Transakcja zostaa ju jawnie lub niejawnie przekazana
lub przerwana"
(my translation is: Transaction has been explicitly or
implicitly interrupted or transfered)
When I try to run distributed transaction from *B* to *A*, I receive
the error message:
"No transaction is active."
The transaction is simple, similar to:
begin distributed tran
SELECT * FROM SerwerA.testowa.dbo.tabela;
commit
MSDTC is running on both computers, properly configured (network-aware),
there are no firewalls running, server names are resolvable (they're
placed in the hosts files). What could be the reason for such a
behaviour? I'd be grateful for any kind of help because I'm fighting
with this problem for a few days...
Regards,
Piotr
Piotr Porzucek wrote:
> Hello, I have a very annoying problem:
> The Lan network comprises:
> A) SQL Server 2005 Express Edition (WindowsXP Sp2)
> B) SQL Server 2005 Standard Edition (Windows 2003 Server Sp2)
> When I try to run distributed transaction from *A* to *B*, I receive
> the error message:
> "Transakcja zostaa ju jawnie lub niejawnie przekazana
> lub przerwana"
> (my translation is: Transaction has been explicitly or
> implicitly interrupted or transfered)
> When I try to run distributed transaction from *B* to *A*, I receive
> the error message:
> "No transaction is active."
> The transaction is simple, similar to:
> begin distributed tran
> SELECT * FROM SerwerA.testowa.dbo.tabela;
> commit
> MSDTC is running on both computers, properly configured (network-aware),
> there are no firewalls running, server names are resolvable (they're
> placed in the hosts files). What could be the reason for such a
> behaviour? I'd be grateful for any kind of help because I'm fighting
> with this problem for a few days...
> Regards,
> Piotr
Hi,
Try to take a look at this link - maybe that can lead you on right track.
http://support.microsoft.com/kb/817064/
Regards
Steen Schlter Persson
Database Administrator / System Administrator

MSDTC connection problem

Hello, I have a very annoying problem:
The Lan network comprises:
A) SQL Server 2005 Express Edition (WindowsXP Sp2)
B) SQL Server 2005 Standard Edition (Windows 2003 Server Sp2)
When I try to run distributed transaction from *A* to *B*, I receive
the error message:
"Transakcja zosta³a ju¿ jawnie lub niejawnie przekazana
lub przerwana"
(my translation is: Transaction has been explicitly or
implicitly interrupted or transfered)
When I try to run distributed transaction from *B* to *A*, I receive
the error message:
"No transaction is active."
The transaction is simple, similar to:
begin distributed tran
SELECT * FROM SerwerA.testowa.dbo.tabela;
commit
MSDTC is running on both computers, properly configured (network-aware),
there are no firewalls running, server names are resolvable (they're
placed in the hosts files). What could be the reason for such a
behaviour? I'd be grateful for any kind of help because I'm fighting
with this problem for a few days...
Regards,
PiotrPiotr Porzucek wrote:
> Hello, I have a very annoying problem:
> The Lan network comprises:
> A) SQL Server 2005 Express Edition (WindowsXP Sp2)
> B) SQL Server 2005 Standard Edition (Windows 2003 Server Sp2)
> When I try to run distributed transaction from *A* to *B*, I receive
> the error message:
> "Transakcja zosta³a ju¿ jawnie lub niejawnie przekazana
> lub przerwana"
> (my translation is: Transaction has been explicitly or
> implicitly interrupted or transfered)
> When I try to run distributed transaction from *B* to *A*, I receive
> the error message:
> "No transaction is active."
> The transaction is simple, similar to:
> begin distributed tran
> SELECT * FROM SerwerA.testowa.dbo.tabela;
> commit
> MSDTC is running on both computers, properly configured (network-aware),
> there are no firewalls running, server names are resolvable (they're
> placed in the hosts files). What could be the reason for such a
> behaviour? I'd be grateful for any kind of help because I'm fighting
> with this problem for a few days...
> Regards,
> Piotr
Hi,
Try to take a look at this link - maybe that can lead you on right track.
http://support.microsoft.com/kb/817064/
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

MSDTC connection problem

Hello, I have a very annoying problem:
The Lan network comprises:
A) SQL Server 2005 Express Edition (WindowsXP Sp2)
B) SQL Server 2005 Standard Edition (Windows 2003 Server Sp2)
When I try to run distributed transaction from *A* to *B*, I receive
the error message:
"Transakcja zostaa ju jawnie lub niejawnie przekazana
lub przerwana"
(my translation is: Transaction has been explicitly or
implicitly interrupted or transfered)
When I try to run distributed transaction from *B* to *A*, I receive
the error message:
"No transaction is active."
The transaction is simple, similar to:
begin distributed tran
SELECT * FROM SerwerA.testowa.dbo.tabela;
commit
MSDTC is running on both computers, properly configured (network-aware),
there are no firewalls running, server names are resolvable (they're
placed in the hosts files). What could be the reason for such a
behaviour? I'd be grateful for any kind of help because I'm fighting
with this problem for a few days...
Regards,
PiotrPiotr Porzucek wrote:
> Hello, I have a very annoying problem:
> The Lan network comprises:
> A) SQL Server 2005 Express Edition (WindowsXP Sp2)
> B) SQL Server 2005 Standard Edition (Windows 2003 Server Sp2)
> When I try to run distributed transaction from *A* to *B*, I receive
> the error message:
> "Transakcja zostaa ju jawnie lub niejawnie przekazana
> lub przerwana"
> (my translation is: Transaction has been explicitly or
> implicitly interrupted or transfered)
> When I try to run distributed transaction from *B* to *A*, I receive
> the error message:
> "No transaction is active."
> The transaction is simple, similar to:
> begin distributed tran
> SELECT * FROM SerwerA.testowa.dbo.tabela;
> commit
> MSDTC is running on both computers, properly configured (network-aware),
> there are no firewalls running, server names are resolvable (they're
> placed in the hosts files). What could be the reason for such a
> behaviour? I'd be grateful for any kind of help because I'm fighting
> with this problem for a few days...
> Regards,
> Piotr
Hi,
Try to take a look at this link - maybe that can lead you on right track.
http://support.microsoft.com/kb/817064/
Regards
Steen Schlter Persson
Database Administrator / System Administrator

MSDTC - standard?

Hi there.
The distributed transaction coordinator that comes with SQL Server
(MSDTC)... is this based on some sort of standard? Are there other
implementations of DTCs?
Any info would be appreciated!
LenAFAIK, DTC supports the XA interface, a protocol defining how transaction ma
nagers can communicate
with each other. I believe XA was defined by X/OPEN, but this was years ago.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"len" <len@.discussions.microsoft.com> wrote in message
news:5ED29886-51F7-4144-AEBD-FBB2408021AF@.microsoft.com...
> Hi there.
> The distributed transaction coordinator that comes with SQL Server
> (MSDTC)... is this based on some sort of standard? Are there other
> implementations of DTCs?
> Any info would be appreciated!
> Len