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?

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.

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.

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.

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?

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.

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?

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 :).

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 "< >"))

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"?

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.

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?

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.

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.

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).

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):

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?

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.

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.

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.

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…