Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Обнаружил такую особенность
ALTER PROCEDURE _Test_
AS
BEGIN
	SELECT tg.id_Goods, tg.id_metrik FROM T_Goods tg

END

Вызовет ошибку:
Msg 207, Level 16, State 1, Procedure _asdasdasd_, Line 4
Invalid column name 'id_metrik'.

ALTER PROCEDURE _Test_
AS
BEGIN
	SELECT tg.id_Goods, tg.id_metrik FROM T_Goods tg
	LEFT JOIN SQLInterface.dbo.ReceiptGoods rg
          ON rrg.Id_ExportIntf = rg.Id_ReceiptGoods
END

Не вызовет ошибку
Command(s) completed successfully.

БД SQLInterface на сервере не существует. Как заставить вызвать ошибку? мало того, что нет поля в T_Goods, так и ON rrg.Id_ExportIntf = rg.Id_ReceiptGoods ссылается на неизвестный alias rrg
16 июл 13, 17:03    [14574330]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
ALTER PROC
Guest
X-Cite
Обнаружил такую особенность
ALTER PROCEDURE _Test_
AS
BEGIN
	SELECT tg.id_Goods, tg.id_metrik FROM T_Goods tg

END

Вызовет ошибку:
Msg 207, Level 16, State 1, Procedure _asdasdasd_, Line 4
Invalid column name 'id_metrik'.

ALTER PROCEDURE _Test_
AS
BEGIN
	SELECT tg.id_Goods, tg.id_metrik FROM T_Goods tg
	LEFT JOIN SQLInterface.dbo.ReceiptGoods rg
          ON rrg.Id_ExportIntf = rg.Id_ReceiptGoods
END

Не вызовет ошибку
Command(s) completed successfully.

БД SQLInterface на сервере не существует. Как заставить вызвать ошибку? мало того, что нет поля в T_Goods, так и ON rrg.Id_ExportIntf = rg.Id_ReceiptGoods ссылается на неизвестный alias rrg

если кратко - никак

http://msdn.microsoft.com/ru-ru/library/ms190686(v=sql.105).aspx
16 июл 13, 17:11    [14574378]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Гость333
Member

Откуда:
Сообщений: 3683
X-Cite,

Этот механизм называется "отложенное разрешение имён".
BOL -> Deferred Name Resolution and Compilation
Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

В первом случае все объекты существуют и могут быть использованы в compile-time, и компиляция ХП завершается с ошибкой. Во втором случае вы ссылаетесь на несуществующий объект, поэтому ХП компилируется в надежде на то, что к моменту выполнения (в run-time) нужные объекты уже будут существовать.
16 июл 13, 17:11    [14574382]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
ЕвгенийВ
Member

Откуда: Москва
Сообщений: 4994
X-Cite,
Первый запрос вообще ругается на процедуру _asdasdasd_!
16 июл 13, 17:13    [14574392]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Гость333
Во втором случае вы ссылаетесь на несуществующий объект, поэтому ХП компилируется в надежде на то, что к моменту выполнения (в run-time) нужные объекты уже будут существовать.


Не совсем так. Поведение явно противоречит документации в части отсутствующей колонки в T_Goods:

BOL
Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.


Можно предположить, что сервер не найдя хотя бы одну таблицу, забивает на проверку наличия полей для всех таблиц в запросе.
16 июл 13, 17:15    [14574409]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Гость333
Member

Откуда:
Сообщений: 3683
pkarklin
Можно предположить, что сервер не найдя хотя бы одну таблицу, забивает на проверку наличия полей для всех таблиц в запросе.

Да, всё правильно. Я даже как-то не задумывался, что в BOL здесь некорректные формулировки :-)
16 июл 13, 17:24    [14574472]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
ЕвгенийВ
X-Cite,
Первый запрос вообще ругается на процедуру _asdasdasd_!

У себя я процедуру назвал так, для форума красивее назвать Test :)
Всем спасибо, жаль. Я думал это будет отличный способ проверить на наличие корректности процедуры.

Если кратко то:
1) Есть 1000 процедур
2) в одной из таблиц удаляется поле (допустим id_metrik)
3) в других таблицах также есть поле с таким названием, они остаются
4) Мне надо найти все процедуры и исправить их

Варианты решения:
1)
  SELECT
    o.name AS 'Имя объекта',
    o.[type] AS 'Тип объекта'
  FROM
    sys.all_objects o
    JOIN sys.schemas u
      ON u.schema_id = o.schema_id
    JOIN sys.all_sql_modules c
      ON c.object_id = o.object_id
  WHERE
    u.name = N'dbo' AND
    (c.definition like '%MyTable%id_metrik%' OR
     c.definition like '%id_metrik%MyTable%')
  ORDER BY
  	o.[type],
  	o.name

И просмотреть все процедуры на наличие удаляемого поля
2)
DECLARE
	@ObjectId INT,
	@Defination NVARCHAR(MAX),
	@SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL FOR
  SELECT
    p.[object_id],
    sm.definition
  FROM
    sys.sql_modules sm
    JOIN sys.procedures p
      ON p.[object_id] = sm.[object_id]
  WHERE
    p.is_ms_shipped = 0
  ORDER BY
    OBJECT_NAME(p.[object_id])

OPEN cur

FETCH FROM cur INTO @ObjectId, @Defination

WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		
	  SET @SQL = REPLACE(@Defination, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
	  SET @SQL = REPLACE(@SQL, 'CREATE  PROCEDURE', 'ALTER PROCEDURE')
	  SET @SQL = REPLACE(@SQL, 'CREATE   PROCEDURE', 'ALTER PROCEDURE')
	  SET @SQL = REPLACE(@SQL, 'CREATE    PROCEDURE', 'ALTER PROCEDURE')
	  
    EXEC (@SQL)
    
  END TRY
	BEGIN CATCH
	
	  PRINT ''
	  PRINT OBJECT_NAME(@ObjectId)
	  PRINT ERROR_MESSAGE()	
		PRINT ''
		
	END CATCH
	 
	FETCH FROM cur INTO @ObjectId, @Defination
	
END

CLOSE cur
DEALLOCATE cur


Вариант 2 мне импонирует больше, т.к. не будет лишних процедур, т.к. в 1 случае он может найти процедуру с полем из другой таблицы, но с соединением нашей.
16 июл 13, 17:29    [14574498]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Вот засада в том, что процедура из случая 2 компилируется, хотя имеет удаленное поле.
16 июл 13, 17:31    [14574518]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
X-Cite,

автор
И просмотреть все процедуры на наличие удаляемого поля


Смотрите. BOL->sys.dm_sql_referenced_entities
16 июл 13, 17:54    [14574686]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
X-Cite,

могу такое предложить (непроверенное!!!):
select object_schema_name ( referencing_id ) as referencing_schema_name,
       object_name(referencing_id) as referencing_entity_name 
from sys.sql_expression_dependencies as sed
where referenced_id = object_id('your_table')
      and object_definition(referencing_id) like '%your_column%';


можно наверное еще sys.objects прикрутить,
чтоб фильтровать именно процедуры
16 июл 13, 18:13    [14574805]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
use tempdb;
go

create table dbo.TestTable (i int, y int);
go

create procedure dbo.spTestProc1
as
begin
 select
  t.i, t.y
 from
  dbo.TestTable t
end;
go

create procedure dbo.spTestProc2
as
begin
 select
  t.i
 from
  dbo.TestTable t
end;
go

select
 quotename(referencing_schema_name) + '.' + quotename(referencing_entity_name),
 b.*
from
 sys.dm_sql_referencing_entities('dbo.TestTable', 'object') a outer apply
 sys.dm_sql_referenced_entities(quotename(referencing_schema_name) + '.' + quotename(referencing_entity_name), 'object') b;

alter table dbo.TestTable drop column y;
go

select
 quotename(referencing_schema_name) + '.' + quotename(referencing_entity_name),
 b.*
from
 sys.dm_sql_referencing_entities('dbo.TestTable', 'object') a outer apply
 sys.dm_sql_referenced_entities(quotename(referencing_schema_name) + '.' + quotename(referencing_entity_name), 'object') b;
go

drop procedure dbo.spTestProc1, dbo.spTestProc2;
drop table dbo.TestTable;
go
16 июл 13, 18:29    [14574907]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Yasha123
X-Cite,

могу такое предложить (непроверенное!!!):
select object_schema_name ( referencing_id ) as referencing_schema_name,
       object_name(referencing_id) as referencing_entity_name 
from sys.sql_expression_dependencies as sed
where referenced_id = object_id('your_table')
      and object_definition(referencing_id) like '%your_column%';


можно наверное еще sys.objects прикрутить,
чтоб фильтровать именно процедуры

Ну там не только процедуры.. Как процедуры это я пример привел. Еще же функции и синонимы надо и представления.
to invm
Спасибо, я первый раз не обратил внимания и использовал dm_sql_referenced_entities и думаю чего ничего не показывает, а их оказывает две.
17 июл 13, 10:15    [14576464]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
invm,
К сожалению не работает для случая "отложенное разрешение имён"

create procedure dbo.spTestProc1
as
begin
 select
  t.i, t.y
 from
  dbo.TestTable t
  JOIN #ttt t
    ON t.id = 1
end;
go

create procedure dbo.spTestProc2
as
begin
 select
  t.i
 from
  dbo.TestTable t
  JOIN #ttt t
    ON t.id = 1
end;
go
17 июл 13, 11:53    [14577138]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Я вот только одного из всего етого топика не понял - вам шашечки или ехать то ?
17 июл 13, 12:03    [14577216]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Мне найти удобный способ определения всех процедур (функций, синонимов, представлений) на предмет использования удаленного поля из таблицы.
17 июл 13, 12:11    [14577266]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
X-Cite,

...а мой вариант чем не подошел?
(ну кроме как с таким предикатом like '%your_column%' можно лишнего огрести,
+ не отловит динамику)
17 июл 13, 12:14    [14577286]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
X-Cite,

переходите на нормальное ведение проекта и хранение исходных кодов БД в какой нить нормальной системе контроля версионости кода. Сразу отпадут подобные лисапеды и еже с ними
17 июл 13, 12:20    [14577321]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Yasha123
X-Cite,

...а мой вариант чем не подошел?
(ну кроме как с таким предикатом like '%your_column%' можно лишнего огрести,
+ не отловит динамику)

потому что он аналогичен поиску через sys.all_sql_modules и выдает много не нужных процедур, в которых есть наша таблица и такое же поле, но другой таблицы.
Maxx,
Да, хотелось был, но кто ж позволит... :(

Как минимальный вариант регламентировать использование жестких алиасов закрепленных за таблицей и искать через них (tgr.id_metrik) и т.п.
17 июл 13, 12:31    [14577389]     Ответить | Цитировать Сообщить модератору
 Re: Компиляция процедуры  [new]
Yasha123
Member

Откуда:
Сообщений: 1973
X-Cite,
посмотреть в сторону sqlcodeguard? -> Object dependencies

http://www.sqlcodeguard.com/
17 июл 13, 12:39    [14577452]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить