Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mahimam_2004
Starting Member
40 Posts |
 Posted - 2007-03-08 : 18:03:24
|
| Hi, I have two databases DB2006, DB2005.I have the Stored Procedure getdata which has the 2 parameters startdate and end date.This Stored procedure exist in all databases.STored procedure CallGetdata@startdate datetime@enddate datetimeIf startdate < 1/1/2007 the call getdata in the DB2006if startdate <1/1/2006 then call getdata in the DB2005.Here the problem is if startdate is 6/1/2005 and Enddate is '3/1/2006' then combine the stored procedure results from the DB2006 and DB2005 databases.I have one idea i.e create a temp table and insert the two Stored procedure results into it.Create #table1(name varchar(20))insert into #table1 exec DB2006.dbo.getdatainsert into #table1 exexc DB2005.dbo.getdataSelect * from #table1drop table #table1.Anyone please give me better idea than creating temp table.Thanks in advance |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-08 : 18:31:18
|
| A partitioned view (and in your case a distributed partitioned view) will make this very easy. You can still use a stored procedure call from the client, but the stored procedure just queries the view and you never even have to know which data is on which server, the view takes care of that for you.Look it up in Books Online, it will make your solution easy. |
 |
|
|
|
|
|
Combine Stored Procedure