Solved

A simple table update stopped working. Why? How to fix?

Posted on 2011-09-07
36
229 Views
Last Modified: 2013-11-05
I have the following embedded in a C++ application which was working fine on a table and suddenly I'm getting an error and no update occurs:

   UPDATE W00ABCD SET updateFlg='0' WHERE mainID LIKE '%UX123R000012%'

Apparently simple so why would it stop? How can I get more error information to find out why this has happened? Namely and most importantly, how to fix it asap?
0
Comment
Question by:RJV
  • 21
  • 15
36 Comments
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36499747
If you run your query in SQL Server Management Studio, does it work?  If not, what error do you get?  If so, are you able to trap the error in your C++ application?  Have you debugged your application by stepping through the code and setting watchpoints and breakpoints?
0
 

Author Comment

by:RJV
ID: 36500182
SQL Server Management Studio works fine. The C++ code hasn't changed at all. In fact, it picks up the error, which it had not before, that the database was not updated. As none of the code was changed the only place there might be an issue is the database itself and indeed, I did change it from collate Latin1_General_CI_AS to Latin1_General_CS_AS. I also had to change the indexed to column from char to nchar. I had to use all characters and that was not working properly.

As the table is created by code that again, has not been changed at all, but now was created from a Latin1_General_CS_AS foundation, I have a feeling this is the problem. Having said that, when creating the table the first column was always changed to Latin1_General_CS_AS, though as a char and not nchar. I manually recreated the table and changed the first column in question to nchar, but that did not solve the problem.

Could this be the issue? I want to run a test to see if it will just find the record (in code), even though I'd prefer not having to touch that code. Maybe you have some thoughts on what I just added.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36500264
The main difference is that you have changed from "CI" (Case Insensitive) to "CS" (Case Sensitive).  Have you verified that your search criteria is matching the case of the data stored in the table?  I know that you don't want to touch the C++ code, but you might have to change it so that the comparison works (e.g., LOWER(value)=LOWER(mainid).  I don't see that the change from char to nchar really affected anything, but I could be wrong.  
0
 

Author Comment

by:RJV
ID: 36502051
It should have been case sensitive and the code was set up that way. I hadn't realized that the default on installation or creation of the database was CI. I changed it to CS but that didn't affect the table and, according to the material I read, I would have to create the table again. I did that with the main table (created manually), but 'A' kept being equal to 'a' as a char. As the record ID is designed in code to use ASCII characters, I was surprised. When I changed it to nchar it worked.

Then I bumped into the problem I'm trying to solve now with the table created in code. It won't SET the selected column and there's no specific error to identify why. The C++ code is correct (really too simple not to be) as are the variables. In fact, I copied and pasted the UPDATE line directly from the variable. It should find 'UX123R000012' and set the column. The 'UX123R000012' is there and the value also is 'UX123R000012' -- and there's no 'Ux123R000012' or similar in the table, anywhere for that matter. Instead I get an error and nothing is set.
0
 

Author Comment

by:RJV
ID: 36502457
A bit more information out of further tests.

First, I successfully found the right record using "SELECT * FROM". As I can get a pointer to the record I decided next to simply update the column, through executeSQL like this:

   UPDATE W00ABCD SET updateFlg='0'

Well, I got an error and the record's updateFlg column in table W00ABCD wasn't updated.

Shouldn't that work?

I understand from the documentation that everything becomes case sensitive by making the database case sensitive. Though the code is fully case sensitive, maybe the best bet is to avoid this type of problem altogether and leave the database case insensitive and set the main table's ID column data to case sensitive, or any other column data that needs case sensitivity, and work it that way. What are your thoughts?
0
 

Author Comment

by:RJV
ID: 36505773
Reading online, some locations mentioned that if the database is case sensitive, then even the column names would be, requiring quotation marks around these names when updating them. As such I decided to leave the database as case insensitive and then, in code, set the first column to CS of the main table. Then I recreated the table in question in code, thus as CI. That code also sets the first column (i.e. mainID) to CS. After that I attempted the update but, unfortunately it does not work. Overall, not logical as essentially that's how the database was before, when it worked. Frustrating, to put it mildly.
0
 

Author Comment

by:RJV
ID: 36506762
Though it was working by now I'm questioning everything. The updateFlg column is an int. Does that have to be set differently?
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36506798
For an integer column, you wouldn't use the quotes, so it would be:

UPDATE W00ABCD SET updateFlg=0
0
 

Author Comment

by:RJV
ID: 36506822
Ah. I'll give it a try and will get back asap.
0
 

Author Comment

by:RJV
ID: 36506883
This is more than frustrating. No luck. I was all set to return with a great, problem solved! Any other ideas? Maybe try and separate the finding from the update, as I attempted before?
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36506932
Can you post the layout of the table, with field names and data types?
0
 

Author Comment

by:RJV
ID: 36507168
Copy pasted straight into here from SQL Server Management Studio:

   mainID  char(16)  Checked
   filesCnt  int  Checked
   workCount  int  Checked
   canPartFl  int  Checked
   updateFlg  int  Checked
   workByID  char(8)  Checked
   workNm  nchar(80)  Checked
   uname  char(20)  Checked
   upswd  char(20)  Checked
   wkDay  int  Checked
   wkMo  int  Checked
   wkYr  int  Checked
   wkHr  int  Checked
   flMin  int  Checked
   Specaddr  char(150)  Checked
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507209
Have you changed any of the permissions on objects in the database?  Does the C++ application user have UPDATE permissions on this stored procedure?

If you execute the following, do you get an error?  If so, what is the error?

UPDATE [W00ABCD]
SET updateFlg=0
WHERE UPPER(mainID) LIKE '%UX123R000012%'

If you don't get an error, try this and let us know the results:

UPDATE [W00ABCD]
SET updateFlg=0
WHERE mainID LIKE '%UX123R000012%'
0
 

Author Comment

by:RJV
ID: 36507260
While I try this, no permissions were changed. The application user has administrative priviledges and SQL and the database is accessed through Windows with no special logon as the application is on the same computer. Are there any other SQL security priviledges that should be looked at?
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507273
Normally, you would not want your application user to have administrative privileges.  For our purposes though, this helps since we can probably rule out a permissions problem. After we get this sorted out, you can work on the permissions :).
0
 

Author Comment

by:RJV
ID: 36507284
I already get an error on this and nothing updated:

UPDATE [W00ABCD]
SET updateFlg=0
WHERE UPPER(mainID) LIKE '%UX123R000012%'
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507315
Can you paste in the exact text of the error?
0
 

Author Comment

by:RJV
ID: 36507331
This is through ODBC through ExecuteSQL so I don't get an error; only TRUE or FALSE. That is part of the problem. How might I get error information?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 8

Accepted Solution

by:
Forefront_Data_Solutions earned 500 total points
ID: 36507369
Are you able to open SQL Server Management Studio (SSMS) and execute the query there?
0
 

Author Comment

by:RJV
ID: 36507431
I use SSMS all the time but never for a query. To speed things up (avoid researching here or trial and error), how should I execute a query there?
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507468
Just open SSMS, log into the server, click on the database, and then choose "New Query" from the menu.  You can then paste the query into the new window and either click on "Execute" or hit F5 to execute the query.   If there are any errors, it will show them in the window below the query.  If you are not able to choose the database, just add the following before your query:

USE <database_name>
GO

(substitute your database name for "<database_name>" (without the "< >"))
0
 

Author Comment

by:RJV
ID: 36507486
I worked it out and get this:

   Invalid object name 'W00ABCD'.

Naturally strange as that's the database name.
0
 

Author Comment

by:RJV
ID: 36507487
Sorry, table name.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507501
Ok, now we are getting somewhere.

While in SSMS, expand the database, then expand "Tables".  Do you see "W00ABCD"?  Verify the spelling of the table name and make sure that you are not using zero instead of "O".  Is there anything preceding the table name, such as "dbo"?
0
 

Author Comment

by:RJV
ID: 36507519
I went in SSMS and copy/pasted the table name and got the same outcome. And yes, it is preceded by dbo (which it always has been, so much that the table was created in code), which I replicated into the query, with the same outcome.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507554
If you go into SSMS, expand Tables, right-click on "W00ABCD", and choose "Select Top 1000 Rows", are you able to view all of the records?  If so, please copy the SQL query code here.
0
 

Author Comment

by:RJV
ID: 36507587
Yes, I can view all records though note that this is SQL 2005 on this test computer but that shouldn't change things. The query code I used is the same you suggested. Is there other query code in SSMS I can have access to?
0
 

Author Comment

by:RJV
ID: 36507698
I just discovered why SSMS didn't work. The database wasn't selected in the drop-down. After I selected the database (and not master as it was) and executed, it worked without an error. So it is working with same code you gave -- copied directly from C++ after I ran the application and stepped into the debug before and after executing the query code with an error.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36507706
In SSMS, you have access to everything about the database, tables, stored procedures, etc.  You can view, modify, execute, etc. everything if you have the proper permissions.  At this point, I don't have any other things that I can suggest.  My gut feeling is that it is something simple, such as permissions, or not specifying the schema name, or even misspelling the table name.  I hope that someone else can pick this up and help you out.  
0
 

Author Comment

by:RJV
ID: 36507744
Thanks to your recommendation I found the problem. Due to the server, we added the option of logging into the database with a name and password. If those variables were blank it should log in with a NULL. As SSMS was working, I went into the code and changed it to NULL thus eliminating the logic, and it worked. The strange part is that this is the same code when creating the table, adding to it, etc. For some odd reason it just doesn't work when updating. Maybe you know why this would be before I close this issue (though I'd like to first run some more tests to be 100% sure, unless you answer why it could happen just with update).
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36509851
It may be that the user has SELECT and INSERT permissions, but does not have UPDATE permissions.  If we put your user in the db_datawriter and db_datareader roles, they should have those permissions.  In SSMS, execute this query (change "DBName" to your database name and "UserName" to your user):

USE [DBName]
GO
EXEC sp_addrolemember N'db_datawriter', N'UserName'
GO
EXEC sp_addrolemember N'db_datareader', N'UserName'
GO
0
 

Author Comment

by:RJV
ID: 36510963
Before changing something I'd like to run this by you.

Originally the database was accessed with Windows priviledges. That meant that when connecting to it the password and user name were NULL. It also meant (as per our understanding) that Windows established the access rules. Everything worked fine on all tests, always local in the testing environment.

When ported to the server there were some access problems, eventually fixed so the Windows access worked as before. However, the connect function was changed such that if there was a password and a user name, that would prevail -- thus not Windows. Otherwise the access would be the original, with Windows controlling access.

Somehow those changes were not working, so the access was through a blank user name and a blank password. The update didn't work; all else did. When set to the original NULL, update started working again. Now the function has been fixed and we're running more tests in other areas to make 100% sure it's okay.

With the blank password/username, what you say makes sense and what happened seems to back that up. Seeing it's working, do you feel it important to make the changes nonetheless, with a Windows only access?
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36511158
It won't really harm anything to add the user into the db_datareader and db_datawriter roles and see if that solves the problem, but that is up to you whether you want to spend more time on this.  Since you have it working the way you want it using Windows access, I would just keep it that way.  If you decide that you need to restrict access to the database later, it is easy enough to do that with Windows authentication.
0
 

Author Comment

by:RJV
ID: 36511723
I'm glad to hear that leaving as is would be okay. The problem with changing is the fact that it should never have worked, as far as I'm concerned. There was no blank user, much less a blank password. Yet overall it did work, except for the update. To make these changes on an unreal user makes little sense. What does make sense is to keep this information to make sure later that all works when the access isn't under Windows but as a valid user/password.

The C++ function was expanded which won't impact its own performance, but the coding is such that it is clear what has happened, not the case before.

I'll wrap it up shortly here but overall, your help is one of the best I've had here. They should have points for that as I've had some pretty poor input in the past.
0
 
LVL 8

Expert Comment

by:Forefront_Data_Solutions
ID: 36512079
Thank you, your comments are very much appreciated!  I also appreciate your quick responses!
0
 

Author Closing Comment

by:RJV
ID: 36512293
The objective of getting help is to find the solution. Thanks to a constant help with many tips the problem was isolated and thus found, so really a combination of inputs led to the solution. Thanks to that time was saved, very important indeed. This particular suggestion greatly helped isolate and solve the problem, though all were important.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now