Database

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

16-Jan-09 7:04

i want to apply trigger on a particular table, so that same operation would occur in another table on different server. but whenever i try to perform this step , i received an error that distributed transaction not allowed.

Kindly help me out.

Aman

Wendelius16-Jan-09 8:31

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

16-Jan-09 8:31

Have a look at these:
- Distributed Transactions[^]
- BEGIN DISTRIBUTED TRANSACTION[^]

The need to optimize rises from a bad design.My articles[^]

Aman786Singh16-Jan-09 20:26

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

16-Jan-09 20:26

i am trying to runn this

BEGIN DISTRIBUTED TRANSACTION;
-- Delete candidate from local instance.
Insert into UserTest values(1211,'AMan',1,Getdate());

-- Delete candidate from remote instance.

Insert into [Replicabackup\replicadb].test.dbo.Userlist(UserID,Name,Status,CreatedDate,TriggeredDate,EventType)
values(1211,'AMan',1,Getdate(),Getdate(),'Inserted');

COMMIT TRANSACTION;
GO

but i got this error

Server: Msg 7391, Level 16, State 1, Line 8
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. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Kindly Help.

Wendelius16-Jan-09 22:08

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

16-Jan-09 22:08

Check that the MSDTC service is running on the server where you start the transaction.

Also it's possible that your linked server connection doesn't support distributed transactions. You could check that remote proc trans is 1 using sp_configure.

The need to optimize rises from a bad design.My articles[^]

Aman786Singh17-Jan-09 7:18

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

17-Jan-09 7:18

my source server has sql 2000 and my destination server has sql 2005..

sql 2000 MSDTC services are already running... and i have also checked all the options in sql 2005 surface configuration manager..

i ran sp_configure command on my both servers. the values are :- 1 ..
now tell me wht shd i do...

Wendelius17-Jan-09 7:36

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

17-Jan-09 7:36

Aman786Singh wrote:
i ran sp_configure command on my both servers. the values are - 1

-1

Possible values are only 0 and 1. You could also check this using Management Studio and the server properties dialog. I don't remember what the actual text was in connection definitions, but there should be a few check boxes you can set on like: are remote connections allowed and are distributed transactions required.

Another thing you could try is that before you start the transaction in your code you can call:

SET REMOTE_PROC_TRANSACTIONS ON

Also check this link: http://support.microsoft.com/kb/839279[^].

Then there's a small utility dtcping.exe, which you can use to determine if firewall is causing a problem. Downloadable here:http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en[^]

The need to optimize rises from a bad design.My articles[^]

Aman786Singh17-Jan-09 7:51

Re: I want that whenever i perform any insertion,updation or deletion on a table,... same operation would be performed in another table located at different server .

17-Jan-09 7:51

i mean to say value is 1 not -1... ok i will try your steps and let u know wht happen.. thanks in advance... Smile | :)

Wendelius17-Jan-09 7:53

CASE / LIKE code will not compile.

17-Jan-09 7:53

No problem. I found that there are severa problems for SQL Server 2000 on Windows 2003 server concerning DTC. Lots of hotfixes available so you might want to check those also.

The need to optimize rises from a bad design.My articles[^]

dptalt16-Jan-09 7:00

Re: CASE / LIKE code will not compile.

16-Jan-09 7:00

The following code is used in the where clause in a stored procedure and it will not compile.

CASE WHEN @ciSubjectId <> '' THEN tJ.cdSubjectCode LIKE '%' + @ciSubjectId + '%' ELSE tJ.cdSubjectCode LIKE tJ.cdSubjectCode END

How can I get this to compile?

Wendelius16-Jan-09 8:24

Re: CASE / LIKE code will not compile.

16-Jan-09 8:24

You cannot use CASE to modify the logic in where clause. Instead you must redefine the logic. If I understood you correctly, you use @ciSubjectId if it's not empty , otherwise you use an exact match which is always true (unless the value inside cdSubjectCode is NULL. If that was correct, your statement could be something like:

... tJ.cdSubjectCode LIKE (CASE @ciSubjectId
                              WHEN '' THEN tJ.cdSubjectCode 
                              ELSE '%' + @ciSubjectId + '%'
                           END)...

The need to optimize rises from a bad design.My articles[^]

dptalt19-Jan-09 3:15

Re: CASE / LIKE code will not compile.

19-Jan-09 3:15

The change you suggested compiles but always returns 0 rows.

Wendelius19-Jan-09 6:50

19-Jan-09 6:50

Could it be because of other conditions or the value in the variable. I made a simple test and it seems to work fine. You can try this on your database and change the value of @ciSubjectId to whatever you like:

declare @ciSubjectId varchar(50);
SET @ciSubjectId = 'table';
SELECT *
FROM sysobjects
WHERE
     --tJ.cdSubjectCode 
     name LIKE (CASE @ciSubjectId                              
                WHEN '' THEN name
                ELSE '%' + @ciSubjectId + '%'
                END)

The need to optimize rises from a bad design.My articles[^]

TSQL Case error

dptalt16-Jan-09 6:56

16-Jan-09 6:56

The following code is used in the where clause in a stored procedure and will error when @ciDept = '4,25'. It works when @cilocn = '4'

tLD.idClientDeptNumb IN (CASE WHEN @ciDept <> '' THEN CONVERT(INT, @ciDept) ELSE tLD.idClientDeptNumb END

The error thrown is:
Syntax error converting the varchar value '4,25' to a column of data type int.

How can this error be resolved?

Re: TSQL Case error

Wendelius16-Jan-09 8:08

16-Jan-09 8:08

dptalt wrote:
Syntax error converting the varchar value '4,25' to a column of data type int

decimal separator in SQL is dot. If you're unable to modify it on the calling side, you can use replace function to modify it in the stored procedure or in the statement.

Also integer cannot handle decimals so you should use for example real as datatype:

tLD.idClientDeptNumb IN (
   CASE 
     WHEN @ciDept <> '' 
         THEN CONVERT(REAL, REPLACE(@ciDept, ',', '.')) 
      ELSE tLD.idClientDeptNumb 
   END...

But if you can, prefer using numeric variables if they contain numeric data.

The need to optimize rises from a bad design.My articles[^]

Re: TSQL Case error

dptalt19-Jan-09 3:14

19-Jan-09 3:14

Replacing ',' to '.' always returns 0 rows.

I thought the IN list command requires the elements in the list to be separated by commas?

If I do not replace the commas with decimals I get the following error:

Error converting data type varchar to real.

Re: TSQL Case error

Wendelius19-Jan-09 6:51