SQL: Delete woes part...3?

"darrel" <notreal@nowhere.com> wrote in message
news:etefm3$lcf$1@forums.macromedia.com...
> And yes, that was EXACTLY the problem! ;o)
>
> -Darrel

I'm glad that was it, because I was out of ideas after that. :)

> You're confusing a primary key with an identity column.
> Primary key is not the same as identity or autonumber
> Primary key <> identity/autonumber
> Primary key != identity/autonumber
>
> Sorry, pet peeve. :)

And I think you've had to explain this to me before. As such...DON'T
APOLOGIZE! ;o)

> On the public database, add the primary key constraint. Just don't make
> the column an identity column.

Yes! That's becoming clear now. I ran into this problem using SQL Express
getting confused between the two. Thanks for the refresher on that concept.

And yes, that was EXACTLY the problem! ;o)

-Darrel

"darrel" <notreal@nowhere.com> wrote in message
news:ete7p3$bab$1@forums.macromedia.com...
> Is the 'fix' for this to just add a new column to the public facing tables
> with a PK? Better method?
>
> -Darrel

You're confusing a primary key with an identity column.
Primary key is not the same as identity or autonumber
Primary key <> identity/autonumber
Primary key != identity/autonumber

Sorry, pet peeve. :)

A primary key can be anything at all, or even span more than one column.
Its only requirement is that it identifies a row uniquely. An identity is
an auto-incrementing integer column that takes a seed (first value) and
increment (the amount added to the last value for the next value). A
primary key constraint does not set any values; that's a property shared by
the IDENTITY property and DEFAULT constraints.

Are you noticing a performance problem on your databases? A primary key
creates a clustered index for you; without a clustered index, your table is
just a heap. You can certainly create a clustered index on columns other
than the key columns, but you need to have one somewhere in the table.

On the public database, add the primary key constraint. Just don't make the
column an identity column.

So for the internal DB, you'd have:
CategoryID INT NOT NULL IDENTITY(1,1)

And on the public DB you'd have:
CategoryID INT NOT NULL

On *both* DB's you'll have:
PRIMARY KEY(CategoryID)

To add the key to an existing table, run the following statement (you can
add a key constraint to an existing column with no problems, so long as the
data in that column is already unique):

ALTER TABLE <table name>
ADD PRIMARY KEY(<column name(s)>)
GO

If you've got a clustered index elsewhere in the table, you may have to add
the NONCLUSTERED keyword. If you don't know, then LEAVE IT OFF - let the DB
throw an error first. The clustered index is very important, and you should
always have indexes on columns that are used in joins.

> There are a lot of rather stringent rules on what can and cannot be done
> on a linked server. One of those requirements is that every table on the
> linked server that you attempt to update or delete rows from must have a
> primary key constraint. Declaring an identity column is not sufficient;
> you must have an honest-to-goodness primary key constraint. That's the
> usual cause of this rather generic error. Unless you're still using
> version 7.0, in which case the brackets are missing when the query is
> submitted to the linked server. It was fixed in 2000.

Aha! That would be it. The catch:

We haven't been using primary key restraints on the public facing DB as it's
merely a copy of the internal DB (which does have primary key restraints).
When we copy data from internal to external, we wanted to make sure the
uniqueID set automatically by the internal PK restraint got copied as is.

Is the 'fix' for this to just add a new column to the public facing tables
with a PK? Better method?

-Darrel

There are a lot of rather stringent rules on what can and cannot be done on
a linked server. One of those requirements is that every table on the
linked server that you attempt to update or delete rows from must have a
primary key constraint. Declaring an identity column is not sufficient; you
must have an honest-to-goodness primary key constraint. That's the usual
cause of this rather generic error. Unless you're still using version 7.0,
in which case the brackets are missing when the query is submitted to the
linked server. It was fixed in 2000.



"darrel" <notreal@nowhere.com> wrote in message
news:etcic5$7jr$1@forums.macromedia.com...
> Thanks to lionstone, I think I'm getting the hang of complex DELETE
> queries.
>
> I'm stuck on this one though:
>
> DELETE faqvars
> FROM
> [SQLCRTPUB\COURTSPUBLIC].DBCourtsPub.CourtsPub.WeFAQsVariations faqvars
> WHERE EXISTS
> (SELECT *
> FROM
> [SQLCRTPUB\COURTSPUBLIC].DBCourtsPub.CourtsPub.WeFAQs faqs INNER JOIN
>
> [SQLCRTPUB\COURTSPUBLIC].DBCourtsPub.CourtsPub.We_about_categories cats ON
> faqs.categoryID = cats.categoryID
> WHERE (faqvars.collectionID =
> faqs.collectionID) AND (faqs.categoryID = 9 OR
> cats.categoryParentID =
> 9))
>
> That gives me this error:
>
> ---------------------------
> SQL Server Enterprise Manager
> ---------------------------
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: Multiple-step OLE DB operation generated errors. Check each OLE
> DB status value, if available. No work was done.]
> ---------------------------
>
>
> This seems to be a bit of a random error. So, two questions: 1) anyone see
> anything particularily wrong with my syntax above and 2) how do I go about
> debugging a 'multiple-step OLE DB' operation in SQL Manager?
>
> -Darrel
>