Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Insert Into Table On Linked Server

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})
 {column list}
from tablename
WHERE col1 =  '7/20/2006'
  AND col2 in (2,5,7,12,32,54,45,33)

Any thoughts?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Insert A Text Datatype From A JET Linked Server Table
I am curious to see if anybody has figured out a way to insert a Memo field from Access 97 into a Text field in SQL Server 7. The problem is that it seems a view must be used because the only way to access a linked server is with the notation 'linked_server_name...table_name.field_name'. This notation is okay for select statments (and creating views), but it is not okay for READTEXT, or WRITETEXT. Does anybody have any ideas? Thanks in advance.


View Replies !   View Related
Access - SQL Server Linked Table : Insert Failed
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


View Replies !   View Related
Linked Server Unable To Insert Result Into A Table
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.





SET @value = 4


CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)



EXEC @RetVal = Server.Database.dbo.testproc @value


SELECT * FROM #TempTable


View Replies !   View Related
HOWTO Insert Into ACCESS Linked Table Via OLE DB Destination Task
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 Replies !   View Related
Truncate Table In Linked Server Linked To (MS Access)
Dear Experts,

when i try to truncate table in the linked server ( linked to MS Access database) like:
truncate table LSchools...Payment
it will give the error:
maximum number of prefixes. The maximum is 2

when i use the statement like :

delete from LSchools...Payment

it will give the error like

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry

( where payment may have more than 100.000 records)

I increase the MaxLocksPerFile to 120.0000 on the server registry

it works , but it is taking long time to execute.

1.Is there a way to use truncate?
2.Is there a way to execute MS ACCESS Query from (query analyzer), like ('Delete * from payment') where this is fast and no record locking encountered

Many Thanks

View Replies !   View Related
Insert On Linked Server

I am using SQL Server to connect to iSeries DB2 on AS/400 with Linked Server through ODBC DSN. I was able to run SELECT using four-part naming convention in SQL Query Analyzer. But INSERT is not working. Here's my query:-


It generates the following error.

OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - TABLE1 in SCHEMA1 not valid for operation.]

The file is not journaling. I have already set COMMIT to *NONE. So it should work in SQL Query Analyzer.

Needless to mention that StarSQL executes the query without errors.

Any ideas? Thanks.

View Replies !   View Related
Linked Server Insert
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 Replies !   View Related
Insert Into Linked Server
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 Replies !   View Related
Linked Server Insert Problem
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 Replies !   View Related
Insert Into Linked Server Problem
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 Replies !   View Related
Insert Problem With Linked Server
Both servers running SQL 2000I have set up on our local SQL server (using Enterprise Manager) a linkedserver running on our ISP. Just did new linked server and added remotepassword and login.The following three queries work:insert into LinkedServer.dbname.dbo.Table2select *from LinkedServer.dbname.dbo.Table1select *into LocalTablefrom LinkedServer.dbname.dbo.Table1insert into LocalTableselect *from LinkedServer.dbname.dbo.Table1This query, which is what we really want to do, does not work:insert into LinkedServer.dbname.dbo.Table1select *from LocalTableand returns the error: 'The cursor does not include the table being modifiedor the table is not updatable through the cursor.'I am new to all this and would welcome some help.Adrian

View Replies !   View Related
Identity Insert On A Linked Server
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!

View Replies !   View Related
Oracle RDB Linked Server 22.17 Won't Insert
Dear All,
The issue I have that I can't insert 22.17 (or other odd fractions) into an Oracle RDB database through a linked server using the RDB ODBC Driver.
insert into dev_shlagh_fin...simon
values (22.16)
insert into dev_shlagh_fin...simon
values (22.17)
insert into dev_shlagh_fin...simon
values (22.18)
(1 row(s) affected)
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. 
[OLE/DB provider returned message: [Oracle][ODBC]Numeric value out of range.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005:   ].
(1 row(s) affected)
The problem seems related to that outlined in  PRB: Numeric Value Out of Range Error with MS Oracle ODBC Driver Version 2.5 or Higherin  - Q Article 199293. This is directly concerned with Oracle not RDB and for select statements but the issue is seems basically the same - OLD MDAC 2.6 is fine, anything later and you get the error. The problem is the workaround does not offer any help for inserts. Reading between the lines and converting syntax to RDB SQL you have this:-
insert into  openquery(dev_fin,'select CAST(quantity AS AS NCHAR(8))) From simon')
VALUES (22.17)
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. 
[OLE/DB provider returned message: [Oracle][ODBC][Rdb]%SQL-F-SYNTAX_ERR, Syntax error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005:   ].

This appears to be a syntax error as it fails with all values but only when you try to insert, if you just select, i.e.
Select * from openquery(dev_fin,'select CAST(quantity AS AS NCHAR(8))) From simon')

 his worke fine.
I have tried all I can think of...
HELP anyone please.. Its crucial that this works and rolling back to MDAC 2.6 does not seem a viable option!


View Replies !   View Related
Linked Server Referred By Insert Trigger
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 Replies !   View Related
Problem Using A Linked Server In An Insert Trigger
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.


View Replies !   View Related
Unidata - SQL Linked Server Insert Statement
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



I get:

Msg 8152, Level 16, State 14, Line 2

String or binary data would be truncated.

The statement has been terminated.


View Replies !   View Related
Insert To A Linked Server Possible Via Service Broker?
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
    @XML XML,
    @MessageBody VARBINARY(MAX),
    @MessageTypeName nvarchar(256),
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
    --BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
    -- Receive the next available message
        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)
    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

View Replies !   View Related
Urgent SQL Sybase Linked Server Insert Problem
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 Replies !   View Related
Append Query From Access Table To Linked SQL Server Table Failing
Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?

View Replies !   View Related
ADOX To Create A DSNless Linked Table To A SQL Server Table In MS Access?
QUESTION: How do I use ADOX (VB) to create a DSNless linked table to a SQL Server Table in MS Access?

- Will need a code skeleton that satisfies all conditions above (Including the minimum table properties required).
- SQL Server Name: MySQLServer
- Database Name: MyTestDB
- Table Name: MyTestTable

View Replies !   View Related
Altering Table Definition On A Linked Server Table.
SQL Server 7.0 (SP1)
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.

Thanks for any input.

View Replies !   View Related
Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
 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();
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;
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;
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;
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"]));
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);
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);
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);
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);
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);
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);
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();

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
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 Replies !   View Related
Login Failed For User 'NT AUTHORITYANONYMOUS LOGON' For Insert Reference Using New Linked Server
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 Replies !   View Related
Linked Table/server To Db2(AS/400)
I'm very new to SQL server so forgive me for the newbie questions.

Basically I have some tables/files on an AS/400.
I want to have a link to those files/tables from SQL server 2005 (like how you would link a file in msAccess.

Is this possible?

View Replies !   View Related
Table Structure Of Linked Server
Hello!Does anybody know how to get tables structure of linked server (DBF tablesvia ODBC connection). I know that table structure of "normal" (not linked)server can get from systables and syscolumns tables, but now I need astructure of linked server tables.Thanks!

View Replies !   View Related
Temp Table On Linked Server
Trying to do this all day and googling for answers but found none, hopesomeone can help. Thanks in * intoOPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTableReason: I am joining local tables with linked server tables using theformat "LinkedServer.database.owner.object" to execute a query, ittakes forever to execute since the tables joined on the remote servershave more than 50Mil records. I read somewhere that sql server needs tocopy the tables locally to the temp db and does the join there, hence Iwas hoping to dump the data of the local database into a temp table onthe remote server and then do a join with OPENQUERY, which will executethe query on the linked server and return the results.

View Replies !   View Related
Truncate Table On Linked Server?
Can one use Truncate Table on a linked server table? When I try it, I get amessage that only two prefixes are allowed. Here's what I'm using:Truncate Table svrname.dbname.dbo.tablename

View Replies !   View Related
Creating Table On A Different Linked Server
Using Microsoft SQL 2000

When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.

Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?

Example :

CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:

The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?

Tx in advance


View Replies !   View Related
Foreign Key From Another Table In A Linked Server

We're having a few linked servers in our company. some tables in one of the linked servers include columns (better saying, foreign keys) of tables in another server. I wonder if there's a way to create a foreign key referencing one column in another server. That is, suppose there's Column A in table A in Server A which references Column B in table B in server B, is there a way to create column A as foreign key referencing column B?

Any idea is appreciated.

View Replies !   View Related
Foreign Key From Another Table In A Linked Server

We're having a few linked servers in our company. some tables in one of the linked servers include columns (better saying, foreign keys) of tables in another server. I wonder if there's a way to create a foreign key referencing one columns in another server. That is, suppose there's Column A in table A in Server A which references Column B in table B in server B, is there a way to create column A as foreign key referencing column B?

Any idea is appreciated.

View Replies !   View Related
INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Hi...  I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update  local SS DB/table - new records writes OK in the table.  BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs.  AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple -  Add new records in a SS table using SSIS .  I only care for new rows and  not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER -  using SELECT stmt
2.  I have LoopUp component that will look for NEW records -  Directs all rows that don't find match and redirect rows (error output).
3.  Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4.  I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing.  And I do not see error in my package either.  It is not failing.
Thanks in advance!

View Replies !   View Related
Create Temp Table On Linked Server From Local Server

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 Replies !   View Related
Using Udl In The Connection For A Linked Table In Access From Sql Server
I have an Access 2000 application that uses sql server as back end.We're trying to avoid using ODBC to set up linked tables in access. Isudl an alternative way? I set up the udl file but don't know how touse them for linked tables in Access? This is what I have in my udlfile.----[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=mySQLDatabase;Data Source=myServer01----Thanks

View Replies !   View Related
SQL Server Linked Table To Dbase III Files

I am trying to use linked tables to connect SQL Server 2000 to a legacy system using dbase III files. (I need real time read only access to these files)

I have created a linked table from SQL server to a folder on the C drive which contains the dbase III files, using an ODBC DSN which uses "Microsoft dbase driver (*.dbf)". DSN tested successfully using Excel. Linked server connection is then created using "Microsoft OLE DB Provider for ODBC drivers".

The dbase tables appear OK in Enterprise manager, but I cannot get a query to work in SQL Anayzer, using the 4-part name syntax.

My query is just :
SELECT * FROM LinkedTable...customers

Error message is "Invalid schema or catalog specified for provider 'MSDASQL'". Now I am pretty sure dbase files do not support any sort of schema / catalog set up, so I suspect SQL Server is looking for something it is not going to get.

One clue might be that in Enterprise manager, under the catalog column, I get the pathname to the dbase file, ie c:customers.dbf, which I cannot enter in the 4-part syntax.

Any suggestion welcome !!!

View Replies !   View Related
How Can I Create A Temp Table On A Linked Server?

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
from myRemoteTable
inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID

View Replies !   View Related
Copying Table From Local To Linked Server

I'm trying to copy all the data from a single table across to a remote linked server and I'm having all sorts of problems.

The SQL query I'm attampting is this:-


EXEC sp_addlinkedserver 'GODZILLA', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'GODZILLA', 'False', 'rmtUser', 'lclUser', 'password'


SELECT horse_id, horse_filename, horse_time, horse_new, horse_driveLetter


EXEC sp_droplinkedsrvlogin 'GODZILLA', 'lclUser'
EXEC sp_dropserver 'GODZILLA'

Basically - this doesn't work as an error is returned:-


The object name 'GODZILLA.Racing.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Now - it has no problem running a SELECT (ie read-only) query from the remote DB but why does it have a problem with writing? And if thats the issue, why doesn't it say so. Am I going about this the right way???

Please help!

View Replies !   View Related
Create Table In MS-Access From SQL Server (linked)

The scenario is I have linked server with access. I want to check the MS-Access Database table weather the table Exists or Not.
If Exists I have insert the newly updated records. else I have to create a same table and I have to insert the records.

Now my question is how could I check weather the table is exists or not in MS-Access Database and If Not Exists, Then How can I create the new table thru linked server Query


View Replies !   View Related
Linked Server Table Name With Period Issue

I've been investigating this for a number of hours with no success, where i cannot cater for a table name that includes a period on a remote server. The table names include periods to allow me structure the database along as a collection of namespaces, I can do selects and updates but cannot get inserts to work, i have illustrated this with the following example

I have two DB servers S1 and S2, i have configured SQL accounts on both servers as sysadmin members to allow me investigate linked servers, where i have configured S2 as a linked server on S1.

On S2 i have added two tables each with a single row
create table dbo.RemoteTable1
 Col1 smallint not null,
 Col2 smallint not null
create table dbo.[RemoteTable.1]
 Col1 smallint not null,
 Col2 smallint not null
insert into dbo.RemoteTable1 values (1, 1)
insert into dbo.[RemoteTable.1] values (1, 1)

On S1, if i try the following all works as i expect
select * from S2.MyDB.dbo.RemoteTable1
Col1   Col2  
------ ------
1      1
update S2.MyDB.dbo.RemoteTable1 set Col2 = Col2 + 1
select * from S2.MyDB.dbo.RemoteTable1
Col1   Col2  
------ ------
1      2
insert into S2.MyDB.dbo.RemoteTable1 values (2, 1)
select * from S2.MyDB.dbo.RemoteTable1
Col1   Col2  
------ ------
1      2
2      1

but if i try the following, note i need to surround the table identifier with [] due to the period in the table name, i get the following
select * from S2.MyDB.dbo.[RemoteTable.1]
Col1   Col2  
------ ------
1      1
update S2.MyDB.dbo.[RemoteTable.1] set Col2 = Col2 + 1
select * from S2.MyDB.dbo.[RemoteTable.1]
Col1   Col2  
------ ------
1      2
insert into S2.MyDB.dbo.[RemoteTable.1] values (2, 1)
Server: Msg 16933, Level 16, State 2, Line 1
The cursor does not include the table being modified or the table is not updatable through the cursor.

I have tried the following naming variations on the table name within the insert statement but with no luck ["RemoteTable.1"] and ['RemoteTable.1']. I cannot change to using a _ char instead of the period at this stage as there are > 100 tables.

Thanks in advance



View Replies !   View Related
Linked Server Not Displaying Table Details
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?

View Replies !   View Related
The OLE DB Provider &"MSDAORA&" For Linked Server &"....&" Does Not Contain The Table &"COUNTRY&". The Table Either Does Not Exist Or The Current User Does Not Have Permissions On That Table.
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.

View Replies !   View Related
Linked Server Table Field Case Issue!!!!
I am using a linked server to select data from one SQL server to another SQL server database.
The problem is
select EMPLOYEE_ID From xxx.xxxx.dbo.EMPLYEE_TBL does not work over linked server in target DB.
select Employee_Id From xxx.xxxx.dbo.Emplyee_Tbl works fine

the same sql
select EMPLOYEE_ID From xxx.xxxx.dbo.EMPLYEE_TBL works fine in taget DB

is the linked server causing the case problem? Is so how to handle the case issue over linked server

View Replies !   View Related
Getting Count From Table In Linked Server Using Runtime Query
Hi Friends,
I want to have solution for one of the problem.
The requirement is like this :
I want to write stored procedure or function which will take parameter as SQL Server name, DB name, UserName and passwod.
This Stored proc will connect to Remote server using these parameters and will get the count of the rows in one of the table.
I created the connection using the linked server

EXEC sp_addlinkedserver @SerevrName,N'SQL Server'


EXEC sp_addlinkedsrvlogin @SerevrName, False, Null, @ServerUserName,@SerevrPws
Now I am trying to get count using following query :
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
But the question is that the execution goes this way :


Now how to assign this count value to some variable so that I can use it later ...?
Going forword I want to use cursor and get the rows in these table using cursor ...?
How can I assign values returned from any runtime query to temporary variable or table ...?
I tried another approach also:
I put remote connection and query execution in inner stored proc called usp_GetTableRowCount
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'

and in outer stored proc : referenced the inner stored proc like this

exec @AFSDataRowCount = dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>>
The execution of dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> gives me exact no of rows
but when I see value of AFSDataRowCount, I get 0.
Kindly help me out whereever I am making mistake or else pls tell me any other approach to follow.
Thanks in advance.

View Replies !   View Related
Can't Add A Linked Table To SQL Server 2005 With Access 2003

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE.  I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections.  The problem is when I try and add another linked table.  I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.


Any suggestions?



View Replies !   View Related
Insert From Linked Oracle Server Into Mssql (was &"Urgent Requirement- HELP NEEDED!!&")
Hello I'm New to this forum, i'm looking for help with the following:

I linked Oracle server to MSSQL as a `linked server,

Under MSSQL i have the following:

PersonTable (4 int ID sequencially and automatically generated PK, EMP number, picture (which should be the ID.jpg), department ID (which related to the ID of the department table, Address1,Address2....)

DepartmentTAble ( ID int 4 sequencially and automatically generated, Department description )

From ORACLE linked server I can have my view that I want to fill the information except that in Oracle I don't know the ID of the persontable in MSSQl neither the ID of the department.

I need help and consultation on the following:

I need to schedule a script or function to do the following :

1-read the new Departments that are not in the MSSQL from the oracle and insert them into the MSSQL department tabe 2 times / day
2-Read the new persons in the oracle and insert them into the MSSQL person table and inserting in their respective Picture field the ID that was used for this record in MSSQL and the correct department ID from MSSQL which is related to ORACLE department table.

NOTE: THE CLIENT refused to write triggers on the ORacle tables

Please let me know if that would be possible, if anyone can offer his services for helping me with that it would be really appreciate. This is a requirement for a project in hand.


View Replies !   View Related
Executing A Sp Via ADO Which Makes A Reference To Linked Server Table Results In Error

Facing a strange problem, but obviously not expected earlier on, I am trying to execute a stored procedure via ADO which refrences a linked server table and I get an error specifying
OLEDB provider SQLOLEDB does not contain table "<DatabaseName>"."<owner>"."<tableName>"

Has any one else encountered this problem before ?

View Replies !   View Related
Reference A Temporary ##global Table Of A Linked Server Using 4-parts Identifier
Is it possible to reference a temporary global tale (##table)  of a linked server using the 4 parts "identifier": linkedserver.database.schema_name.##object_name ??
Thanks in advance for your help.

View Replies !   View Related
Table Loads From DB2 - Parallel Processes, Linked Server, And Lightweight Pooling Issue
Hello.  I have a 32-bit SQL 2005 (SP1) server that is my current Production server.  I also have a 64-bit SQL 2005 (SP1) server that will become my Production server.  I have several SSIS packages that load/refresh data on a nightly basis to a few of my databases from DB2 (MVS).  I have the packages setup on the current Production server (32-bit) and all is working well through the Microsoft OLE DB provider for DB2.  However, on the 64-bit server, I am experiencing some issues with the SSIS packages failing due to large loads.  Loads that are loading tables with 500K, or less, data seem to run without issue (through SQL Agent Jobs).  But, larger table loads are failing.

I do have a linked server set up on the 64-bit server to the 32-bit server, for other processes.  And because of this I have lightweight pooling turned off on the 64-bit server (because of distributed querying).  Lightweight pooling is turned on on the 32-bit server.  Could this be what is causing some of my issue?  Since I don't have the lightweight pooling option turned on (on the 64-bit server), am I not getting the proper amount of through-put for my 8 dual core CPU server?


View Replies !   View Related
Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager
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


View Replies !   View Related
How To Insert Into Linked Db2 ?
Hello everybody.I have 3 linked db2 from linked server
works fine but insert into linked servers gives errors.
I have
SQL 2000 sp2 and UDB 7.2 to set up linked db2 to accept insert into tables located on db2 ?
2. what provider should be used?

View Replies !   View Related

Copyright © 2005-08, All rights reserved