Home page blank.

When i try to administer my Reporting Services, the following page
comes up with the picture of a folder and "Home"
http://dev7/Reports/Pages/Folder.aspx
How do I administer the Reporting Services enough to at least give
myself permissions to administer it?Browse to the server as a local administrator. Local admins have privileges
to set the security on items.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Trevor Morris" <StopTrevor@.gmail.com> wrote in message
news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
> When i try to administer my Reporting Services, the following page
> comes up with the picture of a folder and "Home"
> http://dev7/Reports/Pages/Folder.aspx
> How do I administer the Reporting Services enough to at least give
> myself permissions to administer it?|||I've browsed there as local admin and domain admin. Neither user has
anything on the Reports home page.
"Daniel Reib (MSFT)" wrote:
> Browse to the server as a local administrator. Local admins have privileges
> to set the security on items.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
> > When i try to administer my Reporting Services, the following page
> > comes up with the picture of a folder and "Home"
> > http://dev7/Reports/Pages/Folder.aspx
> >
> > How do I administer the Reporting Services enough to at least give
> > myself permissions to administer it?
>
>|||Usually this is because you have set your web site to anonymous. RS then
treats everybody as the same user which means no matter how you access it
you have only browse rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trevor Morris" <StopTrevor@.gmail.com> wrote in message
news:C4E963BF-836E-46EE-98D4-681C695B5F64@.microsoft.com...
> I've browsed there as local admin and domain admin. Neither user has
> anything on the Reports home page.
> "Daniel Reib (MSFT)" wrote:
> > Browse to the server as a local administrator. Local admins have
privileges
> > to set the security on items.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> > news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
> > > When i try to administer my Reporting Services, the following page
> > > comes up with the picture of a folder and "Home"
> > > http://dev7/Reports/Pages/Folder.aspx
> > >
> > > How do I administer the Reporting Services enough to at least give
> > > myself permissions to administer it?
> >
> >
> >|||Good guess, but still no luck.
The Reports directory does not have anonymous access enabled.
"Bruce L-C [MVP]" wrote:
> Usually this is because you have set your web site to anonymous. RS then
> treats everybody as the same user which means no matter how you access it
> you have only browse rights.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> news:C4E963BF-836E-46EE-98D4-681C695B5F64@.microsoft.com...
> > I've browsed there as local admin and domain admin. Neither user has
> > anything on the Reports home page.
> >
> > "Daniel Reib (MSFT)" wrote:
> >
> > > Browse to the server as a local administrator. Local admins have
> privileges
> > > to set the security on items.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> > > news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
> > > > When i try to administer my Reporting Services, the following page
> > > > comes up with the picture of a folder and "Home"
> > > > http://dev7/Reports/Pages/Folder.aspx
> > > >
> > > > How do I administer the Reporting Services enough to at least give
> > > > myself permissions to administer it?
> > >
> > >
> > >
>
>|||Check both Reports and ReportServer in IIS Manager and make sure that
neither one has anonymous access enabled.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trevor Morris" <StopTrevor@.gmail.com> wrote in message
news:EE02F092-41F6-47E6-B8E0-F60794E9B1AA@.microsoft.com...
> Good guess, but still no luck.
> The Reports directory does not have anonymous access enabled.
> "Bruce L-C [MVP]" wrote:
> > Usually this is because you have set your web site to anonymous. RS then
> > treats everybody as the same user which means no matter how you access
it
> > you have only browse rights.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> > news:C4E963BF-836E-46EE-98D4-681C695B5F64@.microsoft.com...
> > > I've browsed there as local admin and domain admin. Neither user has
> > > anything on the Reports home page.
> > >
> > > "Daniel Reib (MSFT)" wrote:
> > >
> > > > Browse to the server as a local administrator. Local admins have
> > privileges
> > > > to set the security on items.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> > > > news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
> > > > > When i try to administer my Reporting Services, the following page
> > > > > comes up with the picture of a folder and "Home"
> > > > > http://dev7/Reports/Pages/Folder.aspx
> > > > >
> > > > > How do I administer the Reporting Services enough to at least give
> > > > > myself permissions to administer it?
> > > >
> > > >
> > > >
> >
> >
> >|||OK. That did it.
Now, how do I enable anonymous access to reports, but require (or even
allow!) administration of the site? I'm very surprised that these two are
linked! Thanks for the help.
"Bruce L-C [MVP]" wrote:
> Check both Reports and ReportServer in IIS Manager and make sure that
> neither one has anonymous access enabled.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> news:EE02F092-41F6-47E6-B8E0-F60794E9B1AA@.microsoft.com...
> > Good guess, but still no luck.
> > The Reports directory does not have anonymous access enabled.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Usually this is because you have set your web site to anonymous. RS then
> > > treats everybody as the same user which means no matter how you access
> it
> > > you have only browse rights.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> > > news:C4E963BF-836E-46EE-98D4-681C695B5F64@.microsoft.com...
> > > > I've browsed there as local admin and domain admin. Neither user has
> > > > anything on the Reports home page.
> > > >
> > > > "Daniel Reib (MSFT)" wrote:
> > > >
> > > > > Browse to the server as a local administrator. Local admins have
> > > privileges
> > > > > to set the security on items.
> > > > >
> > > > > --
> > > > > -Daniel
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > >
> > > > >
> > > > > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
> > > > > news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
> > > > > > When i try to administer my Reporting Services, the following page
> > > > > > comes up with the picture of a folder and "Home"
> > > > > > http://dev7/Reports/Pages/Folder.aspx
> > > > > >
> > > > > > How do I administer the Reporting Services enough to at least give
> > > > > > myself permissions to administer it?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||You can't have it both ways. You can't say anyone can access it but only
certain people can do certain functions. If they are anonymous then how can
you know who they are. Is there an intranet or extranet application. If
intranet there is an easy solution, extranet is a different matter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trevor Morris" <StopTrevor@.gmail.com> wrote in message
news:71ECABEE-229A-49CA-9030-1289F35930CF@.microsoft.com...
> OK. That did it.
> Now, how do I enable anonymous access to reports, but require (or even
> allow!) administration of the site? I'm very surprised that these two are
> linked! Thanks for the help.
> "Bruce L-C [MVP]" wrote:
>> Check both Reports and ReportServer in IIS Manager and make sure that
>> neither one has anonymous access enabled.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
>> news:EE02F092-41F6-47E6-B8E0-F60794E9B1AA@.microsoft.com...
>> > Good guess, but still no luck.
>> > The Reports directory does not have anonymous access enabled.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > Usually this is because you have set your web site to anonymous. RS
>> > > then
>> > > treats everybody as the same user which means no matter how you
>> > > access
>> it
>> > > you have only browse rights.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
>> > > news:C4E963BF-836E-46EE-98D4-681C695B5F64@.microsoft.com...
>> > > > I've browsed there as local admin and domain admin. Neither user
>> > > > has
>> > > > anything on the Reports home page.
>> > > >
>> > > > "Daniel Reib (MSFT)" wrote:
>> > > >
>> > > > > Browse to the server as a local administrator. Local admins have
>> > > privileges
>> > > > > to set the security on items.
>> > > > >
>> > > > > --
>> > > > > -Daniel
>> > > > > This posting is provided "AS IS" with no warranties, and confers
>> > > > > no
>> > > rights.
>> > > > >
>> > > > >
>> > > > > "Trevor Morris" <StopTrevor@.gmail.com> wrote in message
>> > > > > news:0D68575C-CE15-449D-9FCF-C91208CF3ECC@.microsoft.com...
>> > > > > > When i try to administer my Reporting Services, the following
>> > > > > > page
>> > > > > > comes up with the picture of a folder and "Home"
>> > > > > > http://dev7/Reports/Pages/Folder.aspx
>> > > > > >
>> > > > > > How do I administer the Reporting Services enough to at least
>> > > > > > give
>> > > > > > myself permissions to administer it?
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>

Hlep with Simple questions about Authentication

The following question may be trivial, however, I just can't make it clear t
o
myself, even after reading through the Books Online.
I have two machines in the same network.
A hosts Sql Server (named AS), B access the database ASD in Sql Server AS.
A has windows logon info as UserA and PasswordA, (administrator account).
B has windows logon info as UserB and PasswordB. Application is running
under this account.
The Sql Server is set to Windows and Sql Serevr Mixed Authentication Mode,
The database ASD has a UserAS and PasswordAS as the db owner.
My question is:To successfully access AS from B, does the connection first
pass A's window authentication, then pass the AS's SQL Server authentication
(if set to Mixed Mode)?
If this is true,
What connection string (in ADO.Net) should I have to connect to the Database
?
Or, what authentication settings I should have(set) to make a successful
connection in the case describled above (B access AS on A)?
I observered, with the following string to work: "user
id=UserAS;password=PasswordASD; data source=AS;initial catalog=ASD; persist
security info=True;" , I should first set the A's login credential in B by
visiting B's network place.
Additional Question:
When I use Windows NT Integrated Security from B, which username and
password is used to acess AS on A?
When I use specific user name and password, how to create a pair of username
and password in AS so that the username and password can be authenticated by
the windows A and sql server AS.
Thanks.Start with this
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"zhaounknown" <zhaounknown@.discussions.microsoft.com> wrote in message
news:D272700C-B174-4853-83C3-E0CC8B53830D@.microsoft.com...
> The following question may be trivial, however, I just can't make it clear
> to
> myself, even after reading through the Books Online.
> I have two machines in the same network.
> A hosts Sql Server (named AS), B access the database ASD in Sql Server AS.
> A has windows logon info as UserA and PasswordA, (administrator account).
> B has windows logon info as UserB and PasswordB. Application is running
> under this account.
> The Sql Server is set to Windows and Sql Serevr Mixed Authentication Mode,
> The database ASD has a UserAS and PasswordAS as the db owner.
> My question is:To successfully access AS from B, does the connection first
> pass A's window authentication, then pass the AS's SQL Server
> authentication
> (if set to Mixed Mode)?
> If this is true,
> What connection string (in ADO.Net) should I have to connect to the
> Database?
> Or, what authentication settings I should have(set) to make a successful
> connection in the case describled above (B access AS on A)?
> I observered, with the following string to work: "user
> id=UserAS;password=PasswordASD; data source=AS;initial catalog=ASD;
> persist
> security info=True;" , I should first set the A's login credential in B by
> visiting B's network place.
> Additional Question:
> When I use Windows NT Integrated Security from B, which username and
> password is used to acess AS on A?
> When I use specific user name and password, how to create a pair of
> username
> and password in AS so that the username and password can be authenticated
> by
> the windows A and sql server AS.
> Thanks.|||I read though the article at the link.
It stated that:
Mixed mode: Valid SQL Server login accounts and passwords are not related to
your Microsoft Windows NT/2000 accounts. With this authentication mode, you
must supply the SQL Server login and password when you connect to SQL Server
.
I did have sql server user name and password set in the SQL Server and
supply the username and password for connection. But the connection is
refused because "SQL Server doesn't exists or access denied."
"Uri Dimant" wrote:

> Start with this
> http://vyaskn.tripod.com/sql_server...t_practices.htm --s
ecurity
> best practices
>
>
>
>
>
> "zhaounknown" <zhaounknown@.discussions.microsoft.com> wrote in message
> news:D272700C-B174-4853-83C3-E0CC8B53830D@.microsoft.com...
>
>

Hiya : Full msdb Log message

Could anyone tell me how to check the size of the current msdb and tempdb log files and how do I deal with the following alerts :
Full msdb log Error 9002
Full tempdb Error 9002
Mnay thanks
ZahedFist off, since this thread is much more technical than it is "Hello, world" in nature, I'm going to move it from the New Users and Introductions to the MS-SQL forum.

Next, there are multiple ways to check the size of the databases. Assuming that you are running SQL 2000, probably the easiest way is to open SQL Enterprise Manager, in the navigation pane (leftmost on most systems) you'll find a tree control... Pick your server, then databases, then the database that interests you (such as msdb or tempdb). Right click, and select Properites from the menu. You'll see tabs for data and log files.

If you need to "clean house" because of a log file being full, close the properties dialog, right click again, select All Tasks, then Shrink Database.

-PatP|||Refer to BOoks online for this error, that has complete information to recover it. I would also suggest to check what kind of operation is making this alerts raised and add more disks to accomodate the resource intensive operations.

History of data for documents

Hello,
I have following situation:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[CountryId] [uniqueidentifier] NOT NULL ,
[StreetName] [nvarchar] (100) NOT NULL ,
[StreetNo] [nvarchar] (10) NOT NULL ,
[LocalNo] [nvarchar] (10) NOT NULL ,
[PostalCode] [nvarchar] (20) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OwnerContractorId] [uniqueidentifier] NOT NULL ,
[TargetContractorId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL ,
[OrderId] [uniqueidentifier] NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[Quantity] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[CultureName] [nvarchar] (20) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
The Problem is:
Order - needs information about contractor and his address (address is
related to the contractor by one-to-many
relation). Furthermore, the order needs information about products (its
names, which are stored in
separate table - each product can have several names, depending on language)
.
If a name of a product would change, it will be changed in all orders. But
order is the document and I need to
keep it as it was at the moment of creation (constractor data - names,
address etc. , product data - names etc.).
To do this, I have to design some kind of history of data connection to each
other. It is bad situation to me,
when I change address of some contractor - all orders will have changed
addresses, the proper data connections
will collapse.
My idea is to copy all related data for the record which is edited. For
example if an address for contractor is
changed, the new contractor record is created with new id (guid) and the
same symbol (is unique for all active
records) (old one have flag "history" for example - is deactivated) and the
new address is related to this new contractor record. The same story applies
when a name of product is edited.
This mechanism lets to store correct data for each order in the database -
all documents(orders) are connected to the same data as at the moment of
their creation.
Reports in this case are performed by queries operationg in the "Symbol" of
the contractor - this gives the
possibility to find all orders of one contractor - each having correct
address for the moment of creation of
order.
My question is: is this design corresponding to the "rules of art" somehow?
What are your ideas for solving such problems?
I will be thankful for your opinions
Adam Rozycki & friends>> My question is: is this design corresponding to the "rules of art"
No
I am not sure where you got the idea of using a globally unique identifier
type for every key in your tables.
We all work within existing computational constraints; so no business
segment requires a machine generated global identifier for a table unless
you are researching on such values. Other than the "" factor, abuse &
hype, there is nothing simple, pragmatic or meaningful about using a
uniqueidentifier column as a key for Orders or Products table as in your
situation.
One approach to your problem, based on your narratives, can be like:
CREATE TABLE Orders (
Order_nbr INT NOT NULL PRIMARY KEY,
Product_id INT NOT NULL,
REFERENCES Products ( Product_id )
Contractor_id INT NOT NULL
Address_id INT NOT NULL,
REFERENCES Contractors ( Contractor_id, Address_id )
.. ) ;
CREATE TABLE Contractors (
Contractor_id INT NOT NULL,
Address_id INT NOT NULL,
REFERENCES Addresses ( Address_id ) ,
Name...
PRIMARY KEY ( Contractor_id, Address_id )
) ;
CREATE TABLE Addresses (
Address_id INT NOT NULL PRIMARY KEY,
Address VARCHAR( 40 ) NOT NULL,
City_state_zip VARCHAR( 40 ) NOT NULL,
UNIQUE ( Address, City_state_zip )
.. ) ;
CREATE TABLE Products (
Product_id INT NOT NULL PRIMARY KEY,
Product_name ...
) ;
To keep track of history of change in Product names use another table with
temporal datatypes like:
CREATE TABLE ProductHistory (
Product_id INT NOT NULL,
Product_name VARCHAR ( 100 ) NOT NULL,
Assigned_date DATETIME NOT NULL,
Withdrawn_date DATETIME NOT NULL,
..
PRIMARY KEY ( Product_id, Assigned_date )
CHECK ( Withdrawn_date >= Assigned_date )
);
The same approach can be used for changes in other attributes like symbols
which you mentioned as well.
Anith|||
"Anith Sen" wrote:

> I am not sure where you got the idea of using a globally unique identifier
> type for every key in your tables.
My idea was to single keys instead of complex ones.

> We all work within existing computational constraints; so no business
> segment requires a machine generated global identifier for a table unless
> you are researching on such values.
The reason why I used GUIDs is that this database has to be replicable and
data exchangable with several separate databases. There is to be one master
DB and several slave DBs. Since data can be added in some independent places
- I think I need to use the global identifiers.
My problem is that I have to have a data corresponding to Order, just the
same as it was at the moment of creating of Order. I cannot have situation
when changed contractor data changes data in all orders.
The database has to store names for products in several languages, since
that I cannot put all information about products in one table.
I would like to achieve rather some sort of document revision than history
of action on documents.
Adam|||>> My idea was to single keys instead of complex ones.
Good. Simple keys are a recommended consideration for a primary key.
However, having a uniqueidentifier in a table as the only key in your table
does nothing for entity identification, which is the main purpose of a key
in the first place.
Not necessarily. You could opt for any arbitrary namespace to determine
independent databases distributed over different servers. While
uniqueidentifier type guarantees the value to be unique globally, it cannot
guarantee the uniqueness of the corresponding entity.
For instance, a customer by name Adam in a table in database A cannot be
distinguished from another customer by same name Adam in similar table in a
replicable database B or even in the same table in the same database, just
by virtue of arbitrary GUIDs alone. All you'll have is duplicated entries of
Adam in the table with different GUIDs associated with them. How do you
identify the row corresponding to Adam? How will you enforce entity
integrity? How do you track down an alleged error, for instance in data
entry? Can you use GUIDs for referencing keys reliably without cascading
changes?
It is mostly hard to provide any specific meaningful suggestions here. While
you are familiar with your business model regarding the orders, customers,
languages, symbols, revisions etc., others in this newsgroup have no clue on
what they are or how they are related. You did provide a set of CREATE TABLE
statements without any keys, constraints, references etc. however databases
cannot be designed based on such. Esp. when only a couple of lines of
narrative are provided, there is a high chance that the overall business
model and rules are miscommunicated, misrepresented and/or misunderstood.
As a general suggestion, your approach to use GUIDs all over the table as
primary keys with no identifying attribute seems inherently flawed. However,
if you have made provisions for entity identification using UNIQUE NOT NULL
constraints, perhaps you might be able to work it out to some extent.
Consider using temporal datatypes for tracking historical information unless
you are using them already.
Also a few general design rules of thumb, if it helps:
* When you have a one-to-one relationship between two entity types, unless
there are any non-dependency preserving relationships, you may represent
them in a single table.
* When you have a many-to-one relationship between two entity types, you
should use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have a many-to-many relationship between two or more entity
types, you should introduce an "association" table which reduces the schema
to two or more many-to-one relationships on each table representing these
entity types.
Anith|||
"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creationof tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Is it now possible that you provide some judge of my record-history idea or
provide some other ideas how to perform this correctly?
Adam|||"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creation of tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Can you now provide some judgement of my record-history idea or provide some
other ideas how could it be performed correctly?
Adam

historical lookup query

I'm having a dickins of a time with a particular query and am hoping
someone here can help me.
Using the following example;
declare @.SearchDate datetime
set @.SearchDate = '30 Nov 2005'
declare @.t1 table (t1id int, t1desc varchar(10))
insert into @.t1 (t1id, t1desc) values (1, 'Ed')
insert into @.t1 (t1id, t1desc) values (2, 'Bill')
insert into @.t1 (t1id, t1desc) values (3, 'Bob')
insert into @.t1 (t1id, t1desc) values (4, 'Fred')
insert into @.t1 (t1id, t1desc) values (5, 'John')
declare @.t1history table (t1id int, t1desc varchar(10), created
datetime)
insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
May 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
Oct 2005')
declare @.lookup table (val varchar(10))
insert into @.lookup (val) values ('Ben')
insert into @.lookup (val) values ('Frank')
insert into @.lookup (val) values ('Bill')
--example query
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
I want to filter the results returned from @.t1 against those contained
in @.lookup
I also need to be able to filter the results based on what the value
for t1desc could have been in the past using @.t1history and @.SearchDate
For example, with the date of '30 Nov 2005' I would expect the
following;
t1id t1desc t1deschistory
----
2 'Bill' null
5 'John' 'Ben'
Changing the date to '15 Oct 2005' I would expect;
t1id t1desc t1deschistory
----
2 'Bill' null
and changing it again to '15 Jan 2005' I would expect;
t1id t1desc t1deschistory
----
1 'Ed' 'Frank'
2 'Bill' null
What I basically want to do is this;
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
This gives the following error as expected;
Server: Msg 207, Level 16, State 3, Line 28
Invalid column name 't1deschistory'.
Moving the sub-query into a join doesn't work either;
select *
from @.t1 as Table1
left join (select top 1 * from @.t1history as t1history where
t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) Table1History on Table1.t1id = Table1History.t1Id
where Table1.t1desc in (select val from @.lookup) or
Table1History.t1desc in (select val from @.lookup)
This gives the following error;
Server: Msg 107, Level 16, State 2, Line 28
The column prefix 'Table1' does not match with a table name or alias
name used in the query.
Can anyone help?
Many thanks in advance,
Edone way: make it a derived table before applying the where e.g.
select * from (
select *,
(select top 1 t1desc
from @.t1history as Table1History
where Table1History.t1id = Table1.t1Id
and created <= @.SearchDate +1
order by created desc) as t1deschistory
from @.t1 as Table1
) x
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
ThievingScouser wrote:
> I'm having a dickins of a time with a particular query and am hoping
> someone here can help me.
> Using the following example;
> declare @.SearchDate datetime
> set @.SearchDate = '30 Nov 2005'
> declare @.t1 table (t1id int, t1desc varchar(10))
> insert into @.t1 (t1id, t1desc) values (1, 'Ed')
> insert into @.t1 (t1id, t1desc) values (2, 'Bill')
> insert into @.t1 (t1id, t1desc) values (3, 'Bob')
> insert into @.t1 (t1id, t1desc) values (4, 'Fred')
> insert into @.t1 (t1id, t1desc) values (5, 'John')
> declare @.t1history table (t1id int, t1desc varchar(10), created
> datetime)
> insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
> May 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
> Oct 2005')
> declare @.lookup table (val varchar(10))
> insert into @.lookup (val) values ('Ben')
> insert into @.lookup (val) values ('Frank')
> insert into @.lookup (val) values ('Bill')
> --example query
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
>
> I want to filter the results returned from @.t1 against those contained
> in @.lookup
> I also need to be able to filter the results based on what the value
> for t1desc could have been in the past using @.t1history and @.SearchDate
> For example, with the date of '30 Nov 2005' I would expect the
> following;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> 5 'John' 'Ben'
> Changing the date to '15 Oct 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> and changing it again to '15 Jan 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 1 'Ed' 'Frank'
> 2 'Bill' null
>
> What I basically want to do is this;
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
> where t1desc in (select val from @.lookup) or t1deschistory in (select
> val from @.lookup)
> This gives the following error as expected;
> Server: Msg 207, Level 16, State 3, Line 28
> Invalid column name 't1deschistory'.
> Moving the sub-query into a join doesn't work either;
> select *
> from @.t1 as Table1
> left join (select top 1 * from @.t1history as t1history where
> t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) Table1History on Table1.t1id = Table1History.t1Id
> where Table1.t1desc in (select val from @.lookup) or
> Table1History.t1desc in (select val from @.lookup)
> This gives the following error;
> Server: Msg 107, Level 16, State 2, Line 28
> The column prefix 'Table1' does not match with a table name or alias
> name used in the query.
>
> Can anyone help?
> Many thanks in advance,
> Ed
>

Hints in T-SQL (SQL Server 2000)

Hello,
I want the help on the SQL Server HINTS. In Oracel we use the hints in the following manner:
SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
Similarly, i found the help on SQL Server HINTS. They are used with the Keyword 'OPTION' at the end of SELECT statement.
Here i want to know more about the hints. In specific, i would like to know about the 'OPTION (FORCE ORDER)' hint in sql server.
If any one if you have any idea regarding this then do let me.
Thanking all of u in advance.
Aparna,
Still migrating things? SQL Server uses a cost based
optimizer and it's generally better to let SQL Server figure
out the most efficient plans. In general, you don't see
hints being used in SQL Server to the same degree they are
used in Oracle. For the most part, you'd want to have a good
specific reason for using them in SQL Server. I've seen
cases where hints being used actually hurt performance more
than helped. Have you come up with something specific on the
execution of a query that you'd want to use this?
-Sue
On Mon, 12 Apr 2004 21:36:04 -0700, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>I want the help on the SQL Server HINTS. In Oracel we use the hints in the following manner:
>SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
>Similarly, i found the help on SQL Server HINTS. They are used with the Keyword 'OPTION' at the end of SELECT statement.
>Here i want to know more about the hints. In specific, i would like to know about the 'OPTION (FORCE ORDER)' hint in sql server.
>If any one if you have any idea regarding this then do let me.
>Thanking all of u in advance.

Hints in T-SQL (SQL Server 2000)

Hello,
I want the help on the SQL Server HINTS. In Oracel we use the hints in the f
ollowing manner:
SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
Similarly, i found the help on SQL Server HINTS. They are used with the Keyw
ord 'OPTION' at the end of SELECT statement.
Here i want to know more about the hints. In specific, i would like to know
about the 'OPTION (FORCE ORDER)' hint in sql server.
If any one if you have any idea regarding this then do let me.
Thanking all of u in advance.Aparna,
Still migrating things? SQL Server uses a cost based
optimizer and it's generally better to let SQL Server figure
out the most efficient plans. In general, you don't see
hints being used in SQL Server to the same degree they are
used in Oracle. For the most part, you'd want to have a good
specific reason for using them in SQL Server. I've seen
cases where hints being used actually hurt performance more
than helped. Have you come up with something specific on the
execution of a query that you'd want to use this?
-Sue
On Mon, 12 Apr 2004 21:36:04 -0700, Aparna
<aparna.shirodkar@.lycos.com> wrote:

>Hello,
>I want the help on the SQL Server HINTS. In Oracel we use the hints in the
following manner:
>SELECT /*+ ORDERED use_nl(EMPLOYEE_RECORD_MGR)*/
>Similarly, i found the help on SQL Server HINTS. They are used with the Key
word 'OPTION' at the end of SELECT statement.
>Here i want to know more about the hints. In specific, i would like to know
about the 'OPTION (FORCE ORDER)' hint in sql server.
>If any one if you have any idea regarding this then do let me.
>Thanking all of u in advance.sql

Hilary: Replication Latency (Part 2)

Hilary,
I know you have helped me with a replication latency script in the past. But
what i want is more of the following .
I have trans replication set up in continuous mode.
If I stop the log reader agent and the distribution agent while my publisher
is receiving some changes, I want to find out
a) how many commands/transactions are in the publisher that have not made it
to the distribution db ( Log Reader Agent Latency )
b) how many commands/transactions are in the distributor that have not made
it to the subscribingdbs ( Distribution Agent Latency )
c) How long are these command/trans are in the publisher since they came in
and not made it to the distribution db and also how long have they been
sitting in the distribution db since they came in and not made it to the
subscribing db
So primarily no. of outstanding cmds/trans and time is what im looking at ..
Is this something that you may have a handy script already ?
Thanks
answers inline
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23izmyu4FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> I know you have helped me with a replication latency script in the past.
But
> what i want is more of the following .
> I have trans replication set up in continuous mode.
> If I stop the log reader agent and the distribution agent while my
publisher
> is receiving some changes, I want to find out
> a) how many commands/transactions are in the publisher that have not made
it
> to the distribution db ( Log Reader Agent Latency )
you can tell the number of transactions by issuing by sp_repltrans - this
will give you the number of transactions - but not the number of commands

> b) how many commands/transactions are in the distributor that have not
made
> it to the subscribingdbs ( Distribution Agent Latency )
select * from distribution.MSdistribution_status
Look at the undistributed commands column
> c) How long are these command/trans are in the publisher since they came
in
> and not made it to the distribution db and also how long have they been
> sitting in the distribution db since they came in and not made it to the
> subscribing db
run this in your distribution database
select time, entry_time from
SubscriberServerName.SubscriberDatabaseName.dbo.MS replicationX_subscriptions
,
msrepl_transactions
where transaction_timestamp=xact_seqno
You may want to run this in your subscription database as well to get time
in seconds as opposed to minutes
alter table MSreplication_subscriptions
alter column time datetime

> So primarily no. of outstanding cmds/trans and time is what im looking at
...
> Is this something that you may have a handy script already ?
> Thanks
>
sql

Hilary...Please provide an example

Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
Can you contact me offline. I have a bit of a write up on how to do this.
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
"JLS" <judes@.email.uophx.edu> wrote in message news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
|||Yes, absolutely, how do I contact you offline?
If it's easier, you can contact me at your convenience, toll free 1-800-sartomer, extension 4154 or have the operator page Judy Shoop or directly to my office is 610.363.4154.
Thanx!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:eMLs6Zp$FHA.3096@.TK2MSFTNGP14.phx.gbl...
Can you contact me offline. I have a bit of a write up on how to do this.
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
"JLS" <judes@.email.uophx.edu> wrote in message news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to the db on the DMZ, and now I need to pull 2 tables from this same db from the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_ scripts for the pull subscription & pull agent, for NO SYNC?
Jude
|||Use my email address - but here is the doc I put together.
The requirement is that DATABASE be replicated bi-directionally internally
and externally. There are several options to do this, bi-directional
transactional replication was chosen as it does not modify the schema. Merge
replication, and updateable subscribers all modify the schema with the
addition of a GUID column.
To get around the firewall problem the internal database will replicate to
the external database, and the external database changes will be pulled
internally. The internal server will be configured as an anonymous
subscriber of the external server's publication as a named subscriber will
not work.
Steps required configure replication.
1) connect to the production database, right click on each of the
production tables and set the identity attribute to be not for replication.
Configure the increment to be 2. You will need to modify the following
tables.
TableName1WithIdentityColumn
TableName2WithIdentityColumn
TableName3WithIdentityColumn
TableName4WithIdentityColumn
The best/only way to configure these tables is to right click on them in
Enterprise Manager and set the identity property accordingly. Figure 1 is a
correctly configured table.
2) After all of the tables have been configured, back up the database,
and restore it on the subscriber. Ensure that no users are accessing the
database at this time.
3) Once the database has been restored create the publication on the
Publisher and the Subscriber. Use the attached script for this, editing for
the correct server and database names. - this script is a publication where
the name conflicts section is keep existing table intact, and its a nosync
subscription.
4) Once you have done this you need to run the same script in your
external server. Make sure you have configured your subscriber using its
NetBIOS name in Client Network Utility. A Fully Qualified Domain Name will
not work; it must be a NetBIOS name. Please refer to Figure 2 for an
example.
5) Once you have configured replication, deploy the replication stored
procedures on both sides. Please refer to the attached script to do this.
(this script was generated by running sp_scriptpublicationcustomprocs
6) Once the replication stored procedures are in place you have to
reset the identity seed on both sides. We will assign even numbers to the
internal server, and external numbers to the external server.
7) To do this issue the following commands. Note that TableName5 is the
only table which has to be fixed, and the fix is illustrated in red:
dbcc checkident('TableName5')
--Checking identity information: current identity value '8854', current
column value '8854'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
dbcc checkident('TableName5')
--Checking identity information: current identity value '448', current
column value '448'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
dbcc checkident('TableName6)
--Checking identity information: current identity value '19', current column
value '19'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--not ok as the number is odd
--incrementing it up to the nearest even number
dbcc checkident('TableName7', reseed,20)
--Checking identity information: current identity value '19', current column
value '20'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--now correct
dbcc checkident('TableName8')
--Checking identity information: current identity value '32', current column
value '32'.
--DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--ok as the number is even
8) Repeat for the Subscriber (external server). This time all the
identity values should be reseeded to the next highest odd number.
9) Schedule the distribution agents to start every 5 minutes - this
will guarantee an restart in the event of failure.
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eCOXKey$FHA.1408@.TK2MSFTNGP15.phx.gbl...
Yes, absolutely, how do I contact you offline?
If it's easier, you can contact me at your convenience, toll free
1-800-sartomer, extension 4154 or have the operator page Judy Shoop or
directly to my office is 610.363.4154.
Thanx!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eMLs6Zp$FHA.3096@.TK2MSFTNGP14.phx.gbl...
Can you contact me offline. I have a bit of a write up on how to do this.
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
"JLS" <judes@.email.uophx.edu> wrote in message
news:exbkymo$FHA.356@.TK2MSFTNGP12.phx.gbl...
Please give me more details on what you are saying to do, I'm just not
following how I can accomplish this.
Both servers already have this db, the one in the domain is pushing to
the db on the DMZ, and now I need to pull 2 tables from this same db from
the db on the DMZ.
I am trying to accomplish this with a Anonymous Pull Subscription, but I
don't know how to set it up for no sync.
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ekZ$dCQ$FHA.2392@.TK2MSFTNGP09.phx.gbl...
Jude, to make life simple for your self is there anyway you can
restore the publishing database to the subscriber and do a no-sync?
This will make life much easier for you.
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23%23RCIYP$FHA.264@.tk2msftngp13.phx.gbl...
How can I setup an anonymous pull subscription, using the sp_
scripts for the pull subscription & pull agent, for NO SYNC?
Jude

hii

Write SQL Queries for the following:

Cust

Cust_ID

Name

1

AA

2

BB

3

CC

4

DD

5

EE

Ord

Ord_ID

Cust_ID

Amt

Tran_Type

From_Ord_ID

1

1

10

D

NULL

2

1

20

D

NULL

3

2

30

D

NULL

4

2

40

D

NULL

5

2

NULL

D

NULL

6

3

NULL

D

NULL

7

4

40

D

NULL

8

4

50

D

NULL

9

4

-10

C

7

10

4

-20

C

8

11

4

-30

C

8

<!--[endif]-->Write a query that shows the balance for customer DD by trans_type. Consider a Tran_type of “C” to be a transactional adjustment to the associated original “D” record. The result should be as follows.

Name

D

C

Balance

DD

40

-10

30

(It looks like that time of year again, when we start getting requests to do class assignments.)

Subhash,

Please post the efforts you have made up to now, and we can help guide you to a correct solution. I just don't think that you will find folks here willing to do your classwork for you. Also post the table DDL, and sample data in the form of INSERT statements. If you need help with that concept, check here or here.

|||

hahaha.

well its actually easy. you need to use join

and sum function and group by

sql

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbId FileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
--
Neil HamblyNeil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
--
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hamblysql

High values shown in fn_virtualfilestats

Hello
Recently taken a new DBA position, reviewing existing infrasturture and DB
config's
Ran fn_Virtualfilestats and got the following values
does anyone think these are high or show areas for further investigation,
don't want to waste time on further research unless it is warranted etc..
DbId FileId NumberReads NumberWrites BytesRead BytesWritten
IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
%Bytes %Stall
1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
1,971,159,040 12 14,953 - - -
1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
- - -
2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
4,685,123 265,700,286,464 - 56,711 - - -
2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
2,014,727,680 3 49,441 - - -
3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
5 55,757 - - -
3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
- -
4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
1,727,569,920 9 17,538 - - -
4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
1,123 - - -
5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
7,832,162 179,358,797,824 - 22,900 - - -
5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
1,020,696 146,016,542,720 - 143,055 - - -
6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
7,271,890,944 - 88,095 - - -
6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
13,559 - - -
18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
- -
Neil HamblyNeil,
That is a pretty unreadable post and most people willnot take the time to
clean it up. But a single snapshot of filestats is mostly meaningless by
itself. You need to take a baseline or starting point first. Then after
some period of time you take another reading and compare the differences.
That gives you a time period to reference how long it takes for the numbers
to accumulate.
Andrew J. Kelly SQL MVP
"Neil Hambly" <hambly_neil@.hotmail.com> wrote in message
news:49136F95-EF7A-42AF-9042-08D670FBDA36@.microsoft.com...
> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 940
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168 -
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly|||May be easier to read if values copied into excel
main concerns are with Byte
"Neil Hambly" wrote:

> Hello
> Recently taken a new DBA position, reviewing existing infrasturture and DB
> config's
> Ran fn_Virtualfilestats and got the following values
> does anyone think these are high or show areas for further investigation,
> don't want to waste time on further research unless it is warranted etc..
> DbId FileId NumberReads NumberWrites BytesRead BytesWritten
> IoStallMS TotalIO TotalBytes AvgStallPerIO AvgBytesPerIO %IO
> %Bytes %Stall
> 1 1 130,290 1,526 1,958,551,552 12,607,488 1,698,394 131,816
> 1,971,159,040 12 14,953 - - -
> 1 2 133 8,352 844,288 7,136,768 530 8,485 7,981,056 - 9
40
> - - -
> 2 1 2,083,373 2,601,750 123,263,590,400 142,436,696,064 4,019,981
> 4,685,123 265,700,286,464 - 56,711 - - -
> 2 2 445 40,305 3,399,680 2,011,328,000 138,101 40,750
> 2,014,727,680 3 49,441 - - -
> 3 1 1,657 212 102,449,152 1,761,280 10,597 1,869 104,210,432
> 5 55,757 - - -
> 3 2 125 376 808,960 1,279,488 606 501 2,088,448 1 4,168
-
> - -
> 4 1 85,701 12,803 1,611,898,880 115,671,040 920,057 98,504
> 1,727,569,920 9 17,538 - - -
> 4 2 131 35,076 867,840 38,672,896 734 35,207 39,540,736 -
> 1,123 - - -
> 5 1 129,386,983 74,380,203 4,230,597,074,944 758,879,707,136
> 620,669,109 203,767,186 4,989,476,782,080 3 24,486 23 15 35
> 5 2 46,854 7,785,308 8,537,051,648 170,821,746,176 83,066
> 7,832,162 179,358,797,824 - 22,900 - - -
> 5 3 859,051 161,645 137,308,340,224 8,708,202,496 433,444
> 1,020,696 146,016,542,720 - 143,055 - - -
> 6 1 300,782,919 39,811,200 13,757,658,587,136 883,146,989,568
> 624,737,406 340,594,119 14,640,805,576,704 1 42,986 38 44 35
> 6 2 37,640,629 48,658,405 2,287,763,603,456 1,769,531,336,704
> 587,872 86,299,034 4,057,294,940,160 - 47,014 9 12 -
> 6 3 93,167,363 5,626,515 1,646,596,202,496 91,128,004,608
> 206,870,818 98,793,878 1,737,724,207,104 2 17,589 11 5 11
> 6 4 40,422 42,124 3,743,186,944 3,528,704,000 40,320 82,546
> 7,271,890,944 - 88,095 - - -
> 6 5 37,396,020 9,428,284 4,503,717,322,752 110,340,931,584
> 155,824,784 46,824,304 4,614,058,254,336 3 98,539 5 13 8
> 6 6 87,854,655 6,876,154 2,424,926,773,248 99,701,825,536
> 150,576,872 94,730,809 2,524,628,598,784 1 26,650 10 7 8
> 18 1 1,524 16 20,750,336 131,072 33,663 1,540 20,881,408 21
> 13,559 - - -
> 18 2 10 17 279,552 165,888 187 27 445,440 6 16,497 -
> - -
> --
> Neil Hambly