SQLTeam.com | Weblogs | Forums

External sources creating records, but not editing or deleting in sql server 2008


#1

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.

Thoughts?


#2

Are you getting error messages? (post them). Is the update just not happening? (Post your update query)


#3

No error messages, and no queries.

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.


#4

Ok ,so when you "add a record in the external application's database", what do you do , exactly?

what do you mean by "refresh the db in sql server"?

How are you deleting the records?


#5

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


#6

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.


#7

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)

Just before refresh

After refresh


#8

I'm reading this differently.

The APP is storing the data in something (probably a database, but maybe not) that is not SQL Server.

Whatever does an INSERT, in that "other" database, also copies/inserts the data to SQL Server.

Any other DML in the "other" database does not replicate that change to SQL Server.


#9

You could be right, Kristen. Have to see what the OP says


#10

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.


#11

Do you know how to use SQL Server Profiler? If so, you can trace the actual SQL sent to the server when you perform your inserts and deletes.


#12

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.


#13

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


#14

I will have a look into "SQL Profiler" and see if I find out I'm having this issue.
Thanks everyone for the help.