Database Discussion Boards

Error handling in sql 2000 trigger

codemunch22-Apr-09 10:10

22-Apr-09 10:10

Hello All,

I've been googling and based on many articles I belive what I'm asking of sql2000 triggers is not possible for the particular type of error i'm getting.

I am trying to integrate with an existing application on sql 2000 without affecting it's behaviour. When it inserts/updates/deletes in a few tables I need to perform an I/U/D in a sql 2005 DB through a DB link. This is all set up fine, as well as the trigger to do the insert. I need to be able to put error handling in the trigger so that it does not affect I/U/D of the existing product.

Right now, MSDTC is having a fit because I don't have it configured properly on purpose. I did configure it correctly and then reverted so it would raise errors so I could test error handling.

Nothing I've tried so far will let me handle this error in an insert trigger on sql 2000:

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator.

Execution stops immediately so I can't even get to an "IF @@ERROR" statement and the transaction is rolled back. I know how to fix this error but I need to be able to handle this type of error so I can use xp_logevent or something similar to monitor. Is it possible - Any ideas?

Does anyone know of a way in sql 2000 to handle this type of error in a trigger on sql 2000?

SQL SCRIPT

kibromg22-Apr-09 5:25

kibromg

22-Apr-09 5:25

Hi Guys,

I am having problem when running the Following SQL script.It takes Pretty long time to return result.
I am having about 500,000 records in my database.

select count(distinct(CR_Cli)),SO_name from Callrecords inner join dbo.StudioOperators
On CR_StudioOperatorID=SO_ID
where datename(month,cr_callstart)='April'
and Cr_cli not in (select Cr_cli from Callrecords where cr_callstart<'2009-03-31')
group by SO_name desc

Is there any way i can write this script?

Thanks you very much.

Re: SQL SCRIPT

David Mujica22-Apr-09 5:43

David Mujica

22-Apr-09 5:43

In SQL Server Management Studio, place this query in the workspace and click on the icon that has a description of "display estimated execution plan". This will give you an idea of where there may be table scans and where you might be able to add an index to improve your performance.

You should also check to see if your statistics are updated on the tables and indexes involved in this query before running the "display estimated execution plan" as described above.

David

Re: SQL SCRIPT

kibromg22-Apr-09 6:44

kibromg

22-Apr-09 6:44

Thank you so much.It works

UPDATE statement using a view

Shrimpersfan22-Apr-09 4:19

Shrimpersfan

22-Apr-09 4:19

Hi there I am trying to update a field in our contact table. I have a view which contains all of the contacts that i need to run the update for, my question is:

How do i update a contact table but only update the contacts that are in my custom view?

Re: UPDATE statement using a view

Eddy Vluggen22-Apr-09 5:17

Eddy Vluggen

22-Apr-09 5:17

This example is based on the AdventureWorks database;

UPDATE HumanResources.Employee
   SET Title = 'updateFromView'
  WHERE EmployeeID IN (SELECT EmployeeID FROM HumanResources.vEmployee)

The select statement gets a list of ID's in the view. You can then update all the records in the table that have this particular ID. You'll need something to identify each record individually for this to work. If there isn't, then try to add the primary key to the view. If there is no primary key defined in the base-table, add an identity to fullfill this role.

Good luck Smile | :)

I are troll Smile | :)

Creating a temporary table

fly90422-Apr-09 4:04

fly904

22-Apr-09 4:04

Hi,

I have a table which looks like the following.

OriginalTable
OriginalTable_Id(pk)	OriginalTable_Name(unique)
---------------------------------------------------
1			NewColumnName1
2			NewColumnName2
...

I need to make it look like this table below dynamically:

NewTable
NewTable_Id(pk)     NewColumnName1     NewColumnName2     ...
---------------------------------------------------------------

I know I need to create a new tempory Table (using tempDB?), but I don't know how to get the values in the table to be the new column names.

I have this at the moment:

CREATE TABLE NewTable
(
   NewTable_Id INT PRIMARY KEY,
   
   ???
   
)

???:
Would it be something like:
SELECT OriginalTable_Name FROM OriginalTable

Any tutorials or advice would be greatly appreciated.

Thanks in advance.

hmmm pie

Re: Creating a temporary table

Syed Mehroz Alam22-Apr-09 6:49

Syed Mehroz Alam

22-Apr-09 6:49

Have a look at pivot clause here[^]

Regards,
Syed Mehroz Alam

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Re: Creating a temporary table [SOLVED]

fly90422-Apr-09 8:03

fly904

22-Apr-09 8:03

Brilliant, that's exactly what I was looking for thanks alot.

But how can I get the name of the columns dynamically.

eg.

The value is '198' so how can I make it the column name without knowing the value?

Edit:

Thanks again for your help.

Two minutes after posting this reply and Googleing 'Dynamic Pivots' I found this[^] which solved my problem.

Sorry for the inconvenience.

hmmm pie

modified on Wednesday, April 22, 2009 2:15 PM

Stored procedure with XML as it paramter

ONeil Tomlinson22-Apr-09 3:40

ONeil Tomlinson

22-Apr-09 3:40

Hi I have a stored procedure that takes an XML as a parameter. The problem is that anyone could call it passing in any XML. How do i write the stored procedure so that the XML that it takes in as a parameter is based on a specific schema?

Thanks
Neil

Error when I run my C# program that work's with Oracle

E_Gold22-Apr-09 1:20

E_Gold

22-Apr-09 1:20

Hi
I Made Setup from my C# program (that work's with Oracle 10g)
when I try to run this program - in different Computer, i got this error message:
unable to load dll 'OraOps10.dll'

what it can be ?

thank's in advance

Re: Error when I run my C# program that work's with Oracle

SeMartens22-Apr-09 4:05

SeMartens

22-Apr-09 4:05

Did you add the reference to OraOps10.dll to your project? Did you copied this library also to the output folder (where your app is located)?

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Re: Error when I run my C# program that work's with Oracle

E_Gold22-Apr-09 19:12

E_Gold

22-Apr-09 19:12

Yes I do

Re: Error when I run my C# program that work's with Oracle

SeMartens22-Apr-09 21:12

SeMartens

22-Apr-09 21:12

Maybe you have to install some kind of oracle client at the target computer.

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Re: Error when I run my C# program that work's with Oracle

E_Gold23-Apr-09 4:44

E_Gold

23-Apr-09 4:44

I think so, but which file's I need ?

Re: Error when I run my C# program that work's with Oracle

SeMartens23-Apr-09 5:03

SeMartens

23-Apr-09 5:03

You need the Oracle Data Access Components. They can be downloaded at http://www.oracle.com/technology/software/tech/windows/odpnet/index.html[^]

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Connecting MS-ACCESS via asp.net c#

sachees12321-Apr-09 23:14

sachees123

21-Apr-09 23:14

Friends,

I want to connect MS-ACCESS database through asp.net C#(LOCALLY).

help

Re: Connecting MS-ACCESS via asp.net c#

Eddy Vluggen21-Apr-09 23:19

Eddy Vluggen

21-Apr-09 23:19

Help with what exactly? The connectionstring? The C# code? What provider?

Start here[^].

I are troll Smile | :)

Image in SSRS Header [modified]

Hakmeh Mohannad21-Apr-09 20:27

Hakmeh Mohannad

21-Apr-09 20:27

Hi
i have image stored in the DB, i could retreive it and dispaly it the body of the report but when i try to view it the header i can not because is not allowed to reffrence to dataset feild in the header,

i tried the Convert.ToBase64String but it did not work for me, it does not give an error but nothing is displayed

so please can you help here

modified on Wednesday, April 22, 2009 2:58 AM

Save ROWCOUNT value in a variable

nt_virus21-Apr-09 5:35

nt_virus

21-Apr-09 5:35

Hello. I want to save the RowCount returned value in a variable and want to print (+1 with 'A' prefix).

I created a variable as int.

declare @TotalIntRow as int

Now I'm unable to assign the returned value of rowcount in a variable.

i use these ..

@TotalIntRow = select Count(*) from Rcount

Error :- Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@TotalIntRow".

set @TotalIntRow = select Count(*) from Rcount 

Error : - Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.

[I also tried to add a demo value , Set @TotalIntRow = 1; , but showing message number 137 as above mentioned]

How could I save the value in it.

Re: Save ROWCOUNT value in a variable

Eddy Vluggen21-Apr-09 6:39

Eddy Vluggen

21-Apr-09 6:39

Try this one;

DECLARE @TotalIntRow AS INT
    SET @TotalIntRow = (SELECT COUNT(*) 
                          FROM Rcount)
PRINT @TotalIntRow

Don't forget that the DECLARE statement should be used to declare the variable, before using it.

Hope this helps Smile | :)

I are troll Smile | :)

Re: Save ROWCOUNT value in a variable

nt_virus21-Apr-09 7:43

nt_virus

21-Apr-09 7:43

Awesome mate.

But I cant understand one thing, if I do these steps individually I got scalar error something. why so ? just curious about it. Ain't the values are stored permanently in it?

BTW.. If even I run the above scripts do the variable takes additional space or they get removed after application shutdown?

Begin
DECLARE @TotalIntRow AS INT
    SET @TotalIntRow = (SELECT COUNT(*) 
                          FROM Rcount)
print @TotalIntRow + 1
Insert into Rcount Values(@TotalIntRow)
End

I m getting my partial desired result. I want to add prefix 'A' with the result set .. When I did .. I got , can't convert (variable declared for storing A) to int data type..

Re: Save ROWCOUNT value in a variable

nt_virus21-Apr-09 7:56

nt_virus

21-Apr-09 7:56

Oh my god, I can't believe I did it..

Begin
DECLARE @TotalIntRow AS INT
    SET @TotalIntRow = (SELECT COUNT(*) 
                          FROM Rcount)
print @TotalIntRow + 1
Insert into Rcount Values('A'+ Convert(varchar, @TotalIntRow))
End

Re: Save ROWCOUNT value in a variable

Eddy Vluggen21-Apr-09 8:33

Eddy Vluggen

21-Apr-09 8:33

Well done dude Cool | :cool:

nt_virus wrote:
But I cant understand one thing, if I do these steps individually I got scalar error something. why so ? just curious about it. Ain't the values are stored permanently in it?

Not permanent, they are 'lost' once the command is finished executing. (In this case, once it passes the END statement) That's also the reason why they can't be executed separate, they are interpreted as separate commands if you execute them line by line separately.

nt_virus wrote:
BTW.. If even I run the above scripts do the variable takes additional space or they get removed after application shutdown?

They are gone as soon as the script (the complete collection of lines) are finished, unless you store them in a table somewhere. Most applications execute their script and close the connection to the database when done.

Good work Smile | :)

I are troll Smile | :)

Missing data recovery

Shrimpersfan21-Apr-09 1:15

Shrimpersfan

21-Apr-09 1:15

I am using a piece of automation software called Taskcentre, this has it's own database. This morning we were having issues with the software and it turns out that an essntial table has been cleared of all data.

There are currently no backups for the database and all tasks that have been created over the past year are now gone unless i can find a way of restoring the database to 2 - 7 days ago.

I was wondering if anyone had any ideas.

David

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.