November 14, 2007 at 3:25 pm
Hi all,
My application works with both SQL Server as well as Oracle.For SQL Server 2000 to create primary key in to one table i am using UuidCreate() function which returns unique key of length 32.But in case of SQL Server 2000, it some times accepts junk charecters. For Oracle database with this function i am not gettng any error,But SQL Server sometime accepts junk charecter.Can you please suggest me the root cause of it and solution for it.
Thanks in advance,
Swati
November 28, 2007 at 2:13 am
First of all why would you want a primary key with a length or 32. Seems extremely unefficient to me.
Second could you post ure DDL. Now I can only gues hoe your PK is filled.
Do you have a (n)varchar field with a Default ? In that case the default will not be used if the user supplies a value.
What do you mean with "junk character"?
Markus
[font="Verdana"]Markus Bohse[/font]
November 28, 2007 at 10:05 am
Hi Markus,
Thanks you so much for replying to this query.As per our product design as a primary key we need to have 32 charecters.As far as the junk charecters are concerned we have seen junk charecter whick looks link boxes.Unfortunetly i don't have the screen shot now.But this has happened only for SQL Server in multiple times.We have never faced this issue in oracle.even though we use the same mechanism to insert the value in to both of the database.Can you give me an idea in what all conditions SQL Server can accept junk charecters? We are only inserting the return value of UuidCreate() function , which produces a 32 charecters unique key.
The query used for inseting the value into the table is
INSERT INTO mess_type(MessageKey,Type,TimeDone,TimeStored,
PksOwner_id,Owner,ReadStatus,StatusFlags,LastUpdateDate)
VALUES(?,?,?,?,?,?,?,?,CURRENT TIMESTAMP)
mess_type is the table and Messagekey is the PK here.
i have attached the screenshot of the design of the table.
Thanks for the help.
Swati
November 28, 2007 at 12:20 pm
Swati,
the only explanation I can think of is a collation issue. Maybe the database (or table or column) collation you use in SQL server is different from the Orace collation.
[font="Verdana"]Markus Bohse[/font]
November 28, 2007 at 12:25 pm
Hi Markus,
Is there anyway to make sure that database do not accept those junk charecters.
Thanks,
Swati
November 28, 2007 at 12:37 pm
When you say junk characters exactly what do you mean?
As for design NVARCHAR whill accept any valid character unless you have constrained to a limited list.
Are you trying to create a GUID style value? If so then consider making the column a uniqueidentifier column instead. Or create a constraint for the NVARCHAR column to work with.
November 28, 2007 at 12:43 pm
The junk characters are the extended ascii characters which are not printable and will often be shown in editors as a box or similar. Things like the ascii null character ( char(0) ) or the ascii bell character ( char(7) ) will show up as boxes in many text editors.
The big question is if you genuinely need a universally unique identifier that will never be duplicated anywhere by any system. If you absolutely need that gauruntee, then you may need to accept this extended, non-printing characters. If you do not, you may be better off by creating a function that generates a key for you that you know will be unique within your system which is enough for most applications. Depending on your needs, this can be as simple as starting at one and incrementing, or making part of it meaningful and then incrementing, or even making the entire string meaningful in a way that identifies that row. If you need to make it an arbitrary length, you can pad the extra locations with 0's.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 28, 2007 at 3:27 pm
hi,
For creating the unique key we are using the function UuidCreate() which always generated the unique charecter. We are using the same mechanism for both SQL Server and oracle.i am not sure if this fucntion ever generates the junk charecter during any situation.But since it has never occured for oracle so I am assuming that the fucntion do not return junk values.So my doubt is in which conditions SQL Server can accept junk charecters(this junk charecters look like small boxes as seen in the query analyzer).
November 28, 2007 at 3:50 pm
the char, varchar, nchar, nvarchar types will take any number of extended characters. It doesn't care that it can't display them.
Are you sure that Oracle isn't accepting them, and just not showing you a placeholder for the non-printing characters? It's more of a design choice as to whether to put a placeholder where something with no display component might be.
If you really need to remove these - you might consider stripping out the unacceptable characters before you submit it into SQL server, using regular expressions for example. You could also "clean them up" on SQL server if you implement the regex functions in the DBA toolkit on here (it uses extended stored procs on SQL Server 2000). looking for characters matching this:
'[^\S ]'
should yield most or all non-printing characters you want to get rid of.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2007 at 10:25 pm
Here is the DDL statement of the table
CREATE TABLE mess_type (
MessageKey NVARCHAR(32) NOT NULL,
Type INT NOT NULL,
TimeDone INT NOT NULL,
TimeStored DATETIME NULL,
PksOwner_id INT NOT NULL,
Owner NVARCHAR(32) NOT NULL,
ReadStatus INT NOT NULL,
StatusFlags INT NULL,
Prev_task_id_grhigh INT NULL,
Prev_task_id_grlow INT NULL,
Prev_task_id_seqno INT NULL,
LastUpdateDate DATETIME NULL
CONSTRAINT PK_type PRIMARY KEY CLUSTERED (MessageKey)
)
The primary key is MessageKey here, which is taking the junk charecters.
November 29, 2007 at 2:57 am
alien,
The data that is being stored in SQL Server is the valid result of the UuidCreate() function. They are not junk characters.
In order to generate a globally unique value, the function will always return a data string containing information that cannot be displayed in character format.
If you store the result of UuidCreate in a GUID column, the SQL Server query tools will show the identifier in hex format and all characters will be human-readable. If you feel you need to keep SQL Server column definitions in line with Oracle, then the data in a char() column will not display correctly, but it will still be valid.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 29, 2007 at 7:21 am
EdVassie (11/29/2007)
alien,The data that is being stored in SQL Server is the valid result of the UuidCreate() function. They are not junk characters.
In order to generate a globally unique value, the function will always return a data string containing information that cannot be displayed in character format.
If you store the result of UuidCreate in a GUID column, the SQL Server query tools will show the identifier in hex format and all characters will be human-readable. If you feel you need to keep SQL Server column definitions in line with Oracle, then the data in a char() column will not display correctly, but it will still be valid.
I think you hit the nail on the head. If your submission to the NVARCHAR is a hex value you will end up with non-readable data, but if you convert say to varbinary you may find the guid as you expect. This more than likely is based on how you make the relationship of the field in the application code as far as submission that may be causing a translation of the hex to character value instead.
November 29, 2007 at 9:36 pm
alien (11/28/2007)
hi,For creating the unique key we are using the function UuidCreate() which always generated the unique charecter. We are using the same mechanism for both SQL Server and oracle.i am not sure if this fucntion ever generates the junk charecter during any situation.But since it has never occured for oracle so I am assuming that the fucntion do not return junk values.So my doubt is in which conditions SQL Server can accept junk charecters(this junk charecters look like small boxes as seen in the query analyzer).
SQL Server will readily accept the extended characters and this can cause problems when the information is being passed to other programs. If you do not absolutely need something guaranteed to be unique throughout the world, then I would find a different way of generating it. Depending on your circumstances you may be able to simply increment with 0's padding any other characters you need.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply