How can you link two datasets on a report that come from different databases.
I tried to create a queried parameter that pulls its value from a dataset1
field and use in the where clause of my dataset2 query. It gets an error
message indicating you cannot have a forward looking parameter. Any help for
this newbie is greatly appreciated.You should look at using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rdavis104" <rdavis104@.discussions.microsoft.com> wrote in message
news:5F433F07-C1A9-4B7F-8689-890A5476D8C1@.microsoft.com...
> How can you link two datasets on a report that come from different
> databases.
> I tried to create a queried parameter that pulls its value from a dataset1
> field and use in the where clause of my dataset2 query. It gets an error
> message indicating you cannot have a forward looking parameter. Any help
> for
> this newbie is greatly appreciated.|||Subreports worked great, very easy to setup.
Thanks
"Bruce L-C [MVP]" wrote:
> You should look at using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "rdavis104" <rdavis104@.discussions.microsoft.com> wrote in message
> news:5F433F07-C1A9-4B7F-8689-890A5476D8C1@.microsoft.com...
> > How can you link two datasets on a report that come from different
> > databases.
> > I tried to create a queried parameter that pulls its value from a dataset1
> > field and use in the where clause of my dataset2 query. It gets an error
> > message indicating you cannot have a forward looking parameter. Any help
> > for
> > this newbie is greatly appreciated.
>
>|||I ran across another snag. When I export the main report (with subreports)
to Excel the cells that should contain the subreport data have "Subreports
within table/matrix cells are ignored" as a value. Not sure if I missed
something or if I am doing something wrong. This report will need to output
to Excel format. Thanks in advance for the help.
"rdavis104" wrote:
> Subreports worked great, very easy to setup.
> Thanks
> "Bruce L-C [MVP]" wrote:
> > You should look at using subreports.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "rdavis104" <rdavis104@.discussions.microsoft.com> wrote in message
> > news:5F433F07-C1A9-4B7F-8689-890A5476D8C1@.microsoft.com...
> > > How can you link two datasets on a report that come from different
> > > databases.
> > > I tried to create a queried parameter that pulls its value from a dataset1
> > > field and use in the where clause of my dataset2 query. It gets an error
> > > message indicating you cannot have a forward looking parameter. Any help
> > > for
> > > this newbie is greatly appreciated.
> >
> >
> >sql
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
linking databases question
Hello
Does anyone know the best way to get around this problem.
Basically, I have three servers containing the same database structure. The data is different, each pertaining to the location in question.
Speaking in laymans terms, I would like to throw all the data into a big box from the three servers so that I can get the information I need from ONE source.
A problem I foresee is primary keys. For example, let's take the patient table(I work in health) the patient_key field in table patient although unique within it's own database, is not unique across the other databases from the other sites I want to "throw into the box". I can't just add another primary key of my own because it would not appear in the other tables that patient_key does. Obviously patient_key is one of many such primary keys that share this problem.
Does anyone have any suggestions as to how to go about this problem. Is there a way to "join" these databases together, like a union command does to more than one query.
My organisation uses MS SQL Server 2000, we have no analysis or reporting services add ons.
My SQL server knowledge is ok when it comes to querying and simple stuff, but the more complex administration is all new to me. So treat me like a rookie please.
thanks in advance
PaulCombining multiple databases into a single database is not that hard, although it is rather cumbersome and labor intensive. I have combined 28 databases into one database using MS-SQL 6.5.
The "real skinny" is complex, much more than I'm comfortable doing in a single post, but I can give you the 30,000 foot view fairly easily.
Create a whole new database for the purposes of conversion ONLY. Use your present table structures, but add two new columns to each table... One of those new columns should be a GUID as the unique key that will be used in the future, the other should be an indicator for which of the "old" databases that the row originated from...
The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.
Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another.
While you are doing this conversion, you might also want to consider converting all of your date and time values to UCT. This allows you to correctly display local time for anyone in the world, allowing you to ignore timezone problems too.
-PatP|||Hi Pat
thank you very much for your great reply.
Okay, so I understand most of what you said. But please understand I'm not an I.T tech person. My job is an Information Analyst. I have a strong interest in databases and desperately want to learn more.
Firstly, you say I have to create a new db. That is fine, I can do that. For the purposes of conversion, do you mean by that, somewhere to put the new data? or something else?
I had to look up what a GUID was. Globally unique ID. Ok, I get that. Although I don't know how I'm going to get these GUIDs into the new db. I dont know how.
"The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.
Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another"
I kind of understand this. But I don't know how to actually do it.
You mentioned this is just some outline of a "how to", can you point me in the right direction to see the "in depth" version if it exists.
thanks again
Paul
Combining multiple databases into a single database is not that hard, although it is rather cumbersome and labor intensive. I have combined 28 databases into one database using MS-SQL 6.5.
The "real skinny" is complex, much more than I'm comfortable doing in a single post, but I can give you the 30,000 foot view fairly easily.
Create a whole new database for the purposes of conversion ONLY. Use your present table structures, but add two new columns to each table... One of those new columns should be a GUID as the unique key that will be used in the future, the other should be an indicator for which of the "old" databases that the row originated from...
The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.
Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another.
While you are doing this conversion, you might also want to consider converting all of your date and time values to UCT. This allows you to correctly display local time for anyone in the world, allowing you to ignore timezone problems too.
-PatP
Does anyone know the best way to get around this problem.
Basically, I have three servers containing the same database structure. The data is different, each pertaining to the location in question.
Speaking in laymans terms, I would like to throw all the data into a big box from the three servers so that I can get the information I need from ONE source.
A problem I foresee is primary keys. For example, let's take the patient table(I work in health) the patient_key field in table patient although unique within it's own database, is not unique across the other databases from the other sites I want to "throw into the box". I can't just add another primary key of my own because it would not appear in the other tables that patient_key does. Obviously patient_key is one of many such primary keys that share this problem.
Does anyone have any suggestions as to how to go about this problem. Is there a way to "join" these databases together, like a union command does to more than one query.
My organisation uses MS SQL Server 2000, we have no analysis or reporting services add ons.
My SQL server knowledge is ok when it comes to querying and simple stuff, but the more complex administration is all new to me. So treat me like a rookie please.
thanks in advance
PaulCombining multiple databases into a single database is not that hard, although it is rather cumbersome and labor intensive. I have combined 28 databases into one database using MS-SQL 6.5.
The "real skinny" is complex, much more than I'm comfortable doing in a single post, but I can give you the 30,000 foot view fairly easily.
Create a whole new database for the purposes of conversion ONLY. Use your present table structures, but add two new columns to each table... One of those new columns should be a GUID as the unique key that will be used in the future, the other should be an indicator for which of the "old" databases that the row originated from...
The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.
Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another.
While you are doing this conversion, you might also want to consider converting all of your date and time values to UCT. This allows you to correctly display local time for anyone in the world, allowing you to ignore timezone problems too.
-PatP|||Hi Pat
thank you very much for your great reply.
Okay, so I understand most of what you said. But please understand I'm not an I.T tech person. My job is an Information Analyst. I have a strong interest in databases and desperately want to learn more.
Firstly, you say I have to create a new db. That is fine, I can do that. For the purposes of conversion, do you mean by that, somewhere to put the new data? or something else?
I had to look up what a GUID was. Globally unique ID. Ok, I get that. Although I don't know how I'm going to get these GUIDs into the new db. I dont know how.
"The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.
Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another"
I kind of understand this. But I don't know how to actually do it.
You mentioned this is just some outline of a "how to", can you point me in the right direction to see the "in depth" version if it exists.
thanks again
Paul
Combining multiple databases into a single database is not that hard, although it is rather cumbersome and labor intensive. I have combined 28 databases into one database using MS-SQL 6.5.
The "real skinny" is complex, much more than I'm comfortable doing in a single post, but I can give you the 30,000 foot view fairly easily.
Create a whole new database for the purposes of conversion ONLY. Use your present table structures, but add two new columns to each table... One of those new columns should be a GUID as the unique key that will be used in the future, the other should be an indicator for which of the "old" databases that the row originated from...
The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.
Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another.
While you are doing this conversion, you might also want to consider converting all of your date and time values to UCT. This allows you to correctly display local time for anyone in the world, allowing you to ignore timezone problems too.
-PatP
Linking databases on same SQL Server
Hi,
I am building an application for a client. The App has two separate .adp
frontends connecting to two differenet SQL databases. Now I have to get data
from each of the SQL databases for reporting.
It is possible to create another SQL database that links to the two existing
databases, mentioned above?
--
Regards,
AlanIt is possible, but not necessary. To get data from both dbs for reporting,
simply preface your query with the dbs name.
For example:
USE master
GO
SELECT TOP 5 * FROM Northwind.dbo.Customers
Andre|||This is a pretty open question. What do you mean, link exactly? Like do
you want to query them together? Or combine the data for reporting? And
how much data do you have?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"steel" <steel@.community.nospam> wrote in message
news:36AD2F00-AEC9-47DE-BFA4-65F0F886764D@.microsoft.com...
> Hi,
> I am building an application for a client. The App has two separate .adp
> frontends connecting to two differenet SQL databases. Now I have to get
> data
> from each of the SQL databases for reporting.
> It is possible to create another SQL database that links to the two
> existing
> databases, mentioned above?
> --
> Regards,
> Alan|||Hi Alan,
How are things going? If there are two different data source, you could
also Linked Table both. I would appreciate it if you could post here to let
us know the detail scenario of the issue. If you have any questions or
concerns, please don't hesitate to let me know. I look forward to hearing
from you, and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
I am developing two Access .adp projects. The intention is to have them both
as separate marketable databases. A company purchasing one or both projects
would either setup a SQL server or a single project using MSDE.
One company would like to purchase both databases (i.e., purchase .adp
projects plus setup/purchase of SQL Server). The company would like to have
data flow between the two databases. So rather than make changes to our
databases I thougt we could develop a third database that stores or links th
e
other two databases together. Maybe this database would only contain Queries
linking the data. If the company required any table modifications these to
could be stored in the third database.
One problem we may encounter is during replication of the .adp projects as
the company has an off-site use for the projects but would like to merge the
data into the main SQL db's.
What are your thoughts?
p.s: Sorry if this type of advice isn't covered by MSDN support.
Alan
--
Regards,
Alan
"Michael Cheng [MSFT]" wrote:
> Hi Alan,
> How are things going? If there are two different data source, you could
> also Linked Table both. I would appreciate it if you could post here to le
t
> us know the detail scenario of the issue. If you have any questions or
> concerns, please don't hesitate to let me know. I look forward to hearing
> from you, and I am happy to be of assistance.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||My advice would actually be to only have one database,especially if they are
very tightly bound to one another. Your install scripts could create the
database and or the objects at install time. The queries you create in the
database could be morphed as products are installed/deinstalled. This would
make it easier for the user to maintain in terms of backups and such, and
easier for you to create and everything.
You also might consider letting the database be any database, and give the
user scripts to add if they are advanced.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"steel" <steel@.community.nospam> wrote in message
news:1B6BDFFD-671C-4B6F-ABFE-73C7C4E69666@.microsoft.com...
> Hi Michael,
> I am developing two Access .adp projects. The intention is to have them
> both
> as separate marketable databases. A company purchasing one or both
> projects
> would either setup a SQL server or a single project using MSDE.
> One company would like to purchase both databases (i.e., purchase .adp
> projects plus setup/purchase of SQL Server). The company would like to
> have
> data flow between the two databases. So rather than make changes to our
> databases I thougt we could develop a third database that stores or links
> the
> other two databases together. Maybe this database would only contain
> Queries
> linking the data. If the company required any table modifications these to
> could be stored in the third database.
> One problem we may encounter is during replication of the .adp projects as
> the company has an off-site use for the projects but would like to merge
> the
> data into the main SQL db's.
> What are your thoughts?
> p.s: Sorry if this type of advice isn't covered by MSDN support.
> Alan
> --
> Regards,
> Alan
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi Alan,
Thanks for your questions and kindly understanding.
Yes, for this issue is more likely an Advisory Services issue. Microsoft
Advisory Services provides short-term advice and guidance for problems not
covered by Problem Resolution Service as well as requests for consultative
assistance for design, development and deployment issues. You may call this
number to get Advisory Services: (800) 936-5200.
I understood your scenario as when customer want a customization table, you
would like to put it in another database other than your project. If I have
misunderstood your concern, please feel free to point it out.
Personally, I agreed with MVP Louis Davidson's suggestion having only one
database.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
I am building an application for a client. The App has two separate .adp
frontends connecting to two differenet SQL databases. Now I have to get data
from each of the SQL databases for reporting.
It is possible to create another SQL database that links to the two existing
databases, mentioned above?
--
Regards,
AlanIt is possible, but not necessary. To get data from both dbs for reporting,
simply preface your query with the dbs name.
For example:
USE master
GO
SELECT TOP 5 * FROM Northwind.dbo.Customers
Andre|||This is a pretty open question. What do you mean, link exactly? Like do
you want to query them together? Or combine the data for reporting? And
how much data do you have?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"steel" <steel@.community.nospam> wrote in message
news:36AD2F00-AEC9-47DE-BFA4-65F0F886764D@.microsoft.com...
> Hi,
> I am building an application for a client. The App has two separate .adp
> frontends connecting to two differenet SQL databases. Now I have to get
> data
> from each of the SQL databases for reporting.
> It is possible to create another SQL database that links to the two
> existing
> databases, mentioned above?
> --
> Regards,
> Alan|||Hi Alan,
How are things going? If there are two different data source, you could
also Linked Table both. I would appreciate it if you could post here to let
us know the detail scenario of the issue. If you have any questions or
concerns, please don't hesitate to let me know. I look forward to hearing
from you, and I am happy to be of assistance.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
I am developing two Access .adp projects. The intention is to have them both
as separate marketable databases. A company purchasing one or both projects
would either setup a SQL server or a single project using MSDE.
One company would like to purchase both databases (i.e., purchase .adp
projects plus setup/purchase of SQL Server). The company would like to have
data flow between the two databases. So rather than make changes to our
databases I thougt we could develop a third database that stores or links th
e
other two databases together. Maybe this database would only contain Queries
linking the data. If the company required any table modifications these to
could be stored in the third database.
One problem we may encounter is during replication of the .adp projects as
the company has an off-site use for the projects but would like to merge the
data into the main SQL db's.
What are your thoughts?
p.s: Sorry if this type of advice isn't covered by MSDN support.
Alan
--
Regards,
Alan
"Michael Cheng [MSFT]" wrote:
> Hi Alan,
> How are things going? If there are two different data source, you could
> also Linked Table both. I would appreciate it if you could post here to le
t
> us know the detail scenario of the issue. If you have any questions or
> concerns, please don't hesitate to let me know. I look forward to hearing
> from you, and I am happy to be of assistance.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||My advice would actually be to only have one database,especially if they are
very tightly bound to one another. Your install scripts could create the
database and or the objects at install time. The queries you create in the
database could be morphed as products are installed/deinstalled. This would
make it easier for the user to maintain in terms of backups and such, and
easier for you to create and everything.
You also might consider letting the database be any database, and give the
user scripts to add if they are advanced.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"steel" <steel@.community.nospam> wrote in message
news:1B6BDFFD-671C-4B6F-ABFE-73C7C4E69666@.microsoft.com...
> Hi Michael,
> I am developing two Access .adp projects. The intention is to have them
> both
> as separate marketable databases. A company purchasing one or both
> projects
> would either setup a SQL server or a single project using MSDE.
> One company would like to purchase both databases (i.e., purchase .adp
> projects plus setup/purchase of SQL Server). The company would like to
> have
> data flow between the two databases. So rather than make changes to our
> databases I thougt we could develop a third database that stores or links
> the
> other two databases together. Maybe this database would only contain
> Queries
> linking the data. If the company required any table modifications these to
> could be stored in the third database.
> One problem we may encounter is during replication of the .adp projects as
> the company has an off-site use for the projects but would like to merge
> the
> data into the main SQL db's.
> What are your thoughts?
> p.s: Sorry if this type of advice isn't covered by MSDN support.
> Alan
> --
> Regards,
> Alan
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi Alan,
Thanks for your questions and kindly understanding.
Yes, for this issue is more likely an Advisory Services issue. Microsoft
Advisory Services provides short-term advice and guidance for problems not
covered by Problem Resolution Service as well as requests for consultative
assistance for design, development and deployment issues. You may call this
number to get Advisory Services: (800) 936-5200.
I understood your scenario as when customer want a customization table, you
would like to put it in another database other than your project. If I have
misunderstood your concern, please feel free to point it out.
Personally, I agreed with MVP Louis Davidson's suggestion having only one
database.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Linking between two databases
Is it posable to link between two diferent Databases? I am putting membership on my visual studios website which by default builds it's own database. I am then building a database for our products and services. I don't want to have to recreate all the client inforation that I already have in the default member database. But I don't think I want to put all the other info in the membershipp database.
Thanks for any help and advice
Ed
Hi,
you can either access your database by using three part names:
SELECT * FROM Database.Schema.ObjectName [Schema is called owner in SQL Server 2000]
or (but this implies the above one) creating view based on the table and syntax information above.
You have to keep in mind that you have to deal with the security in any way. There is a feature called crossdatabase ownership chain which would apply if activated to grant the user the rights in the destination database if the owner is the same across the databases and he has access in the current database to the object (like a view).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
sqlLinking 2 Databases
How do i get a query in 1 database to fetch results from a table in another database?
I am using MSSQL 2005I hope ur question is like this
I am using MSSQL 2005I hope ur question is like this
You have 2 database DB1 and DB2. You want to select Tab2 in DB2 while you are executing the query in DB1.
The query would be like this
Select *
From DB2.dbo.Tab2
the general syntax would be
Select <column1>,<column2>......
from <database_name>.dbo.<table_name>
linking 2 database
hi,
I have 2 databases from which my data will be coming from. How can I join this 2 databases in reporting services?
cherriesh
It depends on the structure of the data. There are a few options you can try, though none really involve joining 2 data sources together in Reporting Services - transforming the data for reporting is usually done with an ETL layer.
Build an ETL package to load the data into a temporary staging database or
Build a view in SQL Server using linked servers or 4-part naming to union the data together.
These are two ways to get the data together.
cheers,
Andrew
Linkedservers using windows authentication
I have two database servers with two different databases. I have the same
windows login set up on both database servers with read access to the two
different databases. I have linkedserver between the two database server.
When I query using a SQL login I get a result set. When I use the windows
login, I get an error - all else is equal.
Please help to resolve.
Hi
Next time, please post your error message too.
Look at
http://msdn.microsoft.com/library/de...urity_2gmm.asp
about what you need to setup for Impersonation.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:38BF845C-A1E8-4264-8479-CE3C6DE2776B@.microsoft.com...
> I have two database servers with two different databases. I have the same
> windows login set up on both database servers with read access to the two
> different databases. I have linkedserver between the two database server.
> When I query using a SQL login I get a result set. When I use the windows
> login, I get an error - all else is equal.
> Please help to resolve.
windows login set up on both database servers with read access to the two
different databases. I have linkedserver between the two database server.
When I query using a SQL login I get a result set. When I use the windows
login, I get an error - all else is equal.
Please help to resolve.
Hi
Next time, please post your error message too.
Look at
http://msdn.microsoft.com/library/de...urity_2gmm.asp
about what you need to setup for Impersonation.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"spoons" <spoons@.discussions.microsoft.com> wrote in message
news:38BF845C-A1E8-4264-8479-CE3C6DE2776B@.microsoft.com...
> I have two database servers with two different databases. I have the same
> windows login set up on both database servers with read access to the two
> different databases. I have linkedserver between the two database server.
> When I query using a SQL login I get a result set. When I use the windows
> login, I get an error - all else is equal.
> Please help to resolve.
Monday, March 26, 2012
Linked views
I have a number of databases on the same SQL Server that all use tables from
a database called RestaurantMgr (also on ths server). I have been accessing
these table using linked views (eg. create view Branches as select BranchID,
BranchName from RestaurantMgr..Branches).
Is this the best way to do this ? Or should I be using something else like
replication, triggers to copy data accross, DTS ?
Linked views seem the simplest method, but if there is a reason not to use
this method, then I want to find out what it is.
Thanks, CraigIf these views aren't referencing large tables or they do not return large
result-sets, then this solution is good enough.
But for read-intensive operations it would be better to keep the actual data
in the same database - this way more appropriate indexes can be created if
necessary.
Of course changes to the original tables would need to be propagated to the
other databases through the use of triggers.
ML|||Thanks for the advice, ML.
When you say that data will "need to be propagated to the other databases
through the use of triggers", does that it is better to use triggers for thi
s
than DTS, replication or any other method.|||Well, triggers are immediate and simple to design. But the destination
database must be available to the trigger at the time of execution.
Maybe using replication might be more efficient. Of course it would need to
be immediate. But do consider whether you should allow the subscribers to
modify the data.
MLsql
a database called RestaurantMgr (also on ths server). I have been accessing
these table using linked views (eg. create view Branches as select BranchID,
BranchName from RestaurantMgr..Branches).
Is this the best way to do this ? Or should I be using something else like
replication, triggers to copy data accross, DTS ?
Linked views seem the simplest method, but if there is a reason not to use
this method, then I want to find out what it is.
Thanks, CraigIf these views aren't referencing large tables or they do not return large
result-sets, then this solution is good enough.
But for read-intensive operations it would be better to keep the actual data
in the same database - this way more appropriate indexes can be created if
necessary.
Of course changes to the original tables would need to be propagated to the
other databases through the use of triggers.
ML|||Thanks for the advice, ML.
When you say that data will "need to be propagated to the other databases
through the use of triggers", does that it is better to use triggers for thi
s
than DTS, replication or any other method.|||Well, triggers are immediate and simple to design. But the destination
database must be available to the trigger at the time of execution.
Maybe using replication might be more efficient. Of course it would need to
be immediate. But do consider whether you should allow the subscribers to
modify the data.
MLsql
Linked to Oracle
In my SQL Server I had a link to Oracle server and everything was working fine. Recently I moved all the SQL Server databases to a different SQL Server box and also installed Oracle client on this new server and restarted the server after installing Oracl
e client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?
Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.
e client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?
Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Linked to Oracle
In my SQL Server I had a link to Oracle server and everything was working fine. Recently I moved all the SQL Server databases to a different SQL Server box and also installed Oracle client on this new server and restarted the server after installing Oracle client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Linked to Oracle
In my SQL Server I had a link to Oracle server and everything was working fi
ne. Recently I moved all the SQL Server databases to a different SQL Server
box and also installed Oracle client on this new server and restarted the se
rver after installing Oracl
e client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.
ne. Recently I moved all the SQL Server databases to a different SQL Server
box and also installed Oracle client on this new server and restarted the se
rver after installing Oracl
e client but still getting the following error message.
Error 7399: OLE DB provider 'MSDAORA' reported an error'
Any suggestion?Can you create a UDL file and connect to the Oracle database using that.
To create a UDL file:
Create a text file on the desktop.
Rename the text file with a .udl extension
The icon should change to a printout with a computer in front of it.
Double click on the icon and fill in the necessary information.
On the Connection tab click the Test connection button.
If this fails then there is a general connectivity problem. If it succeeds
login as the SQL server startup account and test the connection with that
account.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Friday, March 23, 2012
servers to DB2 and AS400
Hi!
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.
I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to
configure DB2OLEDB drivers.
Could any one suggest how do go about doing this.
Do I need to buy Host integration server or is there any other way to do it.
your help is much appreciated
NalinaThere are more ways to skin a cat than there are cats. I can think of several ways to do this, from several different vendors.
My suggestion would be to approach your vendor of choice (probably IBM in this case), and ask them to propose a solution. If that solution works for you, and is affordable, I'd run with it. If not, open up the bidding to at least two more vendors (and inform your vendor of choice, of course), then see what happens.
The biggest problem that I see is that when you are doing this kind of integration, there are many factors that come into play. Without considerable knowledge of your circumstances it would be easy to lead you astray and cost you an enormous amount of extra money or create a solution that was only part of what you need.
-PatP
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.
I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to
configure DB2OLEDB drivers.
Could any one suggest how do go about doing this.
Do I need to buy Host integration server or is there any other way to do it.
your help is much appreciated
NalinaThere are more ways to skin a cat than there are cats. I can think of several ways to do this, from several different vendors.
My suggestion would be to approach your vendor of choice (probably IBM in this case), and ask them to propose a solution. If that solution works for you, and is affordable, I'd run with it. If not, open up the bidding to at least two more vendors (and inform your vendor of choice, of course), then see what happens.
The biggest problem that I see is that when you are doing this kind of integration, there are many factors that come into play. Without considerable knowledge of your circumstances it would be easy to lead you astray and cost you an enormous amount of extra money or create a solution that was only part of what you need.
-PatP
Monday, March 19, 2012
servers
Using SQL 2000, we have been using the linked server functionality to
connect two databases that up till now have resided on two separate
servers. Our network people have now moved those databases so they are
on the same server. Can we continue to use Linked Servers to pass
queries back and forth? (I know we don't HAVE to, but I need to know if
we CAN.) Thanks.Yes you can, just remember that any access using a linked server will use a
new connection.
Loopback Linked Servers
http://www.databasejournal.com/feat...10894_1438991_4
AMB
"Rick Charnes" wrote:
> Using SQL 2000, we have been using the linked server functionality to
> connect two databases that up till now have resided on two separate
> servers. Our network people have now moved those databases so they are
> on the same server. Can we continue to use Linked Servers to pass
> queries back and forth? (I know we don't HAVE to, but I need to know if
> we CAN.) Thanks.
>
connect two databases that up till now have resided on two separate
servers. Our network people have now moved those databases so they are
on the same server. Can we continue to use Linked Servers to pass
queries back and forth? (I know we don't HAVE to, but I need to know if
we CAN.) Thanks.Yes you can, just remember that any access using a linked server will use a
new connection.
Loopback Linked Servers
http://www.databasejournal.com/feat...10894_1438991_4
AMB
"Rick Charnes" wrote:
> Using SQL 2000, we have been using the linked server functionality to
> connect two databases that up till now have resided on two separate
> servers. Our network people have now moved those databases so they are
> on the same server. Can we continue to use Linked Servers to pass
> queries back and forth? (I know we don't HAVE to, but I need to know if
> we CAN.) Thanks.
>
servers
I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
was created recently by restoring all the system and user databases from
another server which has now been retired. Now I have both the servers linked
from the other server and they both show up in the Enterprise Manager.
However, I can only access the server running SP4 from the server running
SP3, not vice versa. It gives an error:
Server: Msg 17, Level 16, State 1, Line 2
SQL Server does not exist or access denied.
Any insight will be greatly appreciated. Thanks.Are you able to ping old server from the new one?
Manu
"sharman" wrote:
> I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
> was created recently by restoring all the system and user databases from
> another server which has now been retired. Now I have both the servers linked
> from the other server and they both show up in the Enterprise Manager.
> However, I can only access the server running SP4 from the server running
> SP3, not vice versa. It gives an error:
> Server: Msg 17, Level 16, State 1, Line 2
> SQL Server does not exist or access denied.
> Any insight will be greatly appreciated. Thanks.
was created recently by restoring all the system and user databases from
another server which has now been retired. Now I have both the servers linked
from the other server and they both show up in the Enterprise Manager.
However, I can only access the server running SP4 from the server running
SP3, not vice versa. It gives an error:
Server: Msg 17, Level 16, State 1, Line 2
SQL Server does not exist or access denied.
Any insight will be greatly appreciated. Thanks.Are you able to ping old server from the new one?
Manu
"sharman" wrote:
> I have two servers running SQL Server 2000 SP3 and SP4. The one running SP4
> was created recently by restoring all the system and user databases from
> another server which has now been retired. Now I have both the servers linked
> from the other server and they both show up in the Enterprise Manager.
> However, I can only access the server running SP4 from the server running
> SP3, not vice versa. It gives an error:
> Server: Msg 17, Level 16, State 1, Line 2
> SQL Server does not exist or access denied.
> Any insight will be greatly appreciated. Thanks.
servers
Hello (again!)
We have a new 2005 server, and I am trying to link our old 2000 server so I can use the old databases in new server.
This is what I done:
In 2005:
EXEC sp_addlinkedserver oldServer
--completed fine
then:
EXEC sp_addlinkedsrvlogin oldServer, 'false', NULL, 'Administrator', password
--completed fine
But whenI run the query I get the error:
OLE DB provider "SQLNCLI" for linked server "oldServer" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'Administrator'.
Any ideas??I would recommend to use the Management Studio in order to create the Linked Server.
-Press Right-Click on the node \YourServer\Server Objects\Linked Servers and choose "New Linked Server"
-On the dialog, write the OldServer name in the "Linked server" textbox.
-On the "Server type" option button, choose "SQL Server".
-In then "Security" page, you have to configure how the local server is going to login to the remote server. If you use Windows Authentication, and your user has permissions in the remote server, you can choose "Be made using the login's current security context". But if you want to specify a remote user and password (SQL Server Authentication), you can write that in "Be made using this security context".
-In the "Server options" page, make the RPC properties to TRUE if you want to invoke remote stored procedures.|||I just created a linked server on SQL 2005 point to a SQL 2000 box.
Then I scripted it off; here are the results. Note that I accepted all default options and used a specific user on the remote server.
YMMV
[code]
/****** Object: LinkedServer [REMOTESERVER] Script Date: 04/12/2007 16:58:55 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'REMOTESERVER', @.srvproduct=N'REMOTESERVER', @.provider=N'SQLNCLI', @.datasrc=N'REMOTESERVER'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'REMOTESERVER',@.useself=N'False',@.loc allogin=NULL,@.rmtuser=N'remote_user',@.rmtpassword= '########'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'use remote collation', @.optvalue=N'true'
[code]
Regards,
hmscott
We have a new 2005 server, and I am trying to link our old 2000 server so I can use the old databases in new server.
This is what I done:
In 2005:
EXEC sp_addlinkedserver oldServer
--completed fine
then:
EXEC sp_addlinkedsrvlogin oldServer, 'false', NULL, 'Administrator', password
--completed fine
But whenI run the query I get the error:
OLE DB provider "SQLNCLI" for linked server "oldServer" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'Administrator'.
Any ideas??I would recommend to use the Management Studio in order to create the Linked Server.
-Press Right-Click on the node \YourServer\Server Objects\Linked Servers and choose "New Linked Server"
-On the dialog, write the OldServer name in the "Linked server" textbox.
-On the "Server type" option button, choose "SQL Server".
-In then "Security" page, you have to configure how the local server is going to login to the remote server. If you use Windows Authentication, and your user has permissions in the remote server, you can choose "Be made using the login's current security context". But if you want to specify a remote user and password (SQL Server Authentication), you can write that in "Be made using this security context".
-In the "Server options" page, make the RPC properties to TRUE if you want to invoke remote stored procedures.|||I just created a linked server on SQL 2005 point to a SQL 2000 box.
Then I scripted it off; here are the results. Note that I accepted all default options and used a specific user on the remote server.
YMMV
[code]
/****** Object: LinkedServer [REMOTESERVER] Script Date: 04/12/2007 16:58:55 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'REMOTESERVER', @.srvproduct=N'REMOTESERVER', @.provider=N'SQLNCLI', @.datasrc=N'REMOTESERVER'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'REMOTESERVER',@.useself=N'False',@.loc allogin=NULL,@.rmtuser=N'remote_user',@.rmtpassword= '########'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'REMOTESERVER', @.optname=N'use remote collation', @.optvalue=N'true'
[code]
Regards,
hmscott
Friday, March 9, 2012
server with Oracle
I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
me to getting the same problem so u got the solution please tell to me
my Address:
A. Shiva Prasad
GIS Developer
MIDWEST INFO TECH Pvt Limited
70, kanakapura main road ,J.P. nagar 6th phase
opposite Family mart
Bangalore - INDIA
mail id : shiva.prasad@.midwestinfotech.com alternate mail : asp.347@.gmail.com
mobile : +919886451711
|||This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance
server with Oracle
I installed the Oracle client on my computer, and I can connect to oracle databases using SqlPlus, however when I try setting up the linked server I get the following error after I try executing a query.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].me to getting the same problem so u got the solution please tell to me
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12560: TNS:protocol adapter error
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].me to getting the same problem so u got the solution please tell to me
my Address:
A. Shiva Prasad
GIS Developer
MIDWEST INFO TECH Pvt Limited
70, kanakapura main road ,J.P. nagar 6th phase
opposite Family mart
Bangalore - INDIA
mail id : shiva.prasad@.midwestinfotech.com alternate mail : asp.347@.gmail.com
mobile : +919886451711
|||This looks more like a protocol adapter error. This indicates that Oracle client does not know what instance to connect to or what TNS alias to use. Try fixing the tnsname.ora file so that it points to the correct instance
Wednesday, March 7, 2012
server using SQL Native Client
We have a SQL 2005 SP2 64 bit server that needs to communicate with our 32 bit AS400 server. We have stored procedures in databases that query the AS400 (generally just select statements). I am not understanding the procedure to connect to the AS400 now that the OLE DB for ODBC provider no longer exists.
I know how to create a system DSN (and that it will be located in C:\WINDOWS\SysWOW64\odbcad32.exe). I called the System DSN 'ConnectMe'. The server I am trying to access is 'ServerA'. I have filled out the following to create a linked server:
Linked Server: AS400
Provider: SQL Native Client
Product Name: AS400 (from what I understand, this holds no value and only serves as a description)
Data source: ServerA
Provider String: dsn::=ConnectMe (same as system DSN)
Catalog:<I left it blank>
Under the security tab I have the remote username and password that should be used.
When I try to create the linked server, I get the error:
"OLE DB provider "SQLNCLI" for linked server "AS400" returned message "An error has occured while establishing a connection to the server. ..may be be caused by the fact that under the default settings SQL Server does not allow remote connections"
Remote connections for both TCP/IP and named pipes is enabled and SQL services have restarted to enable that. What am I missing?
64-bit OLE DB provider for ODBC is going to be available with Vista SP, or Longhorn.
SQL Native Client can only be used to connect to a Microsoft SQL server.
You might also want to check the IBM's OLE DB provider for AS400 and see if you can use it. Have a look at http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaik/rzaikoledbprovider.htm
|||Thanks for the reply. I read from various forums that SQL native client can also access ODBC data (http://technet.microsoft.com/en-us/library/ms131415.aspx) but I haven't been able to get it to work.
I tried that OLE DB provider from AS400 but performance is terrible (using OLE DB for ODBC on a 32 bit machine takes around 2 minutes to query one of our tables and using the IBM driver, it takes over 6 minutes to query the same table).
I guess our SQL 2005 farm will have to go back to 32 bit until Longhorn which is disappointing but I guess that's what happens sometimes.
Thanks anyways for your help.
server transfer
Does anyone know the most efficient way of transfering linked servers between databases with out manually recreating them?
I can't seem to script them, I looked into the sysservers table, I don't want to insert into a system table until I have to?
There has to be a better way, has anyone run across this in SQL 2000?
Thanks
Hi,
All are kept in sysservers system table both in sql2k and sql2k5
select * from master.dbo.sysservers where isremote = 1
Friday, February 24, 2012
server to Oracle 9i
I'm connecting to Oracle 8i databases from SQL Server through Linked Server. Now I need to connect to Oracle9i databases from the same SQL Server. What currently I'm doing is: (already installed Oracle 9i client tools on the Server machine) changing the r
egistry setting of "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxO CI]"
to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
Again, if I want to connect to Oracle 8i databases, again I'm changing the registry to point to the 8i DLLs.
My question is: is there any possibility of connecting to both Oracle 8i and 9i databases from one SQL Server machine?
Thanks..
Siva
"Siva" <siva116@.yahoo.com> wrote in message
news:19BE8276-E801-490F-B9B5-EC46DDC600CA@.microsoft.com...
> I'm connecting to Oracle 8i databases from SQL Server through Linked
Server. Now I need to connect to Oracle9i databases from the same SQL
Server. What currently I'm doing is: (already installed Oracle 9i client
tools on the Server machine) changing the registry setting of
"[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxO CI]"
> to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
> Again, if I want to connect to Oracle 8i databases, again I'm changing the
registry to point to the 8i DLLs.
> My question is: is there any possibility of connecting to both Oracle 8i
and 9i databases from one SQL Server machine?
The Oracle client version is not tied to the Oracle server version. Either
client version should be able to connect to either server version, so just
user the Oracle 9i client.
David
egistry setting of "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxO CI]"
to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
Again, if I want to connect to Oracle 8i databases, again I'm changing the registry to point to the 8i DLLs.
My question is: is there any possibility of connecting to both Oracle 8i and 9i databases from one SQL Server machine?
Thanks..
Siva
"Siva" <siva116@.yahoo.com> wrote in message
news:19BE8276-E801-490F-B9B5-EC46DDC600CA@.microsoft.com...
> I'm connecting to Oracle 8i databases from SQL Server through Linked
Server. Now I need to connect to Oracle9i databases from the same SQL
Server. What currently I'm doing is: (already installed Oracle 9i client
tools on the Server machine) changing the registry setting of
"[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxO CI]"
> to the respective Oracle 9i DLLs, overriding Oracle 8i DLLS.
> Again, if I want to connect to Oracle 8i databases, again I'm changing the
registry to point to the 8i DLLs.
> My question is: is there any possibility of connecting to both Oracle 8i
and 9i databases from one SQL Server machine?
The Oracle client version is not tied to the Oracle server version. Either
client version should be able to connect to either server version, so just
user the Oracle 9i client.
David
Subscribe to:
Posts (Atom)