Floyd's and Warshall's algorithms on relational DB schema

Hello,
I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
to find all possible paths (based on the PK - FK tables relations) in a
relational database schema (Graph).
Anyone some usefull tips?
Thanx,
Peter"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:1914E310-C649-4A24-8B95-32DD63B625F4@.microsoft.com...
> Hello,
> I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
> to find all possible paths (based on the PK - FK tables relations) in a
> relational database schema (Graph).
> Anyone some usefull tips?
> Thanx,
> Peter
See http://tinyurl.com/49gft.
There's a recursive solution first, that you can't use with SQL Server 2000
but you can with SQL Server 2005, which is followed by an iterative solution
.
The solutions were implemented for DB2 but if you add an @. to the front of
variable names and change END WHILE to END it should be legal T-SQL.
JAG|||Peter,
Here is a link to a naive transitive closure algorithm (keep taking
powers of the adjacency matrix until you get nothing new). It might
at least help you implement Warshall's or other graph algorithms.
http://groups.google.co.uk/groups?q=kass+transclose
Steve Kass
Drew University
PeterM wrote:

>Hello,
>I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
>to find all possible paths (based on the PK - FK tables relations) in a
>relational database schema (Graph).
>Anyone some usefull tips?
>Thanx,
>Peter
>

flexible column width based on data length in a matrix

I would like my matrix to have the column widths be the same as the maximum
length of data in that column, just like what we like to see when we are
looking at data in Excel. I do not want the data to wrap, I want it to push
the width of the column to the right so all data is on one line.
How do I do that?
StephanieOn Aug 22, 11:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I would like my matrix to have the column widths be the same as the maximum
> length of data in that column, just like what we like to see when we are
> looking at data in Excel. I do not want the data to wrap, I want it to push
> the width of the column to the right so all data is on one line.
> How do I do that?
> Stephanie
If I'm understanding you correctly, you should be able to select the
table/matrix cell and select F4 for the Properties window and then set
'Can Grow' below Layout to 'True.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||My understanding of the 'Can Grow' property is that it will allow it to grow
vertically, not horizontally. Is that correct? I've tried 'Can Grow' with
no luck.
"EMartinez" wrote:
> On Aug 22, 11:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
> wrote:
> > I would like my matrix to have the column widths be the same as the maximum
> > length of data in that column, just like what we like to see when we are
> > looking at data in Excel. I do not want the data to wrap, I want it to push
> > the width of the column to the right so all data is on one line.
> >
> > How do I do that?
> >
> > Stephanie
>
> If I'm understanding you correctly, you should be able to select the
> table/matrix cell and select F4 for the Properties window and then set
> 'Can Grow' below Layout to 'True.' Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Aug 28, 9:32 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> My understanding of the 'Can Grow' property is that it will allow it to grow
> vertically, not horizontally. Is that correct? I've tried 'Can Grow' with
> no luck.
> "EMartinez" wrote:
> > On Aug 22, 11:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
> > wrote:
> > > I would like my matrix to have the column widths be the same as the maximum
> > > length of data in that column, just like what we like to see when we are
> > > looking at data in Excel. I do not want the data to wrap, I want it to push
> > > the width of the column to the right so all data is on one line.
> > > How do I do that?
> > > Stephanie
> > If I'm understanding you correctly, you should be able to select the
> > table/matrix cell and select F4 for the Properties window and then set
> > 'Can Grow' below Layout to 'True.' Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Unfortunately, the Size: Width Property cannot be modified as part of
a table cell, etc. One other thing to consider is to verify that you
don't have merged cells when exporting to Excel. This could be do to
using table controls over each other (i.e., one on the top of the
report and one on the bottom) that have different widths or have cells
of different widths. For the most part, you will want all table/matrix
controls to all have the exact same physical dimensions amongst cells
and overall. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Flattening Parent Child, an issue, please help

Hello Experts,
Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?
Please help.
Thanks.
SoumyaDip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
How do modify the code to work for any level rather than hard coding
the level up to "5"?

If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.

If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.

--
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|||Hello,
I was wondering whether anyone has any sample "Dynamic SQL Code" that I
can use to resolve this issues.
Thanks for any help.
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?

>
If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.
>
If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.
>
>
--
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

|||>Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.|||Hi Celko,
Thanks for your input.
The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!
Any help is greatly appriciated.
Thanks.
Soumya

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. <<

>
I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding the level up to "5"? <<

>
One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.
>
A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.
>
The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.
>
You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.

--
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|||WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

>
You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.
>
>
>
--
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

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

Did you even look at the article I posted the link to?

What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.

It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)

I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.

What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
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|||Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.

I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.

I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

>
Did you even look at the article I posted the link to?
>
What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.
>
It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)
>
I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.
>
What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
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

Flat File to SQL table

I am looking to evaluate a text field in the row and change it to an integer in the sql table based on the text.

What is the best data flow tranformation for me to use inbetween the flat file source and the ole db destination?

it depends on what logic you are using for your evaluation but Derived Column will probably do it. If not, the script component.

-Jamie

|||Can you help with an example If then expression?|||

With the information you have provided, no. What evaluation do you want to do?

-Jamie

|||

Something like:

If [Treatment] = "No Deposit Required" then 1 else 0

I'm not sure how to write this in an expression.

|||

OK

[Treatment] == "No Deposit Required" ? (DT_I4)1 : (DT_I4)0

-Jamie

|||Thanks. your great..

Fixing my table based on Dbcc Showcontig results

Can someone please help me interpret this result set below and suggest
on way I can speed up my table? What changes should I make?

DBCC SHOWCONTIG scanning 'tblListing' table...
Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned........................: 97044
- Extents Scanned.......................: 12177
- Extent Switches.......................: 13452
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.17% [12131:13453]
- Logical Scan Fragmentation ..............: 0.86%
- Extent Scan Fragmentation ...............: 2.68%
- Avg. Bytes Free per Page................: 1415.8
- Avg. Page Density (full)................: 82.51%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Thank you.You should take a look at ttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Let us know if you have any questions after reading this. Keep in mind that it's quite possible that given your server workload, index defragmentation isn't at all necessary.

Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"SR" <yosonu@.socal.rr.com> wrote in message news:ba4c21f6.0406140740.7de00c3a@.posting.google.c om...
Can someone please help me interpret this result set below and suggest
on way I can speed up my table? What changes should I make?

DBCC SHOWCONTIG scanning 'tblListing' table...
Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned........................: 97044
- Extents Scanned.......................: 12177
- Extent Switches.......................: 13452
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 90.17% [12131:13453]
- Logical Scan Fragmentation ..............: 0.86%
- Extent Scan Fragmentation ...............: 2.68%
- Avg. Bytes Free per Page................: 1415.8
- Avg. Page Density (full)................: 82.51%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Thank you.|||"SR" <yosonu@.socal.rr.com> wrote in message
news:ba4c21f6.0406140740.7de00c3a@.posting.google.c om...
> Can someone please help me interpret this result set below and suggest
> on way I can speed up my table? What changes should I make?
> DBCC SHOWCONTIG scanning 'tblListing' table...
> Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
> TABLE level scan performed.
> - Pages Scanned........................: 97044
> - Extents Scanned.......................: 12177
> - Extent Switches.......................: 13452
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 90.17% [12131:13453]
> - Logical Scan Fragmentation ..............: 0.86%
> - Extent Scan Fragmentation ...............: 2.68%
> - Avg. Bytes Free per Page................: 1415.8
> - Avg. Page Density (full)................: 82.51%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Thank you.

http://www.sql-server-performance.c..._showcontig.asp

At first glance, the output seems fine - there is very little fragmentation,
and the scan density is high. If you're having performance issues with this
table, you may want to give some more information. In particular, the CREATE
TABLE and CREATE INDEX statements, plus a query which is performing badly,
and the reason why you believe this table is the problem (eg. the execution
plan for the query).

Simon

fixed page size

I have a page body and a page footer in my report. The page body has a table in its data region. It seems, based on the number of rows displayed, the footer section is adjusting itself tobe shown next to thelast row of the table. That is, if I want to show 15 records in a 2 page report, then in the first page there are 10 records and the footer appears after the 10 the record. But in the 2nd page, the footer is appearing immediately after the 5 record, the footer is appearing. the footer moves itself up to do so.
Question: Do do i make sure that the footer appears at the same place in all the reports irrespective of the data shown in the page body.
Thankx in advance.
Rajesh Jagadeesan.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.For print-oriented output formats (such as image and PDF), you'll get the
footer in the same place on each page.
For online-oriented output formats (such as HTML), the footer appears
immediately after the last item on the logical page, so as not to leave a
useless blank space (which would force the user to scroll unnecessarily).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:ukkjfTvcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a page body and a page footer in my report. The page body has a
table in its data region. It seems, based on the number of rows displayed,
the footer section is adjusting itself tobe shown next to thelast row of the
table. That is, if I want to show 15 records in a 2 page report, then in
the first page there are 10 records and the footer appears after the 10 the
record. But in the 2nd page, the footer is appearing immediately after the
5 record, the footer is appearing. the footer moves itself up to do so.
> Question: Do do i make sure that the footer appears at the same place in
all the reports irrespective of the data shown in the page body.
> Thankx in advance.
> Rajesh Jagadeesan.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

five connection limit

Hi!
Everybody know that MSDE (based on SQL Server 2000) has five-connection
limit after which its performance degrades. Do SQL server 2005 Express
edition also has five-connection limit? Or this limit was dropped?Hi
No connection limit in Express though a performance throttle, but it is
limited to use only 1 CPU, 1GB RAM and a database can have a maximum data
size of 4GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.s2ltu9n6n8ihmu@.iw2k.helpmicro.local...
> Hi!
> Everybody know that MSDE (based on SQL Server 2000) has five-connection
> limit after which its performance degrades. Do SQL server 2005 Express
> edition also has five-connection limit? Or this limit was dropped?|||The limit was removed in SQL 2005 Express. See
http://msdn2.microsoft.com/en-us/library/ms165672.aspx.
Happy Holidays
Dan Guzman
SQL Server MVP
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.s2ltu9n6n8ihmu@.iw2k.helpmicro.local...
> Hi!
> Everybody know that MSDE (based on SQL Server 2000) has five-connection
> limit after which its performance degrades. Do SQL server 2005 Express
> edition also has five-connection limit? Or this limit was dropped?

Fiscal year search

Hi

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

I have tried

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)

Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.Discovered answer on my own. Thanks anyway.

Twobridge wrote:

Quote:

Originally Posted by

Hi
>
I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this
>
Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00
>
I have tried
>
Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)
>
Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.

|||I've got a simlar problem - can you post your solution?
Dan

On Nov 29, 1:50 am, "Twobridge" <Twobri...@.gmail.comwrote:

Quote:

Originally Posted by

Discovered answer on my own. Thanks anyway.
>
>
>
Twobridge wrote:

Quote:

Originally Posted by

Hi

>

Quote:

Originally Posted by

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

>

Quote:

Originally Posted by

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

>

Quote:

Originally Posted by

I have tried

>

Quote:

Originally Posted by

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)

>

Quote:

Originally Posted by

Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.- Hide quoted text -- Show quoted text -

|||On 29 Nov 2006 01:28:49 -0800, Dan wrote:

Quote:

Originally Posted by

>I've got a simlar problem - can you post your solution?
>Dan

Hi Dan,

The best way to solve this is to have a calendar table (see
http://sqlserver2000.databases.aspf...dar-table.html),
with FiscalYear as one of it's columns.

Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:

DECLARE @.FiscalYear int
SET @.FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@.Year AS varchar) + '0701'
AND TheDate < CAST(@.Year + 1 AS varchar) + '0701'

You might want to read this as well:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo

Thanks - i'll take a look

Dan
On Nov 30, 9:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

On 29 Nov 2006 01:28:49 -0800, Dan wrote:
>

Quote:

Originally Posted by

I've got a simlar problem - can you post your solution?
DanHi Dan,

>
The best way to solve this is to have a calendar table (seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...),
with FiscalYear as one of it's columns.
>
Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:
>
DECLARE @.FiscalYear int
SET @.FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@.Year AS varchar) + '0701'
AND TheDate < CAST(@.Year + 1 AS varchar) + '0701'
>
You might want to read this as well:http://www.karaszi.com/SQLServer/info_datetime.asp
>
--
Hugo Kornelis, SQL Server MVP

first day of week by Week Number

I was looking for a solution for this problem - but none of what I
found in Internet helped - so finally I made something myself ( based a
little bit on someone's idea). I hope it will be useful to someone - or
maybe u'll see some bugs in it? please report.
there 2 assumptions
1. before executing this function u SET DATEFIRST 1
2. u have already an IsoW function implemented ( found in Books
Online as example for CREATE FUNCTION)
create function fdaywk (@.year int, @.w int )
returns datetime
as
begin
declare @.date datetime
begin
set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
if datepart(dw,@.date)>4
set @.date = @.date+8-datepart(dw,@.date)
else
set @.date= @.date-datepart(dw,@.date)+1
set @.date = dateadd(wk, @.w-1, @.date)
end
return(@.date)
endHi,
your solution wont work on other servers than US or english one, using
US regional settings.
The code in cause is :
set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
You must use the short ISO date encode wich is SQL Server World Wide
portable solution :
YYYYMMDD HH:MM:SS.nnn
Try it :
SELECT CAST('20050225 11:23:17.852' AS DATETIME) AS DT
This datetime format is corresponding to CONVERT(... 121)
A +
tomek a crit :
> I was looking for a solution for this problem - but none of what I
> found in Internet helped - so finally I made something myself ( based a
> little bit on someone's idea). I hope it will be useful to someone - or
> maybe u'll see some bugs in it? please report.
> there 2 assumptions
> 1. before executing this function u SET DATEFIRST 1
> 2. u have already an IsoW function implemented ( found in Books
> Online as example for CREATE FUNCTION)
> create function fdaywk (@.year int, @.w int )
> returns datetime
> as
> begin
> declare @.date datetime
> begin
> set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
> if datepart(dw,@.date)>4
> set @.date = @.date+8-datepart(dw,@.date)
> else
> set @.date= @.date-datepart(dw,@.date)+1
> set @.date = dateadd(wk, @.w-1, @.date)
> end
> return(@.date)
> end
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

First Data Processing Extension Problem

I am trying to write my first data processing extension. The project
is based upon the Event Log example in chapter 13 of Hitchhiker's
Guide to SQL Server 2000 Reporting Services. The assembly has been
copied to the various directories and the various config files have
been modified.
The extension is used in a report project and a shared datasource is
created using the extension. When a report's dataset is created the
Fields window in the IDE is not populated. When the command is run in
the data window of the designer a null pointer error occurs. In the
preview window the error indicates the connection is not valid. This
project is written in C# and the supplied sample is written in VB.NET.
The same error occurs using the VB assemblies.
The "connection not valid" exception is generated from command class'
ExecuteReader method. The connection member is found to be null w/in
the method. In the overloaded c'tor where the connection argument is
assigned to the connection member, the argument is not null and the
member is not null after it has been assigned to. I am baffled as to
what/why/how this occuring. Suggestion?
Equally or more important how does one setup a data processing
extension project for debugging?
thank you
A.G.All issues resolved. Using System.Diagnostics.Debugger.Break I was
able to access the CLR debugger and see what was wrong. First problem
was a typo in a string. Then an indexing error was found and then the
extension 'worked' returning just a single record.
A closer examination of the datareader's Read method revealed a number
of issues involving the iteration of the event log. Changing the
method so it read one record at a time made everything work as
expected. In the sample code the entire event log is iterated when the
Read method is invoked.
Fortunately the exercise in frustration was offset by the learning and
understanding experience.
regards
A.G.
On Tue, 13 Dec 2005 18:37:43 -0500, Registered User
<n4jvp@.ix.netcom.com> wrote:
>I am trying to write my first data processing extension. The project
>is based upon the Event Log example in chapter 13 of Hitchhiker's
>Guide to SQL Server 2000 Reporting Services. The assembly has been
>copied to the various directories and the various config files have
>been modified.
>The extension is used in a report project and a shared datasource is
>created using the extension. When a report's dataset is created the
>Fields window in the IDE is not populated. When the command is run in
>the data window of the designer a null pointer error occurs. In the
>preview window the error indicates the connection is not valid. This
>project is written in C# and the supplied sample is written in VB.NET.
>The same error occurs using the VB assemblies.
>The "connection not valid" exception is generated from command class'
>ExecuteReader method. The connection member is found to be null w/in
>the method. In the overloaded c'tor where the connection argument is
>assigned to the connection member, the argument is not null and the
>member is not null after it has been assigned to. I am baffled as to
>what/why/how this occuring. Suggestion?
>Equally or more important how does one setup a data processing
>extension project for debugging?
>thank you
>A.G.

First and Last day of week

Hi All
I have a report that brings back data based on the week a user selects.
The user will select a week number (e.g. week 1) then the report will automatically generate the first date (Monday) and last date of that week (Sunday), based on a whole week for the current year.
So, user selects week 3 for this year. The report generates the first date of the week: 16/01/2006 and last date of the week: 22/01/2006.

Note: I’m in the UK, so I am using the UK date format.

Try this:

Code/

Declare @.DOW As Char(10), @.weekdate Datetime, @.Monday DateTime, @.Sunday DateTime,

@.Working As Int, @.WeekNo Int

-- Find first day of year

Set @.WeekDate = (select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--Find a date in this week - any date

Set @.Working = @.WeekNo * 7

Select @.WeekDate = DateAdd(dd,@.Working,@.WeekDate)

Get day of week and subtract number of days to get the Monday

Set @.DOW = DateName(dw,@.Weekdate)

Set @.Monday = @.Weekdate

Set @.Monday = Case @.DOW

When 'Sunday' Then DateAdd(dd,-6,@.WeekDate)

When 'Saturday' Then DateAdd(dd,-5,@.WeekDate)

When 'Friday' Then DateAdd(dd,-4,@.WeekDate)

When 'Thursday' Then DateAdd(dd,-3,@.WeekDate)

When 'Wednesday' Then DateAdd(dd,-2,@.WeekDate)

When 'Tuesday' Then DateAdd(dd,-1,@.WeekDate)

Else @.Monday

End

Set @.Sunday = DateAdd(dd,6,@.Monday)

Select @.Monday, @.Sunday

/Code

You feed in @.WeekNo

Firing DTS through Window Based Forms in VB.net

Here is what I've got so far...for some reason it's not firing off:

<code>

PrivateSub Button1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Button1.Click

Dim dtsp2AsNew DTS.Package

dtsp2.LoadFromSQLServer("jfgp34", "sa", "@.jfgp#1", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "Test Northwind", "")

dtsp2.Execute()

EndSub

</code>

Any one with ideas helps out alot. Thanks in advance everyone.

RB

Check out the DTS Cookbook for .NET,http://www.sqldev.net/dts/DotNETCookBook.htm
|||This approach is more SQL server centric, and less reliant onVB.NET/C#.NET. It's basically a technique for triggering a DTS packagefrom a stored procedure, which can run from your .NET program withoutmuch effort.
http://www.mssqlcity.com/FAQ/Devel/DTSviaQA.htm
With that said, I've only used a similar technique to the one referenced in the previous post.
Jason
|||

Jason,

Thanks for the site. That looks like it is going to do the trick I'll let you know if I need any thing else.

Thanks again.

RB