DB to DB sp

Hi,
i am writing a tool that interfaces to a third-party database. I will be
creating a database for my tool which contains stored procedures that
accesses tables from the third-party database. I am doing this only to aviod
manipulating the third party database. My database will be located in the
same server as the third-party database.
My question is, if i were to create these stored procedures and views that
accesses only tables on the third-party database in my database, would my
app still benefit from the performance gain which stored procedures and
views offers?
regards"Emmanuel" <emmanuel@.email.com> wrote in message
news:OP8RCD4kFHA.3580@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i am writing a tool that interfaces to a third-party database. I will be
> creating a database for my tool which contains stored procedures that
> accesses tables from the third-party database. I am doing this only to
> aviod manipulating the third party database. My database will be located
> in the same server as the third-party database.
> My question is, if i were to create these stored procedures and views that
> accesses only tables on the third-party database in my database, would my
> app still benefit from the performance gain which stored procedures and
> views offers?
> regards
>
That depends...
Are you going to use OPENROWSET and/or OPENQUERY, or are you going to create
a Linked server?
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Rick,
No, since the database will be created on the same server i will access the
third-party db tables using queries like:
SELECT column from [ThirdPartyDB].[dbo].[ThirdPartyTable]
and at most i would make queries which join some tables..
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OenmiI4kFHA.3312@.tk2msftngp13.phx.gbl...
> "Emmanuel" <emmanuel@.email.com> wrote in message
> news:OP8RCD4kFHA.3580@.TK2MSFTNGP09.phx.gbl...
> That depends...
> Are you going to use OPENROWSET and/or OPENQUERY, or are you going to
> create a Linked server?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> My question is, if i were to create these stored procedures and views that
> accesses only tables on the third-party database in my database, would my
> app still benefit from the performance gain which stored procedures and
> views offers?
Yes, if on the same server (instance).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Emmanuel" <emmanuel@.email.com> wrote in message news:OP8RCD4kFHA.3580@.TK2MSFTNGP09.phx.gbl
..
> Hi,
> i am writing a tool that interfaces to a third-party database. I will be
> creating a database for my tool which contains stored procedures that
> accesses tables from the third-party database. I am doing this only to avi
od
> manipulating the third party database. My database will be located in the
> same server as the third-party database.
> My question is, if i were to create these stored procedures and views that
> accesses only tables on the third-party database in my database, would my
> app still benefit from the performance gain which stored procedures and
> views offers?
> regards
>|||Yes, this will work; we do it all the time.
Having had a lot of expeience with integrating with 3rd-part databases,
here's some hard-earned lessons:
1. If you plan on EVER upgrading the third-party aplication, build a
relationship with the vendor. Let them know what you're doing, and
find out if they have a recommended solution for what you are planning
to do.
2. Always try to integrate using an application layer BEFORE going to
the data layer. Sometimes you have no choice, but if they support an
API, use it. It will save you some major headaches when they change
their schema at a later date (and that happens more often than you
know).
3. If there is no API, see if you can get them to commit to a standard
schema for exposng their database (much like Microsoft's
INFORMATION_SCHEMA for their system tables).
HTH,
Stu|||Hi,
thanks everyone! this was really helpful.
regards,
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1122564554.859930.125160@.g44g2000cwa.googlegroups.com...
> Yes, this will work; we do it all the time.
> Having had a lot of expeience with integrating with 3rd-part databases,
> here's some hard-earned lessons:
> 1. If you plan on EVER upgrading the third-party aplication, build a
> relationship with the vendor. Let them know what you're doing, and
> find out if they have a recommended solution for what you are planning
> to do.
> 2. Always try to integrate using an application layer BEFORE going to
> the data layer. Sometimes you have no choice, but if they support an
> API, use it. It will save you some major headaches when they change
> their schema at a later date (and that happens more often than you
> know).
> 3. If there is no API, see if you can get them to commit to a standard
> schema for exposng their database (much like Microsoft's
> INFORMATION_SCHEMA for their system tables).
> HTH,
> Stu
>