Do replication for a non-Administrators

Can I do replication to copy tables in one database which was not create by
me to another database?
possibly, you need to be system administrator or dbo to create publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"M" <mxchen@.hotvoice.com> wrote in message
news:%23Lwd3w38EHA.1188@.tk2msftngp13.phx.gbl...
> Can I do replication to copy tables in one database which was not create
by
> me to another database?
>

Do not lock tables option

sql2k sp3
From what I know the only problem with checking the "Do not lock tables
duing snapshot..." would be that the snapshot would take longer. Are there
any other downfalls?
Chris,
theoretically there would be potential of timeouts occurring due to
blocking, and even the posibility of deadlocks. Some time ago there was a
poster who was having exactly these issues with this option - these
posibilities are the price you pay for allowing greater concurrency. I
imagine most people (like myself) run snapshots out of hours so this option
isn't required.
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]

Do not delete things, it bites

Hello,

I noticed that when you delete things or substitute tables, you get in trouble.

- Delete a named query used in a partition before deleting the partition

- Delete a named Calculation from a table in the Ds view, whathever you do

- Replace a table with another table which has less columns even if none of the lesser columns are used nowhere.

The XML do not get updated accordingly leading in all kinds of errors, some requiring restore from an older version.

Just a FYI.

BTY, I do not know how to submit bug reports.

Philippe

Build 9.00.2175.00

go to connect.microsoft.com|||

I think we have both come across the same problem (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640957&SiteID=1).

For permissions, I have manually cleaned up the xml files. If you post this on connect.microsoft.com, let me know as I cannot do that. I can vote for it.

btw, how did you get build 9.00.2175.00?

|||

Yes, the changes you made in DSV will not propagate to other linked ojects. Since you are deleting things from DSV, the related binding will be broken and it is up to the user to rebind or remove it. Anyway, you should get validation error when you try to deploy and find out the broken binding.

We have already got customer request on this issue. We will consider to improve the user experience in the next release.

|||I disagree. It is not a case of improving the user experience. It is a bug.|||

A bug means that the software is not doing what it is supposed to do in the design spec. This is not a bug because we don't have it in design spec. You can say it is design flaw. However, from the user experience, people hated to see something that deleted automatically without any control. Therefore, what I suggested is to enhance the user experience. After deleting the objects in DSV, we could show a list box to list all the broken bindings and give user the option to delete the broken objects. Some advanced users may want to leave the broken bindings so that they can bind to different things instead of regenerate the objects or redo all the modifications.

Anyway, we already addressed the issue. We will consider it in future release.

Thanks

|||

This often happens when building complex products such as SQL2005. Do not take it the wrong way.

When is the planned release of the fix?

Do Linked Servers REQUIRE SA rights

I am trying to set up a linked server, and one of the user is a SQL
user. This user only has permission to select data from several tables.
When I attempt to query the remote server I receive following message:
Access to the remote server is denied because no log in-mapping exists.
However when I grant the user SA rights on the local server that user
is able to query the data.
Any thoughts ?Accessing a linked server doesn't necessarily require
someone to be a member of the sysadmins role. You need to
check the login mapping as the error suggests - look at the
security tab for the linked server.
-Sue
On 8 Jun 2005 12:41:48 -0700, "Rafet Ducic"
<rducic@.gmail.com> wrote:

>I am trying to set up a linked server, and one of the user is a SQL
>user. This user only has permission to select data from several tables.
>
>When I attempt to query the remote server I receive following message:
>Access to the remote server is denied because no log in-mapping exists.
>
>However when I grant the user SA rights on the local server that user
>is able to query the data.
>Any thoughts ?

Do I want to use a join?

I have two tables, both containing memberID fields. One table is a membership table and the other is a list of members in a committee. So one or more memberIDs from the first table are in the second table, while every memberID in the second table is in the first table.

I need to get all the memberIDs in the first table that are NOT in the second table.

The point of this is to have two lists, one of members not on the committee and one of member that are on the committee. Add a couple of arrow buttons and the user can add or delete members for the committee.

Diane

You can get all Members not on the committee by

SELECT * FROM TableA WHERE MemberId NOT IN (SELECT MemberId FROM TableB)

|||

That was fast, thank you.

Diane

|||As soon as you validated the soultion, please mark the question as answered.|||I did, when I posted my thank you.

Do I need ADO 2.8 to connect to SQL Server 2005 from Access?

We have just upgraded to SQL Server 2005 from 2000. We have an MS Access application that connects and links tables from the new SQL Server database. Users are getting errors when creating records for the first time on the subscriber databases. We have reseeded the primary keys on all the tables to no avail. The only thing we can think of that may be a problem is the version of ADO that we're using as a reference in the MS Access application. We have a reference to ADO 2.5 and don't know whether we should be using 2.8. Any suggestions? Thank you.

YOu should be able to use the MDAC 2.5 for basic funtionality (otherwise and for the new features you should use the SNAC driver). Is the SQL Server located on the servers where the application is installed ? SQL Server 2005 will install MDAC 2.8 so installing 2.5 over this version might casue an error on the server side but not if it is only installed on the client side.

HTH, Jens K. Suessmeyer.

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

Thanks for your response Jens. The Access application resides on the same servers as the databases. Both versions of MDAC exist on each server. It looks like the issues occur soon after the data is synch'd in from the Publisher database. The errors occur on forms that have subforms whose row source is a table, as opposed to a query:

Error Number: 30014

Error: The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.

After receiving the error, the users exit out of the application and log back in. They are then able to add more records without receiving any errors. But, like in the Groundhog Day movie, the exact same problem occurs the next morning after sync has run.

|||Hello,
I just got the same error message when trying to use my Access application connected to my newly installed SQL Server 2005.

Did you manage to solve the pb ?

How ?

Thanks

Do I need ADO 2.8 to connect to SQL Server 2005 from Access?

We have just upgraded to SQL Server 2005 from 2000. We have an MS Access application that connects and links tables from the new SQL Server database. Users are getting errors when creating records for the first time on the subscriber databases. We have reseeded the primary keys on all the tables to no avail. The only thing we can think of that may be a problem is the version of ADO that we're using as a reference in the MS Access application. We have a reference to ADO 2.5 and don't know whether we should be using 2.8. Any suggestions? Thank you.

YOu should be able to use the MDAC 2.5 for basic funtionality (otherwise and for the new features you should use the SNAC driver). Is the SQL Server located on the servers where the application is installed ? SQL Server 2005 will install MDAC 2.8 so installing 2.5 over this version might casue an error on the server side but not if it is only installed on the client side.

HTH, Jens K. Suessmeyer.

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

Thanks for your response Jens. The Access application resides on the same servers as the databases. Both versions of MDAC exist on each server. It looks like the issues occur soon after the data is synch'd in from the Publisher database. The errors occur on forms that have subforms whose row source is a table, as opposed to a query:

Error Number: 30014

Error: The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.

After receiving the error, the users exit out of the application and log back in. They are then able to add more records without receiving any errors. But, like in the Groundhog Day movie, the exact same problem occurs the next morning after sync has run.

|||Hello,
I just got the same error message when trying to use my Access application connected to my newly installed SQL Server 2005.

Did you manage to solve the pb ?

How ?

Thanks

Do i need 3 different tables to accomplish this?

Hi folks,
This is my first post on the SQL forum and maybe it's a dummy question but... i didn't figure it out.
Also, i'm not sure if it's the right place for this type of questions, excuse me if i'm on the wrong spot.

Here we go: I'd like to have on my web sites some articles and bellow each article a list of "Related Articles".
I started with something like this:

After that, i noticed that i have an "One to Many" relationship between the 2 tables (fields ArticleID) but, if i try to create another relationship likeArticleRelated.ArtigleRelatedID -> Articles.ArticleIDit will looks like a circle between the 2 tables and i'm not sure if it's the best way (even if it will work).

Before somebody ask "Wy do i need this third relationship", besides other good reasons, i'm using "LINQ To SQL" and i just drag and drop the tables to the designer window and it automatically create classes and colections based on the existent relationship.

I appreciate any help.

It is a general principle of database design that wherever possible all relationships should be constrained at the database level. Therefore I suggest that you do define the second relationship. I have just set up two tables as you have done. When I added the second relationship, I ended up with a second copy of Articles on the relationship diagram. I suggest that you try again to create the second relationship.

|||

thanks a lot

Do a lot of linked tables cause block?

Hello, everyone:
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
ZYTNo, It should not cause any problems. How did you bring it into sql2k

DMV data lifetime

I'm interested to know how long data 'resides' in a DMV - what causes that data to change etc. since they are dynamic views, and not tables, and I'm sure I've queried that, onyl to find that, later, the data is no longer in there. Is it on server restart, or is there a process or something else that defines how?

I've reviewd the BOL (http://msdn2.microsoft.com/en-us/library/ms188754.aspx) but that didn't really make me any wiser...

Cheers

Dynamic Management Views doesn't hold any data as they return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. The results are produced when they are called and results are dependant on underlying information existence.

|||

Thanks Satya. I was aware that these are not 'tables' that hold the data... my base question is: how long is the "underlying information" going to "exist".

For example: sys.dm_exec_query_stats ... how long will that information be in there? Since I am considering setting up some 'snapshotting jobs' that will capture some of the information available in some of the sys.dm_ views, I need to know what data is available, and how long it is available for...

|||

Geth

As per the explanation I believe it will not be stored anywhere and the results will be in cache to provide the information, even your explanation gives me another thought and would like to here from Dev. team in this regard.

|||

I'd also love to see a dev post on this topic as well, Satya. while I was hoping someone could point me to a definitive answer somewhere in BOL, it is nice to know that it isn't just a case of my not seeing/finding the answer in BOL/google/forums etc.

The closest I've come is that this data might be available until SQL Service stops - but that (a) seems very long, (b) not necessarily accurate - I am 80% sure I had some info from the query_stats DMV that was available in the morning, yet gone by the afernoon, when I wanted to show someone something... (hence the reason I am interested in capturing and persisting the interesting bits to a DB).

|||

Hello,

The answer is "it depends on the DMV".

In the case of sys.dm_exec_query_stats, the BOL says

Returns aggregate performance statistics for cached query plans. The view contains one row per query plan and the lifetime of the row is tied to the plan itself. When a plan is removed from the cache, the corresponding row is eliminated from this view.

You and other customers have expressed "I am interested in capturing and persisting the interesting bits to a DB" and we have heard you.

Thanks!

DML against remote tables (MSSQL to DB2)

I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME

I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?Upon further review I found this site: http://support.microsoft.com/kb/270119/EN-US/

It shows how to use openquery to execute DML statements. nifty.

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!
Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/
|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>
|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divid
e
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:
> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Divide by a first row of resultset in a matrix

I'm creating my first matrix based report, and find that some things that
work with tables doesn't work the same way with matrix.
My resultset comes from an MDX statement. It looks something like this
..............................Registered hours ..........Amount
.............Total.....................10...................
1000
Month1....Group 1..................6.....................600
.............Group
2..................4.....................400
.............Total.....................12...................
1200
Month2....Group 1..................8.....................800
.............Group
2..................4.....................400
The customer wants to display the values for the months next to each other,
for each value. Now, I've managed to get this with a matrix, having the
[static group] ordered on top. (Thanks to Ohjoo Kwon).
BUT one of my columns is supposed to be "Group Result as % of total result."
Meaning for Group1 in Month1 I need to divide 600 on the Total Amount. The
first row in my result set is always a calculated total for that month. So,
when using tables, I usually just do =Amount/First(Amount), and that works
like a charm. When I try to do that in a matrix, all I get in return is
"100%". So my guess is that in a matrix, I can't use First() the same way as
in a table. But how can I do the division? How to divide "Any Row" by "First
Row"?
All help appreciated.
Kaisa M. LindahlYou have to provide the "right" scope for the aggregate function. Try one of
the two following expressions to divide by the first row:
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> I'm creating my first matrix based report, and find that some things that
> work with tables doesn't work the same way with matrix.
> My resultset comes from an MDX statement. It looks something like this
> ..............................Registered hours
..........Amount
>
.............Total.....................10...................
> 1000
> Month1....Group 1..................6.....................600
> .............Group
> 2..................4.....................400
>
.............Total.....................12...................
> 1200
> Month2....Group 1..................8.....................800
> .............Group
> 2..................4.....................400
> The customer wants to display the values for the months next to each
other,
> for each value. Now, I've managed to get this with a matrix, having the
> [static group] ordered on top. (Thanks to Ohjoo Kwon).
> BUT one of my columns is supposed to be "Group Result as % of total
result."
> Meaning for Group1 in Month1 I need to divide 600 on the Total Amount. The
> first row in my result set is always a calculated total for that month.
So,
> when using tables, I usually just do =Amount/First(Amount), and that works
> like a charm. When I try to do that in a matrix, all I get in return is
> "100%". So my guess is that in a matrix, I can't use First() the same way
as
> in a table. But how can I do the division? How to divide "Any Row" by
"First
> Row"?
> All help appreciated.
> Kaisa M. Lindahl
>|||Thanks.
It worked with
=Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
Kaisa
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> You have to provide the "right" scope for the aggregate function. Try one
of
> the two following expressions to divide by the first row:
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > I'm creating my first matrix based report, and find that some things
that
> > work with tables doesn't work the same way with matrix.
> > My resultset comes from an MDX statement. It looks something like this
> >
> > ..............................Registered hours
> ..........Amount
> >
>
.............Total.....................10...................
> > 1000
> > Month1....Group
1..................6.....................600
> > .............Group
> > 2..................4.....................400
> >
> >
>
.............Total.....................12...................
> > 1200
> > Month2....Group
1..................8.....................800
> > .............Group
> > 2..................4.....................400
> >
> > The customer wants to display the values for the months next to each
> other,
> > for each value. Now, I've managed to get this with a matrix, having the
> > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> >
> > BUT one of my columns is supposed to be "Group Result as % of total
> result."
> > Meaning for Group1 in Month1 I need to divide 600 on the Total Amount.
The
> > first row in my result set is always a calculated total for that month.
> So,
> > when using tables, I usually just do =Amount/First(Amount), and that
works
> > like a charm. When I try to do that in a matrix, all I get in return is
> > "100%". So my guess is that in a matrix, I can't use First() the same
way
> as
> > in a table. But how can I do the division? How to divide "Any Row" by
> "First
> > Row"?
> >
> > All help appreciated.
> >
> > Kaisa M. Lindahl
> >
> >
>|||I just realized an error in my original code. What Robert suggested (below)
works for dividing by the very first row of the result set.
Unfortunately, I need to divide the right number with the right first row...
I'll explain
My result looks like this:
.......................Measures_Amount ...
............AllGrps...100
............Grp1........30
200409...Grp2........30
............Grp3.......40
............AllGrps....120
............Grp1........40
200309...Grp2........40
............Grp3........40
My report looks someting like this
.............Measures_Amount.....%ofTotalAmt
Group........200309|200409...200309|200409
Total..............120|...100........100%|...100%
Grp1...............40|....30.........33%|....30%
Grp2...............40|....30.........33%|....30%
Grp3...............40|....40.........33%|....40%
Today I realized that my statement
=First(Fields!Measures_Amount.Value)/ First(Fields!Measures_Amount.Value,
"OmsetningHT")
doesn't work quite as planned. This will divide all rows with the very first
row, which is the total for 200409. Suddenly all results from 200309 are
divided by the total for 200409, which is wrong.
So I need a way to divide a group result by the correct total in my matrix.
The months are organized in a column group.
All help appreciated!
Kaisa M. Lindahl
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> You have to provide the "right" scope for the aggregate function. Try one
of
> the two following expressions to divide by the first row:
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
> =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > I'm creating my first matrix based report, and find that some things
that
> > work with tables doesn't work the same way with matrix.
> > My resultset comes from an MDX statement. It looks something like this
> >
> > ..............................Registered hours
> ..........Amount
> >
>
.............Total.....................10...................
> > 1000
> > Month1....Group
1..................6.....................600
> > .............Group
> > 2..................4.....................400
> >
> >
>
.............Total.....................12...................
> > 1200
> > Month2....Group
1..................8.....................800
> > .............Group
> > 2..................4.....................400
> >
> > The customer wants to display the values for the months next to each
> other,
> > for each value. Now, I've managed to get this with a matrix, having the
> > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> >
> > BUT one of my columns is supposed to be "Group Result as % of total
> result."
> > Meaning for Group1 in Month1 I need to divide 600 on the Total Amount.
The
> > first row in my result set is always a calculated total for that month.
> So,
> > when using tables, I usually just do =Amount/First(Amount), and that
works
> > like a charm. When I try to do that in a matrix, all I get in return is
> > "100%". So my guess is that in a matrix, I can't use First() the same
way
> as
> > in a table. But how can I do the division? How to divide "Any Row" by
> "First
> > Row"?
> >
> > All help appreciated.
> >
> > Kaisa M. Lindahl
> >
> >
>|||This problem just resurfaced.
Anyone want to give it some thought?
Is it possible to check for something like
if month = Parameters!FirstPeriod.Value then
'use this row
end if ?
Kaisa
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> I just realized an error in my original code. What Robert suggested
(below)
> works for dividing by the very first row of the result set.
> Unfortunately, I need to divide the right number with the right first
row...
> I'll explain
> My result looks like this:
> .......................Measures_Amount ...
> ............AllGrps...100
> ............Grp1........30
> 200409...Grp2........30
> ............Grp3.......40
> ............AllGrps....120
> ............Grp1........40
> 200309...Grp2........40
> ............Grp3........40
> My report looks someting like this
> .............Measures_Amount.....%ofTotalAmt
> Group........200309|200409...200309|200409
> Total..............120|...100........100%|...100%
> Grp1...............40|....30.........33%|....30%
> Grp2...............40|....30.........33%|....30%
> Grp3...............40|....40.........33%|....40%
> Today I realized that my statement
> =First(Fields!Measures_Amount.Value)/ First(Fields!Measures_Amount.Value,
> "OmsetningHT")
> doesn't work quite as planned. This will divide all rows with the very
first
> row, which is the total for 200409. Suddenly all results from 200309 are
> divided by the total for 200409, which is wrong.
> So I need a way to divide a group result by the correct total in my
matrix.
> The months are organized in a column group.
> All help appreciated!
> Kaisa M. Lindahl
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > You have to provide the "right" scope for the aggregate function. Try
one
> of
> > the two following expressions to divide by the first row:
> > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "DataSetName")
> > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value, "MatrixName")
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > I'm creating my first matrix based report, and find that some things
> that
> > > work with tables doesn't work the same way with matrix.
> > > My resultset comes from an MDX statement. It looks something like this
> > >
> > > ..............................Registered hours
> > ..........Amount
> > >
> >
>
.............Total.....................10...................
> > > 1000
> > > Month1....Group
> 1..................6.....................600
> > > .............Group
> > > 2..................4.....................400
> > >
> > >
> >
>
.............Total.....................12...................
> > > 1200
> > > Month2....Group
> 1..................8.....................800
> > > .............Group
> > > 2..................4.....................400
> > >
> > > The customer wants to display the values for the months next to each
> > other,
> > > for each value. Now, I've managed to get this with a matrix, having
the
> > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > >
> > > BUT one of my columns is supposed to be "Group Result as % of total
> > result."
> > > Meaning for Group1 in Month1 I need to divide 600 on the Total Amount.
> The
> > > first row in my result set is always a calculated total for that
month.
> > So,
> > > when using tables, I usually just do =Amount/First(Amount), and that
> works
> > > like a charm. When I try to do that in a matrix, all I get in return
is
> > > "100%". So my guess is that in a matrix, I can't use First() the same
> way
> > as
> > > in a table. But how can I do the division? How to divide "Any Row" by
> > "First
> > > Row"?
> > >
> > > All help appreciated.
> > >
> > > Kaisa M. Lindahl
> > >
> > >
> >
> >
>|||If you can group on year or whatever is relevant in your scenario, then
within that group, =First(Fields!Amount.Value) should refer to the
first value in that group. As Robert said, it's all about scope with
aggregate functions. By grouping the data, you are limiting the scope,
which is what you want.
Chris
Kaisa M. Lindahl wrote:
> This problem just resurfaced.
> Anyone want to give it some thought?
> Is it possible to check for something like
> if month = Parameters!FirstPeriod.Value then
> 'use this row
> end if ?
> Kaisa
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> > I just realized an error in my original code. What Robert suggested
> (below)
> > works for dividing by the very first row of the result set.
> > Unfortunately, I need to divide the right number with the right
> > first
> row...
> > I'll explain
> >
> > My result looks like this:
> > .......................Measures_Amount ...
> > ............AllGrps...100
> > ............Grp1........30
> > 200409...Grp2........30
> > ............Grp3.......40
> > ............AllGrps....120
> > ............Grp1........40
> > 200309...Grp2........40
> > ............Grp3........40
> >
> > My report looks someting like this
> >
> > .............Measures_Amount.....%ofTotalAmt
> > Group........200309|200409...200309|200409
> > Total..............120|...100........100%|...100%
> > Grp1...............40|....30.........33%|....30%
> > Grp2...............40|....30.........33%|....30%
> > Grp3...............40|....40.........33%|....40%
> >
> > Today I realized that my statement
> > =First(Fields!Measures_Amount.Value)/
> > First(Fields!Measures_Amount.Value, "OmsetningHT")
> > doesn't work quite as planned. This will divide all rows with the
> > very
> first
> > row, which is the total for 200409. Suddenly all results from
> > 200309 are divided by the total for 200409, which is wrong.
> >
> > So I need a way to divide a group result by the correct total in my
> matrix.
> > The months are organized in a column group.
> >
> > All help appreciated!
> >
> > Kaisa M. Lindahl
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> > message news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > > You have to provide the "right" scope for the aggregate function.
> > > Try
> one
> > of
> > > the two following expressions to divide by the first row:
> > > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value,
> > > "DataSetName") =Sum(Fields!Amount.Value) /
> > > First(Fields!Amount.Value, "MatrixName")
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers
> > > no
> > rights.
> > >
> > >
> > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > > I'm creating my first matrix based report, and find that some
> > > > things
> > that
> > > > work with tables doesn't work the same way with matrix.
> > > > My resultset comes from an MDX statement. It looks something
> > > > like this
> > > >
> > > > ..............................Registered hours
> > > ..........Amount
> > > >
> > >
> >
> .............Total.....................10...............
> .....
> > > > 1000
> > > > Month1....Group
> > 1..................6.....................600
> > > > .............Group
> > > > 2..................4.....................400
> > > >
> > > >
> > >
> >
> .............Total.....................12...............
> .....
> > > > 1200
> > > > Month2....Group
> > 1..................8.....................800
> > > > .............Group
> > > > 2..................4.....................400
> > > >
> > > > The customer wants to display the values for the months next to
> > > > each
> > > other,
> > > > for each value. Now, I've managed to get this with a matrix,
> > > > having
> the
> > > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > > >
> > > > BUT one of my columns is supposed to be "Group Result as % of
> > > > total
> > > result."
> > > > Meaning for Group1 in Month1 I need to divide 600 on the Total
> > > > Amount.
> > The
> > > > first row in my result set is always a calculated total for that
> month.
> > > So,
> > > > when using tables, I usually just do =Amount/First(Amount), and
> > > > that
> > works
> > > > like a charm. When I try to do that in a matrix, all I get in
> > > > return
> is
> > > > "100%". So my guess is that in a matrix, I can't use First()
> > > > the same
> > way
> > > as
> > > > in a table. But how can I do the division? How to divide "Any
> > > > Row" by
> > > "First
> > > > Row"?
> > > >
> > > > All help appreciated.
> > > >
> > > > Kaisa M. Lindahl
> > > >
> > > >
> > >
> > >
> >
> >|||OK, thanks to both. I finally figured it out. :)
I'm still getting confused by when to use what scope. The final solution was
to change from
First(Fields!Amount.Value, "Dataset") to First(Fields!Amount.Value,
"MatrixColumnGroupName").
Kaisa M. Lindahl
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uO5VPAzIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> If you can group on year or whatever is relevant in your scenario, then
> within that group, =First(Fields!Amount.Value) should refer to the
> first value in that group. As Robert said, it's all about scope with
> aggregate functions. By grouping the data, you are limiting the scope,
> which is what you want.
> Chris
>
> Kaisa M. Lindahl wrote:
> > This problem just resurfaced.
> > Anyone want to give it some thought?
> >
> > Is it possible to check for something like
> > if month = Parameters!FirstPeriod.Value then
> > 'use this row
> > end if ?
> >
> > Kaisa
> >
> > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> > > I just realized an error in my original code. What Robert suggested
> > (below)
> > > works for dividing by the very first row of the result set.
> > > Unfortunately, I need to divide the right number with the right
> > > first
> > row...
> > > I'll explain
> > >
> > > My result looks like this:
> > > .......................Measures_Amount ...
> > > ............AllGrps...100
> > > ............Grp1........30
> > > 200409...Grp2........30
> > > ............Grp3.......40
> > > ............AllGrps....120
> > > ............Grp1........40
> > > 200309...Grp2........40
> > > ............Grp3........40
> > >
> > > My report looks someting like this
> > >
> > > .............Measures_Amount.....%ofTotalAmt
> > > Group........200309|200409...200309|200409
> > > Total..............120|...100........100%|...100%
> > > Grp1...............40|....30.........33%|....30%
> > > Grp2...............40|....30.........33%|....30%
> > > Grp3...............40|....40.........33%|....40%
> > >
> > > Today I realized that my statement
> > > =First(Fields!Measures_Amount.Value)/
> > > First(Fields!Measures_Amount.Value, "OmsetningHT")
> > > doesn't work quite as planned. This will divide all rows with the
> > > very
> > first
> > > row, which is the total for 200409. Suddenly all results from
> > > 200309 are divided by the total for 200409, which is wrong.
> > >
> > > So I need a way to divide a group result by the correct total in my
> > matrix.
> > > The months are organized in a column group.
> > >
> > > All help appreciated!
> > >
> > > Kaisa M. Lindahl
> > >
> > >
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> > > message news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > > > You have to provide the "right" scope for the aggregate function.
> > > > Try
> > one
> > > of
> > > > the two following expressions to divide by the first row:
> > > > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value,
> > > > "DataSetName") =Sum(Fields!Amount.Value) /
> > > > First(Fields!Amount.Value, "MatrixName")
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers
> > > > no
> > > rights.
> > > >
> > > >
> > > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > > > I'm creating my first matrix based report, and find that some
> > > > > things
> > > that
> > > > > work with tables doesn't work the same way with matrix.
> > > > > My resultset comes from an MDX statement. It looks something
> > > > > like this
> > > > >
> > > > > ..............................Registered hours
> > > > ..........Amount
> > > > >
> > > >
> > >
> > .............Total.....................10...............
> > .....
> > > > > 1000
> > > > > Month1....Group
> > > 1..................6.....................600
> > > > > .............Group
> > > > > 2..................4.....................400
> > > > >
> > > > >
> > > >
> > >
> > .............Total.....................12...............
> > .....
> > > > > 1200
> > > > > Month2....Group
> > > 1..................8.....................800
> > > > > .............Group
> > > > > 2..................4.....................400
> > > > >
> > > > > The customer wants to display the values for the months next to
> > > > > each
> > > > other,
> > > > > for each value. Now, I've managed to get this with a matrix,
> > > > > having
> > the
> > > > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > > > >
> > > > > BUT one of my columns is supposed to be "Group Result as % of
> > > > > total
> > > > result."
> > > > > Meaning for Group1 in Month1 I need to divide 600 on the Total
> > > > > Amount.
> > > The
> > > > > first row in my result set is always a calculated total for that
> > month.
> > > > So,
> > > > > when using tables, I usually just do =Amount/First(Amount), and
> > > > > that
> > > works
> > > > > like a charm. When I try to do that in a matrix, all I get in
> > > > > return
> > is
> > > > > "100%". So my guess is that in a matrix, I can't use First()
> > > > > the same
> > > way
> > > > as
> > > > > in a table. But how can I do the division? How to divide "Any
> > > > > Row" by
> > > > "First
> > > > > Row"?
> > > > >
> > > > > All help appreciated.
> > > > >
> > > > > Kaisa M. Lindahl
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
>|||Kaisa, in general, leaving the scope out lets RS decide for you.
Generally this will work the way you want.
Chris
Kaisa M. Lindahl wrote:
> OK, thanks to both. I finally figured it out. :)
> I'm still getting confused by when to use what scope. The final
> solution was to change from
> First(Fields!Amount.Value, "Dataset") to First(Fields!Amount.Value,
> "MatrixColumnGroupName").
> Kaisa M. Lindahl
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:uO5VPAzIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> > If you can group on year or whatever is relevant in your scenario,
> > then within that group, =First(Fields!Amount.Value) should refer to
> > the first value in that group. As Robert said, it's all about scope
> > with aggregate functions. By grouping the data, you are limiting
> > the scope, which is what you want.
> >
> > Chris
> >
> >
> >
> > Kaisa M. Lindahl wrote:
> >
> > > This problem just resurfaced.
> > > Anyone want to give it some thought?
> > >
> > > Is it possible to check for something like
> > > if month = Parameters!FirstPeriod.Value then
> > > 'use this row
> > > end if ?
> > >
> > > Kaisa
> > >
> > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > news:OACV70qDFHA.1296@.TK2MSFTNGP10.phx.gbl...
> > > > I just realized an error in my original code. What Robert
> > > > suggested
> > > (below)
> > > > works for dividing by the very first row of the result set.
> > > > Unfortunately, I need to divide the right number with the right
> > > > first
> > > row...
> > > > I'll explain
> > > >
> > > > My result looks like this:
> > > > .......................Measures_Amount ...
> > > > ............AllGrps...100
> > > > ............Grp1........30
> > > > 200409...Grp2........30
> > > > ............Grp3.......40
> > > > ............AllGrps....120
> > > > ............Grp1........40
> > > > 200309...Grp2........40
> > > > ............Grp3........40
> > > >
> > > > My report looks someting like this
> > > >
> > > > .............Measures_Amount.....%ofTotalAmt
> > > > Group........200309|200409...200309|200409
> > > > Total..............120|...100........100%|...100%
> > > > Grp1...............40|....30.........33%|....30%
> > > > Grp2...............40|....30.........33%|....30%
> > > > Grp3...............40|....40.........33%|....40%
> > > >
> > > > Today I realized that my statement
> > > > =First(Fields!Measures_Amount.Value)/
> > > > First(Fields!Measures_Amount.Value, "OmsetningHT")
> > > > doesn't work quite as planned. This will divide all rows with
> > > > the very
> > > first
> > > > row, which is the total for 200409. Suddenly all results from
> > > > 200309 are divided by the total for 200409, which is wrong.
> > > >
> > > > So I need a way to divide a group result by the correct total
> > > > in my
> > > matrix.
> > > > The months are organized in a column group.
> > > >
> > > > All help appreciated!
> > > >
> > > > Kaisa M. Lindahl
> > > >
> > > >
> > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> > > > message news:etV2GOOCFHA.1392@.tk2msftngp13.phx.gbl...
> > > > > You have to provide the "right" scope for the aggregate
> > > > > function. Try
> > > one
> > > > of
> > > > > the two following expressions to divide by the first row:
> > > > > =Sum(Fields!Amount.Value) / First(Fields!Amount.Value,
> > > > > "DataSetName") =Sum(Fields!Amount.Value) /
> > > > > First(Fields!Amount.Value, "MatrixName")
> > > > >
> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and
> > > > > confers no
> > > > rights.
> > > > >
> > > > >
> > > > > "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> > > > > news:enVnqz6BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> > > > > > I'm creating my first matrix based report, and find that
> > > > > > some things
> > > > that
> > > > > > work with tables doesn't work the same way with matrix.
> > > > > > My resultset comes from an MDX statement. It looks something
> > > > > > like this
> > > > > >
> > > > > > ..............................Registered hours
> > > > > ..........Amount
> > > > > >
> > > > >
> > > >
> > > .............Total.....................10............
> > > ... .....
> > > > > > 1000
> > > > > > Month1....Group
> > > > 1..................6.....................600
> > > > > > .............Group
> > > > > > 2..................4.....................400
> > > > > >
> > > > > >
> > > > >
> > > >
> > > .............Total.....................12............
> > > ... .....
> > > > > > 1200
> > > > > > Month2....Group
> > > > 1..................8.....................800
> > > > > > .............Group
> > > > > > 2..................4.....................400
> > > > > >
> > > > > > The customer wants to display the values for the months
> > > > > > next to each
> > > > > other,
> > > > > > for each value. Now, I've managed to get this with a matrix,
> > > > > > having
> > > the
> > > > > > [static group] ordered on top. (Thanks to Ohjoo Kwon).
> > > > > >
> > > > > > BUT one of my columns is supposed to be "Group Result as %
> > > > > > of total
> > > > > result."
> > > > > > Meaning for Group1 in Month1 I need to divide 600 on the
> > > > > > Total Amount.
> > > > The
> > > > > > first row in my result set is always a calculated total for
> > > > > > that
> > > month.
> > > > > So,
> > > > > > when using tables, I usually just do =Amount/First(Amount),
> > > > > > and that
> > > > works
> > > > > > like a charm. When I try to do that in a matrix, all I get
> > > > > > in return
> > > is
> > > > > > "100%". So my guess is that in a matrix, I can't use First()
> > > > > > the same
> > > > way
> > > > > as
> > > > > > in a table. But how can I do the division? How to divide
> > > > > > "Any Row" by
> > > > > "First
> > > > > > Row"?
> > > > > >
> > > > > > All help appreciated.
> > > > > >
> > > > > > Kaisa M. Lindahl
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> >

Ditinct Rows Within Grouped Dataset

Hi
Here's one that is puzzling me!!
We have 2 tables, a category table and a sub category table, what we
need is to display a web control that shows unique entries within the
recordset so if the recorset returns the follow
Accomodation Hotels
Accomodation B&B
Accomodation Self Catering
Restaurants American
Restaurants Indian
we would then see
Accomodation Hotels
B&B
Self Catering
Restaurants American
Indian
To create the data we are using a stored procedure so woncered if this
type of array could be returned direct from that? Sort of DIsTINCT
within the column. Or would we have to do it in code into an array?
We would then have a web control that allows each of the trees to be
collapsed / expanded
Cheers
shaunshaunsizen@.msn.com wrote:
> Hi
> Here's one that is puzzling me!!
> We have 2 tables, a category table and a sub category table, what we
> need is to display a web control that shows unique entries within the
> recordset so if the recorset returns the follow
> Accomodation Hotels
> Accomodation B&B
> Accomodation Self Catering
> Restaurants American
> Restaurants Indian
> we would then see
> Accomodation Hotels
> B&B
> Self Catering
> Restaurants American
> Indian
> To create the data we are using a stored procedure so woncered if this
> type of array could be returned direct from that? Sort of DIsTINCT
> within the column. Or would we have to do it in code into an array?
> We would then have a web control that allows each of the trees to be
> collapsed / expanded
> Cheers
> shaun
If you have a control driving the results, then wouldn't you want the
results to include the first column in all cases, so the code knows what
tree to place the second column? If you key off the first column, then
the order of the results is irrelevant and you can remove any ORDER BY
clauses in the SQL, saving additional server resources.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 4 Oct 2005 06:59:13 -0700, shaunsizen@.msn.com wrote:
>Hi
>Here's one that is puzzling me!!
>We have 2 tables, a category table and a sub category table, what we
>need is to display a web control that shows unique entries within the
>recordset so if the recorset returns the follow
>Accomodation Hotels
>Accomodation B&B
>Accomodation Self Catering
>Restaurants American
>Restaurants Indian
>we would then see
>Accomodation Hotels
> B&B
> Self Catering
>Restaurants American
> Indian
Hi shaun,
Typically, presentation issues should be handled at the front end (it's
not called "presentation tier" for nothing <g>). Use a query such as the
one below to return the data. Then use the frontend (where you'll have
to loop through the rows anyway) to blank out the first column if the
value is unchanged from the previous row.
SELECT FirstCol, SecondCol
FROM YourTable
ORDER BY FirstCol
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Distrubuted Transactions with AS400

Hi All
I have created stored procs that update SQL and AS400 (Linked server)
tables. I want to wrap both updates in a transaction, however when I do, I
get the error
"The operation could not be performed because OLE DB provider "MSDASQL" for
linked server "LinkedServerName" was unable to begin a distributed
transaction".
We are using Client Access ODBC driver (V5R2, SI06631).
All of the documents that I have read talk about MTS and MS DTC.
MS DTC is running on the SQL Server.
Is there any way of using Distributed Transaction processing without
requiring MTS ?
Thanks in advance.not enough info on the error.
here:
http://support.microsoft.com/kb/306212
you may want to look at the full error when you talk to DB2. like so
DBCC TRACEON (3604, 7300)
then run your sp and seewhat it does.
did you try just issue
BEGIN DISTRIBUTED TRAN
and see if the 2 data sources you are working with can be accessed?
and you the same authentication your application is going to call your sp
with.
Thanks, Liliya
"Jane" wrote:
> Hi All
> I have created stored procs that update SQL and AS400 (Linked server)
> tables. I want to wrap both updates in a transaction, however when I do, I
> get the error
> "The operation could not be performed because OLE DB provider "MSDASQL" for
> linked server "LinkedServerName" was unable to begin a distributed
> transaction".
> We are using Client Access ODBC driver (V5R2, SI06631).
> All of the documents that I have read talk about MTS and MS DTC.
> MS DTC is running on the SQL Server.
> Is there any way of using Distributed Transaction processing without
> requiring MTS ?
> Thanks in advance.|||Thanks for the response.
Yes - used the same auth as the app will use (windows auth)
Yes - just used BEGIN DISTRIBUTED TRAN
Used DBCC TRACEON (3604, 7300) & SET XACT_ABORT ON as per the link in a
Query Window then ran the updates again.
Ran DBCC TRACESTATUS (3604,7300) to check that the flags for the session
were set.
Still failing & still no extra error information.
Under "Configuration Issues" the link states ;
"Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers
that are involved in the distributed transaction".
I am not sure what needs to running on the AS400 for this to work.
"l" wrote:
> not enough info on the error.
> here:
> http://support.microsoft.com/kb/306212
> you may want to look at the full error when you talk to DB2. like so
> DBCC TRACEON (3604, 7300)
> then run your sp and seewhat it does.
> did you try just issue
> BEGIN DISTRIBUTED TRAN
> and see if the 2 data sources you are working with can be accessed?
> and you the same authentication your application is going to call your sp
> with.
> Thanks, Liliya
>
> "Jane" wrote:
> > Hi All
> >
> > I have created stored procs that update SQL and AS400 (Linked server)
> > tables. I want to wrap both updates in a transaction, however when I do, I
> > get the error
> > "The operation could not be performed because OLE DB provider "MSDASQL" for
> > linked server "LinkedServerName" was unable to begin a distributed
> > transaction".
> > We are using Client Access ODBC driver (V5R2, SI06631).
> > All of the documents that I have read talk about MTS and MS DTC.
> > MS DTC is running on the SQL Server.
> >
> > Is there any way of using Distributed Transaction processing without
> > requiring MTS ?
> >
> > Thanks in advance.|||OLEDB Errors in SQL Profiler shows ;
<hresult>-2147168246</hresult>
<inputs>
<punkTransactionCoord>0x624A0060</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x00000000</pOtherOptions>
</inputs>|||> I am not sure what needs to running on the AS400 for this to work.
depend on what are you running on AS400. DB2 version. Here. how to set up
mts (0you have mentioned this one earlier in this thread)
http://www-03.ibm.com/servers/enable/site/db2/mts/mts.pdf
as about ms sql server side, it does not hurt to test if your sql side is
ok. Easy enough as long as you have couple ms sql's on the same domain etc.
if not, can install one on your ws, configure it and see if you can issue
distributed transactions. After all, it will give you a 'lab rat' s well to
experiment if you in need of one.
Thanks, Liliya

distributor agent

Win2003srv
mssql2000
mssql2005
Oracle
I have a mssql2000 database that I Replicate to oracle,
some tables have row filtering.
This was working fine until I create an another publication and subscription
(transaction)
on the same database, for mssql2005 This subscription has the
loopback_detection = N'True',
After this, the Oracle subscription stop to work correctly
Tables with row filter are not replicated. In Repl Monitor everything looks
fine
I try to delete all publication and then re-crate it and start snap-shot for
oracle
The bulk copying are doing the right thing regarding filter-rows.
But the distributor agent will not work properly
I can see the log-reader pushing transaction to the distibutor , but nothing
happens
What is wrong her?
-roger
What do you see if you run a sp_browsereplcmds in the distribution database?
The oracle DML should be showing up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roger Nygrd" <roger@.askit.no> wrote in message
news:11uemak27qnmp5d@.corp.supernews.com...
> Win2003srv
> mssql2000
> mssql2005
> Oracle
> I have a mssql2000 database that I Replicate to oracle,
> some tables have row filtering.
> This was working fine until I create an another publication and
> subscription (transaction)
> on the same database, for mssql2005 This subscription has the
> loopback_detection = N'True',
> After this, the Oracle subscription stop to work correctly
> Tables with row filter are not replicated. In Repl Monitor everything
> looks fine
> I try to delete all publication and then re-crate it and start snap-shot
> for oracle
> The bulk copying are doing the right thing regarding filter-rows.
> But the distributor agent will not work properly
> I can see the log-reader pushing transaction to the distibutor , but
> nothing happens
> What is wrong her?
>
> -roger
>
|||It is empty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23w5ntqyKGHA.2036@.TK2MSFTNGP14.phx.gbl...
> What do you see if you run a sp_browsereplcmds in the distribution
> database? The oracle DML should be showing up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roger Nygrd" <roger@.askit.no> wrote in message
> news:11uemak27qnmp5d@.corp.supernews.com...
>

Distribution Database Table Creation Date?

Hi,
In my distribution data base, the system tables creation date is showing Dec
2002, but i replicated the database on feb 2006,
I want to know why the table creation date is in year 2002.
Please clarify me.
rgds,
Soura
Soura,
this is the date that the distribution database was created using
sp_adddistributiondb.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

distribution agent bcp error

I am trying to use immediate updating transactional replication. A few of the tables have text fields. I want to filter out these fields, but when the subscriber reinitializes I get a bcp error - unexpected EOF. When I switch the publisher to use chara
cter format (rather than native) I get an unknown character error. The columns are marked default null. If I do not filter the columns, the initialization works fine. Is there anything I am missing?
Any help is appreciated.
Sql Server 2000 Enterprise sp3
Debra,
I haven't seen this before and don't know of a direct solution. Some posts
have mentioned that the column order is relevant. There is a known bug
(http://support.microsoft.com/?id=271344) which reports the same error - in
this case you'd need to manually apply the snapshot. Please can you post up
the schema of the problematic table and an explanation of the filter you're
using.
TIA,
Paul Ibison
|||I am begining to believe the problem is specific to that server. I copied the databases to to my local machine and the replication with filters worked fine. But, I have to say I have not installed the application locally. I really do not believe the pr
oblem is the application, though.
On the servers, I am replicating the databases behind Great Plains - 2 separate boxes with named instances. I have handled all the identity fields with not for replication and ranges. In the tables that are giving me headaches there are between 10 and 3
0 columns. There are some indexes and PK, but not on the columns I am trying to filter. I tried moving the columns (I had also read that column position might be to blame), with no luck. For a work around, I changed the columns to varchar and left th
em in the replication. When I move to production, I will attempt to leave the fields as text and use the filter. I'll be sure to post if it works or not. I did not set up the servers I am using, but I can not think of why they would not handle the filt
ers!
Thanks for confirming that this was not a known error and possibly is restricted to my enviornment.
"Paul Ibison" wrote:

> Debra,
> I haven't seen this before and don't know of a direct solution. Some posts
> have mentioned that the column order is relevant. There is a known bug
> (http://support.microsoft.com/?id=271344) which reports the same error - in
> this case you'd need to manually apply the snapshot. Please can you post up
> the schema of the problematic table and an explanation of the filter you're
> using.
> TIA,
> Paul Ibison
>
>
|||I moved the databases to a different server and recreated the subsciption and publication. When replicating just that one table, the bcp works with no error. When I add all the other tables (175), the bcp fails if there are any filters on the tables th
at have many columns. I did not test filtering the tables that have fewer columns.
I worked around the issue by doing as you said and manually applying the snapshot. Luckily this is a one time deal and there will only be one subscriber.
Thanks for your help!
"Paul Ibison" wrote:

> Debra,
> I haven't seen this before and don't know of a direct solution. Some posts
> have mentioned that the column order is relevant. There is a known bug
> (http://support.microsoft.com/?id=271344) which reports the same error - in
> this case you'd need to manually apply the snapshot. Please can you post up
> the schema of the problematic table and an explanation of the filter you're
> using.
> TIA,
> Paul Ibison
>
>