Converting Existing Data into Unicode Format?

Hi Everybody,
We have an application which accepts transactions in local regional languages. The SQL Database is in 7.0 and OS is NT, with all latest SPs. We are planning to move it SQL 2000. During our testing we found, that Crystal Reports 9.0 is not able extract data which is in Korean/Chinese Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to Unicode data format. For New data, we can change the datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting -- Locale ID =3D 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description ---
---
--
Character Set =3D 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order =3D 52, nocase_iso
Case-insensitive dictionary sort order for use with several We
stern-European languages including English, French, and German
. Uses the ISO 8859-1 character set.
Characters, in Order ---
---
--
! " # $ % & ' ( ) * + , - . / : ; < =3D > ? @. [ \ ] ^ _ ` { | }
~ =A1 =A2 =A3 =A4 =A5 =A6 =A7 =A8 =A9 =AA =AB =AC =AD =AE =AF =B0 = =B1 =B2 =B3 =B4 =B5 =B6 =B7 =B8 =B9 =BA =BB =BC =BD =BE
=BF =D7 =F7 0 1 2 3 4 5 6 7 8 9 A=3Da =C0=3D=E0 =C1=3D=E1 =C2=3D=E2 = =C3=3D=E3 =C4=3D=E4 =C5=3D=E5 =C6=3D=E6 B=3Db C
=3Dc =C7=3D=E7 D=3Dd E=3De =C8=3D=E8 =C9=3D=E9 =CA=3D=EA =CB=3D=EB = F=3Df G=3Dg H=3Dh I=3Di =CC=3D=EC =CD=3D=ED =CE=3D=EE =CF=3D=EF J
=3Dj K=3Dk L=3Dl M=3Dm N=3Dn =D1=3D=F1 O=3Do =D2=3D=F2 =D3=3D=F3 = =D4=3D=F4 =D5=3D=F5 =D6=3D=F6 =D8=3D=F8 P=3Dp Q=3Dq R=3Dr S
=3Ds =DF T=3Dt U=3Du =D9=3D=F9 =DA=3D=FA =DB=3D=FB =DC=3D=FC V=3Dv = W=3Dw X=3Dx Y=3Dy =DD=3D=FD =FF Z=3Dz =D0=3D=F0 =DE=3D=FE
Can anybody help me, how to convert existing data (Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasumVasum,
Is this the same issue that is posted with the subject "UNICODE" in the
microsoft.public.sqlserver.programming group? If so, see the replies
there.
Thanks,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Content-Class: urn:content-classes:message
From: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Sender: "vasum" <vasu22_madhavan@.yahoo.com.sg>
Subject: Converting Existing Data into Unicode Format?
Date: Sat, 5 Jul 2003 21:10:21 -0700
Lines: 64
Message-ID: <034501c34374$848f5880$a501280a@.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Newsreader: Microsoft CDO for Windows 2000
Thread-Index: AcNDdISPYMUUigRbRNmnrEVJZrvb6Q==X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa09.phx.gbl
Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23000
NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
X-Tomcat-NG: microsoft.public.sqlserver.server
Hi Everybody,
We have an application which accepts transactions in local
regional languages. The SQL Database is in 7.0 and OS is
NT, with all latest SPs. We are planning to move it SQL
2000. During our testing we found, that Crystal Reports
9.0 is not able extract data which is in Korean/Chinese
Languages but Crystal Reports 8.0 is working fine.
So we planned to move all existing data in SQL 7.0 to
Unicode data format. For New data, we can change the
datatypes from varchar to nvarchar.
Existing Sort Order as follows,
Unicode data sorting
--
Locale ID = 1033
case insensitive, kana type insensitive, width insensitive
Sort Order Description
---
---
--
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit
character set.
Sort Order = 52, nocase_iso
Case-insensitive dictionary sort order for use with
several We
stern-European languages including English, French,
and German
. Uses the ISO 8859-1 character set.
Characters, in Order
---
---
--
! " # $ % & ' ( ) * + , - . / : ; < = > ? @. [ \ ] ^ _
` { | }
~ ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º »
¼ ½ ¾
¿ × ÷ 0 1 2 3 4 5 6 7 8 9 A=a À=à Á=á Â=â Ã=ã Ä=ä Å=å
Æ=æ B=b C
=c Ç=ç D=d E=e È=è É=é Ê=ê Ë=ë F=f G=g H=h I=i Ì=ì Í=í
Î=î Ï=ï J
=j K=k L=l M=m N=n Ñ=ñ O=o Ò=ò Ó=ó Ô=ô Õ=õ Ö=ö Ø=ø P=p
Q=q R=r S
=s ß T=t U=u Ù=ù Ú=ú Û=û Ü=ü V=v W=w X=x Y=y Ý=ý ÿ Z=z
Ð=ð Þ=þ
Can anybody help me, how to convert existing data
(Korean/chinese) to the Unicode format in SQL 2000.
tks in advance,
vasum

Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0',
Query=select * from Person'].
--
Message posted via http://www.sqlmonster.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via SQLMonster.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.
> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer..|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via SQLMonster.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...
> Thanks for the feedback. The setup we have is a DBF and a DBT ...
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase memo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0
',
Query=select * from Person'].
Message posted via http://www.droptable.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via droptable.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.

> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer
.
Table, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot locate the requested Xbase mem
o
file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaR
owset:
:GetRowset returned 0x80004005: ].
Hopefully this sheds some light on my issue?
Thanks again,
Michael
Cindy Winegarden wrote:[vbcol=seagreen]
>Hi Michael,
>First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
>FoxPro OLE DB data provider, downloadable from
>msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
>follows:
>-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC fil
e
>is present in the directory) use only the path to the directory and put
>double quotes around it if it has spaces. Something like '"C:\My
>Directory\"'
>-- If a DBC files is present the @.DataSrc value needs to point to the DBC
>file as below.
>EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_NORTHWIND', -- Your linked server name here
> @.srvproduct=N'Visual FoxPro 9', -- can be anything
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
>9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
>Then, you can query your tables like:
>Select * From YourLinkedServer...YourTable
>-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
>if your data source points to a DBC file just use the table name you want t
o
>select from.
>
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via droptable.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...

> Thanks for the feedback. The setup we have is a DBF and a DBT ...

> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase m
emo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Converting DateTime

Hello Everyone and thanks for your help in advance. I have an application that inserts a variety of values into a SQL Server database. Among the columns are three DateTime values. I have code working properly on my test server, but when I port the identical code to my production database, I get the following error:

Arithmetic overflow error converting expression to data type datetime. The statement has been terminated

When I remove any type of insert involving date, the application works. I have tried the date in various formats, for instance "09/12/2001" and "20010912", but still get the same error. Obviously, there must be differences in the SQL Servers, but I have never run into this problem before and the current server is running many applications involving dates. I haven't got a clue as to how to solve this problem. Any help is greatly appreciated. Thanks.hi kmcnet,
i had the same problem. but i was using query in code at that time but now i am using stored procedure and i can pass the date value now. no problem with that. anyway that was really really annoying thing. at one point i had used varchar to store date in database which i used to change to datetime datatype before displaying. but i think that wasn't a proper solution.sqlsql

Converting Data from Access 2000 to SQL Server 2000

Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.

Thanks.
HOW TO: Convert an Access Database to SQL Server

Converting Data

Good Day,
I am new SQL Server 2000 and have a dbase III application to convert. I am trying to import data from dbase III to a SQL table but I don't want <NULL> to show in the field. I am having trouble with the ALTER TABLE command.

Can some tell me how to import dbase III data with NULL or how I can alter the columns data to remove NULL?

Any suggestions would be greatly appreciated.

Regards,
RNettlesYou can import dbase III data many ways. It depends on the level of control that you need and coding for example. Here are some options to consider:

1. Use a Data Transformation Services package (Import/Export Wizard)
2. Define a linked server for the dbase III data and use heterogeneous queries from TSQL directly
3. Export data from dbase to text files and use bcp or BULK INSERT

As for changing the values from NULL, you can update them in SQL by choosing some appropriate default value. But the real question is if you want to model unknown value in your schema. This depends on the functionality that you require. You can use UPDATE to change NULL values:

update tbl
set col1 = 0
where col1 is null;

Converting Clipper .DBF tables to SQL Server 2000

My company is running a large Clipper application using Advantage Database
Server 6.2 as the backend. We have .DBF tables. We have 6 major retail
markets with over 200 tables in each folder (market).
I am looking for an easy (if that is possible) way to convert these .DBF
tables into SQL Server. In the query analyzer I have created 6 linked server
connections to each of the folders. I am able to do selects from any table
using the OpenQuery function. I have heard mention of DTS but I have no idea
what that is.
Can anyone suggest an easy way to convert these tables over? Also in some
cases its not going to be just a straight column to column data transfer. I
made need to combine data from 2 or 3 more tables on the Clipper side to mak
e
one column on the SQL Server side.
Any help would be appreciated.
David Cuffee
Sleep Train Inc.
Software DeveloperYes, you can use the DTS wizzard to import the dbf files without going
through Query Analyzer commands.
However, if you already are at the point of selecting from the tables
via OpenQuery, then you also have the option of selecting a rowset directly
into a SQL Server table using INSERT INTO. For example: "insert into
MySqlTable select a, b, c from MyDbfTable". If you know SQL, then this
method will make it easy to specify columns in proper order, combine,
transform, etc. as needed.
"David C via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:5298807695596@.webservertalk.com...
> My company is running a large Clipper application using Advantage Database
> Server 6.2 as the backend. We have .DBF tables. We have 6 major retail
> markets with over 200 tables in each folder (market).
> I am looking for an easy (if that is possible) way to convert these .DBF
> tables into SQL Server. In the query analyzer I have created 6 linked
> server
> connections to each of the folders. I am able to do selects from any table
> using the OpenQuery function. I have heard mention of DTS but I have no
> idea
> what that is.
> Can anyone suggest an easy way to convert these tables over? Also in some
> cases its not going to be just a straight column to column data transfer.
> I
> made need to combine data from 2 or 3 more tables on the Clipper side to
> make
> one column on the SQL Server side.
> Any help would be appreciated.
> David Cuffee
> Sleep Train Inc.
> Software Developer|||Thank JT. My brain must have not been thinking. Using the INSERT method with
the SELECT is very good way to do this. now that I have the OpenQuery workin
g.
Thank you very much.
David
JT wrote:
> Yes, you can use the DTS wizzard to import the dbf files without going
>through Query Analyzer commands.
> However, if you already are at the point of selecting from the tables
>via OpenQuery, then you also have the option of selecting a rowset directly
>into a SQL Server table using INSERT INTO. For example: "insert into
>MySqlTable select a, b, c from MyDbfTable". If you know SQL, then this
>method will make it easy to specify columns in proper order, combine,
>transform, etc. as needed.
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200508/1

converting binary data to another data type

I have a client application written in C++ to takes an array of doubles and
stores it into a SQL Server 2000 database as an image data type.
We just upgraded to Visual Studio 2005 and SQL Server 2005.
Can the Reporting Services take this image data and convert it to an array
of doubles so that it can be displayed using Reporting Services?
Thanks,
GloriaGloria (Gloria@.discussions.microsoft.com) writes:
> I have a client application written in C++ to takes an array of doubles
> and stores it into a SQL Server 2000 database as an image data type.
> We just upgraded to Visual Studio 2005 and SQL Server 2005.
> Can the Reporting Services take this image data and convert it to an array
> of doubles so that it can be displayed using Reporting Services?
I don't know Reporting Services, so I canot answer the question with any
certainty, but my gut feeling is that you would have to call some piece
of code to unpack that array. Tip: there is a Reporting Services newsgroup,
microsoft.public.sqlserver.reportingsvcs.
The main reason I post, is that I can't refrain from making the comment
table design appears a bit unorthodox to me. Or to put it more bluntly, a
serious violation of first normal form since it includs a repearing
group. The normal way of storing the data would be have a subtable,
and store one float value on each row.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Converting a MySQL multiple column subselect to SQL Server

I am adding SQL Server support to an application that currently uses
MySQL 4.1.
I have a table that looks like this in SQL Server 8.0:
CREATE TABLE dbo.sales_estimates
(
ID int NOT NULL,
YearMonth datetime NULL,
CountryCode char(3) NULL,
StoreCode int NULL,
SalesEstimate decimal(18, 0) NULL,
UserID int NULL,
DateTimeStamp datetime NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.sales_estimates ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
ID
) ON [PRIMARY]
GO
It contains multiple sales estimates for stores, eg different users can
enter their own SalesEstimate for each store's monthly sales.
I want to select the most recent sales estimate for each store for a
given month.
In MySQL 4.1 I can do this by with the folling nested selects:
select YearMonth, CountryCode, StoreCode, SalesEstimate from
store_estimates
where (YearMonth,CountryCode, StoreCode, DateTimeStamp)
in (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode)
I'd like to write a similar statement for SQL Server (version 8.0) if
this is possible, but it appears that I can't have multiple rows in
subselects. Ideally I'd like to find a simpler query that works for
both DBs.SQL Server doesn't support the syntax, I believe it's referred
to as 'row constructors'
You can do this instead
select a.YearMonth, a.CountryCode, a.StoreCode, a.SalesEstimate
from store_estimates a
inner join (select YearMonth, State, StoreCode , max(DateTimeStamp)
from store_estimates
where YearMonth ='2006-01-1'
group by YearMonth, State, StoreCode) b(YearMonth,CountryCode,
StoreCode, DateTimeStamp)
on a.YearMonth=b.YearMonth and a.CountryCode=b.CountryCode
and a.StoreCode=b.StoreCode and a.DateTimeStamp=b.DateTimeStamp
You can also achieve the same results with an EXISTS clause.

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g
.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:

> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>sqlsql

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:
> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>

Converting

Hi, can you help me converting some queries from ASP application Oracle to
SQL SERVER 2000?
example:
1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
hh24:mi:ss')"
2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
How can it be converted to SQL Server?
Hi
You can use the convert function to format dates as text or cast to cast
between datatypes. If your datestring is in a "safe" format e.g CCYYMMDD or
'CCYY-MM-DDTHH:MM:SS.NNN then you could let it do an implicit conversion e.g.
and p.DATACAD < '20071120'
If you specify a date with no time it will default to midnight 00:00:00.000
Datetime granularity is 3.33 milliseconds and smalldatetime is accurate to 1
minute.
You can try this out using Query Analyser or Management studio e.g.
SELECT CAST('11:31:08.000' AS datetime),
CAST('11:31:08.000' AS smalldatetime),
CAST('20071120' AS datetime),
CAST('20071120' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS smalldatetime),
CAST('2007-11-20T23:59:59.995' AS datetime),
CAST('2007-11-20T23:59:59.990' AS datetime),
CAST('2007-11-20T23:59:59.992' AS datetime),
CAST('2007-11-20T23:59:59.993' AS datetime),
CAST('2007-11-20T23:59:59.994' AS datetime),
CAST('2007-11-20T23:59:59.996' AS datetime),
CAST('2007-11-20T23:59:59.997' AS datetime),
CAST('2007-11-20T23:59:59.998' AS datetime),
CAST('2007-11-20T23:59:59.999' AS datetime)
"Paulo" wrote:

> Hi, can you help me converting some queries from ASP application Oracle to
> SQL SERVER 2000?
> example:
> 1) " and p.DATACAD <= to_date('"& BDATACAD2 &" 23:59:59','dd/mm/yyyy
> hh24:mi:ss')"
> 2) " WHERE TO_CHAR(DATA_HORAS,'MM/YYYY') ='"&SEL_PER&"'"
> How can it be converted to SQL Server?
>
>

Convert User Defined Function

We have a User Function that gets called from Access. We now have a need to
use that same User Function in a VB application. Is there a way to convert
the User Function to a Stored Procedure?
I'm pretty green about this, so any help is appreciated.
tia,
--
JMorrellWhy? IIRC, you can call a function from VB (we do that occasionally from C#
and VB.Net).
Anyway, we can't help you convert the function to a procedure unless you
show us what the function does (!).
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> We have a User Function that gets called from Access. We now have a need
to
> use that same User Function in a VB application. Is there a way to
convert
> the User Function to a Stored Procedure?
> I'm pretty green about this, so any help is appreciated.
> tia,
> --
> JMorrell|||Thanks for the reply. We're needing the results of the function within vb.
The function is as follows:
CREATE FUNCTION ufn_GetSubtree
(
@.supervisorid AS int
)
RETURNS @.tree table
(
employeeid varchar(6) NOT NULL,
supervisorid varchar(6) NULL,
lname varchar(25) NOT NULL,
fname varchar(25) not null,
term int not null,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT employeeid, supervisorid, lname, fname, term,
@.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
FROM tblemp
WHERE term = 0
and employeeid = @.supervisorid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
@.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
FROM tblEmp AS E JOIN @.tree AS T
ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
END
RETURN
END
--
I'm not sure how to go about using the function as is when in vb. Is there
a source for help in this?
tiaa,
JMorrell
"Aaron [SQL Server MVP]" wrote:

> Why? IIRC, you can call a function from VB (we do that occasionally from
C#
> and VB.Net).
> Anyway, we can't help you convert the function to a procedure unless you
> show us what the function does (!).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
> news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> to
> convert
>
>|||(a) I suggest CREATE FUNCTION dbo.ufn_GetSubTree
(b) this would be easy to convert to a stored procedure (though I'm sure
Celko will want to re-educate you on the right way to handle trees, nested
sets and heirarchies etc.).
(c) try this in VB:
dim conn as adodb.connection
conn.open "your connection string"
dim rs as adodb.recordset
rs.open "SELECT * FROM dbo.ufn_GetSubTree"
...
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:FECA6975-BE71-4CAB-8107-67598D6C921D@.microsoft.com...
> Thanks for the reply. We're needing the results of the function within
vb.
> The function is as follows:
> CREATE FUNCTION ufn_GetSubtree
> (
> @.supervisorid AS int
> )
> RETURNS @.tree table
> (
> employeeid varchar(6) NOT NULL,
> supervisorid varchar(6) NULL,
> lname varchar(25) NOT NULL,
> fname varchar(25) not null,
> term int not null,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT employeeid, supervisorid, lname, fname, term,
> @.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
> FROM tblemp
> WHERE term = 0
> and employeeid = @.supervisorid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
> @.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
> FROM tblEmp AS E JOIN @.tree AS T
> ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> --
> I'm not sure how to go about using the function as is when in vb. Is
there[vbcol=seagreen]
> a source for help in this?
> tiaa,
> JMorrell
> "Aaron [SQL Server MVP]" wrote:
>
from C#[vbcol=seagreen]
need[vbcol=seagreen]

Convert User Defined Function

We have a User Function that gets called from Access. We now have a need to
use that same User Function in a VB application. Is there a way to convert
the User Function to a Stored Procedure?
I'm pretty green about this, so any help is appreciated.
tia,
--
JMorrellWhy? IIRC, you can call a function from VB (we do that occasionally from C#
and VB.Net).
Anyway, we can't help you convert the function to a procedure unless you
show us what the function does (!).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> We have a User Function that gets called from Access. We now have a need
to
> use that same User Function in a VB application. Is there a way to
convert
> the User Function to a Stored Procedure?
> I'm pretty green about this, so any help is appreciated.
> tia,
> --
> JMorrell|||Thanks for the reply. We're needing the results of the function within vb.
The function is as follows:
CREATE FUNCTION ufn_GetSubtree
(
@.supervisorid AS int
)
RETURNS @.tree table
(
employeeid varchar(6) NOT NULL,
supervisorid varchar(6) NULL,
lname varchar(25) NOT NULL,
fname varchar(25) not null,
term int not null,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT employeeid, supervisorid, lname, fname, term,
@.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
FROM tblemp
WHERE term = 0
and employeeid = @.supervisorid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
@.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
FROM tblEmp AS E JOIN @.tree AS T
ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
END
RETURN
END
--
I'm not sure how to go about using the function as is when in vb. Is there
a source for help in this?
tiaa,
JMorrell
"Aaron [SQL Server MVP]" wrote:
> Why? IIRC, you can call a function from VB (we do that occasionally from C#
> and VB.Net).
> Anyway, we can't help you convert the function to a procedure unless you
> show us what the function does (!).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
> news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> > We have a User Function that gets called from Access. We now have a need
> to
> > use that same User Function in a VB application. Is there a way to
> convert
> > the User Function to a Stored Procedure?
> >
> > I'm pretty green about this, so any help is appreciated.
> >
> > tia,
> > --
> > JMorrell
>
>|||(a) I suggest CREATE FUNCTION dbo.ufn_GetSubTree
(b) this would be easy to convert to a stored procedure (though I'm sure
Celko will want to re-educate you on the right way to handle trees, nested
sets and heirarchies etc.).
(c) try this in VB:
dim conn as adodb.connection
conn.open "your connection string"
dim rs as adodb.recordset
rs.open "SELECT * FROM dbo.ufn_GetSubTree"
...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:FECA6975-BE71-4CAB-8107-67598D6C921D@.microsoft.com...
> Thanks for the reply. We're needing the results of the function within
vb.
> The function is as follows:
> CREATE FUNCTION ufn_GetSubtree
> (
> @.supervisorid AS int
> )
> RETURNS @.tree table
> (
> employeeid varchar(6) NOT NULL,
> supervisorid varchar(6) NULL,
> lname varchar(25) NOT NULL,
> fname varchar(25) not null,
> term int not null,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT employeeid, supervisorid, lname, fname, term,
> @.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
> FROM tblemp
> WHERE term = 0
> and employeeid = @.supervisorid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
> @.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
> FROM tblEmp AS E JOIN @.tree AS T
> ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> --
> I'm not sure how to go about using the function as is when in vb. Is
there
> a source for help in this?
> tiaa,
> JMorrell
> "Aaron [SQL Server MVP]" wrote:
> > Why? IIRC, you can call a function from VB (we do that occasionally
from C#
> > and VB.Net).
> >
> > Anyway, we can't help you convert the function to a procedure unless you
> > show us what the function does (!).
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
> > news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> > > We have a User Function that gets called from Access. We now have a
need
> > to
> > > use that same User Function in a VB application. Is there a way to
> > convert
> > > the User Function to a Stored Procedure?
> > >
> > > I'm pretty green about this, so any help is appreciated.
> > >
> > > tia,
> > > --
> > > JMorrell
> >
> >
> >

Convert User Defined Function

We have a User Function that gets called from Access. We now have a need to
use that same User Function in a VB application. Is there a way to convert
the User Function to a Stored Procedure?
I'm pretty green about this, so any help is appreciated.
tia,
JMorrell
Why? IIRC, you can call a function from VB (we do that occasionally from C#
and VB.Net).
Anyway, we can't help you convert the function to a procedure unless you
show us what the function does (!).
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> We have a User Function that gets called from Access. We now have a need
to
> use that same User Function in a VB application. Is there a way to
convert
> the User Function to a Stored Procedure?
> I'm pretty green about this, so any help is appreciated.
> tia,
> --
> JMorrell
|||Thanks for the reply. We're needing the results of the function within vb.
The function is as follows:
CREATE FUNCTION ufn_GetSubtree
(
@.supervisorid AS int
)
RETURNS @.tree table
(
employeeid varchar(6)NOT NULL,
supervisorid varchar(6)NULL,
lname varchar(25) NOT NULL,
fname varchar(25) not null,
term int not null,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT employeeid, supervisorid, lname, fname, term,
@.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
FROM tblemp
WHERE term = 0
and employeeid = @.supervisorid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
@.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
FROM tblEmp AS E JOIN @.tree AS T
ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
END
RETURN
END
I'm not sure how to go about using the function as is when in vb. Is there
a source for help in this?
tiaa,
JMorrell
"Aaron [SQL Server MVP]" wrote:

> Why? IIRC, you can call a function from VB (we do that occasionally from C#
> and VB.Net).
> Anyway, we can't help you convert the function to a procedure unless you
> show us what the function does (!).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
> news:DE9B9D49-E776-4140-AFAA-98E606B4A2DC@.microsoft.com...
> to
> convert
>
>
|||(a) I suggest CREATE FUNCTION dbo.ufn_GetSubTree
(b) this would be easy to convert to a stored procedure (though I'm sure
Celko will want to re-educate you on the right way to handle trees, nested
sets and heirarchies etc.).
(c) try this in VB:
dim conn as adodb.connection
conn.open "your connection string"
dim rs as adodb.recordset
rs.open "SELECT * FROM dbo.ufn_GetSubTree"
...
http://www.aspfaq.com/
(Reverse address to reply.)
"JMorrell" <JMorrell@.discussions.microsoft.com> wrote in message
news:FECA6975-BE71-4CAB-8107-67598D6C921D@.microsoft.com...
> Thanks for the reply. We're needing the results of the function within
vb.
> The function is as follows:
> CREATE FUNCTION ufn_GetSubtree
> (
> @.supervisorid AS int
> )
> RETURNS @.tree table
> (
> employeeid varchar(6) NOT NULL,
> supervisorid varchar(6) NULL,
> lname varchar(25) NOT NULL,
> fname varchar(25) not null,
> term int not null,
> lvl int NOT NULL,
> path varchar(900) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.lvl AS int, @.path AS varchar(900)
> SELECT @.lvl = 0, @.path = '.'
> INSERT INTO @.tree
> SELECT employeeid, supervisorid, lname, fname, term,
> @.lvl, '.' + CAST(employeeid AS varchar(10)) + '.'
> FROM tblemp
> WHERE term = 0
> and employeeid = @.supervisorid
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> SET @.lvl = @.lvl + 1
> INSERT INTO @.tree
> SELECT E.employeeid, E.supervisorid, E.lname, E.fname, E.term,
> @.lvl, T.path + CAST(E.employeeid AS varchar(10)) + '.'
> FROM tblEmp AS E JOIN @.tree AS T
> ON E.supervisorid = T.employeeid AND T.lvl = @.lvl - 1
> END
> RETURN
> END
> --
> I'm not sure how to go about using the function as is when in vb. Is
there[vbcol=seagreen]
> a source for help in this?
> tiaa,
> JMorrell
> "Aaron [SQL Server MVP]" wrote:
from C#[vbcol=seagreen]
need[vbcol=seagreen]

Convert to Date

I have a variable that I am passing to a stored procedure from a
propritery software application. The variable it passes is the number
of days that have passed since 1/1/1900. I cannot seem to locate a
convert statement that is applicable. Any guidance would be greatly
appreciated.

ThanksDATEADD(DAY,@.var,'19000101')

or

DATEADD(DAY,@.var,'18991231')

depending on whether 1900-01-01 is represented by 0 or 1.

--
David Portas
SQL Server MVP
--

convert SQL 2005 back to SQL 2000

Dear Sir,
Our new test environment running MS SQL 2005. We have finished an
application and need to migrate that to a live server which run SQL 2000.
I have tried enough but with no luck. How can I convert our new SQL 2005
back to 2000 server? The new database contain table schema and quiet some
initial data already. How should I do this?
regards,
Guoqi Zheng
http://www.ureader.com"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
There's no real easy way to do this.
Best bet is to script out the schema, bulk copy out the data and rebuild on
the new box.
> regards,
> Guoqi Zheng
> http://www.ureader.com|||You have to script out all of the objects and then transfer the data using
either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
it can NOT be downgraded.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||It can be downgraded by setting the DB options to 80 compatibility level IF
the DB doesn't use any of the newer 2005 features.
However, I think it is a mistake to not just upgrade the other server to
2005. Upgrading takes so much work, and if the DB is already working in
2005, then why go through all the hassle downgrading and upgrading when that
same fundamental expense can be borne one time to just pay for the 2005
license.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> You have to script out all of the objects and then transfer the data using
> either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
> it can NOT be downgraded.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
>> Dear Sir,
>> Our new test environment running MS SQL 2005. We have finished an
>> application and need to migrate that to a live server which run SQL 2000.
>> I have tried enough but with no luck. How can I convert our new SQL 2005
>> back to 2000 server? The new database contain table schema and quiet some
>> initial data already. How should I do this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>|||Changing the database compatibility level will not allow Guoqi Zheng to go
back to SQL 2000. The on-disk database format will still be SQL 2005 and
will not be recognized by older versions of SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AFN" <replywithingroup@.notreal.com> wrote in message
news:EL90g.3630$3W1.59@.tornado.socal.rr.com...
> It can be downgraded by setting the DB options to 80 compatibility level
> IF the DB doesn't use any of the newer 2005 features.
> However, I think it is a mistake to not just upgrade the other server to
> 2005. Upgrading takes so much work, and if the DB is already working in
> 2005, then why go through all the hassle downgrading and upgrading when
> that same fundamental expense can be borne one time to just pay for the
> 2005 license.
>
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>> You have to script out all of the objects and then transfer the data
>> using either BCP, DTS, or SSIS. A 2005 database can not be loaded into
>> 2000 and it can NOT be downgraded.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>>
>> "guoqi zheng" <no@.sorry.com> wrote in message
>> news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
>> Dear Sir,
>> Our new test environment running MS SQL 2005. We have finished an
>> application and need to migrate that to a live server which run SQL
>> 2000.
>> I have tried enough but with no luck. How can I convert our new SQL 2005
>> back to 2000 server? The new database contain table schema and quiet
>> some
>> initial data already. How should I do this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>>
>|||If you're not using any SQL 2005 specific features, you can use
Transactional Replication to keep a warm SQL 2000 fallback option open.
Regards,
Greg Linwood
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com

convert from db2 to sqlserver

I have an application running in db2 udb v7.2 fp7. there are 76 tables for this app. i want to convert all these tables over to sqlserver 2000. what is the easiest method to get the data from db2 to sqlserver??It depends on easiest for who...

Is the schema complex or simple (foreign keys, constraints, etc)? What is the DB2 device topography? Are there 100 rows of data, or 100 billion?

Too many questions!

-PatP|||I am going to reinstall the app in sql server. the files are not large a couple thousand rows is the largest. i just want to move the data. the ri is good, as they sit in db2 currently.|||Try this tool :

http://www.swissql.com/products/datamigration/data-migration.html|||There is actually a tool in the SQL Server Resource Kit that does a pretty good job. You might want to check it out.

-PatP|||Why would anyone want to move away from DB2?

Convert from .sdf file to .mdb file

Hellow

I have an application that builds a .sdf Sqlq Server Compact Edition file I want to view the file on a desktop computer How can I do it ?

If there is no simple way to do this how can I write a C# application to convert the .sdf file to .mdb file so I will be able to use Microsoft Access to view it.

Thanks

Ofer

You can connect to your SQL Compact Edition database using SQL Server 2005 Management Studio or Visual Studio 2005 SP1. If you need to convert from .SDF to MS Access (or vice-versa), check out the Data Port Wizard tool from one of the other MVPs, JP Figueroa:

http://www.primeworks-mobile.com/

Regards,

Darren Shaffer

|||

You can also try SQL Server Express edition if you do not have SQL Server 2005.

Cheers

Jeba