|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:41 AM
Points: 711,
Visits: 209
|
|
|
|
|
|
SSCarpal Tunnel
       
Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 4,804,
Visits: 8,068
|
|
Nice way to get it to work! I don't see why it shouldn't be used in production environment, it just needs to be a bit refined... I wouldn't include connection strings in the statement, but I don't see it nor dangerous nor unstable, connection strings apart.
I coded something similar a couple of years ago to work around limitations on insert / exec , but I used CLR to achieve it, providing a connection key mapped to a small table holding connection information.
Get your two-cent-answer quickly The Spaghetti DBA
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Thursday, January 17, 2013 5:06 PM
Points: 16,
Visits: 129
|
|
Unrelated suggestion or correction:
I noticed you used this statement to remove the procedure before recreating it
if exists(select * from sysobjects where id = object_id('dbo.proc_simple') and type = 'P') drop procedure dbo.proc_simple go
I've always been using this instead. Is what I'm doing wrong in any way?
if object_id('dbo.proc_simple','P') is not null drop procedure dbo.proc_simple go
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:41 AM
Points: 711,
Visits: 209
|
|
IMHO not. Your way seems to me to be more "clean" and I think that it is better than mine (which is perhaps unnecessarily to sofisticated than it should). It is just my bad habit to drop objects in this way.
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Monday, September 14, 2009 5:22 AM
Points: 438,
Visits: 918
|
|
| Er, I hope it is not a dumb quesiton, but I do not see what go again over just running the stored proc...
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 8,
Visits: 31
|
|
I use this method when I need a dynamic solution:
use AdventureWorks2000 go
if exists(select * from sysobjects where id = object_id('dbo.proc_dynamic') and type = 'P') drop procedure dbo.proc_dynamic go
create procedure dbo.proc_dynamic @name varchar(50) as begin select LocationId, [Name], rowguid from dbo.Location where [Name] = @name end go DECLARE @SELECT varchar(1000), @NAME varchar(50) SET @NAME = 'Location' SET @SELECT = 'SELECT * FROM OPENQUERY([' + @@SERVERNAME + '],''exec AdventureWorks2000.dbo.proc_dynamic ''''' + @Name + ''''' '')'
EXEC(@SELECT) go
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Saturday, March 16, 2013 2:51 AM
Points: 8,
Visits: 31
|
|
To connect to remote server:
select * from openrowset('SQLOLEDB', 'Uid=myUsername;Pwd=myPassword;Server=ServerNameOrAddress;Database=AdventureWorks2000', 'exec dbo.proc_simple')
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,971,
Visits: 1,821
|
|
| Good idea! But just for few rows result set. Else it takes too long to run if put the result set in join with another table.
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Monday, September 14, 2009 5:22 AM
Points: 438,
Visits: 918
|
|
The first statement is very close to what SQL Server generates when you script a procedure, except that the last condition in the WHERE clause is
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
instead of
and type = 'P'
I was just using this statement out of habit, did not think of doing it in a simpler way, such as yours. Nice one.
I love this forum, learning something new everyday.
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,164,
Visits: 3,335
|
|
I think more explanation is needed about what your script is demonstrating. Is your specific intent to demonstrate how to select from a "remote" stored procedure? When I want to return a filtered or transformed result from a stored procedure, I will insert into a table variable or temp table like so. This seems cleaner, because it required no connection string.
create procedure dbo.proc_simple as select top 5 object_id, name from sys.objects go
declare @objects table ( obj_id int, obj_name varchar(180) );
insert into @objects ( obj_id, obj_name ) exec dbo.proc_simple;
select * from @objects;
obj_id obj_name ----------- -------------------- 4 sysrowsetcolumns 5 sysrowsets 7 sysallocunits 8 sysfiles1 13 syshobtcolumns
"Wise people understand the 10,000 things without going to each one. They know them without having to look at each one, and they transform all without acting on each one." - The Tao Te Ching: Verse 47
|
|
|
|