Importing txt files with sql server 2000

HI!

I am importing .txt files. How can i check the errors? I have created a
log file, but the problem is that i lose some characters.
I import for example:

Code
ABC
FZH
JHN

from a text file, but sometimes Code can be 4 caracters long
I import this 3 characters long now. When i add the same structured
text file with some rows lenght 4, it skips the last character, but i
get nothing in the log file.

please help
xgirlSo you already have a table after importing items with 3 characters,
and then later you import items with 4 characters?

Perhaps it set the field width to 3 based on what it found in the first
import.

Sounds like you may need to manually widen the field. Open the table
in design view and make sure the field width is large enough for 4
characters.|||If you are using a DTS for the import, you may want to check the
Transform Data Task. If you are using fixed width delimiting then that
may be the problem.|||I changed from varchar ->nvarchar if you mean that but still i get no
errors of lost characters.

The problem is i have a lot of .txt files and i will get in the future
the same files with different data. if i automaticlly import every
file, how can i be sure the data are not longer and i didn't lost some
characters.

thank you
xgirl|||I changed from varchar ->nvarchar if you mean that but still i get no
errors of lost characters.

The problem is i have a lot of .txt files and i will get in the future
the same files with different data. if i automaticlly import every
file, how can i be sure the data are not longer and i didn't lost some
characters.

thank you
xgirl

importing text in SQL server

I need to import a large textfile into sql server 2005.
Some of the text fields contain special characters...like:

?le-de-France

when I import a text like this into SQL Server I get:
??le-de-France

What can I do to prevent this from happening? (other fieldtype?)Using Unicode is a good choice...That maps to NTEXT data type in SQL Server|||Using Unicode is a good choice...That maps to NTEXT/NVARCHAR data type in SQL Server|||Could you please describe the steps I need to take to make this work?
I cant get it done..:$...sql

importing queries from access

im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
mark
Replied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipa ted]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>

importing queries from access

im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissio
nAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_Oth
erBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCo
mmission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions
.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,&#
91;Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,&#
91;Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipeagreen">
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusineeagreen">
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissioneagreen">
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBueagreen">
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,
[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,
[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>sql

importing queries from access

im having trouble converting access sql queries into mssql, i know to
replace certain characters (eg _ and *) but im having trouble importing
expressions - how do i do these in mssql ? eg an example query i use :-
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
ss.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
sinessCommissionReceived] AS Expr2,
IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
Expr3,
IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
dbo_Life ON dbo_Personal.ID=dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
how would i import this into a stored procedure ?
thanks
markReplied in .programming.
Please do not cross post.
John
"mark" <mark@.remove.com> wrote in message
news:w_RBc.7$Nx2.3@.newsfe3-win.server.ntli.net...
> im having trouble converting access sql queries into mssql, i know to
> replace certain characters (eg _ and *) but im having trouble importing
> expressions - how do i do these in mssql ? eg an example query i use :-
> SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead,
> dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted,
> dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown,
> dbo_Mortgage.MortgageApplicationClosed,
>
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticip
>
ated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusine
> ss.OtherBusinessCommissionAnticipated] AS Expr1,
>
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommission
>
Received]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBu
> sinessCommissionReceived] AS Expr2,
> IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS
> Expr3,
> IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS
> Expr4
> FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID=dbo_Lead.ID)
> LEFT JOIN dbo_Mortgage ON dbo_Personal.ID=dbo_Mortgage.ID) LEFT JOIN
> dbo_OtherBusiness ON dbo_Personal.ID=dbo_OtherBusiness.ID) LEFT JOIN
> dbo_BuildingsAndContents ON dbo_Personal.ID=dbo_BuildingsAndContents.ID)
> LEFT JOIN dbo_Commissions ON dbo_Personal.ID=dbo_Commissions.ID) LEFT JOIN
> dbo_Life ON dbo_Personal.ID=dbo_Life.ID
> WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
> how would i import this into a stored procedure ?
> thanks
> mark
>

Importing from dBase

I'm trying to import a dBase database to SQL Server, but it contains Swedish characters (,,), and these are replaced with other characters.

I've tried importing from dBase 5, III, and IV, without luck. I don't know how to solve this problem. Is there any way to replace characters using T-SQL, or do I need to change some settings?

I hope someone can help me with this...Have you tried using Swedish collation on sql server?
It will also depend on how you are doing the import. It may be losing the data on the extract from dbase or on the import into sql server.|||Thanks for your reply!

I tried changing the collation using this:

alter database TEST COLLATE Finnish_Swedish_CS_AS

It didn't work. I got an error message saying:

"Incorrect syntax near 'Finnish_Swedish_CS_AS'."

I've used the syntax described in Books Online.

*confused*|||I just learned that collation only can be changed in Sql Server 2k, so I'll solve it in another way. Thanks anyway.

importing chinese text

Hello,
I have some multibyte characters and I want to put them into table fields.
how I can do this? Just storing them will result in a ? when reading /
showing them.
thanks & regards
MarkI'm doing similar thing without any problems.

My Chinese characters are in a text file, I used DTS to import them
into my SQL DB, they are shown on web pages perfectly.

How did you read/show them?

SL at http://www.source4book.com