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 getting SQL and SQL agent to start automatically

Hi folks,
Can anyone help me out with this strange problem I'm having. I have a
new server (Win 2003 standard) that is part of a domain and it is
running SQL 2000. I have changed the services to run as a domain
account. The password for this account is correct and the account is
also local admin and domain admin.
I am able to manually start the SQL server and SQL agent service
manually but when i restart the box or try to restart the services
they do not start up. I get the following error message in in the
event log:
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7000
Date:10/05/2007
Time:09:58:45
User:N/A
Computer:xxxxx
Description:
The MSSQLSERVER service failed to start due to the following error:
The account name is invalid or does not exist, or the password is
invalid for the account name specified.
I checked the account details and they are working as I am able to log
into the server using the account.
Does anyone know why this happens and how i can get SQL and SQL agent
to start up automatically as I don't want to be caught out in the
event of the server rebooting.
Anyone any tips or suggestions?
Thanks in advance
Fox1977
The Windows Account for the MSSQLServer service and the SQLServerAgent
service has a bad password, or could not be validated through your Active
Directory or because network services were unavailable.
Do you have any other service that starts under the same account? If so do
you have the same problem with that service also? Also please check the
event logs to check if there are any error messages or warning messages
related to this account. Also one other thing you can try is to use a
different account as a startup for SQL server and see if you can repro the
behavior.
"Fox1977" <foxj77@.gmail.com> wrote in message
news:1178789826.884648.294580@.w5g2000hsg.googlegro ups.com...
> Hi folks,
> Can anyone help me out with this strange problem I'm having. I have a
> new server (Win 2003 standard) that is part of a domain and it is
> running SQL 2000. I have changed the services to run as a domain
> account. The password for this account is correct and the account is
> also local admin and domain admin.
> I am able to manually start the SQL server and SQL agent service
> manually but when i restart the box or try to restart the services
> they do not start up. I get the following error message in in the
> event log:
> Event Type: Error
> Event Source: Service Control Manager
> Event Category: None
> Event ID: 7000
> Date: 10/05/2007
> Time: 09:58:45
> User: N/A
> Computer: xxxxx
> Description:
> The MSSQLSERVER service failed to start due to the following error:
> The account name is invalid or does not exist, or the password is
> invalid for the account name specified.
> I checked the account details and they are working as I am able to log
> into the server using the account.
> Does anyone know why this happens and how i can get SQL and SQL agent
> to start up automatically as I don't want to be caught out in the
> event of the server rebooting.
> Anyone any tips or suggestions?
> Thanks in advance
>
|||I was getting a netlogon error in the event log saying could not
contact PDC. After doing a bit of searching on the net I came across
this site:
[url]http://www.eventid.net/display.asp?eventid=5719&eventno=104&source=NETLOG ON&phase=1[/url]
About half way down there is an entry about lmhosts/wins. I compare
the LMHOSTS setting on the server against some of the other database
servers in the domain. I changed the LMHOSTS/NETBIOS setting to
Default from Disable NETBIOS over TCP/IP.
I rebooted the server and then the PDC error disappeared and the
services started automatically. Brilliant
Thanks for your help, much appreciated

Problem getting SQL and SQL agent to start automatically

Hi folks,
Can anyone help me out with this strange problem I'm having. I have a
new server (Win 2003 standard) that is part of a domain and it is
running SQL 2000. I have changed the services to run as a domain
account. The password for this account is correct and the account is
also local admin and domain admin.
I am able to manually start the SQL server and SQL agent service
manually but when i restart the box or try to restart the services
they do not start up. I get the following error message in in the
event log:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 10/05/2007
Time: 09:58:45
User: N/A
Computer: xxxxx
Description:
The MSSQLSERVER service failed to start due to the following error:
The account name is invalid or does not exist, or the password is
invalid for the account name specified.
I checked the account details and they are working as I am able to log
into the server using the account.
Does anyone know why this happens and how i can get SQL and SQL agent
to start up automatically as I don't want to be caught out in the
event of the server rebooting.
Anyone any tips or suggestions?
Thanks in advanceFox1977
The Windows Account for the MSSQLServer service and the SQLServerAgent
service has a bad password, or could not be validated through your Active
Directory or because network services were unavailable.
Do you have any other service that starts under the same account? If so do
you have the same problem with that service also? Also please check the
event logs to check if there are any error messages or warning messages
related to this account. Also one other thing you can try is to use a
different account as a startup for SQL server and see if you can repro the
behavior.
"Fox1977" <foxj77@.gmail.com> wrote in message
news:1178789826.884648.294580@.w5g2000hsg.googlegroups.com...
> Hi folks,
> Can anyone help me out with this strange problem I'm having. I have a
> new server (Win 2003 standard) that is part of a domain and it is
> running SQL 2000. I have changed the services to run as a domain
> account. The password for this account is correct and the account is
> also local admin and domain admin.
> I am able to manually start the SQL server and SQL agent service
> manually but when i restart the box or try to restart the services
> they do not start up. I get the following error message in in the
> event log:
> Event Type: Error
> Event Source: Service Control Manager
> Event Category: None
> Event ID: 7000
> Date: 10/05/2007
> Time: 09:58:45
> User: N/A
> Computer: xxxxx
> Description:
> The MSSQLSERVER service failed to start due to the following error:
> The account name is invalid or does not exist, or the password is
> invalid for the account name specified.
> I checked the account details and they are working as I am able to log
> into the server using the account.
> Does anyone know why this happens and how i can get SQL and SQL agent
> to start up automatically as I don't want to be caught out in the
> event of the server rebooting.
> Anyone any tips or suggestions?
> Thanks in advance
>|||I was getting a netlogon error in the event log saying could not
contact PDC. After doing a bit of searching on the net I came across
this site:
http://www.eventid.net/display.asp?eventid=5719&eventno=104&source=NETLOGON&phase=1
About half way down there is an entry about lmhosts/wins. I compare
the LMHOSTS setting on the server against some of the other database
servers in the domain. I changed the LMHOSTS/NETBIOS setting to
Default from Disable NETBIOS over TCP/IP.
I rebooted the server and then the PDC error disappeared and the
services started automatically. Brilliant
Thanks for your help, much appreciated

Problem getting SQL and SQL agent to start automatically

Hi folks,
Can anyone help me out with this strange problem I'm having. I have a
new server (Win 2003 standard) that is part of a domain and it is
running SQL 2000. I have changed the services to run as a domain
account. The password for this account is correct and the account is
also local admin and domain admin.
I am able to manually start the SQL server and SQL agent service
manually but when i restart the box or try to restart the services
they do not start up. I get the following error message in in the
event log:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: 10/05/2007
Time: 09:58:45
User: N/A
Computer: xxxxx
Description:
The MSSQLSERVER service failed to start due to the following error:
The account name is invalid or does not exist, or the password is
invalid for the account name specified.
I checked the account details and they are working as I am able to log
into the server using the account.
Does anyone know why this happens and how i can get SQL and SQL agent
to start up automatically as I don't want to be caught out in the
event of the server rebooting.
Anyone any tips or suggestions?
Thanks in advanceFox1977
The Windows Account for the MSSQLServer service and the SQLServerAgent
service has a bad password, or could not be validated through your Active
Directory or because network services were unavailable.
Do you have any other service that starts under the same account? If so do
you have the same problem with that service also? Also please check the
event logs to check if there are any error messages or warning messages
related to this account. Also one other thing you can try is to use a
different account as a startup for SQL server and see if you can repro the
behavior.
"Fox1977" <foxj77@.gmail.com> wrote in message
news:1178789826.884648.294580@.w5g2000hsg.googlegroups.com...
> Hi folks,
> Can anyone help me out with this strange problem I'm having. I have a
> new server (Win 2003 standard) that is part of a domain and it is
> running SQL 2000. I have changed the services to run as a domain
> account. The password for this account is correct and the account is
> also local admin and domain admin.
> I am able to manually start the SQL server and SQL agent service
> manually but when i restart the box or try to restart the services
> they do not start up. I get the following error message in in the
> event log:
> Event Type: Error
> Event Source: Service Control Manager
> Event Category: None
> Event ID: 7000
> Date: 10/05/2007
> Time: 09:58:45
> User: N/A
> Computer: xxxxx
> Description:
> The MSSQLSERVER service failed to start due to the following error:
> The account name is invalid or does not exist, or the password is
> invalid for the account name specified.
> I checked the account details and they are working as I am able to log
> into the server using the account.
> Does anyone know why this happens and how i can get SQL and SQL agent
> to start up automatically as I don't want to be caught out in the
> event of the server rebooting.
> Anyone any tips or suggestions?
> Thanks in advance
>|||I was getting a netlogon error in the event log saying could not
contact PDC. After doing a bit of searching on the net I came across
this site:
http://www.eventid.net/display.asp?...phase=
1
About half way down there is an entry about lmhosts/wins. I compare
the LMHOSTS setting on the server against some of the other database
servers in the domain. I changed the LMHOSTS/NETBIOS setting to
Default from Disable NETBIOS over TCP/IP.
I rebooted the server and then the PDC error disappeared and the
services started automatically. Brilliant
Thanks for your help, much appreciated