Query error handling

Here's what I am trying to do:
I have a large SQL script that I need to run often. I want to be able to
run it and get a summary of the errors that occurred (possible info - where
the error occurred, etc.). If at all possible I would like to stop
execution of the script at the point of error as well.
What kind of strategies are available to accomplish this?
Currently I am using Query Analyzer and can output the results to a text
file, but I still need to scan the text file with grep to pull meaningful
entries. Are there any good third party query tools?
Thanks in advance for any advice,
Greg
Greg Michalopoulos wrote:
> Here's what I am trying to do:
> I have a large SQL script that I need to run often. I want to be
> able to run it and get a summary of the errors that occurred
> (possible info - where the error occurred, etc.). If at all possible
> I would like to stop execution of the script at the point of error as
> well.
> What kind of strategies are available to accomplish this?
> Currently I am using Query Analyzer and can output the results to a
> text file, but I still need to scan the text file with grep to pull
> meaningful entries. Are there any good third party query tools?
> Thanks in advance for any advice,
> Greg
You need to check after each statement. SQL Server 2005 offers a
TRY..CATCH mechanism.
-- Run statement
If @.@.error != 0
Begin
Raiserror ('This error occurred here', 16, 0)
Goto Error
End
Return 0
Error:
Print 'batch terminated'
Return 1
David Gugick
Imceda Software
www.imceda.com

Query doesnt find existing data

SQL Server 2000 Enterprise

While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.

For example:

SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;

would not find the data sought. However, if I put in:

SELECT address FROM tblPersonalInformation WHERE name = Doe, John;

the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but cant figure out a reason. The front-end application displays the data without any apparent problems.

Ideas please.On the surface, it doesn't seem plausable...

Post the DDL of the Table, so we can see...|||Service pack #?|||Trailing spaces, perhaps?

What is the datatype of [ID Number]?|||Do this and let us know what you get:

select * from tblPersonalInformation where charindex(char(160), [ID Number]) > 0|||I have seen corrupt indexes cause this. Try

dbcc dbreindex ('tblPersonalInformation')

Query doesnt find existing data

SQL Server 2000 Enterprise

While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.

For example:

SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;

would not find the data sought. However, if I put in:

SELECT address FROM tblPersonalInformation WHERE name = Doe, John;

the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but cant figure out a reason. The front-end application displays the data without any apparent problems.

Ideas please.What is the datatype of [ID Number]?

Query dbowner

Hi

We have a script that allows customers to change the users of the
database including the database owner. This script can be run at any
time. However, sometimes (and it really is only sometimes!) when the
following statement executes:

"exec sp_changedbowner @.USER_OWNER"

the following error is reported:

"The proposed new database owner is already a user in the database."

I have checked the other postings and the Books Online but cannot find
a way to query the current name of the dbowner i.e. the value of
@.USER_OWNER above. We have to be able to do this within the script so
that if the @.USER_OWNER variable is already the dbowner we do not
bother to execute the statement as otherwise this could display the
error. Can anybody help?

Thanks in Advance

PaulEM gets it from exec sp_MSdbuseraccess N'db', N'%'

which calls

select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status,
o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled')
from master.dbo.sysdatabases o where o.name = @.qual

so

select suser_sname(o.sid) from master.dbo.sysdatabases where name = 'DBNAME'
should do it.

Danny

<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.f14g2000cwb.googlegr oups.com...
> Hi
> We have a script that allows customers to change the users of the
> database including the database owner. This script can be run at any
> time. However, sometimes (and it really is only sometimes!) when the
> following statement executes:
> "exec sp_changedbowner @.USER_OWNER"
> the following error is reported:
> "The proposed new database owner is already a user in the database."
> I have checked the other postings and the Books Online but cannot find
> a way to query the current name of the dbowner i.e. the value of
> @.USER_OWNER above. We have to be able to do this within the script so
> that if the @.USER_OWNER variable is already the dbowner we do not
> bother to execute the statement as otherwise this could display the
> error. Can anybody help?
> Thanks in Advance
> Paul|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

Thanks Danny.|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.

Thanks Danny.|||One method to retrieve the current database owner:

DECLARE @.CurrentOwner nvarchar(256)
SELECT @.CurrentOwner = SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()

You can then conditionally change the owner:

IF @.CurrentOwner <> @.USER_OWNER
EXEC sp_changedbowner @.USER_OWNER

However, your error message is most likely because the new database owner is
a current database user rather than the database owner. The query below
will identify this condition as well as the database owner.

SELECT
l.name as LoginName,
u.name AS UserName
FROM master..syslogins l
JOIN sysusers u ON u.sid = l.sid
WHERE l.sid = SUSER_SID(@.USER_OWNER)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.f14g2000cwb.googlegr oups.com...
> Hi
> We have a script that allows customers to change the users of the
> database including the database owner. This script can be run at any
> time. However, sometimes (and it really is only sometimes!) when the
> following statement executes:
> "exec sp_changedbowner @.USER_OWNER"
> the following error is reported:
> "The proposed new database owner is already a user in the database."
> I have checked the other postings and the Books Online but cannot find
> a way to query the current name of the dbowner i.e. the value of
> @.USER_OWNER above. We have to be able to do this within the script so
> that if the @.USER_OWNER variable is already the dbowner we do not
> bother to execute the statement as otherwise this could display the
> error. Can anybody help?
> Thanks in Advance
> Paul|||Thanks to both of you for replying to my post.
I have now solved this and proven that both methods will work.

Thanks.