openquery - sp_addlinkedserver problem

Hello, i'm trying to figure out how to solve my challenge.

my goal is to access the analysis service to execute an mdx-query.

the resultset is placed in a temp-table in my 'normal' database.

when i login to my db as integrated user (Administrator in this case) everything works tiptop.

but since we cannot use (for now) i have to use a sql-login.

when i login as that, i have trouble getting the resultset.

atm my code looks like this:

USE master

GO

/* Add new linked server */

EXEC sp_addlinkedserver

@.server='SASDEL003_SSAS', -- local SQL name given to the linked server

@.srvproduct='', -- not used

@.provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)

@.datasrc='SASDEL003', -- analysis server name (machine name)

@.catalog='TNT Grip AnalysisServices' -- default catalog/database

GO

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'SASDEL003_SSAS'

, @.useself = false

, @.locallogin = 'domain\foo'

--, @.rmtuser = 'foo'

--, @.rmtpassword = 'bar'

GO

SELECT *

FROM OPENQUERY(SASDEL003_SSAS,'SELECT some highly interesting query')

GO

/* Remove any previous references to the linked server */

EXEC sp_droplinkedsrvlogin @.rmtsrvname= 'SASDEL003_SSAS' , @.locallogin= 'SASDEL003\foo'

GO

EXEC sp_dropserver 'SASDEL003_SSAS'

GO

but i get the error:

OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS" returned message "An error was encountered in the transport layer.".

OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS" returned message "The peer prematurely closed the connection.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "SASDEL003_SSAS".what i'm seeing in the profiler i'm logging in the ssas-database with "anonymous user"

i don't understand that, because i use linked server with sp_addlinkedsrvlogin

should i allow anonymous login then?

OpenQuery

OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
Ths will also work
exec server1.bb01_db.dbo.sp_executesql N'select * from bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001'' and
tran_dt =''2002-12-02'''
If those are datetimes then you should use yyyymmdd especially with remote
server calls just in case the dateformats are different.
"CurtM" wrote:

> OPENQUERY will solve your problem. Examples:
> --This will fail:
> select * from server1.bb01_db.dbo.bb01 (nolock)
> where process_dt = '2002-12-02' and mid = '03301001' and
> tran_dt = '2002-12-02'
> --This will work if you have your linked servers set up --
> correctly:
> select * from openquery(server1, 'select * from
> bb01_db.dbo.bb01 (nolock)
> where process_dt = ''2002-12-02'' and mid = ''03301001''
> and tran_dt = ''2002-12-02''')
>

OpenQuery

OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')Ths will also work
exec server1.bb01_db.dbo.sp_executesql N'select * from bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001'' and
tran_dt =''2002-12-02'''
If those are datetimes then you should use yyyymmdd especially with remote
server calls just in case the dateformats are different.
"CurtM" wrote:
> OPENQUERY will solve your problem. Examples:
> --This will fail:
> select * from server1.bb01_db.dbo.bb01 (nolock)
> where process_dt = '2002-12-02' and mid = '03301001' and
> tran_dt = '2002-12-02'
> --This will work if you have your linked servers set up --
> correctly:
> select * from openquery(server1, 'select * from
> bb01_db.dbo.bb01 (nolock)
> where process_dt = ''2002-12-02'' and mid = ''03301001''
> and tran_dt = ''2002-12-02''')
>sql