Problem in Importing Excel data into MS SQL 2000

Hi all,

I want to import MS Excel data into MS SQL 2000 and I am programming this in VB 6.0. I am referring the article http://support.microsoft.com/kb/316934/EN-US/ for this. I can able to import Excel data successfully into SQL database table. But the problem is, the order of exported data in SQL table is not matching that of MS Excel data. All the rows were jumbled, which made it totally unusable.

Please guide me how to Import Excel data into MS SQL in its original order only. Any other method other than what I am following is present; please suggest me to keep the original order.

Regards,

Rajeev Vandakar

Bangalore

As long as the data is correct on each row, there is no way to 'guarantee' that SQL Server will store data in any particular order -UNLESS there is a CLUSTERED index on the table, and if so, the data will be stored in the index order.

Otherwise, with SQL Server, we do NOT concern ourselves with the order of data storage, we use ORDER BY in our queries to cause the resultset to be in the order desired.

I suspect you may have a PRIMARY KEY with a CLUSTERED Index, and that is why it 'appears' that your data is being jumbled.

Check in Books Online about how to use the ORDER BY clause in your queries to un-jumble your data.

|||

Thank you Arnie Rowland for reply.

I can use ORDER BY clause in MS SQL after importing data. But this will not solve my problem here. Because the way data is present in Excel. In Excel some 12 colums present. Rows were organised in sections and to indicate start of a section 3rd colummn (meant for person name) have section name and remaining columns were left blank. So after importing into SQL, if I use ORDER BY clause, these rows, meant for sectin name will come in the top! If Excel data moves to SQL in as-it-is, same order, my problem will be solved. How to do this?

Regards,

Rajeev Vandakar

|||

As I indicated before, SQL Server (in fact the SQL language specification), clearly states that there is no guarantee about the order that data is stored in a table.

If you MUST load the table with data in a particular order, I suggest that you might wish to add a column to the Excel file, fill that column with some indicator of row order, and add a clustered index (or primary key) to the SQL table using that column.

Then the data will be ordered as you desire.

Without some way to place the rows in a particular order, SQL Server does not care, does not enforce order, and does not produce reliably ordered data.

sql

Problem in connecting 2 databases with sql Server 2005 Express

I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?

Connection String #1 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Main.mdf;Data Source=Steve_Laptop\sqlexpress

Connection String #2 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:\Program Files\Material_Management_System\DATA\Items.mdf;Data Source=Steve_Laptop\sqlexpress

Sql Query: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.PFMS'

Sql Query#2: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.dbo.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID

Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.dbo.PFMS'.

I am running the queries directly from the 2005 Mgt window to take as many variables out of the equasion. I get the same error in Visual basic

How am I supposed to reference a join of 2 databases? Any suggestions

I did not try that yet, but I think the cross join between user instances is not possible, you either have to do an in-memory join in .NET or attach the databases to the SQL Server Express instance and then join them using the three part name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem creating a secondary datafile on drive E:\

Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
programming. I created a database, tsqldb, on C:\, where the ms sql server
files reside. Now, I used the alter database command to add a secondary
datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
want to put this secondary datafile file, E:\. the alter database command
goes like this:
alter database tsqldb
add file
( name = tsqldb_data2,
filename = 'E:\tsqldb_data2.ndf',
size = 50mb,
maxsize = 150mb,
filegrowth = 25%
)
and this is the error I get:
Server: Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
Server: Msg 5009, Level 16, State 1, Line 1
ALTER DATABASE failed. Some disk names listed in the statement were not
found. Check that the names exist and are spelled correctly before rerunning
the statement.
Extending database by 50.00 MB on disk 'tsqldb_data2'.
Can someone help me? Thanks.
DominickCheck whether the service account that runs the SQL Server instance has the
permission to create files in E:\.
Linchi
"Dominick D." wrote:
> Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> programming. I created a database, tsqldb, on C:\, where the ms sql server
> files reside. Now, I used the alter database command to add a secondary
> datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> want to put this secondary datafile file, E:\. the alter database command
> goes like this:
> alter database tsqldb
> add file
> ( name = tsqldb_data2,
> filename = 'E:\tsqldb_data2.ndf',
> size = 50mb,
> maxsize = 150mb,
> filegrowth = 25%
> )
> and this is the error I get:
> Server: Msg 5123, Level 16, State 1, Line 1
> CREATE FILE encountered operating system error 5(Access is denied.) while
> attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> Server: Msg 5009, Level 16, State 1, Line 1
> ALTER DATABASE failed. Some disk names listed in the statement were not
> found. Check that the names exist and are spelled correctly before rerunning
> the statement.
> Extending database by 50.00 MB on disk 'tsqldb_data2'.
> Can someone help me? Thanks.
> Dominick|||How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
> Check whether the service account that runs the SQL Server instance has the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
> > programming. I created a database, tsqldb, on C:\, where the ms sql server
> > files reside. Now, I used the alter database command to add a secondary
> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
> > want to put this secondary datafile file, E:\. the alter database command
> > goes like this:
> >
> > alter database tsqldb
> > add file
> > ( name = tsqldb_data2,
> > filename = 'E:\tsqldb_data2.ndf',
> > size = 50mb,
> > maxsize = 150mb,
> > filegrowth = 25%
> > )
> >
> > and this is the error I get:
> >
> > Server: Msg 5123, Level 16, State 1, Line 1
> > CREATE FILE encountered operating system error 5(Access is denied.) while
> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > Server: Msg 5009, Level 16, State 1, Line 1
> >
> > ALTER DATABASE failed. Some disk names listed in the statement were not
> > found. Check that the names exist and are spelled correctly before rerunning
> > the statement.
> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> >
> > Can someone help me? Thanks.
> >
> > Dominick|||Using Windows Explorer, right click on the drive or folder and bring up its
properties. It's under the Security tab.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
How do I check that? I"m a newbie to SQL, file permissions, so I'd
appreciate
your assistance. Thanks.
"Linchi Shea" wrote:
> Check whether the service account that runs the SQL Server instance has
> the
> permission to create files in E:\.
> Linchi
> "Dominick D." wrote:
> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin
> > and
> > programming. I created a database, tsqldb, on C:\, where the ms sql
> > server
> > files reside. Now, I used the alter database command to add a secondary
> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where
> > I
> > want to put this secondary datafile file, E:\. the alter database
> > command
> > goes like this:
> >
> > alter database tsqldb
> > add file
> > ( name = tsqldb_data2,
> > filename = 'E:\tsqldb_data2.ndf',
> > size = 50mb,
> > maxsize = 150mb,
> > filegrowth = 25%
> > )
> >
> > and this is the error I get:
> >
> > Server: Msg 5123, Level 16, State 1, Line 1
> > CREATE FILE encountered operating system error 5(Access is denied.)
> > while
> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > Server: Msg 5009, Level 16, State 1, Line 1
> >
> > ALTER DATABASE failed. Some disk names listed in the statement were not
> > found. Check that the names exist and are spelled correctly before
> > rerunning
> > the statement.
> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> >
> > Can someone help me? Thanks.
> >
> > Dominick|||Talk to the person who administers the machine in question. They should be able to check what
service account the service is using and see whether that account has permissions to create files in
the folder in question.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
>> Check whether the service account that runs the SQL Server instance has the
>> permission to create files in E:\.
>> Linchi
>> "Dominick D." wrote:
>> > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin and
>> > programming. I created a database, tsqldb, on C:\, where the ms sql server
>> > files reside. Now, I used the alter database command to add a secondary
>> > datafile, tsqldb_data2 to drive E:\ that I've created and that is where I
>> > want to put this secondary datafile file, E:\. the alter database command
>> > goes like this:
>> >
>> > alter database tsqldb
>> > add file
>> > ( name = tsqldb_data2,
>> > filename = 'E:\tsqldb_data2.ndf',
>> > size = 50mb,
>> > maxsize = 150mb,
>> > filegrowth = 25%
>> > )
>> >
>> > and this is the error I get:
>> >
>> > Server: Msg 5123, Level 16, State 1, Line 1
>> > CREATE FILE encountered operating system error 5(Access is denied.) while
>> > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
>> > Server: Msg 5009, Level 16, State 1, Line 1
>> >
>> > ALTER DATABASE failed. Some disk names listed in the statement were not
>> > found. Check that the names exist and are spelled correctly before rerunning
>> > the statement.
>> > Extending database by 50.00 MB on disk 'tsqldb_data2'.
>> >
>> > Can someone help me? Thanks.
>> >
>> > Dominick|||OK, I managed to do a little research on E:\ and I included the service
account that I set up to have full control over E:\ and it did the trick.
"Tom Moreau" wrote:
> Using Windows Explorer, right click on the drive or folder and bring up its
> properties. It's under the Security tab.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Dominick D." <DominickD@.discussions.microsoft.com> wrote in message
> news:BE503C3C-EF2C-47DF-A91B-41C3E95959CB@.microsoft.com...
> How do I check that? I"m a newbie to SQL, file permissions, so I'd
> appreciate
> your assistance. Thanks.
>
> "Linchi Shea" wrote:
> > Check whether the service account that runs the SQL Server instance has
> > the
> > permission to create files in E:\.
> >
> > Linchi
> >
> > "Dominick D." wrote:
> >
> > > Hi. I am a student, taking a class on MS SQL SERVER 2000 system admin
> > > and
> > > programming. I created a database, tsqldb, on C:\, where the ms sql
> > > server
> > > files reside. Now, I used the alter database command to add a secondary
> > > datafile, tsqldb_data2 to drive E:\ that I've created and that is where
> > > I
> > > want to put this secondary datafile file, E:\. the alter database
> > > command
> > > goes like this:
> > >
> > > alter database tsqldb
> > > add file
> > > ( name = tsqldb_data2,
> > > filename = 'E:\tsqldb_data2.ndf',
> > > size = 50mb,
> > > maxsize = 150mb,
> > > filegrowth = 25%
> > > )
> > >
> > > and this is the error I get:
> > >
> > > Server: Msg 5123, Level 16, State 1, Line 1
> > > CREATE FILE encountered operating system error 5(Access is denied.)
> > > while
> > > attempting to open or create the physical file 'E:\tsqldb_data2.ndf'.
> > > Server: Msg 5009, Level 16, State 1, Line 1
> > >
> > > ALTER DATABASE failed. Some disk names listed in the statement were not
> > > found. Check that the names exist and are spelled correctly before
> > > rerunning
> > > the statement.
> > > Extending database by 50.00 MB on disk 'tsqldb_data2'.
> > >
> > > Can someone help me? Thanks.
> > >
> > > Dominick
>