Converting DTS to Stored procedure

Hi,

I created a DTS package for importing a textfile, parse the data and insert it into some tables.

I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...

Now I have to convert the package into a stored procedure instead.
SQL Querys are no problem but the importing of the textfile is.
How do I do it?

First step is to import only row 0 to a table, second step is to import row 1 -> n

Any examples would be nice... :-)bcp or bulk insert...

what's row 0?

Probably a header...

Can you create a "work" table for your own use?

How many rows are we talking about?

Where is the data coming from (it's Access isn't...grrrrrr)

What do you mean by parse (SUBSTRING)?

Does your dog have fleas?

Why is the sky blue?

What's the name of your dba...(just kidding)

Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...|||Whats bcp?

The file contains a header in the first row and then tab separated data...

I have a rawtable that it is imported to now by DTS.

It's about 20000 rows, textfile...

By parsing i mean several queries that translates the data from the rawtable into other tables.

And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...

The sky is blue beacuse God have his blue underwear on...

My dba is named Sven.

--

I have tried to use xp_cmdshell but I'm not allowed too...

Seems like I have to do the import in my client.. written in C++.|||Hi,

You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine ... Hope this helps.

CREATE PROC dbo.DTSExecutePKG
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)
-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT

IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'

IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
RETURN 1
END

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'

IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG

IF @.hr <> 0
BEGIN
RETURN 1
END

GO|||Got a:

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
*** Create Package object failed

--

Seems like I have no permission to run sp_OACreate either...

Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.

Converting Date-Time to Date Conundrum

I need to be able to convert a Date-Time Field to a Date Fied in my SQL
Tables.
Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
but because it converts the date value to a string I can say goodbye to
localization.... any ideas on how to convert the field but still allow
localization.Try this function...
CREATE FUNCTION [dbo].[fnRemoveTimeFromDateTime] (@.InputDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.OUTPUT AS SMALLDATETIME
SET @.OUTPUT = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.InputDate)))
RETURN @.OUTPUT
END
"SAcanuck" wrote:
> I need to be able to convert a Date-Time Field to a Date Fied in my SQL
> Tables.
> Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
> but because it converts the date value to a string I can say goodbye to
> localization.... any ideas on how to convert the field but still allow
> localization.

converting datetime int

I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.

select iorg_name as org, ref_num as [ticketnum], c_first_name as
[firstname], c_last_name as [lastname], sym as type, [description] as
summary, status, dateadd(s,open_date,'12/31/1969 08:00:00pm') as
opened, dateadd(s,last_mod_dt,'12/31/1969 08:00:00pm') as irt,
dateadd(s,close_date,'12/31/1969 08:00:00pm') as closed from
AHD.dbo.HDreports reportview WHERE reportview.open_date >= 1080882000
AND reportview.open_date <= 1081227599.

The result shows correctly with those records that are in daylight
saving time. Those records in standard time show 1 hour behind.

Does anyone know how to make this query correctly display the data in
properly?js (androidsun@.yahoo.com) writes:
> I have tables with columns that stores datetime data in int format on
> SQL server 2000. For example, the datetime for '4/5/2004
> 00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
> 1081180799. I need to generate reports that display datetime columns
> in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
> my query statment.
>...
> The result shows correctly with those records that are in daylight
> saving time. Those records in standard time show 1 hour behind.
> Does anyone know how to make this query correctly display the data in
> properly?

That was a very odd way of storing dates, and probably not the best one.
Apparently this is some variation of Unix, where time is counted as number
of seconds since 1970-01-01 00:00:00, except that here the staring point
is 1969-12-30 20:00:00.

SQL Server is not timezone aware, so you should not expect to be able
to get fully accurate results. You are probably best of getting the
integer value to the client, and try the Windows functions for date
and time. They are likely to work out better.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 ACCESS and EXCEL data to SQL

Hi,

I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.
How may this be done?
I am able to create a Data Component with the ACCESS mdb file.

Likewise, how may I convert EXCEL data to SQL2005 table?
Thanks.

David

Access should have an upsizing wizard look for it, excel try the link and code below. Hope this helps.

http://www.sqlis.com/

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||

Thank you very much for the reply!
Moving along, I tried do an "upsize" from ACCESS to SQL, but I was not able to create a DSN for the SQL server. (Could not specify the "server"; I have tried using "local" and it didn't work.)
I am using SQL 2005 express - not the full version.
Can anyone tell me if this is due to limitation of the express version?
i.e. is it not possible to create a DSN to SQL 2005 Express ?

David

|||

You don't need DSN see if you can upsize it into SQL Server 2000. Then you can backup and restore it. Try the link below download and install the SQL Server 2005 Express Manager it, there must be a way to upsize. BTW Microsoft bought a migration tool company I forgot all about it you can download from the company site and test drive or use the link below to sign up for a Microsoft beta program. Hope this helps.

http://www.microsoft.com/sql/migration/default.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

sqlsql

Converting a type into another one

Hello,

I've got two tables. An old one an a new one. Called "tbl_Filme" and "tbl_Filme2". The differents are that the data types are a little bit smaller as in the old one.

So I've got a column wich should contains a datetime. Unfortunattley the datatype is just a normal date and not "smalldatetime" (the "tbl_Filme" was created with MS Access 2003). So I get an error message:

1> INSERT INTO tbl_Filme2 (Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge)
2> SELECT Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge
3> FROM tbl_Filme
4> go
Meldung '298', Ebene '16', Status '1', Server 'PREDATOR\SQLEXPRESS', Zeile 1
'The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.'

My goal is that I can copy the old table in the new table. If I solved that problem maybe you can help me with that problem, too:

I've programmed a programm for accessing the table in VB 2005. I've got theire a GridView where I can change the cells and a button to sync. the changed values with the SQL Database (2005). But finished with the update, the values aren't updated.

I thought this might be an error of my programming but first I tried to use Access for this (created a new Access Project *.adp). But there was an error message "Could update the RecordSet". Is this beacause of the SQL Server 2005 (maby I must wait untill an update, because Access 2003 is older then SQL 2005?)?

Thanks in advance.

OK, first problem. Rather than doing a automatic convertion between the old and the new one, I would check these incompabilities and solve them. Appearantly the datetime are two big / or too small to be reflected in a smalldatetime. So knowing that smalldatetime is defined in the scope of

from January 1, 1900, through June 6, 2079

You should use the query to identify these "old" ones and UPDATE them to a acceptable format for datetime (as above).

Then there should be no more problem with importing them. If you need the dates prior to 1900 or after 2079 you have to use the datetime data type.

Second problem: ""Could update the RecordSet"." This doens′t sound lkike a problem :-). Assuming that the error message is ""Could NOT update the RecordSet".", I would investigate the command that are passed to the Provider. Did you call the UPDATE method ? There has to be an inner exception which should explain the error message a bit more in detail. This would be more helpful to solve your problem.

HTH, jens Suessmeyer.

|||

Hello,

thank you for your answer. The reason why I wanted to choose the datetime is, that I want to write a time value. Is their any data type for only time without a date in it? But I will try your suggestion.

The second problem occured in Microsoft Access. Well, I startet a new project with a new connection to the SQL Server (with the user-ID "sa").

Then I saw all the tables, which were in the database. But I can only read, I cannot change anything. If I try to change a value the error "Couldn't update the RecordSet" occures.

|||

"Is their any data type for only time without a date in it"

-No.

" But I can only read, I cannot change anything"

Create a primary key in the access enviroment on the tables, that should help.

HTH, jens Suessmeyer.

|||

Unfortunattely their is a message (while opening the window where I can edit the data types of the columes) which sais that it is not possible to save the changes because the used SQL Server is newer than the Access version.

Edit:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=196509&SiteID=1

The problem is written their, too. No useable way to solve the problem. Maybe I will wait until the Office 2007.

I have got one question contains the "SQL Server Management Studio". Where do I get this GUI for the SQL 2005. I don't find it.

Edit:

Sorry, found it. :-)

Edit:

Something is strage. After I "played" a little bit with the SQL Server Management Studio" I tried it again with the Microsoft Office Access 2003 and now it works!

Converting a SQL Database to MSDE

Ok, I've got the tables created, the proc's created (and they work) and
everything else is done on the backend. My question is how to do I get
everything I've created in the database using a full version of SQL into an
MSDE database that I can distribute to my userbase so they can play. Someone
said all I need to do is a backup and restore -- but I'm not sure how it
would be done. Can anyone help?Backup and Restore is all you need.
Backup the database to a flat file
Restore onto MSDE using the flat file.
I would use SQL Enterprise Manager...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MisterB" <MisterB@.discussions.microsoft.com> wrote in message
news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> Ok, I've got the tables created, the proc's created (and they work) and
> everything else is done on the backend. My question is how to do I get
> everything I've created in the database using a full version of SQL into
an
> MSDE database that I can distribute to my userbase so they can play.
Someone
> said all I need to do is a backup and restore -- but I'm not sure how it
> would be done. Can anyone help?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23V6IM1p7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Backup and Restore is all you need.
> Backup the database to a flat file
> Restore onto MSDE using the flat file.
> I would use SQL Enterprise Manager...
Note that the datafile will need to be less than 2 gigabytes.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MisterB" <MisterB@.discussions.microsoft.com> wrote in message
> news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> > Ok, I've got the tables created, the proc's created (and they work) and
> > everything else is done on the backend. My question is how to do I get
> > everything I've created in the database using a full version of SQL into
> an
> > MSDE database that I can distribute to my userbase so they can play.
> Someone
> > said all I need to do is a backup and restore -- but I'm not sure how it
> > would be done. Can anyone help?
>|||How do you reattach the file(s) you backed up to the MSDE?
"Wayne Snyder" wrote:
> Backup and Restore is all you need.
> Backup the database to a flat file
> Restore onto MSDE using the flat file.
> I would use SQL Enterprise Manager...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MisterB" <MisterB@.discussions.microsoft.com> wrote in message
> news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> > Ok, I've got the tables created, the proc's created (and they work) and
> > everything else is done on the backend. My question is how to do I get
> > everything I've created in the database using a full version of SQL into
> an
> > MSDE database that I can distribute to my userbase so they can play.
> Someone
> > said all I need to do is a backup and restore -- but I'm not sure how it
> > would be done. Can anyone help?
>
>

Converting a SQL Database to MSDE

Ok, I've got the tables created, the proc's created (and they work) and
everything else is done on the backend. My question is how to do I get
everything I've created in the database using a full version of SQL into an
MSDE database that I can distribute to my userbase so they can play. Someon
e
said all I need to do is a backup and restore -- but I'm not sure how it
would be done. Can anyone help?Backup and Restore is all you need.
Backup the database to a flat file
Restore onto MSDE using the flat file.
I would use SQL Enterprise Manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MisterB" <MisterB@.discussions.microsoft.com> wrote in message
news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> Ok, I've got the tables created, the proc's created (and they work) and
> everything else is done on the backend. My question is how to do I get
> everything I've created in the database using a full version of SQL into
an
> MSDE database that I can distribute to my userbase so they can play.
Someone
> said all I need to do is a backup and restore -- but I'm not sure how it
> would be done. Can anyone help?|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23V6IM1p7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Backup and Restore is all you need.
> Backup the database to a flat file
> Restore onto MSDE using the flat file.
> I would use SQL Enterprise Manager...
Note that the datafile will need to be less than 2 gigabytes.

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MisterB" <MisterB@.discussions.microsoft.com> wrote in message
> news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> an
> Someone
>|||How do you reattach the file(s) you backed up to the MSDE?
"Wayne Snyder" wrote:

> Backup and Restore is all you need.
> Backup the database to a flat file
> Restore onto MSDE using the flat file.
> I would use SQL Enterprise Manager...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MisterB" <MisterB@.discussions.microsoft.com> wrote in message
> news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> an
> Someone
>
>

Converting a SQL Database to MSDE

Ok, I've got the tables created, the proc's created (and they work) and
everything else is done on the backend. My question is how to do I get
everything I've created in the database using a full version of SQL into an
MSDE database that I can distribute to my userbase so they can play. Someone
said all I need to do is a backup and restore -- but I'm not sure how it
would be done. Can anyone help?
Backup and Restore is all you need.
Backup the database to a flat file
Restore onto MSDE using the flat file.
I would use SQL Enterprise Manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MisterB" <MisterB@.discussions.microsoft.com> wrote in message
news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> Ok, I've got the tables created, the proc's created (and they work) and
> everything else is done on the backend. My question is how to do I get
> everything I've created in the database using a full version of SQL into
an
> MSDE database that I can distribute to my userbase so they can play.
Someone
> said all I need to do is a backup and restore -- but I'm not sure how it
> would be done. Can anyone help?
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23V6IM1p7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Backup and Restore is all you need.
> Backup the database to a flat file
> Restore onto MSDE using the flat file.
> I would use SQL Enterprise Manager...
Note that the datafile will need to be less than 2 gigabytes.

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MisterB" <MisterB@.discussions.microsoft.com> wrote in message
> news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> an
> Someone
>
|||How do you reattach the file(s) you backed up to the MSDE?
"Wayne Snyder" wrote:

> Backup and Restore is all you need.
> Backup the database to a flat file
> Restore onto MSDE using the flat file.
> I would use SQL Enterprise Manager...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MisterB" <MisterB@.discussions.microsoft.com> wrote in message
> news:65AA739F-3DA8-4CEF-813D-E8E4AFD95DAD@.microsoft.com...
> an
> Someone
>
>

Converting a file into multiple tables

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)

It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.

But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.

--
Wes Groleau

If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:K7x6h.10720$l%2.2462@.trnddc05...

Quote:

Originally Posted by

This file format has multiple levels (X12).
One level could have one or more instances of the
next level contained within it. Kind of like XML,
except that some sections have no end tags, and the
ones that do have end tags actually have a _different_
tag for the end. (ISA ...IEA or GS ... GE)
>
It's easy enough to read a line at a time, see what
type it is, and insert its parts into the appropriate
table. Keeping track of the keys of the parent level
for relationships.
>
But I'm wandering whether there's some (not impossibly
complex) more efficient method with SQL and/or DTS.
>
--
Wes Groleau
>
If you put garbage in a computer nothing comes out but garbage.
But this garbage, having passed through a very expensive machine,
is somehow ennobled and none dare criticize it.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Take a look at SQLXML Bulk Load
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?|||You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:PCH6h.708$Fg.683@.tornado.socal.rr.com...

Quote:

Originally Posted by

Dan Guzman wrote:
>

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

|||Dan Guzman wrote:

Quote:

Originally Posted by

You're right about the EDI to XML translator - I misread Wes's post. of
course, SQLXML can't consume EDI directly.

Of course, I could easily write something to
convert it to XML that SQL server can read.
But then I could just as easily convert it
directly into INSERT statements. I'm just
wondering whether DTS or anything else is faster.

I already have a tool that loads the entire
file into an array of lines and provides various
query functions for other apps to access it.

But I'd like to put multiple files in the database
instead of having to select one file at a time.

By the way, whatever the technique is, it could
probably also handle GEDCOM files.

--
Wes Groleau

There ain't no right wing,
there ain't no left wing.
There's only you and me and we just disagree.
(apologies to Jim Krueger)|||Ed Murphy wrote:

Quote:

Originally Posted by

Dan Guzman wrote:

Quote:

Originally Posted by

>Take a look at SQLXML Bulk Load
>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>
He'd also need an EDI to XML translator. (I recognize those
damnable start/end tags.) Google indicates that several
translators exist; anyone want to offer a recommendation?

I think I figured out a solution (haven't tried it yet).

Comments on this idea welcome (I'm kind of new to SQL):

The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1

So if a file has (data elem delims changed to spaces)
....
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
....
then the first pass through the file could create rows

.... A B C X Y Z ...
.... A B C 1 2 3 ...
.... D E F P Q R ...
.... D E F 5 6 7 ...

Next, one query could SELECT DISTINCT to give

.... A B C
.... D E F

while another could SELECT for

.... A X Y Z ...
.... A 1 2 3 ...
.... D P Q R ...
.... D 5 6 7 ...

and the same strategy could be used on each adjacent pair of levels.

Right ?

--
Wes Groleau

He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin|||It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you can
leverage a high-performance XML import utility like SQLXML without writing
additional code. Although it will take a while, I expect XML will
eventually replace both EDI and GEDCOM formats. You'll be a step ahead if
you can process XML too.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@.freeshell.orgwrote in message
news:uJv7h.211$9e.25@.trnddc02...

Quote:

Originally Posted by

Ed Murphy wrote:

Quote:

Originally Posted by

>Dan Guzman wrote:

Quote:

Originally Posted by

>>Take a look at SQLXML Bulk Load
>>(http://msdn2.microsoft.com/en-us/library/ms171993.aspx).

>>
>He'd also need an EDI to XML translator. (I recognize those
>damnable start/end tags.) Google indicates that several
>translators exist; anyone want to offer a recommendation?

>
I think I figured out a solution (haven't tried it yet).
>
Comments on this idea welcome (I'm kind of new to SQL):
>
The X12 files and GEDCOM files (maybe HL7, too?) have
multiple levels. Generally, each "level X" record
may own more than one record on level X+1
>
So if a file has (data elem delims changed to spaces)
...
CLP A B C
SVC X Y Z
SVC 1 2 3
CLP D E F
SVC P Q R
SVC 5 6 7
...
then the first pass through the file could create rows
>
... A B C X Y Z ...
... A B C 1 2 3 ...
... D E F P Q R ...
... D E F 5 6 7 ...
>
Next, one query could SELECT DISTINCT to give
>
... A B C
... D E F
>
while another could SELECT for
>
... A X Y Z ...
... A 1 2 3 ...
... D P Q R ...
... D 5 6 7 ...
>
and the same strategy could be used on each adjacent pair of levels.
>
Right ?
>
--
Wes Groleau
>
He that is good for making excuses, is seldom good for anything else.
-- Benjamin Franklin

|||Dan Guzman wrote:

Quote:

Originally Posted by

It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you
can leverage a high-performance XML import utility like SQLXML without
writing additional code. Although it will take a while, I expect XML
will eventually replace both EDI and GEDCOM formats. You'll be a step
ahead if you can process XML too.

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?

I got the impression when I was reading about it that one
XML file makes one table and vice versa.

--
Wes Groleau
----

"Thinking I'm dumb gives people something to
feel smug about. Why should I disillusion them?"
-- Charles Wallace
(in _A_Wrinkle_In_Time_)|||Wes Groleau wrote:

Quote:

Originally Posted by

OK, I do know how to read and write XML. But can an XML file
be formatted so that the utility will create multiple tables
with the appropriate foreign keys to relate them?
>
I got the impression when I was reading about it that one
XML file makes one table and vice versa.

The impression was wrong. I studied the MS KB article cited
earlier, and I can easily make such XML files. Only, the process
of transforming the file into XML is similar to the process used
by bulk load to turn the XML into records. So I suspect it would
add a little speed if I went directly to records.

--
Wes Groleau

Words of the Wild Wes(t) = http://ideas.lang-learn.us/WWWsqlsql

converting .dbf files to sql server 2000

Hi..

I want to convert .dbf files to sql server 2000 tables.. without using any tools. I need to create a different structure for sql server tables other than contains in the .dbf files. May be the dbf files contain only 3 columns. but i need 5 columns and some calculations to determine the values of some fields to insert into sql server table...

i need to code this using c# in asp.net.. can u help me?

thanks in advance..

Fraijo

A .dbf file is Character and Number data types but the chart below is all the different types yo need to convert that to so I don't see how you can do it without ETL(extraction transformation and loading) tool. So create a DTS package to move your data. Hope this helps.

.NET Framework Type

ADO.NET Database Type

SQL Data Type

String

Varchar

Varchar()

String

Nvarchar

Nvarchar()

String

NChar

Nchar()

String

NText

NText

String

Text

Text

Double

BigInt

Float

DateTime

DateTime

Datetime

DateTime

SmallDateTime

Smalldatetime

Int

Int

Int

Int64

BigInt

Bigint

Int16

SmallInt

smallint

Byte[]

Binary

Binary()

Byte[]

Image

Image

Byte[]

VarBinary

Varbinary()

Byte

TinyInt

Tinyint

Bool

Bit

Bit

Decimal

Decimal

Decimal

Decimal

Money

Money

Decimal

SmallMoney

SmallMoney

Float

Float

Float

Guid

UniqueIdentifier

Uniqueidentifier

Real

Real

Real

|||

Hi..

Thanks for ur reply.. but how can i access the .dbf files/tables from ASP.NET?

What are the procedures used to get the values from a .dbf file/table? the connection string. and driver and the things to connect

Hope get reply soon..

with regards

Fraijo

|||

Assuming your dbf files are for FoxPro the links below is all I have got and I cannot tell you anything about it because I have never used it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfoxgen7/html/usingaspnetwithvfp7.asp

http://forums.asp.net/853129/ShowPost.aspx

convert view to table

Hi,
How to convert a view to a table in SQL server?
I have created the view with related tables and filtered. But my system
studying on can not work with a view.
or any other solution?
Thanks without expect reply,
SELECT * INTO NewTable FROM Yourview
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hseyin Grler" <hguruler@.mu.edu.tr> schrieb im Newsbeitrag
news:OJOWF6ORFHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How to convert a view to a table in SQL server?
> I have created the view with related tables and filtered. But my system
> studying on can not work with a view.
> or any other solution?
> Thanks without expect reply,
>

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

sqlsql

Convert Time to 24 Hour Clock

All ,

I need to convert the given time into 24 hour clock .

I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.

Please help ..

Regards,

Ashish

try this one..

SELECT CONVERT(varchar,GETDATE(),114), CONVERT(varchar,convert(smalldatetime,'1/1/2007 7:00:00 PM'),114)|||

Ashish:

What exactly do you mean by "... one contain time in 12 Hr clock ..." what is the datatype of that particular column? The standard SQL Server "DATETIME" datatype stores data such that if you specify incoming time with a 12 Hr clock going in it is still be stored in the same internal way as time that is specified incoming with a 24 hour clock. If both columns are stored as DATETIME datatypes you can compare them directly without needing any conversion.

Kent

|||

Hi Kent ,

Here is the content of the two table and both data types are varchar

Table 1 Table 2

Col Col

12.00a 1200

1.00p 1300

2.30p 1430

Now the problem is i can directly add 12 to the table 1 values by taking substring and converting it into integer but here i dont have 00.00 for 12 Am rather i have 12.00a for the same so if i add 12 to 12.00a it became 24.

I am not able to get if i can use case in where but seems it is not possible , Do you know any other way to do it.

Regards,

Ashish

|||

Ashish,

In order for us to provide you the best suggestions, please post the DDL for both tables.

|||

( Have you got this one then, Arnie? Thanks, Arnie; I'll go ahead with it then.)

Question: How does 12:30 AM appear for table 2 (24 hour clock)

Also, you show '12.00a' on the 12 hour clock translates to '1200' will you please verify this? This looks to me like an error.

|||Nope, just thought the question should be asked.|||

Code Snippet

declare @.table1 table (time1 varchar(6))
declare @.table2 table (time2 varchar(4))
insert into @.table1
select '12.00a' union all
select '12.30a' union all
select '1.00p' union all
select '2.30p'
--select * from @.table1

insert into @.table2
select '0000' union all
select '0030' union all
select '1300' union all
select '1430'
--select * from @.table2

select a.time1,
b.time2
from ( select case when right(time1, 1) = 'p' then 1200 else 0 end +
100*(cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 2),2) as tinyint)%12) +
cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 1),2) as tinyint)
as time1
from @.table1
) a
join @.table2 b
on a.time1 = cast(b.time2 as integer)

/*
time1 time2
-- --
0 0000
30 0030
1300 1300
1430 1430
*/

Now, there is another piece of this. And that is that both of these are going to table scan as they are because indexes cannot be employed for lookups. What you REALLY need to consider is converting these tables so that the time values get stored in a standard fashion -- either as an integer or as a datetime datatype. This will give the optimizer an optimizer to employ indexes if they are available. And if you are frequently going to join these two tables based on time then you really should try to set it up so that this stuff gets indexed with a standard datatype.

(I don't think I need these reverses, but I don't have time to eliminate them either. Will somebody either check me on this or fix this?)

|||

I didn't even get out of the parking lot and I realized that the PARSENAME was just too much baloney. Hang on and I'll rectify. Something like this:

case when right(time1, 1) = 'p' then 1200 else 0 end +
cast((100*convert(numeric(7,2), reverse(substring(reverse(time1), 2, 5))))%1200 as integer)

is better than this:

case when right(time1, 1) = 'p' then 1200 else 0 end +
100*(cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 2),2) as tinyint)%12) +
cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 1),2) as tinyint)

|||

Here are the values in first table

ID Begin End

Time Time

1 12:00a 12:29a 2 12:30a 12:59a 3 1:00a 1:29a 4 1:30a 1:59a 5 2:00a 2:29a 6 2:30a 2:59a 7 3:00a 3:29a 8 3:30a 3:59a 9 4:00a 4:29a 10 4:30a 4:59a 11 5:00a 5:29a

Here is the Entries in second table

Begin Time

133502 133522 133502 133522 123502 123522 123502 123522 123504 123524 123505 123524 123502 123522 133522 123522 133522 124837

I need to compare Begin Time of first table to Second table and make a join

|||

I think that I see where this is going...

The next part of the problem will be how to group Table2 in the groups of Table1. (Every half-hour.) This is NOT going to stop with a simple join between disparate column datatypes...

**********************************************************************************

>>>> Ashish <<<-

It would be so much easier if you could change the datatypes for both tables to be shortdatetime datatypes.

Is that possible?

Otherwise, you are going to be saddled with incredible 'kludgy' code, and it will be increasingly difficult to create the kinds of information that you want to create from your data. Yes, we can help you hammer out some tortured code that will solve the current problem, BUT I don't think that you have fully explained where this is going. Get a grip and do it right -change the datatypes, then the solutions will be relatively simple! This is crazy making stuff and it is not going to get any better.

|||

Hi Arnie ,

I did either way around , Added one more column in the table and updated it with the corresponding conversion. I guess this will not increase any performance issue.

Your Thoughts....

Regards,

Ashish

|||

Ashish,

Thanks, that will greatly improve performance issues for handling the data in either a JOIN or an aggregation operation.

In your original post, you indicated that you wanted to JOIN the two tables. But your sample data does not indicate any columns with matching time values.

So is it a JOIN, or as I expressed earlier, are you really wanting to group by half-hour intervals -or something else?

Please clarify your desired results, and we can help you find a fast and efficient solution.

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeSQL Server is pretty good about implicitly converting text to time. If the time for is really odd, then you may need to use the CONVERT function or create a custom function.

Can you do this for all 30 tables in a single query? MAYBE using a union query, but don't count on it.|||I am not using SQL Server but Microsoft Access.

I time had an odd format but i have managed to manipulate it to the format 00:00:00.

I only need to convert the field from text to date/time. I have tried convert() and CDate() but i cannot make it to work.

My table is named "01" and the field "Duration".

Can anyone provide me with the full code to make the change.

I will use "Macros" and "RunSQLQuery" to make the converion for all tables.

This is the last problem i have to solve in order to make it work. I have been working on this database for the last week.

Please note that i am new in SQL with MS Access. I have started only one moth ago|||I would suggest that you post this question in the MS-Access (http://www.dbforums.com/f84) forum. They have more experience with the Access GUI and might be able to give you better suggestions.

I tried using Cdate("13:23:45") in an Access query, and it worked nicely for me. I suspect that that is the conversion that you need, but I'm not certain about what code you need to derive a properly formatted time string.

-PatP|||Ok, I thought of something else.

There is no need to change the data type of the table. I just created a query with all the fields of the table but for Duration i put: CDate([Duration]). The query returns the results as Date/Time and from there i can perform the calculations i require.

It works.

Thanks again, problem solved.|||Gee, you just have to love it when you inadvertanly solve a problem!

-PatP

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeFirst, you will need to use the CAST or CONVERT SQL function to make your text a datetime field (assuming you need the seconds in the time otherwise smalldatetime will work also). Then use the DATEPART function to return just the time portion of the field.|||I am new in SQL and the book i use doesn't offer much help in th convert function.

Can you give me the sql statement to convert the field duration ofthe table 01 from text to datetime?

Thanks|||MSDN is a big help. See the documentation on Convert() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) either online or in your copy of Books Online (which is installed as part of the SQL client tools).

-PatP|||I still can't figure it out.

I use the following:

UPDATE 01
set CONVERT (datetime, Duration);

and

UPDATE 01
set CAST (Duration as datetime);

I get a syntax error after (|||You'll have to post at least a few lines of code for me to be able to help you. I can't figure out what you want from what you've posted so far.

-PatP|||My table is named "01".

One of the fields is "Duration".

The table is imported from a csv file. The Duration is not in a valid date/time format so i have to import it as "Text", modify it and convert is to "Date/Time".

I can easily do this from the design view of the table but since i have 31 similar tables, i require a faster way (i don't want to go through all 31 tables to make the changes, it takes too long). The fastest way i can think is through SQl.

So, it would be much apreciated if someone can provide me with the command to update the date type of "Duration" field from "Text" to "Date/Time" using SQL or a Macro.

I will use the command to do the same change for all 31 tables.

This is the last problem i have to solve before i can make the database work.

convert tables in a database to use unicode

Hi!
I have to convert all nonUnicode-fields in a database to use unicode. I
tried to convert one column and realized that it will take very long time or
the memory usage will be too high.
If I create a database (a copy of the one that is used) and create tables
that use unicode in this one and then export the data from the old db to the
new db, is this a good idea? Are there any better ways ( there always are
:-), anyone who has more experience than me?)
Thanks for help!
//Malinthis is how I would do it.
script out all the objects into individual scripts.
create a vb script to search and replace varchar with nvarchar, char with
nchar and text with ntext.
build a database using the scripts.
run a comparison and synchronization on using the newly created database as
the source and the target which would be a copy of the entended database and
record the delta script.
Job done using DB Ghost.
Although the above is certainly possible it lacks any change management. If
you have all your source code in source control the changes could be
automatically made by checking out all the source and running the procedures
above. You'd then have a history of what is changing with your database code
via all the functions of your source control - such as who changed this? why
was it changed? when was it changed? how was it changed? where was it change
d?
most people use source control for procedural code - why not database code?
DB Ghost gives you a fast, easy way to manage your database code using your
favorite source control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Malin Davidsson" wrote:

> Hi!
> I have to convert all nonUnicode-fields in a database to use unicode. I
> tried to convert one column and realized that it will take very long time
or
> the memory usage will be too high.
> If I create a database (a copy of the one that is used) and create tables
> that use unicode in this one and then export the data from the old db to t
he
> new db, is this a good idea? Are there any better ways ( there always are
> :-), anyone who has more experience than me?)
> Thanks for help!
> //Malin
>
>|||Hmm... that's a very complex solution to a very simple problem.
Here's a much simpler way of achieving this goal:
1. Backup your DB if possible.
2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
Here, the script must take into account the size of the table row, to ensure
that you do not exceed 8060 bytes. This is quite simple to do though.
Run this script to loop over all DB tables.
Question: Do you also need to convert TEXT fields to NTEXT, or only from
VARCHAR to NVARCHAR?
I can help out with the script, if you need assistance.
Omri.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com|||my main point in doing it this way is to have all changes under source
control...
"Omri Bahat" wrote:

> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to ensu
re
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>|||Hi!
Yes I also want to change text to ntext, how do I do that? I tried alter
table but noticed tha I'm not allowed to change text-columns... :-/
thanks for all help!
//Malin
"Omri Bahat" <OmriBahat@.discussions.microsoft.com> wrote in message
news:06EAC641-ECFD-4E77-B8FE-01E5D8A83326@.microsoft.com...
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to
> ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>

convert tables in a database to use unicode

Hi!
I have to convert all nonUnicode-fields in a database to use unicode. I
tried to convert one column and realized that it will take very long time or
the memory usage will be too high.
If I create a database (a copy of the one that is used) and create tables
that use unicode in this one and then export the data from the old db to the
new db, is this a good idea? Are there any better ways ( there always are
:-), anyone who has more experience than me?)
Thanks for help!
//Malinthis is how I would do it.
script out all the objects into individual scripts.
create a vb script to search and replace varchar with nvarchar, char with
nchar and text with ntext.
build a database using the scripts.
run a comparison and synchronization on using the newly created database as
the source and the target which would be a copy of the entended database and
record the delta script.
Job done using DB Ghost.
Although the above is certainly possible it lacks any change management. If
you have all your source code in source control the changes could be
automatically made by checking out all the source and running the procedures
above. You'd then have a history of what is changing with your database code
via all the functions of your source control - such as who changed this? why
was it changed? when was it changed? how was it changed? where was it changed?
most people use source control for procedural code - why not database code?
DB Ghost gives you a fast, easy way to manage your database code using your
favorite source control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Malin Davidsson" wrote:
> Hi!
> I have to convert all nonUnicode-fields in a database to use unicode. I
> tried to convert one column and realized that it will take very long time or
> the memory usage will be too high.
> If I create a database (a copy of the one that is used) and create tables
> that use unicode in this one and then export the data from the old db to the
> new db, is this a good idea? Are there any better ways ( there always are
> :-), anyone who has more experience than me?)
> Thanks for help!
> //Malin
>
>|||Hmm... that's a very complex solution to a very simple problem.
Here's a much simpler way of achieving this goal:
1. Backup your DB if possible.
2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
Here, the script must take into account the size of the table row, to ensure
that you do not exceed 8060 bytes. This is quite simple to do though.
Run this script to loop over all DB tables.
Question: Do you also need to convert TEXT fields to NTEXT, or only from
VARCHAR to NVARCHAR?
I can help out with the script, if you need assistance.
Omri.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com|||my main point in doing it this way is to have all changes under source
control...
"Omri Bahat" wrote:
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>|||Hi!
Yes I also want to change text to ntext, how do I do that? I tried alter
table but noticed tha I'm not allowed to change text-columns... :-/
thanks for all help!
//Malin
"Omri Bahat" <OmriBahat@.discussions.microsoft.com> wrote in message
news:06EAC641-ECFD-4E77-B8FE-01E5D8A83326@.microsoft.com...
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to
> ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>

convert tables in a database to use unicode

Hi!
I have to convert all nonUnicode-fields in a database to use unicode. I
tried to convert one column and realized that it will take very long time or
the memory usage will be too high.
If I create a database (a copy of the one that is used) and create tables
that use unicode in this one and then export the data from the old db to the
new db, is this a good idea? Are there any better ways ( there always are
:-), anyone who has more experience than me?)
Thanks for help!
//Malin
this is how I would do it.
script out all the objects into individual scripts.
create a vb script to search and replace varchar with nvarchar, char with
nchar and text with ntext.
build a database using the scripts.
run a comparison and synchronization on using the newly created database as
the source and the target which would be a copy of the entended database and
record the delta script.
Job done using DB Ghost.
Although the above is certainly possible it lacks any change management. If
you have all your source code in source control the changes could be
automatically made by checking out all the source and running the procedures
above. You'd then have a history of what is changing with your database code
via all the functions of your source control - such as who changed this? why
was it changed? when was it changed? how was it changed? where was it changed?
most people use source control for procedural code - why not database code?
DB Ghost gives you a fast, easy way to manage your database code using your
favorite source control.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Malin Davidsson" wrote:

> Hi!
> I have to convert all nonUnicode-fields in a database to use unicode. I
> tried to convert one column and realized that it will take very long time or
> the memory usage will be too high.
> If I create a database (a copy of the one that is used) and create tables
> that use unicode in this one and then export the data from the old db to the
> new db, is this a good idea? Are there any better ways ( there always are
> :-), anyone who has more experience than me?)
> Thanks for help!
> //Malin
>
>
|||Hmm... that's a very complex solution to a very simple problem.
Here's a much simpler way of achieving this goal:
1. Backup your DB if possible.
2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
Here, the script must take into account the size of the table row, to ensure
that you do not exceed 8060 bytes. This is quite simple to do though.
Run this script to loop over all DB tables.
Question: Do you also need to convert TEXT fields to NTEXT, or only from
VARCHAR to NVARCHAR?
I can help out with the script, if you need assistance.
Omri.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
|||my main point in doing it this way is to have all changes under source
control...
"Omri Bahat" wrote:

> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>
|||Hi!
Yes I also want to change text to ntext, how do I do that? I tried alter
table but noticed tha I'm not allowed to change text-columns... :-/
thanks for all help!
//Malin
"Omri Bahat" <OmriBahat@.discussions.microsoft.com> wrote in message
news:06EAC641-ECFD-4E77-B8FE-01E5D8A83326@.microsoft.com...
> Hmm... that's a very complex solution to a very simple problem.
> Here's a much simpler way of achieving this goal:
> 1. Backup your DB if possible.
> 2. Use a simple T-SQL script that converts all VARCHAR fields to NVARCHAR.
> Here, the script must take into account the size of the table row, to
> ensure
> that you do not exceed 8060 bytes. This is quite simple to do though.
> Run this script to loop over all DB tables.
> Question: Do you also need to convert TEXT fields to NTEXT, or only from
> VARCHAR to NVARCHAR?
> I can help out with the script, if you need assistance.
> Omri.
> --
> Omri Bahat
> SQL Farms Solutions
> www.sqlfarms.com
>