Creating a view between 2 different Datasources

I want to create a SQL server view that combines 2 tables on 2 different
servers. Both of the servers are in house and I have permission to view
both of them. What is the easiest way to go about doing this?
I am able to create a view with multiple tables on one server but have
never had to create one from multiple datasources.
Let's say that their names are Server_1 and Server_2.
Thanks in advance.
- Will
*** Sent via Developersdex http://www.examnotes.net ***Create a linked server so that Server_1 can see Server_2. Then your view
can work just like any other query:
SELECT <col_list>
FROM databasename.dbo.tablename t1
INNER JOIN [server_2].databasename.dbo.tablename t2
ON t1.key = t2.key;
"Will Chamberlain" <will.chamberlain@.devdex.com> wrote in message
news:uR950C6GGHA.2212@.TK2MSFTNGP15.phx.gbl...
>I want to create a SQL server view that combines 2 tables on 2 different
> servers. Both of the servers are in house and I have permission to view
> both of them. What is the easiest way to go about doing this?
> I am able to create a view with multiple tables on one server but have
> never had to create one from multiple datasources.
> Let's say that their names are Server_1 and Server_2.
> Thanks in advance.
> - Will
> *** Sent via Developersdex http://www.examnotes.net ***

Creating a Standby Server

Hi,
SQL2000 Std edition.
I'm creating a standby server to use for disaster recovery, etc.
There are 2 servers involved, the primary and the standby, that's all.
I don't know which SQL system databases I need to restore from the primary
to the standby.
I've restored all the application databases with no problem, but can't use
the application on the standby because of "user unknown" problems. Obviously
I've not got essential user role and permission data.
However I'm not sure if I can just restore all the following with no
problems. (I understand I can't restore any transaction logs for the master
database.)
master
model
msdb
pubs
tempdb
My concern is that if I restore them all the standby server will then
contain irrelevant data or it will think its a different server!
Thanks
Steve W
I suggest you read the information about log shipping in below documents:
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
And also search KB for sp_help_revlogins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SteveW" <SteveW@.=No=Spam.org> wrote in message news:%23bMmp%23qhEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> SQL2000 Std edition.
> I'm creating a standby server to use for disaster recovery, etc.
> There are 2 servers involved, the primary and the standby, that's all.
> I don't know which SQL system databases I need to restore from the primary
> to the standby.
> I've restored all the application databases with no problem, but can't use
> the application on the standby because of "user unknown" problems. Obviously
> I've not got essential user role and permission data.
> However I'm not sure if I can just restore all the following with no
> problems. (I understand I can't restore any transaction logs for the master
> database.)
> master
> model
> msdb
> pubs
> tempdb
> My concern is that if I restore them all the standby server will then
> contain irrelevant data or it will think its a different server!
> Thanks
> Steve W
>
|||Hello
Please refer to the following article for more information :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Creating a Standby Server

Hi,
SQL2000 Std edition.
I'm creating a standby server to use for disaster recovery, etc.
There are 2 servers involved, the primary and the standby, that's all.
I don't know which SQL system databases I need to restore from the primary
to the standby.
I've restored all the application databases with no problem, but can't use
the application on the standby because of "user unknown" problems. Obviously
I've not got essential user role and permission data.
However I'm not sure if I can just restore all the following with no
problems. (I understand I can't restore any transaction logs for the master
database.)
master
model
msdb
pubs
tempdb
My concern is that if I restore them all the standby server will then
contain irrelevant data or it will think its a different server!
Thanks
Steve WI suggest you read the information about log shipping in below documents:
http://www.microsoft.com/technet/pr...oy/sqlhalp.mspx
And also search KB for sp_help_revlogins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SteveW" <SteveW@.=No=Spam.org> wrote in message news:%23bMmp%23qhEHA.556@.tk2msftngp13.phx.gb
l...
> Hi,
> SQL2000 Std edition.
> I'm creating a standby server to use for disaster recovery, etc.
> There are 2 servers involved, the primary and the standby, that's all.
> I don't know which SQL system databases I need to restore from the primary
> to the standby.
> I've restored all the application databases with no problem, but can't use
> the application on the standby because of "user unknown" problems. Obvious
ly
> I've not got essential user role and permission data.
> However I'm not sure if I can just restore all the following with no
> problems. (I understand I can't restore any transaction logs for the maste
r
> database.)
> master
> model
> msdb
> pubs
> tempdb
> My concern is that if I restore them all the standby server will then
> contain irrelevant data or it will think its a different server!
> Thanks
> Steve W
>|||Hello
Please refer to the following article for more information :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Creating a Standby Server

Hi,
SQL2000 Std edition.
I'm creating a standby server to use for disaster recovery, etc.
There are 2 servers involved, the primary and the standby, that's all.
I don't know which SQL system databases I need to restore from the primary
to the standby.
I've restored all the application databases with no problem, but can't use
the application on the standby because of "user unknown" problems. Obviously
I've not got essential user role and permission data.
However I'm not sure if I can just restore all the following with no
problems. (I understand I can't restore any transaction logs for the master
database.)
master
model
msdb
pubs
tempdb
My concern is that if I restore them all the standby server will then
contain irrelevant data or it will think its a different server!
Thanks
Steve WI suggest you read the information about log shipping in below documents:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
And also search KB for sp_help_revlogins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SteveW" <SteveW@.=No=Spam.org> wrote in message news:%23bMmp%23qhEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> SQL2000 Std edition.
> I'm creating a standby server to use for disaster recovery, etc.
> There are 2 servers involved, the primary and the standby, that's all.
> I don't know which SQL system databases I need to restore from the primary
> to the standby.
> I've restored all the application databases with no problem, but can't use
> the application on the standby because of "user unknown" problems. Obviously
> I've not got essential user role and permission data.
> However I'm not sure if I can just restore all the following with no
> problems. (I understand I can't restore any transaction logs for the master
> database.)
> master
> model
> msdb
> pubs
> tempdb
> My concern is that if I restore them all the standby server will then
> contain irrelevant data or it will think its a different server!
> Thanks
> Steve W
>|||Hello
Please refer to the following article for more information :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:

> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". W
e
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> Access
> server
> SQL.
> in
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...[vbcol=seagreen]
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
>
We[vbcol=seagreen]
for an[vbcol=seagreen]
with[vbcol=seagreen]
me[vbcol=seagreen]|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:

> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won'
t
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> We
> for an
> with
> me
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...[vbcol=seagreen]
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
>
evaluated[vbcol=seagreen]
won't[vbcol=seagreen]
same[vbcol=seagreen]
SQL".[vbcol=seagreen]
an[vbcol=seagreen]
linked[vbcol=seagreen]
server as[vbcol=seagreen]
server[vbcol=seagreen]
using[vbcol=seagreen]
message[vbcol=seagreen]
getting[vbcol=seagreen]

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
..mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
Dion
I would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion
|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:

> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> Access
> server
> SQL.
> in
>
>
|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...[vbcol=seagreen]
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
We[vbcol=seagreen]
for an[vbcol=seagreen]
with[vbcol=seagreen]
me[vbcol=seagreen]
|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:

> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won't
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> We
> for an
> with
> me
>
>
|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...[vbcol=seagreen]
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
evaluated[vbcol=seagreen]
won't[vbcol=seagreen]
same[vbcol=seagreen]
SQL".[vbcol=seagreen]
an[vbcol=seagreen]
linked[vbcol=seagreen]
server as[vbcol=seagreen]
server[vbcol=seagreen]
using[vbcol=seagreen]
message[vbcol=seagreen]
getting[vbcol=seagreen]

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:
> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > I am trying to use linked servers as a means of accessing files in an
> Access
> > database. I have unsuccessfully tried 2 methods:
> > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> server
> > for Access tables, unless the Access file exists on the same server as
> SQL.
> > This seems odd to me. Is this true? Can I create a linked server for an
> > .mdb file that exists on a different server? And can I do this using
> > sp_addlinkedserver?
> > 2) using OPENROWSET. I get an "Authentication failed" error message with
> > the following:
> > SELECT *
> > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp\z.mdb';'';'', Untitled)
> > Do I need to specify some kind of security? What am I missing?
> >
> > My knowledge on security issues is very limited. Any help on getting me
> in
> > the right direction would be appreciated. I am using SQL 8.00.194
> >
> > Thanks
> > Dion
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
> > I would recommend looking at this thread in the
> > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
We
> > just reviewed it there.
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > I am trying to use linked servers as a means of accessing files in an
> > Access
> > > database. I have unsuccessfully tried 2 methods:
> > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > server
> > > for Access tables, unless the Access file exists on the same server as
> > SQL.
> > > This seems odd to me. Is this true? Can I create a linked server
for an
> > > .mdb file that exists on a different server? And can I do this using
> > > sp_addlinkedserver?
> > > 2) using OPENROWSET. I get an "Authentication failed" error message
with
> > > the following:
> > > SELECT *
> > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > 'c:\temp\z.mdb';'';'', Untitled)
> > > Do I need to specify some kind of security? What am I missing?
> > >
> > > My knowledge on security issues is very limited. Any help on getting
me
> > in
> > > the right direction would be appreciated. I am using SQL 8.00.194
> > >
> > > Thanks
> > > Dion
> >
> >
> >|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:
> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won't
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > So the Access file needs to be either on the local drive, or on the same
> > server as sql, right? Thanks much for the thread.
> >
> > Dion
> >
> >
> > "Simon Worth" wrote:
> >
> > > I would recommend looking at this thread in the
> > > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
> We
> > > just reviewed it there.
> > > --
> > > Simon Worth
> > >
> > >
> > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > I am trying to use linked servers as a means of accessing files in an
> > > Access
> > > > database. I have unsuccessfully tried 2 methods:
> > > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > > server
> > > > for Access tables, unless the Access file exists on the same server as
> > > SQL.
> > > > This seems odd to me. Is this true? Can I create a linked server
> for an
> > > > .mdb file that exists on a different server? And can I do this using
> > > > sp_addlinkedserver?
> > > > 2) using OPENROWSET. I get an "Authentication failed" error message
> with
> > > > the following:
> > > > SELECT *
> > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > Do I need to specify some kind of security? What am I missing?
> > > >
> > > > My knowledge on security issues is very limited. Any help on getting
> me
> > > in
> > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > >
> > > > Thanks
> > > > Dion
> > >
> > >
> > >
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
> > The .mdb database file must reside on the server. data_source is
evaluated
> > on the server, not the client, and the path must be valid on the server.
> >
> > I didn't try it with the access db on the local drive. I assume that
won't
> > work, as it's counter to what BOL says. But try it out, and see if it
> > works.
> >
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > > So the Access file needs to be either on the local drive, or on the
same
> > > server as sql, right? Thanks much for the thread.
> > >
> > > Dion
> > >
> > >
> > > "Simon Worth" wrote:
> > >
> > > > I would recommend looking at this thread in the
> > > > microsoft.public.sqlserver.dts newsgroup with subject "Access and
SQL".
> > We
> > > > just reviewed it there.
> > > > --
> > > > Simon Worth
> > > >
> > > >
> > > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > > I am trying to use linked servers as a means of accessing files in
an
> > > > Access
> > > > > database. I have unsuccessfully tried 2 methods:
> > > > > 1) using sp_addlinkedserver. I am told that I cannot create a
linked
> > > > server
> > > > > for Access tables, unless the Access file exists on the same
server as
> > > > SQL.
> > > > > This seems odd to me. Is this true? Can I create a linked
server
> > for an
> > > > > .mdb file that exists on a different server? And can I do this
using
> > > > > sp_addlinkedserver?
> > > > > 2) using OPENROWSET. I get an "Authentication failed" error
message
> > with
> > > > > the following:
> > > > > SELECT *
> > > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > > Do I need to specify some kind of security? What am I missing?
> > > > >
> > > > > My knowledge on security issues is very limited. Any help on
getting
> > me
> > > > in
> > > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > > >
> > > > > Thanks
> > > > > Dion
> > > >
> > > >
> > > >
> >
> >
> >

creating a link server into oracle

Hi Folks,
Pretty new to linked servers, however I need to create a linked server from
MS SQL Server 2000 into Orcale database sitting on a UNIX platform. I already
have successfull DTS application which takes info from the Orcale database
into MS SQL, using third party Oracle driver via DSN entry. I'm now need to
created a linked server into Orcale, have succesfull created link servers
between MS SQL servers but NOT between MS SQL and orcale. I have checked the
web but finding it hard to come across good instructions. I would appreciate
if anyone can point me to a good web sites or instructions. Can I use my
third party driver(DSN) in creating a linked server?
thanks,
liamo
Install the Oracle client on the SQL Server box.
Configure the client side (SQL Server box) tnsnames, Oracle
alias as needed for your Oracle environment.
Configure a linked server, following the books online topic:
OLE DB Provider for Oracle
as well as the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Thu, 9 Mar 2006 08:31:31 -0800, Liam Mac
<LiamMac@.discussions.microsoft.com> wrote:

>Hi Folks,
>Pretty new to linked servers, however I need to create a linked server from
>MS SQL Server 2000 into Orcale database sitting on a UNIX platform. I already
>have successfull DTS application which takes info from the Orcale database
>into MS SQL, using third party Oracle driver via DSN entry. I'm now need to
>created a linked server into Orcale, have succesfull created link servers
>between MS SQL servers but NOT between MS SQL and orcale. I have checked the
>web but finding it hard to come across good instructions. I would appreciate
>if anyone can point me to a good web sites or instructions. Can I use my
>third party driver(DSN) in creating a linked server?
>thanks,
>liamo

creating a link server into oracle

Hi Folks,
Pretty new to linked servers, however I need to create a linked server from
MS SQL Server 2000 into Orcale database sitting on a UNIX platform. I alread
y
have successfull DTS application which takes info from the Orcale database
into MS SQL, using third party Oracle driver via DSN entry. I'm now need to
created a linked server into Orcale, have succesfull created link servers
between MS SQL servers but NOT between MS SQL and orcale. I have checked the
web but finding it hard to come across good instructions. I would appreciate
if anyone can point me to a good web sites or instructions. Can I use my
third party driver(DSN) in creating a linked server?
thanks,
liamoInstall the Oracle client on the SQL Server box.
Configure the client side (SQL Server box) tnsnames, Oracle
alias as needed for your Oracle environment.
Configure a linked server, following the books online topic:
OLE DB Provider for Oracle
as well as the following article:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in
SQL Server
http://support.microsoft.com/?id=280106
-Sue
On Thu, 9 Mar 2006 08:31:31 -0800, Liam Mac
<LiamMac@.discussions.microsoft.com> wrote:

>Hi Folks,
>Pretty new to linked servers, however I need to create a linked server from
>MS SQL Server 2000 into Orcale database sitting on a UNIX platform. I alrea
dy
>have successfull DTS application which takes info from the Orcale database
>into MS SQL, using third party Oracle driver via DSN entry. I'm now need to
>created a linked server into Orcale, have succesfull created link servers
>between MS SQL servers but NOT between MS SQL and orcale. I have checked th
e
>web but finding it hard to come across good instructions. I would appreciat
e
>if anyone can point me to a good web sites or instructions. Can I use my
>third party driver(DSN) in creating a linked server?
>thanks,
>liamo

Creating a DTS package

I've created a DTS package and now I need to distribute it to
different servers.

I've been looking for a way to automatically/programatically create a
DTS package, but have not found anything definite.

From the DTS package itself, I see where I can save the dts package as
a structured file, with the name XXXXX.dts. Once I have that DTS
file, how dow I turn it back into a dts package in Enterprise manager?
I don't want to have to manually create the package for 300+
servers...

Thanks,
JenniferYou can load a DTS structured storage file from EM by right-clicking on
the Data Transformation Services folder and selecting Open Package. You
can then select Package --> Save As in order to save it locally.

This can be a bit tedious if you have a lot of servers. The VBScript
example below will load your DTS structured storage file and save it to
multiple servers using a trusted connection. See the Books Online for
details.

Dim DtsPackage
Set DtsPackage = CreateObject("DTS.Package2")
DtsPackage.LoadFromStorageFile "C:\MyDtsPackages\MyDtsPackage.dts", ""
DtsPackage.SaveToSqlServer "MyServer1", , , 256
DtsPackage.SaveToSqlServer "MyServer2", , , 256
DtsPackage.SaveToSqlServer "MyServer3", , , 256

Note that you can execute a DTS package directly from a structured
storage file using DTSRUN. If your servers have access to a shared
network location, you could execute the package with a UNC path rather
than saving the package locally on each server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0311041237.15641d35@.posting.google.c om...
> I've created a DTS package and now I need to distribute it to
> different servers.
> I've been looking for a way to automatically/programatically create a
> DTS package, but have not found anything definite.
> From the DTS package itself, I see where I can save the dts package as
> a structured file, with the name XXXXX.dts. Once I have that DTS
> file, how dow I turn it back into a dts package in Enterprise manager?
> I don't want to have to manually create the package for 300+
> servers...
> Thanks,
> Jennifer

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

GO

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Creating a Database from multiple databases accross multiple servers

Hi,

I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.

I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.

I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!

Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):

SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLO\ACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a

I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC

Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)

Thanks

Which version of SQL Server your using (2000 or 2005)?|||Hi I'm using the Express 2005 Edition|||

'ad-hoc dist' property controls whether or not the use of OpenRowset/Datasource is allowed. If it's not enabled, you will not be able to execute the query, regardless of the data access provider you use.

Your only option is to create a linked server and use OpenQuery() or 4-part name query.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

|||

Thanks for the reply

I have managed to create a linked server using the following bit of code:

Code Snippet

EXEC sp_addlinkedserver

@.server = 'APPOLO/ACT7',

@.srvproduct = 'SQLServr OLEDB Provider',

@.provider = 'MSDASQL',

@.datasrc='ACT7'

GO

I then created and ran the following statement:

Code Snippet

SELECT *

FROM OPENQUERY(APPOLO/ACT, 'SELECT * FROM mdc1.TBL_CONTACTS')

I then get the following error message:

"Incorrect Syntac near '/'"

I have tried only using "APPOLO", "ACT7", "APPOLO.ACT7" and putting them all in quotation marks but it does not work.

Any Ideas?

|||How about:

[APPOLO/ACT]

HTH!|||

Great - that solved that problem -

I'm now getting a message to say that authentication failed - I guess this is because I havent provided the sa password - how do i enter this so that I can connect?

Thanks

Tom

|||

You use this to set the login.

Code Snippet

EXEC sp_addlinkedsrvlogin 'APPOLO/ACT', 'false', NULL, 'sa', 'Password'

Create view with tables from 2 different servers

Is it possible to create a view on one SQL server and add a table to the
view from another SQL server?
Tim"Tim Kelley" <tkelley@.company.com> wrote in news:e8Zfl0atGHA.4968
@.TK2MSFTNGP03.phx.gbl:

> Is it possible to create a view on one SQL server and add a table to the
> view from another SQL server?
I cannot remember anything stating that it does not work, but I guess
you'll have to use sp_addlinkedserver first.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||You need to add the remote server as a linked server, using sp_addlinkedserv
er.
Then you can use the four part naming convention.
SELECT
l.Column1
, l.Column2
, r.Column1
, r.Column2
FROM LocalTable l
JOIN RemoteServer.DatabaseName.Schema.RemoteTable r
ON l.PKColumn = r.FKColumn
WHERE {criteria}
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message news:Xns9812F31EE
656Folekristianbangaas@.207.46.248.16...
> "Tim Kelley" <tkelley@.company.com> wrote in news:e8Zfl0atGHA.4968
> @.TK2MSFTNGP03.phx.gbl:
>
>
> I cannot remember anything stating that it does not work, but I guess
> you'll have to use sp_addlinkedserver first.
>
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Create view with tables from 2 different servers

Is it possible to create a view on one SQL server and add a table to the
view from another SQL server?
Tim"Tim Kelley" <tkelley@.company.com> wrote in news:e8Zfl0atGHA.4968
@.TK2MSFTNGP03.phx.gbl:
> Is it possible to create a view on one SQL server and add a table to the
> view from another SQL server?
I cannot remember anything stating that it does not work, but I guess
you'll have to use sp_addlinkedserver first.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||This is a multi-part message in MIME format.
--=_NextPart_000_045C_01C6B57F.02DE1260
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to add the remote server as a linked server, using =sp_addlinkedserver.
Then you can use the four part naming convention.
SELECT l.Column1
, l.Column2
, r.Column1
, r.Column2
FROM LocalTable l
JOIN RemoteServer.DatabaseName.Schema.RemoteTable r
ON l.PKColumn =3D r.FKColumn
WHERE {criteria}
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Ole Kristian Bang=E5s" <olekristian.bangas@.masterminds.no> wrote in =message news:Xns9812F31EE656Folekristianbangaas@.207.46.248.16...
> "Tim Kelley" <tkelley@.company.com> wrote in news:e8Zfl0atGHA.4968
> @.TK2MSFTNGP03.phx.gbl:
> >> Is it possible to create a view on one SQL server and add a table to =the >> view from another SQL server?
> > I cannot remember anything stating that it does not work, but I guess > you'll have to use sp_addlinkedserver first.
> > -- > Ole Kristian Bang=E5s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
--=_NextPart_000_045C_01C6B57F.02DE1260
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You need to add the remote server as a =linked server, using sp_addlinkedserver.
Then you can use the four part naming convention.
SELECT
l.Column1
, =l.Column2
, =r.Column1
, =r.Column2
FROM LocalTable l
JOIN RemoteServer.DatabaseName.Schema.RemoteTable r
=ON l.PKColumn =3D r.FKColumn
WHERE {criteria}
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Ole Kristian Bang=E5s" wrote in message news:Xns9812F31EE656Folekristianbangaas@.207.46.248.16=...> ="Tim Kelley" =wrote in news:e8Zfl0atGHA.4968> @.TK2MSFTNGP03.phx.gbl:> > Is it possible to create a =view on one SQL server and add a table to the > view from another SQL =server?> > I cannot remember anything stating that it does =not work, but I guess > you'll have to use sp_addlinkedserver =first.> > -- > Ole Kristian Bang=E5s> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

---

--=_NextPart_000_045C_01C6B57F.02DE1260--

Create view with data from multiple servers

Hi everyone,

I have 5 servers, all with identical databases just different data. I
have a rather lengthy SQL statement (in a View) to hit one database and
pull-in certain data, but I'd like to somehow run this same SQL
statement within the view but hit all 5 servers so we don't have 5
different versions of this data to mess with.

I'm not opposed to creating an update query in a stored procedure to
hit all 5 databases and update a table or even do this within a DTS,
but I'd prefer to keep it as simple as possible and as dynamic so the
users can simply run the view and get live data anytime based on all 5
tables.

Is this possible ?

Thanks,

rlanglySee "Creating a Partitioned View" in BOL.

AMB

--
Message posted via http://www.sqlmonster.com|||Ringo Langly (rlangly@.gmail.com) writes:
> I have 5 servers, all with identical databases just different data. I
> have a rather lengthy SQL statement (in a View) to hit one database and
> pull-in certain data, but I'd like to somehow run this same SQL
> statement within the view but hit all 5 servers so we don't have 5
> different versions of this data to mess with.
> I'm not opposed to creating an update query in a stored procedure to
> hit all 5 databases and update a table or even do this within a DTS,
> but I'd prefer to keep it as simple as possible and as dynamic so the
> users can simply run the view and get live data anytime based on all 5
> tables.

While you could set up a distributed partitioned view of the servers,
as suggested by Alejandro, but distributed partitioned views is
not really for the faint of heart. Anyway, it requires that the
tables has a partitioning column.

You can still up a view over the tables with UNION ALL, but the view
may not be updatable, and it sounds like you want to do this for an
update operation, but I might be misunderstanding something.

It's a little difficult to say that much more with that small amount
of information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create View from 2 servers.

Hi,
Can we create a view from databases residing on different servers?
Thanks,
Harriet.yes, you can do that. Setup one server as a linked server on the other one and you can access objects in the linked server by using their full names (server.database.owner.objectname). See for more info on setting up a linked server in books online.|||hey, thanks a ton buddy. It was of great help.
Thanks once again

btw, do you have any idea as to how I would be able to run udfs on version 7.0?
Cheers!!
Hsm