Adding A Linked Server
Sep 17, 2007
HI,
There is a requirement to transfer a table data from a oracle view to a SQL Server table. These both reside on the same box. I am trying to add the oracle instance as a linked server, like this:
EXEC sp_addlinkedserver @server = @SOURCE_SERVER_NAME, @srvproduct = '', @provider = @PROVIDER_NAME, @datasrc = @DATA_SOURCE_NAME
The server gets added. But When I use this string as part of a stored procedure (or even not use it there), which does the work of transfering the data as required, I get this error:
Server: Msg 7399, Level 16, State 1, Procedure UBS_PULL_HR_DATA, Line 125
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
I have added this Name in the System DSM also. I dont know how to go about it. Can any one please help?
Thanks a lot and let me know if the question is not clear.
Mannu.
View 3 Replies
ADVERTISEMENT
Feb 2, 2001
Does anyone know how to link an Oracle (8.0.6.0) database to a MS SQL 7.0 db? I have set up the ODBC and it works with no problem. I can get tables from the oracle db and bring them into MS access with ODBC so I know that it is set up correctly. I just cant get visability to the db in a sql 7 environment. Any help or sugestion would be appreciated.
Thanks
-NES
View 3 Replies
View Related
Apr 20, 2007
Is there a certain way of adding users to access a linked server? Ilinked to an Oracle server but somehow, I'm unable to add any users,even after trying in EM. It'll allow me to select the local account,enter a remote userid/password, click apply save, but doesn't seemto hold the info.Please help!Thanks,Danny
View 1 Replies
View Related
Feb 8, 2008
Hi @all,
I try to add a new Linked Server to my DB in Microsoft SQL Server Management Studio Express...
The following error is returned (SERVER2 is the Linked Server to add):
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'SERVER2'.
(.Net SqlClient Data Provider)
I entered the user sa and the correct password for SERVER2 but it doesen't work...
When I connect to SERVER2 in Management Studio with the same infos it works fine...
So please help...
View 9 Replies
View Related
Jan 26, 2006
I am attempting to add a linked server to ORACLE from SQL Server. I am able to get the linked server to work using the MS OLE DB Provider for Oracle , however I would rather use the Oracle Provider for OLE DB. When I set the linked server to use the Oracle provider I get the following error when issueing a query.
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80040155].
The query I attempt is
select * from openquery(oracle_test,'select count(*) from employee')emp
Does anyone have a suggestion on a solution?
View 1 Replies
View Related
Apr 22, 2015
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244
Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
<hr noshade size='1' width='250' color='#BBC8E5'>
View 1 Replies
View Related
Jun 1, 2007
Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? Thank you in advance to anybody who can help me out here!
View 6 Replies
View Related
Mar 25, 2002
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
View 5 Replies
View Related
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
View 6 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
May 18, 2004
I have a ldf and mdf file, i need to add them to my server how do i do this?
View 1 Replies
View Related
May 4, 2007
Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" 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 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.
View 3 Replies
View Related
Jul 22, 2007
i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
how i can execute this trigger
View 5 Replies
View Related
Apr 18, 2007
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
View 1 Replies
View Related
Dec 28, 2005
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
<code>
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'
</code>
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
</code>
Any ideas what is happening here?
View 10 Replies
View Related
Mar 16, 2008
Hi, I'm able to connect to my db using ASP.NET, but now I need to connect with ASP. I need to add a user like <computer name>ASP( I already have <computer name>ASPNET) using windows authentication to the db. I cannot seem find the way to do this in SQL Server management Studio, because it tells me that it doesn't find that group. Where can I add this user?Thanks a lot.
View 3 Replies
View Related
Oct 6, 1998
Hello,
Can anybody please tell me of a way you can tell SQL Server the `suid` number you want for a SQL Login, when you add a new login.
It picks them up by default, but I am in a situation where I am restoring DB`s from one server to another, and the SQL Logins keep falling out of synch with each database suid.
Thanks
David Ray
Advali Ltd.
View 1 Replies
View Related
Mar 11, 2005
Hi all,
I have a database hosted by a webspace provider, I've got the IP address, login name and password, however when I try to do the New SQL Server Registration, it keeps coming up with the error "Remote Server doesn't exist or access denied", I can be sure the remote server is up and running and that I have access to it.
Any ideas? I've been struggling for nearly a week
This is how I do it:
1. Open the Client Network Utility.
2. Select the Alias tab.
3. Click on the Add? button.
4. Ensure that the TCP/IP option is selected in the Network libraries
5. In the Server alias field enter the fully qualified domain name, or the IP address, of the SQL Server to which you are connecting.
The Connection parameters are filled in automatically.
6. Click on the OK button to save the settings and exit the Client Network Utility.
7. Open the Microsoft SQL Server Enterprise Manager.
8. In the Tree view on the left-hand side of the application window, expand the Microsoft SQL Server node.
9. Right click on the SQL Server Group entry, and select New SQL Server Registration from the menu that appears.
10. Click on the Next button to display the Register SQL Server Wizard
11. Select the alias that you have just created, and click on the Add button, followed by Next.
12. Select Use SQL Server Authentication
13. Click on the Next button.
14. Enter your SQL Server username and password, and click Next twice, followed by Finish.
Cheers! Thx a lot!!!!
annie x
View 1 Replies
View Related
Aug 12, 2004
hello, i am try to registering a server, in the first time, it let me register with wizard, but i happen check the box "From now on, i want to perform this task without using a wizard", when i add later, it won't show up the wizard, How can i change back to using wizard to register? Thanks.
View 2 Replies
View Related
Jul 7, 2006
We have an instance of sql running on one box. We're going to be making a copy of a database on another server. I've been working on a local copy of the db and I've had to add a few new stored procedures and tables along with some fts catalogs. When we set up the copy on the production server, I figured I'd set up the new version that I've been working on so it has the latest structures in place. However, we then have to pull all the data over from the existing database to the new one. Obviously the new tables won't get any data input from the pull, but aside from creating a dtsx project to pull it all over, is there any other way to do it a little easier and faster? Thanks.
View 4 Replies
View Related
Oct 24, 2006
I need to find out how to add roles to my SQL that is on a hosted server.I have created 2 roles, but after publishing and uploading my site, when I try and create a user, I get the error:"Exception Details: System.Configuration.Provider.ProviderException: The role 'userAtype' was not found."There are two other roles that the same happens with.Running it locally all was fine, I just didn't seem to get this info where it needs to be when I uploaded it.I'm new to messing about with SQL, so sorry if this is really elementary.I have VS2005 Standard, and the SQL Server Management Studio Express.The hosting provider I have does not allow remote connections to the DB on shared hosting, so I need to (?) generate a script to populate the DB? and run it in their browser-based Querry Window?How would I do this?Thanks.
View 4 Replies
View Related
Oct 13, 2003
I have had log shipping set up for a month at a remote site. Created another server to failover to here locally that utilizes 512K line. Microsoft has a tab on the maint plan to add another server to the log shipping plan. Shut down all log shipping, moved full backup back here locally and created new database. Used tab to add new server to log shipping server and setup plan using this database. When log shipping starts up for this new server it fails. Error shows it is looking for the first log file that was sent over a month ago. Am I correct in assuming that the only way you can really add another log server is to do it at the same time? If so it's kind of stupid to have the tab to add the server. My log files are pushed over every 5 minutes, and I remove them after 1 week.
View 3 Replies
View Related
Oct 11, 2004
When I tried to add a new login to the server and after assigning DB Roles,
I am getting the error message that the role has been already added to the user. I think this username has already been in the system earlier. It is in the sysuser table. I tried to delete the roles to which this user has permissions. But still I am getting this error. I am not sure where else it is stored. How can I delete this username completely from all Databases and reassign this userid in the server.
Please advise.
View 2 Replies
View Related
Oct 26, 2005
Hi guys,
I have this problem. I want to add a new primary key to a table but i want the name of the contstraint to be generated by the system. I have this TSQL code.
ALTER TABLE TableTest
ADD CONSTRAINT PRIMARY KEY (C1)
Reading the BOL, it says that if you don't supply a name for the constraint it generates one. But I get this error "Incorrect syntax near the keyword 'PRIMARY'".
If I add a name to the constraint, it works fine.
I'm using SQL Server 2000
Thanks
Darkneon
View 7 Replies
View Related
Jan 9, 2007
Dear Forum
I really hope someone can help me with the following - it's eating me up.
I'm creating a website in VS2005 and want to add an SQL Server database to the Solution, og I've created a subfolder to the App_Data folder called Database. Now when I click Add New Item and choose SQL database in the Add New Item dialog box I get the following error message
Connections to SQL Server files (*.mdf) require SQL Server Express to function properly. Please verify installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId49251
Now at some point in the past I installed Visual Basic, Visual Web developer and SQL Server express edition, but when I got hold of VS2005 Professional Edition I carefully made sure that all Express version of any VS software was uninstalled beforehand. So now I have only VS2005 Professional Edition and SQL Server 2005 Developer Edition (32 bit) installed on my computer.
I have checked all the environment settings, and can't find a single thing that should point to SQL Server Express Edition.
I would really appriciate if you could give me a helping hand before I loose my sanity.
Thanks a hole bunch.
Tina
View 3 Replies
View Related
Jun 15, 2007
Hello all, I 'm not sure if this is the right forum for this, and I apologies if so...
But in a nut shell I'm attempting to grant SQL server access to a ADAM user group account within an ADAM instance.
I have set up the ADAM instance. Added the ADAM user to an ADAM security group. now I need to add that user group to a SQL instance that resides on our AD domain ( non-ADAM instance.) obviously when I attempt to view all available domains within SQL security manager, all I see is our standard domain, I can't seem to figure out how to make the ADAM domain visible to our AD domain.
Any Thoughts?
Thanks
View 4 Replies
View Related
May 18, 2006
I am adding (trying to add) a procedure (written by a friend of mine and someone who is experienced). I am adding it via the SQL Server Mgt. Studio Express. When I run it, it says that the command completed successfully, but it doesn't appear in the Procedure folder.
When I run my ASP 2.0 form that calls it, it says that it can't find the procedure.
Can anyone help me determine what to look for or what I might be doing wrong?
Thank you.
View 3 Replies
View Related
Aug 28, 2014
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies
View Related
Sep 11, 2015
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
View 7 Replies
View Related
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Feb 23, 2015
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
View 2 Replies
View Related
