How to trap EXECUTE failure

We are running SQL 2000 sp3a on Win2003 Server with all patches and SPs applied.

I want to run a stored procedure from a SQL Agent job. I want the stored procedure to run as a TRANSACTION to force a ROLLBACK in case of failure. However, if execute the procedure and an error of severity level 20 (I think) or greater happens I get blown out of the job immediately. Therefore I cannot call RAISEERROR or use @.@.ERROR. So, how do I assure that I can ROLLBACK the TRANSACTION?

I have searched the web and BOL and cannot find anything that addresses this specifically.

Thanks in advance for any help.Severity level 20 indicates a fatal error that the session encountered. The batch terminates and the connection is severed. The fate of your transaction is predetermined, - it will roll back. You (your spid) cannot roll it back with ROLLBACK because the batch execution will not reach that point.

How to take a SP like a table

Hi

Any one knows how to take a SP(stored procedure) like a table, obviously the SP throws a search

or other way that can help me is if when I execute a varchar, but i

want to manipulate like a table, the varchar has a simple search like

select * from user

Thanks for your help

I can't work out what you're asking for here. Perhaps you want a View, which is code like a SP, but can be referenced as if it was a table ?

|||

You can use OPENQUERY for that:

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/580d0f2c5079e063

HTH, Jens Suessmeyer.

How to structure a FTS query.

Hi
I'm having some problems structuring my query to execute with relevant
results. Basically I have 1 table that has been FT indexed. The table has a
CountryName, GroupName and ItemName.
A user may enter a query as 'India houshold expenditure'. India is in the
CountryName column and household expenditure in the ItemName colum.
When I use a FREETEXT I end up with all GroupNames and ItemNames that
contain household expenditure. Using CONTAINS produces an error:
Syntax error near 'household' in the full-text search condition 'India
household expenditure'.
Alternatively the user may just enter 'India' or 'household consumption
expenditure'
I am expecting my resultset to be in the case of a query for 'India
household consumption' to show only India where household consumption appears
in the ItemName. Not India and ItemNames where consumption or expenditure
appear individually.
Does anyone have suggestions on how I should go about structuring my
procedure to capture the query cases describe above? Any feedback would be
greatly appreciated.
Regards
Craig
I think what you will have to do is parse your search phrase looking for the
country, perhaps through a look up table. If it detects a country in the
phrase it will issue the following query
select *from tableName where contains(CountryName, 'CountryName') and
(contains(GroupName,'Remainder of search phrase') or
contains(ItemName,'Remainder of search phrase'))
If, you are only interested in ItemName containing the remainder of the
search phrase you could reduce this to
select *from tableName where contains(CountryName, 'CountryName') and
contains(ItemName,'Remainder of search phrase')
"Craig" <Craig@.discussions.microsoft.com> wrote in message
news:BAC003AD-41C6-4B18-B63E-0914BCCF79DB@.microsoft.com...
> Hi
> I'm having some problems structuring my query to execute with relevant
> results. Basically I have 1 table that has been FT indexed. The table
> has a
> CountryName, GroupName and ItemName.
> A user may enter a query as 'India houshold expenditure'. India is in the
> CountryName column and household expenditure in the ItemName colum.
> When I use a FREETEXT I end up with all GroupNames and ItemNames that
> contain household expenditure. Using CONTAINS produces an error:
> Syntax error near 'household' in the full-text search condition 'India
> household expenditure'.
> Alternatively the user may just enter 'India' or 'household consumption
> expenditure'
> I am expecting my resultset to be in the case of a query for 'India
> household consumption' to show only India where household consumption
> appears
> in the ItemName. Not India and ItemNames where consumption or expenditure
> appear individually.
> Does anyone have suggestions on how I should go about structuring my
> procedure to capture the query cases describe above? Any feedback would
> be
> greatly appreciated.
> Regards
> Craig

How to Store System Variables in DB?

Hi,

I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @.IntDate and also changed the parameter name but the error was the same.

Please help how to write this System Variable value in to that table.

Regards,
Samy

In this situation I recommend using expressions rather than parameters. This article talks about expressions to store a SQL statement for an OLE DB Source component but this principle can apply to Execute SQL Task as well: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

I know Jamie is a fan of using expressions over parameters, but I disagree, and there are situations where parameters are still better. Security being the big issue with expressions.

Anyway, to get your parameter to work, make sure you have set mappend the parameter on the "Parameter Mapping" page of the Execute SQL Task. Select variable StartTime, direction is Input, Data Type is DATE, and Parameter name is 0.

For OLE-DB connections the parameter name is a zero based incrementing count of the parameters.

The Data Type is DATE, which seems rather confusing, as normally DBTIMESTAMP is the SSIS data type to use, however these are clearly not normal SSIS data types. We just do not have enough different types within SSIS!

|||

Dear Darren,

I did what you mentioned, but the package is not functioning:

SSIS package "HRM_tblParty.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at HRM_tblParty: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "HRM_tblParty.dtsx" finished: Failure.

This is my Query:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)

and I mapped the System::StartTime to Parameter0 (Type: Date and Direction: Input)

Pleae help,
Sassan

|||

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

|||

SamyLahur wrote:

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

The OLE DB Source component can be set by selecting a table, entering a SQL statement, or taking a SQL statement that is stored in a variable. You need to select the 3rd of these options.

-Jamie

|||

The parameter name should be 0, nothing else just the character for zero.

For the expression option you need to specify the variable name correctly-

@.[System::StartTime]

You will also need to convert to date time valeu to a string to do the concatenation-

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= '" + (DT_WSTR, 20 )@.[System::StartTime] + "'"

Note the quick and dirty conversion I have used is open to date format misinterpretation issues DMY, MDY etc. You should break it down and convert to component parts into a string as an unambiguous format. For SQL Server yyyymmdd is unambiguous. Of course using parameter support avoids this issue as it is treated as a date type all the way through.

|||

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

|||Have you read my last post? I appreciate that you may well have missed it due to the exceptionally well designed interface and threading capabilities of these forums.|||

SamyLahur wrote:

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

My first guess would be that you need to cast the date as a string.

There is no expression editor on variable expressions which is (and I'm being polite here) very very BAD. It will appear in SP1 but in the meantime here's a tip for you. Build your expression against the "Description" property of the package. This will enable you to use the expression editor to build your expression and validate it within there. When you have it working, copy and paste the expression into the appropriate place for the variable.

Hope that makes sense.

-Jamie

How to stop execution of DTS package (during loop)

I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.

ActiveX Script 1

Option Explicit

Function Main()

Dim vDate, vCounter, vBranchCode, vPath

vDate="011207"

vCounter=DTSGlobalVariables("gVarCounter").Value

IF vCounter<=3 THEN

IF vCounter=1 THEN
vBranchCode="ALB"
vPath= "D:\PROJECTS\HRIS\ALB\"
ELSEIF vCounter=2 THEN
vBranchCode="MOA"
vPath= "D:\PROJECTS\HRIS\MOA\"
ELSEIF vCounter=3 THEN
vBranchCode="PSQ"
vPath= "D:\PROJECTS\HRIS\PSQ\"
END IF

DTSGlobalVariables("gVarPath").Value=vPath & vDate & "_" & vBranchCode & ".xls"

Main = DTSTaskExecResult_Success

ELSE
<This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1>
DTSGlobalVariables("gVarCounter").Value=1
<DTS Process should stop execution...how is this?>
END IF

End Function

After excel to sql dts
ActiveX Script2

Function Main()

IF gVarCounter<=3 then
DTSGlobalVariables("gVarCounter").Value=DTSGlobalVariables("gVarCounter").Value+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
END IF

End Function

Thanks a lot.there are many ways to stop a DTS. u can simply write

Main = DTSTaskExecResult_Failure

in the ELSE part and link the next step with "on success" workflow. or u can even write a blank ActiveX step and redirect the flow to that step in the ELSE part with "...DTSStepExecStat_Waiting" as u r already doing|||sorry, i pressed the save button twice... and the same thing got posted twice... there sould be an option to delete a post....|||hi upalsen,

thanks a lot. it's now working. great!
god bless.|||hi LimaCharlie,

thanks for your acknowledgement. many here do not acknowledge the solution they have accepted. an acknowledgement is (1) a recognition for support (2) helps in closing the post (3) helps a third user who is viewing the post at a later date (or coming from search engine) to understand what the final solution could be.

How to stop discovery from adomd.net connection

I am using MSAS 2005 with ADOMD.NET 8.0 with .NET 1.1

I observed whenver we execute any query it does discovery of meta data. The meta data is part of resultset that is returned to the client. You can see this part of XML that is returned to client.

<root xmlns=\"urn:schemas-microsoft-com:xml-analysis:mddataset\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><xs:schema targetNamespace=\"urn:schemas-microsoft-com:xml-analysis:mddataset\" elementFormDefault=\"qualified\" xmlns=\"urn:schemas-microsoft-com:xml-analysis:mddataset\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:complexType name=\"MemberType\"><xs:sequence><xs:any namespace=\"##targetNamespace\" minOccurs=\"0\" maxOccurs=\"unbounded\"...........Very long XML >
<CellData><Cell CellOrdinal=\"0\"><Value>6/Value><FmtValue>6</FmtValue></Cell></CellData></root>

So result is very small XML compare to long XML in its tail.

This causes poor performance at client. How can we avoid discovery and return of meta information to client?

It is currently not possible to turn off this extra information sent by the server.

Adomd.Net 9.0 (shipped with Analysis Services 2005 and available at http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en) uses compression by default so this should significantly reduce an performance issues related to XML size.

|||

We are not upgrading soon to version .NET 2.0 that are prerequisite for ADOMD 9.0.

Is there way in current version to turn it off or use compression to reduce its size?

What is reason Meta information passed to client with every call?

|||

There is not currently a way to disable the metadata and Adomd.Net 8.0 does not support compression.

This extra metadata may be of use to some client applications. My experience is that generally the SlicersAxis is the biggest unexpected extra peice of metadata and it contains all slices that apply to the cellset - both explicit and implict.

How to stop a SSIS package execution from code?

Hi everyone,

After a Execute method I would need to stop a package but I don't know why:

sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)

I have a Events class (EventSSIS) which implements IDTSEvents and have an OnQueryCancel event but without parameters, such so:

Function OnQueryCancel() AsBooleanImplements IDTSEvents.OnQueryCancel

ReturnFalse

EndFunction

Let me know how to pass a boolean parameter because of I can't overloaded OnQueryCancel method

TIA-

I'm sorry, issue is solved.

happy coding,