Hi,
I've been tasked with troubleshooting a problem for a client that is having trouble with a procedure created by someone who recently passed away. Unfortunately, this person's computing knowledge is much greater than mine and there wasn't much documentation created on the procedure. But I believe I have narrowed down the issue to this:
An application has its database linked to sql server 2008. When I create a new record in this application, I can see the new record in sql server. However, If I edit any record (or delete a record) in this application, the changes are not reflected in the sql database. Conversely, if I create, edit, or delete a record in sql server, the changes aren't reflected in the external application.
Research has told me I should be looking into sql database permissions or ODBC settings but with a beginner's level of experience with both, I haven't been able to come up with a solution yet.
I'll add a record in the external application's database, refresh the db in sql server and the record appears. I'll delete the same record from the application's db, refresh sql server, but the record is still there.
if your application a web application? is there caching going on? is the application getting its data directly from sql server or is it getting it from a secondary resource sql server dumps to. in some cases people use elasticsearch, membase etc. if so how often is that being refreshed. there can be so many moving pieces to your application
The external application is for inventory control (IC). To create a new record I'll click the 'edit items' btn that opens a table showing all of their inventory, then click the 'new' button, enter in random test data into a few fields (item #, item description - enough to identify the new record) and click 'ok'.
I'll then open sql server, right click on the database holding the inventory data and click refresh. The new record is now visible.
Deleting a record is done in a similar manner as creating one: Edit items-highlight record to delete - delete btn. When db is refreshed in sql server (same method) the record that was deleted in the IC application is still visible.
@yosiasz it is not a web application. I believe it should be acting as the source for sql server, meaning the changes someone makes in the IC application should be reflected in sql server. I believe the ppl at this company using the application don't have access to the sql server.
You mean in SSMS? All the refresh does is re-read the metadata. For that matter, when you are looking at a database in SSMS, no records are visible. So I'm confused.
e.g. Before refresh (no rows in any table visible)
Yes @Kristen this is what I believe is happening and why I looked into ODBC data connection issues or a lack of specific SSMS permissions but could not find any answers.
I am wondering how the OP is editing the data in SSMS - and if he is looking in the edit view in SSMS without refreshing to see the updated data after editing/adding data in the application.
Seems likely to me that the "other" database is pushing data to MS SQL, or that MS SQL is pulling "new records", and that's it. No logic for "replicating" any Updates or Deletes.
Its also possible that the Updates and Deletes are there, but they are failing for some reason (and nothing trapping / reporting / logging the error). GBritton's suggestion of SQL Profiler would discover if those Update / Delete statements were being sent to SQL, or not.
If that is the case then that logic would need to be added / fixed
