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

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

Very-NewBie Linked Server Problem

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server

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  |


View Complete Post

More Related Resource Links

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

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!!!

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

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


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

Linked server login problem -- Msg 15007


I need to have an access database as a linked server, which can be queried from a web application.  It's all set up on an old machine with 2003 server and SQL 2005 express, but now I am trying to migrate to a new machine with 2008 everything.  The linked server itself was not a problem, but creating a login refuses to work.  I tried the following:

sp_addlinkedsrvlogin 'nameoflinkedserver', false, 'NT AUTHORITY\IUSR', 'Admin', NULL

which is roughly what worked on the old system.  Alas, it fails with:

Msg 15007, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 76
'NT AUTHORITY\IUSR' is not a valid login or you do not have permission.

That does seem to be a valid login; 'NT AUTHORITY\IUSR' is listed as one of the server's logins, and is the one that gives the web application access to the SQL server database itself.  I am logged in as administrator on the machine and can do everything else regarding SQL Server, so it's odd that I would not have permission for this.  Does anyone have a guess why this happens?

SQL Server Session state problem.


Hi to all,

I have some things that I want to know. I am right now using a free asp.net webhosting ("somee.com"). The problem is that when I am visiting a page, there's a tendency that values stored in my session variables are lost. Therefore, it causes an error when the requested page is loaded.

1. I am thinking of using sessionState mode="SqlServer". Will it solve my problem?

Actualy Im working on it but Im having problems when connecting to the sql database.

2.  Is that a problem in the webhost?

3. Do free webhosting sites provide allow session state in the sql server?. Because ive tried it locally into my computer and error say's i should intall the ASP.NET Session State SQL Server version 2.0 or above.

SQL Server Express2008 R2 brings app down, instance problem


I have tried dozens of changes and modifications to many settings as I will outilne here and all to no avail.

The short answer here though is that I uninstalled SQL Sever Express 2008 R2 and went back to the "NON" 2008 R2 and got things working. But like the fool I am wanted to run on the more current rev and updated again, all problems returned....



Win Server 2003 R2 SP2


VWD2008 Express (removed)




XP Sp3

VWD2008 Express

SQL Server Express 2008


I was orginally getting this message after updating to SQL Sever Express 2008 R2, I removed VWD 2008 Express from the web server, no good, then uninstalled SQL Server 2008 Express R2 and reinstalled SQL Server 2008 Express. worked but had issues, couldn't install MGTM Studio. Then I removed SQL 2008, cleaned folders and reg. Reloaded SQL Server 2008 Express, still flaky, couldn't get the Management Studio to install. So I removed it again and used the Web Platform Installer, which worked but loaded 2008 R2 again. Which brought back the message.

Failed to genera

Problem in Installation of SQL Server 2008


Dear All,

I have Sqlserver2008 setup.

It is work in wndows Xp.

But I have to Install it on Windows 7 ,during installation process it requires some patch and I am unable to get it.

Plz some buddy can help the same.


Rakesh gupta

Problem download file from SQL Server


Hello everyone. I am having an issue downloading files that I have store in SQL Server. I have no problem in a WinForm. What am I doing wrong in my code?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim sch As New clsReport
        Dim fileData As New clsReport.Letter
        fileData = sch.DownloadLetter(Session("LetterID"))
        Dim ms As System.IO.MemoryStream = fileData.fil

        Dim fil As Byte() = ms.ToArray
        Dim nam As String = fileData.name
        Dim ext As String = fileData.ext
        If Not fil Is Nothing Then
                ' Response.Clear()
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + nam & "." & ext)
                ' Response.AddHeader("Content-Disposition", nam & "." & ext)
                Response.AddHeader("Content-Length", fil.Length.ToString)
                Response.ContentType = "Application/octet-stream"
                Response.Buffer = True
            Catch ex As Exception
            End Try

        End If

    End Su

Problem opening dotnet 1.1 projet on 2008 web server


 We recently upgraded to a 2008 development web server and I installed all the stuff to get dot net 1.1 app working on the server.  I can host them and they work.  My problem is that when I try to open one in VS 2003 I get the following error.  I set up the web server the same as my previous one, and I think a long time  ago I got this error when we set up current one but do not remember how to resolve it.  So I moved my project files from the old server to the new server and then I get these errors. 


The web Server reported the following error when attempting to create or open the Web Project located at the following url: Http://localhost:/MobTracker. 'Http/1.1 500 server error

Problem when installing Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint


I have configured the SharePoint Production Server(Standalone) with WS 2008 64 bit version, SQL Server 2005 with SP3 , Visual Studio 2008 and MOSS 2007(SP3). I have configured SSRS 2005 in SharePoint integration mode. Everything works fine till the last step. Now, i am trying to install Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies feature and failed. I am getting below error message.

Product: Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies -- You must first install SharePoint Server before installing SQL Server 2005 Reporting Services Add-in for SharePoint Technologies

Any help will be very appreciated!


passing object values from server to client problem


I get a message that x_login is not declared.  I added Dim login as String = "123", but that didn't help.  What's the problem?

code behind....

Dim Login = "123"
x_login.Value = loginID


<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<form id="simForm" runat="server" method='post' action='https://test.authorize.net/gateway/transact.dll'>
        <input id="HiddenValue" type="hidden" value="Initial Value" runat="server" />
        <input type='hidden' runat="server" name='x_login' id='x_login' />
        <input type='submit' runat="server" id='buttonLabel' />



Linked Server to access Excel 2007



I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.

Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
Exec sp_dropserver 'AB2','droplogins'

Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges


EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1

The DisAllowAdHocProcess in
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