Linked Server And EXECUTE AS

Oct 10, 2007

I setup a linked server with the following server login mapping:

Local login: db1reader
Remote login: db2reader

db2reader is granted to have read permissions in db2.
I created a stored proc p1 using WITH EXECUTE AS 'db1reader'

Inside the stored proc, I access the link server db2 by "select * from db2server.db2.dbo.table"
db1 reader is granted to have EXECUTE permission on stored proc p1. Trustworthy is on in db1.

Then, I use SQL server 2005 management studio to connect to db1 using db1reader account. Try to execute the stored proc p1. It tells me "Access to the remote server is denied because no login-mapping exists."

Then, I launched a new query in management studio, type in the following TSQL


execute as login = 'db1reader'
go
select * from db2server.db2.dbo.table
revert
go

This time, it works without any error.

Then, in the same query window. I delete all my old TSQL statements and execute my p1 stored proc again.


USE [db1]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[p1]
SELECT 'Return Value' = @return_value
GO

This time, it works.

What did I do wrong?

View 3 Replies


ADVERTISEMENT

Execute SP On Linked Server?

Jul 24, 2006

I've created a stored procedure on several servers. All with the same name and all in a database with same names.
Now I’m trying to execute the stored procedures by executing a script.... I don’t get it to work!
I can execute it successfully by starting it manually:
exec [CIMPDB01CIMT1].zz_am.dbo.usp_SpaceMon
exec [CIMPDB01CIMT2].zz_am.dbo.usp_SpaceMon
and so on..... no problem

But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it won’t:

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 where actief='J'

while @x <= (select max(id) from #databases)
begin
select @dbname = name from #databases where id = @x
print @dbname
select @SQL='exec ' + @dbname + '.zz_am.dbo.usp_SpaceMon'
print @SQL
execute @SQL

set @x = @x + 1
end

drop table #databases


Msg 203, Level 16, State 2, Line 17
The name 'exec [CIMPDB01CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.

Please help....

View 1 Replies View Related

OPENQUERY Vs EXECUTE On A Linked Server -- What Is Better?

May 22, 2007

Can anyone tell me, if, generally, the performance or the cost of executing a pass-through command on a linked server in SQL Server 2005 would be better using OPENQUERY or the new option with EXECUTE -- whether the two servers are on the same box or not? I haven't been able to find a comparison between the two.



Have there been any tests of the difference?



What effect on performance is there with 'rpc out' set with sp_serveroption so EXECUTE can be used?



To be more specific I have a development box with SQL Server 2005 and Oracle 9.2.



The new option with EXECUTE would be something like the example in MSDN (Example J.) at:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx


EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO

View 2 Replies View Related

Execute DBCC On Linked Server

Apr 26, 2006

Hi,

How do you execute 'DBCC' statements or 'EXEC ...' against a linked server (SQL 2000 sp4) ?

Regards,

A.E

View 4 Replies View Related

Execute An Schedule Job From MSDE With LINKED Servers In SQL Server

Oct 17, 2006

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)

View 2 Replies View Related

Error :Execute Trigger From Remote Server To Another Server By Linked Server

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

Remote Update Having A Linked Server Takes Forever To Execute

Oct 17, 2006

UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,

col8=SR.col8,col9=SR.col9,col10=SR.col10

FROM LNKSQL1.db1.DBO.Table1 CD

join Table2 USRI on USRI.col00 = CD.col00

join table3 SR on USRI.col00 = SR.col00

Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you

View 4 Replies View Related

Execute Script To Linked Oracle Server From Sql Server

Feb 26, 2008

Ok, so these are the facts:
My company wants to implement a BI, the warehousing company we used sold us panorama on sql server. Our database is on oracle.
They were proposing this method:
a) Link oracle server, copy oracle tables to sql database and then build cubes from sql database. The operation will be made only once a day at night.
I don't like it so make it like this:
b) Build cubes directly from linked oracle server. As it is not good to build directly from tables and views (because they could change between processing dimensions), I used materialized views (or snapshots) and some views on slow modifying tables.
Problem:
Oracle snapshots must be refreshed and that is done in oracle with a procedure.
My question is:
How can I run an oracle procedure directly from sql server? I want to create a job that will refresh the oracle snapshots and process analysis database after that.
I know I can use OPENQUERY to return queries from oracle but I want to execute a script.
Solutions?

Another way was by doing it in a command line.
I asume there is an special job feature that can run programs.

PS. posted in another section too

View 1 Replies View Related

DTS Execute SQL Task On Access Linked Table

Mar 11, 2004

Can the DTS execute SQL task recognize a table in an Access database that is linked from another Access database?

I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.

The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.

Any Help?

View 1 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

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

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

Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available

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

Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query

Dec 6, 2006

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

View 5 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.


FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

SSIS Execute Package With Execute Out Of Process = True Causes ProductLevelToLow Error

Mar 6, 2008



Hi.

I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.

Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error


Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).

Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).

The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.

Any help would be greatly appreciated.

Thanks

Geoff.

View 7 Replies View Related

Problem Accessing A SQL Server 2000 Linked Server From SQL Server 2005

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

Connect From A SQL Server 2005 Db To A SQL Server 2000 Db, Without Linked Server Connection

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

Instructions For Setting Up A Sybase ASE Server As A SQL Server 2005 Linked Server?

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

Conditional Execute By Execute SQL Task Return Value?

Jun 25, 2007

I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?

Or is it possible to conditional run other control flow items according the the status of SQL task execution?

View 1 Replies View Related

T-SQL (SS2K8) :: OPENQUERY Syntax To Insert Into Server Table From Oracle Linked Server

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

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

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

Linked Server 2000 To 2005: Error 17 Sql Server Does Not Exist Or Access Denined.

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

Running A Distributed Query Against A Loopback Linked Server In SQL Server 2005 Is Not Supported

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

SQL Server 2008 :: How To Write A SELECT Statement To Get Data From A Linked Server

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

SQL Server 2008 :: Linked Server Tests Fine But Query Does Not Work

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

SQL Server 2008 :: View Creation Using XML Column On Linked / Distributed Server?

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

Problems Reading Data From Linked Server To Excel In SQL Server 2005

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

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

Integration Services :: Run Transaction Across Multiple Instance Of Server Without Linked Server

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

Inport And Execute Sql Server 2000 Dts Packgages In Sql Server 2005

Jan 22, 2007

hello world,

i read some kb on msde that explain how to do the task in the title, but in sql 2005 client tool i can't see where to execute dts packgage in 2000 mode,

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

Smalldatetime Comparison Between Local Server And Linked Server Fails?

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

SQL Server 2012 :: How To Insert Data Into Table From Linked Server

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







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