Import Data from Text file

I am importing data from a text file. The file is simple, only 9 fields.
Each field has quotation marks around the data. During the import it
creates a table with varchar fields and length of 8000!!
The only problem with this is that I merge this data and other inforation to
a permenant table with normal field sizes. With this I start getting errors
that the data will be truncated.
Second problem. The code below:
--
Declare @.TableName nvarchar(10)
Declare @.SQLCmd nvarchar(1000)
set @.TableName = 'RS201'
Set @.SQLCmd = '
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select '+ Char(39)+ @.TableName + char(39) + ' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(PatronID,Char(34),Null),
replace(Printer,Char(34),Null),
replace(DocumentType,Char(34),Null)
From ' + @.TableName
print @.Sqlcmd
--
The code produces:
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select 'RS201' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(PatronID,Char(34),Null),
replace(Printer,Char(34),Null),
replace(DocumentType,Char(34),Null)
From RS201
This works with the Print statement.
However when used with the Execute statement, I get:
---
Server: Msg 203, Level 16, State 2, Line 31
The name '
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType)
Select 'RS201' ,
replace(Code,Char(34),Null),
replace(DateStamp,Char(34),Null),
replace(DocumentName,Char(34),Null),
replace(Pages,Char(34),Null),
replace(Cost,Char(34),Null),
replace(Client,Char(34),Null),
replace(P...
---
It truncates the SQL string.
Any Ideas!
ArthurI have the solution.
Apparently the contructed SQL command is to long for the execute to handle
with one variable. The on-line books said to break it up into 2 variables
and concatenate the 2 command strings. Thus, Execute (@.Sqlcmd1 + @.SQLCmd2).
This works! So I think that the 8000 byte field size is affecting this.
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:OaWJoJ$lDHA.2424@.TK2MSFTNGP10.phx.gbl...
> I am importing data from a text file. The file is simple, only 9 fields.
> Each field has quotation marks around the data. During the import it
> creates a table with varchar fields and length of 8000!!
> The only problem with this is that I merge this data and other inforation
to
> a permenant table with normal field sizes. With this I start getting
errors
> that the data will be truncated.
> Second problem. The code below:
> --
> Declare @.TableName nvarchar(10)
> Declare @.SQLCmd nvarchar(1000)
> set @.TableName = 'RS201'
> Set @.SQLCmd = '
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select '+ Char(39)+ @.TableName + char(39) + ' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(PatronID,Char(34),Null),
> replace(Printer,Char(34),Null),
> replace(DocumentType,Char(34),Null)
> From ' + @.TableName
> print @.Sqlcmd
> --
> The code produces:
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select 'RS201' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(PatronID,Char(34),Null),
> replace(Printer,Char(34),Null),
> replace(DocumentType,Char(34),Null)
> From RS201
> This works with the Print statement.
> However when used with the Execute statement, I get:
> ---
> Server: Msg 203, Level 16, State 2, Line 31
> The name '
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType)
> Select 'RS201' ,
> replace(Code,Char(34),Null),
> replace(DateStamp,Char(34),Null),
> replace(DocumentName,Char(34),Null),
> replace(Pages,Char(34),Null),
> replace(Cost,Char(34),Null),
> replace(Client,Char(34),Null),
> replace(P...
> ---
> It truncates the SQL string.
>
> Any Ideas!
> Arthur
>