|
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
|
|
|
|
|
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.
modified on Saturday, January 17, 2009 12:01 PM
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
Thanks
|
|
|
|
|
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
|
|
|
|
|
Thats spot on. Thank you.
|
|
|
|
|
You're welcome
|
|
|
|
|
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 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
|
|
|
|
|
|
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.
|
|
|
|
|
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 .
|
|
|
|
|
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...
|
|
|
|
|
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[^]
|
|
|
|
|
i mean to say value is 1 not -1... ok i will try your steps and let u know wht happen.. thanks in advance...
|
|
|
|
|
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 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?
|
|
|
|
|
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 change you suggested compiles but always returns 0 rows.
|
|
|
|
|
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 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?
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
As in the other post from you, could it be because of other conditions. If it's not working, could you post the whole query.
|
|
|
|