Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ХП во временную таблицу  [new]
А
Guest
Кто скажет, как затолкать ХП во временную таблицу, если она возвращает переменное кол-во столбцов? Какое нибудь выражение типа exec .. into.. (SQL 2005)
17 дек 12, 07:01    [13640669]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
SELECT ... INTO 
...FROM OPENROWSET('SQLNCLI', 'Server=Server;Trusted_Connection=yes;',
 'exec spName') AS a;
17 дек 12, 07:33    [13640679]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
А
Guest
У меня при запуске этого запроса из-под sa выдается следующая ошибка:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Сообщение 53, уровень 16, состояние 1, строка 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Чего ему не хватает?
17 дек 12, 08:41    [13640751]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
Гость333
Member

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

Приведите здесь текст запроса, который вы запускаете. А также результат запроса:
select name from sys.servers where server_id = 0
17 дек 12, 09:56    [13640932]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
А
Guest
Немножко поковырялся, ошибка изменилась, суть нет. Пытаюсь выполнить это
SELECT * INTO #res FROM OPENROWSET('SQLNCLI', 'Server=1cserv;Trusted_Connection=yes;',
 'exec spBudgetData ''20110101'', 14, 13') AS a

select * from #res


Сейчас получаю

Сообщение 7357, уровень 16, состояние 2, строка 1
Cannot process the object "exec spBudgetData '20110101', 14, 13". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

А
select name from sys.servers where server_id = 0
нормально выдает 1cserv.
17 дек 12, 10:39    [13641127]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
Гость333
Member

Откуда:
Сообщений: 3683
А
ошибка изменилась, суть нет.

Суть очень даже изменилась. Если раньше было прописано неправильное имя сервера или сервер не был настроен, и из-за этого не происходило подключения, то теперь подключение происходит.

А
Пытаюсь выполнить это
SELECT * INTO #res FROM OPENROWSET('SQLNCLI', 'Server=1cserv;Trusted_Connection=yes;',
 'exec spBudgetData ''20110101'', 14, 13') AS a

А теперь попытайтесь выполнить это:
SELECT * INTO #res FROM OPENROWSET('SQLNCLI', 'Server=1cserv;Trusted_Connection=yes;',
 'exec Имя_Базы.Имя_Схемы.spBudgetData ''20110101'', 14, 13') AS a
17 дек 12, 10:54    [13641225]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
А
Guest
А вот теперь вообще ничего не изменилось.
SELECT * INTO #res FROM OPENROWSET('SQLNCLI', 'Server=1cserv;Trusted_Connection=yes;',
 'exec ommet.dbo.spBudgetData ''20110101'', 14, 13') AS a
выдает ту же ошибку
17 дек 12, 11:19    [13641410]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Ошибка говорит о том, что невозможно получить метаданные результирующего набора, возвращаемого процедурой. Потому что, скорее всего, в ней используется динамика.
17 дек 12, 11:53    [13641658]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
А
Guest
Конечно в ней используется динамика. Еще в самом первом посте я спрашивал про процедуру с переменным количеством колонок. Так можно это обойти?
17 дек 12, 12:08    [13641768]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
Гость333
Member

Откуда:
Сообщений: 3683
invm
Ошибка говорит о том, что невозможно получить метаданные результирующего набора, возвращаемого процедурой. Потому что, скорее всего, в ней используется динамика.

Не знал. Можете привести какой-нибудь простенький пример? У меня максимум, что получилось вопроизвести, —
автор
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
17 дек 12, 12:14    [13641804]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Гость333
Можете привести какой-нибудь простенький пример?
+ Пример
use tempdb;
go

create procedure dbo.spTest
 @p int = null
 
as
begin
 set nocount on;
 
 if @p = 1
  exec('select top (1) * from sys.objects;');

 if @p = 2
  exec('select top (1) * from sys.sysforeignkeys;');
end;
go

exec dbo.spTest 1;
exec dbo.spTest 2;
go

select * from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec tempdb.dbo.spTest');
go

drop procedure dbo.spTest;
go
17 дек 12, 12:31    [13641943]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
Гость333
Member

Откуда:
Сообщений: 3683
invm
create procedure dbo.spTest
 @p int = null


select * from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec tempdb.dbo.spTest');

У вас идёт вызов ХП с параметром по умолчанию, т.е. null. Для значения @p is null процедура не возвращает набора данных. Если в openrowset написать "exec tempdb.dbo.spTest 1", то всё работает. Так что ошибка в примере возникает не из-за динамики.
17 дек 12, 12:38    [13641997]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Гость333,

А я и не утверждал, что ошибка возникает именно из-за динамики. Я только это предположил, исходя из
А
Кто скажет, как затолкать ХП во временную таблицу, если она возвращает переменное кол-во столбцов?
17 дек 12, 12:50    [13642096]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
А
Конечно в ней используется динамика. Еще в самом первом посте я спрашивал про процедуру с переменным количеством колонок. Так можно это обойти?
+ Пляски с бубном и приседания
use tempdb;
go

create procedure dbo.spTest
 @p int = null
 
as
begin
 set nocount on;
 
 if @p = 1
  exec('select top (1) * from sys.objects;');

 if @p = 2
  exec('select top (1) * from sys.sysforeignkeys;');
end;
go

declare @md table (name sysname, user_type_id int, precision tinyint, scale tinyint, max_length smallint, collation_name sysname null)
declare @s varchar(max);

insert into @md 
 exec('if object_id(''tempdb..#t'', ''U'') is not null drop table #t; select top (0) * into #t from openrowset(''SQLNCLI'', ''Server=.;Trusted_Connection=yes;'', ''exec tempdb.dbo.spTest 1''); select name, user_type_id, precision, scale, max_length, collation_name from sys.columns where object_id = object_id(''tempdb..#t'', ''U'');')

select
 @s = (
  select
   'alter table #r add ' + quotename(md.name) + ' ' + quotename(t.name) +
   case
    when t.name in ('binary', 'varbinary', 'char', 'varchar', 'nchar', 'nvarchar') then
     case
      when md.max_length > 0 then '(' + cast(md.max_length as varchar(10)) + ')'
      else '(max)'
     end +
     case when md.collation_name is not null then ' collate ' + md.collation_name else '' end
    else ''
   end +
   case when t.name in ('numeric', 'decimal') then '(' + cast(md.precision as varchar(10)) + ',' + cast(md.scale as varchar(10)) + ')' else '' end +
   ' null; '
  from
   @md md join
   sys.types t on t.user_type_id = md.user_type_id
  for xml path(''), type
 ).value('.', 'varchar(max)');

if object_id('tempdb..#r', 'U') is not null
 drop table #r;
 
create table #r (dummy_column_123 int);
exec(@s);
alter table #r drop column dummy_column_123;

insert into #r
 exec dbo.spTest 1;

select * from #r;
go

drop procedure dbo.spTest;
go

ЗЫ: Уж сколько раз было говорено-переговорено: Не надо писать универсальных процедур. Нет, продолжают писать и ходить по граблям...
17 дек 12, 13:47    [13642543]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Сортировку забыл. Вот так правильнее:
+
use tempdb;
go

create procedure dbo.spTest
 @p int = null
 
as
begin
 set nocount on;
 
 if @p = 1
  exec('select top (1) * from sys.objects;');

 if @p = 2
  exec('select top (1) * from sys.sysforeignkeys;');
end;
go

declare @md table (name sysname, user_type_id int, precision tinyint, scale tinyint, max_length smallint, collation_name sysname null, column_id int)
declare @s varchar(max);

insert into @md 
 exec('if object_id(''tempdb..#t'', ''U'') is not null drop table #t; select top (0) * into #t from openrowset(''SQLNCLI'', ''Server=.;Trusted_Connection=yes;'', ''exec tempdb.dbo.spTest 1''); select name, user_type_id, precision, scale, max_length, collation_name, column_id from sys.columns where object_id = object_id(''tempdb..#t'', ''U'');')

select
 @s = (
  select
   'alter table #r add ' + quotename(md.name) + ' ' + quotename(t.name) +
   case
    when t.name in ('binary', 'varbinary', 'char', 'varchar', 'nchar', 'nvarchar') then
     case
      when md.max_length > 0 then '(' + cast(md.max_length as varchar(10)) + ')'
      else '(max)'
     end +
     case when md.collation_name is not null then ' collate ' + md.collation_name else '' end
    else ''
   end +
   case when t.name in ('numeric', 'decimal') then '(' + cast(md.precision as varchar(10)) + ',' + cast(md.scale as varchar(10)) + ')' else '' end +
   ' null; '
  from
   @md md join
   sys.types t on t.user_type_id = md.user_type_id
  order by
   md.column_id
  for xml path(''), type
 ).value('.', 'varchar(max)');

if object_id('tempdb..#r', 'U') is not null
 drop table #r;
 
create table #r (dummy_column_123 int);
exec(@s);
alter table #r drop column dummy_column_123;

insert into #r
 exec dbo.spTest 1;

select * from #r;
go

drop procedure dbo.spTest;
go
17 дек 12, 13:53    [13642585]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
Гость333
Member

Откуда:
Сообщений: 3683
invm
Пляски с бубном и приседания

Ух, ничего себе. А можно поинтересоваться, зачем эти приседания, если в примере всё работает через простой "select * into #t from openrowset()"? :-)
17 дек 12, 14:01    [13642684]     Ответить | Цитировать Сообщить модератору
 Re: ХП во временную таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Гость333,

В openrowset динамику ведь не передать :)
17 дек 12, 14:04    [13642729]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить