Remote queries

I nee to execute a proc on few servers and store the results on a
central database in a table. How can I do this without writing the DTS
packages?
I tried the Sybase way ( Server...sp_myproc) and it did not work. I
added the linked server before trying this. Any pointers'
ThanksIt 'should' work by using the Four part name, e.g., ServerName.Database.Sche
ma.Object.
For Example:
Execute MyServer.Northwind.dbo.CustOrderHist
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BS" <minimyme@.yahoo.com> wrote in message news:1154381608.618328.125960@.s13g2000cwa.googleg
roups.com...
>I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
>
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
>
> Thanks
>|||Hi
You could do this in a single DTS and use a Dynamic properties task to
change source server, database and table information.
John
"BS" wrote:

> I nee to execute a proc on few servers and store the results on a
> central database in a table. How can I do this without writing the DTS
> packages?
> I tried the Sybase way ( Server...sp_myproc) and it did not work. I
> added the linked server before trying this. Any pointers'
> Thanks
>|||Here is the SQL that I'm trying to execute and the columns that the
proc returns and the table structute that I want the data to be
inserted into.
create table DBSpace (
ServerName nvarchar(20) null,
DBName nvarchar(20) null,
SegType nvarchar(20) null,
AllocatedSpace int null,
UsedSpace int null,
[Free%] int null )
insert into DBSpace
execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
If I try to execute this, I get the error as below
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
Remove the quotes from around
'WHP-MactiveTest.master.dbo.sp_db_space_used'
Roy Harvey
Beaon Falls, CT
On 1 Aug 2006 12:16:38 -0700, "BS" <minimyme@.yahoo.com> wrote:

>Here is the SQL that I'm trying to execute and the columns that the
>proc returns and the table structute that I want the data to be
>inserted into.
>create table DBSpace (
>ServerName nvarchar(20) null,
>DBName nvarchar(20) null,
>SegType nvarchar(20) null,
>AllocatedSpace int null,
>UsedSpace int null,
>[Free%] int null )
>
>insert into DBSpace
>execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>If I try to execute this, I get the error as below
>Msg 102, Level 15, State 1, Line 2
>Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.|||Two problems.
1. No should NOT be Quotes around the object name
2. In order to use a server name that includes a dash [-] or instance na
me that includes a slash [\], you need to surround ONLY the server name
with square brackets.
E.g.,
EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"BS" <minimyme@.yahoo.com> wrote in message news:1154459798.626453.71360@.m73g2000cwd.googlegr
oups.com...
> Here is the SQL that I'm trying to execute and the columns that the
> proc returns and the table structute that I want the data to be
> inserted into.
>
> create table DBSpace (
> ServerName nvarchar(20) null,
> DBName nvarchar(20) null,
> SegType nvarchar(20) null,
> AllocatedSpace int null,
> UsedSpace int null,
> [Free%] int null )
>
>
> insert into DBSpace
> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'
>
> If I try to execute this, I get the error as below
>
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.
>