Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type =
ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.
Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type =
> ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>
sql

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type =
ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote 
3;"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I
got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type =
> ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote &#
123;"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN"
I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type = ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type => ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

Problem with Dynamic Where Clause

I have a stored proc that accepts a varchar as a parameter. What is being
passed in is one or more IDs in a comma separated list. (ie '123,567,789')
In the where clause I want to pull are records where a value is IN the list
being passed in. (ie. WHERE column IN (@.Var))
When I run this stored proc I get an error because its treating
'123,567,789' as one varchar value insstead of 3 int values.
Is there anything I can do to work around this?
Thanks in advance,
Mike RFaking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>|||Mike wrote:
> I have a stored proc that accepts a varchar as a parameter. What is
> being passed in is one or more IDs in a comma separated list. (ie
> '123,567,789') In the where clause I want to pull are records where a
> value is IN the list being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
You have to use dynamic SQL to do what you want and you'll have to grant
users select access on the tables in question if they don't already have
those rights. That might be asecurity risk in your environment.
declare @.n nvarchar(1000)
Set @.n = N'Select col1 from mytable where col2 in (' + @.Var + N')'
Exec sp_executesql @.n
The other option is to create a temp table with all those IDs and join
the temp table with the main table in the query.
David Gugick
Imceda Software
www.imceda.com|||In article <Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl>,
mraeNOSPAM@.NOSPAMATALLcalibrus.com says...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
http://www.sommarskog.se/arrays-in-...ist-of-integers
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <mraeNOSPAM@.NOSPAMATALLcalibrus.com> wrote in message
news:Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the
list
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>|||If you use the attached User Defined Function to convert the Delimited list
in =to a table variable, you can simply join your main query to this table
variable...
Here's the UDF
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
And in your stoored Proc, just join to the output of this udf as though it
was a table, containing a varchar() named sVal...
Select <stuff>
From Table T
Join dbo.ParseString(@.Var, ',') as V
On T.ColumnName = Cast(V.sVal as Integer)
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>

problem with DTS and text file

Hello,
i have the following problem:
i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.
Someone any idea what's wrong?does your comma separate decimals from the significant digits?|||Originally posted by troj
Hello,
i have the following problem:
i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.

Someone any idea what's wrong?

Nothing's wrong...although I'm suprised it didn't blow up...comma's in numbers are just a presentation issue...unlike precision...

But for example...you can't do..

CREATE TABLE myTablefloat (col1 float)
INSERT INTO myTablefloat(col1) SELECT 1,203.45

But for some reason in a text file it allows it|||Originally posted by ms_sql_dba
does your comma separate decimals from the significant digits?

it are money values, for example 10,22
in sql table it becomes 1022|||Originally posted by troj
it are money values, for example 10,22
in sql table it becomes 1022

What's the collation, language of your sql server box?

Sounds like it doesn't handle comma as precision.

Load the data in to a work table that the column defined as varchar..

Then populate your final table by doing an insert and convert your data..something like

SELECT CONVERT(float,REPLACE(col1,',','.'))|||If changing datatype is a problem you can even use the ActiveX replace function in the DTS transformation to replace "," by "."