Hello.
I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.
Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!
--
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure aes.Check_Dependent_Rows_Exist
(@.RowID int,
@.has_rows int OUTPUT
)
AS
BEGIN
DECLARE @.Colname varchar(200), @.Tablename varchar(200)
DECLARE @.cnt int
DECLARE @.temp_row int
DECLARE @.owner varchar(25)
DECLARE @.ownerid int
DECLARE @.lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR
SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'
OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not
SET @.has_rows = 0
FETCH NEXT FROM lcur INTO @.Colname,@.Tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.temp_row = 0
SELECT @.ownerid = uid from sysobjects where name = @.Tablename
SELECT @.owner = [name] from sysusers where uid = @.ownerid
SET @.lstrSql= 'insert into #Temp Select DependentRows = Count(' + @.Colname + ') from ' + @.owner + '.' + @.TableName + ' where ' +
@.Colname + ' =' + CAST(@.RowID AS VARCHAR(16)) + ''
--print @.lstrSql
EXEC (@.lstrSql)
SELECT @.temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @.temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @.has_rows = 1
BREAK
END
FETCH NEXT FROM lcur INTO @.Colname,@.TableName
END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--
error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.
(1 row(s) affected)
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
The error messages that you posted doesn't seem to have any relation to the SP code that you listed. The 208 messages are obvious in the sense that the code is executing either on invalid database or with wrong input. It will be good if you post a simpler repro that we can run in SQL2000 and compare against SQL2005.|||maybe you are running into trouble with the user-schema separation|||--maybe you are running into trouble with the user-schema separation
Yes, I think you are right. Not sure how to adjust for it
|||SQL Server 2005 severs the implicit connection between database users and schemas.
What is a schema?
A schema is a collection of database entities that form a single namespace. A namespace is a set in which every element has a unique name.
For example, to avoid name collisions, no two tables in the same schema can have the same name. Two tables can have the same name only if they are in separate schemas.
Note:
In discussions of database tools, "schema" also refers to the catalog information that describes the objects in a schema or database. In discussions of Analysis Services, "schema" refers to multidimensional objects such as cubes and dimensions.
From SQL Server 2000 to SQL Server 2005
Although SQL Server 2000 has a CREATE SCHEMA statement, it does not actually create schemas in the sense defined above. In SQL Server 2000, database users and schemas are implicitly connected. Every database user is the owner of a schema that has the same name as the user. And an object's owner is effectively identical to the owner of the schema that contains it. Thus, the "schema" of a fully-qualified name in SQL Server 2000 is also a user in the database. For this reason, prior to dropping a user from a SQL Server 2000 database, the administrator needed to drop all the objects owned by that user or change their owner. Consider a SQL Server 2000 database containing this object:
accounting.ap.george.reconciliation
This object is owned by user "george". If the administrator needs to delete user "george", she must first delete this object or change its owner. In the latter case, it might be renamed as follows:
accounting.ap.sandra.reconciliation
Transferring ownership of the object also changes its fully-qualified name. Any code that refers to accounting.ap.george.reconciliation must be updated to reflect the name change.
In SQL Server 2005, schemas exist independently of the database user that creates them. Ownership of schemas can be transferred without changing their names. And objects can be created in schemas with user-friendly names that clearly indicate their function. For example, rather than accounting.ap.sandra.reconciliation you can create a schema called accounting.ap.invoice.reconciliation. This name need not be changed when a user is dropped from the database, because "invoice" is not a user. This simplifies the work of database administrators and developers.
SQL Server 2005 also introduces the notion of "default schema", which is used to resolve the names of objects that are referred to without their fully-qualified names. In SQL Server 2000, the location first checked is the schema owned by the calling database user, followed by the schema owned by DBO. In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have DBO as its default schema.
|||Thank you for the replies. They have been helpful. The tables referenced in the error are definitely not owned by dbo. I need to figure out how to pull the correct owner. I am working on it.Any suggestions are appreciated.
No comments:
Post a Comment