Answer the question
In order to leave comments, you need to log in
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.
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
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 questionAsk a Question
731 491 924 answers to any question