Table Variable: February 2012

Friday, February 24, 2012

How to use CLR Stored Procedures in Reporting Services?

Hi there.

As subject metioned,I got a problem.

I have a CLR Stored Procedures it works fine in Sql Server Management Studio,but when excuting the stored procedure in Reporting Services Query Builder window(Visual Studio Reporting Services Project),I got a error message "The stored procedure <stored_procedure_name> doesn't exist".

so how to resolve this problem?

my env : SQL Server 2005 , Visual Studio 2005

many thanks.

The CLR stored procedure is attached to a specific database. Make sure that you are connecting to the correct database when you initialize the data source you are using. I have gotten CLR stored procedures to work so I know they will work in Reporting Services.|||

Maybe i got something wrong,i'll check it.

Now i use a T-SQL Stored Procedure as a wrapper to call the CLR one,and it works fine.

thanks and best regards :D

|||

Hi,

I have the same problem.

I also tried with the t-sql stored procedure as wrapper, but when executing it, I get the error

"The transaction context is already in use in another session".

This time the error appears executing the stored procedure from Sql Server Management Studio too.

So I'm giving up. Unless somebody has some suggestions.

Bye.

How to use CLR Stored Procedures in Reporting Services?

Hi there.

As subject metioned,I got a problem.

so how to resolve this problem?

my env : SQL Server 2005 , Visual Studio 2005

many thanks.

Maybe i got something wrong,i'll check it.

Now i use a T-SQL Stored Procedure as a wrapper to call the CLR one,and it works fine.

thanks and best regards :D

|||

Hi,

I have the same problem.

I also tried with the t-sql stored procedure as wrapper, but when executing it, I get the error

"The transaction context is already in use in another session".

This time the error appears executing the stored procedure from Sql Server Management Studio too.

So I'm giving up. Unless somebody has some suggestions.

Bye.

How to use CLR security ..Impersonation to access external resources?

I want to Access External resources inside the CLR Code... But I am getting Security Exception

I have marked Assembly with External Access... here is the way I am doing..

I read articles and MSDN .. everywhere is written to use impersonation like

using (WindowsIdentity id = SqlContext.WindowsIdentity)

{

WindowsImpersonationContext c = id.Impersonate();

//perform operations with external resources and then undo

c.Undo();

}

In above case .. I tried both Windows Authentications and SQL Authentications ...

In case of Windows.. I am have a domain login to logon to my pc, while sql server is at another machine and Active directory is at different machine .. when connect to Database .. it says cannot find user Domainname\user

and the SqlContext.WindowsIdentity is always null or it has exception User.Toked thew Security exception.

After that .. I tried to user custome Identity .. using IIdentity =GenericIdentity("UserName","Windows");

But there is now difference .. still same exception .. as given below..

[Microsoft.SqlServer.Server.SqlProcedure]

public static void MyProcedure()

{

Process[] p = Process.GetProcessesByName("YPager"); //Yahoo messanger exe .. a process

p[0].kill();

}

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyProcedure': System.Security.SecurityException: Request failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)

at DatFileGenerator.StoredProcedures.'MyProcedure'()

No rows affected.

(0 row(s) returned)

@.RETURN_VALUE =

Finished running [dbo].['MyProcedure'].

How could I go ahead... what I should do to accompilsh the task...

Kindlly .. suggestions and ideas..

Thanks,

Muna

few things that might help you...

first, there is this blog post, http://blogs.msdn.com/dataaccess/archive/2006/01/25/517495.aspx

second, YOU MUST CONNECT TO DATABASE USING WINDOWS AUTHENTICATION FOR IMPERSONATION TO WORK

third, try using the UNSAFE PERMISSION SET instead of external access

fourth, here is an impersonation sample from my upcoming SQLCLR book:

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure()]

public static void uspExternalConnection()

{

WindowsIdentity newIdentity = null;

WindowsImpersonationContext newContext = null;

try

{

//impersonate the caller

newIdentity = SqlContext.WindowsIdentity;

newContext = newIdentity.Impersonate();

if (newContext != null)

{

using (SqlConnection oConn =

new SqlConnection("Server=.\\sqlexpress;" +

"Integrated Security=true;"))

{

SqlCommand oCmd = new SqlCommand("SELECT * FROM " +

"AdventureWorks.HumanResources.Employee", oConn);

oConn.Open();

SqlDataReader oRead =

oCmd.ExecuteReader(CommandBehavior.CloseConnection);

//revent security context

newContext.Undo();

//return results

SqlContext.Pipe.Send(oRead);

}

else

{

throw new Exception("user impersonation has failed");

}

catch (SqlException ex)

{

SqlContext.Pipe.Send(ex.Message.ToString());

}

finally

{

if (newContext != null)

{

newContext.Undo();

}

};

|||

Hi Muna,

It looks like your impersonation call and attempt to access external resources are working - the problem is with what you're trying to done from an external_access assembly. Finding and killing an external process is not allowed in external_access assemblies, so you'll need to use UNSAFE instead. The exception shows that you're hitting a CodeAccessSecurity violation, as stated in the clr documentation on the Process class: http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.aspx

"This class contains a link demand and an inheritance demand at the class level that applies to all members. A SecurityException is thrown when either the immediate caller or the derived class does not have full-trust permission. For details about security demands, see Link Demands and Inheritance Demands."

Steven

|||

Thank you for the for helping me out ....

I am able to connect to database, inserting, updating and pulling out data from there, using Windows Authentication....

As well .. I have written data to a file (on filesystem)

All it work fine But when I try to use the code to kill a process .. It generate Exception

Here is code which cause Exception....

public partial class StoredProcedures
{

[Microsoft.SqlServer.Server.SqlProcedure()]

public static void TestSecurity()
{

WindowsIdentity newIdentity = null;
WindowsImpersonationContext newContext = null;
try
{
//impersonate the caller
newIdentity = SqlContext.WindowsIdentity;

newContext = newIdentity.Impersonate();
if (newContext != null)
{
using (SqlConnection oConn = new SqlConnection(@."Data Source=MyComputerName\SQLEXPRESS;Initial Catalog=DataBaseName;Integrated Security=True;"))
{
SqlCommand oCmd = new SqlCommand("SELECT * FROM " +"AdventureWorks.HumanResources.Employee", oConn);
oConn.Open();
SqlDataReader oRead = oCmd.ExecuteReader(CommandBehavior.CloseConnection);

Process[] p = Process.GetProcessesByName("YPager"); //Yahoo messanger exe .
p[0].kill();

//revent security context
newContext.Undo();
//return results
SqlContext.Pipe.Send(oRead);
}
}
else
{
throw new Exception("user impersonation has failed");
}
}
catch (SqlException ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
finally
{
if (newContext != null)
{
newContext.Undo();
}
}
}
}
};

The Problem is with killing external (windows) process from SQL App

SQL Server AppDomain

About impersonating the user.... I am getting Exception

//impersonate the caller
during debuggin At this line when I check in wach window

newIdentity = SqlContext.WindowsIdentity;

It has following information....

AuthenticationType = 'SqlContext.WindowsIdentity.AuthenticationType' threw an exception of type 'System.UnauthorizedAccessException'

Groups = {System.Security.Principal.IdentityReferenceCollection}

ImpersonationLevel = Impersonation

IsAnonymous = false

IsAuthenticated = true

IsGuest = false

IsSystem=false

Name="DomainName\myLoginName"

Owner = {S-1-5-21-15454545467-37684546461-44646464642-1176}

Token="2342"

User = {S-1-5-21-1878703567-3768572861-466091742-1176}

hmmmm Where I am going wrong?

|||Hi Steven,
Thank you for helping me out...
I have used the attribute as following..

[assembly: IsolatedStorageFilePermission(SecurityAction.RequestMinimum, UsageAllowed = IsolatedStorageContainment.AssemblyIsolationByUser)]

public partial class

StoredProcedures
{

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure()]

public static void TestSecurity()
{

WindowsIdentity newIdentity = null;
WindowsImpersonationContext

newContext = null;
try
{

//impersonate the caller
newIdentity =

SqlContext.WindowsIdentity;

newContext =

newIdentity.Impersonate();
if (newContext !=

null)
{
using (SqlConnection oConn = new

SqlConnection(@."Data Source=MyComputerName\SQLEXPRESS;Initial

Catalog=DataBaseName;Integrated Security=True;"))

{
SqlCommand oCmd = new

SqlCommand("SELECT * FROM " +"AdventureWorks.HumanResources.Employee",

oConn);
oConn.Open();

SqlDataReader oRead =

oCmd.ExecuteReader(CommandBehavior.CloseConnection);

Process[] p = Process.GetProcessesByName("YPager");

//Yahoo messanger exe .

p[0].kill();

//revent security

context
newContext.Undo();

//return results

SqlContext.Pipe.Send(oRead);
}

}
else
{
throw new

Exception("user impersonation has failed");
}

}
catch (SqlException ex)
{

SqlContext.Pipe.Send(ex.Message.ToString());
}

finally
{
if (newContext !=

null)
{

newContext.Undo();
}
}
}

}
};

It is helping me out to kill the process but still exception
ExitCode = 'p[0].ExitCode' threw an exception of type 'System.InvalidOperationException'

Thanks
|||

Have you tried killing YPager and doing p[0].ExitCode outside SQL Server in a simple console app? The documentation on Process.ExitCode (http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.exitcode.aspx) says the following:

Exceptions

Exception type

Condition

InvalidOperationException

The process has not exited.

-or-

The process Handle is not valid.

If it works outside SQL Server. Can you post the complete exception that you are getting from inside SQL?

Thanks,

-Vineet.

|||Hi Vineet,

For better understanding here are some facts

ComputerName = ProductionPC

Application Name = TestApplication

Here is execution .code..

Auto-attach to process '[3224] [SQL] ProductionPC' on machine 'ProductionPC' succeeded.
Running [dbo].[TestSecurity].

The thread 'ProductionPC\sqlexpress [55]' (0x8a0) has exited with code 0 (0x0).
The thread 'ProductionPC\sqlexpress [55]' (0x8a0) has exited with code 0 (0x0).
The thread 'ProductionPC\sqlexpress [55]' (0x8a0) has exited with code 0 (0x0).
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Auto-attach to process '[3224] sqlservr.exe' on machine 'ProductionPC' succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'TestApplication', Symbols loaded.
'sqlservr.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', No symbols loaded.
EmployeeID NationalIDNumber ContactID LoginID ManagerID Title BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
-- - -- - -- -- -- - -- - -- -- -- --
No rows affected.
(1 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[TestSecurity].
The thread 'ProductionPC\sqlexpress [55]' (0x8a0) has exited with code 0 (0x0).
The program '[3224] [SQL] ProductionPC: ProductionPC\sqlexpress' has exited with code 0 (0x0).
The program '[3224] sqlservr.exe: Managed' has exited with code 259 (0x103).

Taking look on object P (instance holding the process to kill ) in watch window.. gives following iformation

- p {Dimensions:[1]} System.Diagnostics.Process[]
- [0] {System.Diagnostics.Process (DAP)} System.Diagnostics.Process
+ base {System.Diagnostics.Process (DAP)} System.ComponentModel.Component {System.Diagnostics.Process}
BasePriority 8 int
EnableRaisingEvents false bool
+ ExitCode 'p[0].ExitCode' threw an exception of type 'System.InvalidOperationException' int {System.InvalidOperationException}
+ ExitTime 'p[0].ExitTime' threw an exception of type 'System.InvalidOperationException' System.DateTime {System.InvalidOperationException}
+ Handle 3524 System.IntPtr
HandleCount 439 int
HasExited false bool
Id 2576 int
MachineName "." string
+ MainModule {System.Diagnostics.ProcessModule (DAP.exe)} System.Diagnostics.ProcessModule
+ MainWindowHandle 0 System.IntPtr
MainWindowTitle "" string
+ MaxWorkingSet 1413120 System.IntPtr
+ MinWorkingSet 204800 System.IntPtr
+ Modules {System.Diagnostics.ProcessModuleCollection} System.Diagnostics.ProcessModuleCollection
NonpagedSystemMemorySize 30440 int
NonpagedSystemMemorySize64 30440 long
PagedMemorySize 16273408 int
PagedMemorySize64 16273408 long
PagedSystemMemorySize 75080 int
PagedSystemMemorySize64 75080 long
PeakPagedMemorySize 37670912 int
PeakPagedMemorySize64 37670912 long
PeakVirtualMemorySize 132988928 int
PeakVirtualMemorySize64 132988928 long
PeakWorkingSet 22728704 int
PeakWorkingSet64 22728704 long
PriorityBoostEnabled true bool
PriorityClass Normal System.Diagnostics.ProcessPriorityClass
PrivateMemorySize 16273408 int
PrivateMemorySize64 16273408 long
+ PrivilegedProcessorTime {00:00:00.8437500} System.TimeSpan
ProcessName "DAP" string
+ ProcessorAffinity 1 System.IntPtr
Responding true bool
SessionId 0 int
+ StandardError 'p[0].StandardError' threw an exception of type 'System.InvalidOperationException' System.IO.StreamReader {System.InvalidOperationException}
+ StandardInput 'p[0].StandardInput' threw an exception of type 'System.InvalidOperationException' System.IO.StreamWriter {System.InvalidOperationException}
+ StandardOutput 'p[0].StandardOutput' threw an exception of type 'System.InvalidOperationException' System.IO.StreamReader {System.InvalidOperationException}
+ StartInfo {System.Diagnostics.ProcessStartInfo} System.Diagnostics.ProcessStartInfo
+ StartTime {7/28/2006 11:14:44 PM} System.DateTime
SynchronizingObject null System.ComponentModel.ISynchronizeInvoke
+ Threads {System.Diagnostics.ProcessThreadCollection} System.Diagnostics.ProcessThreadCollection
+ TotalProcessorTime {00:00:03.7031250} System.TimeSpan
+ UserProcessorTime {00:00:02.8593750} System.TimeSpan
VirtualMemorySize 132988928 int
VirtualMemorySize64 132988928 long
WorkingSet 9736192 int
WorkingSet64 9736192 long
+ Static members
+ Non-Public members

Thanks|||

Muna & Steve,

I marked steve's reply as correct...(which he was correct, much better than my response)...NOW HOW CAN I MARK A REPLY AS AN ANSWER TO A THREAD I DID NOT START LOL!

|||Hi

Derek

Thank you for your comments...
I am really glad to know that you are very generous in addition to be a developer (Engineer /Manager)

You are right that steve's reply is more helping...and I will mark it too..
But I am having few exceptions as posted above... I want to make it working so that some one else can get somplete solution from here too..

I will wornder if you guys could help me out to resolve these exceptions related issues.

Thanks

|||

Hi,

Try this:

Process[] p = Process.GetProcessesByName("YahooMessenger"); //Yahoo messanger exe .

p[0].EnableRaisingEvents = true;

p[0].Kill();

p[0].WaitForExit();

int ExitCode = p[0].ExitCode;

This is explained in the documentation for Process.ExitCode at http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.exitcode.aspx . I think i had a link to this documentation in my previous post as well :)

Thanks,

-Vineet.

|||

Hi Vineet,

I could not take a look on your last reply ..(on weekend went away ...)

As you refere to your previous post's links... yes, I worked on that .. as well as on the suggestions from other friends...

Till right now What I got is ..

1- I have marked assembly Unsafe, It allow to kill the process ,

when I call the SP from Visual Studio... even there are exceptions .. but still process if killed

2 - when same SP is called from SQL server, exception is thrown, I am using integrated connection...

Msg 6522, Level 16, State 1, Procedure TestSecurity, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'TestSecurity':

System.ComponentModel.Win32Exception: Access is denied

System.ComponentModel.Win32Exception:

at System.Diagnostics.ProcessManager.OpenProcess(Int32 processId, Int32 access, Boolean throwIfExited)

at System.Diagnostics.Process.GetProcessHandle(Int32 access, Boolean throwIfExited)

at System.Diagnostics.Process.OpenProcessHandle()

at System.Diagnostics.Process.set_EnableRaisingEvents(Boolean value)

at StoredProcedures.TestSecurity()

Because, the actual problem has not been resolved thats why I have not marked any answer.

Any alternate path to accomplish this task?

Thanks,

|||

Hi,

The code i have posted above worked just fine for me (registering the assembly as unsafe). My setup is Windows XP SP2, SQL Server 2005, Yahoo Messenger 7.5.0.819. I am using Windows authentication and my machine is on a domain. I am admin on the machine as well as on SQL Server. SQL Server is running under Local System.

Could you please post your complete code that throws this exception. Also provide details on your setup. It looks like the account you are running under does not have sufficient privileges to open the process. If you search on the internet you would find posts like http://www.thescripts.com/forum/thread162942.html that provide some explanation and suggestions.

Thanks,

-Vineet.

|||

Hi Vineet,

I have just changed the login on account for sqlservice.exe from domain to local system.. now even I am loged on to pc with a domain login.. I am able to impersonate the user.. It is working fine ..

The actual problem was in impersonation...

the Sqlserver was running under network service acount while I was loged using domain user login..

secondly, at start I was using external_access while now with Unsafe..

At the end .. I am able to kill a process, start a process from stored procedure..(SQLCLR SP)

I am closing this thread now..

Thank you all friends for replies.

How to use cell data security with visual totals?

Hi,

We are working on a project with SQL Server 2005 and SSAS.

We created several roles using dimension security and one special role that requires cell data access permissions (i.e. cell security).

This last role is giving us trouble since we do not know how to implement visual totals on cell security.

A problem description follows:

1) We have a budget and expenses cube with a) Account, b) Time and c) Cost Center dimensions
2) The special role was created for a group of users who have the following requirement for data access permission:
- for some cost centers, restrict access to some accounts
- and for the rest of cost centers see all accounts
3) When looking at data at the cost center and account level, the role works fine. Restrictions are applied correctly.
4) However when looking at the data at the cost center level ONLY, the aggregated total shows a sum for all accounts, regardless of restrictions.

We know that if you use regular dimension security, you can enable Visual Totals in order to have aggregated values that reflect
the restrictions applied at the atomic level.

However, we do not know how to apply visual totals on cell data security. If anyone has faced similar situation,
we would appreciate any help on how to do this.

Another way to put it:

1) In a cube with a Product dimension (Family > Category > Product) and a Geography dimension (Country > State > City)
2) You can set dimension security to restrict access to certain products.
3) If you enable Visual Totals when creating the role, the aggregations at the Category level will show total values excluding amounts for those restricted products.
4) If you do not enable Visual Totals, the aggregated totals at the Category level will add up all products, regardless of access restrictions at the product level.

We are trying to achieve the same with Cell Data Access Permissions, but there is no option to Enable Visual Totals. For instance:

1) Same cube as above
3) Your create a role with cell data access permission; for some cities, you restrict acess to some products, and for all other cities allow access to all products.
4) When you look at data at the City level, you want to aggregate totals not for all products, but excluding amounts for those restricted ones.

We would appreciate your help on this enormously!

Cheers!
jbadillo@.streamnova.com

Cell security was not designed to support visual totals as you described.|||

Hi,

Is there a way around this? Instead of using visual totals, is there some other way to get the right totals?

Cheers

|||

One approach, which seems to work in limited testing with Adventure Works, would involve introducing cell security MDX conditions into the cube MDX script. Basically, a scoped calculation sets the secured cell values to NULL, which will cause the aggregation of those values up the hierarchy to adjust accordingly. However, when cube cells are browsed, the secured cells will still show "#N/A". The limitation with this approach, of course, is that user and/or role logic will be needed, so that unrestricted users are unaffected.

The simplified scenario in Adventure Works was as follows:

For user XYZ, secure the data for Bikes in CY 2003, and rollups in the Sales Summary measure group should reflect this.

The Cell Read Permission expression (for a role with user XYZ as member) is:

Not ([Date].[Calendar].CurrentMember is [Date].[Calendar].[CY 2003]
and [Product].[Product Categories].CurrentMember is [Product].[Product Categories].[Bikes])

And the cube calculation MDX script is something like:

Scope(MeasureGroupMeasures("Sales Summary"),

[Date].[Calendar Year].&[2003],

[Product].[Category].[All Products].[Bikes]);

IF UserName = "XYZ"

THEN this = NULL END IF;

End Scope;

How to use CDO to send mail with attachment

I have a stored proc thats basically a copy of the script from MS to create
sp_send_cdosysmail.
This work fine using our mailrelay, but there seems to be no clear way to
add an attachment.
I've tried a simple:
EXEC @.hr = sp_OAMethod @.iMsg, 'AttachFile', NULL, @.Attachment
but that just causes an unknown name error.
I've also tried creating an attachment object but I can't seems to assciate
it with the original cdo.message:
EXEC @.hr = sp_OASetProperty @.iMsg, 'MailFormat', @.Body
EXEC @.hr = sp_OACreate 'CDO.Attachment', @.iATT OUT
EXEC @.hr = sp_OASetProperty @.iATT, 'Position', 0
EXEC @.hr = sp_OASetProperty @.iATT, 'Type', @.CdoFileData
EXEC @.hr = sp_OASetProperty @.iATT, 'Name', @.Attachment
EXEC @.hr = sp_OAMethod @.iMsg, 'CDO.Message.Attachment.Update', null,@.iATT
I've tried variations on creation of the original object and methods, but I
basically just guessing!
I've read somewhere that the file has to be accessable as a http path. This
is purely a SQL 2000 server, no extras like IIS or any form of exchange
client.
Has anyone sent attachments using SQL server and CDO? Can you please offer
some assisance.
Thanks in advance.Typically, I've found out what I need to know about 30mins after this post.
Why doesn't it happen 30 minutes after you first start looking!
Ha|||Hi, Phil
Would you mind sharing your idea about 'CDO sending mail with attachment'?
1. What is the syntax of CDO with attachment?
2. How to use the result of query as attachment?
Thanks a lot!
Tianya
"Phil_Edney" wrote:

> I have a stored proc thats basically a copy of the script from MS to creat
e
> sp_send_cdosysmail.
> This work fine using our mailrelay, but there seems to be no clear way to
> add an attachment.
> I've tried a simple:
> EXEC @.hr = sp_OAMethod @.iMsg, 'AttachFile', NULL, @.Attachment
> but that just causes an unknown name error.
> I've also tried creating an attachment object but I can't seems to assciat
e
> it with the original cdo.message:
> EXEC @.hr = sp_OASetProperty @.iMsg, 'MailFormat', @.Body
> EXEC @.hr = sp_OACreate 'CDO.Attachment', @.iATT OUT
> EXEC @.hr = sp_OASetProperty @.iATT, 'Position', 0
> EXEC @.hr = sp_OASetProperty @.iATT, 'Type', @.CdoFileData
> EXEC @.hr = sp_OASetProperty @.iATT, 'Name', @.Attachment
> EXEC @.hr = sp_OAMethod @.iMsg, 'CDO.Message.Attachment.Update', null,@.iA
TT
> I've tried variations on creation of the original object and methods, but
I
> basically just guessing!
> I've read somewhere that the file has to be accessable as a http path. Thi
s
> is purely a SQL 2000 server, no extras like IIS or any form of exchange
> client.
> Has anyone sent attachments using SQL server and CDO? Can you please offer
> some assisance.
> Thanks in advance.|||I found a solution at the following web site.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649
"Tianya" wrote:
> Hi, Phil
> Would you mind sharing your idea about 'CDO sending mail with attachment'?
> 1. What is the syntax of CDO with attachment?
> 2. How to use the result of query as attachment?
> Thanks a lot!
> Tianya
>
> "Phil_Edney" wrote:
>

How to use CASE WHEN statement in Function

Hi,
My Function as follow-->
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
IF @.cemk = '1'
BEGIN
RETURN 'XX0400100'
END
RETURN 'XX0400200'
END
My Case When statement like this-->
CASE
WHEN iv_cemk = 0 THEN 'XX0400200'
ELSE 'XX0400100'
END AS iv_cemk
And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
Function?
How should I do?
Thanks!
AngiHi Angi,
what about
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
declare @.cReturn char(9)
set @.cReturn = (case @.cemk
when '1' then 'XX0400100'
else 'XX0400200'
end)
return @.cReturn
END
HTH
Meinhard
"angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My Function as follow-->
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> IF @.cemk = '1'
> BEGIN
> RETURN 'XX0400100'
> END
> RETURN 'XX0400200'
> END
> My Case When statement like this-->
> CASE
> WHEN iv_cemk = 0 THEN 'XX0400200'
> ELSE 'XX0400100'
> END AS iv_cemk
> And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> Function?
> How should I do?
> Thanks!
> Angi
>|||Hi, Meinhard
Thank you very much!
And there is aother way like follow..
BEGIN
RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
ELSE 'XX0400200'
END
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And I have another question there, syntax like follow..
CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
RETURNS CHAR(9)
AS
BEGIN
RETURN
CASE--XY13AA
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.csct = 'f1' THEN 'XY1300100'
WHEN @.csct = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13BB
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13CC
WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
END
There are 3 different CASE WHEN conditions and how could I combine it in a
Function?
Thanks!
Angi
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> ¼¶¼g©ó¶l¥ó·s»D
:2s4ev1F1h4vb5U1@.uni-berlin.de...
> Hi Angi,
> what about
>
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> declare @.cReturn char(9)
> set @.cReturn = (case @.cemk
> when '1' then 'XX0400100'
> else 'XX0400200'
> end)
> return @.cReturn
> END
> HTH
> Meinhard
> "angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
> news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > My Function as follow-->
> >
> > CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> > RETURNS CHAR(9)
> > AS
> > BEGIN
> > IF @.cemk = '1'
> > BEGIN
> > RETURN 'XX0400100'
> > END
> > RETURN 'XX0400200'
> > END
> >
> > My Case When statement like this-->
> >
> > CASE
> > WHEN iv_cemk = 0 THEN 'XX0400200'
> > ELSE 'XX0400100'
> > END AS iv_cemk
> >
> > And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> > Function?
> > How should I do?
> >
> > Thanks!
> > Angi
> >
> >
>

How to use CASE WHEN statement in Function

Hi,
My Function as follow-->
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
IF @.cemk = '1'
BEGIN
RETURN 'XX0400100'
END
RETURN 'XX0400200'
END
My Case When statement like this-->
CASE
WHEN iv_cemk = 0 THEN 'XX0400200'
ELSE 'XX0400100'
END AS iv_cemk
And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
Function?
How should I do?
Thanks!
Angi
Hi Angi,
what about
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
declare @.cReturn char(9)
set @.cReturn = (case @.cemk
when '1' then 'XX0400100'
else 'XX0400200'
end)
return @.cReturn
END
HTH
Meinhard
"angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My Function as follow-->
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> IF @.cemk = '1'
> BEGIN
> RETURN 'XX0400100'
> END
> RETURN 'XX0400200'
> END
> My Case When statement like this-->
> CASE
> WHEN iv_cemk = 0 THEN 'XX0400200'
> ELSE 'XX0400100'
> END AS iv_cemk
> And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> Function?
> How should I do?
> Thanks!
> Angi
>
|||Hi, Meinhard
Thank you very much!
And there is aother way like follow..
BEGIN
RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
ELSE 'XX0400200'
END
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
And I have another question there, syntax like follow..
CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
RETURNS CHAR(9)
AS
BEGIN
RETURN
CASE--XY13AA
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.csct = 'f1' THEN 'XY1300100'
WHEN @.csct = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13BB
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13CC
WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
END
There are 3 different CASE WHEN conditions and how could I combine it in a
Function?
Thanks!
Angi
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> glsD
:2s4ev1F1h4vb5U1@.uni-berlin.de...
> Hi Angi,
> what about
>
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> declare @.cReturn char(9)
> set @.cReturn = (case @.cemk
> when '1' then 'XX0400100'
> else 'XX0400200'
> end)
> return @.cReturn
> END
> HTH
> Meinhard
> "angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
> news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
>
|||Same as the answer in the previous post. Just assign to variables in your
logic, then RETURN the variable at the end.
Get the CASE statements working stand-alone in QA first.
Jeff
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:eqMxG95pEHA.2484@.TK2MSFTNGP09.phx.gbl...
> Hi, Meinhard
> Thank you very much!
> And there is aother way like follow..
> BEGIN
> RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
> ELSE 'XX0400200'
> END
> END
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> And I have another question there, syntax like follow..
> CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
> RETURNS CHAR(9)
> AS
> BEGIN
> RETURN
> CASE--XY13AA
> WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
> CASE
> WHEN @.csct = 'f1' THEN 'XY1300100'
> WHEN @.csct = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> CASE--XY13BB
> WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
> CASE
> WHEN @.cect = 'f1' THEN 'XY1300100'
> WHEN @.cect = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> CASE--XY13CC
> WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
> CASE
> WHEN @.cect = 'f1' THEN 'XY1300100'
> WHEN @.cect = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> END
> There are 3 different CASE WHEN conditions and how could I combine it in a
> Function?
> Thanks!
> Angi
>
> "Meinhard Schnoor-Matriciani" <codehack@.freenet.de> glsD
> :2s4ev1F1h4vb5U1@.uni-berlin.de...
>

How to use Case Statement

I tried and came up with no solution.
I need to use Stored procedure and no dynamic sql.
I ma using ms sql 2000 service pack 3
I am using three different tables. How I can make it to run union statements
only for those table where passing values for the variable is 1
Thanks
Tanweer
Declare @.people_list int
Declare @.client_list int
Declare @.joborder_list int
set @.people_list=1
set @.client_list =0
set @.joborder_list =1
select id, code form people_list
union
select id, code form Client_list
union
select id, code form joborder_listselect id, code form people_list
WHERE @.people_list = 1
union
select id, code form Client_list
WHERE @.client_list = 1
union
select id, code form joborder_list
WHERE @.joborder_list = 1
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 16:22:01 -0700, Tanweer
<Tanweer@.discussions.microsoft.com> wrote:

>I tried and came up with no solution.
>I need to use Stored procedure and no dynamic sql.
>I ma using ms sql 2000 service pack 3
>I am using three different tables. How I can make it to run union statement
s
>only for those table where passing values for the variable is 1
>Thanks
>Tanweer
>
>Declare @.people_list int
>Declare @.client_list int
>Declare @.joborder_list int
>set @.people_list=1
>set @.client_list =0
>set @.joborder_list =1
>select id, code form people_list
>union
>select id, code form Client_list
>union
>select id, code form joborder_list
>
>
>|||Try:
SELECT id, code form people_list
WHERE @.people_list = 1
UNION
SELECT id, code form Client_list
WHERE @.client_list = 1
UNION
SELECT id, code form joborder_list
WHERE @.joborder_list =1
Also, consider using UNION ALL instead of UNION unless you need to remove
duplicate rows in the result.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:86921632-36FA-4AD8-857D-4A639725B574@.microsoft.com...
>I tried and came up with no solution.
> I need to use Stored procedure and no dynamic sql.
> I ma using ms sql 2000 service pack 3
> I am using three different tables. How I can make it to run union
> statements
> only for those table where passing values for the variable is 1
> Thanks
> Tanweer
>
> Declare @.people_list int
> Declare @.client_list int
> Declare @.joborder_list int
> set @.people_list=1
> set @.client_list =0
> set @.joborder_list =1
> select id, code form people_list
> union
> select id, code form Client_list
> union
> select id, code form joborder_list
>
>
>|||First of all, CASE is an expression, not a statement, so it has to be used
as a value inside a statement. There is probably some way to get a CASE
expression to help you hear, but you might try something like this instead:
Declare @.people_list int
Declare @.client_list int
Declare @.joborder_list int
set @.people_list=1
set @.client_list =0
set @.joborder_list =1
select id, code form people_list
where @.people_list=1
union
select id, code form Client_list
where @.client_list = 1
union
select id, code form joborder_list
where @.joborder_list =1
So the where clauses will resolve to either WHERE 1=1, which will return all
the rows, or WHERE 0 =1 which will return none of the rows.
HTH
Kalen Delaney, SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:86921632-36FA-4AD8-857D-4A639725B574@.microsoft.com...
>I tried and came up with no solution.
> I need to use Stored procedure and no dynamic sql.
> I ma using ms sql 2000 service pack 3
> I am using three different tables. How I can make it to run union
> statements
> only for those table where passing values for the variable is 1
> Thanks
> Tanweer
>
> Declare @.people_list int
> Declare @.client_list int
> Declare @.joborder_list int
> set @.people_list=1
> set @.client_list =0
> set @.joborder_list =1
> select id, code form people_list
> union
> select id, code form Client_list
> union
> select id, code form joborder_list
>
>
>|||Love you guys
Great help
Thanks very much
Tanweer
"Kalen Delaney" wrote:

> First of all, CASE is an expression, not a statement, so it has to be used
> as a value inside a statement. There is probably some way to get a CASE
> expression to help you hear, but you might try something like this instead
:
>
> Declare @.people_list int
> Declare @.client_list int
> Declare @.joborder_list int
> set @.people_list=1
> set @.client_list =0
> set @.joborder_list =1
> select id, code form people_list
> where @.people_list=1
> union
> select id, code form Client_list
> where @.client_list = 1
> union
> select id, code form joborder_list
> where @.joborder_list =1
> So the where clauses will resolve to either WHERE 1=1, which will return a
ll
> the rows, or WHERE 0 =1 which will return none of the rows.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:86921632-36FA-4AD8-857D-4A639725B574@.microsoft.com...
>
>

How to use Case or If statement in Inner Join ?

Hi All,

I am in need for to write the following query for a stored procedure in SQL Server 2000. Please observe the T-SQL code first.
Please disregard the numbers and Product Numbers, they are not the correct data. I used them for ease of understanding. But the query is identical.

Code Block

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
CASE WHEN @.vchSubscription = 'Weekly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
AND C.dtInsertDate > = @.dtIssueDate

CASE WHEN @.vchSubscription = 'Monthly' THEN

INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
AND C.dtInsertDate > = @.dtIssueDate

END

GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

my requirement is join the same table, but depending on the subscription type i have to join to different product numbers.
I hope you understand. I have been trying this since yesterday, but no luck.

Any help would be greatly appreciated.Maybe this:

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM
ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR
( @.vchSubscription = 'Weekly' AND PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
OR
( @.vchSubscription = 'Monthly' AND PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
)
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8|||

MadhuBabu,

You can use different statements or dynamic sql. You can not use "case" to control program flow. It is a function, so it has to return something when used.

if @.vchSubscription = 'Weekly'

select ...

else

if @.vchSubscription = 'Monthly'

select ...

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Hi Dale J Smile

Thank you very much for the answer. Well, just before 30 minutes I figured another less impressive method which is agreed on. following is the code for that

Code Block

DECLARE @.dtIssueDate DATETIME
SET @.dtIssueDate = '2007-10-01'

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'week1', 'week2', .. 'week4' ,
'Jan', 'Feb', ... , 'Dec'
)
)
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

/*
Remove Weekly Subscribers
*/
IF @.vchJournal = 'Monthly'
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('week1', 'week2', .., 'week4')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END
ELSE IF @.vchJournal = 'Weekly' /* Remove Monthly Subscribers */
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('Jan', 'Feb', ... , 'Dec')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END

But for sure, your code is more optimal, and am implementing it now...
Many Many Thanks.

how to use CASE or IF in SSIS

All,

what's data flow task works with CASE to IF ?

CASE WHEN .ACTION = 'TER' THEN COL 1 ELSE COL 1 end

Thanks

The Derived Column transformation allows you to do this. This is the operator that you're interested in: http://msdn2.microsoft.com/en-us/library/ms141680.aspx

-Jamie

how to use cascade parameters with report model

Hi,

I know the use of cascading parameters when you use queries in your dataset.

But could somebody explain to me how to use them when using a reportmodel ?

Vinnie

Ok,

So after long research and testing and testing and test......

i have finaly found the solution.

In order to use cascading parameters with datasets based on models this is how you proceed.

Use the same filtersettings in your datasets that you want to limit to a previous dataset. (ok, i know this sounds like sh...)

but i will explain with an example.

supose you have in your dataset based on an entity with various fields like datetime, userid, username, ...

the goal is to make a report that will let me select a time period (from ... to ...) and will let me select one or more agents that

worked during that period.

This is how i do that :

Create a first dataset based on an entity (eg: UserData). Select the fields you want to show into the report.

Next specify the filters you want to apply to the report.

First we will define your start and end date :

drag your datetime field into the filter area (this will be your start date) and drag the same field back again in the filter area

(this will be your end date). click on the first filter (equals) and change it to 'on or after'. Do this also for the second filter and

change it into 'on or before'. Then click on both of the filters and click on 'prompt'. (this is to indicate to the program that it has

to prompt you to fill in the parameter. Click ok to confirm these filters and go to report parameters menu. Normaly there should

be two parameters created (DateTime and DateTime1 or something like that). Change the type to a datetime of both the

params. You can also change the label if needed.

So you run the report now, you will be first prompted to set a start and an end date.

Now to setup a third parameter (i want a list of the users who worked during that period i indicated with my start and end

date)

Open the first dataset, go to filters and drag and drop the userID (or wathever) entity into the filters. Make it again so it prompts

you for an input. Change "equals" into "in a list".(this is to be able to select more then one user)

Create a second dataset based on the same entity. Select UserId and UserName as fields you want to use in that dataset.

Go to filters and do the same action with the datetime field as you did in dataset1.

drag your datetime field into the filter area (this will be your start date) and drag the same field back again in the filter area

(this will be your end date). click on the first filter (equals) and change it to 'on or after'. Do this also for the second filter and

change it into 'on or before'. Then click on both of the filters and click on 'prompt'. (this is to indicate to the program that it has

to prompt you to fill in the parameter. Click ok to confirm these filters

Now go again to report parameters menu. A third parameter will be created (Userid). Select this parameter. It will be defined

as a string. check the multi value option. In the available values select from query. select "dataset2" (or whatever you named

it). select userid as value field and username as label field..

Now, when you run the report, and you input the start and end date, your user selection will be limited to your selected time

period (this can be seen because it will take a few moments to populate the user selection listbox).

I know this is a long explanation but i hope it is clear. After all the solution was simple, but i didn't find the answer.

Greetings