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

Counts on Subquerys

OK I have a query that bring back a unique identifier and text value
for instance

Select Distinct global_id, Page_url from PageList

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kindSELECT COUNT(DISTINCT Page_url) from PageList

??|||Originally posted by Brett Kaiser
SELECT COUNT(DISTINCT Page_url) from PageList

??

Brett, this doesn't seem to accomplich what I'm looking for. It's only bringing a count back of one for answerthis.htm, when there are clearly three records.

any suggestions|||I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Isn't the 1st row of your result set kind of ironic?|||Originally posted by Brett Kaiser
I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

The problem I'm having is that the unique global_id is breaking out each record on the group by. so when it is added i can't get a count of the page_url

Isn't the 1st row of your result set kind of ironic?|||Well was that it?|||How about this:

SELECT page_url, count(page_url)
FROM pagelist
group by page_url

Counting Unique Values?

I have a database that holds information about the location of our members. Their location is held as an ID that it gets from another table in the databse.

Is there an SQL SELECT query I can write that will return a result listing the ID and the number of times it appears? I am sure this is rather simple but I just am unaware of the code?

thanks.Assuming that you are using SQL, you could use the GROUP BY clause to do what you've described.

What tool(s) are you using, and what is your table design?

-PatP|||I am using MS Access. The table has fields holding member data. All items that will potentially be repeated ie gender, location, member package, member category, etc are populated with a unique ID referencing another table containing the data that is pertinent to those. All the IDs are held in number fields.

I assume that is the info you were after?|||yes, Access supports COUNT(*) and GROUP BYselect ID,count(*) as rows from sometable group by ID

Counting unique FK

Hi,
I'm not sure how to explain my problem, so I'll go ahead with an example.
There are two tables: tblContact & tblContactAddress
tblContactAddress has a FK ContactId.
I want my SP to return something like this:
ContactAddressId ContactId ... Position
-- -- -- --
1 100 ... 1
2 100 ... 1
3 101 ... 2
4 102 ... 3
5 103 ... 4
6 103 ... 4
7 103 ... 4
8 103 ... 4
9 104 ... 5
So Field 'Position' should increase one every time FK ContactId Changes.
We're on SQL 2000
TIA!
MichaelIt would have been good, if you had given the ddl and insert script.
Anyways, here is the answer :)
create table tbl (ContactAddressId int, ContactId int)
insert into tbl values(1 ,100 )
insert into tbl values(2 ,100 )
insert into tbl values(3 ,101 )
insert into tbl values(4 ,102 )
insert into tbl values(5 ,103 )
insert into tbl values(6 ,103 )
insert into tbl values(7 ,103 )
insert into tbl values(8 ,103 )
insert into tbl values(9 ,104 )
select a.ContactAddressId,a.contactid, count(distinct b.ContactId) from tbl
a, tbl b
where a.ContactId >= b.ContactId
group by a.ContactAddressId,a.contactid
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi Omnibuzz,
Thanks for your input.
The result is indeed what i needed.
The only downside is slow performance (six seconds for only 2.700 records).
Kind regards,
Michael
"Omnibuzz" wrote:

> It would have been good, if you had given the ddl and insert script.
> Anyways, here is the answer :)
> create table tbl (ContactAddressId int, ContactId int)
> insert into tbl values(1 ,100 )
> insert into tbl values(2 ,100 )
> insert into tbl values(3 ,101 )
> insert into tbl values(4 ,102 )
> insert into tbl values(5 ,103 )
> insert into tbl values(6 ,103 )
> insert into tbl values(7 ,103 )
> insert into tbl values(8 ,103 )
> insert into tbl values(9 ,104 )
>
> select a.ContactAddressId,a.contactid, count(distinct b.ContactId) from tb
l
> a, tbl b
> where a.ContactId >= b.ContactId
> group by a.ContactAddressId,a.contactid
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||slow performance..
you need an index on ContactId..
You can't do without the self join... So I guess its your call :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Michael Maes" wrote:
> Hi Omnibuzz,
> Thanks for your input.
> The result is indeed what i needed.
> The only downside is slow performance (six seconds for only 2.700 records)
.
> Kind regards,
> Michael
> "Omnibuzz" wrote:
>|||If you are using SQL Server 2005, then you can use the dense_rank() function
,
Much simpler..
select a.ContactAddressId,a.contactid, dense_rank() over(order by a.Contacti
d)
from tbl a
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> slow performance..
> you need an index on ContactId..
> You can't do without the self join... So I guess its your call :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Michael Maes" wrote:
>|||Thanks for your help Omnibuzz.
Unfortunatly most of our customers haven't migrated to 2005 yet :-(
"Omnibuzz" wrote:
> If you are using SQL Server 2005, then you can use the dense_rank() functi
on,
> Much simpler..
> select a.ContactAddressId,a.contactid, dense_rank() over(order by a.Contac
tid)
> from tbl a
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Omnibuzz" wrote:
>

Counting unique entries in a SQL Statement

I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.

I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".

Does this make sense?

EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....

SAGMIR 025 001 HEM/FIR HLF550...

SAGMIR 025 001 HEM/FIR KILN-D HLF505...

SAGMIR 025 002 HEM/FIR HLF660....

The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...

Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!

Michael

Yes, this is not clear to me yet. Could you explain this a bit in detail ? There is a function in SQL Server which can produce a rank based on several columns. The example for procuding such number would be to get new running number for Order Details per order number. Is is this what you are trying to do ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Your best bet is probably a temporary table... and then join back to it.something like the following :

create table #tempinv
(
invid int identity(1,1),
bl_id int not null
)

insert into #tempinv
(
bl_id
)
select distinct BL_ID from invoices
order by BL_ID

select i.VES, i.VOY, t.invid, i.PRO, i.MARK
from invoices i
inner join #tempinv t on t.bl_id = i.BL_ID
order by i.BL_ID

drop table #tempinv

I think this is what you want....

counter inside select statement?

Hi, can you add a counter inside a select statement to get a unique id line of the rows?

In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.

This is the normal way:

SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID) ... some more where/order by statements

This is what i want:

DECLARE @.tempCounter bigint
SET @.tempCounter = 0
SELECT @.tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID)... some more where/order by statements and at the end.. (SELECT @.tempCounter = @.tempCounter + 1)

Anyone know if this can be done?

Use Row Num ( if you are using SQL SErver 2005 ) as

Select ROW_NUMBER() OVER(ORDER BY Some_Field ) AS rownum,
* from Table_Nane

|||

yes i am using MS SQL 2005, it worked for half of the problem, ill display the SP below, what the SP does is to select the posts from a single user, by ordering them first by topicID then by date created.
In order to bring back just what the client need, i incorperated a custom paging system.

DECLARE @.first_idint, @.startRowint
SET @.startRowIndex=(@.startRowIndex- 1)* @.maximumRows+ 1
IF @.startRowIndex= 0
SET @.startRowIndex= 1

SETROWCOUNT @.startRowIndex

SELECT @.first_id=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC)FROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT @.maximumRows

DECLARE @.tempVarint
SELECT @.tempVar=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC), forum_answer.topicid, forum_answer.id, forum_answer.username, forum_answer.answer, forum_answer.created, profile_publicinfo.signatureFROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)AND(@.first_id<= @.tempVar)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT 0

The first thing we do, is to get the id for the first post that should be returned. This works with the ROW_NUMBER method, the second select statement, takes xx number of rows with start from the position it recieves from the first select statement. So if @.first_id is 5 and maximumRows is 5, then the second select statement will only take rows 5->10 from the table.

The problem is the ROW_NUMBER inside the second select statement, sql screams if i put it at the end like (@.first_id <= ROW_NUMBER...

Msg 4108, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 32
Windowed functions can only appear in the SELECT or ORDER BY clauses.

SQL also screams if i put it in the beginning (as shown above), then i get the following error:

Msg 141, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 33
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Thats why i wanted a way to calculate the rows and get a ID according to that. Any idea of how to proceed?