"Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:FE70404A-732E-4277-84C2-C6C1D8C5EFE5@microsoft.com...
> set @s1 = 'exec OPENDATASOURCE('+'''SQLOLEDB'''+','+'''Data
> Source='+@ClientIPAddress+';User
> ID=sa;Password='+@ClientPassword+''''+')'+'.master.dbo.sp_space_check'
> exec(@s1)
>
>
> result of sp is inserted into temp table in the SP itself .
>
> "Uri Dimant" wrote:
>
>> Hi
>> How do you execute the SP? exec svrname.dbname.dob.SP?
>>
>> One more question , can you tell me , the result of SP should be inserted
>> into a temporary table within the same SP or do you have separated batch?
>>
>>
>> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> news:D016E868-146B-473B-B015-DB7A410A6748@microsoft.com...
>> > Hi,
>> >
>> > The functionality of my procedure is as follows
>> >
>> > we have 700 remote servers and in each remote server I have written a
>> > procedure.
>> > This procedure stores the result in temp table in remote server
>> >
>> > step-1 I will create a table which consists of connection information
>> > like
>> > username,passwordetc... I havent used any index on this table but a
>> > cursor
>> > will fetch all the rows and then goes for next step
>> >
>> > step-2
>> > From my local server I will connect to these 700 servers then
>> > 1. Execute the stored procedure on the remote server
>> > 2. pulls the data from the temp table in remote server
>> > 3. Dumps the data into a local table
>> >
>> > The result rows returned from each remote server would typically vary
>> > from
>> > 10 to 15 rows.
>> >
>> > Observation:
>> > --------------
>> > If in a remote server I get time out expired , Iam re-executing the
>> > procedure
>> > Then I was able to connect to that remote server and can pull the data
>> >
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> Well, you are getting pretty big output or your query does not have
>> >> properly
>> >> created indexes. How many rows do you get back?
>> >> Have you looked at execution plan? Does it use indexes?
>> >>
>> >>
>> >>
>> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> >> news:F9E3D956-B4FE-4907-9825-5EFF4211BFA0@microsoft.com...
>> >> > yes we use a private VPN and our connections to remote servers arew
>> >> > fine.
>> >> >
>> >> > when I run on local server the result will be displayed within
>> >> > 00:00:15
>> >> > seconds
>> >> >
>> >> >
>> >> >
>> >> > "Uri Dimant" wrote:
>> >> >
>> >> >> Hi
>> >> >> Is network traffic OK?
>> >> >> When you run the query on local database , does it return result
>> >> >> fast?
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> >> >> news:08608350-0195-4201-A45A-B9969CAD4FF0@microsoft.com...
>> >> >> > Yes I have pingged the server and connection is fine.
>> >> >> >
>> >> >> > also when I re-execute the procedure then the connection is fine
>> >> >> > ...
>> >> >> >
>> >> >> > but the problem is I need to create a job for this stored
>> >> >> > procedure
>> >> >> > ...
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Uri Dimant" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >> Did you have PING to the remote server?
>> >> >> >>
>> >> >> >>
>> >> >> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> >> >> >> news:7C900F98-D2C8-4C4F-AF9B-72C593673D47@microsoft.com...
>> >> >> >> > Hello all!
>> >> >> >> >
>> >> >> >> > I am writing a stored procedure in SQL SERVER 2000 that
>> >> >> >> > queries
>> >> >> >> > multiple
>> >> >> >> > remote databases.
>> >> >> >> > When one of them timesout, it will give the error 7399
>> >> >> >> >
>> >> >> >> > Error Log:
>> >> >> >> > ----------------
>> >> >> >> > Server: Msg 7399, Level 16, State 1, Line 1
>> >> >> >> > OLE DB provider 'SQLOLEDB' reported an error.
>> >> >> >> > [OLE/DB provider returned message: Timeout expired]
>> >> >> >> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> >> >> >> > IDBInitialize::Initialize
>> >> >> >> > returned 0x80004005: ].
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > and stop everything after it. is there a way to trap the error
>> >> >> >> > and
>> >> >> >> > continue
>> >> >> >> >
>> >> >> >> > I have applied both the fixes given in Microsoft KB article
>> >> >> >> >
>> >> >> >> > http://support.microsoft.com/kb/314530
>> >> >> >> >
>> >> >> >> > but the problem still exist
>> >> >> >> >
>> >> >> >> > can anyone please help on this
>> >> >> >> >
>> >> >> >> > Thanks,
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

"Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:D016E868-146B-473B-B015-DB7A410A6748@microsoft.com...
> Hi,
>
> The functionality of my procedure is as follows
>
> we have 700 remote servers and in each remote server I have written a
> procedure.
> This procedure stores the result in temp table in remote server
>
> step-1 I will create a table which consists of connection information
> like
> username,passwordetc... I havent used any index on this table but a cursor
> will fetch all the rows and then goes for next step
>
> step-2
> From my local server I will connect to these 700 servers then
> 1. Execute the stored procedure on the remote server
> 2. pulls the data from the temp table in remote server
> 3. Dumps the data into a local table
>
> The result rows returned from each remote server would typically vary from
> 10 to 15 rows.
>
> Observation:
> --------------
> If in a remote server I get time out expired , Iam re-executing the
> procedure
> Then I was able to connect to that remote server and can pull the data
>
>
>
> "Uri Dimant" wrote:
>
>> Hi
>> Well, you are getting pretty big output or your query does not have
>> properly
>> created indexes. How many rows do you get back?
>> Have you looked at execution plan? Does it use indexes?
>>
>>
>>
>> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> news:F9E3D956-B4FE-4907-9825-5EFF4211BFA0@microsoft.com...
>> > yes we use a private VPN and our connections to remote servers arew
>> > fine.
>> >
>> > when I run on local server the result will be displayed within 00:00:15
>> > seconds
>> >
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> Is network traffic OK?
>> >> When you run the query on local database , does it return result fast?
>> >>
>> >>
>> >>
>> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> >> news:08608350-0195-4201-A45A-B9969CAD4FF0@microsoft.com...
>> >> > Yes I have pingged the server and connection is fine.
>> >> >
>> >> > also when I re-execute the procedure then the connection is fine ...
>> >> >
>> >> > but the problem is I need to create a job for this stored procedure
>> >> > ...
>> >> >
>> >> >
>> >> >
>> >> > "Uri Dimant" wrote:
>> >> >
>> >> >> Hi
>> >> >> Did you have PING to the remote server?
>> >> >>
>> >> >>
>> >> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> >> >> news:7C900F98-D2C8-4C4F-AF9B-72C593673D47@microsoft.com...
>> >> >> > Hello all!
>> >> >> >
>> >> >> > I am writing a stored procedure in SQL SERVER 2000 that queries
>> >> >> > multiple
>> >> >> > remote databases.
>> >> >> > When one of them timesout, it will give the error 7399
>> >> >> >
>> >> >> > Error Log:
>> >> >> > ----------------
>> >> >> > Server: Msg 7399, Level 16, State 1, Line 1
>> >> >> > OLE DB provider 'SQLOLEDB' reported an error.
>> >> >> > [OLE/DB provider returned message: Timeout expired]
>> >> >> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> >> >> > IDBInitialize::Initialize
>> >> >> > returned 0x80004005: ].
>> >> >> >
>> >> >> >
>> >> >> > and stop everything after it. is there a way to trap the error
>> >> >> > and
>> >> >> > continue
>> >> >> >
>> >> >> > I have applied both the fixes given in Microsoft KB article
>> >> >> >
>> >> >> > http://support.microsoft.com/kb/314530
>> >> >> >
>> >> >> > but the problem still exist
>> >> >> >
>> >> >> > can anyone please help on this
>> >> >> >
>> >> >> > Thanks,
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

"Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:F9E3D956-B4FE-4907-9825-5EFF4211BFA0@microsoft.com...
> yes we use a private VPN and our connections to remote servers arew fine.
>
> when I run on local server the result will be displayed within 00:00:15
> seconds
>
>
>
> "Uri Dimant" wrote:
>
>> Hi
>> Is network traffic OK?
>> When you run the query on local database , does it return result fast?
>>
>>
>>
>> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> news:08608350-0195-4201-A45A-B9969CAD4FF0@microsoft.com...
>> > Yes I have pingged the server and connection is fine.
>> >
>> > also when I re-execute the procedure then the connection is fine ...
>> >
>> > but the problem is I need to create a job for this stored procedure ...
>> >
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> Did you have PING to the remote server?
>> >>
>> >>
>> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> >> news:7C900F98-D2C8-4C4F-AF9B-72C593673D47@microsoft.com...
>> >> > Hello all!
>> >> >
>> >> > I am writing a stored procedure in SQL SERVER 2000 that queries
>> >> > multiple
>> >> > remote databases.
>> >> > When one of them timesout, it will give the error 7399
>> >> >
>> >> > Error Log:
>> >> > ----------------
>> >> > Server: Msg 7399, Level 16, State 1, Line 1
>> >> > OLE DB provider 'SQLOLEDB' reported an error.
>> >> > [OLE/DB provider returned message: Timeout expired]
>> >> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> >> > IDBInitialize::Initialize
>> >> > returned 0x80004005: ].
>> >> >
>> >> >
>> >> > and stop everything after it. is there a way to trap the error and
>> >> > continue
>> >> >
>> >> > I have applied both the fixes given in Microsoft KB article
>> >> >
>> >> > http://support.microsoft.com/kb/314530
>> >> >
>> >> > but the problem still exist
>> >> >
>> >> > can anyone please help on this
>> >> >
>> >> > Thanks,
>> >>
>> >>
>> >>
>>
>>
>>

"Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:08608350-0195-4201-A45A-B9969CAD4FF0@microsoft.com...
> Yes I have pingged the server and connection is fine.
>
> also when I re-execute the procedure then the connection is fine ...
>
> but the problem is I need to create a job for this stored procedure ...
>
>
>
> "Uri Dimant" wrote:
>
>> Hi
>> Did you have PING to the remote server?
>>
>>
>> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
>> news:7C900F98-D2C8-4C4F-AF9B-72C593673D47@microsoft.com...
>> > Hello all!
>> >
>> > I am writing a stored procedure in SQL SERVER 2000 that queries
>> > multiple
>> > remote databases.
>> > When one of them timesout, it will give the error 7399
>> >
>> > Error Log:
>> > ----------------
>> > Server: Msg 7399, Level 16, State 1, Line 1
>> > OLE DB provider 'SQLOLEDB' reported an error.
>> > [OLE/DB provider returned message: Timeout expired]
>> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> > IDBInitialize::Initialize
>> > returned 0x80004005: ].
>> >
>> >
>> > and stop everything after it. is there a way to trap the error and
>> > continue
>> >
>> > I have applied both the fixes given in Microsoft KB article
>> >
>> > http://support.microsoft.com/kb/314530
>> >
>> > but the problem still exist
>> >
>> > can anyone please help on this
>> >
>> > Thanks,
>>
>>
>>

"Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:7C900F98-D2C8-4C4F-AF9B-72C593673D47@microsoft.com...
> Hello all!
>
> I am writing a stored procedure in SQL SERVER 2000 that queries multiple
> remote databases.
> When one of them timesout, it will give the error 7399
>
> Error Log:
> ----------------
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
>
>
> and stop everything after it. is there a way to trap the error and
> continue
>
> I have applied both the fixes given in Microsoft KB article
>
> http://support.microsoft.com/kb/314530
>
> but the problem still exist
>
> can anyone please help on this
>
> Thanks,