Hi all,
So i did learn few things about SQL, SSMS and learning new stuff every day. Got little problem which I don't understand - or i am missing something.
I am using MS POS 2009 Database - stored on SQL Server 2008. Installed SSMS and got access to MSPOS database.
What I want to do:
Change default supplier of item from supplierid 1 to 2
example
Item
itemlookupcode, description, supplierid
Pen-123, Pen black, 1
Supplier
Supplier code, Name, ID
AMA, Amazon.com, 1
Ebay, Ebay.com, 2
How I am doing it:
update Item
set supplierid=2 (tried '2')
where Item lookupcode='Pen-123'
My results:
when I query said item, supplier ID is updated to '2' all good so far
When I go to MS POS 2009 and search for item, then open it to check supplier (if changed) POS crashes.
Working on test system, Installed POS with SQL Server -> restored database in POS -> installed SSMS and accessing restored database.
Tried to check database diagrams, but empty inside (can't see relations)
Anyone able to help with this, any tips appreciated. Not sure what I do wrong 
Thanks
Marcin
in ssms, hover with the cursor over the column supplierid, you should get a popup with the datatype.
After the update (does it work?) can you see the effect of your changes in the tables via SSMS? If so and it looks good, the problem must lie in the MS POS app,
When POS crashes, do you get any specific error messages?
Hi gbritton,
When done (select * from item) and hover mouse over SuplierID only popup I get is "Click to select whole column)
When going to database (name) -> Tables -> dbo.items -> Columns -> SupplierID (int,not null)
Double click and in general Data Type is int
POS don't generate any error, crashes only on items where supplierid has to be changed.
(some items have supplierid 1, supplier with ID1 has been removed. Those items now crash in pos) I can filter all items with SupplierID1 and update supplier to 2.
Items supplier has been updated, but POS still crashes on them.
Sounds like something (either your SQL statement executed direct onto the database, outside the "safety net" of the APP or the APP itself) has created a referential integrity error. e.g An Item Record refers to a Supplier ID and now that Supplier Record no longer exists.
It is possible to define a Foreign Key relationship (i;.e between Item Table and Supplier Table) in the database and then SQL will enforce that rule. This is obviously good because it makes it impossible for a Supplier Record to be deleted if it has any associated Item Records. That means that a bug in the APP (e.g. the APP failed to check that Items Records exist before deleting a Supplier), or someone running some Adhoc SQL to delete a supplier, is safeguarded.
However, even with the best will in the world ... a developer / DBA might have overlooked creating that specific relationship, or the relationship rule might have been dropped at some point (accidental, or deliberately [e.g. during an upgrade] but then recreating it later was forgotten)
In SSMS if you look (in the left pane) under the Items table in "Keys" you may see a Foreign Key (all you will see is its name but you can RightClick and Script to Clipboard or a new Query Window to see the syntax). If you don't see a Foreign Key for the Items table you could have a look at some other tables (which you think have Parent Tables and thus should have Foreign Key relationships). My guess is that you will either generally find that all relationships have Foreign Key definitions (except the Items / Supplier one 
) ... or ... none of them do. If none of them do that does not speak well of the APP I'm afraid
You might be able to fix the problem by changing SupplierID from 1 to 2 (as you have tried) but I suspect there are numerous places where you will have to do that, and you may need to change other things too - for example, the Database might have a column (in the Supplier table, or some other associate table) for the "Number of Items for this Supplier", and if that value becomes wrong them the APP may throw an error or just do goofy things ... There might well be an Inventory Financial Value figure somewhere, and if so that is probably critical so that the accounts balance!!
My expectation is that this might be tricky to fix - if you can get help from the APP Vendor I reckon that is your best starting point.