do i link or use different query?

first post, forum looks like it might help me keep from pulling my hair out :)

noob here to sql and crystal reports, but learning as I go. I hope I can get a bit of guidance here.

I am designing a report to show productivity of employees across our system. I have a query that I am using to extract data for each column and the query will always return 'employee' as the first column and then the data I want as the second column. (in the basic query results)

So, the first 2 columns on the report are both columns from the first query. Employee and hours worked. For the remainder of the columns, I am just insterting the 2nd column from the next queries. Sales, Trades, etc.

The problem I just ran into is that I want to display something which not every employee has a record for. For instance, I want to track how many memberships each employee sold. The way my query is written, when I add it to the report, it supressed the data for the employees that have not sold any memberships. Since it supressed the whole row, I cannot view the results from the other data. My thought is that it should keep the employee row, but list a '0' for that particular column.

Is this going to be a query re-write, or can crystal do what I need?

thanks in advance.Change your link from your employee query to your new query to a left outer join. That way all employee's will show up even if no memberships were sold.
GJ|||thanks. took me a minute to find out where to do that, but i found it.

how about getting those null values to display as a 0 (zero)

and if you can tell me where to find it this time :)

thanks again!|||With Crystal open go to file options and click the reporting tab. Check the convert database null values to default. If greyed out go to database tab and uncheck grouping on server, then you can check the null values check box.

GJ.|||awesome. you know, i try to dig around before i ask, because i just love learning new stuff. i actually got it to work a different way too by using a if else function with some searching around. im gonna try it your way too.

mind if i ask a couple more questions? hehe

i have basically created one report with 23 sub reports in the report footer.

1) is there a way to uniformly space out the subreports (vertically) if more employees show up so i dont have to edit the layout? maybe a percentage of distance away from the bottom of the report above?

2) I figured out how to alternate row color, but if my subreports have an uneven amount of rows, i get 2 rows with the same color

IF (RecordNumber MOD 2 = 0) THEN
crSilver
ELSE
DefaultAttribute

maybe the formula is wrong if i want to have subreports?

3) When using the summary feature, I cannot seem to get it accurate. I have tested a couple sum summarys and avg summaries. If I double check it against an exported excel file, the numbers don't match. Out of the 23 stores, we have several 'regions'. I was initially going to create region reports to be summarized, so that when I add them to the main report, they can see the sum or average of certain data for other regions.

well, that is pretty much all i got left to figure out on this report, so thanks so much for the help!

DNS-less connection w/ no prompt VB

I have a problem trying to link my access table using VB
I can connect using the below connection string ...
Driver={SQL
Server};SERVER=MYSERVER;UID=MYUSERNAME;PWD=myPASSWORD;DATABASE=myDATABASE;
WHEN I USE...
Set dbsODBC = OpenDatabase("",False, False, strConnect)
BUT IF I try to disable the prompt using...
Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False, strConnect)
AND
Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False,"ODBC;" &
strConnect)
my connection either does not connect or it displays the prompt. I
think the problem has something to do with me not using a DSN, but I
thought I should be able to connect without one. PLEASE HELP!
What am I doing wrong, and why would this be happening?
THANK YOU!!I don't understand your question. Open Database is a method of the Jet
database engine (DAO) used with Microsoft Access, not SQL Server. The
connection string you are using is not the correct syntax for the DAO
OpenDatabase method. If you're trying to open an Access database that has a
table in it that is linked to SQL Server, then the linked table has to have
the DSN-less connection string in it's Connect property and you need to use
the syntax for DAO as the parameter to the OpenDatabase function. But, I'm
having a hard time figuring out why you would want to do this? To connect to
a SQL Server DB from VB you should be using ADO (ActiveX Data Objects). The
connection string you are using *is* the correct syntax for the ADO
Connection object's Open method. No need for Jet or Access at this point.
<stoppal@.hotmail.com> wrote in message
news:1129838783.819043.87440@.o13g2000cwo.googlegroups.com...
> I have a problem trying to link my access table using VB
>
> I can connect using the below connection string ...
>
> Driver={SQL
> Server};SERVER=MYSERVER;UID=MYUSERNAME;PWD=myPASSWORD;DATABASE=myDATABASE;
>
> WHEN I USE...
>
> Set dbsODBC = OpenDatabase("",False, False, strConnect)
>
> BUT IF I try to disable the prompt using...
>
> Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False, strConnect)
> AND
> Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False,"ODBC;" &
> strConnect)
>
> my connection either does not connect or it displays the prompt. I
> think the problem has something to do with me not using a DSN, but I
> thought I should be able to connect without one. PLEASE HELP!
>
> What am I doing wrong, and why would this be happening?
>
> THANK YOU!!
>|||thank you for the recommendation I'll try it at work tommorrow.
I'll tell you if it works friday, morning|||GOT IT WORKS, THANK YOU!!!!!!!!!!!!!!!!

DNS-less connection w/ no prompt VB

I have a problem trying to link my access table using VB
I can connect using the below connection string ...
Driver={SQL
Server};SERVER=MYSERVER;UID=MYUSERNAME;P
WD=myPASSWORD;DATABASE=myDATABASE;
WHEN I USE...
Set dbsODBC = OpenDatabase("",False, False, strConnect)
BUT IF I try to disable the prompt using...
Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False, strConnect)
AND
Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False,"ODBC;" &
strConnect)
my connection either does not connect or it displays the prompt. I
think the problem has something to do with me not using a DSN, but I
thought I should be able to connect without one. PLEASE HELP!
What am I doing wrong, and why would this be happening?
THANK YOU!!I don't understand your question. Open Database is a method of the Jet
database engine (DAO) used with Microsoft Access, not SQL Server. The
connection string you are using is not the correct syntax for the DAO
OpenDatabase method. If you're trying to open an Access database that has a
table in it that is linked to SQL Server, then the linked table has to have
the DSN-less connection string in it's Connect property and you need to use
the syntax for DAO as the parameter to the OpenDatabase function. But, I'm
having a hard time figuring out why you would want to do this? To connect to
a SQL Server DB from VB you should be using ADO (ActiveX Data Objects). The
connection string you are using *is* the correct syntax for the ADO
Connection object's Open method. No need for Jet or Access at this point.
<stoppal@.hotmail.com> wrote in message
news:1129838783.819043.87440@.o13g2000cwo.googlegroups.com...
> I have a problem trying to link my access table using VB
>
> I can connect using the below connection string ...
>
> Driver={SQL
> Server};SERVER=MYSERVER;UID=MYUSERNAME;P
WD=myPASSWORD;DATABASE=myDATABASE;
>
> WHEN I USE...
>
> Set dbsODBC = OpenDatabase("",False, False, strConnect)
>
> BUT IF I try to disable the prompt using...
>
> Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False, strConnect)
> AND
> Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False,"ODBC;" &
> strConnect)
>
> my connection either does not connect or it displays the prompt. I
> think the problem has something to do with me not using a DSN, but I
> thought I should be able to connect without one. PLEASE HELP!
>
> What am I doing wrong, and why would this be happening?
>
> THANK YOU!!
>|||thank you for the recommendation I'll try it at work tommorrow.
I'll tell you if it works friday, morning|||GOT IT WORKS, THANK YOU!!!!!!!!!!!!!!!!

DNS-less connection w/ no prompt VB

I have a problem trying to link my access table using VB
I can connect using the below connection string ...
Driver={SQL
Server};SERVER=MYSERVER;UID=MYUSERNAME;PWD=myPASSW ORD;DATABASE=myDATABASE;
WHEN I USE...
Set dbsODBC = OpenDatabase("",False, False, strConnect)
BUT IF I try to disable the prompt using...
Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False, strConnect)
AND
Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False,"ODBC;" &
strConnect)
my connection either does not connect or it displays the prompt. I
think the problem has something to do with me not using a DSN, but I
thought I should be able to connect without one. PLEASE HELP!
What am I doing wrong, and why would this be happening?
THANK YOU!!
I don't understand your question. Open Database is a method of the Jet
database engine (DAO) used with Microsoft Access, not SQL Server. The
connection string you are using is not the correct syntax for the DAO
OpenDatabase method. If you're trying to open an Access database that has a
table in it that is linked to SQL Server, then the linked table has to have
the DSN-less connection string in it's Connect property and you need to use
the syntax for DAO as the parameter to the OpenDatabase function. But, I'm
having a hard time figuring out why you would want to do this? To connect to
a SQL Server DB from VB you should be using ADO (ActiveX Data Objects). The
connection string you are using *is* the correct syntax for the ADO
Connection object's Open method. No need for Jet or Access at this point.
<stoppal@.hotmail.com> wrote in message
news:1129838783.819043.87440@.o13g2000cwo.googlegro ups.com...
> I have a problem trying to link my access table using VB
>
> I can connect using the below connection string ...
>
> Driver={SQL
> Server};SERVER=MYSERVER;UID=MYUSERNAME;PWD=myPASSW ORD;DATABASE=myDATABASE;
>
> WHEN I USE...
>
> Set dbsODBC = OpenDatabase("",False, False, strConnect)
>
> BUT IF I try to disable the prompt using...
>
> Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False, strConnect)
> AND
> Set dbsODBC = OpenDatabase("", dbDriverNoPrompt, False,"ODBC;" &
> strConnect)
>
> my connection either does not connect or it displays the prompt. I
> think the problem has something to do with me not using a DSN, but I
> thought I should be able to connect without one. PLEASE HELP!
>
> What am I doing wrong, and why would this be happening?
>
> THANK YOU!!
>
|||thank you for the recommendation I'll try it at work tommorrow.
I'll tell you if it works friday, morning
|||GOT IT WORKS, THANK YOU!!!!!!!!!!!!!!!!

Distributor server

I have a merge replication environment with 1 publisher/distributor in the
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>

Distributed transactions and replication creation: which is the link?

Hi all,
I'm getting a strange error after reinstalling and recovering a database
server on a new hardware. It was posted on
http://groups.google.com/group/micro...f6213ef6965f41
My new questions are:
-Is replication (or at least replication creation proccess) related to
distributed transaction coordinator (MSDTC)?
-How can I get assured the MSDTC is working properly? Is it possible to
activate a detailed, (I mean verbose) log of activity? In the Event
Viewer/Application Log the only event related to MSDTC is informative: 4156
"Session idle timeout over, tearing down the session" once or twice a day
-In the case the distributor and the publishers are located in separate
servers, if there is involved a distributed transaction, is it the
distributor who initiates the distributed transaction towards the publisher?
-In the Enterprise Manager, in the properties of the server, Connections
tab, "Enforce distributed transactions (MTS)" checkbox has any influence in
the replication configuration? In the previous configuration we tested
distributed transaction between these two servers and moved this checkbox
and other related configuration parameters but had no problems with the
working replication, even we recreated the publication many times without
any problems.
I have no clear clues. Any hint is welcomed.
Thanks in advance
Sammy
The problem was solved the following way:
The publisher server had "Enforce distributed transaction (MTS)" checked. We
unchecked this and restarted all the services, including MSDTC. After that
the problem disappeared.
Hope it helps anybody else
Sammy

Distributed transaction error

We have a server, for various reasons has link to a loop back linked server.
We have dozens of stored procedures that refer to the linked server in their
code. They have worked fine for several years until a developer added an on
update/insert trigger to a table. Now, when these stored procedures execute
,
there is a distritbuted transaction error. Distributed Transaction
Coordinator is verified as being on.
Does anyone know of a way to fix this or what is going on?
Thank you.For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
option XACT_ABORT should also be on to support modifications in a
distributed transaction.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Distributed transactions are not supported on loopback linked servers! If
itrigger is requred on a table and references another database on the same
server, hardcode the full 3 part name, ommiting server name.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Thanks for the advice, however, we have tested it with XACT_ABORT set to ON.
One of our database guru's mentioned:
--start quote--
“SQL Server still sees the all the involved statements as a single
transaction and because it have a call to the linked server, it tries to
initiates it as distributed transaction.”
I read in the sql server docs that whenever two databases are involved, sql
server treats the transaction as a distributed transaction even if the
databases are on the same box
--end quote--
Example: our stored procedure has a query similar to the following:
sql1 is the loop back linked server, the database resides ON the same box,
however, for various reasons we have left the sp to reference it as a linked
server.
update mytable
set mytable.column1 = 1
where NOT EXISTS
(select id from sql1.dbo.mytable2 p where p.column = mytable.column)
The following error message is generated whenever the trigger on
update/insert is active on the table mytable. We remove the trigger and the
SP works as always:
error msg: The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
Thanks for any help you can provide.
"Itzik Ben-Gan" wrote:

> For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
> option XACT_ABORT should also be on to support modifications in a
> distributed transaction.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||I appreciate your response Farmer,
however, the trigger itself has no reference at all to the loopback linked
server. It is trying to update a simple field in a table on the same server
as the trigger. The error happens in the SP, which has worked for several
years. If you remove the trigger, the SP works again.
"Farmer" wrote:

> Distributed transactions are not supported on loopback linked servers! If
> itrigger is requred on a table and references another database on the same
> server, hardcode the full 3 part name, ommiting server name.
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||Any data modification statements BEGIN an implisit transactions, that is why
selects worked and update fails now. Because it is involving linked server,
it is considered distributed.
BOL
Starting Transactions
You can start transactions in Microsoft SQL ServerT as explicit,
autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL
statement is committed when it completes. You do not have to specify any
statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the
Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement
automatically starts a new transaction. When that transaction is completed,
the next Transact-SQL statement starts a new transaction.
Connection modes are managed at the connection level. If one connection
changes from one transaction mode to another it has no effect on the
transaction modes of any other connection.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:A8695506-E70A-4412-8417-97E91699C2A0@.microsoft.com...
> Thanks for the advice, however, we have tested it with XACT_ABORT set to
> ON.
> One of our database guru's mentioned:
> --start quote--
> "SQL Server still sees the all the involved statements as a single
> transaction and because it have a call to the linked server, it tries to
> initiates it as distributed transaction."
> I read in the sql server docs that whenever two databases are involved,
> sql
> server treats the transaction as a distributed transaction even if the
> databases are on the same box
> --end quote--
> Example: our stored procedure has a query similar to the following:
> sql1 is the loop back linked server, the database resides ON the same box,
> however, for various reasons we have left the sp to reference it as a
> linked
> server.
> update mytable
> set mytable.column1 = 1
> where NOT EXISTS
> (select id from sql1.dbo.mytable2 p where p.column = mytable.column)
> The following error message is generated whenever the trigger on
> update/insert is active on the table mytable. We remove the trigger and
> the
> SP works as always:
> error msg: The operation could not be performed because the OLE DB
> provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
>
> Thanks for any help you can provide.
>
> "Itzik Ben-Gan" wrote:
>

DISTRIBUTED TRANSACTION

Hi,
I have two sql servers with SQL2000 service pack 3 which are linked by the "
Link Server". When i use the "begin tran" (distributed transaction) in store
d procedure, i am getting the following error.
"Server: Msg 8525, Level 16, State 1, Line 1
Distributed transaction completed. Either enlist this session in a new trans
action or the NULL transaction. "
The following article talks about this problem.
http://support.microsoft.com/?kbid=834849
But, both servers are SQL 2000 in my case. Any help?
Thanks,
VijayHi, i'm going this trouble but the versions are different. Server A has Sql
server 2000 enterprise edition and the server B has Sql Server 7.0, both
servers are linked properly and running msdtc. No changes were made to their
configurations. If I run the sentence with Begin distributed tran and commin
distributed tran it end right but using that statement it fails with the
error commented is this post.
Any ideas?
"Vijay" wrote:

> Hi,
>
> I have two sql servers with SQL2000 service pack 3 which are linked by the
"Link Server". When i use the "begin tran" (distributed transaction) in sto
red procedure, i am getting the following error.
>
> "Server: Msg 8525, Level 16, State 1, Line 1
> Distributed transaction completed. Either enlist this session in a new tra
nsaction or the NULL transaction. "
>
> The following article talks about this problem.
> http://support.microsoft.com/?kbid=834849
> But, both servers are SQL 2000 in my case. Any help?
>
> Thanks,
> Vijay
>
>

Distributed transaction

We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
can run views in S2 and S3 however we cannot modify the views in S2 or add
new views linked to S11.
Actually, we can modify the views and run them but when we tried to save the
changes, this error message will show up:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specific transaction
coordinator.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
What should we do to fix this?
All servers run Windows 2000 and SQL 2000.
Thanks.
Hello lwidjaya,
sounds like your using the EM tool for these changes, I would suggest using
query analyzer for this.
there are a number of things that might cause this issue, so you need to
know what is different configuration wise from s1 and s11, so do an inventor
of security and setup.
John Vandervliet...
"lwidjaya" wrote:

> We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
> and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
> can run views in S2 and S3 however we cannot modify the views in S2 or add
> new views linked to S11.
> Actually, we can modify the views and run them but when we tried to save the
> changes, this error message will show up:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specific transaction
> coordinator.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> What should we do to fix this?
> All servers run Windows 2000 and SQL 2000.
> Thanks.
|||Hi John,
thanks for your reply.
Our IT guy just found out what happened. He needs to add the server name and
ip address in the hosts file in winnt folder.
Lisa
"John Vandervliet" wrote:
[vbcol=seagreen]
> Hello lwidjaya,
> sounds like your using the EM tool for these changes, I would suggest using
> query analyzer for this.
> there are a number of things that might cause this issue, so you need to
> know what is different configuration wise from s1 and s11, so do an inventor
> of security and setup.
> John Vandervliet...
> "lwidjaya" wrote:

Distributed transaction

We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
can run views in S2 and S3 however we cannot modify the views in S2 or add
new views linked to S11.
Actually, we can modify the views and run them but when we tried to save the
changes, this error message will show up:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The o
peration
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provi
der returned
message: New transaction cannot enlist in the specific transaction
coordinator.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trac
e [OLE/DB
Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
What should we do to fix this?
All servers run Windows 2000 and SQL 2000.
Thanks.Hello lwidjaya,
sounds like your using the EM tool for these changes, I would suggest using
query analyzer for this.
there are a number of things that might cause this issue, so you need to
know what is different configuration wise from s1 and s11, so do an inventor
of security and setup.
John Vandervliet...
"lwidjaya" wrote:

> We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both
S2
> and S3 have views that link to S1. Recently we replaced S1 with S11. Now,
we
> can run views in S2 and S3 however we cannot modify the views in S2 or add
> new views linked to S11.
> Actually, we can modify the views and run them but when we tried to save t
he
> changes, this error message will show up:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable t
o
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB pro
vider returned
> message: New transaction cannot enlist in the specific transaction
> coordinator.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> What should we do to fix this?
> All servers run Windows 2000 and SQL 2000.
> Thanks.|||Hi John,
thanks for your reply.
Our IT guy just found out what happened. He needs to add the server name and
ip address in the hosts file in winnt folder.
Lisa
"John Vandervliet" wrote:
[vbcol=seagreen]
> Hello lwidjaya,
> sounds like your using the EM tool for these changes, I would suggest usin
g
> query analyzer for this.
> there are a number of things that might cause this issue, so you need to
> know what is different configuration wise from s1 and s11, so do an invent
or
> of security and setup.
> John Vandervliet...
> "lwidjaya" wrote:
>

Distributed transaction

We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
can run views in S2 and S3 however we cannot modify the views in S2 or add
new views linked to S11.
Actually, we can modify the views and run them but when we tried to save the
changes, this error message will show up:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specific transaction
coordinator.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
What should we do to fix this?
All servers run Windows 2000 and SQL 2000.
Thanks.Hello lwidjaya,
sounds like your using the EM tool for these changes, I would suggest using
query analyzer for this.
there are a number of things that might cause this issue, so you need to
know what is different configuration wise from s1 and s11, so do an inventor
of security and setup.
John Vandervliet...
"lwidjaya" wrote:
> We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
> and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
> can run views in S2 and S3 however we cannot modify the views in S2 or add
> new views linked to S11.
> Actually, we can modify the views and run them but when we tried to save the
> changes, this error message will show up:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: New transaction cannot enlist in the specific transaction
> coordinator.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> What should we do to fix this?
> All servers run Windows 2000 and SQL 2000.
> Thanks.|||Hi John,
thanks for your reply.
Our IT guy just found out what happened. He needs to add the server name and
ip address in the hosts file in winnt folder.
Lisa
"John Vandervliet" wrote:
> Hello lwidjaya,
> sounds like your using the EM tool for these changes, I would suggest using
> query analyzer for this.
> there are a number of things that might cause this issue, so you need to
> know what is different configuration wise from s1 and s11, so do an inventor
> of security and setup.
> John Vandervliet...
> "lwidjaya" wrote:
> > We had 3 SQL servers (S1, S2, S3). S1 and S3 are in the same domain. both S2
> > and S3 have views that link to S1. Recently we replaced S1 with S11. Now, we
> > can run views in S2 and S3 however we cannot modify the views in S2 or add
> > new views linked to S11.
> > Actually, we can modify the views and run them but when we tried to save the
> > changes, this error message will show up:
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> > could not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> > begin a distributed transaction.
> > [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> > message: New transaction cannot enlist in the specific transaction
> > coordinator.]
> > [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> > Provider 'SQLOLEDB' ITransactionJoinTransaction returned 0x8004d00a].
> >
> > What should we do to fix this?
> >
> > All servers run Windows 2000 and SQL 2000.
> >
> > Thanks.