Article on RS Forms Authentication - Part II

Here comes the second part. Learn how to implement role-membership and
troubleshoot custom security.
http://www.devx.com/dotnet/Article/27133/0/page/1
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---Hi Teo
I have succesfully implemented the FAuth in my reporting services , but my
problem is I cannot use the rs.exe publishreports.rss script to deploy
reports as it gives the follwoing error: could not connect to
http://ws034/reportserver/reportservice.asmx My understanding is because it
is not passing the sqlauth cokie and hence could not connect this is how I am
calling the script:
rs -i C:\PublishReports.rss -s http://ws034/ReportServer/reportservice.asmx
-u admin -p admin -v filePath="C:\unzipped\11946\Reports" -v
sqlServerName="ws034" -v parentFolder="ScriptUpload"
any assistance would be most appreciated.
thanks in advance
"Teo Lachev [MVP]" wrote:
> Here comes the second part. Learn how to implement role-membership and
> troubleshoot custom security.
> http://www.devx.com/dotnet/Article/27133/0/page/1
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
>
>|||Yes, this stems from the fact that the cookie is not supplied. Remember, you
have to call LogonUser first to get the cookie. You may find my WinForm
sample useful to find out how to maintain and send the cookie from a non-web
client.
http://prologika.com/blog/archive/2004/08/27/169.aspx
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"shahab" <shahab@.discussions.microsoft.com> wrote in message
news:88A44FCD-5BD2-494F-BBB5-0E86C95AA36F@.microsoft.com...
> Hi Teo
> I have succesfully implemented the FAuth in my reporting services , but my
> problem is I cannot use the rs.exe publishreports.rss script to deploy
> reports as it gives the follwoing error: could not connect to
> http://ws034/reportserver/reportservice.asmx My understanding is because
> it
> is not passing the sqlauth cokie and hence could not connect this is how I
> am
> calling the script:
> rs -i C:\PublishReports.rss -s
> http://ws034/ReportServer/reportservice.asmx
> -u admin -p admin -v filePath="C:\unzipped\11946\Reports" -v
> sqlServerName="ws034" -v parentFolder="ScriptUpload"
> any assistance would be most appreciated.
> thanks in advance
> "Teo Lachev [MVP]" wrote:
>> Here comes the second part. Learn how to implement role-membership and
>> troubleshoot custom security.
>> http://www.devx.com/dotnet/Article/27133/0/page/1
>> --
>> Hope this helps.
>> ---
>> Teo Lachev, MVP [SQL Server], MCSD, MCT
>> Author: "Microsoft Reporting Services in Action"
>> Publisher website: http://www.manning.com/lachev
>> Buy it from Amazon.com: http://shrinkster.com/eq
>> Home page and blog: http://www.prologika.com/
>> ---
>>|||"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in
news:eHxxOG7EFHA.2600@.TK2MSFTNGP09.phx.gbl:
> Yes, this stems from the fact that the cookie is not supplied.
> Remember, you have to call LogonUser first to get the cookie. You may
> find my WinForm sample useful to find out how to maintain and send the
> cookie from a non-web client.
> http://prologika.com/blog/archive/2004/08/27/169.aspx
>
I am trying to implement the role-based piece of the security extension in
a web farm environment. I think that I am having my issues because of the
use of "HttpContext.Current.Cache" -- would this be an issue in a web farm?
If so, do you have any alternatives other than to not cache the roles?
Thanks!|||I don't think caching will cause any issues. Basically, if the load balanced
server doesn't find the roles in its cache, it will load them from the data
store. So, you will end up with as many cached instances of the user roles
as the number of servers but this is just a performance optimization
technique and shouldn't be a problem.
That's said though, as the article mentiones, you should be aware of memory
constraints that caching may cause. For example, if you have thousands of
users, you may consider expiring the cache on a regular basis.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Jason A. Bentley" <jason_a_bentley@.hotmail.com> wrote in message
news:Xns95FF6B0C984EDjasonabentleyhotmail@.207.46.248.16...
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in
> news:eHxxOG7EFHA.2600@.TK2MSFTNGP09.phx.gbl:
>> Yes, this stems from the fact that the cookie is not supplied.
>> Remember, you have to call LogonUser first to get the cookie. You may
>> find my WinForm sample useful to find out how to maintain and send the
>> cookie from a non-web client.
>> http://prologika.com/blog/archive/2004/08/27/169.aspx
> I am trying to implement the role-based piece of the security extension in
> a web farm environment. I think that I am having my issues because of the
> use of "HttpContext.Current.Cache" -- would this be an issue in a web
> farm?
> If so, do you have any alternatives other than to not cache the roles?
> Thanks!|||I should've given more thought on this, Jason.
I forgot the fact that article code implements role caching in LogonUser.
This WILL present an issue with load balancing since LogonUser is called
once during the cookie lifetime and the custom extension will not find the
roles on the second server during user authorization (assuming that
authentication takes place on server one and the user is re-directed and
authorized on server two). So, one workaround is to move role caching to the
CheckOperations helper function in the Authorization extension. Another
workaround of course is to use sticky sessions.
Thanks for bringing this up.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:u1aseJKFFHA.2572@.tk2msftngp13.phx.gbl...
>I don't think caching will cause any issues. Basically, if the load
>balanced server doesn't find the roles in its cache, it will load them from
>the data store. So, you will end up with as many cached instances of the
>user roles as the number of servers but this is just a performance
>optimization technique and shouldn't be a problem.
> That's said though, as the article mentiones, you should be aware of
> memory constraints that caching may cause. For example, if you have
> thousands of users, you may consider expiring the cache on a regular
> basis.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Jason A. Bentley" <jason_a_bentley@.hotmail.com> wrote in message
> news:Xns95FF6B0C984EDjasonabentleyhotmail@.207.46.248.16...
>> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in
>> news:eHxxOG7EFHA.2600@.TK2MSFTNGP09.phx.gbl:
>> Yes, this stems from the fact that the cookie is not supplied.
>> Remember, you have to call LogonUser first to get the cookie. You may
>> find my WinForm sample useful to find out how to maintain and send the
>> cookie from a non-web client.
>> http://prologika.com/blog/archive/2004/08/27/169.aspx
>>
>> I am trying to implement the role-based piece of the security extension
>> in
>> a web farm environment. I think that I am having my issues because of
>> the
>> use of "HttpContext.Current.Cache" -- would this be an issue in a web
>> farm?
>> If so, do you have any alternatives other than to not cache the roles?
>> Thanks!
>|||Teo thanks for your article I have successfully implemented the Forms based
authentication. My biggest problem now is I cannot run any reports that have
ORACLE datasource. It comes with an error that logon failed!
Do you know some workaround. Please any assistance would be most appreciated.
Thanks
============================================"Teo Lachev [MVP]" wrote:
> I should've given more thought on this, Jason.
> I forgot the fact that article code implements role caching in LogonUser.
> This WILL present an issue with load balancing since LogonUser is called
> once during the cookie lifetime and the custom extension will not find the
> roles on the second server during user authorization (assuming that
> authentication takes place on server one and the user is re-directed and
> authorized on server two). So, one workaround is to move role caching to the
> CheckOperations helper function in the Authorization extension. Another
> workaround of course is to use sticky sessions.
> Thanks for bringing this up.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:u1aseJKFFHA.2572@.tk2msftngp13.phx.gbl...
> >I don't think caching will cause any issues. Basically, if the load
> >balanced server doesn't find the roles in its cache, it will load them from
> >the data store. So, you will end up with as many cached instances of the
> >user roles as the number of servers but this is just a performance
> >optimization technique and shouldn't be a problem.
> >
> > That's said though, as the article mentiones, you should be aware of
> > memory constraints that caching may cause. For example, if you have
> > thousands of users, you may consider expiring the cache on a regular
> > basis.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Jason A. Bentley" <jason_a_bentley@.hotmail.com> wrote in message
> > news:Xns95FF6B0C984EDjasonabentleyhotmail@.207.46.248.16...
> >> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in
> >> news:eHxxOG7EFHA.2600@.TK2MSFTNGP09.phx.gbl:
> >>
> >> Yes, this stems from the fact that the cookie is not supplied.
> >> Remember, you have to call LogonUser first to get the cookie. You may
> >> find my WinForm sample useful to find out how to maintain and send the
> >> cookie from a non-web client.
> >>
> >> http://prologika.com/blog/archive/2004/08/27/169.aspx
> >>
> >>
> >> I am trying to implement the role-based piece of the security extension
> >> in
> >> a web farm environment. I think that I am having my issues because of
> >> the
> >> use of "HttpContext.Current.Cache" -- would this be an issue in a web
> >> farm?
> >> If so, do you have any alternatives other than to not cache the roles?
> >>
> >> Thanks!
> >
> >
>
>|||The report data source fails or the LogonUser fails? What kind of
authentication do you use?
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"shahab" <shahab@.discussions.microsoft.com> wrote in message
news:5E6A0C29-AB87-4B5E-A788-927A87003CFA@.microsoft.com...
> Teo thanks for your article I have successfully implemented the Forms
> based
> authentication. My biggest problem now is I cannot run any reports that
> have
> ORACLE datasource. It comes with an error that logon failed!
> Do you know some workaround. Please any assistance would be most
> appreciated.
> Thanks
>
> ============================================> "Teo Lachev [MVP]" wrote:
>> I should've given more thought on this, Jason.
>> I forgot the fact that article code implements role caching in LogonUser.
>> This WILL present an issue with load balancing since LogonUser is called
>> once during the cookie lifetime and the custom extension will not find
>> the
>> roles on the second server during user authorization (assuming that
>> authentication takes place on server one and the user is re-directed and
>> authorized on server two). So, one workaround is to move role caching to
>> the
>> CheckOperations helper function in the Authorization extension. Another
>> workaround of course is to use sticky sessions.
>> Thanks for bringing this up.
>> ---
>> Teo Lachev, MVP [SQL Server], MCSD, MCT
>> Author: "Microsoft Reporting Services in Action"
>> Publisher website: http://www.manning.com/lachev
>> Buy it from Amazon.com: http://shrinkster.com/eq
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
>> news:u1aseJKFFHA.2572@.tk2msftngp13.phx.gbl...
>> >I don't think caching will cause any issues. Basically, if the load
>> >balanced server doesn't find the roles in its cache, it will load them
>> >from
>> >the data store. So, you will end up with as many cached instances of the
>> >user roles as the number of servers but this is just a performance
>> >optimization technique and shouldn't be a problem.
>> >
>> > That's said though, as the article mentiones, you should be aware of
>> > memory constraints that caching may cause. For example, if you have
>> > thousands of users, you may consider expiring the cache on a regular
>> > basis.
>> >
>> > --
>> > Hope this helps.
>> >
>> > ---
>> > Teo Lachev, MVP [SQL Server], MCSD, MCT
>> > Author: "Microsoft Reporting Services in Action"
>> > Publisher website: http://www.manning.com/lachev
>> > Buy it from Amazon.com: http://shrinkster.com/eq
>> > Home page and blog: http://www.prologika.com/
>> > ---
>> >
>> > "Jason A. Bentley" <jason_a_bentley@.hotmail.com> wrote in message
>> > news:Xns95FF6B0C984EDjasonabentleyhotmail@.207.46.248.16...
>> >> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in
>> >> news:eHxxOG7EFHA.2600@.TK2MSFTNGP09.phx.gbl:
>> >>
>> >> Yes, this stems from the fact that the cookie is not supplied.
>> >> Remember, you have to call LogonUser first to get the cookie. You may
>> >> find my WinForm sample useful to find out how to maintain and send
>> >> the
>> >> cookie from a non-web client.
>> >>
>> >> http://prologika.com/blog/archive/2004/08/27/169.aspx
>> >>
>> >>
>> >> I am trying to implement the role-based piece of the security
>> >> extension
>> >> in
>> >> a web farm environment. I think that I am having my issues because of
>> >> the
>> >> use of "HttpContext.Current.Cache" -- would this be an issue in a web
>> >> farm?
>> >> If so, do you have any alternatives other than to not cache the roles?
>> >>
>> >> Thanks!
>> >
>> >
>>|||thanks for your time. I managed to get it working, what I did was that I
uninstalled RS ,uninstalled the Oracle 9.1 client and then reinstalled Oracle
client and RS respectively. for some reason the RS was not identifying the
existence of Oracle on my machine maybe I installed Oracle client after the
RS was installed.
"Teo Lachev [MVP]" wrote:
> The report data source fails or the LogonUser fails? What kind of
> authentication do you use?
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> news:5E6A0C29-AB87-4B5E-A788-927A87003CFA@.microsoft.com...
> > Teo thanks for your article I have successfully implemented the Forms
> > based
> > authentication. My biggest problem now is I cannot run any reports that
> > have
> > ORACLE datasource. It comes with an error that logon failed!
> > Do you know some workaround. Please any assistance would be most
> > appreciated.
> > Thanks
> >
> >
> > ============================================> > "Teo Lachev [MVP]" wrote:
> >
> >> I should've given more thought on this, Jason.
> >>
> >> I forgot the fact that article code implements role caching in LogonUser.
> >> This WILL present an issue with load balancing since LogonUser is called
> >> once during the cookie lifetime and the custom extension will not find
> >> the
> >> roles on the second server during user authorization (assuming that
> >> authentication takes place on server one and the user is re-directed and
> >> authorized on server two). So, one workaround is to move role caching to
> >> the
> >> CheckOperations helper function in the Authorization extension. Another
> >> workaround of course is to use sticky sessions.
> >>
> >> Thanks for bringing this up.
> >>
> >> ---
> >> Teo Lachev, MVP [SQL Server], MCSD, MCT
> >> Author: "Microsoft Reporting Services in Action"
> >> Publisher website: http://www.manning.com/lachev
> >> Buy it from Amazon.com: http://shrinkster.com/eq
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >>
> >> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> >> news:u1aseJKFFHA.2572@.tk2msftngp13.phx.gbl...
> >> >I don't think caching will cause any issues. Basically, if the load
> >> >balanced server doesn't find the roles in its cache, it will load them
> >> >from
> >> >the data store. So, you will end up with as many cached instances of the
> >> >user roles as the number of servers but this is just a performance
> >> >optimization technique and shouldn't be a problem.
> >> >
> >> > That's said though, as the article mentiones, you should be aware of
> >> > memory constraints that caching may cause. For example, if you have
> >> > thousands of users, you may consider expiring the cache on a regular
> >> > basis.
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > ---
> >> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> >> > Author: "Microsoft Reporting Services in Action"
> >> > Publisher website: http://www.manning.com/lachev
> >> > Buy it from Amazon.com: http://shrinkster.com/eq
> >> > Home page and blog: http://www.prologika.com/
> >> > ---
> >> >
> >> > "Jason A. Bentley" <jason_a_bentley@.hotmail.com> wrote in message
> >> > news:Xns95FF6B0C984EDjasonabentleyhotmail@.207.46.248.16...
> >> >> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in
> >> >> news:eHxxOG7EFHA.2600@.TK2MSFTNGP09.phx.gbl:
> >> >>
> >> >> Yes, this stems from the fact that the cookie is not supplied.
> >> >> Remember, you have to call LogonUser first to get the cookie. You may
> >> >> find my WinForm sample useful to find out how to maintain and send
> >> >> the
> >> >> cookie from a non-web client.
> >> >>
> >> >> http://prologika.com/blog/archive/2004/08/27/169.aspx
> >> >>
> >> >>
> >> >> I am trying to implement the role-based piece of the security
> >> >> extension
> >> >> in
> >> >> a web farm environment. I think that I am having my issues because of
> >> >> the
> >> >> use of "HttpContext.Current.Cache" -- would this be an issue in a web
> >> >> farm?
> >> >> If so, do you have any alternatives other than to not cache the roles?
> >> >>
> >> >> Thanks!
> >> >
> >> >
> >>
> >>
> >>
>
>

ArrayList -> System.Object-Variable

Hi,

I have a custom task to execute a package. This task sets dynamically the values of the child package at runtime before execution of the child.

Everything works fine, as long as Im not trying to use an Object Variable in the child package, trying to fill with an ArrayList. Then, during package validation at runtime, the Properties of the custom task have NULL-Values. I don't know why all Properties got NULL or 0 (int), only adding an ArrayList-Property to the Task-Code.

The ArrayList gets initialized in the constructor of the Task. Values to add in execute(). But the process never gets to the execute()-Method, due to my check in validate() for null Values. So the List is initialized but empty.

Anyone an idea, maybe I'm doing something wrong at all. Anybody experiences with Object-Variables in Packages? A HowTo?

Thanks a lot

Thorsten

When you say you have an System.Object variable, are you referring to an SSIS variable of type Object, or a .NET variable/property of type System.Object?

Also, to get the picture correct. It sounds like this custom task is similar to an Execute Package Task, except that it sets the "values" of the child package at run-time. I assume you're familar with parent package configurations for non-object variables. Anyway, when you say "dynamically sets the values " are you referring literally to properties, as in pkg.SomeAttribute, or variables, as in pkg.Variables...?|||

Hi,

to get some light into the deep darkness I provided. Maybe, man should read over and over and over a post to provide all information :-). Sorry.

I refer to a SSIS Variable of Type Object. The task is similar to a execute package task, except of the UI, which provides a user friendly (non-technics :-)) GUI to set some properties of the task. These properties control the behavior of the package the task executes at runtime.

Yes I'm familiar with package configurations, but they need variables, one can change without the ability to know they link to a special task. Not so with properties, cos you have to explicitit mark the task to change them.

At runtime, I'm setting the variables of the package I wanna execute, by

Variables variables = null;
variableDispenser.LockOneForWrite(name, ref variables);
variables[0].Value = value;
variables.Unlock();

Everything works as expected, as long as I'm not trying to set a SSIS-Object Variable with an ArrayList. Then "NONE" of the vars gets setted.

Array in expression editor anyone?

I'm kind of a newbie at all this and I'm wondering if I can do the
following. Whithout having to use custom code is there a way to use the IN
function in the expression editor?
I'm currently using the following to accomplish something quite simple:
=IIF((month(Fields!por_POSTING_DATE.Value) = 10 or
month(Fields!por_POSTING_DATE.Value) = 11 or
month(Fields!por_POSTING_DATE.Value) = 12), True,False)
I'd like to use:
=IIF(month(Fields!por_POSTING_DATE.Value) in (10,11,12),True,False)
Can you not use an array in the expression editor (and only in custom code?)
Thanks,
RyanHere's one way to do it:
=iif(Array.BinarySearch(new Integer() {10,11,12},
month(Fields!por_POSTING_DATE.Value))>=0,True,False)
--
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.
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:evyaKVzeEHA.2352@.TK2MSFTNGP09.phx.gbl...
> I'm kind of a newbie at all this and I'm wondering if I can do the
> following. Whithout having to use custom code is there a way to use the
IN
> function in the expression editor?
> I'm currently using the following to accomplish something quite simple:
> =IIF((month(Fields!por_POSTING_DATE.Value) = 10 or
> month(Fields!por_POSTING_DATE.Value) = 11 or
> month(Fields!por_POSTING_DATE.Value) = 12), True,False)
> I'd like to use:
> =IIF(month(Fields!por_POSTING_DATE.Value) in (10,11,12),True,False)
> Can you not use an array in the expression editor (and only in custom
code?)
> Thanks,
> Ryan
>|||Thanks Chris that worked great.
Ryan
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:%23OSa8%23AfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Here's one way to do it:
> =iif(Array.BinarySearch(new Integer() {10,11,12},
> month(Fields!por_POSTING_DATE.Value))>=0,True,False)
> --
> 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.
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:evyaKVzeEHA.2352@.TK2MSFTNGP09.phx.gbl...
> > I'm kind of a newbie at all this and I'm wondering if I can do the
> > following. Whithout having to use custom code is there a way to use the
> IN
> > function in the expression editor?
> > I'm currently using the following to accomplish something quite simple:
> >
> > =IIF((month(Fields!por_POSTING_DATE.Value) = 10 or
> > month(Fields!por_POSTING_DATE.Value) = 11 or
> > month(Fields!por_POSTING_DATE.Value) = 12), True,False)
> > I'd like to use:
> > =IIF(month(Fields!por_POSTING_DATE.Value) in (10,11,12),True,False)
> >
> > Can you not use an array in the expression editor (and only in custom
> code?)
> >
> > Thanks,
> >
> > Ryan
> >
> >
>

Arrange Items in Solution Explorer

Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?

Hi.

The reports in the solution explorer are ordered alphabetically when you open a solution/project; when you add them to an open solution/project they tend to be added just to the end.

I dont know of any way to change the default ordering, but as workaround you could just add a number prefix to the title of each report (e.g. 01-This report is top.rdl, 02-Another report.rdl etc.) so they are ordered in the way you want.

Hope this helps,

Gary.

|||Hi Gary,

Thanks for the reply.

I really want to avoid changing the report object names. Perhaps I will just leave it for now since it is just a cosmetical issue anyway.

Thanks again.

Kenny

Arrange Items in Solution Explorer

Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?

Hi.

The reports in the solution explorer are ordered alphabetically when you open a solution/project; when you add them to an open solution/project they tend to be added just to the end.

I dont know of any way to change the default ordering, but as workaround you could just add a number prefix to the title of each report (e.g. 01-This report is top.rdl, 02-Another report.rdl etc.) so they are ordered in the way you want.

Hope this helps,

Gary.

|||Hi Gary,

Thanks for the reply.

I really want to avoid changing the report object names. Perhaps I will just leave it for now since it is just a cosmetical issue anyway.

Thanks again.

Kenny

Arrange Items in Solution Explorer

Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?

Hi.

The reports in the solution explorer are ordered alphabetically when you open a solution/project; when you add them to an open solution/project they tend to be added just to the end.

I dont know of any way to change the default ordering, but as workaround you could just add a number prefix to the title of each report (e.g. 01-This report is top.rdl, 02-Another report.rdl etc.) so they are ordered in the way you want.

Hope this helps,

Gary.

|||Hi Gary,

Thanks for the reply.

I really want to avoid changing the report object names. Perhaps I will just leave it for now since it is just a cosmetical issue anyway.

Thanks again.

Kenny

Arithmetic overflow error converting expression to data type datetime

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS

BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,

DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

Are there any triggers added in transactional replication

As part of transactional replication, are there cases when triggers created
? I know custom sprocs are created in default trans repl and additional
column is created for updatable subscriptions.. but not too sure about
triggers ..
Hassan,
for immediate or queued updating subscribers in transactional replication,
you'll find triggers on the subscriber's replicated tables. These triggers
will either do a distributed transaction with the publisher, or insert into
the queue table.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Are Steps 1 and 3 of a replication agent job important?

Hello,
I am creating a custom job that will publish several publications to
subscribers. I will NOT be using the auto-generated jobs created by SQL
Server to perform these tasks, and instead I am performing this activity in
my custom job. I copied the job steps from the SQL generated jobs over to
mine.
However, I noticed that for every replication task, each job generated has 3
steps. Usually, they include something like the following steps:
1. Snapshot Agent startup message.
2. Run agent.
3. Detect nonlogged agent shutdown.
Because my custom job is very complex, I would prefer not to include steps 1
and 3 of every task as they dont seem to do anything important besides
logging. Before I remove them, I would like to ask for your feedback and/or
find out if I should keep these tasks there for any reason.
What will I miss out on if I remove them?
Johnny
I'd have to imagine you could get by without them... test it out and see
what happens.
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:E6274B37-FFBE-4A5A-9453-A2DDD4FB15A9@.microsoft.com...
> Hello,
> I am creating a custom job that will publish several publications to
> subscribers. I will NOT be using the auto-generated jobs created by SQL
> Server to perform these tasks, and instead I am performing this activity
> in
> my custom job. I copied the job steps from the SQL generated jobs over to
> mine.
> However, I noticed that for every replication task, each job generated has
> 3
> steps. Usually, they include something like the following steps:
> 1. Snapshot Agent startup message.
> 2. Run agent.
> 3. Detect nonlogged agent shutdown.
> Because my custom job is very complex, I would prefer not to include steps
> 1
> and 3 of every task as they dont seem to do anything important besides
> logging. Before I remove them, I would like to ask for your feedback
> and/or
> find out if I should keep these tasks there for any reason.
> What will I miss out on if I remove them?
> Johnny
>
|||I did test it out before and after and everything seems to work perfectly as
step 1 & 3 are simply logging startup and error messages. This works for both
the snapshot & distribution agents.
Johnny
"ChrisR" wrote:

> I'd have to imagine you could get by without them... test it out and see
> what happens.
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:E6274B37-FFBE-4A5A-9453-A2DDD4FB15A9@.microsoft.com...
>
>
|||The first job step adds a message to the history table. The last step is
used to detect if the agent shut down (gpf'd) without an error message.
It is advisable to include these job steps.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:3567799D-ACF3-4CF9-9329-6EC74CCFF89E@.microsoft.com...
> I did test it out before and after and everything seems to work perfectly
as
> step 1 & 3 are simply logging startup and error messages. This works for
both[vbcol=seagreen]
> the snapshot & distribution agents.
> Johnny
>
> "ChrisR" wrote:
SQL[vbcol=seagreen]
activity[vbcol=seagreen]
to[vbcol=seagreen]
has[vbcol=seagreen]
steps[vbcol=seagreen]

Are JOINs and WHEREs semantically equivalent?

Hi,
I have a query that uses a series of JOINs to produce a result set.
However, it needs to be generated by a custom SQL Builder, and it would be
easier to auto-generate if the JOINs were WHERE clauses instead.
Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
and still get *exactly* the same result?
The query is below, if it helps to shed some light on the above question:
SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
OuterDepartment.CalculatedPath as Department
,OuterSource.CalculatedPath as OuterSource
FROM Incident I1
LEFT JOIN
yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
(SELECT yDepartment.ID
FROM yDepartment
WHERE (yDepartment.CalculatedPath =
OuterDepartment.CalculatedPath) OR
(yDepartment.CalculatedPath
LIKE OuterDepartment.CalculatedPath + '\%'))
LEFT JOIN
yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
(SELECT yIncidentSourceType.ID
FROM yIncidentSourceType
WHERE (yIncidentSourceType.CalculatedPath =
OuterSource.CalculatedPath) OR
(yIncidentSourceType.CalculatedPath
LIKE OuterSource.CalculatedPath + '\%'))
WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is null)
AND
I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
[The query sums totals of all children of specified parent records,
presenting them in a Matrix format]
Thanks,
Duncanhttp://www.tek-tips.com/faqs.cfm?fid=5168
"Duncan M Gunn" <gunnd@.gaelqualityNOSPAM.co.uk> wrote in message
news:e0wBGgcQFHA.3816@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPath
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
> = OuterSource.CalculatedPath) OR
> (yIncidentSourceType.CalculatedPath LIKE OuterSource.CalculatedPath +
> '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is
> null)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>|||Duncan,
To put the join expression in the WHERE clause you have to use the old style
join and it is not compatible for outer joins. For example, when you want to
select all rows from t1 with no entries in t2 using a join condition.
select a.*
from t1 as a left join t2 as b on a.pk_col = b.pk_col
where b.pk_col is null
-- this statement will not yield same result as previous
select a.*
from t1 as a, t2 as b
where a.pk_col *= b.pk_col and b.pk_isnull
go
ANSI JOIN vs. OUTER JOIN
http://www.microsoft.com/sql/techin...ment/July23.asp
AMB
"Duncan M Gunn" wrote:

> Hi,
> I have a query that uses a series of JOINs to produce a result set.
> However, it needs to be generated by a custom SQL Builder, and it would be
> easier to auto-generate if the JOINs were WHERE clauses instead.
> Is this possible - i.e. can I do *everything* that a JOIN does in a WHERE,
> and still get *exactly* the same result?
>
> The query is below, if it helps to shed some light on the above question:
>
> SELECT COUNT(DISTINCT(I1.ID)) AS CountOfIncidents,
> OuterDepartment.CalculatedPath as Department
> ,OuterSource.CalculatedPath as OuterSource
> FROM Incident I1
> LEFT JOIN
> yDepartment OuterDepartment ON I1.RaisedAgainstDepartmentID IN
> (SELECT yDepartment.ID
> FROM yDepartment
> WHERE (yDepartment.CalculatedPath =
> OuterDepartment.CalculatedPath) OR
> (yDepartment.CalculatedPat
h
> LIKE OuterDepartment.CalculatedPath + '\%'))
> LEFT JOIN
> yIncidentSourceType OuterSource ON I1.IncidentSourceTypeID IN
> (SELECT yIncidentSourceType.ID
> FROM yIncidentSourceType
> WHERE (yIncidentSourceType.CalculatedPath
=
> OuterSource.CalculatedPath) OR
> (yIncidentSourceType.Calcu
latedPath
> LIKE OuterSource.CalculatedPath + '\%'))
> WHERE (OuterDepartment.ParentID = 12) AND (OuterSource.ParentID is nul
l)
> AND
> I1.[ID] IN (SELECT DISTINCT I1.[ID] FROM [Incident]I1)
> GROUP BY OuterDepartment.CalculatedPath, OuterSource.CalculatedPath
>
> [The query sums totals of all children of specified parent records,
> presenting them in a Matrix format]
>
> Thanks,
> Duncan
>
>|||>> However, it needs to be generated by a custom SQL Builder, and it
would be
easier to auto-generate if the JOINs were WHERE clauses instead. Is
this possible <<
No. The OUTER JOINs have to be done in a sequence and cannot be moved
to the WHERE clause. The old *= syntax is deprecated and will not be
supported in the future.
[sic], presenting them in a Matrix format <<
The path enumeration model for a hierarchy is good for searching, but
terrible for aggregation. Consider changing to a nested sets model and
you can do this in one simple query.