Help With Error Checking

Nov 9, 2007

I have built a procedure to send mail using OLE Automation and want to be able to trap error information when it doesn't work. So in an attempt to do that I have the following stored procedure that will return informaiton if the return value is <> 0. Here is how it is being used:

IF @return <> 0
BEGIN

EXECUTE sp_displayoaerrorinfo @handle, @return

END
ELSE
PRINT 'Success'

This works fine, but I would like to write the error message to a table and so I thought I could just alter to to be:

IF @return <> 0
BEGIN

EXECUTE sp_displayoaerrorinfo @handle, @return = @failure

END
ELSE
PRINT 'Success'

Where @failure is a variable I declared earlier. Then I could insert the value of this variable along with some other infomration into a table that would track the errors. However, when I do this I receive the following:

Error: Procedure or Function 'sp_DisplayOAErrorInfo' expects parameter '@HResult', which was not supplied. Number:201 Severity:16 State:4

So it isn't seeing that I am passing two variables into the stored procedure. I know I must be missing something simple but I've tried a bunch of different itterations and can't seem to get it right. Any help would be great. Thanks.

View 7 Replies


ADVERTISEMENT

Checking For @@Error In DTS Package (was Error Trouble)

Feb 6, 2005

Hi Guys,

SQL Server 2000

I've run out of ideas on what to do with a DTS process that has suddenly started giving an error when one company is processed on it.

Here's the deal:

DTS package launches a complex set of stored procedures (built entirely without error handling) as a step in importing data from various customers. I have isolated the error to one segment where I have to cursor through records that have been identified as having changes. Unfortunately, this cursor is updating about 15 system tables and uses modular stored procedures, about 12 of them, to do the job.

Error: The classic Subquery returned more than one value.

Oddity: If I run the code in Query Analyzer I never get an error, the code finishes without complaints. If I run the exact same code via the DTS package I always get the error. Note that the cursor appears to process every row assigned to it and then the sproc throws the error.

What I've Tried:
1) The obvious, reviewing the code for any unprotected subqueries. Haven't found it.
2) Checking for @@Error = 1 at key points in the code to try to isolate where this is happening. Can't catch it.

Note that this same process is used for several other company's data import process and they do not error even when running the DTS package. Therefore I'm assuming it is a data issue with the one company that errors that is causing the problem.

Any suggestions on what to try next would be greatly appreciated!

Thanks!

View 2 Replies View Related

Checking @@Error

Feb 20, 2003

Hi,

Just a brief question. I have a script which does a number of insert statements. What I would like to do is determine if the insert statements were all successful. Aside from checking @@ERROR after every insert, is there a way to check if all the insert statements completed successfully?

Thanks,

Jim

View 2 Replies View Related

Error Checking Question

Jun 2, 2004

Hi all,

I have a stored procedure that inserts records into the database. It has error checking that checks for transaction failure, and if it happens it rollback the transaction.

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END

My question is how can I test if the error checking works?

View 6 Replies View Related

Error Checking Issue

Mar 12, 2007

Hi All,

I have a stored procedure to which I am adding an error checking. Here is my stored procedure.

CREATE PROCEDURE usp_DBGrowth

AS

DECLARE @dbsize DEC(15,2)
DECLARE @logsize DEC(15,2)
DECLARE @dbname SYSNAME
DECLARE @dbsizestr NVARCHAR(500)
DECLARE @logsizestr NVARCHAR(500)
DECLARE @totaldbsize DEC(15,2)
DECLARE @dbid SMALLINT



DECLARE dbnames_cursor CURSOR
FOR
SELECT name, dbid
FROM dbo.sysdatabases

OPEN dbnames_cursor

FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN


SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 1'

EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
PRINT @dbsize

SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 2'


EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
PRINT @logsize

SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
PRINT @totaldbsize

BEGIN TRANSACTION

IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
@dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)

INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE

COMMIT TRANSACTION


FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid

END

CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor


dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions:
Can you tell me what I am doing wrong?
Also, I need to add error checking after execute sp_executesql statement. Any suggestions?

View 2 Replies View Related

Error Checking Issue

Mar 12, 2007

Hi All,

I have a stored procedure to which I am adding an error checking. Here is my stored procedure.

CREATE PROCEDURE usp_DBGrowth

AS

DECLARE @dbsize DEC(15,2)
DECLARE @logsize DEC(15,2)
DECLARE @dbname SYSNAME
DECLARE @dbsizestr NVARCHAR(500)
DECLARE @logsizestr NVARCHAR(500)
DECLARE @totaldbsize DEC(15,2)
DECLARE @dbid SMALLINT



DECLARE dbnames_cursor CURSOR
FOR
SELECT name, dbid
FROM dbo.sysdatabases

OPEN dbnames_cursor

FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN


SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 1'

EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
PRINT @dbsize

SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 2'


EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
PRINT @logsize

SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
PRINT @totaldbsize

BEGIN TRANSACTION

IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
@dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)

INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE

COMMIT TRANSACTION


FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid

END

CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor


dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions:
Can you tell me what I am doing wrong?
Also, I need to add error checking after execute sp_executesql statement. Any suggestions?

View 14 Replies View Related

SQL Error, Checking For A Date

Nov 24, 2007

Hello,

i have a very simple table called room....

Room_ID Date
1 20/11/2007 00:00:00

and i have a very simple SQL SELECT statement...

SELECT * FROM room WHERE date = '20/11/2007 00:00:00'

i have tried several variations of the query but i either get an error or a null dataset.

can anyone explain where im going wrong ?? i have read about casting but still i cannot return a dataset where i have a date in the statement.

Any help is much appreiciated !!


Truegilly

View 1 Replies View Related

DBCC ERROR ON CHECKING THE DATABASE

Feb 23, 1999

Hi all,
We have been checking the database by dbcc checkdb,through a automated task
on our production server (nightly task run everday)d we always getting the message given below.

Msg 625, Level 20, state 1
Could not retrieve row from logical page 957298 via RID because the entry in the offset table (=0) for that RID (=17) is less than or equal to 0.

If anyone knows about this error message, can you kindly post the message.

Thanks a lot.

Jay.

View 1 Replies View Related

Front-end Input Error Checking Or Backend...?

Feb 3, 2005

This maybe belongs in the Data-Access Forum, but I'm not sure.

Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user.

Are there performance/coding issues involved? Best practices?

View 1 Replies View Related

Checking For @@ERROR After A SELECT Inside A Transaction?

Feb 26, 2006

Is it normal practice to check for @@ERROR  after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.

View 1 Replies View Related

Error Handling In Stored Procedure AND Checking

Jan 3, 2008

I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure:
Code: ( text )

View 4 Replies View Related

Error With Subtotal And Grand Totals With Iff Checking.

Jun 8, 2007



I have a table with amount columns and I want the amount column to either insert the value from the database or a zero based on a condition.



For the table rows I use the following to find the amount:



=iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0)



which works fine. However, when I try to Sum in the group foot I get #Error when I use



=sum(iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0) )



for the groupings that have a type other than Material and Other. For some reason, it doesn't total the amounts of Material and Other with the Zeroes that were placed in the table rows based on the Condition.



For example, the subtotal errors out when trying to total Material with Labor but if it was just Material and Other, it works.



Example of what the Columns are:



job, year, month, type, amount



s57, 2007, 2, labor, 0

s57, 2007, 2, material, 500



month total errors out

year total errors out

job total errors out



Any help would be appreciated.

View 12 Replies View Related

I’m Getting This Error When Checking Out A Report From Visual Source Safe…any Clue As To What It Means?

Mar 22, 2007

Originally got this error as the reason some of my .rptproj files could not be converted from SRS 2000 to 2005.  Now I€™m getting this error when checking out a report from Visual Source Safe 6.0€¦any clue as to what it means?

Project item '4294967294' does not represent a file.

This is for a solution created and stored in VSS using VS2003 and SRS 2000.  Now trying to open with VS 2005.

View 2 Replies View Related

Checking For PK

Feb 10, 2004

Hi,

I want to assign a list columns to be the PK on a table. I don't know what this list of columns would be.How do I go about figuring that?

Sample Data

Col1...Col2...Col3..Col4
1...........2.......A......X
1...........2........B.....X
1...........2.......A......Y



As you can see Col1 & Col2 ONLY do not make up the PK. I need to include Col3 as well. My concern is I am not sure if I have a row like..
1...........2.......A......X reapeated lower down the order. So how do I figure the PK out?

Thanks

View 2 Replies View Related

Checking For Row Existence

Nov 1, 2006

Can someone show me some C# code for detecting if a SQL row exists or not?  This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step.  In my code I'm either going to INSERT or UPDATE a record.  I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response.  Apparently ExecuteNonQuery does not work with SELECT.  I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.So...can anyone share the typical code they use to identify if a row exists or not within a database.  I guess I was execting there to be some method available to do this sort of thing.

View 1 Replies View Related

Checking Value In 2 Tables.

Aug 3, 2007

i want to check a value in 2 tables. the 1st table i want to check if the value exists and from the 2ed table i samething.
i came up with this but when it's doing the second if i get error on the page.
Dim ReturnVal As Integer
Dim ReturnVal2 As IntegerDim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)", conn)
'To check if the # is registered.Dim cmd2 As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [t_RegisterInfoTemp] WHERE ([ReportNumber] = @ReportNumber)", conn)
cmd.Parameters.AddWithValue("@ReportNumber", txtReport.Text)cmd2.Parameters.AddWithValue("@ReportNumber", txtReport.Text)
conn.Open()
ReturnVal = Convert.ToInt32(cmd.ExecuteScalar())
conn.Close()
'This checks if the # is correct.
If ReturnVal > 0 Then
'This checks if its registered with someone else.
conn.Open()
ReturnVal2 = Convert.ToInt32(cmd2.ExecuteScalar())
conn.Close()
If ReturnVal2 <> 0 ThenServer.Transfer("regccinfo.aspx")
Else
lblError.Text = "the # is registered with someone else."
End If
Else
lblError.Text = "the # does not exists."
txtReport.Focus()
End If

View 6 Replies View Related

Checking For A New Record

Jan 5, 2005

Good morning everyone I am writing a windows forms application that will work similar to the windows messenger popup. I need this application to display a message to the user whenever a record is added to table in my DB. Any help would be appreciated in explaining how I can check the DB for new records.

View 3 Replies View Related

Checking Character Set

May 2, 2002

I've forgotten the character set that I've chosen when I was installing the SQL Server 7. Is there a way to check?

I'm currently using US English version of Windows NT4 and SQL7. But interestingly, all the data is in Japanes characters. It's actually for a Japanese website, and the front-end application is written in ASP. I remember reading somewhere that it is impossible to do certain type of sorting (by some particular order for the Japanese language) as it is limited by the choice of the language of the NT OS.

The type of sorting that I'm looking at is the grouping of 5-characters. One example is in http://www.forest.impress.co.jp/aiueo.html

Would this be possible with my current setup? Or would it help if I migrate over to Windows 2000? (I'd rather not move to Japanese NT4)

Your feedback and advice would be very much appreciated!

View 1 Replies View Related

Checking For A Range

Aug 5, 2004

I want to do a check to see if a number falls within a range specified by the user. Basically, they enter in a min and max value and I return a hit if a number falls inbetween.

I have having trouble with my SQL statement:


Code:

SELECT DISTINCT *
FROM Table
WHERE ( (0=0)
AND
(min.value <= Table.Value <= max.value) )


(This isn't my actual code, but just the logic)

It will return all the results everytime. Does anyone have any suggestions on what I could be missing here?

Thanks in advance!

View 2 Replies View Related

Checking Job Status Using SQL-DMO

Jun 27, 2004

I have a question I hope someone can help me with.

My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.

The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.

My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).

Is anyone able to shed some light on this?

I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed

Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?

Thanks for any help,
Andrew

View 2 Replies View Related

Checking Agents

Oct 8, 2007

I have around 25 sql servers(sql server 2000) all on windows server 2003. i would like to know if anyone has a script that will poll all the servers and check to make sure the agent is running.

thanks for your help

View 2 Replies View Related

Table Checking

Jan 9, 2004

I need to check one table while updating another table
suppose i need to check table1's value is whether less then 0 or not
without if condition can i check it with update query?
thank you

View 5 Replies View Related

Backup Checking

Feb 25, 2004

Hello, everyone:

After backup is finished, how to check if the backup is successful, and it is same as the original database? Thanks.

ZYT

View 1 Replies View Related

Checking For Null

May 23, 2008

I have a table that I know has a null or nothing in a field. When I run this query to get a list of them nothing comes back at all but it doesn't error out. Any ideas?

select *
from lab_test_add_conf
where table_name = null
order by lab_test_conf_id

View 5 Replies View Related

Version Checking

Mar 18, 2007

I chaps and chappets.

It's been a while!

This is going to sound ignorant but...
I had a look here http://support.microsoft.com/?id=321185

And one thing isn't clear. How doe you know if the 32-bit or 64-bit version was installed?

------------------------
Me: What do you want to know from your data warehouse?
Client: Err...Emm...Everything
Me: OK, that's great. That's all I need to know. I'll see you when it's done.

View 1 Replies View Related

Checking For Availability

May 22, 2007

i'm not fluent with sql and i have a question to ask..

if i want to check whether a product is available between two dates, how can i do so? for example, i want to check whether it's available between 5/23/2007 to 5/25/2007..

thanks

View 11 Replies View Related

Way Of Checking To See If All SP's Work

Jul 25, 2007

Our System currently has on the upwards of 300 stored procedures in it.

We have SP's that call SP's that call SP's and sometimes a Stored Procedure will return 10 ResultSets which are used by .NET.

Some of our developers do not check other stored procedures when they modify the SP parameters, and in doing so break the system until the testers find it.

is there a way to run a command to check ALL the stored procedures to see if they work, they match the SP they're calling, etc?

View 20 Replies View Related

Data Checking?

Jul 23, 2005

Hey all,prolly a simple solution, but why isn't the following string working inmy execute sql step within DTS? It produces results, just not the onesI want... What am I doing wrong?select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'It's displaying all the records? It should only be displaying thoserecords that do *not* contain letters or numbers.Thanks in advance!-Roy

View 3 Replies View Related

Best Way To Add To DB While Checking For Duplicates

Jul 23, 2005

Situation:Day 1Table contains 100 items of actions imported via FTP. One of the fieldsin the table can be updated to reflect an assigned unit code.Day 2Actions that may be duplicates of the table from Day 1 (with theexception of the updated assigned unit field) are imported forinclusion in the Table from Day 1.Question- What is the best way to insure that any new items are addedto the table and that no duplicates are added at the sametime?Assistance would be appreciated.

View 1 Replies View Related

Security Checking

Nov 16, 2007

SQL Server allows for a user to have SELECT permission on a View withoutthat user requiring an associated SELECT permission on the underlying tablethat the VIEW accesses, but the user can still access the data through theView. A similar arrangement holds true for stored procedures.So based on these initial known behaviours, I have a couple of questions:1. If a stored procedure A executes stored procedure B, does the user of Arequire execute permission for B also? Or will access to B be permittedregardless because the user was given access to A?2. Similarly, if a stored procedure A accesses a View, does the user of Arequire permissions on the referenced View?I guess to paraphrase what I am trying to determine is whether SQL Serveronly checks permissions at the "entry" point of a particular function, orwhether permission checks are performed "intra-function". My openingexamples imply they are only checked "on entry", but I am wondering if thisbehaviour is entirely consistent. Perhaps the SQL Standard mandates this?

View 1 Replies View Related

Checking For Not Null

Apr 1, 2008

Hi,

How can I check in a conditional split for fields not null? I see where I can use the isnull() function but I need to check for is not null.

View 8 Replies View Related

Checking Job Status Using T-SQL

Dec 19, 2006

Hi.

How can i check the job status using SQL Query?

View 25 Replies View Related

Checking For Records Where One Value Is NOT In The Other?

Aug 27, 2007

I am trying to display results in which a value in my first result table is NOT contained in the second result table. Any idea on how to do this?.



SELECT CUSTOMERID, CUSTOMER FROM TABLE1

WHERE CUSTOMERID NOT IN TABLE2

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved