|
If a record exists in Table1, but not in Table2 then it will print 'New entry in Table1'. This would be the case for new Table1 records, or where the other department has changed the Table2 "lis_key" or "attrb_code" values.
If a record exists in Table2, but not in Table1 then it will print 'New entry in Table2'. This would happen where the other department has changed the Table2 "lis_key" or "attrb_code" values.
All of the records that have the same "lis_key" and "attrb_code" will be displayed with "Identical" next to them.
If the "lis_key" is a primary key on Table1 then you can remove "attrb_code" from the join clause - then add when a.attrb_code <> b.attrb_code then 'Attrb_Code has been updated by other department' to the case expression (before the "else" bit). This will allow you to detect then the other department have modified that just field.
|
|
|
|
|
hi
can we delete duplicate rows from a table(there is no primary key) without copying them to another table
|
|
|
|
|
Here is a link to an article that explains deleting Duplicate Row's from a Table using Row_Number() function. If you are using sql 2005 then you can refer this one...
Delete Duplicate Rows[^]
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
Thanks John
But I am using SQL Server 2000 in which Row_Number() function is not available.
|
|
|
|
|
Guys! Anyone help me out! its pain in ass since yesterday
trying to create report using Stored proc, but when i select the report, it says
SP or function has toomany arguments specified" what the hell that mean???
Thanks in advance
|
|
|
|
|
You have passed more parameters to the stored procedure than required!
gauthee
|
|
|
|
|
Thank you very much Gauthee, 
|
|
|
|
|
Hi everyone,
I have a relatively simple stored procedure on a MS-SQL server 2005. It runs fine if I execute it directly, but ends up timing out after the default 30 seconds from C#. I am running various other sprocs from my code in exactly the same way with no problems.
Can anyone think off the top of their head why this may be happening? I will post the code later as this will obviously help...
The only special thing about this is it has a 'nested select' ie:
SELECT * FROM table1 t1
WHERE t1.ColumnA > (SELECT ColumnC FROM table2 WHERE ColumnD = t1.ColumnB)
or seomthing to that effect (I dont expect the above code to parse)
Thanks in advance.
|
|
|
|
|
here is the code for this stored procedure. Can anyone see anything wrong with it?
select exchange_id, trade_date, trade_time,
short_name, code_buy,
capacity_buy, code_sell,
capacity_sell, trade_price,
trade_size, trade_type,
cancelled, quarter_percent_true
from trades t, reports r
where trade_price >
(select 1.02 * AVG(trade_price)
from trades
where isin = t.isin and trade_date = @trade_date
group by isin)
and trade_date = @trade_date
and t.isin = r.ti_code
and r.case_owner = @case_owner
and r.run_rylat = 'YES'
and t.cancelled = 'N'
order by short_name, trade_price DESC
|
|
|
|
|
Hi All:
I was trying to Used Distributed Transaction with Thread to allow many
Inserting operations into Different Databases in Different Server at the same
time
My Question is can we applying the Distributed Transaction and Threading
Together.
I try to apply it But the folowing Problem appeares through Open the first
Database Connection
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in mscorlib.dll
Additional information: The component is configured to use synchronization
and this method call would cause a deadlock to occur.
' Some oF My The Code
Private strDBQA As String = "Data Source=QASRV; Initial Catalog=employee;
Password=sasa; User ID=sa"
Private strDBFA As String = "Data Source=FADI; Initial Catalog=Employee;
Password=sasa; User ID=sa" 'Enlist =false"
Private _Itransaction As System.EnterpriseServices.ITransaction
Private _boolQ As Boolean = False
Private _boolF As Boolean = False
Public ReadOnly Property PITransaction()
Get
Return _Itransaction
End Get
End Property
'The Main Function
Function TestThread(ByRef strError As String)
Try
_Itransaction = System.EnterpriseServices.ContextUtil.Transaction
Dim objThread As System.Threading.Thread
objThread = New System.Threading.Thread(AddressOf QServer)
objThread.Start()
objThread = New System.Threading.Thread(AddressOf Fserver)
objThread.Start()
If _boolF = True AndAlso _boolQ = True Then
ContextUtil.SetComplete()
strError = "Completed"
End If
Catch ex As Exception
ContextUtil.SetAbort()
strError = ex.Message
Catch ex As Exception
End Try
Private Sub QServer()
Try
Dim oConnectionQ As New SqlClient.SqlConnection
Dim cmdSql As New SqlClient.SqlCommand
oConnectionQ .ConnectionString = strDBQA
oConnectionQ .Open()
oConnectionQ .EnlistDistributedTransaction(PITransaction)
cmdSql.Connection = oConnectionQ
cmdSql.CommandText = "update Test set ID = ID + 10"
cmdSql.ExecuteNonQuery()
_boolQ = True
If _boolF = True AndAlso _boolQ = True Then
ContextUtil.SetComplete()
End If
Catch ex As Exception
ContextUtil.SetAbort()
End Try
End Sub
Private Sub Fserver()
Try
Dim oConnectionF As New SqlClient.SqlConnection
Dim cmdSql2 As New SqlClient.SqlCommand
oConnectionF .ConnectionString = strDBFA
'================
' in the strF oPen The Error Appear
oConnectionF .Open()
oConnectionF .EnlistDistributedTransaction(PITransaction)
cmdSql2.Connection = oConnectionF
cmdSql2.CommandText = "update Test set ID = ID + 10"
cmdSql2.ExecuteNonQuery()
_boolF = True
If _boolF = True AndAlso _boolQ = True Then
ContextUtil.SetComplete()
End If
Catch ex As Exception
ContextUtil.SetAbort()
End Try
End Sub
F.mathkour
|
|
|
|
|
Do not cross-post!
__________________________
Don't drink and derive.
Alcohol and calculus don't mix.
|
|
|
|
|
Hi, how can i autoincrement my primary key type id in mssql 2000 server?
To do this what should i need to set in enterprise manager of 2000 server. Thanks for advance help.
|
|
|
|
|
Make it an identity column. To do this, select the Identity property on the column and set it to true. The identity seed will default to 1 and the increment defaults to 1.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Fine and great. Thanks a lot.
|
|
|
|
|
My pleasure.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Select Statment:
Select * From Table
where fieldId = ?parameter(fieldId)
I keep trying to use @fieldid but I get a token unknown error.
I am using Microsoft Reporting Services and I have a datasource/data connection(ODBC) to a Firebird Database. I am unable to parse the Input parameter before the statement is sent to Firebird.
I can use Select * from Table
and I can populate output but when I declare an (@) input parameter in the query string (Text Command Type) I receive the Token uknown error (aforementioned problem)
Also, I tried to use Firebird : for parameter declaration but that didn't work because Microsoft Reporting Services doesn't recognize that.
Any Suggestions or solutions?
-- modified at 10:57 Tuesday 25th September, 2007
|
|
|
|
|
With Access?
Name the parameter @fieldId and use
Select * From Table where fieldid = @fieldid
|
|
|
|
|
I'm not using Access. I modified my problem and I listed more specifics.
|
|
|
|
|
Never used Firebird... but being in Phoenix, maybe I should? 
|
|
|
|
|
This was a company decision. I would much rather use Microsoft SQL 2005. I have searched the web with a comb and I can't find anything that relates to this type of problem. I was hoping someone has experienced this problem. It's such a simple but complex issue.
|
|
|
|
|
PIEBALDconsult wrote: Firebird...
It's okay. I've used it on a few projects in the past.
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
I thought that "@param" named-parameters were only supported by the SQL-Server provider. Have you tried using a "?" positional-parameters in your command text?
|
|
|
|
|
Other databases do too, even Access/Jet.
|
|
|
|
|
Thanks so much. The ? worked.
|
|
|
|
|
I got select first_name from employee where emp_no=@Param0 to work as expected using the Firebird .net connector. (ODBC is so '80s.)
Thanks for giving me the opportunity add support for another database to my library.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
