Passing multiple selections to a stored proc parameter

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@.OrderList varchar(500)
)
RETURNS
@.ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @.OrderID varchar(10), @.Pos int

SET @.OrderList = LTRIM(RTRIM(@.OrderList))+ ','
SET @.Pos = CHARINDEX(',', @.OrderList, 1)

IF REPLACE(@.OrderList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.OrderID = LTRIM(RTRIM(LEFT(@.OrderList, @.Pos - 1)))
IF @.OrderID <> ''
BEGIN
INSERT INTO @.ParsedList (OrderID)
VALUES (CAST(@.OrderID AS int)) --Use Appropriate conversion
END
SET @.OrderList = RIGHT(@.OrderList, LEN(@.OrderList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.OrderList, 1)

END
END
RETURN
END
GO

I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_criI see you parsing the list, but I don't see where you select any records based upon it. So where would your ALL logic be incorporated?sql

Passing data from db to ssis variable

Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?

Thanks,

Execute SQL task can do this.|||I tried, but don't even know where to begin. is there a tutorial or something on the net some where?|||Have you searched this forum? Tons of examples.

Execute SQL task.

If you are selecting where the result is a single row:
Result Set - Single row. Result set tab: Result Name: 0 thru n based on the number of columns. Then simply select the variable name you need to map to.|||There is more than 1 row in my table and 4 columns. I looked at a couple of examples but can't figure it out.|||http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||

I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?

|||

mr4100 wrote:

I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?

Sure you can. What do you mean nothing happens?|||

sorry to keep bugging you and I appreciate your help and patience, but when I open my package the send mail task has error code can't find recipient. I have 4 expressions in this mail task:

toline--set to my to variable

fromline--set to my from variable

subject--set to my subject variable

messagesource--set to my body variable

From what i gather, it's not getting the data from the variables for some reason. Everything seems to look ok.

|||Perhaps you need to add a script task inside the foreach loop so that you can display (via "MsgBox()") the contents of the variables to actually verify that they have data in them.|||i tried that but i get an element is not found in the collection error.|||

mr4100 wrote:

i tried that but i get an element is not found in the collection error.

First, make sure that your variables are of package scope, not some other task scope.

And how did you reference the variables in your script task?|||

they are part of the package and not just a specific task, i also set the delay validation property to true for the foreach loop and mail task, and the error is cannot recognize recipient address. Here is my script code.

Public Sub Main()

Dim Message As String

Message = Dts.Variables("to").Value + " " + Dts.Variables("from").Value + ", " + Dts.Variables("subject").Value

MsgBox(Message)

Dts.TaskResult = Dts.Results.Success

End Sub

|||Try this instead:

Code Snippet

Public Sub Main()
Dim Message as String
Dim vars as Variables
Dts.VariableDispenser.LockForRead("to")
Dts.VariableDispenser.LockForRead("from")
Dts.VariableDispenser.LockForRead("subject")
Dim ToVar as String = vars("to").Value.ToString
Dim FromVar as String = vars("from").Value.ToString
Dim SubjectVar as String = vars("subject").Value.ToString

Message = ToVar + " " + FromVar + ", " + SubjectVar
MsgBox(Message)
Dts.TaskResult = Dts.Results.Success
End Sub

|||well, i get an error with this code as well something about object reference not set to an instance of object

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Pass fields as array to custom function?

I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?

You would need to create a function in the code window that accepts the values and inserts them into an array.

ex.

Code Snippet

Function CreateArray(ByVal field1 As String, ByVal field2 As String) As Array
Dim ar(1) As String
ar(0) = field1
ar(1) = field2
Return ar
End Function

I haven't tested it, but I think it will work.

Simone

|||

You can use the Split function.

http://msdn2.microsoft.com/en-us/library/6x627e5f(VS.80).aspx

|||That is true but you would still need to create a string to split using the required fields. Depending on the data those fields contain, you would need to be careful if they hold the delimeter used in the split function.

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.

Pass data in script component when no transformation needed

Hi,
I have 56 fields coming into the input of an script component, The need for script component was to just to check if one of those 56 columns has a valid date or not, If valid it will parse and put in an output date column, if not, it will put in NULL.

The 55 fields should be passed on. I dont really wanna write code and define output columns. How do I do this ?

Any input in this would be appreciated.

Thanks,As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.|||

jwelch wrote:

As long as the output is synchronous with the input, all the columns should be passed through. You only have to define output columns for new columns you are adding in th script component, which it doesn't sound like you need.

To expand a little, as long as you set the script component to be a "transformation," this is true.

You'll be able to get at them by using Row.XXXX

Just make sure that they are set to READWRITE in the properties when you select the columns to be used in the script component. That way you can assign a value.

The PROBLEM is that you can't change the metadata, so if all of your 55+ columns are strings, but can contain dates, you won't be able to output a datetime data type using the same column name. You'll have to create a NEW output column name, which will have to be done by creating new outputs. You can, however, convert a datetime field to a string inside the script component and will then be able to reuse the column.

Does this make sense?|||

Phil Brammer wrote:

Does this make sense?

Makes me confuse, because I am able to dominate columns by using Script component. I just check its checkbox and it appears in Input list, then I add output column with same name and it works.

Are you talking about something else ?|||

It sounds like you are using an asynchronous output, as a synchronous output throws an error if you create an output column with the same name as an input column.

You might try using a synchronous output. Go to the Inputs and Outputs page of the property dialog, and add an output. Select the new output, and in the properties set the SynchronousInputID to the input for the component. All of the input columns will be available on the synchronous output.