|
Hi,
I'm looking for some advice on a problem:
Mobile application implementing AUTD (http://msdn2.microsoft.com/en-us/library/aa446486.aspx[^]) calls a webservice. This webservice polls a SQL server (2005) every 30 seconds, for at most 3 minutes, lookikng for changes. Once the webservice has detected a change it returns true otherwise false. If true the mobile application makes another request to retrieve the changes, otherwise it issues a call to the webservice to check for changes.
The problem is that I would like a better way of checking SQL server for changes - some kind of event driven approach. I have tried using the Service Broker, based on http://www.codeproject.com/useritems/SQLNotificationRequest.asp?df=100&forumid=399750&exp=0&select=2062673[^]. However timing out the web method was impossible. I left the method running for an hour and it still responded to a change in a SQL table!!!!! The command timeout was set to 10 seconds.
Any help/suggestions would be greatly appreciated,
Mark
|
|
|
|
|
We are redoing our database. We have in an excel spreadsheet the mapping from the old to
the new: old table name, old column names, new table name, new column names. Is there a
tool out there or code that someone has put together that will create a new procedure from
the old replacing the old table names and old column names with the new table names and
new column names?
|
|
|
|
|
I suppose is a typical query but I dont know if exists a query to solve the next. Take for instance:
1.- Table items:
Code|Name
1000 Apple
1001 Orange
1002 Banana
2.- Table prices
Shop|Code|Price
1 1000 1.33
2 1000 1.44
1 1001 1.90
2 1001 2.10...
Can I show in a SQL query something LIKE:
Code Name PriceShop1 PriceShop2
1000 Apple 1.33 1.44
1001 Orange 1.90 2.10....
Thanks for any help because Im getting crazy
|
|
|
|
|
If you just have 2 shops then:
select i.Code, i.Name, p1.Price as PriceShop1, p2.Price as PriceShop2
from Items i
left outer join Prices as p1
on p1.Code = i.Code
and p1.Shop = 1
left outer join Prices as p2
on p2.Code = i.Code
and p2.Shop = 2
order by i.Code This links to the Prices table twice (one for shop 1 and once for shop 2). The "p1" and "p2" bits are "alias" names for these joins.
If you have more shops then you should try searching google for "crosstab queries".
Regards
Andy
|
|
|
|
|
If you are in SQL2005 you should look at the PIVOT command.
|
|
|
|
|
OK I'll take a look, thanks
|
|
|
|
|
sorry friend .i am getting exact ans, iam geting this one.
SELECT i.Item, p.shop
FROM Items i, prices p
WHERE i.code = p.code
GROUP BY i.Item, p.shop
regards venkat
|
|
|
|
|
How To export Data from a DataTable to Excel Sheet
|
|
|
|
|
|
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_code You 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
"Even eagles need a push." David McNally
|
|
|
|
|
Thanks John
But I am using SQL Server 2000 in which Row_Number() function is not available.
|
|
|
|