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?

Opening table in SQL Server 2005?

This ought to be easy. But I can't figure out how to do it.
Using Enterprise Manager in SQL Server 2000, I point to a table, select it,
right click and choose Open Table-> Return all rows. Now I have the table
open and I can edit to my heart's content.
How can I do the same thing in SQL Server 2005?
Hi
With Beta 2 of SQL Server 2005, that functionality is not included. It may
return in Beta 3.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David" <David@.blackdeck.com> wrote in message
news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> This ought to be easy. But I can't figure out how to do it.
> Using Enterprise Manager in SQL Server 2000, I point to a table, select
it,
> right click and choose Open Table-> Return all rows. Now I have the table
> open and I can edit to my heart's content.
> How can I do the same thing in SQL Server 2005?
|||You got to be kidding! I use that all the time! Will it definitely be in
the final release?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> With Beta 2 of SQL Server 2005, that functionality is not included. It may
> return in Beta 3.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "David" <David@.blackdeck.com> wrote in message
> news:39F0E432-BC98-476D-8A8C-B4AAF8E76ED8@.microsoft.com...
> it,
>
>
sql

Opening SqlConnection - VB.NET

I used to use rdo in VB6 and now I'm trying to figure out how to use the SqlDataReader class in VB.NET. I want to use an ODBC data source to specify the connection info. I used to give the rdoConnection object a connect string that looked something like this:
"DSN=[data source name];UID=[sql server user];PWD=[pwd]"
I don't understand the connect string given in all the examples I've found (nor does it seem to work on my system...)

mySqlConnection =New SqlConnection("server=(local)\VSdotNET;Trusted_Connection=yes;database=northwind")
Does anyone have any ideas? I'm open to explanations as well as solutions :)
Thanks,
jdm

Already tried to look at: http://www.connectionstrings.com/ ?|||jsmiller, are you receiving an error message?
Can you shell out to DOS (EDIT: i.e. acommand prompt, sheesh my age is showing), and run this command:
osql -L
This will tell you the name of the SQL Servers to which you haveaccess. Whatever is returned is what should go into the server=part of the connection string. If you are still having trouble,please paste your connection string and error message and we will tryto help you through it.
|||

Success! The thing is that I don't want to specify a server name but rather a System DSN... So I think based on a little more research (thanks connectionstrings.com), I don't want to use a sqlConnection but an odbcConnection. Here is my latest attempt:

Dim myOdbcConnectionAs OdbcConnection

Dim myOdbcCommandAs OdbcCommand

Dim myOdbcDataReaderAs OdbcDataReader

myOdbcConnection =New OdbcConnection("DSN=DSN;User Id=sa;Password=PWD")

myodbccommand =New OdbcCommand(SQLString, myOdbcConnection)

myOdbcConnection.Open()

myOdbcDataReader = myOdbcCommand.ExecuteReader(CommandBehavior.CloseConnection)
The program breaks on the myOdbcConnection.Open line, with an error
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
So if I add "Trusted_Connection=true" to the connect string everything seems to work out. At this point, my question is what does the trusted connection variable really mean? Am I making a security mistake?
I'm new to this so any comments welcome.
Thanks!
jdm

Only getting one record in preview mode

I've created a new simple report and I am trying to figure out why I am only getting one page of the report when I am expecting a page per db record.

1. I have a simple query that returns 7 rows of data, 1 column 'name'. They query test good in the Data tab.

2. I have dragged and drop that column to my page on the Design tab

3. I then go to the Preview tab and I only get one page with the name on it.

What am I doing wrong - I expected one page per record from my query.

Thanks,

Patrick

Obvious question first - have you checked your group by on your report (NOT data tab)?|||Where would I find this?|||Assuming you are displaying your data in a table, then just select the table. Right click on the selected table, and check out the 'Groups' tab...