Product for Creating Data Dictionary

We have a large SQL Server database that has poorly documented tables and
field names. Is there a product that will create a Word document in table
format from the actual database that can be used to add documentation about
the meaning of each field?
--
WillWill
SQL Server 2000 has an option called column description
create table dbo.customer (
customer_id integer not null identity (1, 1)
, trade_name varchar (0255) not null
)
go
alter table dbo.customer add
constraint pk_customer primary key nonclustered (customer_id)
go
/* column description meta info */
execute sp_addextendedproperty N'boolean_property_01', '1', N'user', N'dbo',
N'table', N'customer', N'column', N'trade_name'
execute sp_addextendedproperty N'column_description', 'Customer trading
name', N'user', N'dbo', N'table', N'customer', N'column', N'trade_name'
go
select * from :: fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'customer', 'column', default)
go
drop table dbo.customer
go
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:OOrsHkGUFHA.3636@.TK2MSFTNGP14.phx.gbl...
> We have a large SQL Server database that has poorly documented tables and
> field names. Is there a product that will create a Word document in
table
> format from the actual database that can be used to add documentation
about
> the meaning of each field?
> --
> Will
>
>

Producing several letters based on the results of a query

I have created a single page letter using a number of text boxes and one
table, which is populated with static text along with some data from a single
row results set. This works perfectly when the results set only contains one
row, but when there are multiple data rows, the report still only contains
one page, whereas I expected there to be a page for every row.
A simple way to reproduce this is to create a report with a single text box
containing the value of one column from a query that returns multiple rows.
This results in a one page report, rather than a page for each row returned.
I'd be grateful if someone can help with this - I'm hoping there's a simple
solution, like setting a 'repeating region' property.
By the way, I've tried creating a single column table in the hope I could
put the entire contents of the letter into a cell of the table and that the
table row (and therefore the letter) would then repeat for each row of data,
but it doesn't appear to be possible to fix the size of a table row and have
multiple text boxes (and a table) inside it.
Thanks in advance,
David.Hey David,
If I understand you correctly, your trying to make, basically, a form
letter, that populates the variables from a query. For example
This is my test letter to "DAVID".
Where "DAVID" is generated from column/row in your Dataset. Why can't you
make a single cell table with all the text as you want it, except insert the
Field references you need (instead of textboxes)? For Example, the cell
would look like:
= "This is my test letter to " & Fields!ContactName.Value & ". " &
Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
etc...
Once this is done, you choose the "Group" properties and place a page break
after each group (or row in this case). This creates a new letter, each on
its own pages, for each row returned.
Michael C
"David C" wrote:
> I have created a single page letter using a number of text boxes and one
> table, which is populated with static text along with some data from a single
> row results set. This works perfectly when the results set only contains one
> row, but when there are multiple data rows, the report still only contains
> one page, whereas I expected there to be a page for every row.
> A simple way to reproduce this is to create a report with a single text box
> containing the value of one column from a query that returns multiple rows.
> This results in a one page report, rather than a page for each row returned.
> I'd be grateful if someone can help with this - I'm hoping there's a simple
> solution, like setting a 'repeating region' property.
> By the way, I've tried creating a single column table in the hope I could
> put the entire contents of the letter into a cell of the table and that the
> table row (and therefore the letter) would then repeat for each row of data,
> but it doesn't appear to be possible to fix the size of a table row and have
> multiple text boxes (and a table) inside it.
> Thanks in advance,
> David.|||Thanks Michael - it was the grouping that I needed. I actually stumbled
across the same answer last night in a TechNet article
(http://technet.microsoft.com/en-us/library/ms155816.aspx).
I put a List in first and then added a single column Table to it. I then
created a group expression on the list in Properties - General - Edit Details
Group - General, as follows: -
=Ceiling(RowNumber(Nothing)/1)
I also set 'Page break at end'.
Is this what you meant?
My problem now is that I want to make some of the text bold and I thought
I'd be able to do this using the Format command - something like Format("some
text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
that?
Thanks again.
David.
"Michael C" wrote:
> Hey David,
> If I understand you correctly, your trying to make, basically, a form
> letter, that populates the variables from a query. For example
> This is my test letter to "DAVID".
> Where "DAVID" is generated from column/row in your Dataset. Why can't you
> make a single cell table with all the text as you want it, except insert the
> Field references you need (instead of textboxes)? For Example, the cell
> would look like:
> = "This is my test letter to " & Fields!ContactName.Value & ". " &
> Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> etc...
> Once this is done, you choose the "Group" properties and place a page break
> after each group (or row in this case). This creates a new letter, each on
> its own pages, for each row returned.
> Michael C
> "David C" wrote:
> > I have created a single page letter using a number of text boxes and one
> > table, which is populated with static text along with some data from a single
> > row results set. This works perfectly when the results set only contains one
> > row, but when there are multiple data rows, the report still only contains
> > one page, whereas I expected there to be a page for every row.
> >
> > A simple way to reproduce this is to create a report with a single text box
> > containing the value of one column from a query that returns multiple rows.
> > This results in a one page report, rather than a page for each row returned.
> >
> > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > solution, like setting a 'repeating region' property.
> >
> > By the way, I've tried creating a single column table in the hope I could
> > put the entire contents of the letter into a cell of the table and that the
> > table row (and therefore the letter) would then repeat for each row of data,
> > but it doesn't appear to be possible to fix the size of a table row and have
> > multiple text boxes (and a table) inside it.
> >
> > Thanks in advance,
> >
> > David.|||Hey David,
Unfortunately I do not think there is a way, and if there i do not know
it. I had asked the forum recently about 2 fonts, 1 control but got no
responses.
You have done basically what I was describing. Glad it (sort of) worked for
you.
Michael
"David C" wrote:
> Thanks Michael - it was the grouping that I needed. I actually stumbled
> across the same answer last night in a TechNet article
> (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> I put a List in first and then added a single column Table to it. I then
> created a group expression on the list in Properties - General - Edit Details
> Group - General, as follows: -
> =Ceiling(RowNumber(Nothing)/1)
> I also set 'Page break at end'.
> Is this what you meant?
> My problem now is that I want to make some of the text bold and I thought
> I'd be able to do this using the Format command - something like Format("some
> text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> that?
> Thanks again.
> David.
>
> "Michael C" wrote:
> > Hey David,
> > If I understand you correctly, your trying to make, basically, a form
> > letter, that populates the variables from a query. For example
> >
> > This is my test letter to "DAVID".
> >
> > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > make a single cell table with all the text as you want it, except insert the
> > Field references you need (instead of textboxes)? For Example, the cell
> > would look like:
> >
> > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > etc...
> >
> > Once this is done, you choose the "Group" properties and place a page break
> > after each group (or row in this case). This creates a new letter, each on
> > its own pages, for each row returned.
> >
> > Michael C
> >
> > "David C" wrote:
> >
> > > I have created a single page letter using a number of text boxes and one
> > > table, which is populated with static text along with some data from a single
> > > row results set. This works perfectly when the results set only contains one
> > > row, but when there are multiple data rows, the report still only contains
> > > one page, whereas I expected there to be a page for every row.
> > >
> > > A simple way to reproduce this is to create a report with a single text box
> > > containing the value of one column from a query that returns multiple rows.
> > > This results in a one page report, rather than a page for each row returned.
> > >
> > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > solution, like setting a 'repeating region' property.
> > >
> > > By the way, I've tried creating a single column table in the hope I could
> > > put the entire contents of the letter into a cell of the table and that the
> > > table row (and therefore the letter) would then repeat for each row of data,
> > > but it doesn't appear to be possible to fix the size of a table row and have
> > > multiple text boxes (and a table) inside it.
> > >
> > > Thanks in advance,
> > >
> > > David.|||Hi Michael,
The text I wanted to be in bold was on a separate line, so in the end I just
put in a separate row in the table and set the row to bold. This worked fine,
presumably because the List control was handling the grouping.
Thanks again for your help.
David.
"Michael C" wrote:
> Hey David,
> Unfortunately I do not think there is a way, and if there i do not know
> it. I had asked the forum recently about 2 fonts, 1 control but got no
> responses.
> You have done basically what I was describing. Glad it (sort of) worked for
> you.
> Michael
> "David C" wrote:
> > Thanks Michael - it was the grouping that I needed. I actually stumbled
> > across the same answer last night in a TechNet article
> > (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> >
> > I put a List in first and then added a single column Table to it. I then
> > created a group expression on the list in Properties - General - Edit Details
> > Group - General, as follows: -
> >
> > =Ceiling(RowNumber(Nothing)/1)
> >
> > I also set 'Page break at end'.
> >
> > Is this what you meant?
> >
> > My problem now is that I want to make some of the text bold and I thought
> > I'd be able to do this using the Format command - something like Format("some
> > text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> > that?
> >
> > Thanks again.
> >
> > David.
> >
> >
> >
> > "Michael C" wrote:
> >
> > > Hey David,
> > > If I understand you correctly, your trying to make, basically, a form
> > > letter, that populates the variables from a query. For example
> > >
> > > This is my test letter to "DAVID".
> > >
> > > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > > make a single cell table with all the text as you want it, except insert the
> > > Field references you need (instead of textboxes)? For Example, the cell
> > > would look like:
> > >
> > > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > > etc...
> > >
> > > Once this is done, you choose the "Group" properties and place a page break
> > > after each group (or row in this case). This creates a new letter, each on
> > > its own pages, for each row returned.
> > >
> > > Michael C
> > >
> > > "David C" wrote:
> > >
> > > > I have created a single page letter using a number of text boxes and one
> > > > table, which is populated with static text along with some data from a single
> > > > row results set. This works perfectly when the results set only contains one
> > > > row, but when there are multiple data rows, the report still only contains
> > > > one page, whereas I expected there to be a page for every row.
> > > >
> > > > A simple way to reproduce this is to create a report with a single text box
> > > > containing the value of one column from a query that returns multiple rows.
> > > > This results in a one page report, rather than a page for each row returned.
> > > >
> > > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > > solution, like setting a 'repeating region' property.
> > > >
> > > > By the way, I've tried creating a single column table in the hope I could
> > > > put the entire contents of the letter into a cell of the table and that the
> > > > table row (and therefore the letter) would then repeat for each row of data,
> > > > but it doesn't appear to be possible to fix the size of a table row and have
> > > > multiple text boxes (and a table) inside it.
> > > >
> > > > Thanks in advance,
> > > >
> > > > David.

produce SQL statement for a database

hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)(for SQL Server 2000...and maybe 7.0)

In the SQL Query Analyzer Object Browser, drill down to your table, right-click on the table name and select "Script Object...As" and it will generate the SQL for you.

Additionally, in Enterprise Manager, drill down to your "Tables" and select the table you want to query. Right-click on the table, Select Open Table --> Query. You SQL statement is generated dynamically as you select fields. A Select statement is the default. In the gray area where you see the table and all of it's fields, Right-click and there is a selection for "Change Type." In there you can change the query to an Insert, Create, etc...|||I prefer to build my own..

USE NorthWind

DECLARE @.TBName sysname, @.SQL varchar(8000)

SELECT @.TBName = 'Cust', @.SQL = ''

SELECT @.SQL = @.SQL + RTRIM(SQL) FROM (
--SELECT SQL FROM (
SELECT RTRIM(' SELECT ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

SELECT @.SQL

--EXEC(@.SQL)

Produce data -> Store in table?

Hi!

I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..

Date
Year
Quarter
Month
WeekDay
...

With some precalculated values for each date.

I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.

My first thought is to use some kind of Insert Cursor in a While loop...

Pseudo:

declare cursor ex for 'Insert table ... '

while
begin

(produce data)
insert data

end

close cursor

While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...

Have a nice day!

You could use WHILE loop without cursor:

Code Snippet

create table dates

(

Year int,

Quarter int,

Month int,

WeekDay int,

SomeData decimal

)

go

declare @.start_date datetime

declare @.end_date datetime

set @.start_date = '2007-01-01'

set @.end_date = getdate()

while @.start_date<@.end_date

begin

set @.start_date = dateadd(day, 1, @.start_date)

insert into dates values(

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

rand() --Just for demo

)

end

go

select * from Dates

|||

Best have a calendar table (you can add all the nessasary columns here), fill the table with required date range, join with your table & use the required calculations,

Code Snippet

create table calendar

(

year int,

quarter int,

month int,

weekday int,

date datetime primary key

)

Go

create proc fill_calendar(@.start_date datetime,@.end_date datetime)

as

begin

set nocount on;

while @.start_date <= @.end_date

begin

insert into calendar

select

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

@.start_date

where

not exists(select 1 from calendar where date=@.start_date);

set @.start_date = dateadd(day, 1, @.start_date)

end

end

go

--Fill your calendar when required

exec fill_calendar '1/1/2000','12/31/2007'

select * from calendar

go

select

year --other columns

,sum(yourtable.calculation)

from

calendar

inner join yourtable on yourtable.datefield = calendar.date

group by

year --other columns

procs that reference a table

In SQL Server 2000, I would like to find what stored procedures are accessin
g
a table.nm, got it.
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
"JayKon" wrote:

> In SQL Server 2000, I would like to find what stored procedures are access
ing
> a table.
>|||JayKon,
In an ideal world, you would have your answer, but the world is not always
ideal. Due to deferred name resolution, etc. the sysdepends table is not
always up-to-date. You might find Erland and Roy's comments in this thread
interesting on finding dependencies.
6b64a938d50b3a68" target="_blank">http://groups.google.com/group/micr...b64a938d50b3a68
RLF
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:77339A0F-1895-4856-A80C-EEB0151CF923@.microsoft.com...[vbcol=seagreen]
> nm, got it.
> SELECT name, xtype
> FROM sysobjects
> WHERE id IN (
> SELECT id
> FROM sysdepends
> WHERE depid IN (
> SELECT id
> FROM sysobjects
> WHERE name = @.Table
> )
> )
> "JayKon" wrote:
>

procs that reference a table

In SQL Server 2000, I would like to find what stored procedures are accessing
a table.nm, got it.
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
"JayKon" wrote:
> In SQL Server 2000, I would like to find what stored procedures are accessing
> a table.
>|||JayKon,
In an ideal world, you would have your answer, but the world is not always
ideal. Due to deferred name resolution, etc. the sysdepends table is not
always up-to-date. You might find Erland and Roy's comments in this thread
interesting on finding dependencies.
http://groups.google.com/group/microsoft.public.sqlserver.tools/browse_thread/thread/d8b39c5fd36ad806/6b64a938d50b3a68?lnk=st&q=ROY+HARVEY+SYSCOMMENTS&rnum=8&hl=en#6b64a938d50b3a68
RLF
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:77339A0F-1895-4856-A80C-EEB0151CF923@.microsoft.com...
> nm, got it.
> SELECT name, xtype
> FROM sysobjects
> WHERE id IN (
> SELECT id
> FROM sysdepends
> WHERE depid IN (
> SELECT id
> FROM sysobjects
> WHERE name = @.Table
> )
> )
> "JayKon" wrote:
>> In SQL Server 2000, I would like to find what stored procedures are
>> accessing
>> a table.

procs that reference a table

In SQL Server 2000, I would like to find what stored procedures are accessing
a table.
nm, got it.
SELECT name, xtype
FROM sysobjects
WHERE id IN (
SELECT id
FROM sysdepends
WHERE depid IN (
SELECT id
FROM sysobjects
WHERE name = @.Table
)
)
"JayKon" wrote:

> In SQL Server 2000, I would like to find what stored procedures are accessing
> a table.
>
|||JayKon,
In an ideal world, you would have your answer, but the world is not always
ideal. Due to deferred name resolution, etc. the sysdepends table is not
always up-to-date. You might find Erland and Roy's comments in this thread
interesting on finding dependencies.
[url]http://groups.google.com/group/microsoft.public.sqlserver.tools/browse_thread/thread/d8b39c5fd36ad806/6b64a938d50b3a68?lnk=st&q=ROY+HARVEY+SYSCOMMENTS&r num=8&hl=en#6b64a938d50b3a68[/url]
RLF
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:77339A0F-1895-4856-A80C-EEB0151CF923@.microsoft.com...[vbcol=seagreen]
> nm, got it.
> SELECT name, xtype
> FROM sysobjects
> WHERE id IN (
> SELECT id
> FROM sysdepends
> WHERE depid IN (
> SELECT id
> FROM sysobjects
> WHERE name = @.Table
> )
> )
> "JayKon" wrote:

proclarity question

Hi,

I have a question regarding Proclarity viewer and whether it can assists me.

I have a UDM as follows.

A fact table containing a sale for each salesman. There can be more that one row per salesman

This Fact is connected to a dimension dim_salesman that hold details about each salesman and other more dimensions.

Another fact containing a number of vacations each salesman took. There is more that one row per salesman.

This fact also connected to the same dim_salesman and and other more dimensions.

I want to inquire for example - how many vacations and sales each saleman had, sliced by the dim_saleman.

If I uses a pivot table excel to view a report that contains data from both fact tables I get wrong data.

Is the proclarity viewer able to browse 2 different fact tables and join them to a single report?

Thank for you help.

Hello. Both ProClarity Professional 6.2 and Excel 2007 can create reports from data in two separate fact tables.

This is not as much a client question but how you have related the two measure groups(fact tables) in the cube editors dimension usages tab. In SSAS2005 one cube can have several fact tables(measure group).

HTH

Thomas Ivarsson

Processing XML data in a SQL Server field

Has anyone cracked this? I want to process xml data which is contained within
a SQL server field. I'm using other fields in the table in the normal way.
The xml field actually contains numerous data values which I want to pass
into the the chart object.
So far there seems to be two options to me:
1) Export the entire row of the table to xml and then use a custom data
extension which knows about xml to read it. This route is described in Teos'
book.
2) Call out to a custom assembly and have it process the xml.
My views on the above are:
1) Seems clunky. Already have the data and now I need to export it to a file
and then read it back in?
2) Seems ok but how do I handle multiple items,, such as the data points?
Anyone have any other ideas before I wade in with my boots?!using a custom assembly works like a charm - We have entire XML docs stored
in blob fields and parse them on the fly to get the information we need form
them.

Processing XML data from a table

We have an XML column in a SQL Server 2005 table. Each row of this table contains one XML document.

I want to shred values from the XML documents and process these within a Data Flow. I want the Data Flow to execute once across a record set comprised of all of the XML documents.

I can shred the XML using a For-Each loop and XML Task. I'm kinda stuck on how I then get the data from variables into a Recordset or similar so that I can process this within single iteration of a Data Flow.

Or - is my approach incorrect? I seem to be building a verbose and clunky solution to this problem. I know I could accomplish the same in a pretty simple SQL statement using .value on the XML column... am I missing something? Is a SQL query just better suited to this problem?

Any help much appreciated.

James

" how I then get the data from variables into a Recordset" ... the script source component.

Processing User Activity Table

Hello,

I have an application that will be logging to a SQL Server 2000
database user user activity from several Windows 2003 terminal
servers. This information will be retrieved by monitoring the
Security logs of these servers (this part I know how to accomplish
already).

A table in the database, tblLogEntries, will contain the following
fields:
- ID = autoincrementing int
- LogTime = Date/Time the user activity was recorded in the security
log
- Username = User's login ID that the activity was recorded with
- Type = int, referencing a lookup table with the values of Logon,
Logoff, and possible other future items
- Server = The name of the server the activity was recorded on.

The only question I have is, can you offer a way to process the total
user login time during a given range using T-SQL.

For Example...

Given the table data:
ID LogTime Username Type Server
1 10-10-2003 8:30:00 Tom Logon SERVER-A
2 10-10-2003 8:45:00 Sarah Logon SERVER-A
3 10-10-2003 16:45:00 Tom Logoff SERVER-A
4 10-10-2003 17:00:00 Sarah Logoff SERVER-A
5 10-11-2003 8:30:00 Tom Logon SERVER-A
6 10-11-2003 8:45:00 Sarah Logon SERVER-A
7 10-11-2003 16:30:00 Sarah Logoff SERVER-A
8 10-11-2003 17:15:00 Tom Logoff SERVER-A

How would you receive the output:
User Logon Total Time for SERVER-A
Tom 17.0 hrs
Sarah 16.0 hrs

I know I can handle this type of processing on my ASP.NET front-end,
but I'm curious as to how easily it can be done by the database,
itself.

Thanks in advance for your assistance.Do:

SELECT UserName,
SUM( DATEDIFF( hour, Login, COALESCE( Logoff, Login ) ) )
FROM ( SELECT t1.Username, t1.LogTime,
( SELECT MIN( t2.LogTime )
FROM tbl t2
WHERE t2.Server = t1.Server
AND t2.UserName = t1.Username
AND t2.Type = 'LogOff'
AND t2.LogTime > t1.LogTime )
FROM tbl t1
WHERE t1.Type = 'Logon'
AND t1.Server = 'SERVER-A' ) D ( UserName, Login, Logoff )
GROUP BY UserName ;

--
- Anith
( Please reply to newsgroups only )|||Thanks for the reply.. the query looks good, but I'm having a little
trouble following it, and, as a result, cannot get it to work.

Would you mind explaining it a bit or point me to a reference for this
type of processing?

I'm not sure what that "D" operator is for, and I'll read up on the
COALESCE in BOL.

------------
http://members.tripod.com/kcourville0/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||D is an alias used for a derived table used in the query; when you use a
subquery construct directly in the FROM clause of an SQL statement with an
alias, in t-SQL, it is called a derived table. Pl. refer to SQL Server Books
Online for syntax and more details on this construct.

The logic is simple:

1. Retrieve the list of users with their login times and the subsequent
logout time (this is achieved using a subquery with MIN function). You can
run the derived table by itself to further understand how it is evaluated.

2. With the data in the derived table, the outer query, get the time
difference between the login time and the logout times and then find the
total using the SUM function.

--
- Anith
( Please reply to newsgroups only )|||Cool... I'll have to learn more of derived tables.

Thanks again for your response.

------------
http://members.tripod.com/kcourville0/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

processing time issue

Hi all,

I have a quick question regarding the processing time.

I created view in db for the count purposes with huge fact table. ( I'm using dimension table which

joins the 1 fact and 1 dimension to count the key for the fast data retrieval)

If I write the query for the count in management studio and it gives a results within 5 sec.

But, if I add this view in DSV and process the cubes than it takes around 50 minutes.

This causes due to the Fact table? or what else?

I don't know what to check and please give me some comments.

Thanks in advance.

Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||

Hi Darren,

Thanks for your good tips.

As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.

So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.

Please give me some comments.

Thanks.

|||

There would be a couple of possible approaches to improving the processing performance.

One would be to partition the fact table so that you only process recent records and not the full 153 million.

Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.

Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.

Processing Time

Hi,

I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:

[TimeEnd] – time that reports generation ends.

[TimeStart] - time that reports generation starts.

[TimeDataRetrieval] - amount of time spent running the data sources.

[TimeProcessing] - time spent processing the report.

[TimeRendering] - time spent generating the output format.

If this information is correct the following statement should be true:

([TimeEnd] - [TimeStart]) = ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

But it isn't, ([TimeEnd] - [TimeStart]) is always bigger then ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

Why does this happen?

Regards,

Rodrigo

That's interesting. I've never looked to check that. Are you seeing a big difference? how much is it a % of total execution time?

I suspect that TimeDataRetrieval, TimeProcessing, TimeRendering are each recorded by the subsystem resposible for each of those. I would expect there to be a small amount of overhead for the engine to do it's stuff and transition between the various phases between start and end.

|||

I have the following results for different reports:

End-Start

(ms)

Sum

(ms)

%

5030 3717 73,8966203 17437 12009 68,870792 5047 3374 66,851595 4733 3142 66,3849567 23170 15348 66,2408287 4780 3134 65,5648536 15750 10129 64,3111111 13017 7849 60,2980718 11153 6548 58,7106608 13170 7660 58,1624905 8830 5119 57,9728199

The first column is ([TimeEnd] - [TimeStart])

The second column is ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

How can i find the cause of this difference ?

Regards,

Rodrigo

|||

The difference is consistently large, too large. From your observations i.e. looking at the screen with a stopwatch, which is the "more correct" value?

|||

Do the reports have multiple datasets?

If multiple datasets are present and transactions are not used, SSRS will execute dataset queries in parallel. However, since there is only one data retrieval value in the execution log, it will pick the time of the longest data retrieval thread.

In addition, time data retrieval + processing + rendering does not cover all the time between the start and the end time of the request. Generally however, the other operations performed should just be small fraction compared to ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

-- Robert

Processing the TEXT datatype with TSQL

Hi;

I have a table with a TEXT datatype.
Its a comment field.

Right now the users who put in singlequotes are killing the web front
end.

The programmer responsible is fixing this issue but it might be a few
weeks until we get the patch.

I would like to write a trigger that whenever this field is updated it
will scan the text for single quotes ( and hard returns \r ) and
extract them.

I found some nice string functions in HELP.

Will these string functions work with the TEXT datatype in a TSQL
script/trigger?

Thanks in advance

SteveI handle quotes with the REPLACE function. All languages that I work with
has it.

Two single quotes in a row signify an escape sequence from the normal
interpretation of the single quote character. When two single quotes appear
together, they are interpreted by SQL as one literal single quote. All we
need do, then, is replace any single quote with two single quotes in strings
that we want interpreted literally by SQL.

This won't work on a Text datatype, however it does work on varchars and
stuff. Check your max len() on that field and see if it actually is using
more than the capacity of other datatypes and see about changing it to
varchar or something. This t-sql replaces one quote with two and would save
your web person endless hours of javascript'ing validation code!!

Select REPLACE(testColumn, char(39), char(39) + char(39)) as texta from
myTable

After all, quotes are valid characters too!!!

Good luck!

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0311140657.59346a15@.posting.google.c om...
> Hi;
> I have a table with a TEXT datatype.
> Its a comment field.
> Right now the users who put in singlequotes are killing the web front
> end.
> The programmer responsible is fixing this issue but it might be a few
> weeks until we get the patch.
> I would like to write a trigger that whenever this field is updated it
> will scan the text for single quotes ( and hard returns \r ) and
> extract them.
> I found some nice string functions in HELP.
> Will these string functions work with the TEXT datatype in a TSQL
> script/trigger?
> Thanks in advance
> Steve

Processing one row at a time

I'm populating a new table based on information in an existing table. The new table is a list of all "items" and contains a primary key. The old table is a database of receipts where items can appear many times in any order.

I have put together the off-the-shelf components to do this, using a lookup transformation to see if the item is already in the new table. Problem is, because there's so much repetition in the old table I need to process the old table one row at a time. Batch processing is generating errors because the lookup doesn't detect duplicates within the buffer.

I tried setting the "DefaultBufferMaxRows" property of the task to 1, but that doesn't seem to have any effect.

To get the data from the old table, I'm using an OLE DB source. To get the data into the new table, I'm using the OLE DB Command transformation with parameters to execute an INSERT statement.

This is a job I have to do exactly once, so I don't care if I have to run it overnight. I'd rather have a simple, easy to understand but inefficient script so I understand what it's doing completely.

Any help on forcing SSIS to process one row at a time?

Lee Silverman
JackRabbit Sports

Could you use an Aggregate transform to eliminate the duplicates, or a GROUP BY in the source select?|||

Could you use an Aggregate transform to eliminate the duplicates, or a GROUP BY in the source select?

Unfortunately no. Items wwith the same unique identifier have often had important changes made to the descriptive fields over the course of the last two or three years. When the Lookup finds a unique identifier that is already in the table, the branch that handles that then goes on to check to see if the descriptive info has changed, and if so updates the data in the table. Likewise, on the side of the lookup where the unique identifier was not found, that branch needs to see handle the case where the new item shares an item lookup number with a previous item, and if so marks the previous item as expired.

The data in the source table is ordered sequentially, and the logic of the script requires that each line item be processed one at a time. It's going to be slow, but once it's done it'll be done.

Lee Silverman
JackRabbit Sports

|||Could you use a For Each Container with the ADO enumerator to loop through your recordset of tableA and then do your manipulations/lookups within that?|||You could use an OLE DB Command to do your inserts and updates, and disable caching on the lookups. That will force the lookups to requery the database for each row. I'm not sure that will work in your case, but it might be worth a try.|||

How does one disable caching on lookups? Do you have to check "enable memory restriction" but then not check "enable caching"?

In any event, I solved the problem by writing a script in T-SQL, and I took the opportunity to teach myself how to use a cursor to scroll through the original table one line at a time. It's running now.

Lee

|||

Lee Silverman wrote:

How does one disable caching on lookups? Do you have to check "enable memory restriction" but then not check "enable caching"?

That is it.

Processing of Table in a report, delayed based on input parameter

I have input parameters which are dates in my report. My problem is I
have to validate these parameters before I display the table in the
report. Currently I am handling this by using the "Visibility"
property of the table. But I wanted to know is there is any other way,
I can stop the processing of the table till I validate the input date.
Thank You.Your query parameter can be an expression instead of directly mapped to the
dates. Then use code behind reports to validate the dates. Set the date to a
know bogus date that returns no data.
To map to an expression go to data tab, click on the ..., parameters tab.
Pick expression instead of the report parameter.
Another vaiation of the above. Have hidden parameters. Set the value of the
parameter to an expression that references the visible date parameter (and
again use code behind report). Then map this hidden parameter to your query
parameter. This hidden parameter can also be used for visibility. Hide the
data (by setting to a date that returns no data then it would be empty
anyway). Then show a textbox with your comment on why the date is invalid.
The above should at least point you in the right direction. Some of this
will be a hack but you should be able to get something acceptable worked
out.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
For instance
"ricky" <G.RekhaDevi@.gmail.com> wrote in message
news:55dde3ac-e215-44c3-8a9b-e3f20987e0b8@.i72g2000hsd.googlegroups.com...
>I have input parameters which are dates in my report. My problem is I
> have to validate these parameters before I display the table in the
> report. Currently I am handling this by using the "Visibility"
> property of the table. But I wanted to know is there is any other way,
> I can stop the processing of the table till I validate the input date.
> Thank You.

Processing jam-up

We have a number of jobs that process cubes when table loads complete. The last step in each of these jobs is to reprocess a "verification cube", which contains as linked measures the record counts from ALL other cubes. (We have a report which compares this against a direct SQL record count from the tables).

Anyway, we appear to have jammed up our Analysis Server (and had to restart) because one completing job issued a reprocess command for this "verification cube" while it (the verification cube) was still reprocessing in response to a previous job's completion.

OK, babbling aside, within a job I need to be able to ask "Is the verification cube currently processing, and if not, reprocess it".

Is this trivial, and I am not thinking correctly ? Any suggestions will be appreciated.

Which version of SQL Server 2000 or 2005 you are talking about?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

2005.

Just to mention . . . this has only happened once, and may just be one of those things . . . . But of course if we can better understand the problem and program to avoid it, that would be better.

|||

You should probably create a synchronization logic within your application and not let multiple jobs kick processing of the cube.

It is very hard to say whether processing of the cube is going on. The cube is not a single object it has multiple measure groups. Many processing commands, especially batch commands can include processing of any number of cube objects.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||Thanks Edward.

Processing Cube hangs

Hi,

I have a problem with processing my cube. My fact table (with telephone data) contains about 400,000 records... which is increasing rapidly (400,000 records is about 8 months of data)...
I have a few dimensions:
Dimension User: about 200 records
Dimension Line: about 200 records
Dimension Direction: 4 records
Dimension Date: 365 records for each year
Dimension TimeInterval: with 24 intervals

So far so good... when I process this dimension I have no problem....
However, when I add a dimension (CalledNumber, with exactly 101 records) the processing hangs as soon as it starts...

The SQL performed when processing the cube looks like this:
[CODE]
SELECT field1, field2,... fieldn
FROM table1, table2,.... tablem
WHERE
(table1.id=table2.table1id)
AND
(table2.id=table3.table2id)
...
[/CODE]

When I execute above SQL in the Query Analyser from SQL Server Enterprise Manager, it ALSO hangs...

I am not really suprised by that, because this SQL first create a huge table of 400,000 x 200 x 200 x 4 x 365 x 24 x 101 records and after that works through the WHERE statements to filter out the appropriate records.

for me it would be more logical to use the following code to process the cube, but that cannot be changed in Analysis Manager:
[CODE]
SELECT field1, field2,... fieldn
FROM table1
LEFT JOIN table2 ON (table1.id=table2.table1id)
....
LEFT JOIN tablem ON (tablem.id = tablem-1.tablemid)
[/CODE]

When I execute above SQL in the Query Analyser from SQL Servel Enterprise Manager, it does NOT hang, but performs the query in about 35 seconds....
But Analysis Manager does not allow me to change the SQL used for processing the cube...

What can I do to add more dimensions to my cube... (It will be more anyway after adding the CalledNumber dimension)?
any suggestions?

PS. forgot to mention: I use Sql Server 2000

For people who have the same problem... on another forum I got an answer (Thanks Darren Gosbell) which gave me the solution:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agoptimizing_1qr7.asp

Processing after new data

I'm trying to build a cube in the "real world" for the first time. I had my fact table populated with one day's worth of data, set up my time dimension using named calculations based on a datetime within the DS View of the fact table (a la the Larsen book), then built and deployed and everything worked great.

However, when I added the next day's worth of data into the fact table and tried to get it to process incrementally (and then fully), the processing fails and it looks like it's complaining about those named calculations that worked so well on day one. Any thoughts on what I might be missing here? Thanks

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fact_ALI, Column: YearOfBid, Value: 2006; Table: dbo_fact_ALI, Column: QuarterOfBid, Value: 2006Q2; Table: dbo_fact_ALI, Column: MonthOfBid, Value: 200607; Table: dbo_fact_ALI, Column: DayOfBid, Value: 20060702; Table: dbo_fact_ALI, Column: HourOfBid, Value: 00:00. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Time Key of Dimension: Dim Time from Database: ALI DM 2, Cube: ALI DM 2, Measure Group: Fact ALI, Partition: Fact ALI, Record: 577. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Fact ALI' partition of the 'Fact ALI' measure group for the 'ALI DM 2' cube from the ALI DM 2 database.

You must process each dimension before the cube if you have updated your source system since the last day.

HTH

Thomas Ivarsson

Process Hangs

the OK status is:

I build 12 fact table point to 12 partitions, and a father-son dimention. the dimention get to the fact table by the key column(son). I processFull,processUpdate the dimention, it run well. I processFull the cube or the partition, it run well too.

The Hangs status is:

I use the Process Dimention component in the SSIS. I chose the dimention, and use an ole db Source component point to the dimention table,and in it I use a query to chose the new row:"select from AgentDim where newdata=1". In the IDE, run the package, it's OK. Then I save the package to file system, and run it by Sql Server Job Agent.

Then it hangs, the job to run the package never stop. I stop the AS service and restart it. And I get into the Management Studio, and try to Process, then found it hangs.

I tried ProcessFull, ProcessAdd, ProcessUpdate, I tried deal with cube,dimention and any object, all hangs. It seem to AS is already dead.

God save me.

thanks.

Further Info: the query to chose the new row will return 0 rows.

Found Something, I think I found a bug.

The Hangs status happened when you do this:

The query to chose the new row returns 0 rows, that will make SSIS try to put a NULL set into AS,but AS don't know how to deal with such a NULL set, so it just keeps stop there.

This is really make me angry. We will get new rows everytime if we processAdd? Ofcause not.