Need to copy All oracle tables to SQL Server 2005

I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.

[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

You can't change the source at run-time since the Meta-data for the columns is static.

To do a copy like this, you need to have a seperate source and destination for each table. The easiest way to do this is to use the Import/Export wizard in 2005 to generate the package. Select the target database in SSMS, right click it and pick Tasks/Import data to run the wizard. it should be pretty self-explanatory, but, if you have trouble, there is a good section on it in BOL.

|||

swatts777 wrote:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

This warning can easily be fixed by setting AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component.

swatts777 wrote:

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

SSIS won't do this. I'm worried that you might have read some documentation somewhere that led you think that it will. Is that the case?

Like David said, you have to build a data-flow for each table. You could make it easy on yourself and get the import/export wizard to build it for you.

-Jamie

-Jamie

|||

Apparently, my Oracle Provider wasn't registered in SQL, so I did not know I had this option avalable to me. Using Microsoft's OLEDB Provider for Oracle only lets you pull data over using custom queries. I restarted SQL and my oracle provider showed up. WOW! Thanks for all your help, this worked great! I was able to go through each table and map datatypes and everything. Then I saved the whole thing as an SSIS package for use when we go live.

|||

Hi swatts777,

Can you(or anyone else) tell me how to register an Oracle provider as you described above?
i am having the same endless shyte with data types when pulling single values from Oracle.

Anybody please help me

Regards,

Pieter

|||

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

|||

Peter,

After installing the Oracle client, you must set up Oracle Net services to point to the correct Oracle database. There are plenty of articles on doing this. You should have installed the Client using Oracle Universal Installer that comes with the client you are using and that you downloaded from Oracle's site. If you have done all of this, then you can restart the machine. That is the best way. If you are using SQL Server 2005, under Server Objects>Linked Servers>Providers you should now see a OraOLEDB.Oracle provider listed. Now you may create your Linked server using this provider instead of MSADORA.

Hope this helps,

Shawn

|||

CleverPete wrote:

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

This is a perfect example of how a flamming thread starts and how they get deleted/locked. I just wanted to point out a detail that you could have miss. BTW, that was quite the answer; just install it and re-start the machine.

Rafael Salas wrote:

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||I wasn't really focused on the questions being asked, rather my on experiences in dealing with the problems I faced. I will try to answer questions more precise and pay more attention to the actual questions being posted.|||

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop. I do not want to create a separate design time mapping for each table (there are lots of tables). If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop. Unless I misunderstood, you are not dynamically writing to tables with different structures, correct? If so, how did you get that to work?

|||

Dave Dumas wrote:

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop.

You're right, it doesn't do this. You can't do what you are attempting unless the columns in all the source tables are the same.

Dave Dumas wrote:

I do not want to create a separate design time mapping for each table (there are lots of tables).

Unfortunately if you want to use data-flows this is your only option.

Dave Dumas wrote:

If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop.

Good idea that. Let us know how it goes.

-Jamie

|||

Thanks Jamie. What I did (since the OPENQUERY syntax does not all variables, only strings), was to write a select statement with dynamic sql as output. I outputted 300 OPENQUERY statements inside of try-catch BEGIN END blocks. This worked great. - Dave

Need to copy a database in the same server without using backup re

Hi,
I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
using the backup restore operation? Thanks in advance.
Use a DTS package.
Bobby Ryzhy
bobby@. name of domain below
http://www.weekendtech.net
On Tue, 6 Jul 2004 15:34:01 -0700, "Jack"
<Jack@.discussions.microsoft.com> wrote:

>Hi,
>I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
>using the backup restore operation? Thanks in advance.
|||WHat is wrong with Backup and Restore? You can Restore to a different name
and location. You can also try sp_attach_db.
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.
|||Hi,
Use this below list of commands, But it require a minutes down time for
actual database.
sp_detach_db management
go
copy the MDF and LDF file for management database to a different folder
go
-- attach the original database management
sp_attach_db
'management','physical_mdf_file_with_path','plysic al_ldf_with_path'
go
--attach the new database
sp_attach_db
'management_mid','physical_mdf_file_with_path','pl ysical_ldf_with_path'
Note:
DTS/BCP are also possible , but will take long time. It requires some manual
tasks as well.
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.
sql

Need to copy a database in the same server without using backup re

Hi,
I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
using the backup restore operation? Thanks in advance.Use a DTS package.
Bobby Ryzhy
bobby@. name of domain below
http://www.weekendtech.net
On Tue, 6 Jul 2004 15:34:01 -0700, "Jack"
<Jack@.discussions.microsoft.com> wrote:
>Hi,
>I need to copy a database 'management' in the same server and name it as management_mid. Is there any command or script to copy the database without
>using the backup restore operation? Thanks in advance.|||WHat is wrong with Backup and Restore? You can Restore to a different name
and location. You can also try sp_attach_db.
--
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.|||Hi,
Use this below list of commands, But it require a minutes down time for
actual database.
sp_detach_db management
go
copy the MDF and LDF file for management database to a different folder
go
-- attach the original database management
sp_attach_db
'management','physical_mdf_file_with_path','plysical_ldf_with_path'
go
--attach the new database
sp_attach_db
'management_mid','physical_mdf_file_with_path','plysical_ldf_with_path'
Note:
DTS/BCP are also possible , but will take long time. It requires some manual
tasks as well.
--
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.

Need to copy a database in the same server without using backup re

Hi,
I need to copy a database 'management' in the same server and name it as man
agement_mid. Is there any command or script to copy the database without
using the backup restore operation? Thanks in advance.Use a DTS package.
Bobby Ryzhy
bobby@. name of domain below
http://www.weekendtech.net
On Tue, 6 Jul 2004 15:34:01 -0700, "Jack"
<Jack@.discussions.microsoft.com> wrote:

>Hi,
>I need to copy a database 'management' in the same server and name it as ma
nagement_mid. Is there any command or script to copy the database without
>using the backup restore operation? Thanks in advance.|||WHat is wrong with Backup and Restore? You can Restore to a different name
and location. You can also try sp_attach_db.
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.|||Hi,
Use this below list of commands, But it require a minutes down time for
actual database.
sp_detach_db management
go
copy the MDF and LDF file for management database to a different folder
go
-- attach the original database management
sp_attach_db
'management','physical_mdf_file_with_pat
h','plysical_ldf_with_path'
go
--attach the new database
sp_attach_db
'management_mid','physical_mdf_file_with
_path','plysical_ldf_with_path'
Note:
DTS/BCP are also possible , but will take long time. It requires some manual
tasks as well.
--
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:4B8B3A20-69CC-46F2-A9C3-3009FE770FEE@.microsoft.com...
> Hi,
> I need to copy a database 'management' in the same server and name it as
management_mid. Is there any command or script to copy the database without
> using the backup restore operation? Thanks in advance.

need to convert this SQL2000 DTS validation code to SQL2005 SSIS

Hello,

How do I convert VBSCRIPT code in a DTS package which is used for data validation to SQL 2005 SSIS?

Thanks,

Michael

Michael,

There isn't really a tool to do that for you. Its a manual process I'm afraid.

If you explain what sort of data validation you want to do then you may find people on here will be able to help you achieve the same in SSIS.

-Jamie

|||

Hello,

I have a source table which contains some invalid dates (can be null, letters, etc). I want to check to see if the date is valid. If the date is valid then I want to use it. If the date is not valid then I went to set the destination field to null.

In the past I used vbscript code like

Dim dtStart
Dim dtEnd
Dim dtSource

dtStart = CDate("1/1/1753")
dtEnd = CDate("12/31/9999")

If Not IsNull(DTSSource("MyDate")) Then
If IsDate(DTSSource("MyDate")) Then
dtSource = CDate( DTSSource("MyDate"))

If dtSource >= dtStart and dtSource <= dtEnd Then
DTSDestination("MyDate") = DTSSource("MyDate")
end if
end if
end if
Main = DTSTransformStat_OK

Thanks,

Michael

Need to convert returned values.

I have an application which collects WMI info from servers and stores it in
SQL. The vendor reports for displaying this leave much to be desired. I've
developed numerous replacement reports using SRS, but I'm having trouble
converting the data they collect because of the way it's formatting and
stored....can anyone help?
Sample:
Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
I want to drop all of the [-] and [+] and just leave a space between labels
and values.
Any help woul be greatly appreciated.
RCITGUYMaybe in your SQL statment you could try something like this:
SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
"RCITGuy" wrote:
> I have an application which collects WMI info from servers and stores it in
> SQL. The vendor reports for displaying this leave much to be desired. I've
> developed numerous replacement reports using SRS, but I'm having trouble
> converting the data they collect because of the way it's formatting and
> stored....can anyone help?
> Sample:
> Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> I want to drop all of the [-] and [+] and just leave a space between labels
> and values.
> Any help woul be greatly appreciated.
> RCITGUY
>|||I have to apologize for sounding stupid...but where should I place this in my
existing SQL select statement? "column holding data is
"WMIConfiguration.Configuration"
___________________________________________________________________
SELECT ComputerGroup.Name, Computer.Name AS Server,
WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
Computer.Address, Computer.PhysicalMem,
Computer.PageSize
FROM Computer INNER JOIN
WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID INNER JOIN
ComputerGroup ON Computer.GroupID =ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
_____________________________________________________________
"Aiwa" wrote:
> Maybe in your SQL statment you could try something like this:
> SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> "RCITGuy" wrote:
> > I have an application which collects WMI info from servers and stores it in
> > SQL. The vendor reports for displaying this leave much to be desired. I've
> > developed numerous replacement reports using SRS, but I'm having trouble
> > converting the data they collect because of the way it's formatting and
> > stored....can anyone help?
> >
> > Sample:
> > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> >
> > I want to drop all of the [-] and [+] and just leave a space between labels
> > and values.
> >
> > Any help woul be greatly appreciated.
> > RCITGUY
> >|||Something like this:
SELECT ComputerGroup.Name,
Computer.Name AS Server,
WMIConfiguration.ObjectType,
REPLACE(REPLACE(WMIConfiguration.Configuration,'[+]','
'),'[-]',' ') AS Configuration ,
Computer.OSVer,
Computer.Address,
Computer.PhysicalMem,
Computer.PageSize
FROM Computer
INNER JOIN WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID
INNER JOIN ComputerGroup ON Computer.GroupID = ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
"RCITGuy" wrote:
> I have to apologize for sounding stupid...but where should I place this in my
> existing SQL select statement? "column holding data is
> "WMIConfiguration.Configuration"
> ___________________________________________________________________
> SELECT ComputerGroup.Name, Computer.Name AS Server,
> WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
> Computer.Address, Computer.PhysicalMem,
> Computer.PageSize
> FROM Computer INNER JOIN
> WMIConfiguration ON Computer.ComputerID => WMIConfiguration.ComputerID INNER JOIN
> ComputerGroup ON Computer.GroupID => ComputerGroup.GroupID
> WHERE (ComputerGroup.Name = @.Company)
> _____________________________________________________________
> "Aiwa" wrote:
> > Maybe in your SQL statment you could try something like this:
> > SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> >
> > "RCITGuy" wrote:
> >
> > > I have an application which collects WMI info from servers and stores it in
> > > SQL. The vendor reports for displaying this leave much to be desired. I've
> > > developed numerous replacement reports using SRS, but I'm having trouble
> > > converting the data they collect because of the way it's formatting and
> > > stored....can anyone help?
> > >
> > > Sample:
> > > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> > >
> > > I want to drop all of the [-] and [+] and just leave a space between labels
> > > and values.
> > >
> > > Any help woul be greatly appreciated.
> > > RCITGUY
> > >

Need to convert Date from "YYYY-MM-DD 00:00:00.0000" to "MM/DD/YYYY&qu

Hello, I was asked to run a query to retrieve some data for my boss. The
query works just fine, however, my boss wants to import the data into his
excel spreadsheet, and the date values as displayed in the result set in
query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
all of the time stamp crap. I tried some CONVERT/CAST functions to no avail
.
This is sort of urgent, PLEASE HELP!!
Thank you...
RGAlso, my date does need the forward slashes too (/) as in "mm/dd/yyyyy".
Thanks.
RG
Robert G wrote:
>Hello, I was asked to run a query to retrieve some data for my boss. The
>query works just fine, however, my boss wants to import the data into his
>excel spreadsheet, and the date values as displayed in the result set in
>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
>all of the time stamp crap. I tried some CONVERT/CAST functions to no avai
l.
>This is sort of urgent, PLEASE HELP!!
>Thank you...
>RG|||If its left in a datetime data type, the time portion will always be
returned. Instead, convert it to a string using something like the
following,
select convert(varchar(10), getdate(), 101)
--Brian
(Please reply to the newsgroups only.)
"Robert G via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:546B8E3963E09@.webservertalk.com...
> Hello, I was asked to run a query to retrieve some data for my boss. The
> query works just fine, however, my boss wants to import the data into his
> excel spreadsheet, and the date values as displayed in the result set in
> query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
> Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim
> off
> all of the time stamp crap. I tried some CONVERT/CAST functions to no
> avail.
> This is sort of urgent, PLEASE HELP!!
> Thank you...
> RG|||SQL Server does not have a "date only" data type, so you will need to jump
through some hoops...
Since your boss is importing the data into Excel, my first recommendation
would be to leave the data as it is, and just format it appropriately in
Excel. Excel can easily suppress the display of the time values, and it will
properly recognize the values as date values.
If that is not an option, you could try a kludge such as "SELECT
CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable",
which will output a character column formatted as 'YYYYMMDD'. Depending on
how you are making this output available to Excel, Excel may or may not
determine that this is a Date column, and may not provide proper sorting
functionality.
"Robert G via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:546B8E3963E09@.webservertalk.com...
> Hello, I was asked to run a query to retrieve some data for my boss. The
> query works just fine, however, my boss wants to import the data into his
> excel spreadsheet, and the date values as displayed in the result set in
> query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
> Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim
> off
> all of the time stamp crap. I tried some CONVERT/CAST functions to no
> avail.
> This is sort of urgent, PLEASE HELP!!
> Thank you...
> RG|||References: <546B8E3963E09@.webservertalk.com>
In-Reply-To: <546B8E3963E09@.webservertalk.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <#Wf$YLkuFHA.860@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: 208.13.225.3
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:552942
in addition to the other comments - you could simply change the boss's
spreadsheet column to display date format. :)
Robert G via webservertalk.com wrote:

>Hello, I was asked to run a query to retrieve some data for my boss. The
>query works just fine, however, my boss wants to import the data into his
>excel spreadsheet, and the date values as displayed in the result set in
>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
>all of the time stamp crap. I tried some CONVERT/CAST functions to no avai
l.
>This is sort of urgent, PLEASE HELP!!
>Thank you...
>RG
>|||Hell yes, thanks Brian!!! That worked like a freakin charm !! I did the
following:
convert(varchar(10), [MyDateField], 101)
I'd really like to thank everyone else who responded as well !!! I hope I
can return the favor some day.
RG
Brian Lawton wrote:
>If its left in a datetime data type, the time portion will always be
>returned. Instead, convert it to a string using something like the
>following,
>select convert(varchar(10), getdate(), 101)
>
>[quoted text clipped - 10 lines]|||I know what it is like to have a boss who is a total idiot, but you
might wantot get him/her a copy of ISO-8601 and ask why he is smarter
than the entire world. I would love to hear his/her reply :)
The kludge for the moron is a CONVERT() in a VIEW.|||Hey thanks for your input. That does what you said it would, but excel
doesn't recognize it as a date format. Also, I could've parsed it in the
query and eventually ended up with what I needed, but it would've required a
lot more work than Brian's solution - which took like two seconds.
But thanks so much, I still learned from your response.
RG
Jeremy Williams wrote:
>SQL Server does not have a "date only" data type, so you will need to jump
>through some hoops...
>Since your boss is importing the data into Excel, my first recommendation
>would be to leave the data as it is, and just format it appropriately in
>Excel. Excel can easily suppress the display of the time values, and it wil
l
>properly recognize the values as date values.
>If that is not an option, you could try a kludge such as "SELECT
>CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable",
>which will output a character column formatted as 'YYYYMMDD'. Depending on
>how you are making this output available to Excel, Excel may or may not
>determine that this is a Date column, and may not provide proper sorting
>functionality.
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1