problem with distrubuted query

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)

problem with distrubuted query

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-28 : 08:23:04

CREATE PROCEDURE dbo.usp_GetAssessmentCodes

(
@RouteToCode varchar(6)
)
AS
DECLARE @ERROROCCURRED VARCHAR(3000)
BEGIN TRANSACTION
EXEC sp_dropserver 'LINKED_OLAP'
EXEC sp_addlinkedserver
@server='LINKED_OLAP', /* local SQL name given to the
linked server */
@srvproduct='', /* not used */
@provider='SQLOLEDB', /* OLE DB provider */
@datasrc='148.171.86.78', /* target server name (machine name) */
@catalog='IIF_INTEGRATION' /* database */
set @ERROROCCURRED = @@ERROR
if(@ERROROCCURRED <> '')
goto erroroccurred
SELECT * FROM LINKED_OLAP.IIF_INTEGRATION.dbo.tROUTE_CODE WHERE RTE_TO_CD = ltrim(rtrim(@RouteToCode))
EXEC sp_dropserver 'LINKED_OLAP'
set @ERROROCCURRED = @@ERROR
if(@ERROROCCURRED <> '')
goto erroroccurred
commit
erroroccurred:
if(@ERROROCCURRED <> '')
rollback

i am getting following error :

[Microsoft][ODBC SQL Server Driver][SQL Server][DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

how to override this problem?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

ajitgadge
Starting Member

12 Posts

Posted - 2009-12-28 : 08:33:21

I think you should use provider string with username and pass. Please see this link http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-28 : 08:34:37

I think you need to login after creating the linked server.
sp_addlinkedsrvlogin
http://technet.microsoft.com/en-us/library/ms189811(SQL.90).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-29 : 00:31:46

1. I Cretaed Linked Server By This way :

EXEC sp_addlinkedserver
@server='LINKED_CAMP', /* local SQL name given to the
linked server */
@srvproduct='', /* not used */
@provider='MSOLAP', /* OLE DB provider */
@datasrc='axean401', /* target server name (machine name) */
@catalog='IIF_INTEGRATION' /* database */
2. now i am writting a stored procedure

CREATE PROCEDURE dbo.usp_GetAssessmentCodes

(
@RouteToCode varchar(6)
)
AS
BEGIN
SELECT * FROM LINKED_CAMP.IIF_INTEGRATION.tROUTE_CODE WHERE RTE_TO_CD = ltrim(rtrim(@RouteToCode))
END

i am getting error :

Could not find table LINKED_CAMP.IIF_INTEGRATION.tROUTE_CODE. Will try to resolve this table name later.

how to ovveride this ?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-29 : 02:06:56

I Got The Solution.

Thanx

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources