Create (U)SP In Database On Linked Server

Aug 1, 2006

Hi,
I'm using a couple of linked servers.
I want to create a stored procedure on all of the linked servers in a database with a name which exists on all of the linked servers.
For executing SQL on all of the linked servers I'm using:

declare @x int
declare @dbname varchar(500)
declare @SQL nvarchar(600)
set @x = 1
create table #databases (ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances
while @x <= (select max(id) from #databases)
begin
select @dbname = name from #databases where id = @x
select @SQL='blabla bla bla create PROCEDURE [dbo].[usp_xxxx]'
execute @SQL
set @x = @x + 1
end
drop table #databases

Is it possible to use a ‘create procedure’ in this construction?
Can anybody give me some help how to create a proper syntax for it?

Any help is kindly appreciated!

View 1 Replies


ADVERTISEMENT

DB Engine :: How To Point Linked Server To Specific Database / Rename Linked Server

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

Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource

Jun 5, 2007

Hai,

I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.

The error message is "Invalid Argument".

I use the following code for creating the linked table

Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table

Dim ADOConnection As New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

With tblLink

' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB

' Set the properties to create the link.
Dim adoxPro As ADOX.Property

adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True

adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString

adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl


End With

'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")

'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)

'Append the primary index to table.
catDB = Nothing

End Sub

If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.

Thanks in advance
Sudeep T S

View 4 Replies View Related

Cannot Create Linked Server

Jul 3, 2007

I am trying to create a linked server in Management Studio Exoress. In the Objext Explorer, I open Server Objexts and the right-click on Linked Servers and select New Linked Server. I then get an error that says "Cannot show the requested dialog. Additional information: Cannot find table 0. (System Data). The full text of the error is as follows:

===================================

Cannot show requested dialog.

===================================

Cannot find table 0. (System.Data)

------------------------------
Program Location:

at System.Data.DataTableCollection.get_Item(Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.PopulateProvidersCombo()
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesGeneral.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnInitialization()
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SelectCurrentNode()
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.InitializeUI(ViewSwitcherTreeView treeView, ISqlControlCollection viewsHolder, Panel rightPane)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()



I am running XP SP2 and SSE SP2. One other item is that the providers folder is empty. I checked another box and there are several providers listed in that installation. It looks like when the SSE is installed, the providers are not being created. I have tried uninstalling and reinstalling and am having the same problem. Is this a installation bug or is there a conflict with another program? I also re-downloaded the installation files in case there was a problem with that, but it didn't solve the issue either.







Thanks for any help,



Paul Nelson

View 7 Replies View Related

Create A Linked Server To MPP File

Apr 13, 2007

I have created a linked server that connects to "mpp" file, and it connects and opens successfuly returning a list of tables that exists in the linked server to the mpp file.

But the problem i am facing now that whne i write a select statment to get data from the tables in this linked server

select * from linktompp.testmpp.dbo.tasks

such that linktompp is the linked server name
testmpp is the catalog name
tasks is the table name i need to select data from

it shows an error that says

"Could not obtain a required interface from OLE DB provider 'Microsoft.Project.OleDB.11.0'."

"[OLE/DB Provider 'Microsoft.Project.OleDB.11.0' IUnknown::QueryInterface returned 0x80004002: IGetDataSource]"
could anyone tell me the reason of this error

thanks




Mostafa Salama
M.Sc. in Computer Science

View 1 Replies View Related

Steps To Create A Linked Server

May 7, 2007

hi,

what are the steps to create a linked server, I have tried unsuccesfully:
The scenario is the following:
From server A I want to be able to execute a select statement to a table in server B like
select * from [server B].myRemoteDb.dbo.myremTable

I am administrator of server A but not of server B. I tried the

sp_addlinkedserver syntax but have not been succesful, so I would be grateful if somebody could just list the steps necessary to link Server B from Server A,

thank you

View 4 Replies View Related

Create Linked Exchange Server

Aug 29, 2007

I'm using sql server 2005 and exchange 2003. End result is I want to upload email attachments to a table in sql. No one seems to have any idea how do do this except with .net, which i know nothing about so not really an option for me. I found this bit of info with some extensive web searching. Can anyone help fill in the blanks? I can't get the linked server to work yet.


EXEC sp_addlinkedserver 'exchange',
'Exchange OLE DB provider',
'exoledb.DataSource.1',
'file:\.ackofficestoragemailservernamepublic folders'

Error received:The OLE DB provider "exoledb.DataSource.1" has not been registered.

View 1 Replies View Related

Error When Trying To Create A Linked Server

Jul 24, 2007

Hi all,

I have a problem when i try to create a linked server to a MsAccess Db. That's what i do:



--- linked server drop (in case it already exist)

sp_dropserver 'XXXXX', 'droplogins'



--- linked server creation

sp_addlinkedserver XXXXX, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0', ' serverfolderdb1.mdb'


--- login creation

sp_addlinkedsrvlogin XXXXX, FALSE, NULL, Admin, NULL


--- fill tables

sp_tables_ex XXXXX



File Db1.mdb is located on a partition (D) of the server where Sql is installed.



On login creation i get the following error :

Error -2147217900 Error during decryption. (15466) Source: Microsoft OLE DB Provider for SQL Server.

(This is not the real error message, it has been translated)



Some ideas?

Thanks




View 4 Replies View Related

Create Linked Server To SYBASE ASA 9.0

Apr 2, 2008


Hi all,

I´m trying to create a linked server to a Sybase database (.db file) so i can create some reports in sql 2005 (with Reporting Services and Report Designer). After reading all articles I did the following.

1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.
2) Created a USER DSN to the Db file (Control Panel - odbc connections) and it works fine: The Settings for the ODBC are:


ODBC Tab


Data Source Name: dbNOM

Login Tab


Supply userID and Password: (selected)

User ID: DBA

Password: SQL

Database Tab


Server Name: dbNOM

StartLine: C:Program FilesSybaseSQL Anywhere 9win32dbeng9.exe

Database Name: (blank)

Database File: c:Project FolderDataBase.db
As I said, when i go for "test connection", works fine.

3) Sql Management Studio - Object Explorer - Server Objects - New Linked Server, with settings as following:


General


Linked Server: lkDbNom

Server Type: Other Data Source

Provider: Sybase Adaptive Server Anywhere Provider 9.0

Product Name: Sybase

Product Name: dbNom (the dsn name, right?)

Security


Be made using this security context.- Remote Login: DBA; Password: SQL (same as DSN)
All other settings, as default, click in OK and shows no errors (aparently it creates the linked server successfully). But when i try to query the linked server with:


SELECT * FROM OPENQUERY ('SYBASE', 'SELECT * FROM nom_Robot')

And i get the following message:


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'SYBASE'.

Even better, if i go to the Object Explorer - Server Objects, i test the connection and it´s ok, but when i try to retrieve the catalog an error displays:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "ASAProv.90" for linked server "SYBASE" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASAProv.90" for linked server "SYBASE". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476


So, i really don´t understand what is missing in my linked server, i tried with MDASQL (OLEDB for ODBC) but it doesn´t even completes to create the linked server.

The database file is ok because, because in another server (one that doesn´t have SQL, only visual studio) i did the following:

1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server.
2) Created a DSN to the Db file, and it works fine. (copied the db file, so is local, no remote access), exactly the same setting as i did on the sql 2005 server.
3) Created a connection in visual studio 2008 (server explorer - data connections), i can retrieve sdata...but of course the provider is .NET Framework Data Provider for ODBC and i cannot use the same connection string.

Any ideas?

P.S.

View 7 Replies View Related

HELP - Error 15028 - Can't Create Linked Server

Nov 22, 2002

I have 2 instances of server A. A is the primary instance and A1 is the logical instance. Instance A1 has a linked server to server B. When I try to create a linked server from the primary instance A to server B, I get the error message

Error 15028: The server 'B' already exists.

When I run a select of master..sysservers on the primary instance, A, I see all linked servers for A and A1. When I select off of A1 I only see the instances for A1.

If I try to access server B from the primary instance A, I receive the following message:

Server: Msg 7411, Level 16, State 1, Line 1
Server 'B' is not configured for DATA ACCESS.

(1) Why does sysservers on the primary instance, A, show instances for A and A1 and not just the ones for A?

(2) Why can't I create the linked server 'B' on instance 'A'?

Thanks, Dave

View 2 Replies View Related

How To Create A Linked Server To MySQL From MsSQL?

Nov 7, 2005

I can create a linked server to another MsSQL from MsSQL,but encounter error when create a linked server to MySQL:
Error 7399:OLE DB provider 'MSDASQL' reported an error.
Data source name not found and no default driver specified.
......

Anyone can help me?
Thanks!

View 6 Replies View Related

How Can I Create A Temp Table On A Linked Server?

Apr 11, 2007

Hello

I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so:

create table #myRemoteTempTable

insert into #myRemoteTempTable
select * from myLocalTable

update myRemoteTable
set
Value=#myRemoteTempTable.Value
from myRemoteTable
inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID

View 6 Replies View Related

SQL 2012 :: Create Linked Server For Firebird?

Jan 26, 2015

i didn't figure out how to add a firebird linked server via ODBC

my system :

win 7 pro 64
SQL server 2012 express 64
firebird 2.5.3 64
Official ODBC drivers 64

i have created the odbc source without problem, and checked that in visual studio and ssms import data wizard , both show me the data from firebird database but when i try to create a linked server , i have something like this message (the image is from the web)

View 4 Replies View Related

Create Relationship With Tables In A Linked Server

Jul 23, 2005

I need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.

View 1 Replies View Related

Create Temp Table On Linked Server From Local Server

Jan 15, 2004

Hi,

I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large.

If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that.

I appreciate any advice / guidance anyone can offer me!

View 1 Replies View Related

Cannot Create An Instance Of OLE DB Provider IBMDADB2 For Linked Server

Aug 3, 2007

System: Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.

Error message from Management Studio query window:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".

Event messages associated with this error:

App Event ID: 19036
The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.

Sys Event ID: 10016
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}
to the user domainuser SID (S-1-5-21-126051702-1034962659-2130403006-7826). This security permission can be modified using the Component Services administrative tool.

I€™m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename€™))
The linked server has a remote login and password that it uses to connect to DB2.

I found this from another post on how to fix this error:
Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security
Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account
Under Security - Access permissions: Allow System: Local Access and Remote Access.

After completing these steps I still get the error message but the events are no longer generated. I€™ve also tried different variations of these steps. The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties €“ Identity Tab €“ Run this App as this User.

Can someone please tell me the steps that I€™m missing?

View 11 Replies View Related

SQL Security :: Create Linked Server Using Windows Authentication

Sep 9, 2015

I have two SQL Server machines - RPTPROD and DATAPROD.

I wanted to create a linked server from RPTPROD pointing to DATAPROD using Windows Authentication.  When I tried to create this linked server, I keep getting this error

"Login failed for user 'NT AUTHORITYANNOYMOUS LOGON' (Microsoft SQL Server,Error: 18456)".  

On the "Security" page, I chose "Be made using the login's current security context".   I'm sysadmin on both SQL Server machines.  

View 3 Replies View Related

How To Create A Linked Server Using SQL Server Management Studio?

Feb 23, 2007

Does SQL Server Compact Edition support Linked Server? I have 2 SDF files and I want to copy data from one to the other? I thought Linked Server is the easier to go.

View 3 Replies View Related

SQL 2012 :: Create Linked Server With Failover Partner Option?

Jul 22, 2014

it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.

EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',

[code].....

View 2 Replies View Related

How To Create Linked Server To Access 2007 Accdb File?

Feb 20, 2008

How does one create a linked server to an Access 2007 (accdb) file? I am aware that to create one to a mdb file, the code would be -EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:MSOfficeAccessSamplesNorthwind.mdb'
GO
What is the corresponding code for Access 2007 accdb files?

View 18 Replies View Related

Create Linked Server In SQL 2005 From Excel Spreadsheet And Have Primary Key?

Sep 6, 2007

Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?

Thanks,
--Stan

View 2 Replies View Related

Integration Services :: Need To Create SSIS CONNECTION Manager Specifically For Linked Server?

Oct 1, 2015

I have a linked server setup in dev environment which is pointing to ORACLE server of a third-party. I m on  sql server 2008 r2 and   using BIDS 2008 for building an SSIS package which would pull data from oracle and do a look up in SQL server table to get a final  extract. Now the query which pulls data from a single table from linked server is like the one below

select * from [oracle linked server ]..dbo.tablename a
left join  mySQLSERVERtable b
on a.id=b.id 

Note:I can execute this query on SSMS as  the linked server is setup -[oracle linked server ] and I have sql  oledb connection manager for dev environment already configured in my ssis package .

My questions are do I need to create a SSIS CONNECTION manager specifically for linked server in my ssis package ?

Are there any best practices which we follow when using linked servers in this context ? I am thinking will the join suffer performance issue and what kind of exceptions I need to write when the linked server connection is broken.

View 4 Replies View Related

Cannot Create An Instance Of OLE DB Provider SQLNCLI For Linked Server SERVERNAME, Error: 7302

Mar 2, 2008



I just installed a SQL Server 2005 Express SP2 instance on a server with an existing SQL Server 2000 SP3 installation. (I need SQL Server 2005's INSERT from an EXEC capability). It's working great now except for one thing: I can't link any other SQL servers! I've already successfully added and queried a linked Oracle server, but attempting to add a linked SQL server gives me the following error, no matter which SQL provider I try to use:




Code Snippet
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
Additional information:
--> An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo)
--> Cannot create an instance of OLE DB provider "SQLNCLI" for linked server "SERVERNAME". (Microsoft SQL Server, Error: 7302)


The technical details reveal the error source as "sp_testlinkedserver".




I've scoured the net and premier support for advice on this problem, but the little I found wasn't helpful. I've tried reinstalling the SQL Native Client, but it didn't help. I've tried uninstalling SQL Server 2005 Express completely (including management studio and native client), rebooting, and reinstalling everything, with no luck. The server (obviously) does not have a firewall enabled. I've tried stopping the SQL 2000 Server installed on the system, no help. If I create a test .UDL file on the system, pointing to any of the SQL servers I'm trying to link, clicking the "Test Connection" button returns successful. I'm also able to connect to and query the servers directly from the SQL 2005 Express Management Studio. I just can't add them as linked servers.

The server is running Windows 2003 SP1, SQL Server 2000 SP3, and SQL Server 2005 Express SP2 (the default "SQLExpress" named instance). I've tried setting up SQL 2005 Express to run under the network service account and under a domain account used by the other SQL 2000 servers.

From other SQL 2000 servers, I can connect and link to the SQL Server 2005 Express instance successfully. I can also successfully add linked SQL servers in SQL Server 2000 instance installed on the same server. Only adding linked SQL servers in 2005 Express seems to be broken.

Anyone have any other suggestions? I'm totally baffled. Thank you so much for any helpful advice.

View 5 Replies View Related

Trying To Create A Linked Server From An SQL Server To Another SQL Server On Another Machine.

Sep 12, 2007

I'm Trying to create a linked server from an SQL Server to another SQLServer on another machine.Can anyone suggest some good reference material or tutorials?? So farI'm only finding examples for a linked server to an ACCESS database.Thanks in Advance!

View 1 Replies View Related

Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)

Oct 2, 2007

 
 I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
 
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
 Prontonet
 
 
 

View 4 Replies View Related

Linked Server To A MySQL Database

Feb 25, 2004

Hi,

I've installed MyODBC-3.51.06 and succsesfully created 2 odbc sources to the MySQL database with the ODBC data source administrator. One is a User DSN, the other a system DSN (Q1: what's the difference?) The test in that applet perfoms positively.

Running a dataimport of the tables using the import data option in enterprise manager does work as well.

Now for the part that gives me trouble. I would like to create a linked server using enterprise manager of SQL server 2000 (SQL 8.00.194) but I'm totaly stuck there. So Q2: how to get this set up?

View 2 Replies View Related

Help Connecting Ole/db Linked Server To Msaccess Database In A Different Machine Than Sql Server Resides

Jun 29, 2007

Hi,



I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

View 4 Replies View Related

SQL Server 2014 :: Call For DB Backup For A Database On Another (linked) Server

Jul 8, 2015

I have a sproc that will generate a dynamic call (and this must be done in a stored procedure),... Anyway the dynamic call I am having problems with is that I need to dynamically create a statement to backup a database. It works as long as the database is on the same server / instance as the stored procedure. I want to be able to from server/instance A create a command that does a backup of a database that is on server B. I just can't figure out the syntax for a database backup where the database is on another server.

Trying something like [ServerName].[DatabaseName] does not work...

View 9 Replies View Related

SQL Server Admin 2014 :: Access A Database Without Linked Server

Jul 16, 2015

We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases.

I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?

View 8 Replies View Related

Create Database Permision Denied In Database ' Master' (MS SQL SERVER, ERROR 262

Feb 17, 2007

Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,

I am running windows Vista Business, SQL SERVER 2005

so what going on here

View 23 Replies View Related

Sql Server Linked To Oracle Database...very Slow...why?

Dec 26, 2003

Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...

However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.

I am very confused as to why there would be such a performance difference and why sql server would run so slow.

I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.

collation compatible (not selected)
Data access (selected)
RPC (not selected)
RPC Out (not selected)
collation name
connection timeout
query timeout

View 1 Replies View Related

Creating A Linked Server To An Oracle Database

Aug 29, 2000

Hi all,

I hope someone can help me with linked servers.

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.]


Anybody understand this?


Thanks in advance,
Faustina

View 4 Replies View Related

Querying A Linked Server With Local Database...

Mar 25, 2008

I would like to query a linked server and join that database to my local database. I finally was able to get it to work and I got this query to work:

select * from openquery([hcda-storagesrvlaserfiche], 'select * from toc where parentid=358')

I would like to join one of my tables from my local database to the query. How can I do this? Thanks!

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved