Passing Job to a JobStep

Back in the day of COM and DMO. You could create a COM component, create an SQL job, and using an 'AcitveX Script' job step pass the Job to the component.

The component could then lookup the job schedule using DMO to figure out how long it should run.

Now in the days of SMO and CLR. I want to pass the Job to a CLR Stored Procedure as part of a Transact SQL step... (without hard coding the JobId in the script)

any help is appreciated...

rich

Well, I guess I'll have to do some extra work.

I will automate the job creation and pass put the JobId into the Trasact SQL.

The reason I need this is that the sproc is long running and manages it's own schedule. After it executes it determines the next runtime and updates the schedule on the job

rich

sql

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

pass variables to the sql

Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SMYou can do this by saving all your update statments to .sql or .txt file.
so when ever u need to run the update just call the .sql or .txt file from Sql prompt

SQl>@.xx.sql or
SQl>@.c:\orawin\bin\aa.txt; Make sure you call the file from correct place.

If you want the values to be changed every time.U can do this as follws

UPDATE PS_TEST_TBL
SET TESTFIELD1 = &TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A; &TEST1A for numeric values and '&TEST1A' for char

So the system will prompt :Enter value for TEST1A

Originally posted by meelagupta
Hi guys,
I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
from eg:
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1A
WHERE TESTFIELD2 = TEST2A
AND TESTFIELD3 = TEST3A
;
UPDATE PS_TEST_TBL
SET TESTFIELD1 = TEST1B
WHERE TESTFIELD2 = TEST2B
AND TESTFIELD3 = TEST3B
;
instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
is this possible.. we will save lot of time..

please advise.

Thanks,
SM|||Another way would be to create a stored procedure with the update statements, and pass the values in as parameters.

pass sql error to ksh

Can someone tell me how to pass the sql error to the
ksh script? we would like to trap for certain error messages
coming from sql, but because the script processes good we
don't seem to get the "record not found" from the sql
ThanksIf you are using SQL*Plus, check its WHENEVER SQLERROR command.

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.

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!