I've been able to determine that our Store Ops Item database has 31 items that are INACTIVE when compared to our HQ database (I ran a SELECT COUNT(*) FROM ITEM WHERE INACTIVE = 1). However, I've been unable to determine WHICH
I'd like to run a QUERY that will show me the items in our HQ Item database that are different from the items in our Store Ops Item database. HQ is on server1 and Store Ops is on server2.
Can someone familiar with SQL tell me the query?
Scott
Hi Luminox, You can run the following query to get the result set (can be run any server) but make sure that you have to create the relationship with the HQ if sitting and running the command on store, you can do the reverse also. first in query analyzer do this
EXEC sp_addlinkedserver 'server1', N'SQL Server' (running at store) OR EXEC sp_addlinkedserver 'server2', N'SQL Server' (running at HQ)
Now run this command for test select * from server1.rmshqsample.dbo.item (at store) OR select * from server2.rmssample.dbo.item (at store)
Now run this command to match the items in HQ not in Store. select * from server1.rmshqsample.dbo.item as HQItm where not exists( select itemlookupcode from server2.rmssample.dbo.item as STitm where HQitm.itemlookupcode=STItm.Itemlookupcode) To See Matching Item select * from server1.rmshqsample.dbo.item as HQItm where exists( select itemlookupcode from server2.rmssample.dbo.item as STitm where HQitm.itemlookupcode=STItm.Itemlookupcode)
Appreicate if you rate please.
"Lum> I've been able to determine that our Store Ops Item database has 31 items
Have something to add? Share your thoughts — no account required.
Ask the community — no account required