Need help please... SP,temp tables...
-
can someone tell me if i cant get the expected number of columns for an SP,with out actually running it..like the sql server internally compiles and tells us wht is the expected number of columns..
if i get this,i will be able to break my logic and look for a different work arnd.
-
No you can't do that... because the sp can have more than 1 result set at the same time, also it can have different results set at each run.
The only way I could figure out some sort of solution would be to use the same openrowset on the remote server into a local table. Then search the tempdb..syscolumns table for the temp object.
-
ok, any idea abt this?
"if i cant get the expected number of columns for an SP,with out actually running it"
i explained this in previous post..if u hav missed it..
-
That's what I said. You can't do that without running it. Maybe you can do a general table with 100 varchar columns and insert into that... but that won't come without its load of problems.
PS I e-mailed the expert of this site on remote query. I just hope he'll be able to check it out soon.
-
Dear frnd,any luck with this?
-
Not really. The guy I e-mail didn't even read the message yet and there's nothing else I can think of so looks like you are on your own at the moment.
-
hm..ok ..i will try for some workaround.
Thanks anywyz..
-
Hi,
The INSERT command supports calling a stored procedure to supply rows for insertion into a table
CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL,
indid int, type char(4), resource char(15), mode char(10), status char(6))
INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)
EXEC dbo.sp_lock
SELECT * FROM #locks
DROP TABLE #locks
This is a handy way of trapping the output of a stored procedure in a table so that you can manipulate it or retain it for later use. Prior to the advent of cursor OUTPUT parameters, this was the only way to perform further work on a stored procedure's result set within Transact-SQL.
Note that INSERT...EXEC works with extended procedures that return result sets as well
Reference for detail information
http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=6&rl=1
Hope this will help you.
Redards,
Baliram Suryawanshi
-
@Baliram.
thanks for u r suggestion...But the SP that i execute is dynamic..
So before executing we really dont know wht would be the number of columns..
so temp table cannot be created..prior to execution..
in u r example, EXEC dbo.sp_lock..
sp_lock is dynamic for me..each time the SP may return any number of columns..
lemme know if u have any solution
-
This is a "WORKING" example... do your thing and Enjoy!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc2]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure dbo.proc1 @param1 int, @param2 int
as
begin
set nocount on
create table #T1( data1 int, data2 int)
insert into #T1 ( data1 , data2 )
select top 10 @param1 , @param2
from sysobjects a cross join sysobjects b
select * from #T1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure dbo.proc2 @param1 int, @param2 int, @param3 int
as
begin
set nocount on
create table #T2( data1 int, data2 int, data3 int)
insert into #T2 ( data1 , data2, data3 )
select top 10 @param1 , @param2, @param3
from sysobjects a cross join sysobjects b
select * from #T2
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID('tempdb..##tmptesting') IS NOT NULL
DROP TABLE ##tmptesting
GO
select * into ##tmptesting FROM OPENROWSET('SQLOLEDB','SERVERNAME';'UID';'PWD','SET FMTONLY OFF; exec devdba_data.dbo.proc1 1,1') AS a
select * from ##tmptesting
IF OBJECT_ID('tempdb..##tmptesting') IS NOT NULL
DROP TABLE ##tmptesting
GO
select * into ##tmptesting FROM OPENROWSET('SQLOLEDB','SERVERNAME';'UID';'PWD','SET FMTONLY OFF; exec devdba_data.dbo.proc2 2,2,2') AS a
select * from ##tmptesting
Warning... What you are trying to do does *not* seems like a good design but that is a subject for another thread
Cheers!
* Noel
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply