Multiple Filegroups

Hi experts,

I'm new to SQLServer 2000 and would like some advice on filegroups.

Is there any advantage to seperate the filegroups for different type of data.

For example:

Data_1 for tables
Index_1 for Indexes
Audit_1 for Audit tables

The files for these filegroups would be placed on RAID Disk.

Thanks for any suggestions.Any comments?

I need to know whether there are any performance gain in seperating types of data in different filegroups or is it just good enough to set it to a default secondary filegroup for user data objects.

Please comment.|||Hi,
you can save a lot of system time if you are able to store smart your tables, indexes, ....
This is part from SQL books:

Placing Tables on Filegroups

A table can be created on a specific filegroup rather than the default filegroup. If the filegroup comprises multiple files spread across various physical disks, each with its own disk controller, then queries for data from the table will be spread across the disks, thereby improving performance. The same effect can be accomplished by creating a single file on a RAID (redundant array of independent disks) level 0, 1, or 5 device.

If the computer has multiple processors, Microsoft? SQL Server? 2000 can perform parallel scans of the data. Multiple parallel scans can be executed for a single table regardless of the number of files that are in its filegroup. Additionally, any text, ntext, or image columns within a table can be created on a filegroup other than the one that contains the base table.

Eventually, there is a saturation point when there are too many outstanding I/O's causing bottlenecks in the disk I/O subsystem. These bottlenecks can be identified by using Windows NT? Performance Monitor to monitor the PhysicalDisk object and Disk Queue Length counter. If the Disk Queue Length counter is greater than three, consider spreading the file across more disk drives. For more information, see Monitoring Disk Activity.

It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access. To spread data evenly across all disks, you can place a single file across striped disks or maintain each disk separately and place a file on each disk.

I hope it will help you. It's very good text.
Bye

|||No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.sql

Multiple Dynamic Inserts with SQL

I'm try to a multiple insert from one database to another by using this code:

insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)
(selecttop 30 acno,surname,name,id,type
from [PretoriaDB].[dbo].[WorkingDB])

but I keep on getting this error:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Can any one help!!

If the target table will always be empty, you can just drop the table before the insert and use a "Select Into" statement. Otherwise, you will probably have to open a cursor for the "select top 30 ..." statement and iterate over it to insert each record to the table.

|||

tmametja:

String or binary data would be truncated.

the Error says that...

One of the fields on table [mpis].[dbo].[Residents] of type varchar/char is beingfed data that is too long. i.e. One of the Firleds in [PretoriaDB].[dbo].[WorkingDB] that you are usingto populate the [mpis].[dbo].[Residents] probably contains a string value that is too large for the field thatyou are trying to plug it into.

hope it helps to solve you problem./.

|||

i didn't know 2005 had an "INSERT...SELECT" statement... cool

Anyhow, looks like the syntax is INSERT [table_name] SELECT [colA], [colB] ... FROM [table_name_or_join]

I didn't see any "INTO" in the examples, or any mention of the target column list on the target table as in a tradional insert statement.

|||

tmametja:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Make sure data types (in INSERT and SELECT) are match and field length as well.

Good luck.

|||

thanx

|||

thanxkaushalparik27

|||

tmametja:

thanxkaushalparik27

well pleasure to help you, and dont forget to mark the answers which helped you and resolve the thread. Thanx

Multiple databases using different collations on same instance

Hi all,
I'm in need of some advice, I currently have a SQL 2005 64 bit instance
running SQL_Latin1_CP_CI_AS, which is the collation type for my databases and
suits me fine, however I now need to create and populate a new database which
requires Latin1_General_BIN
Is this possible, are there any pitfalls I should watch out for.
ThanksHi
The biggest problem with creating non-system collation columns is probably
the collation conflicts that you may get when doing out of database
operations, such as using temporary tables; but you can usually get around
this by always specifying a collation using the COLLATE clause.
If you are only looking at using this collation for binary comparisons, you
may want to look at using the same collation but using the COLLATE clause to
force a binary collation in your where clause. This may be a more risky
problem as it will not give you an error when you forget the clause.
Checkout books online for more about COLLATE.
John
"Padraig" wrote:
> Hi all,
> I'm in need of some advice, I currently have a SQL 2005 64 bit instance
> running SQL_Latin1_CP_CI_AS, which is the collation type for my databases and
> suits me fine, however I now need to create and populate a new database which
> requires Latin1_General_BIN
> Is this possible, are there any pitfalls I should watch out for.
> Thanks

Multiple counts in table

I've got a table containing (among some other columns) 3 different type columns.

TABLE
xxxx as int
type 1 as string
type 2 as string
type 3 as string
date as Date

First i'd like to select the number of counts each of these appear in my table. I realized that it can be done by union 3 select statements each grouped by a type.

select type1, count(type1) from table group by type1
union
select type2, count(type2) from table group by type2...

Now this is time consuming and not what I would like to do

Secondly i'll try to do something like

SELECT type1 ,
SUM(CASE WHEN MONTH(date) = 1 THEN 1 END) AS 'Januar'
,SUM(CASE WHEN MONTH(date) = 2 THEN 1 END) AS 'Februar'
,SUM(CASE WHEN MONTH(date) = 3 THEN 1 END) AS 'Mars'
,SUM(CASE WHEN MONTH(date) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(date) = 5 THEN 1 END) AS 'Mai'
,SUM(CASE WHEN MONTH(date) = 6 THEN 1 END) AS 'Juni'
,SUM(CASE WHEN MONTH(date) = 7 THEN 1 END) AS 'Juli'
,SUM(CASE WHEN MONTH(date) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(date) = 9 THEN 1 END) AS 'Sept'
,SUM(CASE WHEN MONTH(date) = 10 THEN 1 END) AS 'Okt'
,SUM(CASE WHEN MONTH(date) = 11 THEN 1 END) AS 'Nove'
,SUM(CASE WHEN MONTH(date) = 12 THEN 1 END) AS 'Desem'
,SUM(CASE WHEN YEAR(date) = 2006 THEN 1 END) AS 'TOTAL'
FROM table
WHERE YEAR(date) = 2006
GROUP BY type

This in combination with union the other 2 types results in what I'd like to do but then again, phuu this is some crappy approach :)

Any suggustions in how to solve this?
I.E I'd like to list each type count for each month like this

Jan Feb Mars ...
type1 23 43 45
type2 12 11 15
type3 54 55 65

Any hints?

/J

Maybe something like?

set nocount on

-- --
-- I am confused by the way that the 'type' column is laid
-- out. The table design seems to indicate that there are
-- separate columns for types 1, 2 and 3; however, the report
-- layout indicates that types 1, 2 and 3 are rather specific
-- instances of a single type column. Since the latter is
-- more commonly practiced, that is the way that I shall
-- approach the problem.
--
-- --
declare @.table table
( rid integer not null
primary key,
type varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number,
'Type' + convert (char(1), number%3 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from ( select type,
month (date) as [month],
count(*) as typeCount
from @.table
group by type,
month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv

-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 11 9 10 10 11 10 10 10 5 0 0 0
-- Type2 10 10 10 10 10 10 11 10 4 0 0 0
-- Type3 10 9 11 10 10 10 10 11 4 0 0 0

|||

Unfortunatly the types are 3 different columns (at design time this report was not considered).
To work around this I union these to one "alltypes" column. This is timeconsuming and I would prefere not to do so.

Part from that your approach works find (part from pivot, I'm on SQL server 2000, not a problem though).

Is there a workaround for the unions?

Thanks

|||Sure there is; please stand by|||

set nocount on

declare @.table table
( rid integer not null
primary key,
[type 1] varchar (10),
[type 2] varchar (10),
[type 3] varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number + 1,
'Type' + convert (char(1), number%7 + 1),
'Type' + convert (char(1), (number+1)%7 + 1),
'Type' + convert (char(1), (number+5)%7 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from( select value as Type,
month (date) as [Month],
count(*) as typeCount
from @.table
unpivot ( value for Type in ([Type 1],[Type 2],[Type 3])
) as unpiv
group by value, month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12])
) piv

-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 14 12 12 14 13 12 14 13 6 0 0 0
-- Type2 14 12 13 13 14 12 14 13 6 0 0 0
-- Type3 14 12 13 12 14 13 13 14 5 0 0 0
-- Type4 13 12 14 12 14 13 12 14 6 0 0 0
-- Type5 12 12 14 13 13 13 13 14 5 0 0 0
-- Type6 13 12 14 13 12 14 13 13 5 0 0 0
-- Type7 13 12 13 13 13 13 14 12 6 0 0 0

Multiple connection type query

Hi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctosuse linked tables to sql server in Access and create your insert query in access.

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
========================================
==
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
========================================
==
The LDP-tool gives the following results:
========================================
==
***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:[vbcol=seagreen]
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=l
ocal;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;[vbcol=seagreen]
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
========================================
==
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ========================================
==
> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ========================================
==
>
> The LDP-tool gives the following results:
> ========================================
==
> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=l
ocal;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ========================================
==
> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/defaul...kb;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens Sü?meyer" wrote:

> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
>
>

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
==========================================
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
==========================================
The LDP-tool gives the following results:
==========================================
***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:[vbcol=seagreen]
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;[vbcol=seagreen]
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
==========================================
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.
Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ==========================================
> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ==========================================
>
> The LDP-tool gives the following results:
> ==========================================
> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ==========================================
> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>
|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/default...b;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens Sü?meyer" wrote:

> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
>
>

Multiple accounts with the name MSSQLSvc...

Hi,
Got a KDC Error with the following description:
========================================== Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 28-04-2005
Time: 2:01:01
User: N/A
Computer: server
Description:
There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
type DS_SERVICE_PRINCIPAL_NAME.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
==========================================
The LDP-tool gives the following results:
========================================== ***Searching...
ldap_search_s(ld, "DC=domain,DC=local", 2,
"serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
&msg)
Result <0>: (null)
Matched DNs:
Getting 2 entries:
>> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
4> objectClass: top; person; organizationalPerson; user;
1> cn: Administrator;
1> description: Built-in account for administering the computer/domain;
1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
1> name: Administrator;
1> canonicalName: domain.local/Users/Administrator;
>> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
5> objectClass: top; person; organizationalPerson; user; computer;
1> cn: server;
1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
1> name: server;
1> canonicalName: domain.local/Domain Controllers/server;
==========================================
Can anyone explain me what I can do about this? Deleting one of the accounts
is not an option I guess... I read that in some cases a computer or user
should be unregistered en registered again but in this case I'm not so
confident about it re-registring the Server itself or the
administrator-account..
Any help on this is much appreciated.
Michel Schuurman
Omni Trade Automatisering B.V.Somebody setup the SPN for the service account on those machines,
unfortunately the same SPN has been promoted more than one time.
Jens Suessmeyer.
"Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> Got a KDC Error with the following description:
> ==========================================> Event Type: Error
> Event Source: KDC
> Event Category: None
> Event ID: 11
> Date: 28-04-2005
> Time: 2:01:01
> User: N/A
> Computer: server
> Description:
> There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> type DS_SERVICE_PRINCIPAL_NAME.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ==========================================>
> The LDP-tool gives the following results:
> ==========================================> ***Searching...
> ldap_search_s(ld, "DC=domain,DC=local", 2,
> "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> &msg)
> Result <0>: (null)
> Matched DNs:
> Getting 2 entries:
>> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
> 4> objectClass: top; person; organizationalPerson; user;
> 1> cn: Administrator;
> 1> description: Built-in account for administering the computer/domain;
> 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> 1> name: Administrator;
> 1> canonicalName: domain.local/Users/Administrator;
>> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
> 5> objectClass: top; person; organizationalPerson; user; computer;
> 1> cn: server;
> 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> 1> name: server;
> 1> canonicalName: domain.local/Domain Controllers/server;
> ==========================================> Can anyone explain me what I can do about this? Deleting one of the
> accounts is not an option I guess... I read that in some cases a computer
> or user should be unregistered en registered again but in this case I'm
> not so confident about it re-registring the Server itself or the
> administrator-account..
> Any help on this is much appreciated.
>
> Michel Schuurman
> Omni Trade Automatisering B.V.
>|||The SPN should be registered under the account SQL is starting under, and
ONLY that account.
You can use the utility setspn to check for the existence of other spn's,
delete the ones you don't want, and add the one you need.
Please note...you are NOT deleting the ACCOUNT, but the Service Principle
Name, which resides IN that user object.
Here's an article with more info than you ever wanted to know about SQL and
SPN's.:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
but there are links to getting setspn in there.
Donna Lambert
"Jens Sü�meyer" wrote:
> Somebody setup the SPN for the service account on those machines,
> unfortunately the same SPN has been promoted more than one time.
> Jens Suessmeyer.
>
> "Michel Schuurman" <ms_remove_@.omni-trade.nl> schrieb im Newsbeitrag
> news:uW9$Ad9SFHA.2172@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Got a KDC Error with the following description:
> >
> > ==========================================> > Event Type: Error
> > Event Source: KDC
> > Event Category: None
> > Event ID: 11
> > Date: 28-04-2005
> > Time: 2:01:01
> > User: N/A
> > Computer: server
> > Description:
> > There are multiple accounts with name MSSQLSvc/server.domain.local:1433 of
> > type DS_SERVICE_PRINCIPAL_NAME.
> >
> > For more information, see Help and Support Center at
> > http://go.microsoft.com/fwlink/events.asp.
> > ==========================================> >
> >
> > The LDP-tool gives the following results:
> >
> > ==========================================> > ***Searching...
> > ldap_search_s(ld, "DC=domain,DC=local", 2,
> > "serviceprincipalname=MSSQLSvc/server.domain.local:1433", attrList, 0,
> > &msg)
> > Result <0>: (null)
> > Matched DNs:
> > Getting 2 entries:
> >> Dn: CN=Administrator,CN=Users,DC=domain,DC=local
> > 4> objectClass: top; person; organizationalPerson; user;
> > 1> cn: Administrator;
> > 1> description: Built-in account for administering the computer/domain;
> > 1> distinguishedName: CN=Administrator,CN=Users,DC=domain,DC=local;
> > 1> name: Administrator;
> > 1> canonicalName: domain.local/Users/Administrator;
> >> Dn: CN=server,OU=Domain Controllers,DC=domain,DC=local
> > 5> objectClass: top; person; organizationalPerson; user; computer;
> > 1> cn: server;
> > 1> distinguishedName: CN=server,OU=Domain Controllers,DC=domain,DC=local;
> > 1> name: server;
> > 1> canonicalName: domain.local/Domain Controllers/server;
> > ==========================================> >
> > Can anyone explain me what I can do about this? Deleting one of the
> > accounts is not an option I guess... I read that in some cases a computer
> > or user should be unregistered en registered again but in this case I'm
> > not so confident about it re-registring the Server itself or the
> > administrator-account..
> >
> > Any help on this is much appreciated.
> >
> >
> >
> > Michel Schuurman
> >
> > Omni Trade Automatisering B.V.
> >
>
>