How To Delte Rows Using Linked Server
Jun 19, 2007
Hi,
I have two mssql databases. I am able to create a linked server.
I want to delete some of the rows using this linked server.
Here HAFEEZ is one mssql database and DPVSQSL is another.So, i want to delete rows from DPVSQL from HAFEEZ using HAFEEZDPVSQL linkedserver.
DELETE OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');
when i run the query i got the following error.
"Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL" supplied inconsistent metadata for a column. The column "EVENT_SYS_ID" (compile-time ordinal 1) of object "SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = 'evesysid0015'" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time."
Please note that i am able to query something using this linked server,please see the below query.
SELECT * FROM OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');
Can anyone please help me regarding this.
Thanks and Regards,
Hafeez.
View 5 Replies
ADVERTISEMENT
Dec 20, 2007
this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3
View 9 Replies
View Related
Jan 9, 2007
HI
I have 3 table
in table 1 i have a ID i use in table 2 and in table 2 i have a ID i use en table 3
In table one i have a datetime field
What i want is to delete all from this 3 table when the datetime value are more than one hour old.
I can delete old from the table one by:
delete from tableName
where datettimeField < dateadd(hour, -1, getdate())
But how do i delete all the records from table 2 and table 3 off course where the ID in table one are the same as in table 2 and the ID in table 2 are the same as in table3
Hope someone can help
LN
View 6 Replies
View Related
Aug 14, 2015
I have a script that inserts rows from a linked server. It basically looks like this:
use mydb;
go
insert into my.table (col1, col2, ...)
select col1, col2, ...
from LinkedServer.db.my.table;
The DDL for both tables is identical: 550 columns (de-normalized from a data warehouse), of which 548 are varchar(max). The remaining two are varchar(255). (not my design! but unchangeable at the moment).
Running my query raises an error:
Cannot create a row of size 9948 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.
Now, inserting rows on the table on the LinkedServer has never been an issue. (Since most columns are varchar(max), SQL has the option to store the data off-row.) The data is there, intact and whole. However, when pulling it into my target server using a LinkedServer (4-part naming), I get the error.
Three pairs of eyes have confirmed that the table definitions are identical on both source and target.
View 9 Replies
View Related
Jun 4, 2007
Hello,
I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.
For some tables, it return all data but for another tables, it only return part of the rows.
How it may happen?
Thanks
View 1 Replies
View Related
Oct 28, 2014
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
View 5 Replies
View Related
Apr 6, 2006
Hii want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.an suggestions?
View 1 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
Feb 20, 2008
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
View 2 Replies
View Related
Feb 20, 2008
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
View 1 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 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
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
Apr 16, 2015
Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
View 0 Replies
View Related
Sep 4, 2015
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
View 4 Replies
View Related
Oct 4, 2007
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
View 8 Replies
View Related
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
May 16, 2015
i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.
View 4 Replies
View Related
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
Mar 8, 2006
Hi all,
I have the following select that is designed to compare values on parallel servers.SELECT TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate,TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate
FROM TradeAnalysis.dbo.SalesIncome_Quarterly AS TA1
FULL OUTER JOIN L_TA_MIRROR.TradeAnalysis.dbo.SalesIncome_Quarterl y AS TA2
ON ((TA1.OSID = TA2.OSID) AND (TA1.QtrYear = TA2.QtrYear) AND (TA1.QuarterNo = TA2.QuarterNo))
WHERE(BINARY_CHECKSUM(TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate) <>
BINARY_CHECKSUM(TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate))Disregard the full outer join, it is necessary in the full select, I have just pared it down to show here and for testing. A standard join will also have the same result.
The trouble is that the comparison (the BINARY_CHECKSUM) is failing on the EarningsReportDate column, which is defined on BOTH servers in DUPLICATE databases as a smalldatetime.
The data is actually the same in both rows of each table (as is all the other data being compared). What I am seeing is that the select returns milliseconds on the LINKED server (the L_TA_MIRROR reference), but not on the local database?
for example,
Local server data returned in above select is 2000-01-28 00:00:00
Linked server data returned in above select is 2000-01-28 00:00:00.000
Wassup with THAT? If I remove the date from the select/comparison, it says everything matches, so I am pretty sure this is the culprit that is causing the BINARY_CHECKSUM comparison to fail.
Is there some setting that I need to put on the Linked Server reference that says "treat this as the smalldatetime data type it IS, dammit!"????
I can get by the issue if I replace the date reference in the linked server part of the select with CAST(TA2.EarningsReportDate AS smalldatetime) but why do I have to do this?
Yes, I am SURE both servers have the column in question defined as a smalldatetime, as running the same select on either server causes the same failure, but only when referencing the LINKED server column.
View 1 Replies
View Related
Nov 19, 2013
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
View 8 Replies
View Related
Dec 8, 2013
Here is my Problem:
1. I have sql 2008 R2 running on my LocalHost.
2. Created Data Base [Customer].
3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:Data which has DBF files in it.
4. Create dbo.Customer_Upload Table.
5. INSERT INTO [Customer].[dbo].[Customer_UpLoad]
([Name],[Email])
SELECT
NAME,EMAIL
FROM [CUSTOMERLINK]...[CUS]
All this works fine. I can even put it in to an After Insert Trigger on another table and it works.
My problem is that I need this to work in a scheduled job.
F:Data is just a folder with files in it.
This info is from a Restaurant POS system and I need to update it every night.
I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.
View 4 Replies
View Related
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
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
