SQL Server 2008 :: Getting Error While Creating Linked Servers
Sep 15, 2015
Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3
/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'
This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".
How to setup the linked server........... Below are the drivers installed on the SQL server
Hi, I have a problem, maybe someone can help me.I'm traing to create a view with a Linked ServerThis query works great:select id, descrfrom SERVER.DB.dbo.TABLEWhen I tray to create the view:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect id, descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'is invalid for schema binding. Names must be in two-part format and anobject cannot reference itself.So I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descrfrom SERVER.DB.dbo.TABLEGOI have this error:Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3The number name 'SERVER.DB.dbo.TABLE' contains more than the maximumnumber of prefixes. The maximum is 3.Then I try this:CREATE VIEW dbo.View1 WITH SCHEMABINDINGASselect a.id, a.descrfrom SERVER.DB.dbo.TABLE as AGOI Have this errorServer: Msg 4512, Level 16, State 3, Procedure View1, Line 3Cannot schema bind view 'dbo.View1' because name'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Namesmust be in two-part format and an object cannot reference itself.This query alone works great:select a.id, a.descrfrom SERVER.DB.dbo.TABLE as AThe names aren't what I describe here (id is not valid without []).ANY IDEAS?!??!?!I don't know what else can I do.I need help!!!TANKS A LOT!!!!!!!!
I am trying to create the linked server to IBM DB2 server, and getting the following error message. Can some one take a look at this and let me know. Thanks!
TCP Provider: No connection could be made because the target machine actively refused it.
OLE DB provider "SQLNCLI" for linked server "Linked server name" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "linked server name" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 10061)
even if I create the linked server using the management studio I am getting the following error when I try to test the connection for the linked server.
TITLE: Microsoft SQL Server Management Studio ------------------------------ "The test connection to the linked server failed." ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The OLE DB provider "DB2OLEDB" has not been registered. (Microsoft SQL Server, Error: 7403) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7403&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
Hi,Please help, I'm getting desperate. Any ideas warmly welcomed!I'm trying to read from a basic excel file (1000 or so rows fromcolumn A) but am having problems. The code I am using is:Declare @Return IntSET NOCOUNT ONExec @Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL','Microsoft.Jet.OleDB.4.0' , 'e:jsbackupRACodes.xls',NULL, 'EXCEL 8.0'print 'set up Return : ' + convert(varchar(10),@Return)--NB E: is the drive as seen oon the serverEXEC sp_addlinkedsrvlogin@rmtsrvname = 'READ_XLS',@useself = 'true'print 'login Return : ' + convert(varchar(10),@Return)When I try to read from the (one) excel sheet in the file, viaSelect * from [READ_XLS]...RACodes$or to list what tables/sheets are available, viaexec sp_tables_ex 'READ_XLS'I get the following error:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.Authentication failed.[OLE/DB provider returned message: Cannot start your application. Theworkgroup information file is missing or opened exclusively by anotheruser.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].What am I missing?*Many* thanks in advance.Andy
This is the siuation: I have on server A SQL 2005 on server B i have SQL 2000
I want to create in a database on server A a view which uses a linked server to get data from server B. The linked server works fine. But when I want to execute the creation of that view i get this error:
OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "Unspecified error". OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". Msg 7311, Level 16, State 2, Procedure VW_ASSETTABLE, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "BI_AX_LINK". The provider supports the interface, but returns a failure code when it is used.
I have checked and the collations between the to databases are the same. Anyone an idea on who to do such thing? The rights that i have an server B or not that much.
I've seen several posts on linking an AS/400 to a SQL 2000 server.
I have created the link, I can use DTS packages to get data to/from the AS/400 to the SQL Server. However, I cannot write any SQL statements against the AS/400. Here is a basic one that doesn't work...
Select * from openquery(jdedwardspy, 'Select * from mhscrp.f0006')
If you look quickly enough, you can see that it does return a row or so, but then that is replaced by this error...
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.]
We're running Client Access V5R2, SQL Server 2000 SP3 and MDAC 2.7.1.
I am trying to setup a linked server on 2 machines. Both machines have identical SA login/pwd. Also, both machines have a database called Federated_Bridge.
The setup I have is as follows:
Machine 1: (local) - As it appears in Enterprise Manager Machine 2: (RealIBM2) - As it appears in Enterprise Manager
Earlier I executed a query to successfully link machine 1 to machine 2 as follows:
The above query works. However, when I try to do the same thing from Machine 2, it's not working. It doesn't seem to like the (local) name for the @datasrc field. Here is my new query .. which is failing:
I am running the following query trying to return server properties across a linked server. I want to store the results in a table on the server where I an running the query.
DECLARE @BuildClrVersionx nvarchar(128)
SET @BuildClrVersionx =
(SELECT *
FROM OPENQUERY(LKMSSQLXYZ01, 'CONVERT(nvarchar(128),SERVERPROPERTY("BuildClrVersion")'))
I am getting the following errors:
OLE DB provider "SQLNCLI" for linked server "LKMSSQLADM01" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
If you have any ideas how I can run this query across a linked server I would appreciate it.
I'm working with SQL Server 2000 installed in Windows 2000. When i tried to add the 'servername' as a linked server , i got the following message:
" Error: 15038. The Server 'servername' already exists."
When i execute "sp_linkedservers" , i could see the name "servername" in the list. But before doing this i had Restored the "MSDB" database on this server. Should i use "sp_droplinkedservers" and add them again.
I've set up a Linked server connection to a Windows 2003 server running MySql using SSL.
I can run SQL and T-SQL from the query window using the Server management studio on the server, and everything is good.
As soon as I apply a trigger to a table on our local server running the same script, I get a "linked server unable to begin a distributed transaction" error.
I've google'ed my brains out on this error and tried both Mysql ODBC drivers 3.51 and 5.1 and keep getting the same error.
Does anyone know where I can get some good examples of how to set up a linked server to an Oracle data source in SQL 2000? I can't seem to get a link set up to my Oracle database. In particular I am wondering how the communication works between OLEDB to the Oracle Instance and how to trouble shoot issues (for example, would is the Oracle Listener what OLEDB is talking to?) Thanks!!
I have migrated databases and logins from SQL server 2008 R2->SQL Server 2014. Now I also want to migrate/copy the "linked servers". Do you have a step-by-step for this, so that i will copy everything that is necessary regarding logins etc ?
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that select * from Alias1.db1.dbo.table1 and select * from Server1.db1.dbo.table1 should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-) This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
I have a schedule job that I would like to run on a MSDE database. Thestored proc executes just fine if I run it manually. But trying toschedule it through the Enterprise Manager (or Management Studio)generates an error saying the the "Remote Access not allowed forWindows NT user activated by SETUSER.Essentially I have tables in the MSDE which are being updated based ontables from a LINKED SQL Server.I can not appear to get this job to execute unattended.Please advise,Rob(rkershberg@gmail.com)
I've been put in a situation where I have a number of SQL DB's running on 2005/2008 which I have responsibility for. I've been given limited information so am looking at a starting point to determine where I go from here.
I have of course ensured there is a backup strategy in place to secure the data.
I have to set up log shipping from Prod server "A" to 2 different DR servers ("B" and "C")...What do i have to do differently (or additional) using the GUI (ie not using Tsql Scripts) to accomplish this, in addition to the steps that are done to log ship to just one DR server?
I have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?
i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information
I have installed SQL 2005 (X64) on 64-bit Windows 2003 server (AMD). One of my databases need to connect several Oracle databases on 32 bit servers.
I managed to install Oracle 32 bit Client (was unsuccessful with 64 bit installation) and could set up the odbc (32). But I coul'nt find Oracle under linked servers!
I have the default trace on a SQL Server 2008R2 instance enabled and found today that there is a gap of nearly 4 minutes in the trace during a time of the day when there most certainly is not going to be a 4 minute window of nothing.
What if anything could cause the default trace to have a gap like this? The SQL Server Instance (against my preferences) is hosted on VMware however it has its on HOST and so its resources are not being shared with any other server. The data & log files reside on different parts of the SANS. Our IT & Network admins are looking into the issue on their end but when I looked and found a near 4 minute gap in the default trace it hit me that this could be something above/outside of SQL Server.
how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
I wanted to know whether it is possible to query a database in AS400 server from MS SQL Server 7.0 using linked server. If it is possible, could you show some pointers as to how it can/should be implemented. Thanks in Advance. Manojkumar DMT Team DaimlerChrysler Capital Services Phone - 203-845-7326 EMail - manoj.kumar@dcxcapital.com
When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.
Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?
Example :
CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:
The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?
I have a Merge Replication Setup. With 3 computers. One computer acts has a central publisher. The 2nd one is a subscriber, and republishes its data to the 3rd computer. Every thing is being done using Windows Authentication method. the 1st PC has SQL SERVER 2000 (Enterprise Manager, the works). The other two are SQL Server MSDE (Rel. A). My process is up and running and works, but heres my issue.
I need to script the replication installation process out., For future installations on machines that will be located in remote parts of the world. The will reside on a secure DSL network.
Anyone familiar with Scripting Merge Replication knows that once you've run certain scripts/procedures on your subscriber, you have to run a final procedure on the publisher to activate or initiate the subscriber as a valid one, and the you can start the Merge Agent on the subscriber, and voila! you're good. The procdure you run is called sp_addmergesubscription . It goes something like this :
My intention was to embed this system in a stored proc on the publisher and then call it remotely from the subscriber, before starting the Agent. IT FAILS!!! This is my proc:
Create Procedure RunSp_AM_subscription(@servername Varchar(30), @IAdbname Varchar(20), @Attachedbname Varchar(20)) AS
DECLARE @publicationname Varchar(30) SET @publicationname = 'REPUBLISH-'+@IAdbname+''
When I call it locally ot works fine, but not otherwise........... :eek:
When I try to create a linked server SQL Server seems to think that one already exists (because Replication has already created the said server as a remote server). I think the fact that I am not using SQL Authentication might be a problem too.
Does anyone have any ideas? I mean seriously, any suggestions on what to do. This is qute Urgent..............
I would like to create a linked server from SQL Server to Sybase IQ. I have created linked servers before so I know how to do that. However, I dont know the specifics of creating a linked server to Sybase IQ.
What are the parameters that are necessary to link IQ to SQL Server. Which Provider do I use? Do I need to install a special driver?
I'm trying to create a linked server to an Access database that resides on a separate machine.
On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.
If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.
The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.
I create the linked server using the following command: exec sp_addlinkedserver @server = 'AccLinkedServer', @provider = 'Microsoft.jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = '\DatabaseServerAccessDatabase.mdb'
If I run the SQL statement:
SELECT * FROM AccLinkedServer...AnyTableYouLike
I get this error: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\DatabaseServerAccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.
Anyone got any other advice, before I go totally insane with this.
Hii have created a new database and a new linked server that points to anAccessDB using an ODBC DSN.Now inside that new sql db i have create i need to created a new view soi open EM went to views and paste the followingselect * from openquery (AccessLinkedServer,'select * from mytable')i press run and i see the data ok .but when i try to save the view i getthe followingODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operationcould not be performed because the OLE DB provider 'MSDASQL' was unableto begin a distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returnedmessage: [DataDirect][ODBC dBase driver]Optional feature notimplemented.][Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned0x8004d00a].*** Sent via Developersdex http://www.developersdex.com ***
In one of my applications running on a MSSQL database, I need to issue a select call to an Oracle database to pull information into my application.
To accomplish this,
1. the ORacle Admin has created a view of the Oracle database for me. 2. I have installed the Oracle client on the SQl server box. 3. A friend has told me that one way to accomplish this is create a linked server from SQL to use OLE/DB for Oracle to connect to the Oracle database 4. So I create the linked server successfully; create the server login sp_addlinkedsrvlogin successfully 5. When I go to query the database, I get the error below:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.
You will be unable to use this provider until these components have been installed.]
