How can I implement error handling within a SQL script that us

Hi Hugo,
I was thinking in similar lines as the solution you gave (not the one
with severity 20.. I would have never come up with such a solution :)
But I think there is a problem in the other one. We are talking about
different batches. and these batches will be creating database objects.
Can you give an example on how will you implement the error handling with
temp tables for this scenario?
1. Create a view on some existing table first (select *)
2. If the view creation is successful,then create another view on this view.On Thu, 1 Jun 2006 22:35:01 -0700, Omnibuzz wrote:
(snip)
>Can you give an example on how will you implement the error handling with
>temp tables for this scenario?
>1. Create a view on some existing table first (select *)
>2. If the view creation is successful,then create another view on this view.[/color
]
Hi Omnibuzz,
There's one thing I hadn't thought about - CREATE VIEW must be the only
in a batch, so you'll have to use dynamic SQL to make it conditional.
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
EXEC ('CREATE VIEW v1
AS
SELECT 1 AS a')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
EXEC ('CREATE VIEW v2
AS
SELECT a
FROM v1')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
go
Hugo Kornelis, SQL Server MVP

how can I have this package execute in loop....I want to change every lines that are not c

declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')Without a problem description, including table DDL and sample data, a
meaningful response is not likely.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:eZTmg.15162$XT2.164998@.wagner.videotron.net...
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>

How can I get the result of a SQL PRINT Statement

I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.

I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.

I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.If you have the SQL Client tools then you can use Profiler to catch the statement that is sent as D-SQL. This is the best way to extract the D-SQLs that are getting executed rather than the Print statement ...

You can on the contrary use a Select and pass this D-SQL statement and catch it in your recordset returned ...

These are some of the options I can think of ...|||Where would I get the SQL Client tools?|||The simplest answer might be to create a DebugLog table and insert your dynamic SQL statement into it.

First, create the table to log your SQL statement:

CREATE TABLE
DebugLog
(
SQLStatement varchar(8000),
AddDate datetime DEFAULT GETDATE()
)

Next, add code to your stored procedure to insert your statement into the log file:

DECLARE @.SQLStatement Varchar(8000)
SET @.SQLStatement = 'SELECT * FROM test'
INSERT INTO DebugLog (SQLStatement) VALUES (@.SQLStatement)
EXEC(@.SQLStatement)

Next, take a look at the SQL statements that were executed (the most recent statement will be on top):

SELECT * FROM DebugLog ORDER BY AddDate DESC

Alternately, you could create an OUTPUT parameter in your sproc and pass the value of @.SQLStatement back to it and display it on your ASP.NET page.

Terri|||Thanks for the help. I'll try that. In point of fact the dynamic sql I'm trying to debug is using sp_executesql with an output parameter. I am trying to retrieve the recordset count based on the criteria parameters I'm passing to it from my asp.net application. In the application, the value returned is zero, which I know cannot be correct. Once I verify the proper assembly of the query, I can concentrate on the syntax of sp_executesql.|||Hi Terri,

When I first saw this post, I was going to respond that PRINT only writes to Query Analyzer. But upon researching it, I found that BOL has this to say about it in the Using PRINT topic:

"The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement."

This suggests that one could capture the error and do something with the PRINT text. But there can be multiple PRINT statements in a sproc, so I assume this would be an SqlException with nested exceptions.

Anyone ever done anything with this? Could be an interesting thing to explore.

Don|||yes, in my data access layer the error handling is like so.

try
{
oCmdExecute.ExecuteNonQuery(); // or ExecuteReader etc...
}
catch(SqlException ex)
{
foreach(SqlError err in ex.Errors)
{
strErrorString = strErrorString + "SqlError: #" + err.Number.ToString () + "\n" + err.Message;
}
strErrorString = strErrorString + "\n\nStored proc: " + sSql + "\n";
}

the SqlError collection will contain all the print statements, of course this only when the stored procedure fails, so you would have to call RAISERROR(' test error ', 16, 1) to force it into the exception block.

Note: that is RAISERROR not RAISEERROR go figure?|||Cool.

Note: that is RAISERROR not RAISEERROR go figure?

Yeah, that's been something odd since the statement was first added to T-SQL.

Don