Problem with SQL Server 2000 DTS Package:Column Delimiter not found

I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.

any help on this is appreciated.

Thanks
Venki

Found the actual problem. The error record number that DTS shows is not accurate. Acutal error was at 8790 th record but the DTS shows that the error is at 8000. The data has Quotes in it which was causing the problem.

Thanks
Venki|||

Hi

I'm having the exact same problem, with sp4 installed, and i can't find a solution

did u find a way to solve that problem?

Problem with SQL Server 2000 DTS Package:Column Delimiter not found

I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.

any help on this is appreciated.

Thanks
Venki

Found the actual problem. The error record number that DTS shows is not accurate. Acutal error was at 8790 th record but the DTS shows that the error is at 8000. The data has Quotes in it which was causing the problem.

Thanks
Venki|||

Hi

I'm having the exact same problem, with sp4 installed, and i can't find a solution

did u find a way to solve that problem?

Problem with size of text of title of column.

Hello :

In a title of a column of a table, I put a too long text, with sorting, but when I spread (display) the report has a problem on text of the title of the column there.

The probleme settles (arises) when I activate the sorting to post (show) the image of sorting.

How to fit the size of the cell to the text?

Thenk's.

.......|||Please click the Report Abuse link only if you have abuse to report. It is not going to attract attention to your post.

Problem with Simple ScriptTask

I am new to SSIS 2005. we have a text file with large amount of data. we need to transform it to a database table. Before truncating and loading the table every time the package runs, we want to check with a column(date) in db with file creation time. So for that I created two variables(A,B(value:default N)). And in the data flow, using oledb source got the column value from db and then I passed it to script component. In that script component I take the input as the value from db and checked it with file creation time if its less than the file creation time(date) then make the other variable (B) as Y else N.
Now In the control flow I have dataflow and sql task so using prescendense constraint, I kept as constraint and expression and make the expression as @.B == "Y" to make the flow from dataflow to sql script.
Dont know whats wrong but I think the variable value of B is not changing or not coming to control flow from data flow.
Please help me over here.
Thanks,I really don't understand what you're trying to do here but it sounds as though at some point you are setting the value of a variable from within a data-flow script component.
Why are you doing this? I can't think of many situations where it would be a good idea.

-Jamie|||I am a bit confused as to what you want to do here. If you want to

check a file date then you would not be doing this in the pipeline. It

would be in the Control flow.

Here is what I presume you are wanting please correct me if I am wrong

Check a File date in a DB table with the date of the file at which you

are looking

Based on what is returned you carry on with the package (Data Flow Task)

or not.

Is that right?

Here is how you would probably solve this one

ExecuteSQL task to get the date from the DB Table

Script Task to get the date on the file in question

Same script task compare your two dates

Set an external variable to a value.

Have a conditional precedence constraint that executes the Data Flow

task based on whether the variable value returned is good AND the Script

task succeeds.

Is that it?

Allan

"PavaniPolu@.discussions.microsoft.com"

wrote in message

news:17d9c02f-2e8c-46c1-b1bc-ee650b8d6b5b@.discussions.microsoft.com:

> I am new to SSIS 2005. we have a text file with large amount of data. we

> need to transform it to a database table. Before truncating and loading

> the table every time the package runs, we want to check with a

> column(date) in db with file creation time. So for that I created two

> variables(A,B(value:default N)). And in the data flow, using oledb

> source got the column value from db and then I passed it to script

> component. In that script component I take the input as the value from

> db and checked it with file creation time if its less than the file

> creation time(date) then make the other variable (B) as Y else N.

> Now In the control flow I have dataflow and sql task so using

> prescendense constraint, I kept as constraint and expression and make

> the expression as @.B == "Y" to make the flow from dataflow to sql

> script.

> Dont know whats wrong but I think the variable value of B is not

> changing or not coming to control flow from data flow.

> Please help me over here.

> Thanks,sql

Problem with Saving data in SQL Server

Hi,

I have a problem. The thing is that when I enter the values in a text boxes like evendate.text = "02/11/07" and desc.text="hello how r u" then I click on Save Button and its save in the SQL DATABASE but after this if I click on Refresh Button every time from the internet Explorer its automatically again save the data in the database although I have empty the textboxes.

The code is below:

<script runat="server">

sub SaveEvent(sender as object, e as EventArgs)

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")

dim cmd as new SQLCommand("insert into events values('" & desc.text & "','" & DateTime.Parse(eventdate.text) & "')",con)

con.open()

cmd.executenonquery()

con.close()
clearform()
display()

end sub

sub display()

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("select * from events",con)

con.open()

dim SDR as SQLDataReader
SDR = cmd.ExecuteReader()


if SDR.HasRows = true then
ViewEvents.DataSource = SDR
ViewEvents.DataBind()

end if

con.close()


end sub

sub clearform()
eventdate.text=""
desc.text=""

end sub

sub Reset(sender as object, e as EventArgs)
clearform()

end sub
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>

<style type="text/css">

#display_prod{float:left;}

#prod{margin-top:0;}

</style>

</head>
<body>

<table border="1" align="center" cellpadding="5" cellspacing="0">
<tr>
<td valign="top">

<form id="prod" runat="server" enctype="multipart/form-data">
<table id="display_prod" width="554" border="1" cellspacing="0" cellpadding="0" align="left" height="400">
<tr valign="top" height="20">
<td> </td>
<td>
<table border="1" align="center" cellpadding="10" cellspacing="0">
<tr>
<td>PRODUCTS DESCRIPTION <asp:Label runat="server" ID="lbl"></asp:Label></td>
</tr>
</table>
</td>
</tr>

<tr valign="top">
<td>
<table align="left" id="menu" width="150" border="1" cellpadding="0" cellspacing="0">
<tr align="center">

<td><a href="http://links.10026.com/?link=home.aspx">Main</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=Events.aspx">Change Password</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=events.aspx">Events</a></td>
</tr>
</table>
</td>

<td align="left">
<table width="390" border="1" cellpadding="0" cellspacing="0">
<tr>
<td width="100" >Enter Date :</td>
<td width="243" ><asp:TextBox runat="server" ID="eventdate" BackColor="#FFFFFF" Width="100"></asp:TextBox> mm/dd/yy</td>
</tr>
<tr>
<td>Description :</td>
<td><asp:TextBox runat="server" ID="desc" TextMode="MultiLine" Columns="20" Rows="3" BackColor="#FFFFFF" MaxLength="200"></asp:TextBox> max 200</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button runat="server" ID="btnsubmit" Text="Save" OnClick="SaveEvent"></asp:Button>
<asp:Button runat="server" ID="btnreset" Text="Reset"></asp:Button>
</td>
</tr>
</table>

<table width="390" border="1" align="left" cellpadding="0" cellspacing="0">
<tr>
<td>
<asp:DataGrid runat="server" ID="ViewEvents" AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td><asp:Label runat="server" ID="eventname" Text='<%#container.DataItem("event_name")%>' ></asp:Label></td>

<td><asp:Label runat="server" ID="pdesc" Text='<%# trim(DataBinder.Eval(Container.DataItem,"event_date","{0:MM/dd/yyyy}")) %>' ></asp:Label></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
</td>
</tr>
</table>
</td>
</tr>
</table>

</form>

</td>
</tr>
</table>
</body>
</html>

it will happend.. on think you can do after successfully save the data you can redirect the page with same URL.

|||

Thanks its solved now!

Here is a structure of my table

create table events
(
event_id integer primary key identity(1,1),
event_name varchar(200),
event_date datetime,
)

Note: The event_id is a automacally field and we dont need to insert record in this.

for example

i have inserted 10 records in a table

then i deleted all records manually from sql server

but when again I save the record the event_id field shows started from 11 even the table was empty, it should be start from 1?

Thanks for the co-operation

Problem with saving Chinese.

Hi.. I'm trying to save text into SQL 2000 database.

When user enter text in text box , the text save into variable , and show it in confirm page , after save the text to database, all the text turned into "??"

I try to view the data in SQL enterprise Manager / Web Matrix / ASP web page gridview , all of them showing the text fields in "??"

Then I try to add record which come with SQL 2000 enterprise Manager.After save the record , the chinese also turned into "??"

Is there something I need to set for database or server?

Is your datatype VARCHAR, TEXT or CHAR as the datatype on the table columns?

If VARCHAR change to NVARCHAR. If TEXT change to NTEXT. If CHAR change to NCHAR.

If you are using stored procedures (and you should be), you will to amend the parameter declaration on them.

You might want to change the collating sequence on your database to one more appropriate to Chinese (if collating sequence is meaningful for that language), but otherwise you should not need to change anything at the database level.

If this reply provides the anser to your question, please mark as such.

|||all the text are stored in varchar... I'll try to change it to nvarchar|||

yea... it works when I change all the text field type to nnvarchar..

Problem with ROWTERMINATOR

I am trying to import data from the text file using BULK INSERT command.

BULK INSERT test.s1_test

FROM 'D:\s1.txt'

WITH

(

CODEPAGE = 1250,

FIELDTERMINATOR = '|',

FIRSTROW = 2,

ROWTERMINATOR = '|\r\n'

)

In the text file in the end of each row is symbol which looks like a square - you can see it here

If I use rowterminator '|\n' of '|\r\n', 0 row(s) affected.

Please, can you help me?

hi,

are you sure it's a CR + LF carachter?

it this is the case, data should show it, but your pic does not..

open it with a hex editor...

regards

Problem with Report viewer

This is a multi-part message in MIME format.
--=_NextPart_000_0006_01C6EDDF.D2BB2D90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I recently upgraded by ASP.Net 1.1 application to 2.0. 1.1 application = was using awReportViewer to view the reports.
But, when upgraded, awreportViewer doesnt display the RS2005 = enhancements like sorting, etc...so I decided to replace it with Report = Viewer control in VS2005.
Now the problem starts, Whenever I try accesing the reports, it gives me = error "Client found response content type of '', but expected = 'text/xml'.
The request failed with an empty response."
This is how I have coded,
ReportViewer1.ProcessingMode =3D ProcessingMode.Remote
ReportViewer1.ServerReport.ReportServerUrl =3D New = Uri(http://localhost/ReportServer/reportservice.asmx)
ReportViewer1.ServerReport.ReportPath =3D reportname
//Some code to set parameters in Report Paramater "parameters"
ReportViewer1.ServerReport.SetParameters(parameters)
My questions:
1. Am I going wrong somewhere in the above code?
2. Can awReportViewer be modified / upgraded so that it can support = RS2005 features like sorting, multivalued parameters?
TIA
Tanmaya
--=_NextPart_000_0006_01C6EDDF.D2BB2D90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,

I recently upgraded by ASP.Net 1.1 = application to 2.0. 1.1 application was using awReportViewer to view the = reports.
But, when upgraded, awreportViewer = doesnt display the RS2005 enhancements like sorting, etc...so I decided to replace it = with Report Viewer control in VS2005.

Now the problem starts, Whenever I try = accesing the reports, it gives me error "Client found response content type = of '', but expected 'text/xml'.The request failed with an empty response."

This is how I have coded,

ReportViewer1.ProcessingMode =3D ProcessingMode.Remote
ReportViewer1.ServerReport.ReportServerUrl =3D New Uri(http://localhost/ReportServer/reportservice.asmx)
ReportViewer1.ServerReport.ReportPath =3D = reportname
//Some code to set parameters in Report = Paramater "parameters"
ReportViewer1.ServerReport.SetParameters(parameters)
My questions:
1. Am I going wrong somewhere in the = above code?
2. Can awReportViewer be modified / = upgraded so that it can support RS2005 features like sorting, multivalued = parameters?
TIA
Tanmaya
--=_NextPart_000_0006_01C6EDDF.D2BB2D90--On Thu, 12 Oct 2006 09:21:39 +0530, "Tan"
<tanmaya_kulkarni@.persistent.co.in> wrote:
>Hi,
>I recently upgraded by ASP.Net 1.1 application to 2.0. 1.1 application was using awReportViewer to view the reports.
>But, when upgraded, awreportViewer doesnt display the RS2005 enhancements like sorting, etc...so I decided to replace it with Report Viewer control in VS2005.
>Now the problem starts, Whenever I try accesing the reports, it gives me error "Client found response content type of '', but expected 'text/xml'.
>The request failed with an empty response."
>This is how I have coded,
>ReportViewer1.ProcessingMode = ProcessingMode.Remote
>ReportViewer1.ServerReport.ReportServerUrl = New Uri(http://localhost/ReportServer/reportservice.asmx)
>ReportViewer1.ServerReport.ReportPath = reportname
>//Some code to set parameters in Report Paramater "parameters"
>ReportViewer1.ServerReport.SetParameters(parameters)
>My questions:
>1. Am I going wrong somewhere in the above code?
>2. Can awReportViewer be modified / upgraded so that it can support RS2005 features like sorting, multivalued parameters?
>TIA
>Tanmaya
Dunno but I've never added the /reportservice.asmx at the end fo the
ReportServerURL. I've just used http://localhost/ReportServer.
Actually I set that at design time so I don't know if that makes a
difference.

Problem with Query

I need to replace the text (III|xII) with (1II|xII)'. For that i created the following query.

SELECT (REPLACE((STUFF(Col1,1,1,'1')),'I','1')) FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

The problem with this query is that it replaces all '|' with '1'. ie, it gives (111|x11).

Could anyone ple show me what's wrong with the query.

All help appreciated.

Thanks,

The following code will give you the flexibility to change any char in the string, at any position, at any length. This function returns max 15 chars (but that’s easy to change)…

CREATE FUNCTION dbo.FixString
(
@.DataString varchar(15),
@.StartPosition smallint,
@.StartLength smallint,
@.SeacrhString varchar(15),
@.ReplaceString varchar(15)
)
RETURNS varchar(15)
AS
BEGIN

DECLARE @.Part1 varchar(15),
@.Part2 varchar(15),
@.Part3 varchar(15)

SET @.Part1 = SUBSTRING(@.DataString, 1, @.StartPosition - 1)
SET @.Part2 = REPLACE(SUBSTRING(@.DataString, @.StartPosition, @.StartLength), @.SeacrhString, @.ReplaceString)
SET @.Part3 = SUBSTRING( @.DataString, @.StartPosition + @.StartLength, LEN(@.DataString) - (@.StartPosition + @.StartLength) + 1 )

RETURN @.Part1 + @.Part2 + @.Part3
END
GO

SELECT dbo.FixString(Col1,1,1,'B','1') FROM SourceData
WHERE Record = (SELECT Record + 2 FROM SourceData WHERE Col1 = 'MILESTONES' AND Heading = 'Milestones')
AND Heading = 'Milestones'

Happy SQL'n,
Kent Howerter

|||You don't need the REPLACE since it will replace all occurrences of the specified string. STUFF is enough in this case since you are using the length parameter to delete the character at specified position and insert the new one.

Problem with page breaks and large amount of text

What have folks here done with large amounts of text in a table with regard to page breaks? I've found that in a PDF the page break happens before the large block of text starts regardless of where it starts on the page. Even if there is 3/4 of the page empty it will always page break.

This also happens in preview mode in VS2003 as well. The weird thing is that the second page is very long, almost twice as long as the regular page. The third page is ok. This should be a 2 page report but because of the page break it's a 3 page report. I can post samples if required.

Any help is appreciated.

Thanks

Richard.

Here's an example. To illustrate, I'm just pulling back the Article Title and the Article Details. I made the table background dark just so to distinguish it from the rest of the page.

http://www.1uvaknd.com/pagebreaktest.pdf

This is in VS2003 preview mode. Take a look at page 2. The page size is huge in comparison with the other pages.

http://www.1uvaknd.com/pagebreaktest_page1.jpg
http://www.1uvaknd.com/pagebreaktest_page2.jpg
http://www.1uvaknd.com/pagebreaktest_page3.jpg

Anyone else run into this?

Thanks

Richard.

|||

If you ever resolved this I would be interested in your resolution as I am having a very simular issue.

Thank You

Ed

|||

EdneyHolder:

I have come up with a solution/workaround. Put all the tables into a List object. Then take the large text column out of the table and place it in the list object itself. As long as the large text column is NOT in a table but in a List object - it seems to work much better.

Richard.

Problem with osql.exe -L

This is a multi-part message in MIME format.
--=_NextPart_000_0033_01C566E6.B2395460
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Everyone,
I'm facing a small problem. Whenever I execute osql.exe -L command it = lists all the server on the network except my local server instance (not = default instance) and other similar server instance names on the = network.
Earlier it use to list all the server instance but all of a sudden = stopped listing.
What can be the problem? Please help me to rectify this issue.
Thanks in adv.
Regards,
BGS
--=_NextPart_000_0033_01C566E6.B2395460
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Everyone,I'm facing a small problem. Whenever I execute osql.exe = -L command it lists all the server on the network except my local = server instance (not default instance) and other similar server instance names = on the network.Earlier it use to list all the server instance but all = of a sudden stopped listing.What can be the problem? Please help me = to rectify this issue.Thanks in adv.Regards,BGS
--=_NextPart_000_0033_01C566E6.B2395460--OSQL -L uses the ODBC SQLBrowseConnect functionality. It's
not guaranteed to give a complete list due to broadcast
issues, network routers/subnet issues, instances may not
respond within the timeout period, etc. You can find issues
listed here:
http://www.sqldev.net/misc/ListSQLSvr.htm
-Sue
On Wed, 1 Jun 2005 20:15:46 +0530, "Gururaj Badam"
<gururajb@.msn.com> wrote:
>Hi Everyone,
>I'm facing a small problem. Whenever I execute osql.exe -L command it lists all the server on the network except my local server instance (not default instance) and other similar server instance names on the network.
>Earlier it use to list all the server instance but all of a sudden stopped listing.
>What can be the problem? Please help me to rectify this issue.
>Thanks in adv.
>Regards,
>BGS|||Sue,
Is ListAvailableSQLServers in SQL-DMO is also same as "osql -L" command?
Actually I'm trying to populate a ComboBox with list of all the SQL Servers
on the network and set the default to the Local Server (desired instance).
Can you suggest some other better mechanism which can be used to acheive the
same?
-BGS|||ListAvailableServers is also based on the ODBC function
SQLBrowseConnect. If you need a guaranteed list and are
running SQL 2000, you could register all SQL Servers in AD
and go through AD to list all of SQL Servers.
-Sue
On Thu, 2 Jun 2005 09:57:10 +0530, "Gururaj Badam"
<gururajb@.msn.com> wrote:
>Sue,
>Is ListAvailableSQLServers in SQL-DMO is also same as "osql -L" command?
>Actually I'm trying to populate a ComboBox with list of all the SQL Servers
>on the network and set the default to the Local Server (desired instance).
>Can you suggest some other better mechanism which can be used to acheive the
>same?
>-BGS
>|||Sue,
Actually I'm not using SQL 2k but MSDE installation. It is a product, I
can't ask the client to install SQL Server 2000.
That's the reason I'm packing the MSDE along with the application
installation package.
-BGS
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:g9ut91daj141d35g8v2dn133q713uo9ieq@.4ax.com...
> ListAvailableServers is also based on the ODBC function
> SQLBrowseConnect. If you need a guaranteed list and are
> running SQL 2000, you could register all SQL Servers in AD
> and go through AD to list all of SQL Servers.
> -Sue
>|||MSDE is SQL Server, so everything that Sue mentioned still applies.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gururaj Badam" <gururajb@.msn.com> wrote in message news:OV9E6C3ZFHA.2916@.TK2MSFTNGP14.phx.gbl...
> Sue,
> Actually I'm not using SQL 2k but MSDE installation. It is a product, I
> can't ask the client to install SQL Server 2000.
> That's the reason I'm packing the MSDE along with the application
> installation package.
> -BGS
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:g9ut91daj141d35g8v2dn133q713uo9ieq@.4ax.com...
>> ListAvailableServers is also based on the ODBC function
>> SQLBrowseConnect. If you need a guaranteed list and are
>> running SQL 2000, you could register all SQL Servers in AD
>> and go through AD to list all of SQL Servers.
>> -Sue
>|||Tibor,
How can I ask the user of my product to register the SQL Server with the AD,
that looks odd.
Is there any other way to approach this problem?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23AYiWX3ZFHA.3040@.TK2MSFTNGP14.phx.gbl...
> MSDE is SQL Server, so everything that Sue mentioned still applies.
> --
> Tibor Karaszi, SQL Server MVP