December 01, 2020, 09:19:24 pm
News: If someone gives you crap then give them a Bad Star
Pages: [1]   Go Down
Author Topic: Tracing SQL on a SQL Server 2005 Express database via ODBC if possible.  (Read 4211 times)
Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« on: October 22, 2007, 01:38:01 pm »

Does anyone know how I might go about this?

The problem is Current Software doesn't play too nicely with SQL Server 2005 when dealing with Unicode and character page 1256 (arabic). The problem I'm seeing is that when the software writes arabic data to database it pads X number of white spaces to the arabic that fills the field. However, it shouldn't do this and I can't tell if it is the application or the database server that is doing this.

On other database systems this functionality works OK, and we believe that this is a problem that the software was written in VC++ 6 and we're working with SQL Server 2005 (which operates on .NET).

The application connects to the database via a ODBC System DSN. What I want to do is have a third application that listens in on the connection to read the SQL that is sent from the program. I'm aware that with the full SQL Server 2005 this is already possible in the Management Studio but we aren't running this on a full version of the database so I need to do a work around of sorts.

Any help would be ... helpful Grin
Logged

-- signature --

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #1 on: October 22, 2007, 11:06:07 pm »

Are the database fields defined as a NCHAR rather than NVARCHAR?
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #2 on: October 23, 2007, 05:55:15 am »

Nope they are NVARCHAR. Originally they were set as Varchar and the system worked fine but when I had to change the collation for use of Arabic I had to change the VARCHAR to NVARCHAR to allow for Unicode.

EDIT

Have tried it with NCHAR and the same thing happens. I need to see if the padding is being added in the SQL from the software. If so then I know where the problem lies else it is in the database and I need to find the settings to stop this from happening.
« Last Edit: October 23, 2007, 05:59:27 am by Jason Reed » Logged

-- signature --

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #3 on: October 23, 2007, 07:31:17 am »

Ha!

Got the problem solved I think. Change the Database collation and all varchar columns' collation to ARABIC_CI_AS and then the need for nvarchar goes away and therefore no automatic padding. Found the link in MSDN that says the ANSI_PADDING is always on for nvarchar and nchar (which is really annoying). So I tried it with the arabic collation and the varchar and the software worked right off the bat.

So to ensure that I'm on the right path I dropped the database and re-installed it with the ARABIC_CI_AS and then will try it with the software. If no errors then I know that I've got it solved.
Logged

-- signature --

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #4 on: October 23, 2007, 11:32:54 am »

Actually NVARCHAR shouldn't have the ANSI PADDING on so something to do with the collation.

Glad you figured it out.
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #5 on: October 23, 2007, 12:49:05 pm »

Read it from the Microsoft Source : http://msdn2.microsoft.com/en-us/library/ms186939.aspx

Last line under Remarks
Quote
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.

Therefore when I was sending data to the database where the data type was nvarchar it was automatically padding out the data.
Posted on: October 23, 2007, 02:48:01 pm
Actually not all is perfect. For some reason the software still says that Arabic is lowercase letters (when importing from file) which it doesn't do in other installations. I'm not sure why though hmm
Logged

-- signature --

Pages: [1]   Go Up
Print
 
Jump to: