|
Hai All,
i am new to sql server2000,
i just want to know about the Querry
to display all the tables from one database(ex:Master)
Thanks in Advance
Thanks
Subbu.
|
|
|
|
|
seelct * from systables
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hai ,
Thanks to u for immediate response,
but i want the querry in sqlserver2000 not in oracle.
so pls send the querry.
Thanks
Subbu.
|
|
|
|
|
sp_tables
which returns all tables in the database including system tables
select * from sysobjects where xtype='U'
which is also returns tables
|
|
|
|
|
select * from sysobjects where xtype='u'
regards
venkataramu c
venkat
|
|
|
|
|
Hi All
i have a table with data to send it to the other Department. and when the bring back this table, it will come back with more additional Fields.the others Fields that were present in that table, i dont want them to be tempered with. So i want a way to validate or check if there was anything change from the table. i want to check based on two Fields. am using SQL2000. i need something like a Match. to match two tables.
Table 1 Table 2
============ =========
Num_key Num_key
Lis_key Lis_key
i want to check if the value i have sent to the other Department, did not change.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
I'm not sure what you are trying to do. Do you want to find the records in Table 2 that are not present in Table 1? If so, use the following query:
SELECT Num_key, Lis_key
FROM [Table 2]
WHERE NOT EXISTS
(SELECT TOP 1 *
FROM [Table 1]
WHERE Num_key = [Table 2].Num_key
AND Lis_key = [Table 2].Lis_key)
Paul
Paul Marfleet
|
|
|
|
|
HI Paul
Thanks for the Reply
what am trying to achieve is the Following.
i have Table 1, that has records, and i want to send it to the other Department. after sending it, i will keep the copy of table1. when a Copy of Table1 has return from the Department i have sent too, it will have more additional Fields with it. i will now call it "Table2", now remember,that the same fields are still in Table 2 but with more addition. Now i want to Compare the Fields in Table1 And Table 2, only on the records i have sent to the Department, if they have changed in a Field ="Lis_key" and "Attrib_code".
i hope i have Explained it well this time
Thanks again
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
Is there a primary key on this table?
|
|
|
|
|
Yes there is a Primary key,
When i sent the table to the Other Department the Primary key was a field ("Property_ID") , and now because there are some records that i did not send to the department that will be present when table 2 returns. so i deciced to accept table2 and add a primary key to it as (Primary_ID). that means Table1 Primary key will be (Property_ID) and table2 will be Primary_ID. and the Field Property_ID will still be present in table2 but it will not be a primary key anymore.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
Hi Vuyiswa
If you use SQL-Server then should be able to try something like:
select IsNull(a.Lis_key, b.Lis_key) as Lis_key,
IsNull(a.Attrib_code, b.Attrib_code) as Attrib_code,
case
when a.Lis_key is null then 'New entry in Table2'
when b.Lis_key is null then 'New entry in Table1'
else 'Identical'
end as MatchComment
from Table1 as a
full outer join Table2 as b
on a.Lis_key = b.Lis_key
and a.Attrib_code = b.Attrib_codeYou can use the same technique with many other types of database, but the IsNull function and Case expression may need to change.
I'm not sure that I fully understood your requirement - I didn't find it very clear what you want to do with the additional fields on Table2. Hopefully you can use my solution as a starting point.
Regards
Andy
|
|
|
|
|
Thanks it will help me a lot,Please one more question about your above statement. tell me if am wrong
1) The query will Display all records and if they are not identical in the field "lis_key", of the table 1 it will tell display the Field
'New entry in Table1'
else
'Identical' ?
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
