default field type when importing

I asked this question in the .dts newsgroup and got no response. If someone
can help me here, that would be most appreciated.
I have a customer that does alot of importing of .txt files into SQL
databases. In the past, the default field type seemed to be varchar( 255).
Now, it seems that all the fields import as varchar( 8000 ), which messes up
some of their third-party programs that they use. So, whenever they import,
they have to go through every field and change it's type.
Does anyone know why this is happening and how I can change it back?
I have a feeling that this change occurred when I installed the MDAC
security update. Is this possible?
Thanks,
MikeWhat is your version of SQL Server. This is mine.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
Where do you see (8000). I just asked DTS to create a table for me from a
CSV and it created it as (255).
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> I asked this question in the .dts newsgroup and got no response. If
someone
> can help me here, that would be most appreciated.
> I have a customer that does alot of importing of .txt files into SQL
> databases. In the past, the default field type seemed to be varchar(
255).
> Now, it seems that all the fields import as varchar( 8000 ), which messes
up
> some of their third-party programs that they use. So, whenever they
import,
> they have to go through every field and change it's type.
> Does anyone know why this is happening and how I can change it back?
> I have a feeling that this change occurred when I installed the MDAC
> security update. Is this possible?
> Thanks,
> Mike
>|||Here's what I get:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you running
yours on a WinXP Pro workstation?
Once I have the source selected, there's a button with an (...) to view the
transform. That's where the varchar( 8000 ) can first be seen -- and
changed.
Have you kept up with all the Windows security updates? Including the MDAC
and JET updates that came out in the last couple of months?
My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
that got installed with the SQL Server 2000 SP3a.
Thanks for your help.
Mike
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> What is your version of SQL Server. This is mine.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> Where do you see (8000). I just asked DTS to create a table for me from a
> CSV and it created it as (255).
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > I asked this question in the .dts newsgroup and got no response. If
> someone
> > can help me here, that would be most appreciated.
> >
> > I have a customer that does alot of importing of .txt files into SQL
> > databases. In the past, the default field type seemed to be varchar(
> 255).
> > Now, it seems that all the fields import as varchar( 8000 ), which
messes
> up
> > some of their third-party programs that they use. So, whenever they
> import,
> > they have to go through every field and change it's type.
> >
> > Does anyone know why this is happening and how I can change it back?
> >
> > I have a feeling that this change occurred when I installed the MDAC
> > security update. Is this possible?
> >
> > Thanks,
> > Mike
> >
> >
>|||I am on WinXP yes
MDAC = 9.0.608.0 (Rollback version is the same as yours)
Ahhhhhhhhhhhh. I just spotted where you get your values from. You get them
in the wizard. Sorry I never use the wizard. I therefore do not know if
this is changed behaviour. I know that in the Designer i.e. building a
package without the wizard it is 255.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
news:OUlFMJJoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> Here's what I get:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
> happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you
running
> yours on a WinXP Pro workstation?
> Once I have the source selected, there's a button with an (...) to view
the
> transform. That's where the varchar( 8000 ) can first be seen -- and
> changed.
> Have you kept up with all the Windows security updates? Including the
MDAC
> and JET updates that came out in the last couple of months?
> My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
> that got installed with the SQL Server 2000 SP3a.
> Thanks for your help.
> Mike
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > What is your version of SQL Server. This is mine.
> >
> > Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> >
> > Where do you see (8000). I just asked DTS to create a table for me from
a
> > CSV and it created it as (255).
> >
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >
> > "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> > news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > > I asked this question in the .dts newsgroup and got no response. If
> > someone
> > > can help me here, that would be most appreciated.
> > >
> > > I have a customer that does alot of importing of .txt files into SQL
> > > databases. In the past, the default field type seemed to be varchar(
> > 255).
> > > Now, it seems that all the fields import as varchar( 8000 ), which
> messes
> > up
> > > some of their third-party programs that they use. So, whenever they
> > import,
> > > they have to go through every field and change it's type.
> > >
> > > Does anyone know why this is happening and how I can change it back?
> > >
> > > I have a feeling that this change occurred when I installed the MDAC
> > > security update. Is this possible?
> > >
> > > Thanks,
> > > Mike
> > >
> > >
> >
> >
>|||Thanks for your help. I'm not too familiar with DTS. I don't even know how
to get started in it. Can you do all the same things in the designer as in
the wizard?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:enEPzlJoDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I am on WinXP yes
> MDAC = 9.0.608.0 (Rollback version is the same as yours)
>
> Ahhhhhhhhhhhh. I just spotted where you get your values from. You get
them
> in the wizard. Sorry I never use the wizard. I therefore do not know if
> this is changed behaviour. I know that in the Designer i.e. building a
> package without the wizard it is 255.
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> news:OUlFMJJoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> > Here's what I get:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> > It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
> > happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you
> running
> > yours on a WinXP Pro workstation?
> >
> > Once I have the source selected, there's a button with an (...) to view
> the
> > transform. That's where the varchar( 8000 ) can first be seen -- and
> > changed.
> >
> > Have you kept up with all the Windows security updates? Including the
> MDAC
> > and JET updates that came out in the last couple of months?
> >
> > My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
> > that got installed with the SQL Server 2000 SP3a.
> >
> > Thanks for your help.
> > Mike
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > > What is your version of SQL Server. This is mine.
> > >
> > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > May 31 2003 16:08:15
> > > Copyright (c) 1988-2003 Microsoft Corporation
> > > Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> > >
> > > Where do you see (8000). I just asked DTS to create a table for me
from
> a
> > > CSV and it created it as (255).
> > >
> > >
> > > --
> > >
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > >
> > >
> > > "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in
message
> > > news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > > > I asked this question in the .dts newsgroup and got no response. If
> > > someone
> > > > can help me here, that would be most appreciated.
> > > >
> > > > I have a customer that does alot of importing of .txt files into SQL
> > > > databases. In the past, the default field type seemed to be
varchar(
> > > 255).
> > > > Now, it seems that all the fields import as varchar( 8000 ), which
> > messes
> > > up
> > > > some of their third-party programs that they use. So, whenever they
> > > import,
> > > > they have to go through every field and change it's type.
> > > >
> > > > Does anyone know why this is happening and how I can change it back?
> > > >
> > > > I have a feeling that this change occurred when I installed the MDAC
> > > > security update. Is this possible?
> > > >
> > > > Thanks,
> > > > Mike
> > > >
> > > >
> > >
> > >
> >
> >
>sql

DEC TO HEX CONVERSION WHILE IMPORTING

I HAVE DECIMAL NUMBER IN MY TEST TABLE COLUMN CARD_NUMBER
AS FOLLOWS
2571549730.0
2571549826.0
2571550034.0
2571550210.0
2571550306.0
2571551378.0
2571551586.0
2571551682.0
2571551762.0
2571551874.0
I WANT TO CREATE A TEMP TABLE AND MOVE THIS COLUMN BUT
BEFORE MOVING IT SHOULD CONVERT INTO HEXADECIMAL NUMBER
WHILE INSERTING INTO TEMP TABLE
THANKS
Mustafa,
If your decimal values fit into a bigint, you could write a
user-defined function (SQL Server 2000 required):
create function dec2hex (
@.decimal bigint
) returns varbinary(20) as begin
declare @.b varbinary(20)
set @.b = 0x
while @.decimal > 0 begin
set @.b = cast(cast(@.decimal%256 as tinyint) as binary(1)) + @.b
set @.decimal = @.decimal/256
end
return @.b
end
go
select dbo.dec2hex(1)
select dbo.dec2hex(2571551874.0)
go
drop function dec2hex
-- Steve Kass
-- Drew University
-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
MUSTAFA wrote:

>I HAVE DECIMAL NUMBER IN MY TEST TABLE COLUMN CARD_NUMBER
>AS FOLLOWS
>2571549730.0
>2571549826.0
>2571550034.0
>2571550210.0
>2571550306.0
>2571551378.0
>2571551586.0
>2571551682.0
>2571551762.0
>2571551874.0
>I WANT TO CREATE A TEMP TABLE AND MOVE THIS COLUMN BUT
>BEFORE MOVING IT SHOULD CONVERT INTO HEXADECIMAL NUMBER
>WHILE INSERTING INTO TEMP TABLE
>
>THANKS
>
|||YES IT WORKS
THANKS Mr. STEVE

>--Original Message--
>Mustafa,
> If your decimal values fit into a bigint, you could
write a
>user-defined function (SQL Server 2000 required):
>create function dec2hex (
> @.decimal bigint
>) returns varbinary(20) as begin
> declare @.b varbinary(20)
> set @.b = 0x
> while @.decimal > 0 begin
> set @.b = cast(cast(@.decimal%256 as tinyint) as binary
(1)) + @.b[vbcol=seagreen]
> set @.decimal = @.decimal/256
> end
> return @.b
>end
>go
>select dbo.dec2hex(1)
>select dbo.dec2hex(2571551874.0)
>go
>drop function dec2hex
>-- Steve Kass
>-- Drew University
>-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
>MUSTAFA wrote:
CARD_NUMBER
>.
>
|||Dear Mr. Steve
i have created user define function dec2hex in sql server
2000 and when i access it using sql server query analyser
it give me desired result i.e it convert decimal into
hexadecimal number
The query is as follow
select dbo.dec2hex(card_number) as card_number from
tbltest
But when i used it using VB6 recordset object it show ?
rather then showing hexadecimal number
thanks

>--Original Message--
>Mustafa,
> If your decimal values fit into a bigint, you could
write a
>user-defined function (SQL Server 2000 required):
>create function dec2hex (
> @.decimal bigint
>) returns varbinary(20) as begin
> declare @.b varbinary(20)
> set @.b = 0x
> while @.decimal > 0 begin
> set @.b = cast(cast(@.decimal%256 as tinyint) as binary
(1)) + @.b[vbcol=seagreen]
> set @.decimal = @.decimal/256
> end
> return @.b
>end
>go
>select dbo.dec2hex(1)
>select dbo.dec2hex(2571551874.0)
>go
>drop function dec2hex
>-- Steve Kass
>-- Drew University
>-- Ref: 77AF63E1-A927-40A1-A7B0-6EA31F1C80F2
>MUSTAFA wrote:
CARD_NUMBER
>.
>
|||Mustafa,
This function returns a varbinary(20) value, so if you are seeing ?
somewhere, then something in your application is not displaying
varbinary values correctly. I don't know how VB6 recordsets display
information of varbinary type. Can your VB recordset object display
what is in this recordset?
select 0x123456AB as aBinaryValue
It sounds like a VB issue, not a SQL Server issue.
Steve Kass
Drew University
mustafa wrote:
[vbcol=seagreen]
>Dear Mr. Steve
>i have created user define function dec2hex in sql server
>2000 and when i access it using sql server query analyser
>it give me desired result i.e it convert decimal into
>hexadecimal number
>The query is as follow
>select dbo.dec2hex(card_number) as card_number from
>tbltest
>But when i used it using VB6 recordset object it show ?
>rather then showing hexadecimal number
>thanks
>
>
>write a
>
>(1)) + @.b
>
>CARD_NUMBER
>