We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

Error dropping shema

4,256 Views
Last Modified: 2010-04-21
Attempting to drop shema then user via MSSQL 2005 Server Mngr but get the following error.
I do know this user account is not logged in when this error occurs.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot drop schema 'EPIDIRECT\fmg.Jackie.Infusino' because it is being referenced by object 'ds_GetServerCase'.
(Microsoft SQL Server, Error: 3729)

Any suggestion how to determine dependency causing this problem and how to correct?
Comment
Watch Question

Faiga DiegelSr Database Engineer
CERTIFIED EXPERT

Commented:
Look for this object: EPIDIRECT\fmg.Jackie.Infusino.ds_GetServerCase. This maybe an SP or table or view or function. Change that object to another owner -- dbo perhaps, then try droppign the schema again.
Right click the schema and thru Studio Management and click View Dependecies.

Author

Commented:
ds_GetServerCase is owned by dbo with no dependencies

Right clicking on this schema does not show 'View Dependencies' as an option.

schema owner is dbo
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ran this script, then ran following script. All dependences seem to still exist. Attempted schema delete with same error

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + p.Name FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name <> 'dbo'

SELECT s.Name + '.' + p.Name, * FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id
WHERE s.Name <> 'dbo'

Not sure if could be relevant but each of the users are also members of a group with dbo priviledges.
Execute the scripts that the previus query will generate. So that all the procedures will change the schema to dbo.

Author

Commented:
I did run these scripts and attempted to delete the schema but continued to receive the dependency error.
However I did find where an application was creating a stored procedure for each login account. Was able to delete these procedures and then successfully remove the schemas in question.

Given this suggestion helped me to find problem but not convinced it fixed I am awarding partial points.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.