Create as Clustered Index Error

Hi,
I am getting an error message when I try to make an already existing,
unique index, clustered. I have other databases with the same core
design, some will allow the clustered index to be created, others
display the same error. Could anyone tell me what the error message is
refering to?
Details are:
SQL2000 SP3
The column is of data type varchar.
I am using Enterprise Manager and trying to change the index through the
design - properties window.
There are no nulls in this column.
There are relationships with six other tables, all bound to this column.
Enforce relationship for replication and Enforce relationship for
INSERTs and UPDATEs are checked.
Error Message:
'Tracks' table
- Unable to create index 'PK_Tracks'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location:
statisti.cpp:3222
Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
SPID: 51
Process ID: 828
Many thanks
Gareth KingGareth King (QBVBKADUOIUJ@.spammotel.com) writes:
> I am getting an error message when I try to make an already existing,
> unique index, clustered. I have other databases with the same core
> design, some will allow the clustered index to be created, others
> display the same error. Could anyone tell me what the error message is
> refering to?
> Details are:
> SQL2000 SP3
> The column is of data type varchar.
> I am using Enterprise Manager and trying to change the index through the
> design - properties window.
> There are no nulls in this column.
> There are relationships with six other tables, all bound to this column.
> Enforce relationship for replication and Enforce relationship for
> INSERTs and UPDATEs are checked.
> Error Message:
> 'Tracks' table
> - Unable to create index 'PK_Tracks'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> [Microsoft][ODBC SQL Server Driver][SQL Server]Location:
> statisti.cpp:3222
> Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
> SPID: 51
> Process ID: 828
That seems like an assertion error. An assertion error is a check
that the programmer adds to his code, to be sure that some condition
is true at this point. If they are not, he aborts, because if he
continued he could cause a big mess.
Or more concisely: this is a bug in SQL Server.
What you could try if you want to create this index as quick as
possible, is to configure SQL Server to only use one single processor
and then run the CREATE INDEX statement. These errors are often due
to problems with parallelism.
If that does not work out, I would suggest that you open a case with
Microsoft. Since this is a bug in SQL Server, any expenses you may
be charged initially, should be refunded.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

create a unique Random integer

Hi there,
I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key.

I have written the following function, but when i call it as a default value for a field, it does not produce a unique number.

CREATE FUNCTION GETNEXTID(@.CURDATE DATETIME)
RETURNS BIGINT
AS
BEGIN
RETURN (SELECT CAST(
CAST(DATEPART(YY,@.CURDATE) AS VARCHAR) +
RIGHT('0' + CAST(DATEPART(M,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(D,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(HH,@.CURDATE) AS VARCHAR),2) +
RIGHT('0' + CAST(DATEPART(SS,@.CURDATE) AS VARCHAR),2) +
RIGHT('00' + CAST(DATEPART(MS,@.CURDATE) AS VARCHAR),3) AS BIGINT))
END

Can anyone help?I would create a table as follows:

Create Table DatabaseId
(
DBId Int Identity,
DBName vachar (40) NOT NULL
)

When you import data from the different databases, prefix your unique identifier with DBId. That should give you an unique number across the DB's.|||Take a look at newid()

Create a linked server between 2 sql serve

Hi,
Hw can I create a linked server between 2 sql server databases in diferente
machines?
How can I indicate the 2 catalogs and the 2 servers?
Thanks in advance
Pedro
The syntax is:
[LINKEDSERVER].[Databasename].[owner].[Objectname]
e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> Hi,
> Hw can I create a linked server between 2 sql server databases in
> diferente
> machines?
> How can I indicate the 2 catalogs and the 2 servers?
> Thanks in advance
> Pedro
|||Hi Jens,
My question is how can I create a linkedserver.
Imagine that I have 2 sql server's, SQLSERVER1 with a catalog BOOK and
SQLSERVER2 with a catalog PUBLISHER.
I want to link this to server so I can have a trigger on a table of the BOOK
database on SQLSERVER1 invoke a storeprocedure of database PUBLISHER on
SQLSERVER2
How can I do this?
How can I setup this kind of linkedserver?
On Entreprise manager I can add a linkedserver chose "SQL Server" but I
can't setup the address of the 2 servers or the 2 databases on that 2
servers.
"Jens Sü?meyer" wrote:

> The syntax is:
> [LINKEDSERVER].[Databasename].[owner].[Objectname]
> e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
>
>
|||I wouldn=B4t do that, trigegr behave synchron, they wait for the
transaction started, so if the line between the servers isn=B4t setup
properly or the other server doesn=B4t repsond you will get into trouble
because the transaction is rolled back.
Anyway, the name of the server (in the menu of the linkedserver) is the
name of the server/instance. The database can be reached with the above
mentioned 4 part notation.
otherwise you could use the sp_addlinkedserver command which can be
found (with samples) int he BOL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de

Create a linked server between 2 sql serve

Hi,
Hw can I create a linked server between 2 sql server databases in diferente
machines?
How can I indicate the 2 catalogs and the 2 servers?
Thanks in advance
PedroThe syntax is:
[LINKEDSERVER].[Databasename].[owner].[Objectname]
e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> Hi,
> Hw can I create a linked server between 2 sql server databases in
> diferente
> machines?
> How can I indicate the 2 catalogs and the 2 servers?
> Thanks in advance
> Pedro|||Hi Jens,
My question is how can I create a linkedserver.
Imagine that I have 2 sql server's, SQLSERVER1 with a catalog BOOK and
SQLSERVER2 with a catalog PUBLISHER.
I want to link this to server so I can have a trigger on a table of the BOOK
database on SQLSERVER1 invoke a storeprocedure of database PUBLISHER on
SQLSERVER2
How can I do this?
How can I setup this kind of linkedserver?
On Entreprise manager I can add a linkedserver chose "SQL Server" but I
can't setup the address of the 2 servers or the 2 databases on that 2
servers.
"Jens Sü�meyer" wrote:
> The syntax is:
> [LINKEDSERVER].[Databasename].[owner].[Objectname]
> e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Objectname]
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> > Hi,
> >
> > Hw can I create a linked server between 2 sql server databases in
> > diferente
> > machines?
> > How can I indicate the 2 catalogs and the 2 servers?
> >
> > Thanks in advance
> >
> > Pedro
>
>|||I wouldn=B4t do that, trigegr behave synchron, they wait for the
transaction started, so if the line between the servers isn=B4t setup
properly or the other server doesn=B4t repsond you will get into trouble
because the transaction is rolled back.
Anyway, the name of the server (in the menu of the linkedserver) is the
name of the server/instance. The database can be reached with the above
mentioned 4 part notation.
otherwise you could use the sp_addlinkedserver command which can be
found (with samples) int he BOL.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--sql

Create a linked server between 2 sql serve

Hi,
Hw can I create a linked server between 2 sql server databases in diferente
machines?
How can I indicate the 2 catalogs and the 2 servers?
Thanks in advance
PedroThe syntax is:
[LINKEDSERVER].[Databasename].[owner].[Objectname]
e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[Ob
jectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
> Hi,
> Hw can I create a linked server between 2 sql server databases in
> diferente
> machines?
> How can I indicate the 2 catalogs and the 2 servers?
> Thanks in advance
> Pedro|||Hi Jens,
My question is how can I create a linkedserver.
Imagine that I have 2 sql server's, SQLSERVER1 with a catalog BOOK and
SQLSERVER2 with a catalog PUBLISHER.
I want to link this to server so I can have a trigger on a table of the BOOK
database on SQLSERVER1 invoke a storeprocedure of database PUBLISHER on
SQLSERVER2
How can I do this?
How can I setup this kind of linkedserver?
On Entreprise manager I can add a linkedserver chose "SQL Server" but I
can't setup the address of the 2 servers or the 2 databases on that 2
servers.
"Jens Sü?meyer" wrote:

> The syntax is:
> [LINKEDSERVER].[Databasename].[owner].[Objectname]
> e.g. Select * from [LINKEDSERVER].[Databasename].[owner].[
Objectname]
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:B828F7E5-C4A1-41B2-9717-D1DC9433B6DB@.microsoft.com...
>
>|||I wouldn=B4t do that, trigegr behave synchron, they wait for the
transaction started, so if the line between the servers isn=B4t setup
properly or the other server doesn=B4t repsond you will get into trouble
because the transaction is rolled back.
Anyway, the name of the server (in the menu of the linkedserver) is the
name of the server/instance. The database can be reached with the above
mentioned 4 part notation.
otherwise you could use the sp_addlinkedserver command which can be
found (with samples) int he BOL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

crash recovery (huge rollback) going on over 24 hours! How can I stop it?

Crash recovey is going on over 24 hours!

I want to stop it!

One of the databases in our server is in recovery mode after a huge

delete job (started as a job) did not complete ( I suspect due to

lack of transaction log sapce.) the log file size is 120 GB.

Whenever the server restarted the recovery for this database starts.

After 20 hours, analysis done 100% and recovery done up to

76%. Then after “the percentage of recovery done” went down to

16% and it is still running.

How can I get rid of this database without damaging the master database and other database in the same instance?

I don’t mind to lose this database.

There are 4 user databases in this instance and msdb and the recovery mode database are not operational.(I cannot connect)

Your best option is to let recovery complete. Anything else you try to do is not supported and could end up causing you bigger problems.

I also recommend batching up your delete job in some way to avoid having a single, huge transaction that could cause the log file to run out of space.

Thanks

CPU Usage(%), Logical IO Performed (%) Usage for Adhoc Queries is 90%

Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.

90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?

sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.

Thanks in advance. Hail SQL Server!

This means that of all the CPU and I/O performed 90% is coming from Adhoc Queries, it does not mean 90% CPU usage.

WesleyB

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

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server
.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that mos
t
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100
%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

CPU usage on SQL server

Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?
Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com

Couple of questions = )

1.
Does anyone know the max number of Databases a single MSSQL 2000 enterprise machine can handle ??
2.
Does anyone have an idea on how you could use the DTS in MSSQL server to kick a script off on a UNIX machine ??
Chris1. 32,767
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

Counter settings?

I'm changing databases for a website from Access to SQl Server.
In the Access file, the ID column is assign as 'counter' so the value is automaticly raised by 1 when 'Insert'-statement is used.
How is the equivalent made for SQL Server?
I get an error message when I use the 'Insert statement'

Thanks
CalleOriginally posted by Calle
I'm changing databases for a website from Access to SQl Server.
In the Access file, the ID column is assign as 'counter' so the value is automaticly raised by 1 when 'Insert'-statement is used.
How is the equivalent made for SQL Server?
I get an error message when I use the 'Insert statement'

Thanks
Calle

Try changing your ID column to an integer and set the Identity property to Yes (not for replication), set seed to 1 and increment to 1.

This is the same as the Autonumber function in Access.|||I think this has already been done. actually this is the reason for the error. you can remove identity property on the target table and do your insert. once it completes you can reestablish identity(1,1). another way would be to set identity_insert your_table_name on before the insert, and then set it off after insert completes. third way would be to exclude the identity field from being populated. but the last method would assign brand new identity values which may break relationship between tables.