Fetch a certain row

Hi.
I have a dataset with a two column table:
name | value
What I'm trying to do is to make a textbox in the report designer to fetch a
certain field (value) from the table based on the name field.
How to fetch that certain cell from the dataset.
Any help appreciated.
AlekWhere do you select the name for which you want the value
You could do this with a parameter which displays the name and returns the
value. Then in the Text box
=Parameters!myparam.value
You could maybe filter the where clause in the sql or in the data set..
You could write a funcion which accepts some value and returns the name of
the field in the data set... Then use
=Fields(Code.GetFieldname("mypassedinvalue")).Value in the textbox
Hope this helps... If I do not understand your question, please send more
details
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alek Lynge" <alekl@.penguin.dk> wrote in message
news:OUSV$MI2FHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi.
> I have a dataset with a two column table:
> name | value
> What I'm trying to do is to make a textbox in the report designer to fetch
> a certain field (value) from the table based on the name field.
> How to fetch that certain cell from the dataset.
> Any help appreciated.
> Alek
>|||Hi, Wayne.
Thanx for your quick answer. However I'm still a bit lost in the VBScript
world, as I'm usually coding in c#.
It is easiest to explain with a sample data example:
I have a dataset with multiple tables. The one I need is called
InvoiceTemplate and has its tableadapter implemented to fetch data from the
sql server backend with a stored proc, which accepts a single parameter: an
integer determining the country.
When the dataset is filled I get the following data in my InvoiceTemplate
table:
Columns: name, value
Sample data (ntext, ntext):
'iName', 'Lene Espersen'
'iNumber', 'Faktura nr.'
'iUnits', 'Antal'
'iPrice', 'Pris'
Etc.
So I've got a textbox in the report designer which I need to fill with the
value from the row containing 'iUnits' (get the data 'Antal')?
How to make an elegant expression solution to this? Essentially I'm trying
to escape from table pivoting, as sql server 2000 has poor support for that.
Best regards
Alek
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O7y8AmI2FHA.2616@.tk2msftngp13.phx.gbl...
> Where do you select the name for which you want the value
> You could do this with a parameter which displays the name and returns
> the value. Then in the Text box
> =Parameters!myparam.value
> You could maybe filter the where clause in the sql or in the data set..
> You could write a funcion which accepts some value and returns the name of
> the field in the data set... Then use
> =Fields(Code.GetFieldname("mypassedinvalue")).Value in the textbox
> Hope this helps... If I do not understand your question, please send more
> details
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Alek Lynge" <alekl@.penguin.dk> wrote in message
> news:OUSV$MI2FHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hi.
>> I have a dataset with a two column table:
>> name | value
>> What I'm trying to do is to make a textbox in the report designer to
>> fetch a certain field (value) from the table based on the name field.
>> How to fetch that certain cell from the dataset.
>> Any help appreciated.
>> Alek
>

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Timnot entirely sure why my browser posted my original message again!
sorry.

thanks guys for your help so far.
yes I have indexes on the columns

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

Tim|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

The DROP at the end is just cleanup. The script was meant to show how
you create an indexed view and how to use it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.

Thanks

Tim|||"C10B" <tswalton@.gmail.comwrote in message
news:1172494748.828877.126110@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.
>
Thanks
>
Tim
>

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||

Quote:

Originally Posted by

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

The requirement is simply this...

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"

with unlimited segmenting of the data really.

So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

Any other tips?

Thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?

No. An indexed view is materialised, and this particular view has one
row per webpage with the count of visitors. An alternative would be have
a separate table with the counts, and update that table through a trigger.
But why roll our own when SQL Server can do the job for us?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"C10B" <tswalton@.gmail.comwrote in message
news:1172502298.291068.60420@.s48g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>

Quote:

Originally Posted by

>If you want more help, I suggest you give us a complete DDL of your
>database, some real example data and perhaps we can do better.

>
>
The requirement is simply this...
>
I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>

Yes, we understand the requirements. That doesn't eliminate the value of a
DDL and sample data.

Quote:

Originally Posted by

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.
>

Define slow?

(my former employer did queries similar to this in subsecond times.)

Quote:

Originally Posted by

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.
>
Any other tips?

Yes, post a full DDL.

Quote:

Originally Posted by

>
Thanks
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>
"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

You did not say anything about wanting to get the counts for a certain
day, so I interpreted your question that you wanted total counts of
the entire table. Whence my suggestion of an indexed view.

If you are not able to relate clearly what you want to do, then the
answers you get will be equally imprecise.

Quote:

Originally Posted by

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

There is no need for that. If you always want to run these count
queries for a certain period, make sure that there is a clustered
index on the datetime column in your table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Tim"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

First question, do you have an index on the table.

That should help.

Quote:

Originally Posted by

>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?

A non-clustered index on place would help, as SQL Server then only
would have to scan that index, and not the entire difference. But it
seems that the table has two columns. In such case, the index would not
reduce execution time that much.

A better alternative may be to define an indexed view that maintains
the count:

CREATE TABLE pagehits (pageid varchar(20) NOT NULL,
viewtime datetime NOT NULL,
PRIMARY KEY (pageid, viewtime)
)
go
CREATE VIEW pagecount WITH SCHEMABINDING AS
SELECT pageid, cnt = COUNT_BIG(*)
FROM dbo.pagehits
GROUP BY pageid
go
CREATE UNIQUE CLUSTERED INDEX pagecount_ix ON pagecount(pageid)
go
SELECT TOP 10 pageid, cnt
FROM pagecount WITH (NOEXPAND)
ORDER BY cnt
go
DROP VIEW pagecount
DROP TABLE pagehits

I added the NOEXPAND hint to the query, since it's only on Enterprise
Edition, the optimizer considers indexed views.

Note that this could have effect on performance when writing to the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fastest returning query would be on a separate table that maintains the
count to the granularity you would require in your output (week, day, or
hour). A query that requires more than hourly granularity can still be done
on the source table.

For each new week|day|hour add a row for each page to be tracked. This can
be done dynamically during monitoring but it is better to do it ahead of
time.

Then put an insert trigger on your million row table that will increment the
count on the proper row of the table.

An alternative would be a temp table that contains a subset copy of the
source data for the time range most likely to queried. For example a table
that contains the last 31 days of data, the 32nd day of data
deleted/archived every night.

"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

|||erland - you are quite right, I could have worded my first post a
little better

greg - I don't know what a DDL is

russ - thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

greg - I don't know what a DDL is

DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.

If I had had a penny for every time someone says DDL in answer, without the
person asking having no idea what it means, I would be a rich man now.

Quote:

Originally Posted by

russ - thanks

What Russ proposed is the same idea that I proposed, but rather than relying
SQL Server updating an indexed view, he suggested a separate table that you
update through a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98E480B95D736Yazorman@.127.0.0.1...

Quote:

Originally Posted by

C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

>greg - I don't know what a DDL is

>
DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.
>
If I had had a penny for every time someone says DDL in answer, without
the
person asking having no idea what it means, I would be a rich man now.
>

Quote:

Originally Posted by

>russ - thanks

>
What Russ proposed is the same idea that I proposed, but rather than
relying
SQL Server updating an indexed view, he suggested a separate table that
you
update through a trigger.
>

Indexed view is probably best, I just like having more control.

Quote:

Originally Posted by

>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

failure during updation

In SQL SERVER if there is failure during updation of certain rows, what will be the state?The state will be like New Jersey (specifically, Newark), I think.

You need to provide more information for someone to accurately define the state, however you can assume that if the Update fails, the rows you attempted to Updated did not get updated.

Whether or not that's an error depends on your application. If your application isn't handling the 'failed state' (North Dakota?), then you'll be in a state of mess a chusetts, I think.|||That's a "politically" overloaded stament, but the gutt is right on the money, - the transaction (explicit or implicit, regardless of error handling) will fail and the data will be in "pre-update" state, like what Texas used to be.|||:D this was an interesting thread :))