.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Problem with linked tables

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

I am linking to some Access 97 tables from SQL Server 2005, however, I have had a lot of problems. These are generally sorted out by restarting the SQL server, but after a while it fails again. (queries to XLS files on the same configuration are fine)

If I go to SQL Server Management studio and check the connection to the Access databases, I get a 'Cannot initialise data source object of OLE DB provider' error message. However, if I go to the provider properties for the Jet provider and deselect 'allow inprocess'. it works. If I reselect it, it fails.

Can anyone help?

View Complete Post

More Related Resource Links

Problem with viewing grid with linked tables



My grid doesn't show on the web form when I configure its datasource to access the link table and display data from the receiver table. Both link and receiver tables are associated/linked in the .dml file. Any ideas why the grid doesn't show on the form?



Linked server and sensitive to register name of table. Problem with UPDATE.

Hi All. I try to work with table with "sensitive to register" name through Linked Server (MSSQL 2005/2008) and get the problem with UPDATE statement. Reason: MSSQL generates UPDATE statement with "un-quoted" table name. With SELECT/INSERT/UPDATE - no any problems. ----- Linked Database Information: 1. Firebird 2.5 2. OLEDB Provider: IBProvider v3 3. Database dialect: 3 Metadata: CREATE GENERATOR "GEN_ID_TableWithMixName1"; CREATE TABLE "TableWithMixName1" ( TEST_ID INTEGER NOT NULL, "Col" VARCHAR(100), DUMMY_COL INTEGER, CONSTRAINT "PK_TableWithMixName1" PRIMARY KEY (TEST_ID) ); CREATE TRIGGER "BI_TableWithMixName1_TEST_ID" FOR "TableWithMixName1" BEFORE INSERT AS BEGIN IF(NEW."TEST_ID" IS NULL)THEN NEW."TEST_ID" =GEN_ID("GEN_ID_TableWithMixName1",1); END; ------- MSSQL Test 1. MSSQL: select * from IBP_TEST_FB25_D3_V3...TableWithMixName1; IBProvider: Command_Execute   SELECT "Tbl1002"."TEST_ID" "Col1004",         "Tbl1002"."Col" "Col1005",         "Tbl1002"."DUMMY_COL" "Col1006"   FROM "TableWithMixName1" "Tbl1002" No Problem ------- MSSQL Test 2. MSSQL: delete from IBP_TEST_FB25_D3_V3...Tabl

Search in two linked tables

I've 2 tablesCorsi with a list of courses and AttivitaCorsi where I've a list of activities linked to each courseCREATE TABLE [dbo].[AttivitaCorsi]( [ID] [int] IDENTITY(1,1) NOT NULL, [IDAttivita] [int] NULL, [IDCorso] [int] NULL, [ConcurrencyId] [timestamp] NULL, CONSTRAINT [PK_AttivitaCorsi] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [dbo].[Corsi]( [ID] [int] IDENTITY(1,1) NOT NULL, [IDTipologia] [varchar](1) NULL, [IDFornitore] [int] NULL, [Descrizione] [varchar](200) NULL, [ConcurrencyId] [timestamp] NULL, CONSTRAINT [PK_Corsi] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[Attivita]( [ID] [int] IDENTITY(1,1) NOT NULL, [DescrizioneAttivita] [varchar](200) NULL, [ConcurrencyId] [timestamp] NULL, CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Attivita table is used for know the description of each attivita in AttivitaCorsi (using IDAttivita)Now I've a SP where I can

Linked Server. DBTYPE_DBTIME2. Problem with Precision and Scale

Hi I added to my OLEDB provider (for IB/FB) the support of DBTYPE_DBTIME2 (from MSSQL Native Client) FB/IB supports a data type "TIME" with four digit in fraction of second. By other words: "hh:mm:ss.ffff" So, I define Precision=13, Scale=4 When I try to work with this DBTIME2-column through linked server, MSSQL 2008 returns the error: Msg 7356, Level 16, State 1, Line 7 OLE DB "LCPI.IBProvider.3" "IBP_TEST_FB25_v3" . "COL_TYPE_TIME"  .... "LENGTH" at compile time: 5, at execute time: 4. Ok. I change the Precision to 16 and Scale to 7 (as in SQLNCLI) With this settings, MSSQL 2008 can to work (read/write) with my DBTIME2-column. I tried to use new column flags [DBCOLUMNFLAGS_SS_ISVARIABLESCALE]. With (16/7) - no problem. With (13/4) - I get the same error. Question: How to define my DBTIME2-column with required precision and scale (13/4)? --- I tried to explore how SQLNCLI works with the time(4) column (precision:13, scale:4) But did not found anything unexpected, except DBCOLUMNFLAGS_SS_ISVARIABLESCALE

Problem when adding Linked Server...

Hi @all,I try to add a new Linked Server to my DB in Microsoft SQL Server Management Studio Express...The following error is returned (SERVER2 is the Linked Server to add): There is no remote user 'sa' mapped to local user '(null)' from the remote server 'SERVER2'. (.Net SqlClient Data Provider)I entered the user sa and the correct password for SERVER2 but it doesen't work...When I connect to SERVER2 in Management Studio with the same infos it works fine...So please help...

Linked Server Problem

We migrated our production SQL Server 2000 database to a new machine and all seems to be working well except for our linked server on our SQL Server 2005 instance that points to our SQL Server 2000 database that was just moved.  We get the following error when we try to query a table on the 2000 database.  I have read this article in the Books online but that does not seem to apply to us ( http://msdn2.microsoft.com/en-us/library/ms175496.aspx ). OLE DB provider "SQLNCLI" for linked server "SQLPRD-DBS" returned message "Communication link failure". Msg 233, Level 16, State 1, Line 0 Named Pipes Provider: No process is on the other end of the pipe. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'sa1'.   Anyone have any ideas? Thanks!!!

problem seems to be related to grouping by on columns with the same name in different tables


using OLEDB provider IBMDADB2.1

and the following SQL run from ReportBuilder


sect.name as Team,

tasktype.name as TaskType,










The SQL runs fine in a DB2 query tool but when run through ReportBuilder I get

[DB2/LINUXX8664] SQL0119N  An expression starting with "TASKTYPE" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.  SQLSTATE=42803


Basically the problem seems to be related to grouping by on columns with the same name in different tables.

Anyone know a work around?

Problem with Linked servers from sql 2005 to Excel file


I am probably doing something stupid BUT I am creating a linked server to an excel file, I have done this on sql2005sp2x64 using the ace ole 12 provder, and replicated on another machine running sql2005sp2x32 using the jet4 provider and I am having the same problem on both.  I have turned allow inprocess option on on both providers.  I as an administrator on the machine where the database and datafile are residing have no problem accessing the linked server and the file underneath however any enduser that is not in the local administrators group gets the error   Cannot initialize the data source object of OLE DB provier "Either provider I have tried" for linked server    SQL Server Error 7303.  I have checked file permissions, and they are open to the world, I have tried both use existing authentication and use no authentication when accessing the linked server.  and I have tried endless different combinations of providers and provider strings.  I have went as far as having the enduser create a linked server to an excel file on his local machine (which he is admin on and I am not)   and he can access the file .. and I can not.  so I am just assuming there is some sort of security setting somewhere .. that I am just missing.



Multiple Tables needed for Report Builder Problem


I have 3 tables I need to join: Voucher, Voucher_Detail, and Place
Voucher is related to Voucher_Detail by column voucher_run_id
Voucher has no relationship to Place
Voucher_Detail is related to Place by column place_id

I have solved the one to many relationship problem I was having between Voucher and Voucher_Detail by adding a New Named
Query called LeftOuterJoin.

In my DSV, the Voucher table links to the LeftOuterJoin which links to the Voucher_Detail which links to the Place

When I go into Report Builder, as soon as I select Voucher_Detail ( which is the table that has the most columns I need in the report),
the Voucher table disappears from view.

Not sure what I have missed. Obviously I need access to all 3 tables' data.

Problem aligning two tables


Hi, I'm fairly new to aspx. I'm trying to create two quite similar pages of web form. However, the two tables on the two different pages have different table style and one of them drags across the whole page and the other just occupies half of it. I've tried dragging the table and changing the style, but nothing seems to help. Is there a solution?

Access 2007, SQL 2008 and linked tables


I have a bunch of databases running on a SQL server 2008 instance.  They're all set to the default Latin collation, and the server is set to UK English locale.


I have an access database using linked tables to the SQL server.  The lcal PC is Windows 7 and also set to English UK locale.


However, the date fields in the SQL server tables are interpreted by Access as "text" type, not date.  When I type a date into them in standard UK english format - dd-mm-yyyy, access accepts them but the SQL server rejects them because for some reason it is wanting to store the data in the format yyyy-dd-mm.


I realise I'm no expert, this is probably something really simple and I'm probably being stupid, but can anyone shed any light on WTF i going on here and why, and how I fix it.  I realise I could code in access forms something to force the date into yyyy-dd-mm format and re-interpret it to English when I want to display it, but that's clunky and horrible.


Any ideas gratefully received.  Thanks.

Another Oracle linked server problem


I have trouble making a linked server from a SQL Server 2008 R2 64bit to an Oracle database.  I have a linked servers on a few other PCs working fine to this same Oracle server.

On this particular one, I configured the following and Command Test connection shows that it is working fine:


But when I tried to create the linked server as shown below, it fails.

The error is: 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "CARE".
OLE DB provider "OraOLEDB.Oracle" for linked server "CARE" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

I can't find what other parameters I could adjust.  How should I go about solving this problem.  Thanks.

Very strange Linked Server problem



I've got a server setup with SQL2008R2. It connects (linked server) to another SQL server which runs SQL2005. The link works fine in management studio, and it also works ok in code (where I use it to query the data). But after a few minutes of inactivity, suddenly the link begins to fail in code (SQLException; System.Data.SqlClient.SqlException: SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. ). I then open up management studio, click "Test connection"; management studio says it's ok. Running the code again, and it suddenly works fine for a few minutes, untill the same problem appears.

Seems like a bug, but is there a solution for this problem?

MCTS in Web Application Development in .NET 2.0

Very-NewBie Linked Server Problem


I'm having a problem selecting from an Excel linked server...


1. Create a new simple Excel file at C:\DeleteMe.xls and add a couple column headers and a row of data. e.g.



ColA | ColB |


123  | 456  |


Linked Server using Microsoft.Jet.OLEDB.4.0 problem


Hi all,

I'm using following code to create linked server and then access data

EXEC sp_addlinkedserver N'XLS',

'Jet 4.0',




'Excel 5.0;'

EXEC sp_addlinkedsrvlogin N'XLS', FALSE, NULL, Admin, NULL


Entity Framework - DB tables problem


I have a problem with the Entity Framework. Everything seems to work (created edmx file, associated with a DB, generated edmx.sql) except that tables in the DB are not created.

As the result during updating the entity container (SaveChanges method) an exception is thrown telling "{"Invalid object name 'dbo.Entity1Set'."}".

Correct me if I am wrong but the editor's operation "Generate Database from Model" should create the edmx.sql file (the file is created) and tables in the DB?

What am I doing wrong? I am using the VS2010 and tried different projects: console app, ASP MVC2; every time the result is the same: tables in the DB are not created.

Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:


I installed linked server using all this 3 providers

    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.

  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend