convert dates: customer

Showing posts with label customer. Show all posts

Monday, March 19, 2012

Convert Varchar is inconsistent

Hi All,
I have a source table which contains a customer code in a CHAR(8) column.
The customer codes are only ever 5 or 6 characters long, so in the reporting
tables I am extracting data out to, I have been specifying the customer code
column as a VARCHAR(8). [I am aware that for such a small variance its
debatable whether its worth the overhead of having a VARCHAR column, but jus
t
run with me on this one].
In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
CustomerCode) but some of my tables still contain a padded 8 character
customer code, despite the datatype being VARCHAR(8) and the sp explicitly
stating convert this code to a varchar. I can verify this with the LEN and
DATALENGTH commands. All the tables are in the same database and I havent
been playing with the SET ANSI_PADDING setting either.
I'm thinking that SQL is evaluating whether its worth doing the conversion,
and sometimes it thinks its worthwhile, and other times it doesnt (maybe on
whether it comes accross a 5 or a 6 character customer first?). However, I
cant find anything about this 'machine learning' feature in BOL.
Has anyone come accross this before? and if so, what did you do to force SQL
into storing the data in the column as a VARCHAR?
TIA,
Bill PColumns of type CHAR are padded with space (See ANSI_PADDING in BOL), so whe
n
you convert to varchar you are converting also the spaces and they are not
trimmed in the convertion. You need to rtrim the converted value.
Example:
create table t (
colA char(8) not null
)
insert into t values('a')
insert into t values('bb')
insert into t values('ccc')
insert into t values('dddd')
insert into t values('eeeee')
insert into t values('gggggg')
insert into t values('hhhhhhhh')
select
convert(varchar(8), colA),
datalength(convert(varchar(8), colA)),
rtrim(convert(varchar(8), colA)),
datalength(rtrim(convert(varchar(8), colA)))
from
t
select
convert(varchar(1), space(1)),
datalength(convert(varchar(1), space(1))),
len(convert(varchar(1), space(1)))
drop table t
go
AMB
"Bill P" wrote:

> Hi All,
> I have a source table which contains a customer code in a CHAR(8) column.
> The customer codes are only ever 5 or 6 characters long, so in the reporti
ng
> tables I am extracting data out to, I have been specifying the customer co
de
> column as a VARCHAR(8). [I am aware that for such a small variance its
> debatable whether its worth the overhead of having a VARCHAR column, but j
ust
> run with me on this one].
> In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
> CustomerCode) but some of my tables still contain a padded 8 character
> customer code, despite the datatype being VARCHAR(8) and the sp explicitly
> stating convert this code to a varchar. I can verify this with the LEN an
d
> DATALENGTH commands. All the tables are in the same database and I havent
> been playing with the SET ANSI_PADDING setting either.
> I'm thinking that SQL is evaluating whether its worth doing the conversion
,
> and sometimes it thinks its worthwhile, and other times it doesnt (maybe o
n
> whether it comes accross a 5 or a 6 character customer first?). However,
I
> cant find anything about this 'machine learning' feature in BOL.
> Has anyone come accross this before? and if so, what did you do to force S
QL
> into storing the data in the column as a VARCHAR?
> TIA,
> Bill P|||On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
(snip)
> [I am aware that for such a small variance its
>debatable whether its worth the overhead of having a VARCHAR column, but ju
st
>run with me on this one].
Hi Bill,
It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
characters.
But okay - I'll run with you.

>In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
>CustomerCode) but some of my tables still contain a padded 8 character
>customer code, despite the datatype being VARCHAR(8) and the sp explicitly
>stating convert this code to a varchar.
That's correct. As CHAR(8), the data got padded with spaces. The
conversion to VARCHAR won't remove the trailing spaces.
Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
spaces and really reduce the length.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Alejandro,
Many thanks for that. Thats something that I hadnt considered, but it makes
total sense. I will ensure that I perform RTRIMs in all the SPs to ensure
consistency.
I doesnt answer why some of my tables were RTRIMing themselves and some
werent though? Thats not something I want you to answer as I am more than
happy with the solution, but its still something that I dont at this point
fully understand. If I find out, I will post it to the group.
Thanks once again,
Bill P
"Alejandro Mesa" wrote:
> Columns of type CHAR are padded with space (See ANSI_PADDING in BOL), so w
hen
> you convert to varchar you are converting also the spaces and they are not
> trimmed in the convertion. You need to rtrim the converted value.
> Example:
> create table t (
> colA char(8) not null
> )
> insert into t values('a')
> insert into t values('bb')
> insert into t values('ccc')
> insert into t values('dddd')
> insert into t values('eeeee')
> insert into t values('gggggg')
> insert into t values('hhhhhhhh')
> select
> convert(varchar(8), colA),
> datalength(convert(varchar(8), colA)),
> rtrim(convert(varchar(8), colA)),
> datalength(rtrim(convert(varchar(8), colA)))
> from
> t
> select
> convert(varchar(1), space(1)),
> datalength(convert(varchar(1), space(1))),
> len(convert(varchar(1), space(1)))
> drop table t
> go
>
> AMB
>
> "Bill P" wrote:
>|||Hi Hugo,
If only life were that simple. If I set these up as CHAR(6) or VARCHAR(6)
you can bet your bottom dollar that the some bright spark will create a new
customer with an 8 character code, simply because the ERP system lets them.
But I take your point.
Bill :-)
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
> (snip)
> Hi Bill,
> It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
> bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
> characters.
> But okay - I'll run with you.
>
> That's correct. As CHAR(8), the data got padded with spaces. The
> conversion to VARCHAR won't remove the trailing spaces.
> Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
> spaces and really reduce the length.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 07:07:04 -0800, Bill P wrote:
(snip)
> the ERP system lets them.
Ah, I see how that changes things. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Thursday, March 8, 2012

Convert SSN (varchar) to Number

Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.Is casting really necessary? Could you try:
select
max (SSN)
from
MyTable
where
SSN between '001330000' and '001379999'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.|||Take a look at this
select RIGHT('000000000' + CONVERT(VARCHAR(9),12345),9)
change 12345 to your output
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ILCSP@.NETZERO.NET wrote:
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||What does Homeland Security think about you creating 'fake SSNs'?
g,d,r
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
>
Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA
will be looking for you... :-)
Oh crap, now me too...|||What if you create a 'fake' SSN for someone and then later on someone
signs up with that SSN, then what happens? Hint: Big Headache since
you have to assign a new 'fake' SSN to the old person and changes this
probably all over the place
Better to use XXXX00001 or some other Format like FAKE00001 etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> > Hello, I'm trying to get the Max number from a range of Social Security
> > numbers in my SQL 2000 Customer table. The SSNs as stored as
> > varchar(9).
> >
> > I'm supposed to get the SSN with the 'highest' numbers from 001330000
> > to 001379999 range.
> >
> > I know that number should be 001340062, but I never got it. I've tried
> > to convert it and cast it, but so far I haven't been able to do so.
> > When I converted it, I got only 1340062 (it was missing the first 2
> > zeroes).
> >
> > We're creating fakes SSNs for people who don't have one and I'm
> > supposed to know which one is the max we've created so far and then add
> > 1 to it and so on and so on. This worked in Access, but when we
> > upgraded to SQL it didn't work anymore.
> >
> > Any help would be appreciated it.
> >
> > Thanks.
> >|||Hello all, I used Tom's way and it works great. Thanks for replying.
I think I should have not used the phrase fake SSNs, but I guess I just
wanted to get some attention.
:-)
Tom Moreau wrote:
> Is casting really necessary? Could you try:
> select
> max (SSN)
> from
> MyTable
> where
> SSN between '001330000' and '001379999'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
I would think they are already contacting
CoreComm - Voyager, Inc
East Lansing, Michigan|||You're guarenteed to be high on the DHS/NSA watch list!
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151438702.180395.194640@.u72g2000cwu.googlegroups.com...
> Hello all, I used Tom's way and it works great. Thanks for replying.
> I think I should have not used the phrase fake SSNs, but I guess I just
> wanted to get some attention.
> :-)
>
> Tom Moreau wrote:
>> Is casting really necessary? Could you try:
>> select
>> max (SSN)
>> from
>> MyTable
>> where
>> SSN between '001330000' and '001379999'
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> <ILCSP@.NETZERO.NET> wrote in message
>> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
>> Hello, I'm trying to get the Max number from a range of Social Security
>> numbers in my SQL 2000 Customer table. The SSNs as stored as
>> varchar(9).
>> I'm supposed to get the SSN with the 'highest' numbers from 001330000
>> to 001379999 range.
>> I know that number should be 001340062, but I never got it. I've tried
>> to convert it and cast it, but so far I haven't been able to do so.
>> When I converted it, I got only 1340062 (it was missing the first 2
>> zeroes).
>> We're creating fakes SSNs for people who don't have one and I'm
>> supposed to know which one is the max we've created so far and then add
>> 1 to it and so on and so on. This worked in Access, but when we
>> upgraded to SQL it didn't work anymore.
>> Any help would be appreciated it.
>> Thanks.
>|||Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
assign them in a range that is currently in use by a state. That's just
asking for trouble.
According to the SSA, any valid number beginning with "000" will never be a
valid SSN. Here are some links to help with the business rules:
http://www.socialsecurity.gov/employer/highgroup.txt
http://www.socialsecurity.gov/employer/stateweb.htm
Given the three numbers you've passed in, the SELECT below will grab the
MAX(). It sounds like you want to add one and left pad it with zeroes.
Here's a sample:
CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @.iSSN INT
SELECT @.iSSN = CAST(@.ssn AS INT) + 1
DECLARE @.newSSN VARCHAR(9)
SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
RETURN @.newSSN
END
GO
CREATE TABLE #temp(ssn VARCHAR(9))
INSERT INTO #temp(ssn)
SELECT '001330000'
UNION SELECT '001379999'
UNION SELECT '001340062'
SELECT dbo.AddOneAndPadSSN(MAX(ssn))
FROM #temp
DROP TABLE #temp
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Nobody here but us wannabe fashion clothes reps... :)
"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
>> --
>> Arnie Rowland, YACE*
>
> I would think they are already contacting
> CoreComm - Voyager, Inc
> East Lansing, Michigan
>|||One would think they don't have enough people to cover the newsgroups, what
with trying to keep up with all of our phone calls :)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uTsVwOimGHA.856@.TK2MSFTNGP03.phx.gbl...
> Arnie Rowland wrote:
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
> Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA will
> be looking for you... :-)
> Oh crap, now me too...|||SSN are not as aggressively validated in the States the way they are in
Canada. With SSI numbers (the Canadian equivalent of SSNs) your number is
validated along with your birthdate before your first pay check is cut.
Providing an employer with a fake SSI number will only generate a request
for validation from Revenue Canada if the name and birthdate so not match
and the SSI number is not already in the system.
In America you have to prove your authorization to work in the States which
may or may not include you displaying a Social Security card to your
employer. If you provide them with a bogus number and they do not request to
see the card at some point in time the Social Security Administration may
make a request of your employer for validation of the number you supplied at
some point down the road - normally a year down the road.
Social Security Numbers have no checksum on them the way credit card numbers
do. The first three digits normally indicate where it was issued, and
immigrants typically receive their numbers from a different bank. Locales
may exhaust their number allotments before other locales and they may pull
from the same number pool as a less populous area.
Please refer to this document for more information.
http://en.wikipedia.org/wiki/Social_security_number#Valid_SSNs
I had a friend who had a completely bogus SSN for years with no problems.
Most frequently you run into problems when you and someone else uses the
same number, but with different names for it.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23GbmMmimGHA.5052@.TK2MSFTNGP04.phx.gbl...
> Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
> advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
> assign them in a range that is currently in use by a state. That's just
> asking for trouble.
> According to the SSA, any valid number beginning with "000" will never be
> a valid SSN. Here are some links to help with the business rules:
> http://www.socialsecurity.gov/employer/highgroup.txt
> http://www.socialsecurity.gov/employer/stateweb.htm
> Given the three numbers you've passed in, the SELECT below will grab the
> MAX(). It sounds like you want to add one and left pad it with zeroes.
> Here's a sample:
> CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
> RETURNS VARCHAR(9)
> AS
> BEGIN
> DECLARE @.iSSN INT
> SELECT @.iSSN = CAST(@.ssn AS INT) + 1
> DECLARE @.newSSN VARCHAR(9)
> SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
> RETURN @.newSSN
> END
> GO
> CREATE TABLE #temp(ssn VARCHAR(9))
> INSERT INTO #temp(ssn)
> SELECT '001330000'
> UNION SELECT '001379999'
> UNION SELECT '001340062'
> SELECT dbo.AddOneAndPadSSN(MAX(ssn))
> FROM #temp
> DROP TABLE #temp
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
>> Hello, I'm trying to get the Max number from a range of Social Security
>> numbers in my SQL 2000 Customer table. The SSNs as stored as
>> varchar(9).
>> I'm supposed to get the SSN with the 'highest' numbers from 001330000
>> to 001379999 range.
>> I know that number should be 001340062, but I never got it. I've tried
>> to convert it and cast it, but so far I haven't been able to do so.
>> When I converted it, I got only 1340062 (it was missing the first 2
>> zeroes).
>> We're creating fakes SSNs for people who don't have one and I'm
>> supposed to know which one is the max we've created so far and then add
>> 1 to it and so on and so on. This worked in Access, but when we
>> upgraded to SQL it didn't work anymore.
>> Any help would be appreciated it.
>> Thanks.
>|||Hey!! Bugger off! I wanted that job.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Mike C#" <xyz@.xyz.com> wrote in message
news:O%238n7mimGHA.1404@.TK2MSFTNGP05.phx.gbl...
> Nobody here but us wannabe fashion clothes reps... :)
> "Michael Kujawa" <nof at kujawas dot net> wrote in message
> news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>>
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
>> --
>> Arnie Rowland, YACE*
>>
>> I would think they are already contacting
>> CoreComm - Voyager, Inc
>> East Lansing, Michigan
>>
>|||SSN can be aggressively validated. Where I used to work we actually ran
validations, and discovered some fascinating things. Like several people in
a 6-block radius of Los Angeles with the same SSN :) Employers here are
supposed to look at, and make copies of, your SSN card when they hire you
(I-9 form). Of course no one from the government ever checks up on their
I-9 forms, so employers don't really go out of their way to validate the
information given to them...
Anyway, some info about SSNs:
-The first three digits are normally assigned based on the state or
territory/protectorate belonging to the ZIP code you mailed your application
in from. Some are assigned to U.S. citizens abroad (U.S. Embassies,
Enumeration at Entry) and some were assigned by the Railroad Board back in
the day.
-Numbers beginning with 000, 666 or 900 - 999 will not be assigned by the
SSA (they're used for internal government agencies). Numbers with 00 in the
middle or 0000 at the end will not be issued.
-There are some groups of Americans who refuse to get SSN's. The Amish are
an example of these groups.
-The first SSN ever assigned was to John D. Sweeney, Jr., of New Rochelle,
NY.
-The lowest SSN ever assigned was 001-01-0001 to former N.H. Governor and
Social Security Board Chairman John G. Winant. He turned it down. Then it
was offered to John Campbell of the Federal Bureau of Old Age Benefits. He
also turned it down. Finally it was assigned to the first applicant from
New Hampshire: Grace D. Owen of Concord.
Don't ask why I know all this crap about SSN's :) My old job required a lot
of investigation into it for validation purposes. In fact, a while back I
posted a basic SSN validation web service to Code Project that performs all
of the basic validations, including high group check, group validation and a
check against the invalidated SSN list (those used in advertising and
invalidate by the SSA): http://www.codeproject.com/aspnet/ssnvalidator.asp.

Wednesday, March 7, 2012

Convert SQL 2005 to SQL 2000

I started a project using SQL 2005 and now the customer has decided they can't go to 2005 yet so I need to convert it back to 2000. I didn't use any new features of 2005. How do I move it back to SQL 2000?Hi Steve,

Well, backup of 2005 doesn't restore on SQL 2000. However, you can generate SQL Script from objtecs and run into SQL Server 2000 Database.

Follow this steps:

1. Create new database in SQL 2000
2. Genarete script from objtects on the SQL 2005
3. Execute this script on the SQL 2000

If you have data, use bcp utilities to export and import.

Nilton Pinheiro|||

I am have problems with the syntax on the sql05 scripts. They are not running in 2000.

Has anyone else had problems?

|||socalmp, can you give some examples? Hard to say without them.|||

I have a db I imported into sql05 and now I need to transfer that db to a hosting company using sql2000.

I go right click on db >Tasks > generate scripts > and a wizard pops up to walk thru the settings for the script. I turn everything to false except script foreign keys, primary keys and unique keys because I have some relationships in the tables. The script version is sql2000 and the behavior is to generate create statements only. this is what I get:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [patients](

[patientID] [int] IDENTITY(1,1) NOT NULL,

[last] [text] NULL,

[first] [text] NULL,

[dob] [datetime] NULL,

[gender] [text] NULL,

[sexuality] [text] NULL,

[ethnicity] [text] NULL,

[job] [varchar](50) NULL,

[company] [varchar](50) NULL,

[homephone] [varchar](50) NULL,

[homephone2] [varchar](50) NULL,

[mobile] [varchar](50) NULL,

[businessphone] [varchar](50) NULL,

[streetadd] [varchar](50) NULL,

[state] [text] NULL,

[zip] [nvarchar](50) NULL,

[country] [varchar](50) NULL,

[email] [varchar](50) NULL,

[email2] [varchar](50) NULL,

[webpage] [varchar](50) NULL,

[im] [varchar](50) NULL,

[reffered] [varchar](50) NULL,

[type] [varchar](50) NULL,

CONSTRAINT [PK_patients] PRIMARY KEY CLUSTERED

(

[patientID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [treatment](

[patientID] [int] NOT NULL,

[treatmentdate] [datetime] NULL,

[product] [varchar](50) NULL,

[amount] [numeric](18, 0) NULL

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [notes](

[patientID] [int] NOT NULL,

[notes] [varchar](50) NOT NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[treatment] WITH CHECK ADD CONSTRAINT [FK_treatment_patients] FOREIGN KEY([patientID])

REFERENCES [patients] ([patientID])

ALTER TABLE [dbo].[notes] WITH CHECK ADD CONSTRAINT [FK_notes_patients] FOREIGN KEY([patientID])

REFERENCES [patients] ([patientI

When You run this in query analyzer I get this error:

Line 29: Incorrect syntax near '('.

And that is from this line:

CONSTRAINT [PK_patients] PRIMARY KEY CLUSTERED

(

I have tried many times with no success to get the script wizard to make a script compatible with SQL2000. My t-sql skills need improvement obviously, but I don't know why this has to be so difficult. Enterprise manager seemed easier to work with then the new sql05. I am sure I just need to get up to speed with its features

Any suggestions? I am trying to migrate a db from sql05 to sql 2000 using Query Analyser or csv import wizard only. I was able to previoulsy generate scripts from enterpirse manager to create the db with Query Analyser.

|||

Here's a script from Sql2000 made with enterprise manager and it ran just fine in the query anaylzer:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_notes_patients]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[notes] DROP CONSTRAINT FK_notes_patients

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_treatment_patients]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[treatment] DROP CONSTRAINT FK_treatment_patients

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[notes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[notes]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[patients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[patients]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[treatment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[treatment]

CREATE TABLE [dbo].[notes] (

[patientID] [int] NOT NULL ,

[notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[patients] (

[patientID] [int] IDENTITY (1, 1) NOT NULL ,

[last] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[first] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dob] [datetime] NULL ,

[gender] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[sexuality] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ethnicity] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[job] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[homephone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[homephone2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mobile] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[businessphone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[streetadd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[state] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[zip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[email2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[webpage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[im] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[reffered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[treatment] (

[patientID] [int] NOT NULL ,

[treatmentdate] [datetime] NULL ,

[product] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[amount] [numeric](18, 0) NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[patients] WITH NOCHECK ADD

CONSTRAINT [PK_patients] PRIMARY KEY CLUSTERED

(

[patientID]

) ON [PRIMARY]

ALTER TABLE [dbo].[notes] ADD

CONSTRAINT [FK_notes_patients] FOREIGN KEY

(

[patientID]

) REFERENCES [dbo].[patients] (

[patientID]

)

ALTER TABLE [dbo].[treatment] ADD

CONSTRAINT [FK_treatment_patients] FOREIGN KEY

(

[patientID]

) REFERENCES [dbo].[patients] (

[patientID]

)

So I am not sure What the difference is betwen the scripts?

|||

Maybe your default collation is not set to SQL_Latin1_General_CP1_CI_AS

I noticed in the 2000 script it is being specified all the way down. That might need to be specified in your 2000 instance. Try adding the collation statement to your columns in the 2005 script and see.

|||There is an easy way to solve the problem.

Open the Microsoft SQL Management Studio, then in "Object Explorer" right click on your Database->Tasks->Generate Scripts

In the Wizard

Script Window Click Next, Select You DB, Click Next and in the Choose

Script Option Step, Change the value of "Script for Server Version"

from "SQL Server 2005" to "SQL Server 2000" and go on.

When you click "Finish" button, your script will SQL 2000 compatible.

Good luck.

|||I tried this and the first script is a product of SQL 2005 and their acript generator. I made sure it was SQL 2000 compatible and it will not run in a SQL 2000 query analyzer. Strange!|||

On the script side, I looked more closely and I think the (IGNORE_DUP_KEY = OFF) syntax is incorrect. I believe you either have IGNORE_DUP_KEY specified or it is absent (which is the equivalent of OFF).

Another way you could accomplish your goal is to set your database to a 2000 version format, make a backup, and send it to your hosting provider to restore on their server. That solution should at least solve any script problems.

Run the following command against your database in SQL Management Studio and then create your backup.

sp_dbcmptlevel 'MyDatabaseName', 80

Hope that helps.
Hugh

|||

While generating the Script

We have to select option

Script for Server version as SQL Server 2000

|||

Created the databse with compatibility level set to sql200.

restored my client database from sql2005

i run the command "sp_dbcmptlevel 'MyDatabaseName', 80"

backup the database

everyrhing was ok.

I go to the enterprise manager of my sql2000 and restore the backed up database and this is the error i got.

"Too many backup devices specified for backup or restore; only 64 are allowed. Restore Database is terminating abnormally."

|||Has anybody found useful input on the 64 backup device limit? Same problem here moving a SQL 2005 db to SQL 2005 or 2000.|||

Hi Byrnie,

In my case I have SQL2005 running on my laptop. I needed to move the database onto my server which is currently running SQL 2000. On the laptop and in SQL Server Management Studio I selected the import/export Wizard. Using the wizard I was able to export my SQL2005 tables across to the SQL2000 server. I have not yet completed the process and have just started the job. I will have to verify the data as soon as the job has completed. This is the first time I am contributing anything to a forum. I hope it helps

|||Hi frnds,

I have done the process of Converting SQL 2005 Database into SQL 2000 Database.

1.Create Database in SQL Server 2000 which is the name into SQL 2005.
2.From SQL 2005 Mgmt Console Studio , from Database - tasks - Generate Script
-then select Database -Script for Server Version - Change it from SQL Server 2005 to SQL Server 2000
3. Do the process as per wizard.
4.We can run that Script into SQL Query Analyzer 2000 for that selected database where you want to move it.

Its working fine.I got whole database table creation and whole relationship which was made into SQL Server 2005.

Gr8t.. thanks for your help.