OLAP connection problems

I have two systems setup with SQL Server 2000 + Analysis Services. Both servers have been patched with both SP3a patches.

I wrote a trivial command line app that tests connection strings through ADO.

On each system, I can connect locally perfectly using the connection string:
"Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

However, once I change "Data Source" to refer to the other system, the call to ADODB.Connection.Open throws a "System.Runtime.InteropServices.COMException" with message: "Unspecified error". (The simple app is written in C# and talks to ADO through Interop)

I've tried referencing ADO 2.1, ADO 2.5 and ADO 2.7
I've tried a "Provider" of "MSOLAP.2" as well as just "MSOLAP"
I've also tried a variety of different connection strings with many different permissions settings.

It is very frustrating to get an absolutely generic error message.

Any ideas?

Thanks in advance!Have you tried adding "User ID=xxx;Password=xxx"

or

"Integrated Security=SSPI"|||Yes, just retried all of them and they all result in the same System.Runtime.InteropServices.COMException, "Unspecified error"

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"|||Let me reclarify. This one works fine:

adomdtest "Provider=MSOLAP;Data Source=localhost;Initial Catalog=FoodMart 2000"

These (that reference another system) fail with System.Runtime.InteropServices.COMException, "Unspecified error":

adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;Integrated Security=SSPI;Initial Catalog=FoodMart 2000"
adomdtest "Provider=MSOLAP;Data Source=192.168.128.53;User Id=sa;Password=xxxx;Initial Catalog=FoodMart 2000"

I experience the same effect from two systems; either system can open its own database but can't open a remote one.|||Can't think of anything here...Maybe these links can give you some ideas?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_8wfm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/agtroubleshoot_64fb.asp

Ok.. Step 2 of my clustering

I managed to successfully install the Windows clustering service on Node 1
of my active\passive setup environement on win2003.
The only thing i had done on Node 2 was install the OS and did not configure
the shared disks on the shared array...
I now powered Node 2 and wanted to go to disk management to configure those
shared disks. What am I supposed to see ? Would they be inaccessible because
Node 1 has the cluster service installed and owns this disk resource...or
should I be able to access them and configure them the same way as Node 1
with the same drive letters,etc.. and then join that node to the cluster..
Thanks
If MSCS is installed and the cluster service is running on Node 1, the disks
should be inaccessible on Node 2.
Regards,
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3MBpg4EEHA.1240@.TK2MSFTNGP10.phx.gbl...
> I managed to successfully install the Windows clustering service on Node 1
> of my active\passive setup environement on win2003.
> The only thing i had done on Node 2 was install the OS and did not
configure
> the shared disks on the shared array...
> I now powered Node 2 and wanted to go to disk management to configure
those
> shared disks. What am I supposed to see ? Would they be inaccessible
because
> Node 1 has the cluster service installed and owns this disk resource...or
> should I be able to access them and configure them the same way as Node 1
> with the same drive letters,etc.. and then join that node to the cluster..
> Thanks
>

Odd SQLTrigger behavior

I have three tables I am using, aspnet_Users, Stories,CustomizedStory. Stories andCustomizedStory are related via a foreign key StoryID. I've setup the tables so that when I delete aStory row it cascade deletes the corresponding row from CustomizedStory. Each CustomizedStory row has a reference to UserID from aspnet_Users.Since, I didn't want to mess with the table definition by adding a cascadedelete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:

ALTERTRIGGER[dbo].[DeleteCustomizedStories]

ON[dbo].[aspnet_Users]
FOR DELETE
AS
BEGIN
DELETE FROMdbo.Story
WHEREStoryID=
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserID =
(SELECTUserIDFROMdeleted))
END

The problem I am having is that it deletes all of theCustomizedStory rows as specified by the cascading option, but doesn't deletethe Story rows. I can't seem to understandwhy this is happening, especially when Iexplicitly told it to delete story rows.

The behavior is a bit weird, I must admit. Your DELETE-query is wrong though, which may cause problems.

DELETE FROMdbo.Story
WHEREStoryIDIN
(SELECTStoryIDFROMdbo.CustomizedStoryWHEREUserIDIN
(SELECTUserIDFROMdeleted))

or

DELETE s FROMdbo.Storys
INNER JOINdbo.CustomizedStory c ON s.StoryID=c.StoryID
INNER JOINdeleted d ON c.UserID=d.UserID

Odd Rights Issue

SQL Server 2000 Server at SP3a
WinXP SQL Server 2000 Client at SP4
Win2K SQL Server 2000 Client at SP3a
Identical local user account setup on both clients using peer-to-peer
windows authentication to server.
The account on Win2K client at SP3a can successfully open pre-existing
transformation tasks in DTS.
The account on WinXP client at SP4 cannot open pre-existing transforamtion
tasks in DTS (but can create new ones okay).
What gives? Thanks for any help anyone can offer!
ChuckI forgot to include the actual error message in WinXP client:
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : [DNETLIB][ConnectionOpen (Connect()).]SQL Server
does
not exist or access denied.
WinXP client has no difficulty registering the server and doing other
functions. Since exact same account works on another client, leads me to
believe something about either XP or SP4 that's causing the problem. Can
SP4 cleint connect to SP3a server?
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:eZItbHLrFHA.2896@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000 Server at SP3a
> WinXP SQL Server 2000 Client at SP4
> Win2K SQL Server 2000 Client at SP3a
> Identical local user account setup on both clients using peer-to-peer
> windows authentication to server.
> The account on Win2K client at SP3a can successfully open pre-existing
> transformation tasks in DTS.
> The account on WinXP client at SP4 cannot open pre-existing transforamtion
> tasks in DTS (but can create new ones okay).
> What gives? Thanks for any help anyone can offer!
> Chuck
>|||I figured it out -- the problem client was missing a cleint network alias
for the server. Problem had nothing to do with XP or SP4.
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:OxYvzKLrFHA.716@.TK2MSFTNGP10.phx.gbl...
> I forgot to include the actual error message in WinXP client:
> Error Source : Microsoft OLE DB Provider for SQL Server
> Error Description : [DNETLIB][ConnectionOpen (Connect()).]SQL Serv
er does
> not exist or access denied.
> WinXP client has no difficulty registering the server and doing other
> functions. Since exact same account works on another client, leads me to
> believe something about either XP or SP4 that's causing the problem. Can
> SP4 cleint connect to SP3a server?
>
> "CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
> news:eZItbHLrFHA.2896@.TK2MSFTNGP10.phx.gbl...
transforamtion[vbcol=seagreen]
>

Odd INSERT INTO issue with replication setup

I've got a db setup for replication. Replication works fine in both
directions. However ...
On the SQL2k backend, I can do an INSERT INTO to a particular table
(Results) with no problem. When I try to do the same INSERT INTO on the
Results table with SQLCE, I get the error message, "A duplicate value cannot
be inserted into a unique index". Note that I can do an UPDATE on the
Results table with no problem.
I do have one guidcol (uniqueidentifier, Non-null) on the SQL Server table.
To simplify matters, I have NO indexes (except the PK) and a single INT
primary key.
I've noticed in the Query Analyzer (SQLCE), that after a replication sync,
there are now 3 index (?) columns on the table, 2 of which are not in the
main SQLServer table.
s_Generation (int)
s_RowLineage (varbinary)
rowguid (uniqueidentifier)
Is this INSERT INTO failure somehow related to the extra columns added or is
this a rowguid problem?
Earl,
does your INSERT INTO specify the guid's value?
Rgds,
Paul Ibison
|||No it does not. My understanding is that this is a read-only column?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVEX24TVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Earl,
> does your INSERT INTO specify the guid's value?
> Rgds,
> Paul Ibison
>
|||If there's only one index on the PK and you're not entering a duplicate
record, then the problem must be with the system tables. I notice that you
have posted this same question some time ago without it being solved. Have
you reinitialized in the meantime? Does the error occur after
reinitialization? How about if you remove replication and set it up - is it
reproducible?
Rgds,
Paul Ibison
|||Yes, I actually removed replication from SQL2k, made sure everything was
cleaned out of the database and set it all back up (I drop the database
during the replication process on the subscriber/SQLCE side). I'm really
scratching my head on this one, because everything "looks" correct. I posted
the code for the INSERT INTO logic over in the
microsoft.public.dotnet.framework.adonet forum, just in case I missed
something on that side of it. Apparently it is accurate, as I've had no
responses to a request for input.
What is left? Does the SQLCE database not actually generate a guid for that
column?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eRws5BYVFHA.628@.TK2MSFTNGP09.phx.gbl...
> If there's only one index on the PK and you're not entering a duplicate
> record, then the problem must be with the system tables. I notice that you
> have posted this same question some time ago without it being solved. Have
> you reinitialized in the meantime? Does the error occur after
> reinitialization? How about if you remove replication and set it up - is
> it reproducible?
> Rgds,
> Paul Ibison
>

ODBC Setup

I have downloaded MS SQL Server for Desktop to upsize my
access database to SQL Server database.
The upsizing is successful and when I setup the DSN
connection. It gives the following error :
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user 'TIMESWEB\IUSR_TIMESWEB'.
I remember the password. But the username is not
recognised by the SQL Server. Can you please let me know
how I can change the Username & password for MS SQL Server
after installing it. I do not know from where I can
change the username & password.
Kindly assist.
Thanks,
Rajesh.VMSDE does not come with the SQL administrator tools (like Enterprise Manager
or Query Analyzer). In order to manage the database, you need to use T-SQL
commands.
The T-SQL commands that should help you are:
If you want to add a SQL login use the following stored procedures:
sp_password:
http://msdn.microsoft.com/library/d.../>
pz_5x44.asp
sp_addlogin:
http://msdn.microsoft.com/library/d... />
a_0q7i.asp
If you want to use an existing Windows username (ie.
'TIMESWEB\IUSR_TIMESWEB'), then use the following stored procedure to give
the username access to the database:
sp_grantlogin:
http://msdn.microsoft.com/library/d... />
a_0q7i.asp
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Rajesh" <timesweb@.omantel.net.om> wrote in message
news:01a401c3c3a9$87064410$a001280a@.phx.gbl...

quote:

---

> I have downloaded MS SQL Server for Desktop to upsize my
> access database to SQL Server database.
> The upsizing is successful and when I setup the DSN
> connection. It gives the following error :
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user 'TIMESWEB\IUSR_TIMESWEB'.
> I remember the password. But the username is not
> recognised by the SQL Server. Can you please let me know
> how I can change the Username & password for MS SQL Server
> after installing it. I do not know from where I can
> change the username & password.
> Kindly assist.
> Thanks,
> Rajesh.V

---

ODBC failing to connect

Ok here is the problem. I am trying to set up logging on SQL 2005. Now I
am trying to setup the OBDC on the ISA server and it is failing each time.
It cannot reach the server. Every other computer in the domain can access
the SQL server via OBDC. So what am I missing to do that on the ISA server.
I am running ISA 2006 Enterprise, and have set the system policy to use SQL
for logging. Plus I have a access rule that allows SQL through ISA. Anyone
know what I missed?
TCP/IP is set up as dynamic, and I can ping the SQL Server.
The following is the failure I get from ODBC:
Connection failed:
SQLState: '01000'
SQL Server Error: 10065
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist
or access denied.
Thanks,
Michael R. Mastro II
It can be a lot of different things. Enable login auditing
or run profiler to see if it's actually connecting on the
SQL Server end. It just makes troubleshooting easier.
If it's a named instance, make sure you are specifying the
instance name.
You'd also want to check your ports and protocols - one way
to check is look in the SQL Server error log from when it
started up to determine what ports and protocols SQL Server
is listening on.
From the ISA server, try to telnet to the port SQL Server is
listening on. Make sure the client end (ISA) is using the
same port.
Make sure SQL Server is listening on the correct protocols
as well. Make sure you have TCP/IP enabled.
And then run through the following article:
Potential causes of the "SQL Server does not exist or access
denied" error message
http://support.microsoft.com/?id=328306
-Sue
On Tue, 23 Jan 2007 10:03:10 -0500, "Michael Mastro"
<MikeRM2XXX@.worldnet.att.net> wrote:

>Ok here is the problem. I am trying to set up logging on SQL 2005. Now I
>am trying to setup the OBDC on the ISA server and it is failing each time.
>It cannot reach the server. Every other computer in the domain can access
>the SQL server via OBDC. So what am I missing to do that on the ISA server.
>I am running ISA 2006 Enterprise, and have set the system policy to use SQL
>for logging. Plus I have a access rule that allows SQL through ISA. Anyone
>know what I missed?
>TCP/IP is set up as dynamic, and I can ping the SQL Server.
>The following is the failure I get from ODBC:
>Connection failed:
>SQLState: '01000'
>SQL Server Error: 10065
>[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
>(Connect()).
>Connection Failed:
>SQLState: '08001'
>SQL Server Error: 17
>[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist
>or access denied.
>
>Thanks,
>Michael R. Mastro II
>
|||I tried the following with still no luck.
First off I went into the SQL Server logs and found out that it is listening
in on TCP port 2440, it also mentioned 2441.
I then went to the ISA server and created protocol that allows inbound and
outbound connections on 1433, 2440, and 2441. I created an access rule that
allows any of the protected networks and the local host to access the new
protocol I created with the SQL server.
I went back to the SQL server machine, and started up the profiler to run a
trace.
Back to the ISA machine, and opened up telnet and tried to connect to SQL
server machine via 1433, then 2440, then 2441. Each time connect failed. I
also tried a static IP on the ODBC set for 2440. And each time as the same
before.
I looked over the SQL Profiler trace and it does not even show any
connection attempts from the ISA machine.
Pinging and PathPinging show good connections. Plus a looking over the
ports on the SQL server machine show that it is listening on those ports.
Still scratching my head.
Michael R. Mastro II
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:iejdr215njd7fp0taafif3lcsdb508i420@.4ax.com...
> It can be a lot of different things. Enable login auditing
> or run profiler to see if it's actually connecting on the
> SQL Server end. It just makes troubleshooting easier.
> If it's a named instance, make sure you are specifying the
> instance name.
> You'd also want to check your ports and protocols - one way
> to check is look in the SQL Server error log from when it
> started up to determine what ports and protocols SQL Server
> is listening on.
> From the ISA server, try to telnet to the port SQL Server is
> listening on. Make sure the client end (ISA) is using the
> same port.
> Make sure SQL Server is listening on the correct protocols
> as well. Make sure you have TCP/IP enabled.
> And then run through the following article:
> Potential causes of the "SQL Server does not exist or access
> denied" error message
> http://support.microsoft.com/?id=328306
>
> -Sue
> On Tue, 23 Jan 2007 10:03:10 -0500, "Michael Mastro"
> <MikeRM2XXX@.worldnet.att.net> wrote:
>
|||Well at least it's narrowed down. Check the logs on your ISA
server. It looks like it's still being blocked there.
-Sue
On Thu, 25 Jan 2007 10:35:04 -0500, "Michael Mastro"
<MikeRM2XXX@.worldnet.att.net> wrote:

>I tried the following with still no luck.
>First off I went into the SQL Server logs and found out that it is listening
>in on TCP port 2440, it also mentioned 2441.
>I then went to the ISA server and created protocol that allows inbound and
>outbound connections on 1433, 2440, and 2441. I created an access rule that
>allows any of the protected networks and the local host to access the new
>protocol I created with the SQL server.
>I went back to the SQL server machine, and started up the profiler to run a
>trace.
>Back to the ISA machine, and opened up telnet and tried to connect to SQL
>server machine via 1433, then 2440, then 2441. Each time connect failed. I
>also tried a static IP on the ODBC set for 2440. And each time as the same
>before.
>I looked over the SQL Profiler trace and it does not even show any
>connection attempts from the ISA machine.
>Pinging and PathPinging show good connections. Plus a looking over the
>ports on the SQL server machine show that it is listening on those ports.
>Still scratching my head.
>Michael R. Mastro II
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:iejdr215njd7fp0taafif3lcsdb508i420@.4ax.com.. .
>
|||Well looking at the Firewall logs I can see the it is opening the port then
2 seconds later terminating with a 0x80074E21. Still unable to figure a way
around this one.
Michael R. Mastro II
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0e3jr2dffad96n056htlmnpkrubqdvr49s@.4ax.com...
> Well at least it's narrowed down. Check the logs on your ISA
> server. It looks like it's still being blocked there.
> -Sue
> On Thu, 25 Jan 2007 10:35:04 -0500, "Michael Mastro"
> <MikeRM2XXX@.worldnet.att.net> wrote:
>