Хранимая процедура SQL Server проверяет, существует ли таблица в другой базе данных, и переименовывает ее.

Есть 2 базы данных: MAIN и IP2LOCATION

в MAIN у меня есть следующая хранимая процедура:

CREATE PROCEDURE dbo.Update_IP2Location_DB11_from_CSV
AS
BEGIN
    IF  NOT EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[ip2location].[dbo].[db11_new]') AND type in (N'U'))
        BEGIN
            CREATE TABLE [ip2location].[dbo].[db11_new]
            (
                [ip_from]       bigint          NOT NULL,
                [ip_to]         bigint          NOT NULL,
                [country_code]  nvarchar(2)     NOT NULL,
                [country_name]  nvarchar(64)    NOT NULL,
                [region_name]   nvarchar(128)   NOT NULL,
                [city_name]     nvarchar(128)   NOT NULL,
                [latitude]      float           NOT NULL,
                [longitude]     float           NOT NULL,
                [zip_code]      nvarchar(30)    NOT NULL,
                [time_zone]     nvarchar(8)     NOT NULL,
            ) ON [PRIMARY]

            CREATE INDEX [ip_from] ON [ip2location].[dbo].[db11_new]([ip_from])
        END
    ELSE
        BEGIN
            DELETE FROM [ip2location].[dbo].[db11_new]
        END

    BULK INSERT [ip2location].[dbo].[db11_new]
        FROM 'D:\IP2LOCATION-LITE-DB11.CSV'
        WITH
        ( FORMATFILE = 'C:\inetpub\wwwroot\ws\DB11_ip4.FMT')

    EXEC sp_rename N'dbo.db11', N'db11_old', 'OBJECT'
    EXEC sp_rename N'ip2location.dbo.db11_new', N'db11', 'OBJECT'   
END

это не работает должным образом:

если db11_new не существует, он (правильно) создает его, но если он существует.. Я получаю

В базе данных уже есть объект с именем 'db11_new'.

поэтому, кажется, что-то не так в

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[ip2location].[dbo].[db11_new]') AND type in (N'U'))

а также в конце процедуры с 2 Переименовать я получаю (всегда) следующий ответ

Сообщение 15248, уровень 11, состояние 1, процедура sp_rename, строка 359. Либо параметр @objname является неоднозначным, либо заявленный @objtype (OBJECT) неверен.

кажется, проблема в том, что sproc хранится не в базе данных ip2location, а в другой базе данных.

Можете предложить решение, учитывая, что я предпочел бы сохранить все sprocs в основной базе данных, так как есть все остальные?

Спасибо

Источник

4 ответа

Решение
therefore it seems there is something wrong in
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[ip2location].[dbo].[db11_new]') AND type in (N'U'))

Ваш анализ верен. Представление каталога sys.objects будет возвращать объекты в текущем контексте базы данных (MAIN). Хотя вы можете просто использовать имя из 3 частей (ip2location.sys.objects), Я предлагаю вам просто проверить NULLOBJECT_ID Результат функции:

IF  OBJECT_ID(N'[ip2location].[dbo].[db11_new]', 'U') IS NULL
        BEGIN
            CREATE TABLE [ip2location].[dbo].[db11_new]
            (
                [ip_from]       bigint          NOT NULL,
                [ip_to]         bigint          NOT NULL,
                [country_code]  nvarchar(2)     NOT NULL,
                [country_name]  nvarchar(64)    NOT NULL,
                [region_name]   nvarchar(128)   NOT NULL,
                [city_name]     nvarchar(128)   NOT NULL,
                [latitude]      float           NOT NULL,
                [longitude]     float           NOT NULL,
                [zip_code]      nvarchar(30)    NOT NULL,
                [time_zone]     nvarchar(8)     NOT NULL,
            ) ON [PRIMARY];

            CREATE INDEX [ip_from] ON [ip2location].[dbo].[db11_new]([ip_from]);
        END;
    ELSE
        BEGIN
            DELETE FROM [ip2location].[dbo].[db11_new];
        END;
Источник

sys.objects и sp_rename являются локальными объектами. Попробуйте использовать это:

IF  NOT EXISTS (SELECT * FROM ip2location.sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[db11_new]') AND type in (N'U'))

а также

    EXEC ip2location.sp_rename N'dbo.db11_new', N'db11', 'OBJECT'

Может быть, это помогает...

В качестве альтернативы, когда вы хотите сделать что-то в другой базе данных, чем текущая, вы можете написать свой код в динамическом sql, а затем выполнить его непосредственно в другой базе данных.

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Источник

Благодаря Ребуну и Дану Гузману здесь решение немного улучшилось:

CREATE PROCEDURE dbo.spA_Update_IP2Location_DB11_from_CSV
AS
BEGIN

IF  OBJECT_ID(N'[ip2location].[dbo].[db11_new]', 'U') IS NULL
    BEGIN

        CREATE TABLE [ip2location].[dbo].[db11_new](
            [ip_from] bigint NOT NULL,
            [ip_to] bigint NOT NULL,
            [country_code] nvarchar(2) NOT NULL,
            [country_name] nvarchar(64) NOT NULL,
            [region_name] nvarchar(128) NOT NULL,
            [city_name] nvarchar(128) NOT NULL,
            [latitude] float NOT NULL,
            [longitude] float NOT NULL,
            [zip_code] nvarchar(30) NOT NULL,
            [time_zone] nvarchar(8) NOT NULL,
        ) ON [PRIMARY]

        CREATE INDEX [ip_from] ON [ip2location].[dbo].[db11_new]([ip_from]) ON [PRIMARY]

    END
ELSE
    BEGIN
        delete from [ip2location].[dbo].[db11_new]
    END

BULK INSERT [ip2location].[dbo].[db11_new]
    FROM 'D:\IP2LOCATION-LITE-DB11.CSV'
    WITH
    ( FORMATFILE = 'C:\inetpub\wwwroot\ws\DB11_ip4.FMT' )

BEGIN TRANSACTION
    EXEC ip2location.dbo.sp_rename N'dbo.db11', N'db11_old'
    EXEC ip2location.dbo.sp_rename N'dbo.db11_new', N'db11'
    IF  OBJECT_ID(N'[ip2location].[dbo].[db11_old]', 'U') IS NOT NULL
        BEGIN
            DROP  TABLE ip2location.dbo.db11_old
        END
COMMIT TRANSACTION  
END
Источник

Я проверил этот запрос (без загрузки CSV)

Сначала я удаляю все ссылки на ip2location:

CREATE PROCEDURE dbo.Update_IP2Location_DB11_from_CSV
AS
BEGIN
IF  NOT EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'dbo.db11_new') AND type in (N'U'))
        BEGIN
            CREATE TABLE [dbo].[db11_new]
            (
                [ip_from]       bigint          NOT NULL,
                [ip_to]         bigint          NOT NULL,
                [country_code]  nvarchar(2)     NOT NULL,
                [country_name]  nvarchar(64)    NOT NULL,
                [region_name]   nvarchar(128)   NOT NULL,
                [city_name]     nvarchar(128)   NOT NULL,
                [latitude]      float           NOT NULL,
                [longitude]     float           NOT NULL,
                [zip_code]      nvarchar(30)    NOT NULL,
                [time_zone]     nvarchar(8)     NOT NULL,
            ) ON [PRIMARY]

            CREATE INDEX [ip_from] ON [dbo].[db11_new]([ip_from])
        END
    ELSE
        BEGIN
            DELETE FROM [dbo].[db11_new]
        END

    BULK INSERT [dbo].[db11_new]
        FROM 'D:\IP2LOCATION-LITE-DB11.CSV'
        WITH
        ( FORMATFILE = 'C:\inetpub\wwwroot\ws\DB11_ip4.FMT')

    EXEC sp_rename N'dbo.db11', N'db11_old', 'OBJECT'
    EXEC sp_rename N'dbo.db11_new', N'db11', 'OBJECT'   
END
GO

Первый забег:

У меня нет db11* столы. Казнь приносит мне:

Сообщение 15248, Уровень 11, Состояние 1, Процедура sp_rename, Строка 401 [Строка пакетного запуска 2] Либо параметр @objname является неоднозначным, либо заявленный @objtype (OBJECT) неверен. Внимание! Изменение любой части имени объекта может привести к поломке скриптов и хранимых процедур.

Это означает, что db11_new был создан, а затем переименован в db11, но db11_old не был найден, поэтому я получил эту ошибку. я получил db11 таблица в моей БД.

Второй прогон:

Внимание! Изменение любой части имени объекта может привести к поломке скриптов и хранимых процедур. Внимание! Изменение любой части имени объекта может привести к поломке скриптов и хранимых процедур.

Это означает, что все было создано и переименовано.

Третий прогон:

Сообщение 15335, уровень 11, состояние 1, процедура sp_rename, строка 509 [Строка пакетного запуска 2] Ошибка: новое имя 'db11_old' уже используется в качестве имени ОБЪЕКТА и приведет к дублированию, которое не разрешено. Сообщение 15335, уровень 11, состояние 1, процедура sp_rename, строка 509 [Строка пакетного запуска 2] Ошибка: новое имя 'db11' уже используется в качестве имени ОБЪЕКТА и приведет к дублированию, которое недопустимо.

Так что при каждом следующем повторном запуске вы будете получать такие же ошибки.

Я предлагаю сделать что-то db11_old,

Источник
Другие вопросы по тегам