Database

Re: Creating relation between two tables in VS2008 & SQL Express ?

18-Jan-09 7:25

Most likely you have a mismatch in the code behind of xsd itself. Click DateID column in your dataset designer and check the DataType property from properties window.

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

Mohammad Dayyan18-Jan-09 8:45

Mohammad Dayyan

18-Jan-09 8:45

Wow, thanks Smile | :)

Re: Creating relation between two tables in VS2008 & SQL Express ?

Wendelius18-Jan-09 8:57

18-Jan-09 8:57

You're welcome Smile | :)

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

trigger problem

monuSaini16-Jan-09 20:05

monuSaini

16-Jan-09 20:05

Hello Friends,

I have create an trigger on an table insert and update event :

CREATE TRIGGER sp_Kp1 ON [dbo].[OEORDD]
for insert, update

AS
DECLARE @x DECIMAL(18,2);
DECLARE @y varchar(10);
DECLARE @z DECIMAL(18,2);

SELECT @x = xx FROM inserted;
SELECT @y = yy FROM inserted;
SELECT @z = zz FROM table1 where xx= @x and yy = @y;

INSERT INTO table2 (tx,ty,tz) VALUES @x,@y,@z);

This trigger is working fine only if one row is updated
but it fails if i update more than one row using update query.

Thanks n regards,

rahul saini

Re: trigger problem

Wendelius16-Jan-09 21:52

Re: Primary key violation

16-Jan-09 21:52

Can you simply use one insert into...select from query:

CREATE TRIGGER sp_Kp1 ON [dbo].[OEORDD] 
for insert, update
AS
BEGIN
  INSERT INTO table2 (tx,ty,tz) VALUES @x,@y,@z)
  SELECT i.xx, 
         i.ii, 
         t.zz
  FROM   inserted i,
         table1   t
  WHERE  t.xx = i.xx
  AND    t.yy = i.yy
END;

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

Re: trigger problem

N a v a n e e t h18-Jan-09 21:44

N a v a n e e t h

18-Jan-09 21:44

monuSaini wrote:
This trigger is working fine only if one row is updated
but it fails if i update more than one row using update query.

Expanding Milka's answer,

SQL server don't have row level trigger like oracle. SQL server trigger will be executed only after the whole statement is completed. So when multiple rows are updated, the trigger will be executed only after all the updations are complete. You can use the INSERTED or UPDATED virtual tables to get the data affected.

Navaneeth

How to use google | Ask smart questions

Primary key violation

CodingYoshi16-Jan-09 18:35

CodingYoshi

16-Jan-09 18:35

I am using this query to see if any key is already in the table and returns 0 rows.

select * from dbo.Table1
where AccountCode IN(SELECT
[AccountCode] FROM #TempTable)

But when I do this it fails due to violation of primary key constraint:
INSERT INTO
dbo].[Table1]
(
[col1]
, [col2]
, [AccountCode]
, [col4]
, [col5]
, [colDate]
)
SELECT
[col1]
, AccountCode
, '12345'
, '12345'
, 100
, '01/16/2009'
FROM #TempTable

Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it.

Aman Bhullar16-Jan-09 19:39

Aman Bhullar

16-Jan-09 19:39

CodingYoshi wrote:
Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it.

you cannot use above query till there is identity column in destination table.
Remove identity for a while, execute your query and then put identity on column

Re: Primary key violation [modified]

Wendelius16-Jan-09 22:12

16-Jan-09 22:12

CodingYoshi wrote:
Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it

So you should remove the identity column from the insert statement since it's autogenerated. You cannot define value to it

INSERT INTO 
dbo].[Table1]
(
   [col1]
   , [col2]
   , [AccountCode]
   , [col4]
   , [col5]
   , [colDate]
)
SELECT 
   [col1]
   , AccountCode
   , '12345' 
   , '12345'
   , 100
   , '01/16/2009'
FROM #TempTable

If you must insert a value to it, you can temporarily disable it:

SET IDENTITY_INSERT [Table1] ON
INSERT...
SET IDENTITY_INSERT [Table1] OFF

But if you insert new values to the identity column, you may end up in a situation where the next insert generates a duplicate identity value.

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

modified on Saturday, January 17, 2009 12:01 PM

Division by zero error

Unsy16-Jan-09 9:27

Unsy

16-Jan-09 9:27

Hi All

I'm a newbie to SQL coding, and I have a problem which I'm hoping you can help with regarding the Division by zero error.

I have a calculated field in a sql statement where the results from 1 case statements is divided by another. Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception?

Thank you in advance.

Wendelius16-Jan-09 9:28

16-Jan-09 9:28

Unsy wrote:
Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception

Most likely, but could you post the statement. Without that it's quite impossible to say how to implement it.

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

Unsy17-Jan-09 6:39

Unsy

17-Jan-09 6:39

Hi Mika

I dont have the exact code with me at the moment. It was similar to this...

SELECT 
    Field1
   ,Field2
   ,SUM(
        SUM(CASE WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 END))
        /
        SUM(CASE WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 TotalPaid END))
       )
FROM
    Table1
GROUP BY 
    Field1
   ,Field2

The idea is to get a total of how many Term - 20's are over 0 which were representing a loan term. Anything less than 0 would mean it has gone over term and should not be counted.
This was to be divided by the sum of total paid to calculate performance.
Please excuse the SQL code if it is full of errors as I am 250 miles from home and have no way of testing if it is right. Sigh | :sigh:

Thanks

Wendelius17-Jan-09 7:11

17-Jan-09 7:11

Unsy wrote:
Please excuse the SQL code if it is full of errors as I am 250 miles from home

No problem.

And the problem was that the divisor is equal to zero in some cases. If it's zero, you wanted the result of the division to be zero. In that case you could for example use inline view for intermediate results and make the statement something like:

SELECT
    Field1
   ,Field2
   ,SUM( CASE Divisor
            WHEN 0 THEN 0
            ELSE Amount / Divisor
         END)
FROM (
   SELECT Field1,
          Field2,
          SUM(CASE 
                 WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 
                 THEN 0 
                 ELSE 1 
              END
          ) amount,
          SUM(CASE 
                 WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 
                 THEN 0 
                 ELSE 1 
              END
          ) divisor
   FROM
       Table1
   GROUP BY
       Field1
      ,Field2) alias
GROUP BY
    Field1
   ,Field2

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

Unsy18-Jan-09 21:37

Unsy

18-Jan-09 21:37

Thats spot on. Thank you. Big Grin | :-D

Wendelius19-Jan-09 2:02

Walkthrough: Filtering and Updating in an N-Tier Data Applicaition

19-Jan-09 2:02

You're welcome Smile | :)

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

Brian C Hart16-Jan-09 7:53

Brian C Hart

16-Jan-09 7:53

Dear Code Project General Database Forum Readers,

I would like to bring your attention to an update and extension of the Walkthrough: Creating an N-Tier Data Application[^] article on the MSDN Library. The article details how to add Save Data and Refresh features to the user interface of the walkthrough's presentation layer. It also illustrates a basic filter and how to requery a TableAdapter object from code.

I invite you to read the article if you are interested in learning more abou N-Tier Data Access.

http://www.codeproject.com/KB/database/filter_update_n-tier.aspx[^]

Have a pleasant day.

V/R
Brian C. Hart, Ph.D.

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 .

Aman786Singh16-Jan-09 7:04

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