Entities Disappear in Ad Hoc Reports

I have a single data source view which contains 3 tables. Each table is a
completely denormalized (like a spreadsheet) dataset from a particular
business system. The tables themselves don't contain any foreign key
relationships, but within the data source view editor I created primary keys
on each table and created relationships between each table. However, when I
try to create a report in report builder, as soon as add an entity from one
table to my report, the other 2 tables disappear. I have found a few other
posts about this problem and the resolutions offered were (1) add a "refer",
but I don't know what that is; and (2) create or add relationships, but I
believe I have done that. What do I have to do to prevent the tables from
disappearing? Thanks.
Also, is there a good resource for reading up on Ad-Hoc reporting? The
books I have seen only cover it briefly.I figured this out. I need to add a role to each table in the Report Model
Designer. It is all in Books Online.
"Bodhicitta" wrote:
> I have a single data source view which contains 3 tables. Each table is a
> completely denormalized (like a spreadsheet) dataset from a particular
> business system. The tables themselves don't contain any foreign key
> relationships, but within the data source view editor I created primary keys
> on each table and created relationships between each table. However, when I
> try to create a report in report builder, as soon as add an entity from one
> table to my report, the other 2 tables disappear. I have found a few other
> posts about this problem and the resolutions offered were (1) add a "refer",
> but I don't know what that is; and (2) create or add relationships, but I
> believe I have done that. What do I have to do to prevent the tables from
> disappearing? Thanks.
> Also, is there a good resource for reading up on Ad-Hoc reporting? The
> books I have seen only cover it briefly.
>

Enterprise Manager question

When I click on a server node in EM, I am prompted to
enter the sa password in hte usual dialog box. However for
one particular server I get this dialog box twice - why is
this different to the others?
TIA,
BBSounds to me like something introduced by installing a security hotfix. Chan you check if that
differ on the server or client level?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:01d601c4a1a5$83884c40$a501280a@.phx.gbl...
> When I click on a server node in EM, I am prompted to
> enter the sa password in hte usual dialog box. However for
> one particular server I get this dialog box twice - why is
> this different to the others?
> TIA,
> BB

Enterprise Manager question

When I click on a server node in EM, I am prompted to
enter the sa password in hte usual dialog box. However for
one particular server I get this dialog box twice - why is
this different to the others?
TIA,
BB
Sounds to me like something introduced by installing a security hotfix. Chan you check if that
differ on the server or client level?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:01d601c4a1a5$83884c40$a501280a@.phx.gbl...
> When I click on a server node in EM, I am prompted to
> enter the sa password in hte usual dialog box. However for
> one particular server I get this dialog box twice - why is
> this different to the others?
> TIA,
> BB

Enterprise Manager Icon

Hi,
I have just started using SQL server and am getting on fine with it,
however I cannot find any infomation on a particular Enterprise
Manager icon. Basically we have a number of databases, but I noticed
that the default Northwind database has a "small hand" - like a
Windows network share icon - displayed with it; what does this icon
mean? How can it be removed as none of the other databases display
this icon and are accessible in a multi-user environment?
Regards
The icon means that the database is enabled for replication. The
replication icons are documented in the SQL 2000 Bools Online
<replsql.chm::/repltools_8o6r.htm>.
If you have no replication publications or subscriptions for the Northwind
database, you can remove the 'publish' database option with
sp_replicationdboption:
EXEC sp_replicationdboption 'Northwind', 'publish', false
Hope this helps.
Dan Guzman
SQL Server MVP
"Zane" <zane_cahill@.hotmail.com> wrote in message
news:f27a37b7.0404041707.75c51f8@.posting.google.co m...
> Hi,
> I have just started using SQL server and am getting on fine with it,
> however I cannot find any infomation on a particular Enterprise
> Manager icon. Basically we have a number of databases, but I noticed
> that the default Northwind database has a "small hand" - like a
> Windows network share icon - displayed with it; what does this icon
> mean? How can it be removed as none of the other databases display
> this icon and are accessible in a multi-user environment?
> Regards

Enterprise Manager Icon

Hi,
I have just started using SQL server and am getting on fine with it,
however I cannot find any infomation on a particular Enterprise
Manager icon. Basically we have a number of databases, but I noticed
that the default Northwind database has a "small hand" - like a
Windows network share icon - displayed with it; what does this icon
mean? How can it be removed as none of the other databases display
this icon and are accessible in a multi-user environment?
RegardsThe icon means that the database is enabled for replication. The
replication icons are documented in the SQL 2000 Bools Online
<replsql.chm::/repltools_8o6r.htm>.
If you have no replication publications or subscriptions for the Northwind
database, you can remove the 'publish' database option with
sp_replicationdboption:
EXEC sp_replicationdboption 'Northwind', 'publish', false
Hope this helps.
Dan Guzman
SQL Server MVP
"Zane" <zane_cahill@.hotmail.com> wrote in message
news:f27a37b7.0404041707.75c51f8@.posting.google.com...
> Hi,
> I have just started using SQL server and am getting on fine with it,
> however I cannot find any infomation on a particular Enterprise
> Manager icon. Basically we have a number of databases, but I noticed
> that the default Northwind database has a "small hand" - like a
> Windows network share icon - displayed with it; what does this icon
> mean? How can it be removed as none of the other databases display
> this icon and are accessible in a multi-user environment?
> Regards

Enterprise Manager Corrupted?

We create a simple maintenance plan with no error. When we go back to edit the settings, many have defaulted.
In particular, the backup definition loses the definition of where to backup files to on the disk is cleared, and the Device option is checked.
Further, in delete files older than, the selector will not populate (although we can manually enter a number), and the drop-down that should show "hours", "Days" etc, is completely empty, and we cannot enter anything.
Suggestions?
Have your client tools been updated with SP3?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"jnorman" <anonymous@.discussions.microsoft.com> wrote in message
news:ADE1F886-6641-4741-ABD4-BFED23FD7AED@.microsoft.com...
> We create a simple maintenance plan with no error. When we go back to edit
> the settings, many have defaulted.
> In particular, the backup definition loses the definition of where to
> backup files to on the disk is cleared, and the Device option is checked.
> Further, in delete files older than, the selector will not populate
> (although we can manually enter a number), and the drop-down that should
> show "hours", "Days" etc, is completely empty, and we cannot enter
> anything.
> Suggestions?
>
|||Good idea. In fact it turns out that, despite assurances
to the contrary, our new client had NOT upgraded to sp3.
So we installed it.
Unfortunately, We still have the same problem. We can
still save an original maintenance plan, although the drop-
down list for the number of time units to delete after is
still blank (it apparently defaults to "weeks," though).
The original save is error-free, but to save any mods
generates this error:
error 8114: error converting data type int to tinyint
We still have no way to simply modify a maintenance plan.
More suggestions?
>--Original Message--
>Have your client tools been updated with SP3?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>"jnorman" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ADE1F886-6641-4741-ABD4-BFED23FD7AED@.microsoft.com...
we go back to edit
definition of where to
option is checked.
not populate
down that should
cannot enter
>
>.
>
|||What is the database compatibility? What hotfixes have been applied? Have
you restored master or msdb from another place or another version?
I'm just trying to think of things which would make a system-used datatype
change, and usually these are restricted to service pack/version releases.
A
<anonymous@.discussions.microsoft.com> wrote in message
news:153c901c41a8d$122349f0$a601280a@.phx.gbl...
> Good idea. In fact it turns out that, despite assurances
> to the contrary, our new client had NOT upgraded to sp3.
> So we installed it.
> Unfortunately, We still have the same problem. We can
> still save an original maintenance plan, although the drop-
> down list for the number of time units to delete after is
> still blank (it apparently defaults to "weeks," though).
> The original save is error-free, but to save any mods
> generates this error:
> error 8114: error converting data type int to tinyint
> We still have no way to simply modify a maintenance plan.
> More suggestions?
> message
> we go back to edit
> definition of where to
> option is checked.
> not populate
> down that should
> cannot enter

Enterprise Manager Corrupted?

We create a simple maintenance plan with no error. When we go back to edit t
he settings, many have defaulted.
In particular, the backup definition loses the definition of where to backup
files to on the disk is cleared, and the Device option is checked.
Further, in delete files older than, the selector will not populate (althoug
h we can manually enter a number), and the drop-down that should show "hours
", "Days" etc, is completely empty, and we cannot enter anything.
Suggestions?Have your client tools been updated with SP3?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"jnorman" <anonymous@.discussions.microsoft.com> wrote in message
news:ADE1F886-6641-4741-ABD4-BFED23FD7AED@.microsoft.com...
> We create a simple maintenance plan with no error. When we go back to edit
> the settings, many have defaulted.
> In particular, the backup definition loses the definition of where to
> backup files to on the disk is cleared, and the Device option is checked.
> Further, in delete files older than, the selector will not populate
> (although we can manually enter a number), and the drop-down that should
> show "hours", "Days" etc, is completely empty, and we cannot enter
> anything.
> Suggestions?
>|||Good idea. In fact it turns out that, despite assurances
to the contrary, our new client had NOT upgraded to sp3.
So we installed it.
Unfortunately, We still have the same problem. We can
still save an original maintenance plan, although the drop-
down list for the number of time units to delete after is
still blank (it apparently defaults to "weeks," though).
The original save is error-free, but to save any mods
generates this error:
error 8114: error converting data type int to tinyint
We still have no way to simply modify a maintenance plan.
More suggestions?
>--Original Message--
>Have your client tools been updated with SP3?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>"jnorman" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ADE1F886-6641-4741-ABD4-BFED23FD7AED@.microsoft.com...
we go back to edit
definition of where to
option is checked.
not populate
down that should
cannot enter
>
>.
>|||What is the database compatibility? What hotfixes have been applied? Have
you restored master or msdb from another place or another version?
I'm just trying to think of things which would make a system-used datatype
change, and usually these are restricted to service pack/version releases.
A
<anonymous@.discussions.microsoft.com> wrote in message
news:153c901c41a8d$122349f0$a601280a@.phx
.gbl...
> Good idea. In fact it turns out that, despite assurances
> to the contrary, our new client had NOT upgraded to sp3.
> So we installed it.
> Unfortunately, We still have the same problem. We can
> still save an original maintenance plan, although the drop-
> down list for the number of time units to delete after is
> still blank (it apparently defaults to "weeks," though).
> The original save is error-free, but to save any mods
> generates this error:
> error 8114: error converting data type int to tinyint
> We still have no way to simply modify a maintenance plan.
> More suggestions?
> message
> we go back to edit
> definition of where to
> option is checked.
> not populate
> down that should
> cannot enter|||The SQL Server 2000 instance was originally installed (by another
consultant) last summer. I doubt that any hot fixes were applied before we
came onto the scene in December, since, as you saw, they never got around to
applying SP3.
The master/msdb were installed fresh, I'm sure (the other vendor wasn't
sophisticated enough to know how to do any of that.
Any other thoughts? I'm sort of getting the idea that we probably have no
choice but to rip it out and reinstall?
J
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:OHyiE7oGEHA.2052@.TK2MSFTNGP11.phx.gbl...
> What is the database compatibility? What hotfixes have been applied?
Have
> you restored master or msdb from another place or another version?
> I'm just trying to think of things which would make a system-used datatype
> change, and usually these are restricted to service pack/version releases.
> A
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:153c901c41a8d$122349f0$a601280a@.phx
.gbl...
>

Enterpirise Manager Help

I need to know what tables in a particular database have been updated. The
Enterpirise manager only displays the creation date and not when the table
was updated. If their any way of finding this out?..
Thanks
OK, I've just read that you cant do that so the problem I have is this;
I have a DB with around 80 tables which only 20-30 are used on a daily
basis. I created a snapshot of all tables so thats not a problem.
What I want to know is when my subscriber has new data (merge) will it
automaticly push that data (table) to my distributor/publisher or does it
have to be triggered.
I'm new to this so please be patient.
Many thanks
""confused"" wrote:

> I need to know what tables in a particular database have been updated. The
> Enterpirise manager only displays the creation date and not when the table
> was updated. If their any way of finding this out?..
>
> Thanks
|||When the subscriber changes the data at the subscriber, it gets recorded in
metadata tables. These tables are read by the merge agent which then applies
the changes to the publisher. IE the data only flows upstream when the merge
agent runs. If this is continuously, this'll be usually within a minute;
alternatively it might be set to run on a schedule - you have to look at the
job to see how it is set.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply.
Another question I have is why do I have hundreds of 'tsvw_xxxxx' and
'ctsv_xxx' views. Also 'sp_selxxxxx', 'sp_updxxxxx' and 'sp_insxxxx' in the
stored procedure area??. What purpose are these files or do I have to
delete them manually.
Many thanks
"Paul Ibison" wrote:

> When the subscriber changes the data at the subscriber, it gets recorded in
> metadata tables. These tables are read by the merge agent which then applies
> the changes to the publisher. IE the data only flows upstream when the merge
> agent runs. If this is continuously, this'll be usually within a minute;
> alternatively it might be set to run on a schedule - you have to look at the
> job to see how it is set.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The stored procedures 'sp_selxxxxx', 'sp_updxxxxx' and 'sp_insxxxx' etc are
all used by the distribution agent in transactional replication. When an
update occurs on the publisher, this is replicated (by default) as a call to
execute the 'sp_updxxxxx' procedure on the subscriber. The views " tsvw_"
are used internally by the merge engine. When I'm removing replication
completely from a database and some procedures/views remain afterwards, I
delete them manually. Otherwise I'd recommend leaving them in place or
you'll have to examine each one carefully so as to not break the existing
setup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Entering Unique data into a column

Is there a better to insure an INSERT will only add a new row only if the
data being added to a particular column is unique, besides doing a SELECT
before doing the INSERT?
SELECT X from table where X='NewData'
ExecuteReader and test for number of Rows
if Zero then do the INSERT
Thank you...
BruceINSERT INTO YourTable (x, ...)
SELECT 'NewData', ...
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE x = 'NewData')
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Thanks.....
Bruce|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Okay, What am I doing wrong? The following didn't work.. The INSERT happened
even though the value of 'Dentist' was in the table in the columne
AddressType.
INSERT INTO AddressType(AddressType) Values ('Dentist')
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
Now, my table, AddressType, had only two columns, PrimaryKey (with
autoincrement) and AddressType.
Thanks...
Bruce|||You added the VALUES clause, which means it's no longer an INSERT... SELECT.
Try:
INSERT INTO AddressType (AddressType)
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
BTW I assume AddressType is declared unique. Always declare unique columns,
don't just rely on the INSERT.
David Portas
SQL Server MVP
--|||
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:L8udnQJ32Z_Hm2ncRVn-rA@.giganews.com...
> You added the VALUES clause, which means it's no longer an INSERT...
SELECT.
> Try:
> INSERT INTO AddressType (AddressType)
> SELECT 'Dentist'
> WHERE NOT EXISTS
> (SELECT *
> FROM AddressType
> WHERE AddressType = 'Dentist')
> BTW I assume AddressType is declared unique. Always declare unique
columns,
> don't just rely on the INSERT.
> --
> David Portas
> SQL Server MVP
> --
>
This works. Thanks. And no, the column is not declared unique. I forgot
about that attribute, but will add it...
Thanks again...
Bruce