"rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message
news:9B37074A-227B-4991-B5F2-6AE1EF5BD0CB@microsoft.com...
> Where I work we write hundreds of procedures which run against different
> databases depending on the connection string passed in. To manage this,
> there
> is a user function which returns the string for the linked server, eg.
> '[x.x.x.x].somdb.dbo.' which can be prepended to tablenames in a string.
>
> Normally, we use either varchar(8000) or nvarchar(4000) strings to
> concattentate the sql and then we execute it or pass it to sp_executeSQL.
>
> It's a real pain writing the sql in this way as debugging is difficult -
> in
> an 8000 character piece of sql, being taken to the first line and being
> told
> there's a bug in there somewhere isn't much help.
>
> I know it's possible to add linked servers using code in the sql sproc, so
> there should be a way round this - add a different linked server depending
> on
> the connection string passed in but using the same name, then write the
> sql
> as normal.
>
> My question is about the overhead of doing this. These processes may run a
> few hundred times a day and each procedure's runtime needs to be kept
> below
> 30 seconds (some do stray up to 5 mins - but a huge amount of effort is
> put
> into making them run quicker). Is it normal practice to add and drop
> linked
> servers in this way? If a linked server is added by two sprocs with a
> different name in each, will that cause any issues?
>
> All comments welcome.

"Hilary Cotter" wrote:

> You might want to look at openrowset which creates a connection similar to a
> linked server on the fly.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message
> news:9B37074A-227B-4991-B5F2-6AE1EF5BD0CB@microsoft.com...
> > Where I work we write hundreds of procedures which run against different
> > databases depending on the connection string passed in. To manage this,
> > there
> > is a user function which returns the string for the linked server, eg.
> > '[x.x.x.x].somdb.dbo.' which can be prepended to tablenames in a string.
> >
> > Normally, we use either varchar(8000) or nvarchar(4000) strings to
> > concattentate the sql and then we execute it or pass it to sp_executeSQL.
> >
> > It's a real pain writing the sql in this way as debugging is difficult -
> > in
> > an 8000 character piece of sql, being taken to the first line and being
> > told
> > there's a bug in there somewhere isn't much help.
> >
> > I know it's possible to add linked servers using code in the sql sproc, so
> > there should be a way round this - add a different linked server depending
> > on
> > the connection string passed in but using the same name, then write the
> > sql
> > as normal.
> >
> > My question is about the overhead of doing this. These processes may run a
> > few hundred times a day and each procedure's runtime needs to be kept
> > below
> > 30 seconds (some do stray up to 5 mins - but a huge amount of effort is
> > put
> > into making them run quicker). Is it normal practice to add and drop
> > linked
> > servers in this way? If a linked server is added by two sprocs with a
> > different name in each, will that cause any issues?
> >
> > All comments welcome.
>
>
>

"rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message
news:049EED15-229A-4D8F-B8DF-758CC7048D6A@microsoft.com...
> I've used OPENROWSET and OPENDATASOURCE in different places. Wouldn't
> these
> require authentication each time they access the remote server? I had
> thought
> these would be the most inefficient method, but I may be wrong.
>
> "Hilary Cotter" wrote:
>
>> You might want to look at openrowset which creates a connection similar
>> to a
>> linked server on the fly.
>>
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>>
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>>
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>>
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>>
>>
>> "rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message
>> news:9B37074A-227B-4991-B5F2-6AE1EF5BD0CB@microsoft.com...
>> > Where I work we write hundreds of procedures which run against
>> > different
>> > databases depending on the connection string passed in. To manage this,
>> > there
>> > is a user function which returns the string for the linked server, eg.
>> > '[x.x.x.x].somdb.dbo.' which can be prepended to tablenames in a
>> > string.
>> >
>> > Normally, we use either varchar(8000) or nvarchar(4000) strings to
>> > concattentate the sql and then we execute it or pass it to
>> > sp_executeSQL.
>> >
>> > It's a real pain writing the sql in this way as debugging is
>> > difficult -
>> > in
>> > an 8000 character piece of sql, being taken to the first line and being
>> > told
>> > there's a bug in there somewhere isn't much help.
>> >
>> > I know it's possible to add linked servers using code in the sql sproc,
>> > so
>> > there should be a way round this - add a different linked server
>> > depending
>> > on
>> > the connection string passed in but using the same name, then write the
>> > sql
>> > as normal.
>> >
>> > My question is about the overhead of doing this. These processes may
>> > run a
>> > few hundred times a day and each procedure's runtime needs to be kept
>> > below
>> > 30 seconds (some do stray up to 5 mins - but a huge amount of effort is
>> > put
>> > into making them run quicker). Is it normal practice to add and drop
>> > linked
>> > servers in this way? If a linked server is added by two sprocs with a
>> > different name in each, will that cause any issues?
>> >
>> > All comments welcome.
>>
>>
>>