Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts and where clauses.

So what we need is a way to translate a query like this:

SELECT Whatever FROM Person WHERE Name = 'John';

to this:

SELECT Whatever FROM Person WHERE Name = N'John';

Is there a way to implicitly force this on SQL server (2005 or higher, I guess if an upgrade is needed it would be acceptable).

Otherwise, does anyone know of a 3rd party proxy that would be able to translate the queries? Building one would be costly, but I think it is feasible. Or could we use some other trick like a trigger or something?

I have already checked if the 3rd party application can handle multi-byte characters in the strings that are sent and returned, that is not a problem at all. The only problem is that it's formatting the queries without using N's. (It should have been using parameters but we can't change it anyway).

share|improve this question
1  
A string literal without the N prefix is always treated as varchar. There is no way of changing this. There is no such thing as a SELECT trigger or any built in way of amending queries sent by the client on the fly. –  Martin Smith Sep 7 '13 at 9:36
    
@Martin Smith, Ok so then a proxy is the only way to go in that case. I think it would be quite simple using a TCP connection to change incoming requests. Maybe only a single regex is needed. The response can be passed on as-is. –  Louis Somers Sep 7 '13 at 9:41
    
Interesting idea. You'd need something that understood (at least partially) the TDS protocol though. I have no idea how possible this would be. –  Martin Smith Sep 7 '13 at 9:48
    
What is the actual problem you're trying to solve here? If it's to enable this application to use multi-byte characters, you may be in for a rude awakening if there's any programmatic code that relies on there only being one byte per character. I don't believe for a second that comprehensive testing of this has been done to see if it's fully supported. If you're willing to spend $ on this project, does the latest version of the software support what you want to do? I would much rather spend $ on that than some kind of unsupported hack solution which may or may not work. –  Jon Seigel Sep 7 '13 at 20:20
    
@Jon Seigel, The 3rd party app runs on the .Net platform where strings are multibyte by default (we also see a few queries with parameters work as expected). The 3rd party do not officially support multibyte character sets, but Sales don't care and they sold a solution to a large customer promising multibyte character support... so, we have to hack it in somehow with a short deadline. I guess most readers are experiencing a déjà vu right now? We can change the database fields (not supported) but not decompile and recompile the code with changes (that's illegal) :-) –  Louis Somers Sep 7 '13 at 20:47
add comment

1 Answer

I guess the implicit conversion causes index scan, if that is the case, You can use plan guides and force index seek without changing the source code.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.