"Invalid column name" problem

Discussion:

(too old to reply)

Balt

2006-02-12 02:08:26 UTC

Hi all,

I have this insert statement:

INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);

This statement works fine when executed in SQL Server Management Studio and
inserts the data as expected. When I run that query through dbcmd/dbsqlexec
from within my C program, i get above error for each and every column
specified in the insert statement:
SQL Server Message 207: Invalid column name 'CH1'.
SQL Server Message 207: Invalid column name 'CH2'.
... and so forth, for every column in the table.

I tried double quotes, single quotes, with and without the trailing ;,
executing sp_defaultdb after login so as to make sure we're on the right
database, I also tried specifying the table name as dbo.dta instead of just
dta... all to no avail.

Any ideas anyone??

Thanks!

- balt

Dan Guzman

2006-02-12 02:17:38 UTC

Permalink

Post by Balt
I tried double quotes, single quotes, with and without the trailing ;,

Have you tried no quotes? IIRC, DBLib defaulted to QUOTED_IDENTIFERS OFF.
Also, see my reply to your other post.
--
Hope this helps.

Dan Guzman
SQL Server MVP

Post by Balt
Hi all,
INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);
This statement works fine when executed in SQL Server Management Studio and
inserts the data as expected. When I run that query through
dbcmd/dbsqlexec
from within my C program, i get above error for each and every column
SQL Server Message 207: Invalid column name 'CH1'.
SQL Server Message 207: Invalid column name 'CH2'.
... and so forth, for every column in the table.
I tried double quotes, single quotes, with and without the trailing ;,
executing sp_defaultdb after login so as to make sure we're on the right
database, I also tried specifying the table name as dbo.dta instead of just
dta... all to no avail.
Any ideas anyone??
Thanks!
- balt

Balt

2006-02-12 05:26:27 UTC

Permalink

Dan,

thanks, that was the problem! Removed all quotes around column names, and
now it works.

However, the performance is utterly devastating... the import of one
datafile (around 30'000 records) takes over 3 minutes. If I remove the
indices, it's about 10% less... still 3 minutes. With SQLite, the exact same
process did 30'000 records in 6 seconds (with SQL inserts, no bulk import of
any kind).

Are there any SQL Server settings that might help speed up that process?

Post by Dan Guzman

Post by Balt
I tried double quotes, single quotes, with and without the trailing ;,

Have you tried no quotes? IIRC, DBLib defaulted to QUOTED_IDENTIFERS OFF.
Also, see my reply to your other post.
--
Hope this helps.
Dan Guzman
SQL Server MVP

Louis Davidson

2006-02-12 06:48:05 UTC

Permalink

Can you supply more information? How are you building the query? I doubt
you have this query:

INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);

in the datafile. What version of SQL Server are you running and what kind
of hardware? What tool are you using to do the insert? Is this just a
stand alone table? Have you checked your transaction log? What is the
recovery model being used? Simple? How fast are your disks? If you send
this as many statements, each statement has to be written to the log before
moving to the next. Adding transactions will really help out the process,
such as (obviously I just repeated your statement over and over, but you get
the point):

BEGIN TRANSACTION
INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);
INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);
INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);
INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);
INSERT INTO dta ("CH1", "CH2", "CH3", "CH4", "CH5", "Date", "OLSTime",
"Latitude", "Longitude", "Orbit", "Scan") VALUES ( -71.74, 13.36,
34.32, -158.13, 1237.52, 5001, 1007, 1.02, -42.25, 26117, 1);
COMMIT TRANSACTION

This only requires a single write to the transaction log...

I hope I don't sound like a jerk (I really am not one) but from what I saw
on the SQLite website it is a very compact simple (perhaps one might say
"lite" SQL Engine. SQL Server does have more overhead, but you are correct,
there are definitely setting that can help. Also, check out the SQL
Profiler tool to watch the statements. You can then see how often a command
is sent, which can tell you if the import tool is taking longer or the
inserts themselves.
--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Post by Balt
Dan,
thanks, that was the problem! Removed all quotes around column names, and
now it works.
However, the performance is utterly devastating... the import of one
datafile (around 30'000 records) takes over 3 minutes. If I remove the
indices, it's about 10% less... still 3 minutes. With SQLite, the exact same
process did 30'000 records in 6 seconds (with SQL inserts, no bulk import of
any kind).
Are there any SQL Server settings that might help speed up that process?

Post by Dan Guzman

Post by Balt
I tried double quotes, single quotes, with and without the trailing ;,

Have you tried no quotes? IIRC, DBLib defaulted to QUOTED_IDENTIFERS OFF.
Also, see my reply to your other post.
--
Hope this helps.
Dan Guzman
SQL Server MVP

Balt

2006-02-12 07:22:26 UTC

Permalink

Hi Louis,

I'm "bundling" the insert statements so that every raw data file I'm
importing is treated as one transaction. This results in anywhere between 200
to 20'000 inserts per transaction. I did notice a slight improvement in speed
when using transactions, but compared to the speed i get with sqlite, it's
negligible.

I still think that with turning off transaction logs etc. I might be able to
gain a lot of speed, although it of course will never be as fast as with
SQLite. But I was hoping for SQL Server to be faster in in the long run.
SQLite becomes unmanageably slow once there are a about a million records in
the table.

Regarding hardware, I'm using the same for sqlserver and sqlite, so there
really shouldn't be much of an issue. But for your information, here's the
details:

Dual Opteron 250
4GB RAM
Windows XP Pro (will go XP Pro 64bit soon, which should increase speed again
by maybe 10%)
1.5 TB RAID5 (3ware) distributed over 8 250GB Disks
SQL Server 2005
Using DBLib from my own program written in C to do the insert
Just one table in the database. Doesn't get much simpler!

Cheers

- Balt

Hilary Cotter

2006-02-12 11:00:38 UTC

Permalink

I think you should look at bcp. This may involve changing your data
slightly, probably to tab delimited format. Shell out some your c program to
run this.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Balt
Hi Louis,
I'm "bundling" the insert statements so that every raw data file I'm
importing is treated as one transaction. This results in anywhere between 200
to 20'000 inserts per transaction. I did notice a slight improvement in speed
when using transactions, but compared to the speed i get with sqlite, it's
negligible.
I still think that with turning off transaction logs etc. I might be able to
gain a lot of speed, although it of course will never be as fast as with
SQLite. But I was hoping for SQL Server to be faster in in the long run.
SQLite becomes unmanageably slow once there are a about a million records in
the table.
Regarding hardware, I'm using the same for sqlserver and sqlite, so there
really shouldn't be much of an issue. But for your information, here's the
Dual Opteron 250
4GB RAM
Windows XP Pro (will go XP Pro 64bit soon, which should increase speed again
by maybe 10%)
1.5 TB RAID5 (3ware) distributed over 8 250GB Disks
SQL Server 2005
Using DBLib from my own program written in C to do the insert
Just one table in the database. Doesn't get much simpler!
Cheers
- Balt

Tibor Karaszi

2006-02-12 10:18:59 UTC

Permalink

What is "SQLite"?

Some thought in addition to Louis points:

Do not send each index in its own batch. Send several in the same batch (similar to the grouping
inserts in one transaction concept). Each batch requires a network trip, parse and compile phase and
then finally execution.

Add SET NOCOUNT ON in the beginning of your batch.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Post by Dan Guzman

Post by Balt
I tried double quotes, single quotes, with and without the trailing ;,

Have you tried no quotes? IIRC, DBLib defaulted to QUOTED_IDENTIFERS OFF.
Also, see my reply to your other post.
--
Hope this helps.
Dan Guzman
SQL Server MVP