Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server i
n
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/d...br />
5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...

>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle serve
r
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.sql

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server in
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...
>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle server
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.

Linked server and replication

Hello,

I have one server using SQL Server Express (Server1\SqlExpress - Sql Authentication) and another one using Sql Server 2005 (Server2 - Windows Authentication)

My Server2 is the publisher for a replication and Server1\SqlExpress is the client for this replication.

For any reason that has been discussed in details with Raymond Mak in some other threads, some triggers and stored procedures cannot be replicated via the replication, so I use a post replication script.

To avoid having to maintaint coherent my source control, my master DB and the post replication script I wanted to create a script that will extract from the master DB the source code of the problematic triggers and stored procedures.

To do that, here the kind of script I use (example for the stored procedure):

DECLARE @.query NVARCHAR(max)

select @.query = routine_definition from openquery(MASTER,

'

SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_name = N''spU_GUI_AppliquePerteAgrement''

')

EXEC (@.query)

So my script will be run on the subscription client and will get the script to the master db using a linked server. So, here is the step I have followed :

Create a domain user (let's call it localAdmin)

Set localAdmin as a local administrator for Server1 and Server2

Update the properties of my subscription to impersonate localAdmin on the publisher and on the subscriber

Create a linked server on Server1, linked to Server2. (for security, I have tried either "Be made without using a security context" and "Be made using the current security context")

Then I am able to run my script on Server1\SqlExpress but when I launch the replication, here is the error I receive : "Login failed for Autorite NT \ Anonymous Login"

Do you have any idea of what can happen ?

Thanks,

Pierre-Emmanuel

When you configure to use "Be made using the current security context", SQL Server uses windows NT kerberos delegation to authenticate client to the linked server. If the delegation configuration is incorrect, you will get error ""Login failed for Autorite NT \ Anonymous Login".

You can check you configuration against the recommendations,

(1) http://msdn2.microsoft.com/en-us/library/ms189580.aspx

(2) SQL Server setion in http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

If using client account to authenticate to the linked server is not a requirement, you can configure to use SQL login for the linked server configuration. "Be made using this security context".

Linked server and MDAC driver.

Hi,
I have developed a database on my local Win Xp machine, using Express
version of SQL server.
The database is using a linked server to an Oracle database. On the XP
machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
for the linked server.
Now I'm trying to port this version to full SQL server 2005 on a Windows
2003 Server, but it seems that the driver is not installed(only the Oracle
driver). On MSDN I read that MDAC is installed with the OS on windows 2003
server.
Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
Windows Server 2003?
Thanks!
D.Hi
"Dahab" wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
I would expect these to be part of the Connectivity Components which you can
add from the command prompt using the ADDLOCAL parameter.
John|||Dahab wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
Is it by any change a 64-bit installation you are using? The MSDAORA
provider doesn't exists on the 64-bit version. Instead you should use
the Oracle ODAC which is the OraOledb.Oracle provider. You should also
be aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
there are a number of things that isn't supported in this provider.
You can take a look at the link below which is a document that gives a
pretty good overview over the various connectivity options.
http://msdn2.microsoft.com/en-us/library/bb332055.aspx
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Steen,
Yes it's a 64bit server.
So I have to figure out how to use the Oracle driver then. Gave it a try a
few days ago with no luck :(...
Thanks very much Steen.
D.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
>> Hi,
>> I have developed a database on my local Win Xp machine, using Express
>> version of SQL server.
>> The database is using a linked server to an Oracle database. On the XP
>> machin I have used the "Microsoft OLE DB Provider for Oracle" as the
>> driver for the linked server.
>> Now I'm trying to port this version to full SQL server 2005 on a Windows
>> 2003 Server, but it seems that the driver is not installed(only the
>> Oracle driver). On MSDN I read that MDAC is installed with the OS on
>> windows 2003 server.
>> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
>> Windows Server 2003?
>> Thanks!
>> D.
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck :(...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html)
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Linked server

Hi All,
I'm using Sql Server 2005 express, what i want is?
in my requirement the LAN may disconnected for sometime for some reason what ever it may? during that time i want to store the details in MS Access database which is present in the local machine....once the LAN Connection established i want to transfer the data from MS Access to Sql Server...? IS it possible..? or otherwise i want to Sync the data between MS Access and Sql Server 2005 Express is it possible.

I'm using C# .net Desktop Application for development.

I taught of creating a linked server, Is it correct or some other things to perform to do this operation.
plz help me...

-Amjath

I don't think that the concept of a 'Linked Server' will solve your problem. You are indicating that the problem is that the link is not dependable. You indicate that you need the ability to store locally, and 'push' to the server when the server is available.

I suggest that you may find that having the application store the data locally as a 'persisted' xml dataset when the server is not available, and that the application could periodically check for server connection and 'push' the data to the server when connected. Alternatively, the data could be stored in a local Access database or even a local SQL Express database.

Storing locally in SQL Express and then using Replication might solve the problem, but it would require that the server was using SQL Standard Edition.