Having major problems with my insert query logic

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement\n";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement\n";
Win32::ODBC::DumpError();
}
else
{
print "successful\n";
}
}
}


}
}

Thanks,
LauraI'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

Is the CPUNumber the primary key for the CPU table? If so, you could use something like:my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
THEN UPDATE CPU
SET Name = '$HoH->{CPU}{$field}{Name}'
, MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
, SystemNetName = '$HoH->{Host}{SystemNetName}'
WHERE CPUNumber = '$field'
ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values (
'$field'
, '$HoH->{CPU}{$field}{Name}'
, '$HoH->{CPU}{$field}{MaxClockSpeed}'
, '$HoH->{Host}{SystemNetName}')";-PatP|||I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

What do you suppose that means? I copied and pasted the code as is.

Thanks,
Laura|||That error means that I don't proofread very well ;) I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

-PatP|||oh wow. That's so cool, it worked.

Thanks Pat for your help. I learn new things everyday.

-Laura|||I just love it when I can make a lovely lady happy!

-PatP

Having issue inserting large text colum into DB

I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs

I have set the colum type in the table to text

I have set the table option for text in row to on

I have set the table option for text in row to 1000

But it is still chopping the text at the 256 char mark on insert.

Anyone have any ideas ?? This is SQL 2000.

ChrisAre you sure it does? How do you check for the length of the inserted value? By doing SELECT?

Try this:

select datalength(<your_text_field>) from <your_table>

And why do you need TEXT IN ROW setting? Are you searching on that field? If that's the case, - you should implement Full-Text Search.|||In Query Analyzer:

1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column

If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!

-PatP|||In Query Analyzer:

1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column

If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!

-PatP
That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?|||That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?You did fine, as far as giving them what they needed to figure out the answer. I just like my solution better because then they can SEE the answer, which is often better than being able to deduce it.

-PatP

having difficulty inserting into the database table

I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code:
Dim Message As String
Dim connStr As String
Dim myConnection As SqlConnection
Dim mySqlCommand As SqlCommand
connStr = "server=SIMI\VSdotNET;Trusted_Connection=yes;database=AeroSea"
myConnection = New SqlConnection(connStr)
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)

If I execute the above code, then nothing gets updated. When I change the insert staement into the following,
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then,
value 1 for customerID field and myname in the namefield is added.
I know I am doing something stupid, but can't figure it out.
I am confused. please help me.One possibility is this (presuming textbox is name):

mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'" + name.text + "')", myConnection)

Better,use parameters, as the code above is subject to SQL Injection attacks, and as written will fail if the name.Text is "O'Reilly".|||Thanks, I used the parameters and it works fine.

Have Stored Procedure with input parameters and want to Use Spreadsheet

I have a stored procedure that is able to ultimately do an create a temp table and insert into another table. I'm trying to figure out how I could execute the stored procedure that would get it's input parameters from a spreadsheet rather than execute it line by line. Can anyone suggest anything?

CREATE PROCEDURE osp_xmlSvc_UpdateItemAttribute

@.PartNumber varchar(50) = '',

@.Rev varchar(50) = '',

@.AttributeName varchar(256) = '',

@.AttributeValue varchar(1024) = '',

@.EditSource varchar(255) = '',

@.UserName varchar(50) = 'admin',

@.ReturnState int = 0 Output

WITH ENCRYPTION

AS

DECLARE @.ItemID int

DECLARE @.RevID int

DECLARE @.AssignAllRevs int

DECLARE @.ParamIndexID int

DECLARE @.CurrentValue varchar(1024)

DECLARE @.UserID int

SET @.ReturnState = -1

SET @.ItemID = -1

SET @.RevID = -1

SET @.ParamIndexID = -1

SET @.CurrentValue = ''

SET @.AssignAllRevs = 0

SELECT @.UserID = ID FROM UserProfile WHERE UserName = @.UserName

SELECT @.ItemID = ID FROM Entry WHERE PartNumber = @.PartNumber

if(NOT(@.ItemID = -1) AND @.ItemID IS NOT NULL) begin

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Rev=@.Rev

if(@.RevID = -1 OR @.RevID IS NULL) begin

SET @.AssignAllRevs = 1

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Expired=0

end

SELECT @.ParamIndexID = ID FROM ParamIndex WHERE [Name]=@.AttributeName

if(NOT(@.ParamIndexID = -1) AND @.ParamIndexID IS NOT NULL) begin

SET @.ReturnState = 0

CREATE TABLE tmp_xml_AV (AttVal varchar(1024))

DECLARE @.tmpSQL nvarchar(2024)

SET @.tmpSQL = 'INSERT INTO tmp_xml_AV (AttVal) SELECT [' + @.AttributeName + '] FROM ParamValue WHERE Expired=0 AND ItemID=' + CAST(@.ItemID AS VARCHAR)+ ' AND RevID=' + CAST(@.RevID AS VARCHAR)

EXECUTE sp_executesql @.tmpSQL

SELECT @.CurrentValue = IsNull(AttVal, '') FROM tmp_xml_AV

DROP TABLE tmp_xml_AV

if(NOT(@.CurrentValue = @.AttributeValue) OR @.CurrentValue IS NULL) begin

SET @.ReturnState = 1

SET @.tmpSQL = 'UPDATE ParamValue SET [' + @.AttributeName + ']=''' + @.AttributeValue + ''' WHERE ItemID=' + CAST(@.ItemID AS VARCHAR)

if(@.AssignAllRevs = 0) begin

SET @.tmpSQL = @.tmpSQL + ' AND RevID=' + CAST(@.RevID AS VARCHAR)

end

EXECUTE sp_executesql @.tmpSQL

-- Record history

DECLARE @.tmpInt int

SELECT @.tmpInt = Max(ID)+1 FROM EntryChangeAction

INSERT INTO EntryChangeAction (ID,EntryAffected,RevID,ActionType,Details,Tool,UserID)

VALUES (@.tmpInt,@.ItemID,@.RevID,6,@.AttributeName + ': ' + @.CurrentValue + ' to: ' + @.AttributeValue,@.EditSource,@.UserID)

end

end

end

GRANT EXECUTE ON [dbo].[osp_xmlSvc_UpdateItemAttribute] TO [public]

GO

Thanks.

Amy

In the stored procedure, you could open and read the xls file and put the values into variables. (An xml file would be simpler -Excel could save the file as xml.)

Check in Books Online about [OpenXML].

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for
each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or
if I'm just missing something. Anyway the scan results is stored as an
XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
Now here is the insert statement for the table I am having trouble
with.
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
#temp.Name
If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.Because your procedure don't use sp_executeSql you can use a table variable,
not a temp table.
Declare @.Tab table
(
Field1 nvarchar(10),
Field2 int,
...(exactly the fields in the xml file)
)
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, @.Tab.ID, @.TabParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
@.Tab.scanattribute, getdate(), getdate()
FROM tblScan
INNER JOIN tblScanAttribute
JOIN@.Tab ON tblScanAttribute.Name =
@.Tab.Name
Of course fields must match...
Hope it helps
Benga.
"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...
> Using ms sql 2000
> I have 2 tables.
> I have a table which has information regarding a computer scan. Each
> record in this table has a column called MAC which is the unique ID for
> each Scan. The table in question holds the various scan results of
> every scan from different computers. I have an insert statement that
> works however I am having troulbe getting and update statement out of
> it, not sure if I'm using the correct method to insert and thats why or
> if I'm just missing something. Anyway the scan results is stored as an
> XML document(@.iTree) so I have a temp table that holds the relevent
> info from that. Here is my Insert statement for the temporary table.
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
>
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.
>|||While this is good to know my real problem is that I need the statement
that will do what my insert does accept I need it to be an update
statement. I need the update because an insert is only going to happen
once for each client.
Benga wrote:
> Because your procedure don't use sp_executeSql you can use a table variabl
e,
> not a temp table.
> Declare @.Tab table
> (
> Field1 nvarchar(10),
> Field2 int,
> ...(exactly the fields in the xml file)
> )
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, @.Tab.ID, @.TabParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> @.Tab.scanattribute, getdate(), getdate()
> FROM tblScan
> INNER JOIN tblScanAttribute
> JOIN@.Tab ON tblScanAttribute.Name =
> @.Tab.Name
> Of course fields must match...
> Hope it helps
> Benga.
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...|||Fixed it, no problems.
rhaazy wrote:
> While this is good to know my real problem is that I need the statement
> that will do what my insert does accept I need it to be an update
> statement. I need the update because an insert is only going to happen
> once for each client.
> Benga wrote:

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for

each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or

if I'm just missing something. Anyway the scan results is stored as an

XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.

INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)

Now here is the insert statement for the table I am having trouble
with.

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON
tblScanAttribute.Name =
#temp.Name

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.rhaazy (rhaazy@.gmail.com) writes:
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(),
> LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON
> tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:
> rhaazy (rhaazy@.gmail.com) writes:
> > INSERT INTO #temp
> > SELECT * FROM openxml(@.iTree,
> > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > WITH(
> > ID nvarchar(50) './@.ID',
> > ParentID nvarchar(50) './@.ParentID',
> > Name nvarchar(50) './@.Name',
> > scanattribute nvarchar(50) '.'
> > )
> > Now here is the insert statement for the table I am having trouble
> > with.
> > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > ScanID, AttributeValue, DateCreated, LastModified)
> > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > #temp.scanattribute, DateCreated = getdate(),
> > LastModified =
> > getdate()
> > FROM tblScan, tblScanAttribute JOIN #temp ON
> > tblScanAttribute.Name =
> > #temp.Name
> > If there is a way to do this without the temporary table that would be
> > great, but I haven't figured a way around it yet, if anyone has any
> > ideas that would be great, thanks.
> I have some difficulties to understand what your problem is. If all
> you want to do is to insert from the XML document, then you don't
> need the temp table, but you could use OPENXML directly in the
> query.
> But then you talk about an UPDATE as well, and if your aim is to insert
> new rows, and update existing, it's probably better to use a temp
> table (or a table variable), so that you don't have to run OPENXML twice.
> Some DB engines support a MERGE command which performs the task of
> UPDATE and INSERT in one statement, but this is not available in
> SQL Server, not even in SQL 2005.
> If this did not answer your question, could you please clarify?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fixed it no problems.
rhaazy wrote:
> My app runs on all my companies PCs every month a scan is performed and
> the resulst are stored in a database. So the first time a scan is
> performed for any PC it will be an insert, but after that it will
> always be an update. I tried using openxml in my insert statement but
> kept getting an error stating my sub query is returning more than one
> result... So since I couldn't do it that way I'm trying this method.
> All the relevent openxml is there I just couldn't figure out how to
> insert each column using it. If you have any suggestions I'm open to
> give it a try.
> Erland Sommarskog wrote:
> > rhaazy (rhaazy@.gmail.com) writes:
> > > INSERT INTO #temp
> > > SELECT * FROM openxml(@.iTree,
> > > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > > WITH(
> > > ID nvarchar(50) './@.ID',
> > > ParentID nvarchar(50) './@.ParentID',
> > > Name nvarchar(50) './@.Name',
> > > scanattribute nvarchar(50) '.'
> > > )
> > > > Now here is the insert statement for the table I am having trouble
> > > with.
> > > > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > > ScanID, AttributeValue, DateCreated, LastModified)
> > > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > > #temp.scanattribute, DateCreated = getdate(),
> > > LastModified =
> > > getdate()
> > > FROM tblScan, tblScanAttribute JOIN #temp ON
> > > tblScanAttribute.Name =
> > > #temp.Name
> > > > If there is a way to do this without the temporary table that would be
> > > great, but I haven't figured a way around it yet, if anyone has any
> > > ideas that would be great, thanks.
> > I have some difficulties to understand what your problem is. If all
> > you want to do is to insert from the XML document, then you don't
> > need the temp table, but you could use OPENXML directly in the
> > query.
> > But then you talk about an UPDATE as well, and if your aim is to insert
> > new rows, and update existing, it's probably better to use a temp
> > table (or a table variable), so that you don't have to run OPENXML twice.
> > Some DB engines support a MERGE command which performs the task of
> > UPDATE and INSERT in one statement, but this is not available in
> > SQL Server, not even in SQL 2005.
> > If this did not answer your question, could you please clarify?
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pr...oads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodin...ions/books.mspxsql

hash table (#) order by problem with more records

We have one single hash (#) table, in which we insert data processing
priority wise (after calculating priority).
for. e.g.

Company Product Priority Prod. QtyProd_Plan_Date
C1 P11100
C1 P22 50
C1 P33 30
C2 P11200
C2 P42 40
C2 P53 10

There is a problem when accessing data for usage priority wise.
Problem is as follows:

We want to plan production date as per group (company) sorted order and
priority wise.

==>With less data, it works fine.
==>But when there are more records for e.g. 100000 or more , it changes
the logical order of data

So plan date calculation gets effected.

==Although I have solved this problem with putting identity column and
checking in where condition.

But, I want to know why this problem is coming.

If anybody have come across this similar problem, please let me know
the reason and your solution.

IS IT SQL SERVER PROBLEM?

Thanks & Regards,
T.S.Negi> when there are more records for e.g. 100000 or more , it changes
> the logical order of data

Are you referring to the perceived order in the table? Rows in tables
have NO logical order in a relational database. If you require a
particular order you have to query them using a SELECT statement with
an ORDER BY clause otherwise the ordering is undefined.

If that doesn't answer your question then please describe your problem
with DDL (including keys), sample data INSERT statements and show your
required end result.

--
David Portas
SQL Server MVP
--|||While inserting records in hash table. It is already order by on some
fields.
But when selecting/updating records, I want the same order of records
should be updated/selected.

"Rows in tables have NO logical order in a relational database"
I think, True for hash(#) and permanent table.

T.S.Negi

David Portas wrote:
> > when there are more records for e.g. 100000 or more , it changes
> > the logical order of data
> Are you referring to the perceived order in the table? Rows in tables
> have NO logical order in a relational database. If you require a
> particular order you have to query them using a SELECT statement with
> an ORDER BY clause otherwise the ordering is undefined.
> If that doesn't answer your question then please describe your
problem
> with DDL (including keys), sample data INSERT statements and show
your
> required end result.
> --
> David Portas
> SQL Server MVP
> --|||There is an update condition. Which I want to make sure, performing on
ordered data (order by used at the time of insert).
I want to avoide loop.

Reason: "Rows in tables have NO logical order in a relational database"
!!!!

So Please advice.
Thanks,
T.S.Negi

Sample SQL:
===========

UPDATE #WK_PDR_ProcessingData SET
@.Opn_Stock_Qty= CASE WHEN (
@.Customer_Cd = Customer_Cd
AND @.Product_No = Product_No
AND @.Product_Site_Cd = Product_Site_Cd
AND @.Assy_Company_Cd = Assy_Company_Cd
AND @.Assy_Section_Cd = Assy_Section_Cd
AND @.Line_Cd = Line_Cd
) THEN @.Opn_Stock_Qty + @.Production_Qty - @.Requirement_Qty
ELSE begin_Stock_Qty END,
Calc_Stock_Qty= @.Opn_Stock_Qty + Production_Qty - Requirement_Qty,
@.Customer_Cd = Customer_Cd,
@.Product_No = Product_No,
@.Product_Site_Cd= Product_Site_Cd,
@.Assy_Company_Cd= Assy_Company_Cd,
@.Assy_Section_Cd= Assy_Section_Cd,
@.Line_Cd = Line_Cd,
@.Production_Qty = Production_Qty,
@.Requirement_Qty= Requirement_Qty
FROM #WK_PDR_ProcessingData|||tilak.negi@.mind-infotech.com (tilak.negi@.mind-infotech.com) writes:
> While inserting records in hash table. It is already order by on some
> fields.

And once it is inserted, there is no longer any order.

> But when selecting/updating records, I want the same order of records
> should be updated/selected.
> "Rows in tables have NO logical order in a relational database"
> I think, True for hash(#) and permanent table.

Well, obviously you have some operation that does not give you the
desired result, and you posted an UPDATE statement, which is a little
funny, because all you do is to assign a variable.

I suggest that you follow the standard recommendation and post:

o CREATE TABLE statement for your table(s)
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of what ou are trying to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||UPDATEs are not ordered either. The result of your UPDATE statement is
undefined, unreliable and, in my view, not useful.

Please specify the whole problem rather than post fragments of your
non-working solution. The best way to specify the problem is to post
DDL, sample data and required end results. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

hardware upgrade -> lower "insert" performance

Hey all,
I was about to move my SQL Server from a box that's about four years
old to a current box. The new box should be faster for everything ...
more RAM, faster CPU, faster disks... but the performance of repeated
"insert" statements is measurably worse. The following script took
2:16 on my old hardware (which is running my production system at the
same time), and 3:23 on the new hardware. "select" and "update"
statements do seem to be faster on the new hardware.
Anybody got any clue where I should be looking?
select getdate()
set nocount on
go
if exists
( select *
from sysobjects
where type = 'U'
and name = 'cccInsertTest')
begin
drop table cccInsertTest
end
go
create table cccInsertTest
( a int not null)
go
declare @.i int
select @.i = 0
while @.i < 50000
begin
insert into cccInsertTest (a) values (@.i)
select @.i = @.i + 1
end
go
select getdate()
go
hmmm, is the raid different?
"Chris Curvey" wrote:

> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>
|||Chris,
A couple of ideas:
Was the test database and log created with enough room so the database and
log didn't have to allocate more space (grow) while the process was running?
Instead of using getdate(), set the statistics time and statistics io
options on (QA Tools/Options, Connection Properties tab). Compare those
metrics instead. Also, run the tests on the servers using Remote Desktop to
eliminate any network transport (should be neglegible, but I'd do it
anyway).
-- Bill
"Chris Curvey" <ccurvey@.gmail.com> wrote in message
news:1170084503.675585.149280@.q2g2000cwa.googlegro ups.com...
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>
|||On Jan 29, 10:28 am, "Chris Curvey" <ccur...@.gmail.com> wrote:
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
>

> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
Turns out that there were two issues. We needed to turn on write
caching within our RAID controller. But the bigger problem was the
client program that we were using to drive the script. "isql" was
running at 4 xacts/sec. "osql" ran at 9 xacts/sec. Writing a Python
script (that ran from another machine, across a slow network link)
gave us 30 xacts/sec.
Go figure!

hardware upgrade -> lower "insert" performance

Hey all,
I was about to move my SQL Server from a box that's about four years
old to a current box. The new box should be faster for everything ...
more RAM, faster CPU, faster disks... but the performance of repeated
"insert" statements is measurably worse. The following script took
2:16 on my old hardware (which is running my production system at the
same time), and 3:23 on the new hardware. "select" and "update"
statements do seem to be faster on the new hardware.
Anybody got any clue where I should be looking?
select getdate()
set nocount on
go
if exists
( select *
from sysobjects
where type = 'U'
and name = 'cccInsertTest')
begin
drop table cccInsertTest
end
go
create table cccInsertTest
( a int not null)
go
declare @.i int
select @.i = 0
while @.i < 50000
begin
insert into cccInsertTest (a) values (@.i)
select @.i = @.i + 1
end
go
select getdate()
gohmmm, is the raid different?
"Chris Curvey" wrote:
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>|||Chris,
A couple of ideas:
Was the test database and log created with enough room so the database and
log didn't have to allocate more space (grow) while the process was running?
Instead of using getdate(), set the statistics time and statistics io
options on (QA Tools/Options, Connection Properties tab). Compare those
metrics instead. Also, run the tests on the servers using Remote Desktop to
eliminate any network transport (should be neglegible, but I'd do it
anyway).
-- Bill
"Chris Curvey" <ccurvey@.gmail.com> wrote in message
news:1170084503.675585.149280@.q2g2000cwa.googlegroups.com...
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>|||On Jan 29, 10:28 am, "Chris Curvey" <ccur...@.gmail.com> wrote:
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
>
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
Turns out that there were two issues. We needed to turn on write
caching within our RAID controller. But the bigger problem was the
client program that we were using to drive the script. "isql" was
running at 4 xacts/sec. "osql" ran at 9 xacts/sec. Writing a Python
script (that ran from another machine, across a slow network link)
gave us 30 xacts/sec.
Go figure!

hardware upgrade -> lower "insert" performance

Hey all,
I was about to move my SQL Server from a box that's about four years
old to a current box. The new box should be faster for everything ...
more RAM, faster CPU, faster disks... but the performance of repeated
"insert" statements is measurably worse. The following script took
2:16 on my old hardware (which is running my production system at the
same time), and 3:23 on the new hardware. "select" and "update"
statements do seem to be faster on the new hardware.
Anybody got any clue where I should be looking?
select getdate()
set nocount on
go
if exists
( select *
from sysobjects
where type = 'U'
and name = 'cccInsertTest')
begin
drop table cccInsertTest
end
go
create table cccInsertTest
( a int not null)
go
declare @.i int
select @.i = 0
while @.i < 50000
begin
insert into cccInsertTest (a) values (@.i)
select @.i = @.i + 1
end
go
select getdate()
gohmmm, is the raid different?
"Chris Curvey" wrote:

> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>|||Chris,
A couple of ideas:
Was the test database and log created with enough room so the database and
log didn't have to allocate more space (grow) while the process was running?
Instead of using getdate(), set the statistics time and statistics io
options on (QA Tools/Options, Connection Properties tab). Compare those
metrics instead. Also, run the tests on the servers using Remote Desktop to
eliminate any network transport (should be neglegible, but I'd do it
anyway).
-- Bill
"Chris Curvey" <ccurvey@.gmail.com> wrote in message
news:1170084503.675585.149280@.q2g2000cwa.googlegroups.com...
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>|||On Jan 29, 10:28 am, "Chris Curvey" <ccur...@.gmail.com> wrote:
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
>

> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
Turns out that there were two issues. We needed to turn on write
caching within our RAID controller. But the bigger problem was the
client program that we were using to drive the script. "isql" was
running at 4 xacts/sec. "osql" ran at 9 xacts/sec. Writing a Python
script (that ran from another machine, across a slow network link)
gave us 30 xacts/sec.
Go figure!

Handling SQLServerCE DataBase By windows application ?

Hi

i have the following problem :

i attempted to connect with a SQLServerCE DataBase

to Insert and update its rows, but i noticed that i want the reference :

System.Data.SqlServerCe

i went to (Add references) but i didn't find it ..

what should i do to break this problem ?

please help me !

Hi Imad

I have moved your treat to the Devices team who should be able to help you

mairead

PM, TS Data

|||

Hello and sorry for the delayed reply.

Just a question first - are you attemping to open a SqlServerCe database on your desktop PC or on a mobile device (like a Pocket PC / Smartphone)? This is important because we want to add the correct DLL to your project.

If you're opening the database on the desktop, then we can find the System.Data.SqlServerCe.DLL in the directory where DEVENV.EXE is. That would be something like C:\Program Files\Microsoft Visual Studio 8\Common7\IDE

If you're opening the database on a device, then we can find the System.Data.SqlServerCe.DLL in the Mobile SDK folder. That would be something like C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

If either of the DLLs are missing then it means your SQLServerCe SDK is not installed on your machine. The easiest way to fix this is to REPAIR the visual studio installation. This will re-install the SDK and ensure the DLLs are present on the machine.

Please let me know how it works out,

Kind regards,

Carlton Lane

Microsoft Visual Basic Team

|||

I am trying to use desktop application to open a Sqlserverce database that located on the PDA. but it throw this exception:

Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

I checked and see System.Data.SqlServerCe.DLL exists under:

D:\Program Files\Microsoft Visual Studio 8\Common7\IDE

D:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

Actually it throw that exception no matter what database I am trying to open. here is my code:

connStr = "Data Source =""Mobile Device\Program Files\Barcode_PDA\pda.sdf"";"
conn1 = New SqlServerCe.SqlCeConnection(connStr) < this generates the error

Thanks for your help!

|||

Hi Alex,

I'm sorry but the scenairo of opening a database on a device remotely from a desktop PC isnt supported by the SQLCE engine. Eventually, you'll get an error about the connection string being invalid. This is because the engine is targeting local data scenarios - that is where the Application and Database reside on the same machine, in the same process. This scenario starts to touch on client / server scenarios which currently arent supported.

Your current error about the missing dlls is telling us that the application is starting but cant find the engine. These dlls are found next to the System.Data.SqlServerCe.dll file. The ones in the Common7\IDE are for your PC. THe ones in SmartDevices\SDK are for the device. For a PC application, copy the ones from Common7\IDE into the executing directory of your application. But again, after you get pass this error, you will eventually get an error about the connection string being invalid because this scenario isnt supported.

HTH and good luck,

Carlton

|||

hi everybody

first thank you for helping me ..

but i want to tell you that i have solved my problem easily by installing sql server everywhere edition CTP

to install sql serverce tools and then all dll's have been loaded correctly ..

Handling SQLServerCE DataBase By windows application ?

Hi

i have the following problem :

i attempted to connect with a SQLServerCE DataBase

to Insert and update its rows, but i noticed that i want the reference :

System.Data.SqlServerCe

i went to (Add references) but i didn't find it ..

what should i do to break this problem ?

please help me !

Hi Imad

I have moved your treat to the Devices team who should be able to help you

mairead

PM, TS Data

|||

Hello and sorry for the delayed reply.

Just a question first - are you attemping to open a SqlServerCe database on your desktop PC or on a mobile device (like a Pocket PC / Smartphone)? This is important because we want to add the correct DLL to your project.

If you're opening the database on the desktop, then we can find the System.Data.SqlServerCe.DLL in the directory where DEVENV.EXE is. That would be something like C:\Program Files\Microsoft Visual Studio 8\Common7\IDE

If you're opening the database on a device, then we can find the System.Data.SqlServerCe.DLL in the Mobile SDK folder. That would be something like C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

If either of the DLLs are missing then it means your SQLServerCe SDK is not installed on your machine. The easiest way to fix this is to REPAIR the visual studio installation. This will re-install the SDK and ensure the DLLs are present on the machine.

Please let me know how it works out,

Kind regards,

Carlton Lane

Microsoft Visual Basic Team

|||

I am trying to use desktop application to open a Sqlserverce database that located on the PDA. but it throw this exception:

Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

I checked and see System.Data.SqlServerCe.DLL exists under:

D:\Program Files\Microsoft Visual Studio 8\Common7\IDE

D:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

Actually it throw that exception no matter what database I am trying to open. here is my code:

connStr = "Data Source =""Mobile Device\Program Files\Barcode_PDA\pda.sdf"";"
conn1 = New SqlServerCe.SqlCeConnection(connStr) < this generates the error

Thanks for your help!

|||

Hi Alex,

I'm sorry but the scenairo of opening a database on a device remotely from a desktop PC isnt supported by the SQLCE engine. Eventually, you'll get an error about the connection string being invalid. This is because the engine is targeting local data scenarios - that is where the Application and Database reside on the same machine, in the same process. This scenario starts to touch on client / server scenarios which currently arent supported.

Your current error about the missing dlls is telling us that the application is starting but cant find the engine. These dlls are found next to the System.Data.SqlServerCe.dll file. The ones in the Common7\IDE are for your PC. THe ones in SmartDevices\SDK are for the device. For a PC application, copy the ones from Common7\IDE into the executing directory of your application. But again, after you get pass this error, you will eventually get an error about the connection string being invalid because this scenario isnt supported.

HTH and good luck,

Carlton

|||

hi everybody

first thank you for helping me ..

but i want to tell you that i have solved my problem easily by installing sql server everywhere edition CTP

to install sql serverce tools and then all dll's have been loaded correctly ..

Handling Character large objects in Java application

In my Java application, I have a stream of character data in a java.io.Reader object.
I am using a PreparedStatement object to insert data into a table containing such a large object column (datatype - text). I am using the following API call:
PreparedStatement.setCharacterStream(colIndex, reader, size);

In order to find the size in the above statement, I read the stream and find the length.
Because of this I am getting the following error message and the data is not getting inserted:

Exception during insertion : Failed for MYTABLE Reason [Microsoft][SQLServer 2000 Driver for JDBC]Transliteration failed.

Is there any alternate method to handle this? Please help.Are you executing the statement within a loop? If that's the case, - see if this will help:

http://knowledgebase2.datadirect.com/kbase.nsf/SupportLink+Online/2471321MF?OpenDocument

handle errors in trigger

How to handle error in trigger.
i m inserting record in other table in after insert trigger. but if insert
statement in trigger has error, procedure quits with error and i cannot use
@.@.error to check itThats an artifical error, but that the way you could handle it:
Create Table SomeTable
(
SomeColumn INT
)
Go
ALTER TRIGGER SomeTrigger
on SomeTable
FOR INSERT
AS
BEGIN
RAISERROR('SomeError',16,1)
PRINT @.@.ERROR
IF @.@.ERROR <> 0
PRINT 'Seems that an error occured.'
END
INSERT INTO SomeTable VALUES (1)
DELETE FROM SomeTable
SELECT * FROM SomeTable
Keep in mind that some error aren=B4catchable due to the severity.
I suggest taking a look in Erland=B4s Error handling script
http://www.sommarskog.se/error-hand...triggercontext.
HTH, jens Suessmeyer.|||I don't know exactly what you're doing but there are a couple of things
you may want to check before you do an insert.
If you're using variables in your values list check them before you
insert if they aren't null in case your table has colums with not null
defined.
But in general do have a look at Erland's article.|||Ya variable is null and table does not allow null, but error should be
trapable isnt it ?
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137149721.665336.187300@.g44g2000cwa.googlegroups.com...
> I don't know exactly what you're doing but there are a couple of things
> you may want to check before you do an insert.
> If you're using variables in your values list check them before you
> insert if they aren't null in case your table has colums with not null
> defined.
> But in general do have a look at Erland's article.
>|||Error handling in sql server 2000 within triggers is tricky business,
have a look at Erland's article.
http://www.sommarskog.se/error-hand...triggercontext.
But since you know that the variable is null then something like this
may avoid the error:
DECLARE @.var int
SET @.var = ISNULL(@.var, 1) /* the variable will be assigned the value
of 1 if it is null
or
IF(@.var) IS NULL
BEGIN
-- write all variables to a log table
INSERT INTO ...
RETURN
END|||The AFTER trigger fires *after* the insert - hence the name. In case of a
constraint violation (such as the nullability constraint) the the AFTER
trigger will not fire at all, since the insert is aborted.
Consider using an INSTEAD OF trigger or better yet - set values properly
before inserting them or use a deafult.
ML
http://milambda.blogspot.com/|||who says the variable is coming from inserted?|||I was guessing based on OP's narrative.
ML
http://milambda.blogspot.com/|||from OP
> but if insert statement in trigger|||> but if insert statement in trigger

Handle DTS Errors

Hi,
I'm using DTS for pulling data from Oracle into SQL Server.

I retrieve the records from Oracle and insert into a staging table and from there invoke an ExecuteTask in DTS to transfer the data to the SQL Server table deciding on whether to Insert / Update / Delete appropriately based on certain parameters.

During the Insert / Update / Delete there might be constraints on the original SQL Server table which might throw errors in case of data inconsistencies. I want to track the errors arising in this scenario and take a call on whether to ignore them or not.

When I schedule the DTS package from the designer the constraint violations are picked up by the DTS and it stops the package execution. Later I coded the same on VBScript and I've handled the error using the WithEvents along with the Package2 object and tried giving a Cancel = False in the OnError Event.

But this does not seem to be working. The package is still exiting abnormally. The piece of code which I've used to Cancel the DTS Error is given below.

Private Sub goPackage_OnError(ByVal EventSource As String, _
ByVal ErrorCode As Long, _
ByVal Source As String, _
ByVal Description As String, _
ByVal HelpFile As String, _
ByVal HelpContext As Long, _
ByVal IDofInterfaceWithError As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Error"
pbCancel = False
End Sub

Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Query cancelled"
pbCancel = False
End Sub

Setting pbCancel = False should ideally inform the DTS package to continue with execution and ignore the Errors but then the package is still terminating abnormally when an error is encountered and the rest of the records do not get processed.

Any suggestions in this regard would be greatly appreciated.A couple of thoughts:

1. Your code includes the statements Debug.print. I don't think that Debug.print is supported in VBScript and it's not supported at runtime in VB.

2. Have you looked at DTSTransformStat_SkipRow? I'm having a hard time picturing where this code is in your DTS Package. However, if you use the ActiveX script to copy data over, you can specify that on an error the row should be skipped (and you can log that row if you wanted). For example:

Function Main

DTSDestination("Foo") = DTSSource("Foo")
DTSDestination("Bar") = DTSSource("Bar")

errCount = 0

' Do your exception handling here
If DTSSource("Foo") > 10 Then
errCount = errCount + 1
End if

If DTSSource("Bar") < 8 Then
errCount = errCount + 1
End if

If errCnt > 0 Then
Main = DTSTransformStat_SkipRow
Else
Main = DTSTransformStat_OK
End If
End function

Note that this is probably a very sloppy (not to mention slow) way of doing things. Nevertheless, it is a potential solution to your question.

Hopefully, it will give you some ideas that you can pursue.

Regards,

Hugh Scott

Originally posted by Mohana Krishnan
Hi,
I'm using DTS for pulling data from Oracle into SQL Server.

I retrieve the records from Oracle and insert into a staging table and from there invoke an ExecuteTask in DTS to transfer the data to the SQL Server table deciding on whether to Insert / Update / Delete appropriately based on certain parameters.

During the Insert / Update / Delete there might be constraints on the original SQL Server table which might throw errors in case of data inconsistencies. I want to track the errors arising in this scenario and take a call on whether to ignore them or not.

When I schedule the DTS package from the designer the constraint violations are picked up by the DTS and it stops the package execution. Later I coded the same on VBScript and I've handled the error using the WithEvents along with the Package2 object and tried giving a Cancel = False in the OnError Event.

But this does not seem to be working. The package is still exiting abnormally. The piece of code which I've used to Cancel the DTS Error is given below.

Private Sub goPackage_OnError(ByVal EventSource As String, _
ByVal ErrorCode As Long, _
ByVal Source As String, _
ByVal Description As String, _
ByVal HelpFile As String, _
ByVal HelpContext As Long, _
ByVal IDofInterfaceWithError As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Error"
pbCancel = False
End Sub

Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Query cancelled"
pbCancel = False
End Sub

Setting pbCancel = False should ideally inform the DTS package to continue with execution and ignore the Errors but then the package is still terminating abnormally when an error is encountered and the rest of the records do not get processed.

Any suggestions in this regard would be greatly appreciated.|||Thanks buddy. This did give me a idea. I split my tables into Staging tables and Temporary tables. Finally added a task in between to make sure that all the erroneous records are filtered out and moved to the respective error tables. I wanted to check for foreign key violations mostly. Later I went ahead and inserted the rest of the records in the staging tables directly into the master tables. This worked out fine.

Thanks anyway.
Regards
Mohan