I found a bug in MSSQL DTS import wizard

We have a textfile that we import in Excel(provided by our mainframe folks).
It's fixed width with 142 columns. When I import it in excel or ms access,
it gives me about ~34000 rows. But when I import using DTS package that is
exported to Access mdb, rows becomes ~17000 rows :(
And another hurdle that I'm faced is that there are rows that contains
combined data such as street, city and state all together without spaces.
Unfortunately, we can't modify the source text file. However, I do have a
fieldmarker(save a .txt file) which I always use in Excel so that when I
import it, I can select the area to create a vertical line just right that
marker.
Here is a sample of my fieldmarker.
12345p1234567p12pp123456p
Everytime i see a p, I click it with a mouse to create a vertical line in
fixed width importing. In that case, there are 5 columns. These are all
similar to ms access or excel.
The bug.
Please see this link below about the bug I found in MSSQL DTS Import wizard.
It doesn't happen in Excel or MS Access. Excel and MS Access can handle it
properly.
http://restricted.dyndns.org/dtsimportissue.html
Please help. Solution needed very badly!
Thanks,
Neil"Neil" <neil-on-ht@.restricted.dyndns.org> wrote in message
news:Oj$m4gz5EHA.1292@.TK2MSFTNGP10.phx.gbl...
> We have a textfile that we import in Excel(provided by our mainframe
> folks). It's fixed width with 142 columns. When I import it in excel or ms
> access, it gives me about ~34000 rows. But when I import using DTS package
> that is exported to Access mdb, rows becomes ~17000 rows :(
> And another hurdle that I'm faced is that there are rows that contains
> combined data such as street, city and state all together without spaces.
> Unfortunately, we can't modify the source text file. However, I do have a
> fieldmarker(save a .txt file) which I always use in Excel so that when I
> import it, I can select the area to create a vertical line just right that
> marker.
> Here is a sample of my fieldmarker.
> 12345p1234567p12pp123456p
> Everytime i see a p, I click it with a mouse to create a vertical line in
> fixed width importing. In that case, there are 5 columns. These are all
> similar to ms access or excel.
> The bug.
> Please see this link below about the bug I found in MSSQL DTS Import
> wizard. It doesn't happen in Excel or MS Access. Excel and MS Access can
> handle it properly.
> http://restricted.dyndns.org/dtsimportissue.html
> Please help. Solution needed very badly!
It looks as though DTS is using the CRLF in the first line to define the
line lengths, and concatenating data in the rows because they are too short
and therefore incomplete records. What happens if you trim the first line
back to the same length as the other lines? Or how about padding the lines
out so they are all the same length?
According to http://support.microsoft.com/default.aspx?scid=kb;en-us;271247
if any rows are longer than expected then additional characters appear
wrapped in the DTS wizard, but are actually discarded.
However, in article
http://support.microsoft.com/default.aspx?scid=kb;en-us;247360 it mentions
that DTS expects a fixed length file to have lines of fixed length, and
having shorter lines will cause additional characters to be read from the
next line.
So the answer appears to be that in a fixed length import, make sure every
line is the same length to avoid this problem.
Dan|||"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:uZDTPI05EHA.3076@.TK2MSFTNGP15.phx.gbl...
> It looks as though DTS is using the CRLF in the first line to define the
> line lengths, and concatenating data in the rows because they are too
> short and therefore incomplete records. What happens if you trim the first
> line back to the same length as the other lines? Or how about padding the
> lines out so they are all the same length?
> According to
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271247 if any rows
> are longer than expected then additional characters appear wrapped in the
> DTS wizard, but are actually discarded.
> However, in article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;247360 it
> mentions that DTS expects a fixed length file to have lines of fixed
> length, and having shorter lines will cause additional characters to be
> read from the next line.
> So the answer appears to be that in a fixed length import, make sure every
> line is the same length to avoid this problem.
>
Ok. Then we're out of luck then. I will contact the vendor if there will be
data for those columns in the future. If not, I can just delete it for now
to avoid the problem. It's just that MS Excel and Access support it. I don't
know why they didn't do that for DTS :(
Thanks.
Neil|||Neil,
I don't quite understand your explanation or the online example. You
say this is a fixed-width file, but the example you give online shows a
first line that is much longer than the second line. Fixed-width means
every line of the text file has the same length. Your file has lines of
different lengths, but it also doesn't have a separator character
between each column, so the wizard won't help you, and you'll need to do
something else, I guess.
Maybe one of these will work:
Import using BULK INSERT with a format file.
Preprocess the file with Unix-like utilities to add delimiters, then
import as delimited
Import the file into a staging table with one long column, then split
it up with
insert into FinalDestinationTable
select substring(s,1,4), substring(s,5,7), ...
Steve Kass
Drew University
Neil wrote:
>We have a textfile that we import in Excel(provided by our mainframe folks).
>It's fixed width with 142 columns. When I import it in excel or ms access,
>it gives me about ~34000 rows. But when I import using DTS package that is
>exported to Access mdb, rows becomes ~17000 rows :(
>And another hurdle that I'm faced is that there are rows that contains
>combined data such as street, city and state all together without spaces.
>Unfortunately, we can't modify the source text file. However, I do have a
>fieldmarker(save a .txt file) which I always use in Excel so that when I
>import it, I can select the area to create a vertical line just right that
>marker.
>Here is a sample of my fieldmarker.
>12345p1234567p12pp123456p
>Everytime i see a p, I click it with a mouse to create a vertical line in
>fixed width importing. In that case, there are 5 columns. These are all
>similar to ms access or excel.
>The bug.
>Please see this link below about the bug I found in MSSQL DTS Import wizard.
>It doesn't happen in Excel or MS Access. Excel and MS Access can handle it
>properly.
>http://restricted.dyndns.org/dtsimportissue.html
>Please help. Solution needed very badly!
>Thanks,
>Neil
>
>

I forgot my SA password for my instance of SQL 2000

Hi all,

I have SQL 2000 and I forgot my SA password and cannot login as administrator using windows authentication.

Is there any way to reset SA password?

"Mixed Mode" authentication is enabled.

If I change authentication mode in register to Windows only and then after restarting SQL change it back to Mixed, will it reset SA password to "null"?

Thanks in advance.

If you log onto the machine with the Database server installed and use the local administrator account or a network administrator account you should be able to reset the password.

|||Just in addition to Glenns post and your qustion: It won′t reset the password to null with changing the authentication type back and forth. If you don′t have any GUI to administer your passwords you can use the procedure sp_password passing in a NULL for the old password parameter, as you don′t need the old if you are logged on as a user with sysadmin / securityadmin rights.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||As I said before I can't login even with local Administrator. I don't know what is going on. It is so weird.

I forgot my SA password for my instance of SQL 2000

Hi all,

I have SQL 2000 and I forgot my SA password and cannot login as administrator using windows authentication.

Is there any way to reset SA password?

"Mixed Mode" authentication is enabled.

If I change authentication mode in register to Windows only and then after restarting SQL change it back to Mixed, will it reset SA password to "null"?

Thanks in advance.

If you log onto the machine with the Database server installed and use the local administrator account or a network administrator account you should be able to reset the password.

|||Just in addition to Glenns post and your qustion: It won′t reset the password to null with changing the authentication type back and forth. If you don′t have any GUI to administer your passwords you can use the procedure sp_password passing in a NULL for the old password parameter, as you don′t need the old if you are logged on as a user with sysadmin / securityadmin rights.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||As I said before I can't login even with local Administrator. I don't know what is going on. It is so weird.|||

Try

sp_password NULL,NULL,'sa'

Assuming you never created password at the first place. parameter 1 is Old password, parameters 2 is new password, parameter 3 is user.

I fixed this problem!

In my SSAS SP1, I received an error msg in Aggregation design wizard "A connection cannot be made. Ensure that the server is running ..." while I was tryng to start "Performance gain reaches%" box. I verified that the local source SQL 2005 sample "AdventureWorksDW" is running without a problem.

Make sure your Analysis Server is running.

Aggregation Design Wizard sends commands to Analysis Server that is running aggregation desing algorithm.

Try and open SQL Management Studio and make sure you can connect to Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

While I tried to connect to Analysis Server in SQL Management Studio, I got an error as following -
* I am sure the service of "SQL Server Analysis Services (MSSQLSERVER)" is started on port 2383.

Quotes:

TITLE: Connect to Server

Cannot connect to localhost.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

DIME protocol error: The required bytes cannot be read from the stream. (Microsoft.AnalysisServices.AdomdClient)

BUTTONS:

OK

|||

Open Service control manager and make sure you've started "SQL Server Analysis Services( MSSQLSERVER)" service. That is in case you installed default instance.

If you installed named instance the service name will contain the instance name.

For troubleshooting connectivity issues you can look at some tips on http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks a lot.
I had carefully read your posted URL, but I had NOT found a way to solve the problem yet.
(1) In Service control manager, SQL Server Analysis Services (MSSQLSERVER) is started.
(2) In DOS command console, C:/>telnet localhost 2383 , output a blank screen, means that - port 2383 is on listening on localhost.
so that, I am SURE that
- SQL Server Analysis Services (MSSQLSERVER)" is started and listening on port 2383 -
but when I try to connect to Analysis Services in Microsoft SQL Server Management Studio , the error popup:

Frank X. Huang wrote:

TITLE: Connect to Server

Cannot connect to localhost.

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

DIME protocol error: The required bytes cannot be read from the stream. (Microsoft.AnalysisServices.AdomdClient)

BUTTONS:

OK

|||

Let's try another thing.

On your server machine:

Create a new text file in one of your folders.

Change the extension of the file from .txt to .udl. Doble click on the file and you'd see data link dialog open up.

Switch to the Provider tab and select "Microsoft OLEDB provider for Analysis Services 90"

Go to the Connection tab and type in the server name for the Data Source. Choose Windows auth.

At this moment you should be able to see list of databases on the server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I do like this -

(1) on my server machine(localhost): create a new text file and rename to x.udl;
(2) Double click on the file, and In the popup window which titled "Data Link Properties",
(2.1) In "Provider" tab, I select "Microsoft OLE DB Provider for Analysis Services 9.0",
(2.2) In "Connection" tab, I select "Use Windows NT Integrated security"; Fill "Data Source:" textbox with "localhost"; let "Location:" textbox empty; and then
(2.2.1) if I drop down the list of "Enter the initial catalog to use:", there is an error "An error was encountered in the transport layer." with title "Microsoft Data Link Error".

(2.2.1.1) I have no choice but to click "OK" on the error window, and then another window shows "Login failed. Catalog information cannot be retrieved."
(2.2.2) or, if I click "Test Connection" button, an error show "Test connection failed because of an error in initializing provider. An error was encountered in the transport layer."

Further more, I try some other things -
(1) create a data source with "SQL Native Client"(version 2005.90.2047.00), and then retry all steps.
(2) at step 2.1, I select another Provider "SQL Native Client"(version 2005.90.2047.00); at step 2.2 fill the data source with "localhost"; and then "Test Connection"......It's OK!......or, drop down the database name list, It's OK!.......no error!

By the way, "Database Engine", "Integration Services", and "Reporting Services" are ALL can be connected, ONLY "Analysis Services" CANNOT be connected in the "Microsoft SQL Server Management Studio".

|||(1) I removed and re-installed Microsoft SQL Server 2005 Developer Edition on my Windows XP Professional, yet the error remains.
(2) I, then, downloaded and installed Microsoft SQL Server 2005 SP1, and try again, the error remains.
(3) I also remove and reinstall my Network Interface Card on my IBM ThinkPad, and start all services who can be started on my Operating System, the error remains.
Who can help me, God?|||

Another idea.

This could be a network related. Instead of localhost try to use machine name or IP of your server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks God! I fixed this problem, but I really do not know how and why?

I will do some experiment and try to find it out (this may take me several hours or days or never), but now, I only list what I did -

(1) There is an error "Could not add the requested component.
The error is: ...."(sorry for lost details) when I try to install Microsoft TCP/IP version 6 on my Network Adapter;

(2) There is an error "Unable to start the MSMQ service. Error Code: 0x433
Error description: The dependency service does not exist or has been marked for deletion. "
and/or "Setup could not copy one or more files. The specific error code is 0x4b8..."
and/or "The MSMQTriggers service cannot be started. Error Code: 0x42C Error Description: The dependency service or group failed to start."
When I removed and try to re-install Message Queuing;

(3.1) To resolve 0x4b8, I running this command:
esentutl /p c:\WINDOWS\security\Database\secedit.sdb

(3.2) To resolve 0x42C, I found a very useful message on Etc. Windows.
http://www.google.com/notebook/public/04097591150171922613/BDT6NIgoQlMDj180h
and running these commands:

net stop msdtc

msdtc -uninstall

go into regedit and delete the key HKLM\Software\Microsoft\MSDTC

msdtc -install

net start msdtc

(3.3) Now, I can continue to re-install Message Queuing.

(4) Check and install Microsoft TCP/IP version 6.

(5) Connect to localhost (Analysis Server) in Microsoft SQL Server Management Studio. It is OK now!

I really don't know how to fixed but I finally fiexed it anyhow! If you doubt whether it meets your case or not, please just take a trial; If you want to contact me, please email to huangxiangyao@.hotmail.com.

|||

Thanks God! I fixed this problem, but I really do not know how and why.

I will do some experiment and try to find it out (this may take me several hours or days or never), but now, I only list what I did -

(1) There is an error "Could not add the requested component.
The error is: ...."(sorry for lost details) when I try to install Microsoft TCP/IP version 6 on my Network Adapter;

(2) There is an error "Unable to start the MSMQ service. Error Code: 0x433
Error description: The dependency service does not exist or has been marked for deletion. "
and/or "Setup could not copy one or more files. The specific error code is 0x4b8..."
and/or "The MSMQTriggers service cannot be started. Error Code: 0x42C Error Description: The dependency service or group failed to start."
When I removed and try to re-install Message Queuing;

(3.1) To resolve 0x4b8, I running this command:
esentutl /p c:\WINDOWS\security\Database\secedit.sdb

(3.2) To resolve 0x42C, I found a very useful message on Etc. Windows.
http://www.google.com/notebook/public/04097591150171922613/BDT6NIgoQlMDj180h
and running these commands:

net stop msdtc

msdtc -uninstall

go into regedit and delete the key HKLM\Software\Microsoft\MSDTC

msdtc -install

net start msdtc

(3.3) Now, I can continue to re-install Message Queuing.

(4) Check and install Microsoft TCP/IP version 6.

(5) Connect to localhost (Analysis Server) in Microsoft SQL Server Management Studio. It is OK now!

I really don't know how to fixed but I finally fiexed it anyhow! If you doubt whether it meets your case or not, please just take a trial; If you want to contact me, please email to huangxiangyao@.hotmail.com. (From 1:00 to 17:00 UTC)

I fixed it but want to confirm my solution

Hi Everyone,
I installed MSDE and created some VB.Net apps that worked. However,
when I tried to access them over a peer to peer LAN, I kept getting ..
"Server does not exist or access denied."
I researched the newsgroups and learned about ...
SVRNETCN.exe - Server Network configuration utility.
I launched the tool and saw that nothing was enabled. I enabled TCP/IP,
then stopped and restarted the SQL services and it now seems to work.
Questions:
- I read about 'named pipes' too and I'm wondering if I have to enable
them too (I have no idea what named pipes are)
- I enabled just the TCP/IP protocol and didn't poke around anywhere
else. Is this sufficient? Or should I verify anything else?
(I might have to ask this in another newsgroup but I'll try...)
In my search to fix the problem, I came across many messages about "Code
Access Security Policy" and I started reading about .Net Configuration
utility with its runtime security policies (code groups, permission
sets, policy assemblies) and I was trying things in there. Is there
anything that MSDE or VB.Net apps need changed in there?
Thanks for your help
Richard
You would only need to enable named pipes if you had clients connecting to
your MSDE instance that had named pipes as the default protocol.
Having only TCP/IP enabled is ok as long as any client connections are also
using TCP/IP.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:uaP0NIPGEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi Everyone,
> I installed MSDE and created some VB.Net apps that worked. However,
> when I tried to access them over a peer to peer LAN, I kept getting ..
> "Server does not exist or access denied."
> I researched the newsgroups and learned about ...
> SVRNETCN.exe - Server Network configuration utility.
> I launched the tool and saw that nothing was enabled. I enabled TCP/IP,
> then stopped and restarted the SQL services and it now seems to work.
> Questions:
> - I read about 'named pipes' too and I'm wondering if I have to enable
> them too (I have no idea what named pipes are)
> - I enabled just the TCP/IP protocol and didn't poke around anywhere
> else. Is this sufficient? Or should I verify anything else?
>
> (I might have to ask this in another newsgroup but I'll try...)
> In my search to fix the problem, I came across many messages about "Code
> Access Security Policy" and I started reading about .Net Configuration
> utility with its runtime security policies (code groups, permission
> sets, policy assemblies) and I was trying things in there. Is there
> anything that MSDE or VB.Net apps need changed in there?
> Thanks for your help
> Richard
sql

I encountered problem after .NET framework upgraded to 2.0 in the server

I posted the question below to this website 6 hours ago, the subject was "SQL server registration failed". I couldn't find this question just now. This is my first time to post a question to microsoft website. Please let me know the related website if the question shouldn't be posted here.(I think it 's related to this site)
URGENT, I need help, thanks.

I installed .Net framework 2.0 in a Window 2003 Server and VS2005 in my PC a few days ago (.NET framework 1.1 is still in the server and VS.NET2003 is still in my PC), SQL server 2000 is with web server together. there is no problem to run ASP.net 2.0 application, the application also can query the database in SQL server.
But I encounter a problem that I can't create new SQL server registration from my PC to the above server. the error msg is "SQL Server registration failed. SQL Server does not exist or access denied ConnectionOpen(Connect())"
There is no problem to connect SQL server remotely before I upgraded .net framework to 2.0 and VS.NET2003 to VS2005.
I checked the SQL service (started), TCP/IP(enabled). I also installed SQL server SP4 in the server, but I still encounter the same problem.
Anybody can help? Thanks

moving to SQL forum.

|||I guess the firewall on the Windows 2003 server doesn′t allow incoming communication on port 1433. Change that and as a stateful firewall Windows will open the outgoing ports for communication.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

I downloaded and installed SQLEXPR_ADV.EXE

This file is 240 Megabytes. What am I supposed to see on the start
menu after this is installed?
Is there a tool in here with which I can create a database?
in florida,
IanODepends on what you want to do. Go back to the download page:
http://msdn.microsoft.com/vstudio/express/sql/download/
and either download the Toolkit if you want the complete set of tools or
management studio Express if you just want to manage the relational database
part.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"iano" <IanONet@.gmail.com> wrote in message
news:1151882090.532700.166560@.75g2000cwc.googlegroups.com...
> This file is 240 Megabytes. What am I supposed to see on the start
> menu after this is installed?
> Is there a tool in here with which I can create a database?
> in florida,
> IanO
>