Distributed Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distributed Query Problem

Author

Topic

tarique_it
Starting Member

2 Posts

Posted - 2011-03-09 : 06:15:45

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=[user];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

tarique_it
Starting Member

2 Posts

Posted - 2011-03-14 : 08:08:05

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources