@@Identity not returning Value

I have a stored procedure that inserts a record. I call the @.@.Identity variable and assign that to a variable in my SQL statement in my asp.net page.

That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @.@.Identity field is no longer being returned.

As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?

When I pass the value of the TicketID variable to a text field after the insert, it gives me "@.TicketID".

Anyone have any ideas?

CREATE PROCEDURE [iguser].[newticket]
(
@.Category nvarchar(80),
@.Description nvarchar(200),
@.Detail nvarchar(3000),
@.OS nvarchar(150),
@.Browser nvarchar(250),
@.Internet nvarchar(100),
@.Method nvarchar(50),
@.Contacttime nvarchar(50),
@.Knowledge int,
@.Importance int,
@.Sendcopy bit,
@.Updateme bit,
@.ClientID int,
@.ContactID int,
@.TicketID integer OUTPUT
)
 AS
INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@.Category,
@.Description,
@.Detail,
@.OS,
@.Browser,
@.Internet,
@.Method,
@.Contacttime,
@.Knowledge,
@.Importance,
@.Sendcopy,
@.Updateme
)
SELECT
@.TicketID = @.@.Identity
INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@.ContactID,
@.TicketID
)
INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@.ClientID,
@.TicketID
)

Fixed the problem, it was with my .net code|||The best practice is to constrain, you should use IDENT_CURRENT('Tickets') instead of @.@.IDENTITY when you are inserting into multiple tables. IDENT_CURRENT gives you the last Identity generated in a specific table, as @.@.IDENTITY has no constraint and returns the last Identity of any table in the session or scope.

Based on your execution @.@.IDENTITY will work, but I figured I would throw this out there anyway.

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"
Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"
|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>> I'm running the following within a For Next loop but the
>> @.@.identity value doesn't always show up. It only shows
>> for every 3rd record. Any ideas on why this might be?
>> Thanks,
>> Ben Bleser
>> sql = "INSERT INTO table . . . . "
>> conn.execute(sql)
>> sql = "Select @.@.Identity as Id"
>> set rsTmp = conn.execute(sql)
>> RequestID = rsTmp("Id")
>> rsTmp.close
>> set rsTmp = nothing
>> Response.write RequestID & "<br><br>"
>|||Hello Ben,
Take a look at SCOPE_IDNTITY() too - in books online.
Cheers
SQLCatZ

@@identity

Consider Table A having an Identity column.
If when I want insert a record to the table, I want to have the autogenerated value to be inserted into another column within the same table and same row, how am I going to do it?
rignhomCheck @.@.Identity from BOOKS ONLINE to get last-inserted identity value.|||Originally posted by Satya
Check @.@.Identity from BOOKS ONLINE to get last-inserted identity value.

Actually, to be safer, I have found that SCOPE_IDENTITY() is better than @.@.IDENTITY. Both do the same thing, but if you happen to have 2 identities being created within different execution scopes, SCOPE_IDENTITY always seems to return the correct one.|||Hi, what I want is
consider TableA with (Column1 Identity, Column2, Column3).

When I insert into this table
I want to issue sort of

INSERT INTO jobs (Column2,Colum3)
VALUES ('Col2Value', @.@.IDENTITY)

I want Column3 to have the same value as the newly autogenerated value for Column1.

Thanks
rignhom|||CREATE TRIGGER TRG_NAME ON TableA
FOR INSERT
AS
BEGIN
DECLARE @.ID BIGINT
SELECT @.ID = Column1 FROM INSERTED

UPDATE TableA
SET Column3 = @.ID
WHERE Column1 = @.ID
END|||if Column3 is a does not allow Null, would this trigger be useful?|||I don't know of another way of retrieving the id value, because the values for either @.@.identity and scope_identity() are only know after the insert in the table. To solve your problem you can define a default value of let's say 0 (zero), this value will then be overwritten by the trigger.|||hmm.. that's a way.. but would the cost be too high, since there's two write. And it may hit concurrency as well..|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

HTH|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

Can you explain what the intention is? Does your insert consists of two selects? How do you know the value for column3 before the actual insert?|||If you run this after running my prior SQL

select * from syscolumns where name in ('ikey','column2','column3')

You will see that ikey and column 3 are almost identical (except for xoffset which I am not sure what it's for, BOL says internal use only)

That makes me think that in one insert it is seeing the insert of those two columns as the same, that's why you are able to specify the insert with only one value even though you have three columns. Not sure if that is answering your question and I'm not sure exactly how it works but I know it's faster and preferred than a Trigger.|||nice ...sql

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identitiy always returns a value of 1 from sp

The data is entered correctly but identity of new record is always sent back
as 1 .
Why is this always returning a value of 1 ?
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT SCOPE_IDENTITY()Without answering the original question, let me tell
you that your approach is totally wrong.
Why you have to pass all the sql from the client side
and use the SP just to call sp_executesql ?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"TJS" <nospam@.here.com> wrote in message
news:e3R8CnXHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The data is entered correctly but identity of new record is always sent
> back as 1 .
> Why is this always returning a value of 1 ?
> sp:
> Create PROCEDURE AddPlayer
> @.SQLCMD nvarchar(1000)
>
> AS
> BEGIN
> EXECUTE sp_executesql @.SQLCMD
> END
> SELECT SCOPE_IDENTITY()
>
>|||A much more important question is why are you using dynamic SQL to
insert a row? Don't do that if you can possibly avoid it. Instead, add
parameters for each column and insert them with a static INSERT
statement.
SCOPE_IDENTITY() won't see the IDENTITY value in this example because
sp_executesql has its own scope. Use @.@.IDENTITY instead, although if
you have a trigger on the table then @.@.IDENTITY will give you the last
value inserted by a trigger.
David Portas
SQL Server MVP
--|||The SQL is generated dynamically, so I just pass it through once developed.
Seemed like a good solution.
I tried using "SELECT @.@.IDENTITY" in the procedure but it still returns a
value of 1.
Is there a way to requery the table and get the maximum vaslue of the
identity , within the same procedure and have it return that value ?|||> The SQL is generated dynamically, so I just pass it through once
developed.
> Seemed like a good solution.
Then I suggest that you should do some serious studying of development
best practices in SQL. Dynamic SQL is something to be avoided unless
you have exceptional cause to need it. There are good reasons for this.
The following article discusses some of them:
http://www.sommarskog.se/dynamic_sql.html

> Is there a way to requery the table and get the maximum vaslue of the

> identity , within the same procedure and have it return that value ?
You should always be able to retrieve the IDENTITY value after an
INSERT by using an alternate key of the table because IDENTITY should
never be the only key. Having said that, the @.@.IDENTITY method should
work in the scenario you have given so if you need more help please
post some code that will reproduce the problem for us: A CREATE TABLE
statement and some code to call the SP.
David Portas
SQL Server MVP
--|||here is the revised code
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT @.@.IDENTITY|||It works for me:
CREATE TABLE T1 (x INTEGER IDENTITY PRIMARY KEY, z INTEGER NOT NULL
UNIQUE)
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (2)'
Result:
(1 row(s) affected)
---
1
(1 row(s) affected)
(1 row(s) affected)
---
2
(1 row(s) affected)
David Portas
SQL Server MVP
--|||Did you check to see if there is a trigger on this table?
David Portas
SQL Server MVP
--|||no triggers
I don't see where in your sp the identity being returned ?
what did you use to return the last identity
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109627060.294495.297220@.o13g2000cwo.googlegroups.com...
> Did you check to see if there is a trigger on this table?
> --
> David Portas
> SQL Server MVP
> --
>|||I was calling your SP:
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
If you get a different result then please post some complete code that
we can actually run, including the CREATE TABLE statement and INSERT
statement(s).
David Portas
SQL Server MVP
--

If-Then in a select statement ?

I'm pulling a recordset for the last 8 weeks of data, but need to add a true/false flag in the recordset to indicate if the record is within the last 12 hours.

tblData.StartTime is the column that has the time for the record. I'm familiar with datediff() - but how do I check the value returned from that function to get a true/false (or 1/0) returned as a column in the select statement.

Select *, [What do I put here] FROM tblData

Thanks for your help,

AlexDECLARE @.myDateCol datetime
SELECT @.myDateCol = '1/19/2005'
SELECT CASE WHEN DateDiff(hh,@.myDateCol,GetDate()) < 12 THEN 1 ELSE 0 END AS TwelveHour_Ind|||I need to use this in a view...which doesn't support the case statement.

Any other way you can think of?

Thanks,

Alex|||Case statements are only not supported by Enterprise Manager. If you use Query Analyzer, you will be able to use Case statements.|||Use CASE for clarity, but if you like, here is an alternative method in the spirit of that famous Computer Scientist, Dr. Rube Goldberg:

declare @.TestDate datetime
set @.TestDate = '2005-01-19 14:39:29.530'
SET @.TestDate = dateadd(hh, -12, getdate())

select cast(sign(DateDiff(hh, GetDate(), @.TestDate) + 12) + 1 as bit)|||Open EM, and use it for refernece purposes...

NEVER Develop in EM, use QA.

CREATE VIEW myView99 AS
SELECT CASE WHEN DateDiff(hh,@.myDateCol,GetDate()) < 12 THEN 1 ELSE 0 END AS TwelveHour_Ind
, Col2
, Col3
FROM myTable99

.Update creates new record but all fields null

Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3

rsUpdateEntry.AddNew

rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")


rsUpdateEntry.Update

rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
MarkAt the risk of offending you, may I suggest that you instead consider a stored procedure? At first it require a bit more effort and thought, but in the long run, stored procedures are a sensible way to handle database activity (including updates, inserts, selects and deletes).

As for the code you've written, are you sure that the .Add is in the right place? It looks to me (though I don't write my code this way) like it should come after you have assigned all of the values.

Regards,

hmscott

.NET class to hold single disconnected record? Nothing smaller than DataSet?

What is the most efficient standalone .NET class that can hold a single disconnected record? The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).

If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.

I'm familiar with DataSet, is that the only .NET-standard class to do this?

Thank you,

Shannon

Why not create a normal class with properties and fill it from a SqlDataReader, or use a OR-Mapper..?

|||

I second N Frederick 's suggestiong, nothing will be faster (or easier) than a datareader populating a regular class

|||

Thanks for your reply. If I understand correctly, you are suggesting that in most application-specific design scenarios, a strongly-typed class representing my scenario data object is proscribed? "normal class" assumes that I would benefit from get/set properties for myself to code against. And O/R mapper suggestion to accomplish the same.

I'm building a generic server control (an in-place editor with HTML client-side functionality). I just want something to do table-based forms against for now.

My comment that I need to store data type and column name result from the fact that I won't know at design-time (of the server control) what they are.

p.s. If you are aware of a free .NET library to do this already, I'm all ears.

|||

TwasBrillig:

you are suggesting that in most application-specific design scenarios, a strongly-typed class representing my scenario data object is proscribed? "normal class" assumes that I would benefit from get/set properties for myself to code against. And O/R mapper suggestion to accomplish the same

Speaking for myself, I wouldn't overanalyze. The requirement you gave originally is very simple, so solve it with something very simple. OTOH....

TwasBrillig:

comment that I need to store data type and column name result from the fact that I won't know at design-time (of the server control) what they

that's a different story, if you don't know at design time what you're getting back then you can't put it in a predefined class. I suspect that your requirements are much more complex than yo've get on

|||

dbland07666:

TwasBrillig:

you are suggesting that in most application-specific design scenarios, a strongly-typed class representing my scenario data object is proscribed? "normal class" assumes that I would benefit from get/set properties for myself to code against. And O/R mapper suggestion to accomplish the same

Speaking for myself, I wouldn't overanalyze. The requirement you gave originally is very simple, so solve it with something very simple. OTOH...

That's the second cryptic reply that implies in an offhand way that the answer is simple, but doesn't answer. I hear you, the answer is 42. But what does it mean? Sure I'd love to do something very simple; What is very simple to store one record, for goodness sake? A DataSet? That was my question.

dbland07666:

TwasBrillig:

comment that I need to store data type and column name result from the fact that I won't know at design-time (of the server control) what they

that's a different story, if you don't know at design time what you're getting back then you can't put it in a predefined class. I suspect that your requirements are much more complex than yo've get on

Not knowing the type is different from not knowing the structure. A record. One table record. Sure I can build a class myself to do it, I can iterate through columns and store Column-name:Object pairs in a dictionary list, and inspect later what type is in the box. No, my requirements aren't more complex. But my question is: Is there a class already in the .NET library to hold one record, one I can copy one record into with a single method call? When I'm done I have one record in storage, and can disconnect my DbDataReader and think about my one record, its columns' values, its columns' data types, and their names for as long as I like?

|||

Here are the types in ADO.Net that you can use:

DataSet = Table, rows and columns, a disconnected resultset that more or less reflects a databas but in memory.

DataReader = Need a connection and will read a row on command, so it will only stay at a specific position and move to the next recoed when you ask it to do it. See it as a Row, Column based object.

You can also use DataSet with XML etc.

Then you can create your own classes (custom business objects "entities"), such as a Customer, Product etc.

In your case where you specify that you can't use a custom class, I think you should simply use a DataSet.

|||

Thank you Fredrik, that absolutely answered my question. I appreciate you taking your time answering something so basic.

I felt like there must be another class out there that I had overlooked in the library, or some other magic to DbDataRecord that I wasn't aware of.

If I'm not mistaken, I could reliably use DataReader to access the current row after disconnecting it if I only ever asked it for the values, and not any schema information (such as column name or data type).

Thanks again!

**** How do you detect a change in the record using T-SQL? *****

Hello,
I need to write a stored procedure that will run every morning or
night.
Basically I need to keep my PRODUCT table in SQL 2000 with up-to-update
information
from the master PRODUCTION table on the mainframe (AS400).
Let's say I have the following for simplicity:
On mainframe:
--
Table name: MASTER_PRODUCT
PRODUCT_ID PROD_DESC
--
1001 KEYBOARD
1002 MOUSE
--
and on the SQL side, I have the same table with the same columns. When
the job
runs and if there is a change in the PROD_DESC in any of the PRODUCT_ID
on the
mainframe, I want to update that PROD_DESC on the SQL side.
I was wondering what would be the appropriate and efficient way to
handle this type
of action/processing using T-SQL in the stored procedure? Is it
possible to write
it without using cursor and looping through and findind out which
record got changed.
Any help would be greatly appreciated.
Thanks in advance!
Sydney Luusydney.luu@.gmail.com wrote:
> Hello,
> I need to write a stored procedure that will run every morning or
> night.
> Basically I need to keep my PRODUCT table in SQL 2000 with
> up-to-update information
> from the master PRODUCTION table on the mainframe (AS400).
> Let's say I have the following for simplicity:
> On mainframe:
> --
> Table name: MASTER_PRODUCT
> --
> PRODUCT_ID PROD_DESC
> --
> 1001 KEYBOARD
> 1002 MOUSE
> --
> and on the SQL side, I have the same table with the same columns.
> When the job
> runs and if there is a change in the PROD_DESC in any of the
> PRODUCT_ID on the
> mainframe, I want to update that PROD_DESC on the SQL side.
> I was wondering what would be the appropriate and efficient way to
> handle this type
> of action/processing using T-SQL in the stored procedure? Is it
> possible to write
> it without using cursor and looping through and findind out which
> record got changed.
> Any help would be greatly appreciated.
> Thanks in advance!
> Sydney Luu
You can use the BINARY_CHECKSUM function to compute a checksum value for
the entire row in the table. You can compare the checksum values of the
SQL Server table to that of the imported mainframe table and update
those rows where the checksum is different.
David Gugick
Imceda Software
www.imceda.com|||How perfect is this method? I have seen it used but had never really used
it. I would have just compared it via a where clause. Also, shouldn't you
use CHECKSUM only in this case, since hopefully you will have proper cased
the values in the transform.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e8Dp33wOFHA.1040@.TK2MSFTNGP12.phx.gbl...
> sydney.luu@.gmail.com wrote:
> You can use the BINARY_CHECKSUM function to compute a checksum value for
> the entire row in the table. You can compare the checksum values of the
> SQL Server table to that of the imported mainframe table and update those
> rows where the checksum is different.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I've used it and it seems to "Sorta" work.
I'm with you though, I'm not sure I totally trust the checksum gig.
GAJ|||Louis Davidson wrote:
> How perfect is this method? I have seen it used but had never really
> used it. I would have just compared it via a where clause. Also,
> shouldn't you use CHECKSUM only in this case, since hopefully you
> will have proper cased the values in the transform.
>
Louis,
I think it's about as reliable as any checksum or hash. It's possible to
get the same value back after a change, but it's highly unlikely. BOL
recommends the binary version to detect changes in a row. I've only used
to the function as a proof of concept to do what the OP asked (a while
back). If I remember, I used another table that mapped the PK values to
the checksum like the BOL example.
Using the binary version allows you to pick up case changes to any text
(e.g. GReen vs. Green):
create table #emp (col1 int not null identity, col2 varchar(20) not
null)
create table #emp_bc (col1 int not null, checkval int not null)
insert into #emp values ('David GReen')
insert into #emp values ('David Smith')
insert into #emp values ('David Jones')
Insert into #emp_bc (col1, checkval)
Select col1, BINARY_CHECKSUM(*)
From #emp
Select * from #emp_bc
col1 checkval
-- --
1 -163446450
2 -162136984
3 -162735501
Update #emp
set col2 = 'David Green'
Where col1 = 1
Select a.col1, c.col2 as "NewVal"
from #emp_bc a
inner join #emp c
on a.col1 = c.col1
inner join (
Select col1, BINARY_CHECKSUM(*) as "checkval"
From #emp) b
on a.col1 = b.col1
Where a.checkval != b.checkval
col1 NewVal
-- --
1 David Green
OR
SELECT col1
FROM #emp_bc
WHERE EXISTS (
SELECT *
FROM #emp
WHERE #emp.col1 = #emp_bc.col1
AND BINARY_CHECKSUM(*) <> #emp_bc.checkval)
col1
--
1
Drop Table #emp_bc
Drop Table #emp
David Gugick
Imceda Software
www.imceda.com|||pdxJaxon wrote:
> I've used it and it seems to "Sorta" work.
> I'm with you though, I'm not sure I totally trust the checksum gig.
>
> GAJ
It's far from foolproof. These values produces the same checksum:
Select
BINARY_CHECKSUM('A'),
BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA')
Thanks to Narayana over at http://vyaskn.tripod.com
David Gugick
Imceda Software
www.imceda.com|||David
I have used this one to detect the changes
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ObQjTgyOFHA.4000@.TK2MSFTNGP15.phx.gbl...
> Louis Davidson wrote:
> Louis,
> I think it's about as reliable as any checksum or hash. It's possible to
> get the same value back after a change, but it's highly unlikely. BOL
> recommends the binary version to detect changes in a row. I've only used
> to the function as a proof of concept to do what the OP asked (a while
> back). If I remember, I used another table that mapped the PK values to
> the checksum like the BOL example.
> Using the binary version allows you to pick up case changes to any text
> (e.g. GReen vs. Green):
> create table #emp (col1 int not null identity, col2 varchar(20) not
> null)
> create table #emp_bc (col1 int not null, checkval int not null)
> insert into #emp values ('David GReen')
> insert into #emp values ('David Smith')
> insert into #emp values ('David Jones')
> Insert into #emp_bc (col1, checkval)
> Select col1, BINARY_CHECKSUM(*)
> From #emp
> Select * from #emp_bc
> col1 checkval
> -- --
> 1 -163446450
> 2 -162136984
> 3 -162735501
> Update #emp
> set col2 = 'David Green'
> Where col1 = 1
> Select a.col1, c.col2 as "NewVal"
> from #emp_bc a
> inner join #emp c
> on a.col1 = c.col1
> inner join (
> Select col1, BINARY_CHECKSUM(*) as "checkval"
> From #emp) b
> on a.col1 = b.col1
> Where a.checkval != b.checkval
> col1 NewVal
> -- --
> 1 David Green
> OR
> SELECT col1
> FROM #emp_bc
> WHERE EXISTS (
> SELECT *
> FROM #emp
> WHERE #emp.col1 = #emp_bc.col1
> AND BINARY_CHECKSUM(*) <> #emp_bc.checkval)
> col1
> --
> 1
>
> Drop Table #emp_bc
> Drop Table #emp
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Uri Dimant wrote:
> David
> I have used this one to detect the changes
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
That works as well as the binary version. If you're looking for an easy,
but not completely foolproof, method to compare data in tables, I think
it's a good fit. But as my other post mentioned, some values produce the
same checksum... in a lab, and the agg function returns the same value
as well. Whether you're likely to see the same test data in a real world
application is debatable, but it's worth knowing the function is not
foolproof.
David Gugick
Imceda Software
www.imceda.com|||Yep, David
Based on your DDL, CHECKSUM does show a difference on text columns as
opposite BINARY_CHECKSUM does.
create table #emp (col2 varchar(20) not null)
create table #emp_bc (col2 varchar(20) not null)
insert into #emp values ('David GReen')
insert into #emp values ('David Smith')
insert into #emp values ('David Jones')
insert into #emp_bc values ('David Green')
insert into #emp_bc values ('David Smith')
insert into #emp_bc values ('David Jones')
if (select checksum_agg(BINARY_CHECKSUM(*)) from #emp)
<> (select checksum_agg(BINARY_CHECKSUM(*)) from #emp_bc)
print 'different'
else
print 'probably the same'
drop table #emp,#emp_bc
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OHIdfXzOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> Uri Dimant wrote:
> That works as well as the binary version. If you're looking for an easy,
> but not completely foolproof, method to compare data in tables, I think
> it's a good fit. But as my other post mentioned, some values produce the
> same checksum... in a lab, and the agg function returns the same value
> as well. Whether you're likely to see the same test data in a real world
> application is debatable, but it's worth knowing the function is not
> foolproof.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Very stuff. Thanks for the information.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uATTb7zOFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Yep, David
> Based on your DDL, CHECKSUM does show a difference on text columns as
> opposite BINARY_CHECKSUM does.
> create table #emp (col2 varchar(20) not null)
> create table #emp_bc (col2 varchar(20) not null)
> insert into #emp values ('David GReen')
> insert into #emp values ('David Smith')
> insert into #emp values ('David Jones')
> insert into #emp_bc values ('David Green')
> insert into #emp_bc values ('David Smith')
> insert into #emp_bc values ('David Jones')
> if (select checksum_agg(BINARY_CHECKSUM(*)) from #emp)
> <> (select checksum_agg(BINARY_CHECKSUM(*)) from #emp_bc)
> print 'different'
> else
> print 'probably the same'
> drop table #emp,#emp_bc
>
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OHIdfXzOFHA.1884@.TK2MSFTNGP15.phx.gbl...
>

(system) Variable that holds the record count of a result set?

Hello
is there a variable that is available to me that contains the number
of rows contained in a dataset return from a database call?

have a class that runs a stored proc and returns a dataset/resultset
looking to simply assign an integer this value if it is possible

i'm using (learning) vb.net and sql server

thanks in advance

If you are using a dataset, you can get the number of rows with this syntax:

DataSet1.tables(0).rows.count (assuming your Dataset returns only 1 resultset. If you have more than 1, just replace the zero with whatever index you need.)

If you use a DataReader, which tends to be faster, this property is not available, unfortunately.|||

hello. thank you for the reply. that is exactly what i was looking for (DataSet1.tables(0).rows.count). a question about how to reference this from the codebehind...

i have a codebehind that Dims a class, the class returns the dataset, in the codebehind i have a line likeDropDownList1.DataSource = myClass.function1(parm1) to populate the dropdown with the data from the dataset. i get how i could reference this value in the class code, but how would i reference it in the codebehind? is there a way. would i have to do it in the class function and store it in a session variable or something? would be like to reference it directly in the codebehind as that is where i would be using the value.

thanks again.

|||

i believe i have figured my question out (yup, i'm new), but if you have any comments i'd appreciate it

instead of directly coding the line as DDL1.DataSource = myClass.function1(parm1)

i modified the code to have...

Dim ds as dataSet = myClass.function1(parm1)
Dim dsCnt as integer = ds.Tables(0).Rows.Count()
DDL1.DataSource = ds

this seemed to get me what i was after. if there is a more efficient or elegant way to do this, i'm all ears

|||The way you've done it is fine. An alternative is to create a property of type dataset in your class, then fill that property in your function. The code behind could then reference your property.

Public Class myClass

Private mMyDataset as dataset

Public Sub New()
MyBase.new()
End Sub

Property MyDataset() As dataset
Get
Return mMyDataset
End Get
Set(ByVal Value As dataset)
mMyDataset = Value
End Set
End Property

Public Sub function1(param) 'can change to a sub since you are filling a property rather than using a return value
myDataset = Database call goes here
End Sub

From your code behind:

dim objClass as new myClass()

with objClass
.function1(param)
DDL1.datasource = .myDataset
DDL1.databind
end with