MS SQL server Insert Error [109]

Greetings!

When I run the following SQL statement in Perl, I get an error
stating:
any help/pointers how this can be resolved?

Thanks,
-Murali

SQL note_insert error: [109] [2] [0] "[Microsoft][ODBC SQL Server
Driver][SQL Server]There are more columns in the INSERT statement than
values specified in the VALUES clause. The number of values in the
VALUES clause must match the number of columns specified in the INSERT
statement."

The perl code:

$sql_stmt1 = "select cast(newid () as varbinary(16)) as notes_id
from cc_test";

if($db_ends->Sql($sql_stmt1))
{
$db_ends->Error();
exit(-1);
}

while($db_ends->FetchRow())
{
undef %Data;
%Data = $db_ends->DataHash();
$notes_id = $Data{"notes_id"};
}

# Prepare the columns for insert

my$updated_detail = "$pn . $cc_ver\n";

my $note_columns = "user_note_id, bio_name, related_tbl_name,
related_string_id, related_int_id, note_type_lkp, notes,
internal_flag, revision_number, obsolete_flag";

my $note_values = "cast(" +$notes_Id + "as binary(16)), Null, Null,
Null, $bfn, 0x74942E5A0A04022800415DE63529D4B7, $notes_detail, 0, 0,
0";

$sql_note_insert = "insert into user_note ($note_columns) values
($note_values)";

if($db_ends->Sql($sql_note_insert))Make sure you delimit all strings with single quotes ('). If one of the
current variables (such as $note_details) contains a comma, then you
will have more "values" than "columns".

If this doesn't work, then please post the actual query you are
submitting to SQL-Server.

Hope this helps,
Gert-Jan

Murali Kanaga wrote:
> Greetings!
> When I run the following SQL statement in Perl, I get an error
> stating:
> any help/pointers how this can be resolved?
> Thanks,
> -Murali
> SQL note_insert error: [109] [2] [0] "[Microsoft][ODBC SQL Server
> Driver][SQL Server]There are more columns in the INSERT statement than
> values specified in the VALUES clause. The number of values in the
> VALUES clause must match the number of columns specified in the INSERT
> statement."
> The perl code:
> $sql_stmt1 = "select cast(newid () as varbinary(16)) as notes_id
> from cc_test";
> if($db_ends->Sql($sql_stmt1))
> {
> $db_ends->Error();
> exit(-1);
> }
> while($db_ends->FetchRow())
> {
> undef %Data;
> %Data = $db_ends->DataHash();
> $notes_id = $Data{"notes_id"};
> }
> # Prepare the columns for insert
> my $updated_detail = "$pn . $cc_ver\n";
> my $note_columns = "user_note_id, bio_name, related_tbl_name,
> related_string_id, related_int_id, note_type_lkp, notes,
> internal_flag, revision_number, obsolete_flag";
> my $note_values = "cast(" +$notes_Id + "as binary(16)), Null, Null,
> Null, $bfn, 0x74942E5A0A04022800415DE63529D4B7, $notes_detail, 0, 0,
> 0";
> $sql_note_insert = "insert into user_note ($note_columns) values
> ($note_values)";
> if($db_ends->Sql($sql_note_insert))sql

MS SQL Server equivalent query

I have this query I use on MySql, and I'm trying to translate that to make it work on MS SQL Server.

INSERT INTO totable (col1,col2,col3)

(SELECT col1,col2,col3 FROM fromtable AS x)

ON DUPLICATE KEY UPDATE col1=x.col1,col2=x.col2,col3=x.col3;

Basically it inserts all rows from one table into another, and if you get a unique key constraint, it updates that rows instead. So far I haven't found any equivalent for MS SQL Server Anyone have a suggestion?

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

WesleyB wrote:

I'm afraid you will have to wait for SQL Server 2008 with the new MERGE statement :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Well...I don't have that amount of time Anyway...to elaborate a little bit...I have a working solution, but I'm trying to make it better. Currently this is done from a c++ program in a cursor loop. I send a select, insert and update statement to a function, selects a dataset and for each row in that dataset I'll first try to update, and if the update fails (returns no affected rows), I execute the insert statement. Needless to say that it isn't very efficient, but I can be flexible in terms of prepare temporary tables, send in help queries etc. I just can't figure out a better way than the current scenario...I just think that it really have to be a better way to do this.

|||

Rather than use a CURSOR, I would load the data in question into a staging table, using a table variable or #temp table, then with a single update statement, update all pertinent rows in the production table, a second query to delete those rows from the staging table, and then a third query to add the remainder to the production table.

Depending upon the number of rows, it is likely to be quite a bit more efficient and 'faster'.

|||

Why not doing first the update then the insert ?

Update SomeTable
SET
col1=x.col1
col2=x.col2,
col3=x.col3
From SomeTable
INNER JOIN fromtable x
ON --Place your join conditions here

INSERT INTO totable (col1,col2,col3)

(
SELECT col1,col2,col3
FROM fromtable AS x
WHERE NOT EXISTS
(
Select * from totable T
Where t.SomeColumn = x.SomeColumn --These should be your join conditions
)
)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

MS SQL Server 2005 hang

Hi All,
We get very strange hang situation at our customers from time to time.
I execute very simple query like
insert into <table1>
select <columns> from <table2>
where <conditions>
Table <table1> has clustered index on float column.
Normally this query is executing, say, 2 minutes. But sometimes it
suddenly begins to hang for 2 hours and go to query timeout. I did not
find something special or different in execution plan.
The only workaround I have found is to recreate <table1>. I just copy
all data from this table to another table, then drop table <table1>,
then create it with adding necessary index and then copy data back
from temptable to original one. And it helps! The same data is easily
inserted in 2 minutes.
I have never experienced such problem on SQL Server 2000, only on
2005. Unfortunately, we cannot reproduce it on our environment but
there are no visible differences in server or db options.
Probably somebody already solved such problem or can advise where to
go. Any help would be appreciated.
Thanks in advance!Hi
"prudon@.inbox.ru" wrote:
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
Have you checked the version of SQL 2005 that you are running? Make sure
that it is up to date. Also look for blocking
http://support.microsoft.com/kb/271509 missing indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx or out of date
statistics http://msdn2.microsoft.com/en-us/library/ms190397.aspx
John|||1) Almost certainly a blocking situation. moving (potentially large)
amounts of data like this is often a performance issue because the locks
escalate to full table, preventing ANY other update/delete/insert access to
the table for the duration of the transaction.
2) My gut tells me to question a clustered index on a float datatype.
TheSQLGuru
President
Indicium Resources, Inc.
<prudon@.inbox.ru> wrote in message
news:1180681124.707731.111770@.q69g2000hsb.googlegroups.com...
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>|||Thank you very much!
The specific thing of our application that there is only one connect
per database. So, there are no other transactions on those database. I
cannot understand why we didn't experienced such problems on SQL
Server 2000 for more than 4 years nowhere. If the problem is in
clustered index, why "re-creating" table with index helps to avoid the
problem. Next time I get such problem I will check statistics, but I'm
afraid it will not give anything.
Many thanks for your feedback

MS SQL SERVER 2005 BUG? Cannot insert NULL into column diagram_id

Hi friends,
when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.
Whats with this??I had the same issue an just fixed it by turning the "diagram_id" field in SysDiagrams table to "identitity".

I dropped the table and ran the following script.

CREATE TABLE [dbo].[sysdiagrams](
[name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[principal_id] [int] NOT NULL,
[diagram_id] [int] identity(1,1),
[version] [int] NULL,
[definition] [varbinary](max) NULL
) ON [PRIMARY]

It worked.

MS SQL Server 2000: Can Insert New Rows, but can't Update or Delete!!

I'm facing a strange problem here (SQL Server 2000).
I can Insert new rows from My ASP.NET 2.0 Pages but Can't Update or Delete.
usualy I test and build my web applications offline on my home workstation before I uppload them to my Server (Windows Server 2003 Standard), and I make sure that its error free.
The problem goes as follows:
1- I created a data grid that you can insert, delete and update and it worked fine when I tested it on my home computer.
2- When I updated the application to my server, I've noticed that I can Insert new columns to the database but When I try to update or delete the old columns, it doesn't allow me (so I have to use interprise manager to delete them from under- using same username and password!!!!).

I've never faced anything like this before, so please if anyone can give me any advice...

The problem only happened when I was using ASP.NET 2.0 Beta on my Home computer, and the full version on my server. so it was obvious that i need to install the same thing on both computers. but when I did I had to reconfigure my connection to accomodate the changes with the version. and now its working fine...

MS SQL Server 2000: Can Insert New Rows, but can't Update or Delete!!

I'm facing a strange problem here (SQL Server 2000).
I can Insert new rows from My ASP.NET 2.0 Pages but Can't Update or Delete.
usualy I test and build my web applications offline on my home workstation before I uppload them to my Server (Windows Server 2003 Standard), and I make sure that its error free.
The problem goes as follows:
1- I created a data grid that you can insert, delete and update and it worked fine when I tested it on my home computer.
2- When I updated the application to my server, I've noticed that I can Insert new columns to the database but When I try to update or delete the old columns, it doesn't allow me (so I have to use interprise manager to delete them from under- using same username and password!!!!).

I've never faced anything like this before, so please if anyone can give me any advice...

The problem only happened when I was using ASP.NET 2.0 Beta on my Home computer, and the full version on my server. so it was obvious that i need to install the same thing on both computers. but when I did I had to reconfigure my connection to accomodate the changes with the version. and now its working fine...

sql

MS SQL Question - Insert data to a new table

Hello, I am using a database .mdf and I want to insert values in a table (I mean how to connect also with a database, open it and store the values). Can someone describe the whole process to do that (as I am newbie in asp.net). I want the whole code if it is possible.

For example I am using the above code to create 2 textboxes where the user will write his name and password. Then I want to store (by clicking the button) the values to a table in database. Thank you

<%@.PageLanguage="VB"AutoEventWireup="false"CodeFile="Manufacturer.aspx.vb"Inherits="Manufacturer" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LabelID="Label1"runat="server"Text="Name"></asp:Label>

<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/>

<asp:LabelID="Label2"runat="server"Text="Password"></asp:Label>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit"/></div>

</form>

</body>

</html>

Hi,Have you tried the sqldatasource controls?

For tutorials,http://asp.net/learn/videos/

MS SQL Command line insert

I use a similar command below to insert into a temp table the result of a large command line call to an exectable with many parameters passed in the command of which the result passed back contains many items. I then parse the response string to get my results...

set @.command = 'dir'
insert into tsverisign(response) exec master..xp_cmdshell @.command

My question is our can I insert two values at the same time to this same table one of which is my "exec master..xp_cmdshell @.command"

similar to insert into tables (field_a, feild_b) values ('1','2')

Something like (and I know this does not work):

insert into tsverisign(response,trans_id)
values (exec master..xp_cmdshell @.command, '123')

Any help would be greatly appreciated ... PS I'm new to MS SQL 2000 and proper syntax etc. etc. so I need full example so I can try. :rolleyes:What you're trying to do is not possible (in any dbms). What you should do is to dump the result of @.command into a staging table. Then insert it into the target table.

e.g.
insert into staging(i)
exec master..xp_cmdshell @.command

insert into target(i,j)
select i, @.other_val
from staging

Ms Sql Blob ?

Hi to all

I'm starting ussing Microsoft SQL2000, and i need help about how to insert any type of data file( .xls,.pdf, .jpg, .txt, ...) Into a column to extract later the files on web.

I heared about data tipe BLOB, but i can't find the way to work with it...

I'm lost... some body can help me?

Thanks.I struggled for a long time to try to store images in the SQL 2000 database. After long hours and many frustrations, I decided to leave these kinds of files in the capable hands of the file system. Once I did that, life got a lot easier.

Ling

MS SQL Basics: Insert into multiple tables at once.

I'm trying to do something very basic here but I'm totally new to MS SQL Server Manager and MS SQL in general.

I'm using the Database Designer in MS SQL Server Manager. I created two tables with the following properties:

Schedule (ScheduleID as UniqueIdentifier PK, Time as dateTime)

Course (CourseID as UniqueIdentifier PK, Name as VarChar(50))

I create a relationship by dragging the PK from the first table over to the second and I link on ScheduleID to CourseID columns (I'm not certain what type of relationship is created here N:N?). It appears to work, I can do a Select * and join the two tables to get a joined query.

The problem starts when I try to populate the tables: a course will have a schedule. I can't seem to get the rows to populate across both tables. I try to select the pk from the first table and insert it into the second but it complians about it not being a uniqueidentifier. I know this is very basic but I can't seem to find this very basic tutorial anywhere.

I come from the Oracle world of doing DB's so if you have some examples that relate across that would be great or better yet if you can point me to a good reference for doing M$ DB stuff that would be great.

Thanks.

Is Schedule supposed to represent timeslots for when Courses can take place (so that a Course has a Schedule)? Or is Schedule a collection of Courses (so that a Schedule has a Course)?

Either way, I would think that you need to insert a Foreign Key into the child table that contains the Primary Key value from the parent table.

For instance, let's assume that you intend for Course for have a Schedule:

Schedule
================================
ScheduleID uniqueidentifier (PK)
Time datetime

Course
================================
CourseID uniqueidentifier (PK)
ScheduleFK uniqueidentifier
Name varchar(50)

In this case, multiple courses can be assigned to the same schedule. You would use the "Relationships" functionality of the design mode for either table to create the foreign key relationship. From queries, you would use an INNER JOIN:

select * from course inner join schedule on course.schedulefk=schedule.scheduleid

(btw--this concept is shared across all relational databases, not just SQL Server)

|||

Sorry I should have been a bit more clear. Course holds golf course information and the schedule holds the tee times for a golf course. So one golf course has one schedule.

I understand the PK FK relationship. I assumed, incorrectly I guess in this case, that the FK is created automatically when I create the relationship. I also understand the inner join.

I'll give this a try.

|||If you have a one-to-one relationship between the two tables it may be advisable to normalise them into a singular table therefore reducing the complexity|||

Ya I was out to lunch on the one to one relationship, it is one to many; sorry I was rushing out the door this morning when I wrote this.

Couple more questions:

Weird thing happens sometimes when I make a column the PK. I get an error message when I try to save it, the system says that the column cannot be null. I have not put any data into the table yet so I don't understand where it is getting this null thing from. If I del and create the row again there is no problme, is this is bug?

Secondly, do I need to check of 'identity' in the PK column? Do the values automatically go from the PK column to the FK column or do I have to mannualy tell it to do that as the rows are being inserted?

Thanks.

|||

*I meant to say table not row for the null error part.

The error message is this:

'Course' table
- Unable to modify table.
Cannot insert the value NULL into column 'CourseID', table 'GolfPlanner.dbo.Tmp_Course'; column does not allow nulls. INSERT fails.
The statement has been terminated.

|||

I've been playing with this a bit today. What I don't understand is how to generate the PK's. Do I have to call a counter to do it?

When I try to add stuff now it tells me that courseID cannot be null, but I can't make that column into an identity because the datatype is uniqueIdentifier...??

|||

NewID() as in:

INSERT INTO MyTable(Col1,Col2,Col3) VALUES (NewID(),@.Col2,Col3)

|||Great thanks, that solved one problem. Now how to I get taht PK to become the FK in the second table. I tried to do a select inside the insert but it's telling me I'm not allowed to do that?|||

I got it to work, probably not the proper solution but here it is for anyone having the same issue:

-- =============================================

ALTER

PROCEDURE [dbo].[PopulateDatabase]-- Add the parameters for the stored procedure here

AS

declare @.CourseIDuniqueIdentifier

BEGIN

SETNOCOUNTON;INSERTINTO

Course

(CourseID,Name, Address, PhoneNumber)VALUES (NewID(),'Prospect Lake','123 Prospect St', 2508129832)

SET @.CourseID=(SELECT CourseIDFROM CourseWHEREName='Prospect Lake')

INSERTINTO

Schedule

(ScheduleID, Course_FK, Date, TeeTime, NumberOfPlayers)VALUES (NewID(), @.CourseID,getDate(),'06:00','2')