B
B
Boris the Animal2017-02-01 14:12:36
SQL
Boris the Animal, 2017-02-01 14:12:36

Execute DML query after DDL. How to check it?

Error text:

Msg 2760, Level 16, State 1, Line 588
The specified schema name "copy" either does not exist or you do not have permission to use it.
Msg 208, Level 16, State 1, Line 607
Invalid object name 'copy.REPORTS'.
Msg 15151, Level 16, State 1, Line 664
Cannot find the object 'REPORTS', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 665
Cannot drop the schema 'copy', because it does not exist or you do not have permission.

Patches are run from the command line using " sqlcmd "
What is the point: Create a "copy" schema, create a table that is already in the database, but in the dbo schema. Further from dbo.REPORTS we receive the data, we modify (there removal of duplicates with addition of some values). We place the modified data in copy.REPORTS. Next, we delete dbo.REPORTS and change the schema of copy.REPORTS to dbo.
The advantage of schema replacement is that you don't need to rename anything when you need to create the same table, no restrictions, nothing. It's comfortable.
When executing this query, when it is launched from the command line (this is part of the patch to the database), then after creating the schema, an error occurs further that there is no such schema or there are no rights to create it. How to fix it? The script is run under Service administrator (sa).
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'copy')
  EXECUTE( N'CREATE SCHEMA copy')
GO

IF OBJECT_ID('copy.REPORTS', 'U') IS NOT NULL
  DROP TABLE copy.REPORTS;
GO

-- Таблица реальная немного другая, это от фонаря
CREATE TABLE copy.REPORTS (
  REPORT_DATE DATE NOT NULL
 ,REPORT_ID INT NOT NULL
 ,CONSTRAINT PK_REPORTS PRIMARY KEY CLUSTERED (REPORT_DATE, REPORT_ID)
) ON [PRIMARY]
GO

-- ЗАПРОС МОДИФИЦИРУЮЩИЙ ДАННЫЕ ИЗ dbo.REPORTS и помещающий их в copy.REPORTS

DROP TABLE dbo.REPORTS;  
GO

ALTER SCHEMA dbo TRANSFER copy.REPORTS
GO

EXECUTE( N'DROP SCHEMA copy');

Answer the question

In order to leave comments, you need to log in

1 answer(s)
B
Boris the Animal, 2017-02-01
@Casper-SC

It turned out that the problem was that all of the above code was executed after changing the stored procedure (ALTER PROCEDURE), and there was no GO statement after the code for this change itself. That's the whole problem, and so the code is working.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question