Anyone else had this error situation?

So, for the past two weeks or so every morning Reporting services has come up
with the report error "Sorting cannot be performed on table1". For some odd
reason though it does the error on all reports with any sorting whatsoever.
I've checked and double checked my sorting expressions
(=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, 0) and =iif(Parameters!Direction.Value
= "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
Direction is a drop down param which is either Ascending or Descending and
SortBy is a drop down param that contains all table columns) and have had
others look at it as well and the logic is good for it. What ultimately
remedies it is a restart of IIS on the server we run the reports on.
So has anyone else experienced this problem? If so I could use any
information as to the possible cause or fix to this.Please try this - note the CInt() function call:
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
etc.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <Timm@.discussions.microsoft.com> wrote in message
news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
> So, for the past two weeks or so every morning Reporting services has come
> up
> with the report error "Sorting cannot be performed on table1". For some
> odd
> reason though it does the error on all reports with any sorting
> whatsoever.
> I've checked and double checked my sorting expressions
> (=iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, 0) and
> =iif(Parameters!Direction.Value
> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
> Direction is a drop down param which is either Ascending or Descending and
> SortBy is a drop down param that contains all table columns) and have had
> others look at it as well and the logic is good for it. What ultimately
> remedies it is a restart of IIS on the server we run the reports on.
> So has anyone else experienced this problem? If so I could use any
> information as to the possible cause or fix to this.|||The column names in use are string values (non-numeric) so using CInt throws
back a
different error that will cause the report to not work 100% of the time. I'm
toying with
an idea that this is something to do with the reporting services and not the
reports
themselves since restarting IIS resolves the issue for a day (and the next
day the error
occurs again).
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Please try this - note the CInt() function call:
> =iif(Parameters!Direction.Value = "Ascending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> =iif(Parameters!Direction.Value = "Descending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> etc.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <Timm@.discussions.microsoft.com> wrote in message
> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>|||If they are strings, then this explains why it does not work. Use the
following expression then - note that 0 is replaced with an empty string:
=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, "")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
> The column names in use are string values (non-numeric) so using CInt
> throws back a
> different error that will cause the report to not work 100% of the time.
> I'm toying with
> an idea that this is something to do with the reporting services and not
> the reports
> themselves since restarting IIS resolves the issue for a day (and the next
> day the error
> occurs again).
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>|||I did that change and this morning we got that error still. After restarting
IIS it now works again.
Is this a known issue or is this something that may be unique to our system?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> If they are strings, then this explains why it does not work. Use the
> following expression then - note that 0 is replaced with an empty string:
> =iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, "")
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <tim@.gumdropbooks.com> wrote in message
> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>|||I haven't seen this before. Are you sure you don't have any report execution
caching set up?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:eHjfzC3ZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>I did that change and this morning we got that error still. After
>restarting IIS it now works again.
> Is this a known issue or is this something that may be unique to our
> system?
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> If they are strings, then this explains why it does not work. Use the
>> following expression then - note that 0 is replaced with an empty string:
>> =iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, "")
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <tim@.gumdropbooks.com> wrote in message
>> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For
>> some odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What
>> ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>>
>|||I am new to reporting services, so my answer may not be correct.
I had same error, when 2nd parameter was changed to cint, error went
away.
Something like this -
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.
I think you answered your own question Profile the database, filter the
user, save the trace to a table and query the table for the results...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>
|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>
|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.I think you answered your own question :) Profile the database, filter the
user, save the trace to a table and query the table for the results...
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.I think you answered your own question Profile the database, filter the
user, save the trace to a table and query the table for the results...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to force collation/sort-order as part of the ODBC connection?

In the situation where the ODBC client app assumes a specific collation or, more specifically, a specific sort-order (case-sensitive vs. case-insensitive) but the collation/sort-order of the SQL Server Database and/or Table is unknown, is there a way to coerce the ODBC connection to always use a case-sensitive/case-insensitive sort order?

I believe case-sensitivity is a characteristic of the database you're connecting to. I don't know of a client setting for this.|||Collation is specified during server installation or db/table creation time. I don't believe you can change it on the fly via ODBC API.|||

Thanks Warren and Ricky. I suspected as much, just wasn't sure.

Any way to force collation/sort-order as part of the ODBC connection?

In the situation where the ODBC client app assumes a specific collation or, more specifically, a specific sort-order (case-sensitive vs. case-insensitive) but the collation/sort-order of the SQL Server Database and/or Table is unknown, is there a way to coerce the ODBC connection to always use a case-sensitive/case-insensitive sort order?

I believe case-sensitivity is a characteristic of the database you're connecting to. I don't know of a client setting for this.|||Collation is specified during server installation or db/table creation time. I don't believe you can change it on the fly via ODBC API.|||

Thanks Warren and Ricky. I suspected as much, just wasn't sure.