Problem importing sequential text files

Hello everybody

I have a rather strange problem I guess. Most likely I did something wrong but I can't find it right now. So this is what happens.

Well, I receive a number of text files and I have to import them into SQL server 7. I use Vbscript (ActiveX import) to modify some data before putting it all into my tables. Sql server imports all records perfectly but sometimes the sequential order of the text file is not respected when I look into the table. I'd like to explain this a bit better.

Let's say I have 3 types of records in the textfile. "NATIONAL" , "DISTRICT" and "AREA". The textfiles (fixed columns) look a bit like this.

0123321334253 NATIONAL 432748234
2347234712357 DISTRICT 234852348
2348235724137 DISTRICT 242752348
6975067587684 DISTRICT 664568483
6534534537453 AREA 324537453
6578305607965 AREA 485834690
3247573284920 AREA 453753648
6795673435734 AREA 457352888
4564573486943 AREA 583475386

So the order of the records is really important. Mostly SQL Server imports all records correctly but sometimes (without changing the inputfiles nor the DTS package !!!!) it mixes up the order of the records. After importation into a table it could look like this.

3247573284920 AREA 453753648
6795673435734 AREA 457352888
0123321334253 NATIONAL 432748234
2347234712357 DISTRICT 234852348
6534534537453 AREA 324537453
6578305607965 AREA 485834690
4564573486943 AREA 583475386
2348235724137 DISTRICT 242752348
6975067587684 DISTRICT 664568483

How is that possible? Most likely it's a setting I configured wrongly but what worries me is that mostly everything goes right and without changing anything it could be mixed up.

Can you please help me?

Anyway, thanks a lot!

Greetz,

Bart.SQL Server will maintain the record order as inserted UNLESS you apply a clustered index. I would check for indexes the next time this happens. Can anyone esle in your organization change the schema?

Also, do you use only one process to read the input files? Two process could also produce a mixed order.

Could you add an identity column to preserve the inserted order?|||Hello Paul,

First of all a big thank you for your reply.

Well, let me answer your questions first ;)

I don't use clustered indexes and I don't use multiple read processes. So I only import one text file at a time.

I have also tried to add an identity field to a record. In my ActiveX import script (vbscript) I test on the used level. If it's "NATIONAL" I put "1" into the ID column, when it's "RESEAU" I put "2" into the ID column and so on.

So I import everything into a temporary table and afterwards I put everything with an "ORDER BY ID ASC" into the final table.

You won't believe it, but even after that "ORDER BY" I SOMETIMES, not always, have a wrong order into the final table. So it's possible that in the final table I have such an ID column

1
2
2
2
2
3
3
3
3
3
4
4
3
3
3
5
5
5

:mad: You can surely understand that this irritates me a lot.

How is that possible?

Greetz, :confused:

Bart.|||By definition record order is unimortant in a relational structure. SQL Server does not guarantee row order because internally it stores the data in pages that may or may not be sequential.

Why not just apply the 'ORDER BY' clause when you retrieve the records from the database -- that way it makes no difference which order they are stored. An index on that field would speed this operation up a great deal.

BTW, IDENTITY fields are auto-incrementing -- you should not be trying to insert discrete values.

Regards,
Jason Woosley
SQL Server DBA|||Thanks for your reply Jason.

Your last solution is exactly what I tried last week. And indeed, this works perfectly. I only forgot to post this answer onto the messageboard.

Greetz,

Bart.sql

problem importing csv delimited text file into a sql server 2005 table

I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", " The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only. If I have a comma within a address field for example then the data gets imported into the wrong fields. What can I use to identify that the text qualifier is ". I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly. I thank you in advance for any response or suggestion you may have.

BULK INSERT AdventureWorks.dbo.MbAddress

FROM 'a:\mbAddress.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR=',',

ROWTERMINATOR='\n',

CODEPAGE = '1252',

KEEPIDENTITY,

KEEPNULLS,

FIRSTROW=2)

Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.

"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403 W. Kline Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501 Dunford Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115 Cappaert Rd #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728 Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700 S. 51st St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509 N. Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080 E. Mountain View Lake Rd. #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129 N6889 Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402 64th Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83 Sweetbriar Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83 Sweetbriar BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8 127 Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"

Hi

You can do this by using a formatfile. You can define each field in a formatfile and specify a differend field terminator for each field.

For example:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="""," MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="""\n" MAX_LENGTH="30"/>

[....]

</RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLNVARCHAR"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>

=== Edited by Hatzi74 @. 25 Apr 2006 2:49 PM UTC===
Edit2: You could also use the Data Import Wizzard... Select "Flatfile" as source, and then match the fielddelimiters for every field the way you need them to be...

For example... After a Text -> Number you would need --",-- as a delimiter if you go from text -> text you will need --","-- as a delimiter and when coming from number -> text you need --,"--

Hope this makes sense. Also the row delimiter could be adjusted to match --"\n-- if the last field is a text field. But this will leave the field "dirty" (It will include a tailing ")

|||

Thank you for your quick reply. I did try a non xml format file using the bcp command and it came out like the info below. However, It worked on importing but I still had the same problem with the commas as the delimiter. I wanted to add the quotes as a delimiter but I could not figure out how to do that in this type of file.

I will try a xml format this time but I am not sure by your instructions how to tell it that the file has quotes for the text qualifiers. I think you are saying I need a "," as a delimiter

Thank you so much for your time and effort. I am going to try it.

Most sincere,

Sarah

my format file below (.fmt type)

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "," 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "," 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "," 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "," 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "," 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "," 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "," 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "," 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 "," 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "," 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "," 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

|||

Try this:

3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",""" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 """," 5 Address1 SQL_Latin1_General_CP1_CI_AS

|||

I am trying that, but it does not seem to be working.

Sarah

|||

I think it is getting confused with the quotes because they seem to be reserved for the program or something. I get an error.

do you know the bcp command I need to get the xml format? I will try that as well.

Sarah

|||

Hi

I just wanted to thank you for your reply and that you somewhat directed me into the right direction.

I just wanted to let you know that I finally figured it out. I had to strip the " quotes out of the ascii file and replace them with | pipes and then I created the fmt (format file) and imported the data. Here is a copy of the fmt file just for your own information. I had to tell it exactly where the pipes started and ended for each field delimter. I tried setting up the format file to look for the quotes but it had a problem with that because " " were being used in the format file.

Anyways I got it and now I am having a problem accessing the A:\ drive, using the bulk insert command. sql server thinks I am accessing the actual servers A:\ and not my local drive. wow what a nightmare just to read a simple ascii file into a table. Unfortunatley the operator has to import data from floppy diskettes. I will repost this problem.

Anyways, thank you very much for your imput.

Most sincere,

Sarah Diane Reid

Sample Format File below:

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",|" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "|,|" 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "|,|" 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "|,|" 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "|,|" 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "|,|" 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "|,|" 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "|,|" 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "|," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 ",|" 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "|," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 ",|" 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "|," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 ",|" 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "|\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

problem getting "LIKE @parameter%" to work

Hello,

I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.

SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'

It works fine in when i type it in manually.

But im using a stored procedure from VS and it will not work with the '%' part

SELECT *
FROM table
WHERE column_name LIKE @.parameter%

Any help or ideas would be greatly appreciated.Hi, my similar line looks like ...

Dim myCommand = New SqlCommand("exec search_telephone '%" & filterValue1 & "%'", myConnection)

... where search_telephone is a stored procedure expecting an input of part of a surname.

NOTE the 2 percentage characters.

Richard|||Like uses a string as its input. So you'd need to use '%' + @.param + '%'|||Is your SQL running inside a stored proc? That's what it sounds like to me.

If that's the case, do something like this:

declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''' + @.parameter + ''''
EXEC ( @.strSQL )

I do this all the time in my stored procs for searches. I haven't found another way to do this. The trick is getting the number of single quotes right.|||It should be:

declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''%' + @.parameter + '%'''
EXEC ( @.strSQL )

but you get the point.

For help debugging these types of "dynamically generated" SQL statements, use PRINT ( @.strSQL ) and run it in query analyzer.|||in your stored procedure this should work and will not require SELECT permissions on the table like an EXEC(@.sql) would.

CREATE PROC [some_search]
 @.Search nvarchar(50)
AS
 Declare @.LikeSearch nvarchar(52)
 -- you could also add do '%' + @.Search + '%' depending on how you want the search to work
 SET @.LikeSearch = @.Search + '%'
 SELECT *
 FROM table
 WHERE column_name LIKE @.LikeSearch

sql

Problem creating Job from tsql

This is a multi-part message in MIME format.
--=_NextPart_000_008B_01C43846.EBF4ED60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm attempting to create a job on a client's server using tsql over a = VPN.
* I've created this procedure before using the same tsql and VPN
* The same tsql works presently on my server
* I'm not having any other problems on the server
* We're using NT auth, and my userid is sa
When I try to create a job from query analyzer, I get:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite = (send()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thanks!
Greg
--=_NextPart_000_008B_01C43846.EBF4ED60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I'm attempting to create a job on a client's = server using tsql over a VPN.

* I've created this procedure before using the same = tsql and VPN
* The same tsql works presently on my = server
* I'm not having any other problems on the = server
* We're using NT auth, and my userid is = sa

When I try to create a job from query analyzer, I get:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). Server: = Msg 11, Level 16, State 1, Line 0 General network error. = Check your network documentation.

Connection Broken

Any ideas?

Thanks!
Greg
--=_NextPart_000_008B_01C43846.EBF4ED60--This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C438DD.3B28E440
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Seems that 'sp_add_jobstep' is generating the error. Anyone have a =similar experience?
--
Greg C
"Greg C" <uce@.ftc.com> wrote in message =news:QAxoc.88432$Dn1.44627@.fe2.texas.rr.com...
I'm attempting to create a job on a client's server using tsql over a =VPN.
* I've created this procedure before using the same tsql and VPN
* The same tsql works presently on my server
* I'm not having any other problems on the server
* We're using NT auth, and my userid is sa
When I try to create a job from query analyzer, I get:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite =(send()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thanks!
Greg
--=_NextPart_000_000D_01C438DD.3B28E440
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Seems that 'sp_add_jobstep' is generating the =error. Anyone have a similar experience?
-- Greg C
"Greg C" =wrote in message news:QAxoc.88432$Dn1=.44627@.fe2.texas.rr.com...

I'm attempting to create a job on a client's =server using tsql over a VPN.

* I've created this procedure before using the =same tsql and VPN
* The same tsql works presently on my =server
* I'm not having any other problems on the server
* We're using NT auth, and my userid is =sa

When I try to create a job from query analyzer, I get:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). =Server: Msg 11, Level 16, State 1, Line 0 General network =error. Check your network documentation.

Connection Broken

Any ideas?

Thanks!
Greg

---

--=_NextPart_000_000D_01C438DD.3B28E440--

problem creating DTS package

Hi,
I am trying to create a DTS package wherein the data from the sql
server is transfered to a text file. I am using the Transform Data Task
to transfer the data.In Transform Data task properties, I build the
query and able to preview the data but when i click OK in the Transform
Data task properties screen I get the following error.
"To continue , define transformations. You can make changes to the
default transformations"
I am not able to define the transformations. Has anyone had this
problem and know a way to resolve it. I would appreciate if anyone
could help me.
Thanks
MBare you working on the server or throught the EMC remotely?|||are you working on the server or through the EMC remotely?|||Mohan wrote:
> Hi,
> I am trying to create a DTS package wherein the data from the sql
> server is transfered to a text file. I am using the Transform Data Task
> to transfer the data.In Transform Data task properties, I build the
> query and able to preview the data but when i click OK in the Transform
> Data task properties screen I get the following error.
> "To continue , define transformations. You can make changes to the
> default transformations"
> I am not able to define the transformations. Has anyone had this
> problem and know a way to resolve it. I would appreciate if anyone
> could help me.
> Thanks
> MB
>
You need to define the transformations first before you can click OK.
The text file must be defined with column names and then the
transformations can be assigned.|||I am actually using the remote connection to work directly on the
server.
i was able to get it to work if i selected some of the columns, but not
all. Is there a limit on the number of columns?
Let me explain in more detail.
When i am setting up the transform data task, I build the query and
then go to the destination tab. I click the Execute button to define
the columns for the destination.
The problem is that when i select all the columns from the table then
nothing happens when i click Execute. There are no columns defined and
when i click on the Define Columns button it gives an error and
terminates Enterprise Manager.
I am not sure whats going on. Really appreciate your help
Thanks
MB|||Mohan wrote:
> I am actually using the remote connection to work directly on the
> server.
> i was able to get it to work if i selected some of the columns, but not
> all. Is there a limit on the number of columns?
> Let me explain in more detail.
> When i am setting up the transform data task, I build the query and
> then go to the destination tab. I click the Execute button to define
> the columns for the destination.
> The problem is that when i select all the columns from the table then
> nothing happens when i click Execute. There are no columns defined and
> when i click on the Define Columns button it gives an error and
> terminates Enterprise Manager.
> I am not sure whats going on. Really appreciate your help
> Thanks
> MB
>
What service pack is SQL Server running on? That may be part of the
issue if you have a large amount of columns to export.|||Its running on SP3.|||Mohan wrote:
> *Its running on SP3. *
I figured out a fix without having to upgrade to SP4. If you don't
upgrade your client tools to SP3 on your computer you don't have the
errors. I had to uninstall my client tools and re-install the client
tools on my machine (not on the server). There were no issues. Its
not a pretty fix, but I can always use any new functionality on the
server itself. Good luck.
dmasuda
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message2000028.html|||This thread is confusing... ..
What was the error you got was it "Invalid Class String" Error?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"dmasuda" wrote:

> Mohan wrote:
> I figured out a fix without having to upgrade to SP4. If you don't
> upgrade your client tools to SP3 on your computer you don't have the
> errors. I had to uninstall my client tools and re-install the client
> tools on my machine (not on the server). There were no issues. Its
> not a pretty fix, but I can always use any new functionality on the
> server itself. Good luck.
>
> --
> dmasuda
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message2000028.html
>

problem creating DTS package

Hi,
I am trying to create a DTS package wherein the data from the sql
server is transfered to a text file. I am using the Transform Data Task
to transfer the data.In Transform Data task properties, I build the
query and able to preview the data but when i click OK in the Transform
Data task properties screen I get the following error.
"To continue , define transformations. You can make changes to the
default transformations"
I am not able to define the transformations. Has anyone had this
problem and know a way to resolve it. I would appreciate if anyone
could help me.
Thanks
MB
are you working on the server or throught the EMC remotely?
|||are you working on the server or through the EMC remotely?
|||Mohan wrote:
> Hi,
> I am trying to create a DTS package wherein the data from the sql
> server is transfered to a text file. I am using the Transform Data Task
> to transfer the data.In Transform Data task properties, I build the
> query and able to preview the data but when i click OK in the Transform
> Data task properties screen I get the following error.
> "To continue , define transformations. You can make changes to the
> default transformations"
> I am not able to define the transformations. Has anyone had this
> problem and know a way to resolve it. I would appreciate if anyone
> could help me.
> Thanks
> MB
>
You need to define the transformations first before you can click OK.
The text file must be defined with column names and then the
transformations can be assigned.
|||I am actually using the remote connection to work directly on the
server.
i was able to get it to work if i selected some of the columns, but not
all. Is there a limit on the number of columns?
Let me explain in more detail.
When i am setting up the transform data task, I build the query and
then go to the destination tab. I click the Execute button to define
the columns for the destination.
The problem is that when i select all the columns from the table then
nothing happens when i click Execute. There are no columns defined and
when i click on the Define Columns button it gives an error and
terminates Enterprise Manager.
I am not sure whats going on. Really appreciate your help
Thanks
MB
|||Mohan wrote:
> I am actually using the remote connection to work directly on the
> server.
> i was able to get it to work if i selected some of the columns, but not
> all. Is there a limit on the number of columns?
> Let me explain in more detail.
> When i am setting up the transform data task, I build the query and
> then go to the destination tab. I click the Execute button to define
> the columns for the destination.
> The problem is that when i select all the columns from the table then
> nothing happens when i click Execute. There are no columns defined and
> when i click on the Define Columns button it gives an error and
> terminates Enterprise Manager.
> I am not sure whats going on. Really appreciate your help
> Thanks
> MB
>
What service pack is SQL Server running on? That may be part of the
issue if you have a large amount of columns to export.
|||Its running on SP3.
|||

Quote:

Originally posted by Mohan
Its running on SP3.

I figured out a fix without having to upgrade to SP4. If you don't upgrade your client tools to SP3 on your computer you don't have the errors. I had to uninstall my client tools and re-install the client tools on my machine (not on the server). There were no issues. Its not a pretty fix, but I can always use any new functionality on the server itself. Good luck.|||This thread is confusing... ..
What was the error you got was it "Invalid Class String" Error?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"dmasuda" wrote:

> Mohan wrote:
> I figured out a fix without having to upgrade to SP4. If you don't
> upgrade your client tools to SP3 on your computer you don't have the
> errors. I had to uninstall my client tools and re-install the client
> tools on my machine (not on the server). There were no issues. Its
> not a pretty fix, but I can always use any new functionality on the
> server itself. Good luck.
>
> --
> dmasuda
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message2000028.html
>

problem creating DTS package

Hi,
I am trying to create a DTS package wherein the data from the sql
server is transfered to a text file. I am using the Transform Data Task
to transfer the data.In Transform Data task properties, I build the
query and able to preview the data but when i click OK in the Transform
Data task properties screen I get the following error.
"To continue , define transformations. You can make changes to the
default transformations"
I am not able to define the transformations. Has anyone had this
problem and know a way to resolve it. I would appreciate if anyone
could help me.
Thanks
MBare you working on the server or throught the EMC remotely?|||are you working on the server or through the EMC remotely?|||Mohan wrote:
> Hi,
> I am trying to create a DTS package wherein the data from the sql
> server is transfered to a text file. I am using the Transform Data Task
> to transfer the data.In Transform Data task properties, I build the
> query and able to preview the data but when i click OK in the Transform
> Data task properties screen I get the following error.
> "To continue , define transformations. You can make changes to the
> default transformations"
> I am not able to define the transformations. Has anyone had this
> problem and know a way to resolve it. I would appreciate if anyone
> could help me.
> Thanks
> MB
>
You need to define the transformations first before you can click OK.
The text file must be defined with column names and then the
transformations can be assigned.|||I am actually using the remote connection to work directly on the
server.
i was able to get it to work if i selected some of the columns, but not
all. Is there a limit on the number of columns?
Let me explain in more detail.
When i am setting up the transform data task, I build the query and
then go to the destination tab. I click the Execute button to define
the columns for the destination.
The problem is that when i select all the columns from the table then
nothing happens when i click Execute. There are no columns defined and
when i click on the Define Columns button it gives an error and
terminates Enterprise Manager.
I am not sure whats going on. Really appreciate your help
Thanks
MB|||Mohan wrote:
> I am actually using the remote connection to work directly on the
> server.
> i was able to get it to work if i selected some of the columns, but not
> all. Is there a limit on the number of columns?
> Let me explain in more detail.
> When i am setting up the transform data task, I build the query and
> then go to the destination tab. I click the Execute button to define
> the columns for the destination.
> The problem is that when i select all the columns from the table then
> nothing happens when i click Execute. There are no columns defined and
> when i click on the Define Columns button it gives an error and
> terminates Enterprise Manager.
> I am not sure whats going on. Really appreciate your help
> Thanks
> MB
>
What service pack is SQL Server running on? That may be part of the
issue if you have a large amount of columns to export.|||Its running on SP3.|||Mohan wrote:
> *Its running on SP3. *
I figured out a fix without having to upgrade to SP4. If you don't
upgrade your client tools to SP3 on your computer you don't have the
errors. I had to uninstall my client tools and re-install the client
tools on my machine (not on the server). There were no issues. Its
not a pretty fix, but I can always use any new functionality on the
server itself. Good luck.
dmasuda
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message2000028.html

problem creating assembly using other assemblies

Hi,

I have developed a Stored procedure to output text to a text file, after doing a lot of reading here.
I want to be able to output data to a Postgres database, to be used for web mapping. I have referenced an Assembly Npgsql.dll so I can send updates to the postgres database when my ms sql database is updated. However, after building my class library, when I try to CREATE ASSEMBLY in sql server 2005 express, it sends out a message:
Msg 10301, Level 16, State 1, Line 1
Assembly 'ClassLibrary1' references assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(error not found)). Please load the referenced assembly into the current database and retry your request.
The error seems to be in the Npgsql.dll assembly, can anybody please shed some light on this.
I tried to load the system.drawing assembly, and can do so in Unrestricted mode. It then spits out the same problem for System.Windows.Forms.dll.
I am still very new to all of this so any help would be appreciated.
Cheers,
Jatz91.

I got around this problem.

I used CREATE ASSEMBLY with PERMISSION_SET=UNSAFE and put the Microsoft.NET Framework Assemblies in the same folder where my built assembly was.

When I used CREATE ASSEMBLY, a warning was spat out for each assembly saying:

Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

So now I run my CLR in UNSAFE mode

Cheers

Problem converting decimal values

Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.

Any help on this ? tnks in advance
Anyone ? It's a very urgent problem, my deadline is approaching and this problem remains. Do i have to replace the points by dot's ? It's the only solution ?
|||I don't know anything about locales, but I guess if I had this problem I'd read the amounts in as strings and then use a Derived Column component to do the necessary string manipulations and data conversion. Hopefully you don't have a lot of them or you have a convenient asynchronous component (like a Union) where you can drop out the string artifacts. Otherwise, you can do the transformation/conversion and drop the artifacts at the same time using an asynchronous script.
|||

What locale have you tried to use? Where did you set it? English (United States) should be available on your machine.

Thanks.

|||First of all tnks for your answers. Well the problem was indeed very simple, i was setting English as the locale and not English (United States), that's what i call a stupid error ;-). Anyway thank you very much for the help