Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
John
In general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John
|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John
|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
sql

Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the mai
n
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting t
o
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example abov
e
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John

Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
>> Perhaps if you provided a bit more details about what you are attempting
>> to
>> accomplish, someone here will have a better solution.
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John

Newbie question

I would like to know how to be able to insert into one table for example and
have that table linked to another table.
For example, I have a Wrrok Order system VIA the Web, what happens is
someone will create a work order, and the records are created. I would like
to be able to dynamically add notes to it so that whenever a note is added
to a certain work order, it is added into a second table with a record
called notes (I have primary Keys on both Tables called "wo_id")
Then we I retrieve the Work Order using the Primary Key I want it to display
all of the notes in the secondary table related to that Primary Key.
Any help would be great, thanks and let me know if I did not ask it
correctly!> For example, I have a Wrrok Order system VIA the Web, what happens is
> someone will create a work order, and the records are created. I would
like
> to be able to dynamically add notes to it so that whenever a note is added
> to a certain work order, it is added into a second table with a record
> called notes (I have primary Keys on both Tables called "wo_id")
Just execute an INSERT statement with the wo_id for the work order and the
note for the second column.
> Then we I retrieve the Work Order using the Primary Key I want it to
display
> all of the notes in the secondary table related to that Primary Key.
Either first select the relevant columns from the WorkOrder table and
another SELECT that select the relevant columns from the other table.
SELECT col1, col2...
FROM WorkOrder
WHERE wo_id = ...
SELECT col1, col3
FROM SecTable
WHERE wo_id = ...
Or, do a join:
SELECT wo.col1, wo.col2, sec.col1, sec.col2
FROM WorkOrder AS wo INNER JOIN SecTable AS sec ON sec.wo_id = wo.wo_id
WHERE wo_id = ...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JC" <Josh@.Network-Medics.Com> wrote in message
news:uWTMO8JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> I would like to know how to be able to insert into one table for example
and
> have that table linked to another table.
> For example, I have a Wrrok Order system VIA the Web, what happens is
> someone will create a work order, and the records are created. I would
like
> to be able to dynamically add notes to it so that whenever a note is added
> to a certain work order, it is added into a second table with a record
> called notes (I have primary Keys on both Tables called "wo_id")
> Then we I retrieve the Work Order using the Primary Key I want it to
display
> all of the notes in the secondary table related to that Primary Key.
> Any help would be great, thanks and let me know if I did not ask it
> correctly!
>|||Thanks!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:evoRiLKpDHA.372@.TK2MSFTNGP11.phx.gbl...
> > For example, I have a Wrrok Order system VIA the Web, what happens is
> > someone will create a work order, and the records are created. I would
> like
> > to be able to dynamically add notes to it so that whenever a note is
added
> > to a certain work order, it is added into a second table with a record
> > called notes (I have primary Keys on both Tables called "wo_id")
> Just execute an INSERT statement with the wo_id for the work order and the
> note for the second column.
>
> > Then we I retrieve the Work Order using the Primary Key I want it to
> display
> > all of the notes in the secondary table related to that Primary Key.
> Either first select the relevant columns from the WorkOrder table and
> another SELECT that select the relevant columns from the other table.
> SELECT col1, col2...
> FROM WorkOrder
> WHERE wo_id = ...
> SELECT col1, col3
> FROM SecTable
> WHERE wo_id = ...
> Or, do a join:
> SELECT wo.col1, wo.col2, sec.col1, sec.col2
> FROM WorkOrder AS wo INNER JOIN SecTable AS sec ON sec.wo_id = wo.wo_id
> WHERE wo_id = ...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "JC" <Josh@.Network-Medics.Com> wrote in message
> news:uWTMO8JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > I would like to know how to be able to insert into one table for example
> and
> > have that table linked to another table.
> >
> > For example, I have a Wrrok Order system VIA the Web, what happens is
> > someone will create a work order, and the records are created. I would
> like
> > to be able to dynamically add notes to it so that whenever a note is
added
> > to a certain work order, it is added into a second table with a record
> > called notes (I have primary Keys on both Tables called "wo_id")
> >
> > Then we I retrieve the Work Order using the Primary Key I want it to
> display
> > all of the notes in the secondary table related to that Primary Key.
> >
> > Any help would be great, thanks and let me know if I did not ask it
> > correctly!
> >
> >
>

Newbie Q: VBA referencing recordset in linked MSDE SQL table

Hello,
I posted the following question a few days ago and then had to focus on a
different emergency so I didn’t have an opportunity to follow up with
additional information requested. Below is a copy of my initial post, an
example of the code, and the error that is triggered. I’m sorry I was too
vague before.
Thanks!!
Andrea
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hi! I am brand spankin' new at this and need help! We just upsized an
Access 2002 database to having an MSDE backend. We used Access's wizard.
In general, the forms seem to be working fine. My problem is with the VBA
underneath. The code breaks when it tries to work with recordsets. I get a
runtime error, “Item not found in this collection.” Between the text book I
have and my web searches, I can't seem to figure out how I need to reference
the SQL tables or if there is a reference library that I need to activate to
make this work now. I have a lot of code like this and for the time being I
am not interested in converting it all to ADO. That can come later. I just
need to get this functional for the client.
The error that is triggered is: Runtime Error 3265 “Item not found in this
collection”
Below is a sample of the code. It breaks at the line that reads:
If Me.NewRecord Then
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
Private Sub Form_Current()
Dim rec As Recordset
' If the form is showing a new record, assignes next chronoligical id number
stored
' in system_maint
If Me.NewRecord Then
Set rec = CurrentDb.OpenRecordset("system_maint")
Me.ID = rec("last_id_num") + 1
rec.Edit
rec("last_id_num") = Me.ID
rec.Update
rec.Close
End If
' requeries all look-up combo boxes.
Me.Artist.Requery
Donor_s_Name.Requery
Building.Requery
End Sub
Andrea
Andrea M wrote:

> Hello,
> I posted the following question a few days ago and then had to focus on a
> different emergency so I didn’t have an opportunity to follow up with
> additional information requested. Below is a copy of my initial post, an
> example of the code, and the error that is triggered. I’m sorry I was too
> vague before.
> Thanks!!
> Andrea
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Hi! I am brand spankin' new at this and need help! We just upsized an
> Access 2002 database to having an MSDE backend. We used Access's wizard.
> In general, the forms seem to be working fine. My problem is with the VBA
> underneath. The code breaks when it tries to work with recordsets. I get a
> runtime error, “Item not found in this collection.” Between the text book I
> have and my web searches, I can't seem to figure out how I need to reference
> the SQL tables or if there is a reference library that I need to activate to
> make this work now. I have a lot of code like this and for the time being I
> am not interested in converting it all to ADO. That can come later. I just
> need to get this functional for the client.
> The error that is triggered is: Runtime Error 3265 “Item not found in this
> collection”
> Below is a sample of the code. It breaks at the line that reads:
> If Me.NewRecord Then
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
> Private Sub Form_Current()
> Dim rec As Recordset
> ' If the form is showing a new record, assignes next chronoligical id number
> stored
> ' in system_maint

> If Me.NewRecord Then
> Set rec = CurrentDb.OpenRecordset("system_maint")
and try CurrentProject instead of CurrentDB.
CurrentProject.OpenRecordset will return an ADO recordset object, which
is what "dim rec as Recordset" has made a reference to.
CurrentDB.OpenRecordset returns a DAO Recordset object.

> Me.ID = rec("last_id_num") + 1
also try:
Me!ID = rec("last_id_num") + 1
> rec.Edit
> rec("last_id_num") = Me.ID
and...
rec("last_id_num") = me!ID
This will ensure that you're accessing the ID control on the form,
instead of an ID Property of the Form object that, of course, doesn't exist.

> rec.Update
> rec.Close
> End If
> ' requeries all look-up combo boxes.
> Me.Artist.Requery
> Donor_s_Name.Requery
> Building.Requery
> End Sub
>

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?
Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/default...b;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>
|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.[vbcol=seagreen]
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].
>
|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
sql

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>> Anyone know what I'm doing wrong?
>>
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> > Hi
> >
> > Have you checked
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> >
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>
> >
> >
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trac
e [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/defaul...kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].[vbcol=seagreen]
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

Newbie problem: Saving a view from a linked server wont work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:

SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1

From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].

Anyone know what I'm doing wrong?Hi

Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106

John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?|||Hi

Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106

John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?|||None of the stuff in that article seems to help. Same error message occurs.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/defaul...kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>>
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>>
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>>
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>>
>>
>> Anyone know what I'm doing wrong?
>>
>>|||Hi

This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd

John

<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> > Hi
> > Have you checked
> > http://support.microsoft.com/defaul...kb;EN-US;280106
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].

I assume that with "view designer" you mean what's in Enterprise Manager.

I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.

Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.

But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>>
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>>
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>>
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?

Newbie Design Question

I am a newbie in BI and have the following design question:

I have a scenario where I have a Sales fact table linked to a customer dimension. The customers some customers attributes might change and I would like to keep history of those attributes, such as Marital Status.
One way is to store the Marital Status as attribute in the customer dimension and enter multiple records for each customer whenever the marital status changes, but I should then add date and status fields to the customer dimension.
Another way is to store the Marital Status as a separate dimension and link it directly to the fact table. While maintaining only one record for each unique customer in the customer dimension, and in this case no Date and Status fields are needed on customer.
What is the best way of implementing this scenario? Are there any pros cons for each one, Or Maybe there is a better way of solving it?

And suppose I use the first method where multiple customer records are kept, while processing to get the sales done for people that are married, some processing should be done to only consider each customer once (Only Status is Active). This is through MDX I suppose.

I am sure this issue is typical and basic but just wanted to get some guidelines.

Appreciate your help,
Grace

Hi Grace,

These design issues are often discussed as part of the "Slowly Changing Dimension" techniques in dimensional modelling, for example in the Kimball article below. Typically these technique ensure that only the appropriate row corresponding to a dimension member is considered (one way is by using surrogate keys):

http://www.intelligententerprise.com/db_area/archives/1999/990308/warehouse.jhtml

>>

When A Slowly Changing Dimension Speeds Up
Ralph Kimball

...

>>

|||

Thanks Deepak for the guidance.

Newbie ahoy! - SQL Server database keeps freezing...

Hi,
I've something of a mystery.
Our DB currently runs as a SQL Server 2000 database linked to a number of
Access MDB front ends via an ODBC connection.
We're having problems with the response of the server. Basically, when a
user is entering / updating data, when they try to update a record, the ODBC
connection times out.
I've checked the server and there doesn't appear to be any problems with
SQL2000.
However, I am something of a complete newbie when it comes to SQL server. In
light of this can anyone recommend any steps I should take to investigate
and diagnose this.
In particular, I know SQL server keeps logs but I'm not sure to what detail
and how to access them.
Sorry to appear like a complete idiot but I guess we all have to start
somewhere. :)
Thanks
Chris StrugA common cause of these symptoms is blocking. You can run sp_who2 from
Query Analyzer to identify the blocked/blocking processes. Note that a
SELECT statement will hold locks until the resultset is processed so it
is important that the application to retrieve results as soon as
possible and keep transactions short.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Chris Strug" <hotmail@.solace1884.com> wrote in message
news:%23Ti2WgETDHA.2852@.tk2msftngp13.phx.gbl...
> Hi,
> I've something of a mystery.
> Our DB currently runs as a SQL Server 2000 database linked to a number
of
> Access MDB front ends via an ODBC connection.
> We're having problems with the response of the server. Basically, when
a
> user is entering / updating data, when they try to update a record,
the ODBC
> connection times out.
> I've checked the server and there doesn't appear to be any problems
with
> SQL2000.
> However, I am something of a complete newbie when it comes to SQL
server. In
> light of this can anyone recommend any steps I should take to
investigate
> and diagnose this.
> In particular, I know SQL server keeps logs but I'm not sure to what
detail
> and how to access them.
> Sorry to appear like a complete idiot but I guess we all have to start
> somewhere. :)
> Thanks
> Chris Strug
>