How to connect to AS 2000

Hi all,

Currently I am working on the Window 2003 Enterprise 64-bit with SQL 2005 Enterprise and Visual Studio Professional install. I try to write a console application using Visual Studio to connect to the Analysis Server 2000, but I get the follow error message:

Errorystem.Runtime.InteropServices.COMException (0x800A0E7A): Provider cannot be found. It may not be properly installed.
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
at ExceptionCheck.Module1.Hodb03_ItemSales() in Z:\Visual Studio\Console\ExceptionCheck\ExceptionCheck\Module1.vb:line 16"

I know if I want to connect to the Analysis Service 2000, I need to install the Microsoft SQL Server 2000 PivotTable Services. After I install the services, I still get the error message.

Thanks,

Tomas

Hi,

Try to re-register msolap (regsvr32 msolap80.dll from "%ProgramFiles%\Common Files\System\Ole DB"). If you get the same error, let's check with the MDX Sample application (ships with AS2000, you can copy it from another machine). If you get the same error, then you probably have corrupted installation of the Pivot Table Service.

|||

Hi,

I tried to re-register msolap, but it didn't help.

|||The MDX Sample application failed with the same error ? Beside re-installing PTS, can't think of something else.

How to connect MS AS 2005 with AMO in vb.net

Hi All,

I am working on MSAS 2005 and am new to this one but i worked on AS 2000(DSO).I just want to connect to the MS AS 2005 Server from vb.net by using AMO(Analysis Management Object).

It is really very urgent.Help needed.

Regards

vishu

Hi,

MSDN has some great examples over here Those should get you started..

C.
|||

I tried with that.But i am getting below error.

connection cannot be made.Ensure that the server is running.

What could be the problem i could not understand.I checked in my services all the services are running and i able to connect from MS Management Studio to Analysis services.I am giving my Machine Name(ServerName)/SQL2005.

But when i am trying to connect from VB.Net am getting error.

Please help on this.

Regards

vishu

|||It may help to paste your code.

C
|||

Hi

I am Placing that part of code.Please check it and let me know.

using System.AnalysisServices

//for strStringConnection am passing Data Source=ServerName

static Server ServerConnect( String strStringConnection)

{

string methodCaption = "ServerConnect method";

Server svr = new Server();

try

{

svr.Connect(strStringConnection);

}

#region ErrorHandling

catch (AmoException e)

{

MessageBox.Show( "AMO exception " + e.ToString());

svr = null;

}

catch (Exception e)

{

MessageBox.Show("General exception " + e.ToString());

svr = null;

}

#endregion

return svr;

}

Is there any wrong with the code. Please advise the same.

Regards,

vishu

|||Looks right to me, except i tag 'Provider=msolap' on the end of my connection string, like:

'Data Source=ServerName;Provider=msolap'

But i tried it without the provider, and it worked also. So, i'm not sure i can be of any help.

C
|||

Thanks.It is working now.

I need some info like can we connect Ms As 2000 and Ms As 2005 from any one like either with DSO or AMO.

it this possible or else we have to mainatin separate things for above servers.

help me on this.

|||

No you cannot connect to both AS 2000 and 2005 using one API

AMO = AS 2005

DSO = AS 2000

If your app needs to manage both you will need to use both API's. You do not need the "provider=msolap" in the connect method, you should only need to pass in the server name eg. svr.Connect("localhost")

|||

Hi

Thanks,I am sending my current environment where i am working OS:win 2003 server and MS AS 2005 and .net 2.0.In my previous environment win 2000 and MS As 2000 by using DSO and ADOMD i worked there is no issues in that.My client wants both has to work.becaz they are migrating servers one by one.coming to the code In this i had written some Webmethods through which am managing Ms As 2000 with DSO(connecting to MSAS and managing).So now they are going to change the servers in to MS As 2005 and they need the same kind of functionality should work from win 2003 and MS As 2005 & .net 2.0.For MDX purpose we used ADOMD.

This is some wht migration work.

Is there any chances to communicate and maintain the both the calls and how help needed.

|||You will have to develop 2 branches of code. Keep the existing one that works with AS2000 and develop a new one for AS2005. You can use code similar to that which I suggested here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1523691&SiteID=1 to detect which version you are talking to.|||

Thanks Darren.

It is helped me to go further.

|||

Hi Darren Gosbell

I am working on some migration project.it is from AS 2000 to AS 2005.

I have to create a role in AS 2005.Is this one is similer functionality like in AS 2000.

Plz let me know.

I am using AMO for making connection i can able to connect and now i have to create a role,In my previous development we used to create a role in database then after in cube with the help of DSO.Could you please help on this.

if possible if you have any sample to create a role in AS 2005 with AMO please send to me.

Thanks in Advance.

vishu

sql

how to connect from asp

How do I connect to sql server express from asp.

to sql server I would do as follows but this is not working with sql server express

<%
Dim objconn ,mserver,dbName,UserID_db, PassWD
Dim strCon
Set objconn=Server.CreateObject("ADODB.Connection")
mserver="iphere"
dbName="dbname"
UserID_db="login"
PassWD="password"
strCon="Provider=SQLOLEDB.1;Data Source=" & mServer & ";Initial Catalog=" & _
dbName & ";User ID=" & UserID_db & ";Password=" & PassWD & ";"
objconn.Open strCon
response.write "opened"

%

Did you enabled remote connections for SQl Server Express (this is disabled by default). See the screeencast on my site for more information. SQL Server grabs a dynamically assigned port at startup, so you either specify the port after the servername --> Servername,Portnumber or start SQL Server browser on the server which will redirect the request to the appropiate port.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Also check if NET DDE and NET DDE DSDM services are started under Administrative Tools->Services

and Firewall rules are set properly to allow connection.

Hemantgiri S. Goswami

how to configure multiple databases for one asp.net website

Hello:
I am working on a website that will require the use of multiple
databases - a unique database for each user in fact. It is somewhat of
a light CRM application and it is important to keep a user's customer
information in their own database.
So I am hoping to get input on the best way to implement this. The
approach I am using so far includes storing the database connection
string in a property of the user's profile object. However, this
requires referencing the profile object each time I hit the database.
And for pages that are using the sqldatasource control, that means
assigning the connectionstring property for that control in the
page_load event outside of an "if not page.ispostback" block. So I
wonder if there isn't a better way.
Any feedback would be really appreciated here.
ChrisHow about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||How about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||Aaron:
Thanks for your reply. I actually left out some stuff in my post to
keep the thread somewhat open-ended. Your suggestion actually looks
very close to what I ended up implementing. I am storing a database
connection string in a profile object, then loading that into a session
variable when the user logs in. It sounds like I'm on the right track
so that's reassuring. Thanks for the peace of mind!
Chris

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.

how to check whether the sql server is up

hi,
i'm working on a windows service in vb.net which will be started
automatically. but sometimes, on startup, it will show this error --
ERROR [08001] [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does not exist or access denied.
I think this is because, my service uses an sql server connection, but
sql server starts only after my service.
plz help me out to check whether the sql server is up in vb.net code so
that if it is not started, v can wait for it to start.
thanx
vipinPing to the server.
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
<vipinhari@.gmail.com> wrote in message
news:1142402449.177773.136960@.j52g2000cwj.googlegroups.com...
> hi,
> i'm working on a windows service in vb.net which will be started
> automatically. but sometimes, on startup, it will show this error --
> ERROR [08001] [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
> Server does not exist or access denied.
> I think this is because, my service uses an sql server connection, but
> sql server starts only after my service.
> plz help me out to check whether the sql server is up in vb.net code so
> that if it is not started, v can wait for it to start.
> thanx
> vipin
>|||Uri,
That's fine but your snippet only check whether such workstation/server is
up, nothing about sql server/agent services.
--
current location: alicante (es)
"Uri Dimant" wrote:

> Ping to the server.
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
> <vipinhari@.gmail.com> wrote in message
> news:1142402449.177773.136960@.j52g2000cwj.googlegroups.com...
>
>|||Hi,
if you don=B4t wanna use SMO to check this, simply build a snippet of
code connection to the server and encapsulate this with a try catch
block.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Using the .Status property of the SQLDMO.SQLServer object, you can test the
status of a SQL Server. Also of interest are the .Start, .Pause, .Continue,
and .Stop method calls.
http://www.devx.com/vb2themax/Tip/18459
Discuss with the DBA or network admin why the server would occasionally not
be running when needed.
<vipinhari@.gmail.com> wrote in message
news:1142402449.177773.136960@.j52g2000cwj.googlegroups.com...
> hi,
> i'm working on a windows service in vb.net which will be started
> automatically. but sometimes, on startup, it will show this error --
> ERROR [08001] [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
> Server does not exist or access denied.
> I think this is because, my service uses an sql server connection, but
> sql server starts only after my service.
> plz help me out to check whether the sql server is up in vb.net code so
> that if it is not started, v can wait for it to start.
> thanx
> vipin
>