Problems importing TXT files using DTS

Hello,

I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)

I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.

Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.

What am I doing wrong? Should I be using something else than DTS?

Any suggestions are greatly appreciated.

Thanks,
JByou should create a transform data task and connect the text file to the destination though the task.
create the destination table in the xform task and map the data types to the new destinations.

ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.|||Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.|||Ruprect,

Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.

I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.

At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.

I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.

Thanks again,
JB|||Yeah, you've got bad data...

I would load the data to a table of a varchars...like you have now

Think of it as a staging table

Then audit the data...

For example

Col030 should be a date

Find out all the rows with bad dates

SELECT * FROM myTable99 WHERE ISDATE(Col030)=0

Will show you rows sql doesn't consider a date...

Same with numerics

ISNUMERIC(col1)=0

You won't be able to load those rows to your final destination table

either fix the input file, or ignore the rows...

in any case, go to who ever gave you the file and say...see here...this is garbage...fix it...

and wait a week while they try to figure out what to do...

Golf, Tennis, Long lunches...whatever you want...|||Brett,

You are correct it was bad data. That should have been the first place I looked.

I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.

Anyways, the problem is solved.

Thanks,
JB

Problems importing text files with double-quotes as text qualifier

I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:

The column delimiter for column "Column 1" was not found.

Any ideas on how to resolve this issue will be greatly appreciated.

Thanks
pcp

Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Dim unparsedSubString As String

Dim commaPos As Integer = 0

Dim firstQuotePos As Integer = 0

Dim nextQuotePos As Integer = 0

Dim colArray(0) As String

unparsedSubString = Row.RawColumns

commaPos = unparsedSubString.IndexOf(",", 0)

' while the comma position exists within the string, loop

While commaPos <> -1

' get the position of the first two quotes within the string

firstQuotePos = unparsedSubString.IndexOf("""", 0)

nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)

' if the comma position is between a set of quotes, grab everything within the quotes

If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then

commaPos = unparsedSubString.IndexOf(",", nextQuotePos)

End If

' Resize the array to hold a new element.Element 0 may exist and be unfilled, which

' is required to make sure there are no null references on first byref call to Resize.

If colArray(0) <> Nothing Then

Array.Resize(colArray, colArray.Length + 1)

End If

If commaPos >= 0 Then

colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")

unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))

Else

' This clause covers the last field being qouted with a comma

colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")

unparsedSubString = String.Empty

End If

' values for next iteration of loop

commaPos = unparsedSubString.IndexOf(",", 0)

End While

' Add whatever contents exist after the last comma

' This clause covers the last field being unquoted or quoted without a comma.

If unparsedSubString.Length > 0 Then

If colArray(0) <> Nothing Then

Array.Resize(colArray, colArray.Length + 1)

End If

colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")

End If

If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then

Me.OutputBuffer.AddRow()

Me.OutputBuffer.Column1 = colArray(0)

Me.OutputBuffer.Column2 = colArray(1)

Me.OutputBuffer.Column3 = colArray(2)

Me.OutputBuffer.Column4 = colArray(3)

Me.OutputBuffer.Column5 = colArray(4)

Me.OutputBuffer.Column6 = colArray(5)

Me.OutputBuffer.Column7 = colArray(6)

Me.OutputBuffer.Column8 = colArray(7)

Me.OutputBuffer.Column9 = colArray(8)

Me.OutputBuffer.Column10 = colArray(9)

Me.OutputBuffer.Column11 = colArray(10)

End If

End Sub

End Class

I'm guessing you can figure the rest of the column mappings and such from here. Good luck.

|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||

Hi Robert,

I tried to use a transformation Script Component to do it, but all I am getting is

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.

I am using SQL 2005 Developer edition with SP1. What am I missing here?

Thanks.

|||

Another question :

In 2005 SSIS, Is there an easy way to import a flat file like

"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?

Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.

Thanks.

|||I tried pasting the script, but I get the following errors:

type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........

The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.

Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.

Problems importing text files with double-quotes as text qualifier

I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:

The column delimiter for column "Column 1" was not found.

Any ideas on how to resolve this issue will be greatly appreciated.

Thanks
pcp

Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Dim unparsedSubString As String

Dim commaPos As Integer = 0

Dim firstQuotePos As Integer = 0

Dim nextQuotePos As Integer = 0

Dim colArray(0) As String

unparsedSubString = Row.RawColumns

commaPos = unparsedSubString.IndexOf(",", 0)

' while the comma position exists within the string, loop

While commaPos <> -1

' get the position of the first two quotes within the string

firstQuotePos = unparsedSubString.IndexOf("""", 0)

nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)

' if the comma position is between a set of quotes, grab everything within the quotes

If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then

commaPos = unparsedSubString.IndexOf(",", nextQuotePos)

End If

' Resize the array to hold a new element.Element 0 may exist and be unfilled, which

' is required to make sure there are no null references on first byref call to Resize.

If colArray(0) <> Nothing Then

Array.Resize(colArray, colArray.Length + 1)

End If

If commaPos >= 0 Then

colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")

unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))

Else

' This clause covers the last field being qouted with a comma

colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")

unparsedSubString = String.Empty

End If

' values for next iteration of loop

commaPos = unparsedSubString.IndexOf(",", 0)

End While

' Add whatever contents exist after the last comma

' This clause covers the last field being unquoted or quoted without a comma.

If unparsedSubString.Length > 0 Then

If colArray(0) <> Nothing Then

Array.Resize(colArray, colArray.Length + 1)

End If

colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")

End If

If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then

Me.OutputBuffer.AddRow()

Me.OutputBuffer.Column1 = colArray(0)

Me.OutputBuffer.Column2 = colArray(1)

Me.OutputBuffer.Column3 = colArray(2)

Me.OutputBuffer.Column4 = colArray(3)

Me.OutputBuffer.Column5 = colArray(4)

Me.OutputBuffer.Column6 = colArray(5)

Me.OutputBuffer.Column7 = colArray(6)

Me.OutputBuffer.Column8 = colArray(7)

Me.OutputBuffer.Column9 = colArray(8)

Me.OutputBuffer.Column10 = colArray(9)

Me.OutputBuffer.Column11 = colArray(10)

End If

End Sub

End Class

I'm guessing you can figure the rest of the column mappings and such from here. Good luck.

|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||

Hi Robert,

I tried to use a transformation Script Component to do it, but all I am getting is

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.

I am using SQL 2005 Developer edition with SP1. What am I missing here?

Thanks.

|||

Another question :

In 2005 SSIS, Is there an easy way to import a flat file like

"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?

Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.

Thanks.

|||I tried pasting the script, but I get the following errors:

type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........

The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.

Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.

Problems importing text files with double-quotes as text qualifier

I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:

The column delimiter for column "Column 1" was not found.

Any ideas on how to resolve this issue will be greatly appreciated.

Thanks
pcp

Import the data as a flat file... don't try to parse the columns apart yet. Save the data as one giant column per row. In your data flow, use a custom script to parse it apart. Here's a modified example of mine:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Dim unparsedSubString As String

Dim commaPos As Integer = 0

Dim firstQuotePos As Integer = 0

Dim nextQuotePos As Integer = 0

Dim colArray(0) As String

unparsedSubString = Row.RawColumns

commaPos = unparsedSubString.IndexOf(",", 0)

' while the comma position exists within the string, loop

While commaPos <> -1

' get the position of the first two quotes within the string

firstQuotePos = unparsedSubString.IndexOf("""", 0)

nextQuotePos = unparsedSubString.IndexOf("""", firstQuotePos + 1)

' if the comma position is between a set of quotes, grab everything within the quotes

If commaPos > firstQuotePos AndAlso commaPos < nextQuotePos Then

commaPos = unparsedSubString.IndexOf(",", nextQuotePos)

End If

' Resize the array to hold a new element.Element 0 may exist and be unfilled, which

' is required to make sure there are no null references on first byref call to Resize.

If colArray(0) <> Nothing Then

Array.Resize(colArray, colArray.Length + 1)

End If

If commaPos >= 0 Then

colArray(colArray.Length - 1) = unparsedSubString.Substring(0, commaPos).Replace("""", "")

unparsedSubString = unparsedSubString.Substring(commaPos + 1, unparsedSubString.Length - (commaPos + 1))

Else

' This clause covers the last field being qouted with a comma

colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")

unparsedSubString = String.Empty

End If

' values for next iteration of loop

commaPos = unparsedSubString.IndexOf(",", 0)

End While

' Add whatever contents exist after the last comma

' This clause covers the last field being unquoted or quoted without a comma.

If unparsedSubString.Length > 0 Then

If colArray(0) <> Nothing Then

Array.Resize(colArray, colArray.Length + 1)

End If

colArray(colArray.Length - 1) = unparsedSubString.Replace("""", "")

End If

If colArray.Length >= 2 AndAlso colArray(1) = "FIL" Then

Me.OutputBuffer.AddRow()

Me.OutputBuffer.Column1 = colArray(0)

Me.OutputBuffer.Column2 = colArray(1)

Me.OutputBuffer.Column3 = colArray(2)

Me.OutputBuffer.Column4 = colArray(3)

Me.OutputBuffer.Column5 = colArray(4)

Me.OutputBuffer.Column6 = colArray(5)

Me.OutputBuffer.Column7 = colArray(6)

Me.OutputBuffer.Column8 = colArray(7)

Me.OutputBuffer.Column9 = colArray(8)

Me.OutputBuffer.Column10 = colArray(9)

Me.OutputBuffer.Column11 = colArray(10)

End If

End Sub

End Class

I'm guessing you can figure the rest of the column mappings and such from here. Good luck.

|||Thanks Robert. This was very helpful. I just tried a variation with the Split function and it worked too. Any thoughts on the shortcomings of using the Split?|||

Hi Robert,

I tried to use a transformation Script Component to do it, but all I am getting is

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

I cannot get it to be created with Row as InputBuffer and use the RawColumns properties. InputBuffer class seems to be unrecognized somehow.

I am using SQL 2005 Developer edition with SP1. What am I missing here?

Thanks.

|||

Another question :

In 2005 SSIS, Is there an easy way to import a flat file like

"ABC", "EDFG", "123", "10/1/2006" to a SQL database table, with the last two columns as int and date fields ?

Always errored out for data type mismatch. I used SQL 2000 DTS for same data file, no problem at all, but can't get the import work in SSIS.

Thanks.

|||I tried pasting the script, but I get the following errors:

type 'UserComponent' is not defined
type 'InputBuffer' is not defined
'OutputBuffer' is not a member of 'ScriptTask_7a67..........

The dtspipeline doesn't show up as an available reference in the editer.However, it does show up when I launch a regular instance of Visual Studio.I tried copying DTSPipeline.dll from the sql server directory to the .net framework directory, but it doesn't seem to register automatically.

Any ideas?
Thanks|||i seem to be having the same problems. let me know if you have a solution to this.

Problems backing up SQL DB.

Every time i try to back up a specific DB i get an error saying it can
not backup because the full text catalog is not permitted because it
is not online. It does not matter if i try to backup the db through
SQL of my Backup software. here is the sql log.

LogJob History (DB File level Backup.Subplan_1)

Step ID1
ServerServer Name
Job NameDB File level Backup.Subplan_1
Step NameSubplan_1
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0

Message
Executed as user: Domain\USer. ...ecute Package Utility Version
9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All
rights reserved. Started: 7:07:43 AM Progress: 2007-06-01
07:07:44.14 Source: {B6285A6A-0066-416F-AD9F-F31ED5A68F14}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE
msdb..sp".: 100% complete End Progress Error: 2007-06-01
07:07:44.49 Code: 0xC002F210 Source: Back Up Database (Full)
Execute SQL Task Description: Executing the query "BACKUP DATABASE
[DBName] FILEGROUP = N'PRIMARY' TO DISK = N'E:\Program Files
\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBName_200706010707.ba k'
WITH NOFORMAT, NOINIT, NAME = N'DBName_backup_20070601070744', SKIP,
REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The
backup of full-text catalog 'ftcat_documentindex' is not permitted
because it is not online. Check errorlog file for the reason that full-
text catalog became offline and . The step failed.Bryan (bsockel@.gmail.com) writes:

Quote:

Originally Posted by

Every time i try to back up a specific DB i get an error saying it can
not backup because the full text catalog is not permitted because it
is not online. It does not matter if i try to backup the db through
SQL of my Backup software. here is the sql log.

Searching Books Online on "fulltext offline", I found this link:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlerrm9/html/592bd03e-7f46-4b88-9bd0-
7a0b32cbfd58.htm

It seems that there is a chance that you find the answer there.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.

Problem with using a text data type.

I am using a 'text' data type in a table and am having trouble adding data to it. I'm using an MS Access ADP project front end for my forms. I want to copy and paste from a text editor such as NotePad to a 'text' data type field in a form. There appears to be a limit that the field will accept which is much below the 2GB size that is allowed for the field. I get the following error message: "The text is too long to be edited." Once I get this error I can add characters from the keyboard but eventually the field will not accept any more characters. Typing or pasting does nothing. I can delete. So I've hit a limit but I'm not sure if it is in Access or SQL Server.

Any help would be appreciated.

Dennis GeasanGo to design view on the form. Right click on your text box. Look at the properties. I believe there's a property there to set the max length.|||No such property exists on a 'text' data type field. I did some further testing. The field will take up to 64000 characters and then quit accepting any more. It looks like I will have to use special SQL commands to add additional data to this field. It does not behave the same as an MS Access Memo field and directly allow a large amount of text to be added. Further research indicates the SQL Server 'text' data type is a linked list and I will have to start working with pointers to be able to add more data. Ugly.

Dennis Geasan

Problem with Updatetext in SQL 2000 trying to concatenate multiple text fields

Hi all, I am creating a search table where the keywords field is made
up of several text fields and this is causing me some problems. I can
concatentate the text ok but i can't seem to concatenate matching
records here is the cursor loop. I'm not a fan of cursors but also
didn't see another way of achieving this.

declare @.ptr1 varbinary(16)
declare @.Ptr2 varbinary(16)
declare @.profileid int
declare @.x int

set @.profileid = 0

while @.profileid is not null
begin

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

declare c2 cursor fast_forward for
select textptr(searchterms), datalength(searchterms)
from search
where search.[id] = @.profileid

open c2

fetch c2 into @.ptr2, @.x

while @.@.fetch_status = 0
begin
updatetext search.searchterms @.ptr2 null 0 #holding.text1 @.ptr1
fetch c2 into @.ptr2, @.x
end

close c2
deallocate c2

end

The #holding table contains the fields that i want to concatenate and
the search table is the resulting table. This example would loop
through search and find id 1 in search and then append another field
matching id 1 in holding then move onto the next field in turn going
through the whole table.

i.e.

search holding result after each loop
id text id text
1 abc 1 def abcdef
2 ghi 2 jkl ghijkl

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

DanDan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

This does not look good. Is there are any reason to assume that text
pointers are aligned with id?

Either do

SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid

SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid

Or run a second cursor over #holding.

And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.

In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dan wrote:

Quote:

Originally Posted by

When I run this, some of the records concatenate properly but most dont
with the same text being appended to the end of searchterms. i.e loads
of results will end up with jkl tagged onto the end. I can't figure out
when my loop is falliing over!!! Can anyone help?

I don't know /why/ the loop is doing the wrong thing, but adding some
debugging output after the UPDATETEXT line (e.g. SELECT * FROM SEARCH)
should at least reveal /when/ it's doing the wrong thing.

Also, I would seriously avoid columns named "id".|||Thanks Erland, you hit the nail on the head, i misunderstood what the
textptr did and thought both could be set at the same point. Hopefully
this will be the one and only time i have to use either text
concatenation or cursors. Is is the varchar(max) that replaces text in
SQL Server 2005?

Thanks again

Dan
Erland Sommarskog wrote:

Quote:

Originally Posted by

Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

select
@.profileid = min([id]),
@.ptr1 = MIN(textptr(text1))
from #holding
where [id] @.profileid

>
This does not look good. Is there are any reason to assume that text
pointers are aligned with id?
>
Either do
>
SELECT @.profileid = MIN(id)
FROM #holding
WHERE id @.profileid
>
SELECT @.ptr1 = textptr(text1) FROM #holding WHERE id = @.profileid
>
Or run a second cursor over #holding.
>
And, yeah, this is ugly, painful and no fun at all. But it's the only
alternative.
>
In SQL 2005 there are new data types that replaces text & co, and
which does not have all the restrictions of text. Or the need to
use UPDATETEXT...
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dan (dan_barber2003@.hotmail.com) writes:

Quote:

Originally Posted by

Is is the varchar(max) that replaces text in SQL Server 2005?

Yes.

And to make it clear, text is still there but deprecated. You will
have to make an explicit change to the tables to use the new goodies.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx