Distributed Query Problem

  • Hi there,

    I've created a windows application which uses distributed queries (those, uses linked server). When I run that application in my development environment, it works; but in a fresh PC that does not work.

    What can I do? Please! See below-

    I've created a link server using the following code, to access a remote SQL Server 2005 Dbase -

    EXEC sp_addlinkedserver

    @server = 'MIS',

    @srvproduct = '',

    @provider = 'MSDASQL',

    @provstr = 'DRIVER={SQL Server};SERVER=172.22.0.67;UID=;PWD=[pass];'

    In my application I've called a SP which has the following code -

    Select Convert(char(11), b.sys_date, 113) MISDate

    From MIS.DW_STG_ICB.dbo.ddpar1 a, CIBBatchDB..H_Date b

    Where a.POSTD7=b.YD_Date

    The query above executes perfectly in management studio and the application works in my development environment, too.

    But the thing is, when I Install the application in a different pc, it gives the following error -

    "System.Data.SqlClient.SqlException: The OLE DB provider "MSDASQL" for linked server "MIS" reported an error. The provider did not give any information about the error.

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MIS"."

    Does anybody have any ideas?

    Note: I've attached MDAC2.8,.Net2.0 prerequisites with the installation.

    Thanks

  • I was able to make it work as follows (using management studio interface)-

    ->Expand [Server Objects] -> Expand [Linked Servers] -> right click [New Linked Server ...] -> In general tab: Linked Server as [ICBDCDW] (it is the server name that shows as network server in management studio); Server Type: [SQL Server] -> then in Security tab, I selected the option "Be made using this security context:" and then I entered the username and password in the provided input fields.

    Now the query works-

    Select Convert(char(11), b.sys_date, 113) MISDate

    From ICBDCDW.DW_STG_ICB.dbo.ddpar1 a, CIBBatchDB..H_Date b

    Where a.POSTD7=b.YD_Date

    But, one thing is the remote/linked server name has to be same as the network name of the remote instance of SQL Server.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply