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'
ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It 'should' work by using the Four part name, e.g., =ServerName.Database.Schema.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.googlegroups.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
>
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It 'should' work by using the Four part =name, e.g., ServerName.Database.Schema.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"
--=_NextPart_000_0078_01C6B4B9.9CC3E0B0--|||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'.|||This is a multi-part message in MIME format.
--=_NextPart_000_03A4_01C6B567.F7543DF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 =name 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.googlegroups.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'.
>
--=_NextPart_000_03A4_01C6B567.F7543DF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Two problems.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>1. No should NOT be Quotes around the =object name</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2. In order to use a server name that =includes a dash [-] or instance name that includes a slash [\], you need to =surround ONLY the server name with square brackets.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>E.g.,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>EXECUTE [WHP-MactiveTest].master.dbo.sp_db_space_used</FONT></DIV>
<DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, =Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Most good judgment comes from =experience. <BR>Most experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>"BS" <</FONT><A href=3D"mailto:minimyme@.yahoo.com"><FONT face=3DArial size=3D2>minimyme@.yahoo.com</FONT></A><FONT face=3DArial size=3D2>> =wrote in message </FONT><A href=3D"news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com"><FONT =
face=3DArial size=3D2>news:1154459798.626453.71360@.m73g2000cwd.googlegroups.com</FONT>=</A><FONT face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> =Here is the SQL that I'm trying to execute and the columns that the<BR>> proc returns =and the table structute that I want the data to be<BR>> inserted =into.<BR>> <BR>> create table DBSpace (<BR>> ServerName nvarchar(20) =null,<BR>> DBName nvarchar(20) null,<BR>> SegType nvarchar(20) null,<BR>> AllocatedSpace int null,<BR>> UsedSpace int null,<BR>> [Free%] int =null )<BR>> <BR>> <BR>> insert into DBSpace<BR>> execute 'WHP-MactiveTest.master.dbo.sp_db_space_used'<BR>> <BR>> If I try =to execute this, I get the error as below<BR>> <BR>> Msg 102, Level =15, State 1, Line 2<BR>> Incorrect syntax near 'WHP-MactiveTest.master.dbo.sp_db_space_used'.<BR>></FONT></BODY></HTM=L>
--=_NextPart_000_03A4_01C6B567.F7543DF0--
