SQL Server 2017 — хранимая процедура почты базы данных

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

  1. Создайте временную таблицу (если она не существует) и заполните ее данными
  2. Вывод запроса в SSMS и присвоение запросу переменной (@sql)
  3. Используя запрос, отправьте содержимое запроса по электронной почте получателям.

Мой сценарий таков:

Create Procedure ListDaysofYear(@year as integer)
as 
    Declare @sql as varchar(200), @DBqry as varchar(200),
            @tab as char(1) = char(9)
    Declare @dayofyear as bigint = 1
    Declare @monthofyear as int = 1
    Declare @day as int = 1
    Declare @curDate as datetime
    Declare @DB as varchar(40)
    Declare @sql2 as varchar(40)

    Set @curDate = datefromparts(@year, @monthofyear, @day)
    Set @DB = 'msdb'

    IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
        DROP TABLE ##daysofYear
        --Print 'YES'
    ELSE
        CREATE TABLE ##daysofYear
        (
             cDate DATETIME PRIMARY KEY NOT NULL,
             cMonth VARCHAR(20) NOT NULL,
             cDay VARCHAR(20) NOT NULL
        )

    WHILE year(@curDate) = @year
    BEGIN
        -- Insert rows based on each day of the year
        INSERT INTO ##daysofYear (cDate, cMonth, cDay)
        VALUES( (@curDate),
                (DATENAME([MONTH], @curDate)),
                (DATENAME([WEEKDAY], @curDate)) )

        SET @curDate = @curDate + 1
    END 

    --Output file to SSMS query window
    Select dy.* from ##daysofYear dy;

    Set @sql = 'Select dy.* from ##daysofYear dy;'
    Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
             @profile_name = ''Notifications'',
             @recipients = ''[email protected]'',
             @attach_query_result_as_file = 1,
             @query_attachment_filename = ''daysofyear.txt'',
             @query_result_separator = '',
             @body = ''The attached output file - DaysofYear table'',
             @query = ''Select dy.* from ##daysofYear dy'' ;'

    --Execute sp_sqlexec @sql
    Exec(@sql2)

В основном, когда я запускаю строку выполнения:

Exec dbo.ListDaysofYear 2018 ;

Я получаю следующее сообщение в первый раз:

Сообщение 208, уровень 16, состояние 0, процедура dbo.ListDaysofYear, строка 25
[строка запуска пакета 52] Недопустимое имя объекта '##daysofYear

Я полагаю, что это связано с частью T-SQL «DROP TABLE».

Спасибо


sql-server-2017 ssms-2017 drop-table sp-send-dbmail
person Mike Mirabelli    schedule 20.11.2018    source источник
comment
Я заметил, что когда я пытаюсь написать что-нибудь простое в параметре @query для отправки почты SP, я получаю это сообщение, иначе эта вторая ошибка НИКОГДА не срабатывает. SOmething всякий раз, когда я пишу простой выбор (Ex Select 1), всегда возвращает это сообщение.   -  person Mike Mirabelli    schedule 21.11.2018
comment
Я не думаю, что временные таблицы доступны через динамический SQL. Разве вы не можете использовать настоящую таблицу и очистить их в конце?   -  person sheeni    schedule 21.11.2018
comment
Я думал об этом, но когда я попытался использовать обычную таблицу, тот же результат - IF не срабатывает, как предполагалось, в запросе. Это странно, когда я делаю следующее: 'ЕСЛИ OBJECT_ID('tempdb.dbo.##daysofYear','U') НЕ NULL Печатает 'Существует' ELSE Печатает 'Не существует' IF OBJECT_ID('tempdb.dbo.## daysofYear','U') НЕ NULL Удаление таблицы ##daysofYear ELSE Создать таблицу ##daysofYear (id# int primary key); ' И я хожу туда и обратно, это работает, как и ожидалось.   -  person Mike Mirabelli    schedule 21.11.2018
comment
Попробуйте, ЕСЛИ OBJECT_ID('tempdb..##daysofYear') НЕ NULL   -  person sheeni    schedule 21.11.2018
comment
Это нормальный синтаксис для глобальных таблиц? Вы не против пояснить разницу? Я могу попробовать это, когда снова войду в систему и дам вам знать, спасибо @sheeni   -  person Mike Mirabelli    schedule 21.11.2018
comment
NVM, думаю, я нашел проблему, ЕСЛИ OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL ‹-- здесь вы удаляете таблицу, если существует, но не создаете ее, поэтому она выдает ошибку в строке 25, где он пытается вставить данные (в таблицу, которую вы удалили). я предлагаю заменить drop table на TRUNCATE TABLE. Дайте мне знать, если это сработает, я добавлю это как ответ   -  person sheeni    schedule 21.11.2018
comment
Очищает ли усеченная таблица содержимое таблицы, но сохраняет структуру?   -  person Mike Mirabelli    schedule 21.11.2018
comment
Да, просто очистите данные и сохраните структуру   -  person sheeni    schedule 21.11.2018
comment
Большой. Имеет смысл, я это реализую. Я предполагаю, что альтернативой было бы перемещение вставки после создания, хотя я думаю, что если операторы, в отличие от других языков, обрабатывают только 1 команду, верно?   -  person Mike Mirabelli    schedule 21.11.2018
comment
у вас может быть несколько операторов, если вы добавите BEGIN и END под вашими if и else. У вас может быть несколько строк кода между BEGIN и END   -  person sheeni    schedule 21.11.2018
comment
Отличный ти. @шини   -  person Mike Mirabelli    schedule 21.11.2018

Ответы (1)


arrow_upward
1
arrow_downward

Думаю, я нашел проблему:

IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL ‹-- здесь вы удаляете таблицу, если она существует, но не создаете ее, поэтому она выдает ошибку в строке 25, где она пытается вставить данные (в таблицу, которую вы удалили). я предлагаю заменить drop table на TRUNCATE TABLE.

person sheeni    schedule 21.11.2018