Finding column use with syscomments

In SQL 2000, I know that "Display Dependencies" (and probably the
Sysdepends table) are not accurate.
Does the syscomments field for a view always contain the correct and
current view definition?
I need to find all uses of a given field across all views (there are about
150 views). Is looking in syscomments, or Information_Schema, going to be
reliable? Are there better ways? I have looked in Google and haven't
found anything yet. Still looking though...
Thanks.
David WalkerYou can refresh all your views and then use system views
information_schema.columns.
Example:
use northwind
go
declare @.ts sysname
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'view'
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_refreshview ''' + quotename(@.ts) + N'.' +
quotename(@.tn) + ''''
exec sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
declare @.cn sysname
set @.cn = 'OrderID'
select
*
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsView') = 1
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_name = @.cn
order by
table_schema,
table_name,
ordinal_position
go
AMB
"DWalker" wrote:

> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker
>|||> Does the syscomments field for a view always contain the correct and
> current view definition?
AFAIK, yes. With one exception. If you use sp_rename to rename a view, the i
nfo in syscomments will
have the old name (in the CREATE VIEW part).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNGP09.phx.gbl...[co
lor=darkred]
> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker[/color]|||And another one is that if you use "select * ..." then you will not find any
column name in the syscomments, but you will in the syscolumns. That is the
reason why after refreshing the view, I selected from
information_schema.columns and not from syscomments.
AMB
"Tibor Karaszi" wrote:

> AFAIK, yes. With one exception. If you use sp_rename to rename a view, the
info in syscomments will
> have the old name (in the CREATE VIEW part).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNG
P09.phx.gbl...
>
>|||"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:B0934023-3D8D-46DE-AA73-C27AC81C8F73@.microsoft.com:

> And another one is that if you use "select * ..." then you will not
> find any column name in the syscomments, but you will in the
> syscolumns. That is the reason why after refreshing the view, I
> selected from information_schema.columns and not from syscomments.
>
> AMB
>
Thanks to you both. I didn't know about refreshing the views. I'll
steal that code from you, AMB, and keep it in my database. There are
times when I want Display Dependencies in EM to give me the right
answer, and it looks like refreshing the views when I need this
information, is the way to go.
Thanks!
Davidsql

Find the greatest of three columns per each row and display

Hi,

i have a problem where in i have to display the greatest marks scored by each student.

How can i do this? Is there any built in TSQL function.

Rgds..,

Aazad

You either use a CASE Statement or Pivot the data to make a relational query possible through the aggregation functions.

SELECT CASE WHEN COlA > COLB THEN COLA ELSE

(CASE WHEN COLB > COLC THEN COLB ELSE COLC END)

END

FROM SomeTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I worked. Nice one. I got the other way too..!

Rgds..,

Aazad.

Filters - need help

I am having issues applying a filter to my report.
I have one dataset that i want to filter and display serveral times in one
report.
The query is complicated and somewhat slow(yes i am working on optimizing
it), so i want to pull it once, and then use it in 4 different tables in my
report.
The first table, will be unfiltered, the others will be filtered by one of
the fields in my dataset that returns an int datatype, none of the values are
null.
I tried following the instructions here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
but it doesn't work.
When i preview the report, it returns no build errors or warnings, but i get
this dialog:
Processing Errors
An error has occurred during the report processing.
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
----
What am i doing wrong?
This is driving me nuts...
--
RyanWhat is the filtering expression?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
>I am having issues applying a filter to my report.
> I have one dataset that i want to filter and display serveral times in one
> report.
> The query is complicated and somewhat slow(yes i am working on optimizing
> it), so i want to pull it once, and then use it in 4 different tables in
> my
> report.
> The first table, will be unfiltered, the others will be filtered by one of
> the fields in my dataset that returns an int datatype, none of the values
> are
> null.
> I tried following the instructions here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> but it doesn't work.
> When i preview the report, it returns no build errors or warnings, but i
> get
> this dialog:
> Processing Errors
> An error has occurred during the report processing.
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> the
> filter expression.
> ----
> What am i doing wrong?
> This is driving me nuts...
> --
> Ryan|||Expression: Fields!ProcessStage.Value>=80
I think i found part of my issue.
When you select a field from the drop down in Table
Properties->Filters/Expressions, it leaves a "=" at the beginning. By
removing that, i no longer get the error. But it does not seem to filter the
data either...
"Lev Semenets [MSFT]" wrote:
> What is the filtering expression?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> >I am having issues applying a filter to my report.
> >
> > I have one dataset that i want to filter and display serveral times in one
> > report.
> > The query is complicated and somewhat slow(yes i am working on optimizing
> > it), so i want to pull it once, and then use it in 4 different tables in
> > my
> > report.
> > The first table, will be unfiltered, the others will be filtered by one of
> > the fields in my dataset that returns an int datatype, none of the values
> > are
> > null.
> >
> > I tried following the instructions here:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > but it doesn't work.
> >
> > When i preview the report, it returns no build errors or warnings, but i
> > get
> > this dialog:
> > Processing Errors
> > An error has occurred during the report processing.
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > the
> > filter expression.
> > ----
> > What am i doing wrong?
> > This is driving me nuts...
> > --
> > Ryan
>
>|||Ok;
I figured i out, picky little application:
Expression: =Fields!ProcessStage.Value
Operator: >=Value: =80
Thanks...
"Ryan" wrote:
> Expression: Fields!ProcessStage.Value>=80
> I think i found part of my issue.
> When you select a field from the drop down in Table
> Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> removing that, i no longer get the error. But it does not seem to filter the
> data either...
>
> "Lev Semenets [MSFT]" wrote:
> > What is the filtering expression?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > >I am having issues applying a filter to my report.
> > >
> > > I have one dataset that i want to filter and display serveral times in one
> > > report.
> > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > it), so i want to pull it once, and then use it in 4 different tables in
> > > my
> > > report.
> > > The first table, will be unfiltered, the others will be filtered by one of
> > > the fields in my dataset that returns an int datatype, none of the values
> > > are
> > > null.
> > >
> > > I tried following the instructions here:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > but it doesn't work.
> > >
> > > When i preview the report, it returns no build errors or warnings, but i
> > > get
> > > this dialog:
> > > Processing Errors
> > > An error has occurred during the report processing.
> > > The processing of filter expression for the table 'table1' cannot be
> > > performed. The comparison failed. Please check the data type returned by
> > > the
> > > filter expression.
> > > ----
> > > What am i doing wrong?
> > > This is driving me nuts...
> > > --
> > > Ryan
> >
> >
> >|||I tore my hair out for about 24 hours until I found this post.. Thanks
Ryan - likely I would be tearing my hair out for days more had I not
happened on this. The nest question is when will someone from MS get
around to addressing this "feature."|||Thanks for the post Ryan...you bet the application is really picky...I hope
the new version is really fine tuned in such areas
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||It is so helpful. Thanks!!!!!
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||Thanks Ryan!
I still have issue with the following combination
Expression: =Fields!X.Value
Operator: =Value: =0
(Operator: < or > worked fine)
I got around this problem by casting X in my sql statement to a varchar(1)
and setting -> Value: 0
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >

Filters

A common question is on reports with parameters - is there some way to also
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>

Filtering SQL statement with datepart

Hi!

Im making a page where I want to display this weeks information & news as a default setting.

What do I have to do to make it work (Im new to .Net but used to ASP 3 & MySql)?

My idea is to store the week number in a variable which is updated att Page_load and then
to use this variable in the following sql statement that gets news and info from the MsSql
database where weeknumber is = the defined variable.

So how do I store weeknumber in a variable?
AND
How do I use this variable in the SQL statement?

Weeknumber can be store in a Session variable.

There is a function in SqlServer called DateDiff() with many options, you can get the number of week between 2 date. than compare this nubmer with the Session variable.

Hope this help