mercredi 23 septembre 2015

Default schema for DROP and SELECT INTO in stored procedures

I am a bit confused as to how the default database schema is determined in MS SQL server.

I have the following stored procedure as a minimal working example:

CREATE PROCEDURE [dbo].[SampleSP]
AS

SELECT 'HI' as [SampleColumn]
INTO [SampleTable]

DROP TABLE [SampleTable]

All tests are executed using a user User on MS SQL server using Windows Auth with a default database schema of the same name.

  • When I execute this SP on MS SQL Server 2005 installation (running in compatibility mode 80, i.e. Server 2000) the table is created as [User].[SampleTable] and DROP TABLE fails with Invalid object name 'SampleTable' (I assume because it looks for [dbo].[SampleTable])
  • When I DROP TABLE [SampleTable] in a separate query it does work
  • When I execute the SP on MS SQL Server 2008 R2 (also running in compat. 80) the table is created as [dbo].[SampleTable] and dropped without error

I have found this answer describing the lookup in stored procedures, but it doesn't mention the user default in that context, even though it is used on 2005. Maybe someone knows how this changed and whether new versions can be configured to behave the same way.

Aucun commentaire:

Enregistrer un commentaire