one configuration file across multiple packages.

Hi,

At just the point at which I was going to write some verbose schpeel, I found this, which really does it all for me:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1163727&SiteID=1&mode=1

Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone?

Just edit the Config file to add manually the second configuration. Notice that by doing so you may receive warnings when executing the package as some configurations in the file do not exist in the package using the file. My advice is to use separate configuration files.

|||

'...My advice is to use separate configuration files...'

In the circumstances Rafael, I think this is the best advice. I must say I find it disappointing that one can only share config files in this half-baked way. It would be much better to have one config file shared across 2 packages for say connection managers A,B,C,D,E, where package 1 used connection managers A,B,C, and package 2 used C,D,E.

Maybe that's something for Microsoft to think about...

Thanks for your input Rafael,

Tamim.

|||

BTW,

SQL Server table based configurations behaves more on the way you want. You can have a single table with a row per each configuration value...no warnings if only some are used within the package.

OLTP access

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bobhave you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.
"Bob" wrote:

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
> actually processing the cube, then it errors processing the dimension.
> If I move the OLTP database (SQL Server 2000) onto the machine running the
> OLAP it works fine.
> It seems to be a rights issue but the ODBC tests work fine and the cube
> design has no trouble viewing the OLTP schema.
> In its 'normal' position the OLTP is on a Win2k Server that is part of a
> work group.
> The OLAP is on a Win2k Domain Controller.
> Any clues on how to establish a successful processing of the OLTP in its
> normal position would be appreciated.
> Thanks
> Bob
>
>|||Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.
This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.
There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?
I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.
I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob
"bc" <bc@.discussions.microsoft.com> wrote in message
news:D6258097-009A-492A-BA75-2AC40A664968@.microsoft.com...
> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
> When you are in the design in AS, you are authenticated using your login
to
> the sql server source so it is ok there, but when you are processing, it
is
> authenticated using the login that start up AS, so that login might not
have[vbcol=seagreen]
> access to the sql server tables to process the dimensions.
>
> "Bob" wrote:
>
of[vbcol=seagreen]
the[vbcol=seagreen]

OLTP access

Hi,
With my OLTP on another server, OLAP cube design works up to the point of
actually processing the cube, then it errors processing the dimension.
If I move the OLTP database (SQL Server 2000) onto the machine running the
OLAP it works fine.
It seems to be a rights issue but the ODBC tests work fine and the cube
design has no trouble viewing the OLTP schema.
In its 'normal' position the OLTP is on a Win2k Server that is part of a
work group.
The OLAP is on a Win2k Domain Controller.
Any clues on how to establish a successful processing of the OLTP in its
normal position would be appreciated.
Thanks
Bob
have you made sure the services for OLTP sql server and AS on the other
server are started with the same admin account and not local?
When you are in the design in AS, you are authenticated using your login to
the sql server source so it is ok there, but when you are processing, it is
authenticated using the login that start up AS, so that login might not have
access to the sql server tables to process the dimensions.
"Bob" wrote:

> Hi,
> With my OLTP on another server, OLAP cube design works up to the point of
> actually processing the cube, then it errors processing the dimension.
> If I move the OLTP database (SQL Server 2000) onto the machine running the
> OLAP it works fine.
> It seems to be a rights issue but the ODBC tests work fine and the cube
> design has no trouble viewing the OLTP schema.
> In its 'normal' position the OLTP is on a Win2k Server that is part of a
> work group.
> The OLAP is on a Win2k Domain Controller.
> Any clues on how to establish a successful processing of the OLTP in its
> normal position would be appreciated.
> Thanks
> Bob
>
>
|||Hi,
Thanks for your reply.
I altered the OLAP service to run under administrator but it has made no
difference.
This is extremely poor design in IMHO. If the ODBC object can connect to
the OLTP, the OLAP should be able to process, end of story.
There is no trusted connection between the OLAP machine (Win2k Domain
Controller) and the OLTP machine (Win2k Server but running as a member of a
workgroup)
So I am relying ODBC to sort out the security issues.
e.g.. Access can use ODBC to link to tables in the OLTP so how come the OLAP
is so frail?
I have tried to create an ODBC connection to the OLTP that uses SQL server
authentication but have failed . I keep getting
'Not associated with a trusted SQL server connection'.
In a perfect world I would promote the OLTP to be a domain controller of its
own domain and establish a trust relationship between the to domains. But no
can do.
I think Bill's merry men should be looking at this.
Either I am missing something fundamental or the OLAP connection needs
redesigning.
No way should it be this difficult to use in this situation.
i.e. Anything that Access can do, OLAP should be able to do. The security
implications are the same.
regards
Bob
"bc" <bc@.discussions.microsoft.com> wrote in message
news:D6258097-009A-492A-BA75-2AC40A664968@.microsoft.com...
> have you made sure the services for OLTP sql server and AS on the other
> server are started with the same admin account and not local?
> When you are in the design in AS, you are authenticated using your login
to
> the sql server source so it is ok there, but when you are processing, it
is
> authenticated using the login that start up AS, so that login might not
have[vbcol=seagreen]
> access to the sql server tables to process the dimensions.
>
> "Bob" wrote:
of[vbcol=seagreen]
the[vbcol=seagreen]

OLE DB provider MSDAORA was unable to begin a distributed transaction

Dear all,

I have been attempting to set up a linked server in SQL Server 2000 to point to an Oracle database (a very old Oracle database, v7!).

The linked server set up works fine. Then I created a database on the same SQL Server. Within that database I created a view which reads information from a view in the Oracle database (linked server). When I attempted to create this view in Enterprise Manager, I get the following error:

"The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

And so I created the view in Query Analyzer. This worked perfectly, no errors, and I can now go into Enterprise Manager and call up the view, which happily goes off to the view in the Oracle linked server and pulls back the info. BUT, when I attempt to do the same thing through an application on a different machine (using OLE Db, and a UDL to connect) the same error as above appears.

Does anyone know what this error means, and where I went wrong? Any help greatly appreciated. My view onto the linked server looks like this:

"CREATE VIEW dbo.NLPG_VIEW
AS
SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')"

Many thanks in advance for your wisdom!

P.S I wondered whether I need to check if the distributed transaction co-ordinator is running but don't know how.
Thanks,http://www.aspfaq.com/2455 fyi.|||Thanks, I've already had aread through this. I did create the view in query analyzer but the problem now is that I can't get it to work from a remote application. I think I need to look into the distributed transaction co-ordinator, but I don't know how or where. How do I see if it is running? How do I get it running?|||On the SQL Server, start the SQL Services Manager. Change the Service to Distributed Transaction Coordinator. Play to your heart's content!

FYI, with Oracle 7.3 I needed to use SET REMOTE_PROC_TRANSACTIONS (http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_206r.asp) to make things happy.

-PatP|||Would SET_REMOTE_PROC_TRANSACTIONS have to be coded into the app, as I can't put it in the SQL in the view can I?|||http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx a guide to the solution.
Also check whether compatible Oracle driver is being used to connect, try with latest drivers.|||When you said remote procedure transactions, is that the same as setting remote procedure call in the GUI in enterprise manager? RPC and RPC OUT?

Thanks,
Paula|||No Polly, the concepts are related, but definitely not the same.

The problem is that SQL Server uses many flavors of locks in order to acheive true data consistancy... Some locks prohibit writing, some prohibit any access at all, and some do things that are difficult to describe in English, but make the engine behave as well as possible.

I haven't tried this in a long time, and no longer have an Oracle 7.x server to even test with, but I think you could get by with:SET REMOTE_PROC_TRANSACTIONS OFF
GO
CREATE VIEW dbo.NLPG_VIEW
AS
SELECT * from openquery(sadaslink, 'select * from NLPG_VIEW')All I can suggest that you do is to try it... I don't see how the attempt could hurt anything.

-PatP|||Thanks very much, I'll try it. Can't test it until next week, but Il'l let you know the results.