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?

Openning an access database using ADO.NET

What is the proper way to open an access database using ADO.NET (that is, using c#) ?
I have the following code, but somehow it does not compile saying that it doesn't find the mdb file called PhoneBook :

OleDbConnection Myconnection = null;
Myconnection = new OleDbConnection(@."Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PhoneBook");
Myconnection.Open();

What am I doing wrong here ?
All help appreciated !:)Stab in the dark:
change C:\PhoneBook to include the file extension i.e. C:\PhoneBook.mdb|||http://www.connectionstrings.comsql

Opening SQLBase database

Is it possible to open, or migrate a SQLBase (5.2) database into Microsoft
SQL Server database, or even an access database etc? Just something more
easy to interface with for me (without a SQLBase server...).
Its only a small database.
Any assistance or ideas would be appreciated
Thanks
Guy
You can upsize Access DB to SQL Server using Access features or DTS from
Access DB added as linked server... I did this to some Access DB and it
works. Don't know what is SQLBase really but if this product have some ODBC
or OLEDB drivers than you might use it as linked server too
"GB" <guy@.dilithiumNO_SPAM.com.au> wrote in message
news:uguexnyNEHA.3960@.TK2MSFTNGP10.phx.gbl...
> Is it possible to open, or migrate a SQLBase (5.2) database into Microsoft
> SQL Server database, or even an access database etc? Just something more
> easy to interface with for me (without a SQLBase server...).
> Its only a small database.
> Any assistance or ideas would be appreciated
sql

Opening SQLBase database

Is it possible to open, or migrate a SQLBase (5.2) database into Microsoft
SQL Server database, or even an access database etc? Just something more
easy to interface with for me (without a SQLBase server...).
Its only a small database.
Any assistance or ideas would be appreciated
Thanks
GuyYou can upsize Access DB to SQL Server using Access features or DTS from
Access DB added as linked server... I did this to some Access DB and it
works. Don't know what is SQLBase really but if this product have some ODBC
or OLEDB drivers than you might use it as linked server too
"GB" <guy@.dilithiumNO_SPAM.com.au> wrote in message
news:uguexnyNEHA.3960@.TK2MSFTNGP10.phx.gbl...
> Is it possible to open, or migrate a SQLBase (5.2) database into Microsoft
> SQL Server database, or even an access database etc? Just something more
> easy to interface with for me (without a SQLBase server...).
> Its only a small database.
> Any assistance or ideas would be appreciated

Opening SQLBase database

Is it possible to open, or migrate a SQLBase (5.2) database into Microsoft
SQL Server database, or even an access database etc? Just something more
easy to interface with for me (without a SQLBase server...).
Its only a small database.
Any assistance or ideas would be appreciated
Thanks
GuyYou can upsize Access DB to SQL Server using Access features or DTS from
Access DB added as linked server... I did this to some Access DB and it
works. Don't know what is SQLBase really but if this product have some ODBC
or OLEDB drivers than you might use it as linked server too
"GB" <guy@.dilithiumNO_SPAM.com.au> wrote in message
news:uguexnyNEHA.3960@.TK2MSFTNGP10.phx.gbl...
> Is it possible to open, or migrate a SQLBase (5.2) database into Microsoft
> SQL Server database, or even an access database etc? Just something more
> easy to interface with for me (without a SQLBase server...).
> Its only a small database.
> Any assistance or ideas would be appreciated

Opening an .mds Database in SQL Server 2000

I am to convert some data and all I've been provided is a database.mds file.
I've been told this is a sql server database. How do I access the
information within.
Any Help is Greatly Appreciated!!
Thanks in Advance!!!I do not recognize this extension, there is no convention in SQL Server for
naming either backup nor
database files this way. Ask the person who sent it to you whether it is a d
atabase file (.mdf) and
of so, read about sp_attach_db and sp_attach_single_file_db. Or it might be
a backup file (.bak) and
if so, read about RESTORE DATABASE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Superfreak3" <Superfreak3@.discussions.microsoft.com> wrote in message
news:BC0E72BE-9E20-41DC-BAC0-6A18B8F2E93B@.microsoft.com...
>I am to convert some data and all I've been provided is a database.mds file
.
> I've been told this is a sql server database. How do I access the
> information within.
> Any Help is Greatly Appreciated!!
> Thanks in Advance!!!

Opening an .mds Database in SQL Server 2000

I am to convert some data and all I've been provided is a database.mds file.
I've been told this is a sql server database. How do I access the
information within.
Any Help is Greatly Appreciated!!
Thanks in Advance!!!I do not recognize this extension, there is no convention in SQL Server for naming either backup nor
database files this way. Ask the person who sent it to you whether it is a database file (.mdf) and
of so, read about sp_attach_db and sp_attach_single_file_db. Or it might be a backup file (.bak) and
if so, read about RESTORE DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Superfreak3" <Superfreak3@.discussions.microsoft.com> wrote in message
news:BC0E72BE-9E20-41DC-BAC0-6A18B8F2E93B@.microsoft.com...
>I am to convert some data and all I've been provided is a database.mds file.
> I've been told this is a sql server database. How do I access the
> information within.
> Any Help is Greatly Appreciated!!
> Thanks in Advance!!!sql

opening ADP combo box causes high CPU utilization on SQL Server; query never completes

Hello all,
I'm using an Access 2000 (9.0.6926 SP-3) ADP as a front-end to a SQL Server
2000 (8.00.760 SP-3) database. SQL Server is running on a dedicated server.
There is a certain query, based on a view, that completes in about two
seconds via Query Analyzer. (It's a straightforward SELECT ... FROM
view_name WHERE ...; I'll spare you the code since the view does end up
hitting many tables, and I'm mostly curious whether this is a well-known bug
I wasn't able to find.) The query returns less than 300 rows.
We're using that query as the Row Source for a combo box in the ADP
front-end. When attempting to expand the combo box, the ADP seems to
freeze. CPU utilization for sqlservr.exe jumps to around 99. Firing up
Profiler beforehand and doing a trace on SQL Server shows a
SQL:BatchStarting and SQL:StmtStarting, with the TextData being the SELECT,
as I'd expect.
Eventually, if I kill the ADP (via task manager), the trace shows a
SQL:BatchCompleted with high numbers for CPU and Reads (unscientifically, it
seems that the longer I let the ADP run, the higher the numbers). There is,
of course, no SQL:StmtCompleted. I'm inclined to believe that, somehow, the
way that Access submits the query is different from Query Analyzer and is
causing SQL Server to go into an infinite loop of some kind.
I'm hoping this is a straightforward, known issue. I'd rather not open a
call with Microsoft; the beauracracy here with providing the up-front fee
(even if it is refunded) would make doing so very painful and difficult. I
can provide more back-end code, the execution plans, trace information,
etc., if anyone takes an interest. Thanks in advance for any help!
Regards,
Brian J. Parker
Systems Coordinator, Childhood Depression Research
Western Psychiatric Institute and Clinic, UPMC Health SystemA little follow-up on this problem:
The view on which the hanging SELECT is based includes a join between two
large tables; they were being joined on a shared derived (text) column
because the view writer didn't want to type out all the underlying integer
columns instead.
I re-wrote the view to use "real" columns and the problem vanishes (which
takes away some of the urgency). My original issue is, I think, still
valid-- a query that takes six seconds in Query Analyzer shouldn't run
indefinitely as the row source of a combo box-- but this may be a clue.
Regards,
Brian|||This would depend on how the data is retrieved for the combo box. Is it a bound control? That could be adding some overhead on the query being sent to SQL Server.|||"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:B676064D-81AB-43FB-AE1C-23EC93FCC069@.microsoft.com...
> This would depend on how the data is retrieved for the combo box. Is it a
bound control? That could be adding some overhead on the query being sent
to SQL Server.
No, it's not a bound control, but thanks for the suggestion.
If you're curious: the control provides a list of research study interviews
meeting certain criteria. Based on the user's selection, we launch a report
that provides some information related to that interview.
Regards,
Brian

opening ADP combo box causes high CPU utilization on SQL Server; query never completes

Hello all,
I'm using an Access 2000 (9.0.6926 SP-3) ADP as a front-end to a SQL Server
2000 (8.00.760 SP-3) database. SQL Server is running on a dedicated server.
There is a certain query, based on a view, that completes in about two
seconds via Query Analyzer. (It's a straightforward SELECT ... FROM
view_name WHERE ...; I'll spare you the code since the view does end up
hitting many tables, and I'm mostly curious whether this is a well-known bug
I wasn't able to find.) The query returns less than 300 rows.
We're using that query as the Row Source for a combo box in the ADP
front-end. When attempting to expand the combo box, the ADP seems to
freeze. CPU utilization for sqlservr.exe jumps to around 99. Firing up
Profiler beforehand and doing a trace on SQL Server shows a
SQL:BatchStarting and SQL:StmtStarting, with the TextData being the SELECT,
as I'd expect.
Eventually, if I kill the ADP (via task manager), the trace shows a
SQL:BatchCompleted with high numbers for CPU and Reads (unscientifically, it
seems that the longer I let the ADP run, the higher the numbers). There is,
of course, no SQL:StmtCompleted. I'm inclined to believe that, somehow, the
way that Access submits the query is different from Query Analyzer and is
causing SQL Server to go into an infinite loop of some kind.
I'm hoping this is a straightforward, known issue. I'd rather not open a
call with Microsoft; the beauracracy here with providing the up-front fee
(even if it is refunded) would make doing so very painful and difficult. I
can provide more back-end code, the execution plans, trace information,
etc., if anyone takes an interest. Thanks in advance for any help!
Regards,
Brian J. Parker
Systems Coordinator, Childhood Depression Research
Western Psychiatric Institute and Clinic, UPMC Health SystemA little follow-up on this problem:
The view on which the hanging SELECT is based includes a join between two
large tables; they were being joined on a shared derived (text) column
because the view writer didn't want to type out all the underlying integer
columns instead.
I re-wrote the view to use "real" columns and the problem vanishes (which
takes away some of the urgency). My original issue is, I think, still
valid-- a query that takes six seconds in Query Analyzer shouldn't run
indefinitely as the row source of a combo box-- but this may be a clue.
Regards,
Brian|||This would depend on how the data is retrieved for the combo box. Is it a b
ound control? That could be adding some overhead on the query being sent to
SQL Server.|||"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:B676064D-81AB-43FB-AE1C-23EC93FCC069@.microsoft.com...
> This would depend on how the data is retrieved for the combo box. Is it a
bound control? That could be adding some overhead on the query being sent
to SQL Server.
No, it's not a bound control, but thanks for the suggestion.
If you're curious: the control provides a list of research study interviews
meeting certain criteria. Based on the user's selection, we launch a report
that provides some information related to that interview.
Regards,
Brian

Opening access via ip

Hi
sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
server using its ip address. What is the procedure to set-up ISA to allow
this?
Thanks
Regards
Hi
"John" wrote:

> Hi
> sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
> server using its ip address. What is the procedure to set-up ISA to allow
> this?
> Thanks
> Regards
>
You have cross posted this into may groups some of which are not ISA
related. Please only post to active relevant groups.
John

Opening access via ip

Hi
sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
server using its ip address. What is the procedure to set-up ISA to allow
this?
Thanks
Regards
Hi
"John" wrote:

> Hi
> sbs 2003 with isa 2000. I need to access an external (to the sbs/isa domain)
> server using its ip address. What is the procedure to set-up ISA to allow
> this?
> Thanks
> Regards
>
You have cross posted this into may groups some of which are not ISA
related. Please only post to active relevant groups.
John

Opening access via ip

Hi
sbs 2003 with ISA 2000. I need to access an external (to the sbs/isa domain)
server using its ip address. What is the procedure to set-up ISA to allow
this?
Thanks
RegardsHi
"John" wrote:

> Hi
> sbs 2003 with ISA 2000. I need to access an external (to the sbs/isa domai
n)
> server using its ip address. What is the procedure to set-up ISA to allow
> this?
> Thanks
> Regards
>
You have cross posted this into may groups some of which are not ISA
related. Please only post to active relevant groups.
Johnsql

OpenDataSource Quandary

I am trying to open and query a Microsoft Access "like" database from SQL Server 2000 using OpenDataSource. This will work for a little while, but if I am to update my ASP.NET code base I have to completely reboot the machine for these queries to work again. I also have two reboot the machine periodically because this functionality decides to crap out. I am doing this from a stored procedure. My code looks like this:

DECLARE @.SelectCode varchar(5000)
SET @.SelectCode =
'SELECT CONVERT(varchar, Value) AS UnitName FROM
OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''Data Source = "' + @.Path + @.DataFile + '.fcd";User ID=Admin;Password=;'')...Properties WHERE Property LIKE ''UnitName'''
EXEC(@.SelectCode)

Now, when I am developing on my local machine that uses Windows Authentication, I have absolutely no problems, unless the source file has a schema error.

Any ideas?

Hi jmace,

I've checked your code and didn't find any problems. One doubt here is that what a FCD file format is?(i've searched through google while didn't get enough valuable information).

It sounds to me that you are accessing this stored procedure from asp.net code, so, my suggestion is, can you try to execute it from your Management Studio directly and see if your problem still can be reproduced? This will help limit this problem. Also, check if it's the database itself's issue. Again, seems your code is fine.

Hope my suggestion helps

sql

OpenDataSource

We are using OPENDATASOURCE to select data from an Access db using the MS Je
t
OLEDB provider. My question is regarding the authorities needed to execute
this command. When I run this command using windows authentication or
integrated security everything seems to work fine—even with users that do
NOT
have symin rights on our SQL Server. However if I try to login using a
SQL Server login, rather than the windows login. I get the error: “Ad hoc
access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You mus
t
access this provider through a linked server.” This occurs even if the sq
l
login has dbo authorities to the database with the Stored proc call to
OPENDATASOURCE.
What authorities must the SQL login possess and why does the Windows login
with very few SQL privileges have access to this method? Is it possible to
run OPENDATASOURCE from a SQL login without symin rights?
We are running Win2K SP4 and the DisallowAdhocAccess key only exists in the
SQLOleDb provider, where it is set to 0.
Our goal is to be able to pull data from a variable Access file and put it
into our SQL db tables and vice versa (import/export), so if you have anothe
r
method we are open to that. It must be able to work with SQL Server and MSD
E
and connect to a variable Access file. Our application is VB6 and uses ADO
to access the SQL Server. We have looked into the linked server method some
,
but since we need to access different files, the OPENDATASOURCE method seeme
d
more applicable.
Thanks for any help.Have a look at MS KB 328569
Adrian
LisaConsult wrote:
> We are using OPENDATASOURCE to select data from an Access db using the MS
Jet
> OLEDB provider. My question is regarding the authorities needed to execut
e
> this command. When I run this command using windows authentication or
> integrated security everything seems to work fine—even with users that d
o NOT
> have symin rights on our SQL Server. However if I try to login using a
> SQL Server login, rather than the windows login. I get the error: “Ad h
oc
> access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You m
ust
> access this provider through a linked server.” This occurs even if the
sql
> login has dbo authorities to the database with the Stored proc call to
> OPENDATASOURCE.
> What authorities must the SQL login possess and why does the Windows login
> with very few SQL privileges have access to this method? Is it possible t
o
> run OPENDATASOURCE from a SQL login without symin rights?
> We are running Win2K SP4 and the DisallowAdhocAccess key only exists in th
e
> SQLOleDb provider, where it is set to 0.
> Our goal is to be able to pull data from a variable Access file and put it
> into our SQL db tables and vice versa (import/export), so if you have anot
her
> method we are open to that. It must be able to work with SQL Server and M
SDE
> and connect to a variable Access file. Our application is VB6 and uses AD
O
> to access the SQL Server. We have looked into the linked server method so
me,
> but since we need to access different files, the OPENDATASOURCE method see
med
> more applicable.
> Thanks for any help.
>|||Hi Adrian
Thanks for the comment, but what does MS KB 328569 stand for? I tried
google, but got no answer :-(
Cheers, Mohamed
Message posted via http://www.webservertalk.com|||microsoft knowledge base
go to http://www.msdn.microsoft.com/ and enter the number below into the sea
rch box on the top right
and click 'go'
Article ID : 328569
Last Review : October 30, 2003
Revision : 1.0
"Mohamed Azzouzi via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:7f3f9ed681a1436f8098611b3eea8b0d@.SQ
webservertalk.com...
Hi Adrian
Thanks for the comment, but what does MS KB 328569 stand for? I tried
google, but got no answer :-(
Cheers, Mohamed
Message posted via http://www.webservertalk.com

Open Sessions using MS Access as Front End

I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?

I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.

Any help would be appreciated.What happens if you kill your Access client or re-boot the client PC? If memory serves, ODBC Manager uses a cached connection and will hold a connection past a close. The idea is that you might want to re-connect very soon and by holding a connection the ODBC Manager can save a bit of time on the second connection.|||Originally posted by Paul Young
What happens if you kill your Access client or re-boot the client PC? If memory serves, ODBC Manager uses a cached connection and will hold a connection past a close. The idea is that you might want to re-connect very soon and by holding a connection the ODBC Manager can save a bit of time on the second connection.

If I close the Access client and reboot the machine the session will disappear, but there are times the session won't dissappear and that session becomes an orphan/ghost session . This ghost session then keeps a lock onto the table.

open ports

what ports do I need to open in my router so that I can access my SQL server
via IP addr?
Thanks,
Raul RegoRaul,
TCP port 1433 should do it if I'm not mistaken, unless you changed the
default port.
--
HTH
Karl Gram
"Raul" <jjkgr@.hotmail.com> wrote in message
news:2Goje.13569$KQ6.13453@.trndny02...
> what ports do I need to open in my router so that I can access my SQL
> server via IP addr?
> Thanks,
> Raul Rego
>

open ports

what ports do I need to open in my router so that I can access my SQL server
via IP addr?
Thanks,
Raul Rego
Raul,
TCP port 1433 should do it if I'm not mistaken, unless you changed the
default port.
HTH
Karl Gram
"Raul" <jjkgr@.hotmail.com> wrote in message
news:2Goje.13569$KQ6.13453@.trndny02...
> what ports do I need to open in my router so that I can access my SQL
> server via IP addr?
> Thanks,
> Raul Rego
>

open ports

what ports do I need to open in my router so that I can access my SQL server
via IP addr?
Thanks,
Raul RegoRaul,
TCP port 1433 should do it if I'm not mistaken, unless you changed the
default port.
HTH
Karl Gram
"Raul" <jjkgr@.hotmail.com> wrote in message
news:2Goje.13569$KQ6.13453@.trndny02...
> what ports do I need to open in my router so that I can access my SQL
> server via IP addr?
> Thanks,
> Raul Rego
>

open MDF file without MSDE

Hello,
I'm trying to access .mdf file (a sql server database) without having
SQL Server installed on the machine (neither MSDE).
It can be done from any programming language (like Java or C#)?
Is there anyone who knows how can I do it?
Or does a native library that can access the .mdf file exist?

Thank to anyone who can help me...

Pietro.Pietro wrote:
> Hello,
> I'm trying to access .mdf file (a sql server database) without having
> SQL Server installed on the machine (neither MSDE).
> It can be done from any programming language (like Java or C#)?
> Is there anyone who knows how can I do it?
> Or does a native library that can access the .mdf file exist?
> Thank to anyone who can help me...
> Pietro.

You can read it as a binary file of course but the only supported
method to get at SQL Server's logical data and metadata structures
(tables etc) is to use the SQL Server engine. Microsoft doesn't publish
detailed specs for the file format.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

open LDAP via MSSQLServer

Hi!
How can i access an open ldap server via MSSQLServer2000)?
Is there an oledb provider to connect to open ldap via linked server (for example)?
Thanks!
greets
chrisChris-

I believe the answer is NO. I did some research a while ago about the same. Just in case someone tells you otherwise, I will be interested too...

- CB

Originally posted by come_get_some
Hi!

How can i access an open ldap server via MSSQLServer2000)?

Is there an oledb provider to connect to open ldap via linked server (for example)?

Thanks!

greets

chris|||Hi!

Thanks for your answer.

I have to get open ldap data into SQLServer.......
I need to think about how to do it...

greetz

chris