Insert Problem With Linked Server
Jul 20, 2005
Both servers running SQL 2000
I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.
The following three queries work:
insert into LinkedServer.dbname.dbo.Table2
select *
from LinkedServer.dbname.dbo.Table1
select *
into LocalTable
from LinkedServer.dbname.dbo.Table1
insert into LocalTable
select *
from LinkedServer.dbname.dbo.Table1
This query, which is what we really want to do, does not work:
insert into LinkedServer.dbname.dbo.Table1
select *
from LocalTable
and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'
I am new to all this and would welcome some help.
Adrian
View 1 Replies
ADVERTISEMENT
Mar 24, 2002
hi everybody.I have linked db2 server .Select from this server goes fine but when i do insert
case A
insert into pricing..UCIT.BOOM(A,B) VALUES(3,5)
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error
case B
SELECT * from
OPENQUERY(pricing, 'insert into UCIT.BOOM(A,B) VALUES(3,5)')
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'insert into UCIT.BOOM(A,B) VALUES(3,5)'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
how to do insert into linked db2 server?
I am running SQL 2000 sp2 on WIN20000
and DB2 UDB 7.2 service pack 4
View 2 Replies
View Related
Nov 8, 2000
Hi,
I am trying to insert data from a SQL (7.0) table into an Access table but I get an error message saying the MS DTC is nor running. Apart from E.M, how can you check this. Using Query Analyzer I can select form tyhe destination table but not insert - perhaps I have missed something - acn you please help?
View 2 Replies
View Related
Feb 26, 2008
Hey there..
I need to insert some data into a linked server where I need to insert the Identity field. When I try to turn IDENTITY INSERT on, I get this error
The object name 'Server-SQL.MyDatabase.dbo.MyTable' contains more than the maximum number of prefixes. The maximum is 2.
The line I try to execute is this...
SET IDENTITY_INSERT [Server-SQL].MyDatabase.dbo.MyTable ON
From my searching around about this error, the workaround seems to be aliasing the table name, but I can't really see how to use an alias in this situation.
Thanks a bunch!
Greg
View 4 Replies
View Related
Jul 23, 2005
I have two sql servers, I have defined each one as a linked server tothe other. I can mostly access the servers from one another, but I getthe following error on a sql insert.Insert statement...INSERT INTO [U1STSV02].[Custom Log Shipping].dbo.ls_secondary_files(database_name, tl_file_name, tl_applied, lsplanid, lssecid,compression_type) VALUES ('javaweb', 'c:', 'N', 1, 1, 0)i get an error messageServer: Msg 913, Level 16, State 8, Line 1Could not find database ID 10. Database may not be activated yet or maybe in transition.I can query the table with select using the followingselect * from [u1stsv02].[custom log shipping].dbo.ls_secondary_filesand I can delete rows from the table usingdelete from [u1stsv02].[custom log shipping].dbo.ls_secondary_filesI have searched Microsoft's site and googled for a while and cannot seemto find a solution.Both servers are running SQL Server 2000 with service pack 4Thanks in advance for any replies.Steve KuekesPhysicians Pharmacy Alliancejust remove the "1", "2", "3" from my email to reach me.
View 2 Replies
View Related
Mar 6, 2007
hi,on localServer i execute this queryINSERT INTO table (A, B, C)SELECT A, B, C FROM LinkedServer.myDB.dbo.tableeverything is fine. But if i execute this oneINSERT INTO LinkedServer.myDB.dbo.table (A, B, C)SELECT A, B, C FROM tableit is very slow. Is there any solution to make it any faster?
View 2 Replies
View Related
Oct 23, 2006
Insert statement to remote server is running very slowly. I have run Profiler and find there is a 'sp_cursor' call for each row. The source system is SQL2005 and destination is SQL2000(sp4). The linked server is using 'SQL server' type connection. Source query is against a single table with a where clause. source and destination table are identical with Primary keys. Purpose is just to move the rows. Connection is a slow network connection - should be ok. I have already overcome same problem for related update and delete queries by use of 'EXECUTE (query) AT LinkedServer' that works great - but insert can not take advantage of this...
INSERT [LinkedServSQL2000sp4].dbname.schema.tablename
({column list})
Select
{column list}
from tablename
WHERE col1 = '7/20/2006'
AND col2 in (2,5,7,12,32,54,45,33)
Any thoughts?
View 1 Replies
View Related
Aug 24, 2004
I'm writing an insert trigger in one SQL Server database that is supposed to insert another record into a linked SQL Server database. I have the linked server set up and have been using it for a few weeks in queries and stored procedure with no problem. Now that I'm trying to use it within a trigger and it just bombs.
I'm getting the following message in one of my logs and I don't know what it means... "Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE". I've googled around, but can't really find anything. Any help would be appreciated.
Thanks,
Bryan
View 1 Replies
View Related
Jul 23, 2005
Hei,We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separatedmachines).I am triing to connect them så that when one row is added to the table inthe database in main server - then the same row is added to the same tablein the second server database.I made the insert trigger on the table in the first server ( the secondserver is added as a linked server):-----------------------------------------------------------------------------------------create trigger ti_myTabe1 on myTable1 for insert asbegindeclare ........BEGINinsert into server2.myDatabase2.owner.myTable2(column1, column2, column3)SELECT column1, column2, column3FROM inserted insEND......end-----------------------------------------------------------------------------------------When I run the statement in "SQL Query Analyzer"on the first server:insert into Table1 values(va1,val2,val3)then error is coming:Server: Msg 7391, Level 16, State 1, Procedure ti_myTabe1 , Line 19The operation could not be performed because the OLE DB provider 'SQLOLEDB'was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].The straing thing is: if I run the statement in "SQL Query Analyzer"on thefirst server:insert into server2.myDatabase2.owner.myTable2 values(va1,val2,val3)then it works!But not inside the trigger!!! - What I am doing wrong ?Any idea is greatly appeciated.
View 2 Replies
View Related
Jul 14, 2006
I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:
The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'
As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?
CREATE PROC sp_mytableServiceProgram
AS
SET NOCOUNT ON;
DECLARE
@XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName nvarchar(256),
@Dialog UNIQUEIDENTIFIER;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@Dialog = conversation_handle
FROM mytableQueue
), TIMEOUT 2000 ;
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END ;
SET @XML = CAST(@MessageBody AS XML);
INSERT INTO LINKEDSERVER.dbname.user.mytable
SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no,
tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid,
tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num,
tbl.rows.value('@status', 'NVARCHAR(20)') AS status,
tbl.rows.value('@creationtime', 'DATETIME') AS creationtime,
tbl.rows.value('@note', 'NVARCHAR(250)') AS note,
tbl.rows.value('@posted', 'NCHAR(1)') AS posted,
tbl.rows.value('@kms', 'INT') AS kms,
tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid,
tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code,
tbl.rows.value('@page_count', 'INT') AS page_count,
tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number,
tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution
FROM @XML.nodes('/inserted') tbl(rows);
-- If the INSERT did not insert any rows, rollback.
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
COMMIT TRANSACTION;
END
GO
View 8 Replies
View Related
Dec 29, 2006
Hi -
I am using linked server to insert data to a table. When I do select, it does show me results but when I do insert, it does not work. My source/destination has exact same data types defined. Any idea?
insert into dbo.tb_PERSONNEL
select * from openquery(CADC1, 'SELECT PERSONNEL_1_NF.ID, PERSONNEL_1_NF.NAME, PERSONNEL_1_NF.PNUM, PERSONNEL_1_NF.PN
FROM PERSONNEL_1_NF')
I get:
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
View 1 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
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
Oct 12, 2000
Good afternoon one and all,
I have the folowing problem that I could use some help with :
I have an SQL server database acting as a back end to an access dbase. The SQL srv table contains over 32 million records and I am trying to use an append query (in access) to import a further 2 million records to the SQLSRV table. The append query fails with the message 'Insert on table bcdsales failed' followed by an ODBC timeout error message. I can append one record fine but a mass import fails.
Unfortunately i can't use SQL srv to do the import (internal policy says we must stick with access front end for now).
Any and all ideas welcomed.
TIA for your time and attention
Gurmi
View 2 Replies
View Related
Aug 13, 2012
I have a historical table on a dedicated SQL Server (let's call it the reporting db) that is populated every morning with production data that does not already exist. The data in the prod table is purged after 7 days and nothing is ever deleted from the historical table. I have set up the linked server between the two 2008 SQL Servers, but when I try to run this simple query from the reporting DB, it takes more than 5 minutes and still "executing". I eventually have to cancel it:
-- INSERT INTO Temp_Import_historical
SELECT TOP 1 *
FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a
WHERE NOT EXISTS (select [Temp_Import_ID] from Temp_Import_historical where a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])
I have omitted the INSERT statement on purpose, since I can't even get to output 1 row. Why this is such a resource intensive query?
View 9 Replies
View Related
Nov 30, 2007
I am getting error when I try Inserting data in sybase 12.5 using linked server from SQL2K5
I am able to select
Following is the code i am using.error is same for both stmts
insert into l_syb_ibt.ibtqa.dbo.rajtest (id)values (1)
insert openquery(l_syb_ibt, 'select id from rajtest where 1=0') values (1000)
please help.thanks in advance
following is the error
OLE DB provider "MSDASQL" for linked server "l_syb_ibt" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "l_syb_ibt" could not INSERT INTO table "[l_syb_ibt].[ibtqa].[dbo].[rajtest]".
View 5 Replies
View Related
Jul 12, 2007
I have a 2000 machine which calls a stored procedure on another 2000 machine via a linked server. The results come back and insert into a temporary table.
When I use the same code executing the from the 2000 machine over to 2005 machine via a linked server I cannot insert into the table. But I am able to see the data if I remove the insert statement.
I have tried to place the data into a permanent table without success. I have also checked to be sure the linked server properties are the same.
Any help on this would be appreciated. Below is the code. It is very simple and returns only one value but the bigger procedure that is ran returns several records and mutliple columns. This seems to easy but doesn't work.
DECLARE @retval AS INT
DECLARE @value AS INT
SET @value = 4
CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)
INSERT INTO #TempTable
EXEC @RetVal = Server.Database.dbo.testproc @value
SELECT * FROM #TempTable
DROP TABLE #TempTable
View 6 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Mar 23, 2006
I get this error when trying to alter a stored procedure that has an insert statement referencing a new linked server I created:
INSERT INTO [servername].databasename.dbo.DirectReport
...
Msg 18456, Level 14, State 1, Procedure Get_Direct_Pay_Move_Data, Line 17
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I added Administrators and my logon to the permissions of my linked server but still get this error when it tries to save my stored proc...the one which has that insert.
View 4 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
May 6, 2004
Hello
I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.
Here's the code:
---------------------
create table #b (type int,label varchar(100),x int,y int,so int)
declare @arg nvarchar(100)
set @arg = 'BLANKET.MaalGrupper.ID38'
declare @tsql varchar(1000)
select @tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( ''''' + @arg + ''''' )'' )'
insert #b exec (@tsql)
drop table #b
-------------------------
I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.
Can anybody help with this very irritating problem?
Thanks
MNJ
View 6 Replies
View Related
Jul 23, 2005
BCP and Bulk Insert to Linked ServersHi guys!Heres my set up:1) Im using Win2003 with MS SQL 20002) I have a linked server in SQL Server pointing to an MS Access DB.Why MS Access? Gee, I dont know. The guy who owns it refused to update hisVB app and point it to SQL Server.Anyway, I have 190,000 records in SQL Server that I wanted to dump andinsert it to MS Access.I tried to use OPENQUERY but OLE DB provider choked and wont be able tohandle that much records. Sucks!Moreover, DTS packages wont do the job. I tried it and it have the sameproblem.Now, I got one last option to go to. I EXPORTED SQL Server data to a textfile using BCP but my problem is how to IMPORT those data from the TEXTfile to my Linked Server that points to an MS Access.This is what Im trying to do:SQL Server Data ---> Text file ---> Linked Server (MS Access)bcp LinkedServerName..MSAccess_TableName in Shares1_tmp.txt -c -T -t ',' -r''SQLState = 08001, NativeError = 17Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does notexist or access denied.SQLState = 01000, NativeError = 53Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).Thank you and you guys have a nice day.--Message posted via http://www.sqlmonster.com
View 5 Replies
View Related
Jun 22, 2007
I have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
View 4 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
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
