Showing posts with label value. Show all posts
Showing posts with label value. Show all posts

Tuesday, March 27, 2012

@@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 does not return the correct value.

@.@.Identity does not return the correct value.
Anyone have any ideas how to fix this,
and more important,
how does it get out of sync and return the wrong value?
Account.AccountID = (int IDENTITY, PRIMARY KEY )
/* Three statements executed together */
EXEC ('DBCC CheckIdent(Account)')
INSERT Account(AccountNumber, AccountAccountStatus,
AccountAgency, AccountAgencyClientID,
AccountPaymentAmount)
VALUES('9',2,1,'sadasd',787.8)
SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
(AccountID) as "Max(AccountID)" from Account
/* Results of three statements executed together */
Checking identity information: current identity
value '261', current column value '261'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
(1 row(s) affected)
AccountID = @.@.IDENTITY Max(AccountID)
-- --
543 262
(1 row(s) affected)Do you have a trigger on the table? What does SCOPE_IDENTITY() give you?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ken" <KLomax@.Noteworld.com> wrote in message
news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> @.@.Identity does not return the correct value.
> Anyone have any ideas how to fix this,
> and more important,
> how does it get out of sync and return the wrong value?
> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> /* Three statements executed together */
> EXEC ('DBCC CheckIdent(Account)')
> INSERT Account(AccountNumber, AccountAccountStatus,
> AccountAgency, AccountAgencyClientID,
> AccountPaymentAmount)
> VALUES('9',2,1,'sadasd',787.8)
> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> (AccountID) as "Max(AccountID)" from Account
> /* Results of three statements executed together */
> Checking identity information: current identity
> value '261', current column value '261'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> (1 row(s) affected)
> AccountID = @.@.IDENTITY Max(AccountID)
> -- --
> 543 262
> (1 row(s) affected)
>|||Do you have a trigger on the table, perhaps doing an insert to another
table?
If so, that's why you'll see that problem... Use SCOPE_IDENTITY() instead...
"Ken" <KLomax@.Noteworld.com> wrote in message
news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> @.@.Identity does not return the correct value.
> Anyone have any ideas how to fix this,
> and more important,
> how does it get out of sync and return the wrong value?
> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> /* Three statements executed together */
> EXEC ('DBCC CheckIdent(Account)')
> INSERT Account(AccountNumber, AccountAccountStatus,
> AccountAgency, AccountAgencyClientID,
> AccountPaymentAmount)
> VALUES('9',2,1,'sadasd',787.8)
> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> (AccountID) as "Max(AccountID)" from Account
> /* Results of three statements executed together */
> Checking identity information: current identity
> value '261', current column value '261'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> (1 row(s) affected)
> AccountID = @.@.IDENTITY Max(AccountID)
> -- --
> 543 262
> (1 row(s) affected)
>|||Tibor:
You are the man!
I did not realize @.@.Identity was not scope specific.
SCOPE_IDENTITY() fixes the problem.
I have a trigger that inserts audit records on updates and
inserts, so @.@.Identity was returning the key of the audit
table, not the Account table.
THANK YOU.
Ken
>--Original Message--
>Do you have a trigger on the table? What does
SCOPE_IDENTITY() give you?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ken" <KLomax@.Noteworld.com> wrote in message
>news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
>> @.@.Identity does not return the correct value.
>> Anyone have any ideas how to fix this,
>> and more important,
>> how does it get out of sync and return the wrong value?
>> Account.AccountID = (int IDENTITY, PRIMARY KEY )
>> /* Three statements executed together */
>> EXEC ('DBCC CheckIdent(Account)')
>> INSERT Account(AccountNumber, AccountAccountStatus,
>> AccountAgency, AccountAgencyClientID,
>> AccountPaymentAmount)
>> VALUES('9',2,1,'sadasd',787.8)
>> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
>> (AccountID) as "Max(AccountID)" from Account
>> /* Results of three statements executed together */
>> Checking identity information: current identity
>> value '261', current column value '261'.
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> (1 row(s) affected)
>> AccountID = @.@.IDENTITY Max(AccountID)
>> -- --
>> 543 262
>> (1 row(s) affected)
>
>.
>|||Adam:
You are the man!
I did not realize @.@.Identity was not scope specific.
SCOPE_IDENTITY() fixes the problem.
I have a trigger that inserts audit records on updates and
inserts, so @.@.Identity was returning the key of the audit
table, not the Account table.
THANK YOU.
Ken
>--Original Message--
>Do you have a trigger on the table, perhaps doing an
insert to another
>table?
>If so, that's why you'll see that problem... Use
SCOPE_IDENTITY() instead...
>
>"Ken" <KLomax@.Noteworld.com> wrote in message
>news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
>> @.@.Identity does not return the correct value.
>> Anyone have any ideas how to fix this,
>> and more important,
>> how does it get out of sync and return the wrong value?
>> Account.AccountID = (int IDENTITY, PRIMARY KEY )
>> /* Three statements executed together */
>> EXEC ('DBCC CheckIdent(Account)')
>> INSERT Account(AccountNumber, AccountAccountStatus,
>> AccountAgency, AccountAgencyClientID,
>> AccountPaymentAmount)
>> VALUES('9',2,1,'sadasd',787.8)
>> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
>> (AccountID) as "Max(AccountID)" from Account
>> /* Results of three statements executed together */
>> Checking identity information: current identity
>> value '261', current column value '261'.
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> (1 row(s) affected)
>> AccountID = @.@.IDENTITY Max(AccountID)
>> -- --
>> 543 262
>> (1 row(s) affected)
>
>.
>|||Wait, I thought Tibor was the man. Who exactly _is_ the man? : )
<anonymous@.discussions.microsoft.com> wrote in message
news:001b01c3aadb$85d144f0$a501280a@.phx.gbl...
> Adam:
> You are the man!
> I did not realize @.@.Identity was not scope specific.
> SCOPE_IDENTITY() fixes the problem.
> I have a trigger that inserts audit records on updates and
> inserts, so @.@.Identity was returning the key of the audit
> table, not the Account table.
> THANK YOU.
> Ken
> >--Original Message--
> >Do you have a trigger on the table, perhaps doing an
> insert to another
> >table?
> >
> >If so, that's why you'll see that problem... Use
> SCOPE_IDENTITY() instead...
> >
> >
> >"Ken" <KLomax@.Noteworld.com> wrote in message
> >news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> >> @.@.Identity does not return the correct value.
> >>
> >> Anyone have any ideas how to fix this,
> >> and more important,
> >> how does it get out of sync and return the wrong value?
> >>
> >> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> >>
> >> /* Three statements executed together */
> >> EXEC ('DBCC CheckIdent(Account)')
> >>
> >> INSERT Account(AccountNumber, AccountAccountStatus,
> >> AccountAgency, AccountAgencyClientID,
> >> AccountPaymentAmount)
> >> VALUES('9',2,1,'sadasd',787.8)
> >>
> >> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> >> (AccountID) as "Max(AccountID)" from Account
> >>
> >> /* Results of three statements executed together */
> >> Checking identity information: current identity
> >> value '261', current column value '261'.
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> (1 row(s) affected)
> >>
> >> AccountID = @.@.IDENTITY Max(AccountID)
> >> -- --
> >> 543 262
> >>
> >> (1 row(s) affected)
> >>
> >
> >
> >.
> >|||I think we're gonna have to take this outside.
"Eric Sabine" <mopar41@.hyottmail.com> wrote in message
news:3fb54413$0$43854$39cecf19@.news.twtelecom.net...
> Wait, I thought Tibor was the man. Who exactly _is_ the man? : )
>

@@IDENTITY and thread safety

I am currently using @.@.Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:
SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @.@.Identity from table",conn);
string identity=cmd.ExecuteScalar();

Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?
I think you should use scope_identity()|||I would search the online books on @.@.Identity. I'm sure the subject istouched upon. I seem to recall something about @.@.Identity applying tothe current connection or context but I forgot the details. In anycase, @.@.Identity is so widely used that if it wasn't thread safe we'dall be in serious trouble.
|||scope_identity()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp
@.@.Identity
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp
@.@.Identity is thread safe, but scope_identity() is the way to go.|||

Everyone,

Thanks for the help. JHouse, those articles are great. Just for the benefit of others reading this thread, the key difference between the @.@.IDENTITY and Scope_Identity() is the @.@.IDENTITY returns the last identity value of any table your batch or procedure inserts into - implicit or explicit - while Scope_Identity() is explicit only. So if you insert into tableA that has a trigger that inserts into tableB. @.@.Identity will return the identity from tableB, and Scope_Identity will return the identity from tableA.

@@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
--

@@Error not catching error.

Hi all,

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.

Say suppose i have statment like

select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'

here rowid is integer value so i am getting above mention error.

So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.

thanks in advance.(trialproduct2004@.yahoo.com) writes:

Quote:

Originally Posted by

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.


If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.

--
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|||(trialproduct2004@.yahoo.com) writes:

Quote:

Originally Posted by

I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.


If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.

--
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

@@error in vb6

Hi everyone!
How can I read the value of @.@.error from within vb6 source? Or how can I check the success of a transaction (comit/rollback) from within vb6 frontend source?
Thank you!I believe you will have to pass it back as an output parameter from your stored procedure. That's how I do it anyway.|||To capture @.@.error you can either store its value into an output parameter, or return it in the form of a result set.

To trap an execution error and notify your FE of a specific section of BE code that encountered the error use RAISERROR. In your VB code precede the call to SQL with ON ERROR (either RESUME NEXT and check for error right below, or GOTO <LABEL> and do the error trapping there).

EDITED: At the end make sure to turn error-fall-through OFF by issueing ON ERROR GOTO 0. Failure to do so often yields hours of debugging.|||ON ERROR GOTO 0. Failure to do so often yields hours of debugging.

Couldn't agree more. That's why I don't use it. Way too many sleepless nights trying to figure out why the stupid app isn't giving me the results I expect.

On Error Goto BeerFridge

Now that works :)

@@error for dynamic SQL

Does anyone know of a simple, correct way to retrieve the value of @.@.error from dynamic SQL? I can't use:SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

EXECUTE (@.cCmd)
SELECT @.@.error -- only shows locally reported error

EXECUTE sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported errorI can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?

-PatPI'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).

QUERY:
Set Concat_Null_Yields_Null Off

Declare @.cCmd nvarchar(4000)
Declare @.cServer nvarchar, @.cLogin nvarchar
, @.iError int

SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

Print @.cCmd + N' << The Command' + nchar(10)

EXECUTE (@.cCmd)
SELECT @.@.error As 'Error' -- only shows locally reported error

EXECUTE @.iError = sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported error

Select @.iError

RESULTS
EXECUTE @.i = [].master.dbo.sp_password NULL, 'junque', '' << The Command

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Error
----
1038

(1 row(s) affected)

Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

----
170

(1 row(s) affected)

What value do you expect for the error codes?|||I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.

When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @.@.error result.

At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.

-PatP|||Thanks - now I get it.

The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).

If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.

WRAPPER
create procedure my_sp_password
@.old sysname = NULL, -- the old (current) password
@.new sysname, -- the new password
@.loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @.self int, @.error int
select @.self = CASE WHEN @.loginame is null THEN 1 ELSE 2 END
, @.error = -1

-- RESOLVE LOGIN NAME
if @.loginame is null
select @.loginame = suser_sname()

-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @.self = 1
begin
dbcc auditevent (107, @.self, 0, @.loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
select @.error = 15210
return (@.error)
end
ELSE
begin
dbcc auditevent (107, @.self, 1, @.loginame, NULL, NULL, NULL)
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@.@.trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
select @.error = 15002
return (@.error)
end

-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @.loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@.loginame)
select @.error = 15007
return (@.error)
end

-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@.self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @.loginame and isntname = 0
AND sysadmin = 1) )
SELECT @.self = 1

-- CHECK OLD PASSWORD IF NEEDED --
if (@.self = 1 or @.old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @.loginame and
( (@.old is null and password is null) or
(pwdcompare(@.old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
select @.error = 15211
return (@.error)
end

-- CHANGE THE PASSWORD --
exec sp_configure 'allow', 1
Reconfigure
set @.error = @.@.error

if @.error = 0
begin
-- update master.dbo.sysxlogins
-- set password = convert(varbinary(256), pwdencrypt(@.new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
-- where name = @.loginame and srvid IS NULL
exec @.error = sp_password @.old, @.new, @.loginame
end

-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
if @.error = 0
begin
exec('use master grant all to null')
set @.error = @.@.error
end

-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @.error <> 0
return (@.error)
raiserror(15478,-1,-1)
set @.error = 15478
return (@.error) -- sp_password

EXECUTION
Declare @.error int
Select @.error = -1
exec @.error = my_sp_password 'fred', 'ted', 'sam'
Select @.error|||Or

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
GO

DECLARE @.cCmd varchar(8000), @.cServer sysname, @.cLogin sysname
SELECT @.cServer = @.@.SERVERNAME, @.cLogin = SYSTEM_USER

SELECT @.cCmd = 'DECLARE @.i int' + CHAR(13)
+ 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''test'', '''
+ @.cLogin + ''''+ CHAR(13)
+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
+ 'SELECT ' + CONVERT(varchar(15),@.@.SPID) + ', ''sp_password'''+', @.@.ROWCOUNT, @.@.ERROR, @.i' + CHAR(13)

SELECT @.cCmd

EXECUTE (@.cCmd)

SELECT * FROM ProcessLog o
WHERE Spid = @.@.SPID
AND Add_Dt IN ( SELECT MAX(Add_Dt)
FROM ProcessLog i
WHERE i.Spid = o.Spid)
GO

SET NOCOUNT OFF
DROP TABLE ProcessLog
GO|||Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.

-PatP|||How does your first one work?

Doesn't it return an error?

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.

?

I didn't test it remotely, are you saying it won't work?|||What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.

The first bit of logic works nicely when the code is being executed on the local server (in other words, when @.cServer = @.@.servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @.@.error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.

As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.

-PatP|||DECLARE @.j INT
CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
INSERT INTO #temp (result)
EXEC @.j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
SELECT @.j
IF @.j = -100
BEGIN
SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
FROM #temp
WHERE id = 1
END

DROP TABLE #temp

NOTE: I don't have a remote server to test it with at home.

Tim S|||I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:SET @.cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', '''
+ @.cLogin + ''' EXIT(SELECT @.@.error)" -s ' + @.cServer'

EXECUTE @.iRC = master.dbo.xp_cmdshell @.cCmdTo simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.

-PatP

Sunday, March 25, 2012

@@Connection

When I use TSQL Debugger it will open three windows... I wanna ask about the
second windows. it gave me output like that
Name (Globals) Value (Globals) Type
(Globals)
@.@.connections 38
int
@.@.transcount 0
int
I only trying to debug on stored procedure and it's giving me @.@.connection =
38.. can any one explain me...
Thanks
@.@.connections is the total number of connection attempts
since SQL Server last started. It's not the number of users
but rather the connections. You can find more information in
books online under: @.@.connections
-Sue
On Thu, 24 Mar 2005 13:50:06 -0500, "John"
<naissani@.hotmail.com> wrote:

>When I use TSQL Debugger it will open three windows... I wanna ask about the
>second windows. it gave me output like that
>
>Name (Globals) Value (Globals) Type
>(Globals)
>@.@.connections 38
>int
>@.@.transcount 0
>int
>
>I only trying to debug on stored procedure and it's giving me @.@.connection =
>38.. can any one explain me...
>Thanks
>
|||Thanks sue...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:74t641phrlih05ei5eqrkp1bnlg1u25g8h@.4ax.com... [vbcol=seagreen]
> @.@.connections is the total number of connection attempts
> since SQL Server last started. It's not the number of users
> but rather the connections. You can find more information in
> books online under: @.@.connections
> -Sue
> On Thu, 24 Mar 2005 13:50:06 -0500, "John"
> <naissani@.hotmail.com> wrote:
the[vbcol=seagreen]
@.@.connection =
>

Thursday, March 22, 2012

on parameters

Hi,
I have 3 parameters-
columnname -takes the column name like name, course, marks
operator - takes the operator - <.>.<= value - takes the value like 30,computers , english etc
i want to pass these parameter to a table and filter the value out.
how can i make the operator as a parameter ?
in the filter expression -
i can give in the column name - column parameter
value - value parameter
but how do i give the operator ?
Thanks
RPIlJQIiA8UlBAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGUgDQo+IEkgaGF2ZSAzIHBh
cmFtZXRlcnMtDQo+IGNvbHVtbm5hbWUgICAtdGFrZXMgdGhlIGNvbHVtbiBuYW1lIGxpa2UgbmFt
ZSwgY291cnNlLCBtYXJrcw0KPiBvcGVyYXRvciAtIHRha2VzIHRoZSBvcGVyYXRvciAtIDwuPi48
PQ0KPiB2YWx1ZSAtIHRha2VzIHRoZSB2YWx1ZSBsaWtlIDMwLGNvbXB1dGVycyAsIGVuZ2xpc2gg
ZXRjDQo+IA0KPiBpIHdhbnQgdG8gcGFzcyB0aGVzZSBwYXJhbWV0ZXIgdG8gYSB0YWJsZSBhbmQg
ZmlsdGVyIHRoZSB2YWx1ZSBvdXQuDQo+IGhvdyBjYW4gaSBtYWtlIHRoZSBvcGVyYXRvciBhcyBh
IHBhcmFtZXRlciA/DQo+IGluIHRoZSBmaWx0ZXIgZXhwcmVzc2lvbiAtDQo+IGkgY2FuIGdpdmUg
aW4gdGhlIGNvbHVtbiBuYW1lIC0gY29sdW1uIHBhcmFtZXRlcg0KPiB2YWx1ZSAtIHZhbHVlIHBh
cmFtZXRlcg0KPiBidXQgaG93IGRvIGkgZ2l2ZSB0aGUgb3BlcmF0b3IgPw0KDQp0cnkgdG8gd3Jp
dGUgeW91IHF1ZXJ5IGFzIHN0cmluZy4NCkluc3RlYWQgb2YgDQpTRUxFQ1QgKiBGUk9NIGR1bW15
IFdIRVJFIC4uLi4NCndyaXRlDQo9IlNFTEVDVCAqIEZST00gZHVtbXkgZCBXSEVSRSAiICtQYXJh
bWV0ZXJzIXBBZ2VudENvZGUuY29sdW1ubmFtZSAgKyBQYXJhbWV0ZXJzIXBBZ2VudENvZGUub3Bl
cmF0b3IgKyBQYXJhbWV0ZXJzIXBBZ2VudENvZGUudmFsdWUgDQo=|||b29wcw0KcXVlcnkgbXVzdCBsb29rIGxpa2UgdGhpczoNCg0KID0iU0VMRUNUICogRlJPTSBkdW1t
eSBkIFdIRVJFICIgK1BhcmFtZXRlcnMhY29sdW1ubmFtZS52YWx1ZSArIFBhcmFtZXRlcnMhb3Bl
cmF0b3IudmFsdWUgKyBQYXJhbWV0ZXJzIXZhbHVlLnZhbHVlIA0K

Tuesday, March 20, 2012

::FN_DBLOG - Retreiving [row data]

Subj: ::FN_DBLOG - Retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back.
The retrived record does contain row data. It can be seen when using DBCC LO
G.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and ::
FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Hi hillel,
I don't know exactly what you are looking for but launching this you can
recover the log info:
SELECT * FROM ::fn_dblog(null,null)
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"hillel" wrote:

> Subj: ::FN_DBLOG - Retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back.
> The retrived record does contain row data. It can be seen when using DBCC
LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and ::
> FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>|||Thank you Enric.
My question is very specific.
In SQL 2000 - there are 2 interfaces by which I can access the transaction
LOG:
DBCC LOG is not a very convinient API in many aspects.
::FN_DBLOG() is much more tempting but - surprisingly - it does not display
the [row data] columns - as seen below:
SELECT [Current LSN],[Transaction ID],[Object Name],[row data]
FROM ::fn_dblog(null,null) where operation='LOP_INSERT_ROWS'
0000000b:0000018b:0002 0000:000005bf dbo
.jobs (1977058079) 0x
0000000b:00000190:0002 0000:000005c1 dbo
.jobs (1977058079) 0x
0000000b:00000190:0003 0000:000005c1 dbo
.jobs (1977058079) 0x
There is data associated with these records - as seen below:
dbcc log(DB5,3,logrecs,'LOP_INSERT_ROWS')
0000000b:0000018b:0002 ...0000:000005bf... dbo.jobs (1977058079)...
0x3000080025004749040000010030004261636B
75705F313931393139313931393139313931
3931393139313931393139
Did I miss something in the ::FN_DBLOG() invocation - or- what...?
Thanks
Hillel.
"Enric" wrote:
> Hi hillel,
> I don't know exactly what you are looking for but launching this you can
> recover the log info:
> SELECT * FROM ::fn_dblog(null,null)
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "hillel" wrote:
>

Thursday, March 8, 2012

.Net passing bad formatted value to stored procedure

Hi, that's the problem:

I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.

When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:

exec sel_despesa_procura @.codigo=NULL,@.fornecedor=NULL,@.descricao=NULL,@.vencto_ini=''2005-10-10 00:00:00:000'',@.vencto_fim=''2005-10-20
00:00:00:000'',@.pagto_ini=NULL,@.pagto_fim=NULL,@.valor=NULL,@.valor_pago=NULL,
@.centro_custo=NULL,@.pago=N'0,1'

The fields are defined as follows:

<SelectParameters>
...
<asp:ControlParameterControlID="txtFiltroVencIni"Name="vencto_ini"PropertyName="Text"Type="DateTime"/>
<asp:ControlParameterControlID="txtFiltroVencFim"Name="vencto_fim"PropertyName="Text"Type="DateTime"/>
...
</SelectParameters>

The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '2005'.

I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.

Thanks in advance,

Anderson

That is not it's normal behavior. Just for laughs, set the pages culture and uiculture to "en-US", and run the page again and see if the problem disappears. If it does, then it's a bug with your particular culture settings.

Sunday, February 19, 2012

... not in ... what is the fastest way ?

Hi,
i have two tables, each has a columnn called "value". Now i want all
"values" from table_1 which does not exist in table_2. I tried:
select table_1.value from table_1 where not exists (select * from table_2
where table_2.value = table_1.value)
Is this the fastest way (in SQL-Server2000)?
thanks,
HelmutHi Helmut,
Yes.
Make sure there is an index on table2.value.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"helmut woess" <hw@.iis.at> wrote in message
news:1tm1fparsdfl.w6pfxn05crxk.dlg@.40tude.net...
> Hi,
> i have two tables, each has a columnn called "value". Now i want all
> "values" from table_1 which does not exist in table_2. I tried:
> select table_1.value from table_1 where not exists (select * from table_2
> where table_2.value = table_1.value)
> Is this the fastest way (in SQL-Server2000)?
> thanks,
> Helmut|||Your query just might be the fastest, but compare in Profiler with this one:
select value
from table_1
left join table_2
on table_2.value = table_1.value
where (table_2.value is null)
ML
http://milambda.blogspot.com/|||Am Tue, 3 Jan 2006 01:34:05 -0800 schrieb ML:

> Your query just might be the fastest, but compare in Profiler with this on
e:
> select value
> from table_1
> left join table_2
> on table_2.value = table_1.value
> where (table_2.value is null)
>
> ML
> --
> http://milambda.blogspot.com/
Thanks, i checked it with the profiler and it seems to be nearly indentical
(i have not so much records now). But your query needs some rows more and
has an additional filter, so if the table has millions of records maybe my
way is faster.
bye,
helmut|||Am Tue, 3 Jan 2006 09:36:12 -0000 schrieb Tony Rogerson:

> Hi Helmut,
> Yes.
> Make sure there is an index on table2.value.
I have an index on it - thanks,
Helmut|||Please, let us know when you test it on millions of rows. We'd like to know.
:)
ML
http://milambda.blogspot.com/

*Help* Function to convert a column to a scalar value - how?

Hi,
I'm trying to consolidate a column from the 'many' side of a relationship
into a single NVARCHAR scalar value so that it can be displayed in a client
application (no editing is required).
I figured that I would be best using a user function called from within the
originating stored procedure, passing the record ID value to the function
which is then used as the foreign key within a SELECT statement on the
'many' table.
My confusion is around the looping structure within the function and
persisting a current value to append to the NVARCHAR value, which can then
be passed out to the calling stored proc.
The function currently looks like this (but doesn't work):
ALTER FUNCTION dbo.fnCofAsStringForContactID
(
@.ContactID AS INTEGER
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @.TempString AS NVARCHAR(500)
DECLARE @.FullLocation AS NVARCHAR(100)
SELECT @.FullLocation = CompanyLocations.LocationName
FROM ContactCofAJoin
INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
CompanyLocations.LocationID
WHERE (ContactCofAJoin.fContactID = @.ContactID)
WHILE @.FullLocation IS NOT NULL
BEGIN
SET @.TempString = @.TempString + @.FullLocation + ', '
END
RETURN @.TempString
END
Am I correct in thinking that the WHILE loop will iterate through the SELECT
records and extract the data that I need?
Cheers,
Andyhttp://www.aspfaq.com/show.asp?id=2529
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>|||You don't actually need a loop. Try this:
http://milambda.blogspot.com/2005/0...s-as-array.html
Adjust to your specific needs.
ML|||Many thanks for the tips guys!
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>

Thursday, February 16, 2012

**HAVE NULL AND NOT DUBLICATED VALUES IN COL**

Hi
I'm using SQL 2000,how is it possible to control a value of column to get
the ollowing result?
Emp (EmpId PK,EmpName,EmpCode)
EmpId EmpName EmpCode
-- -- --
100 John 3
101 Anne 5
102 Robert null
103 Jennifer null
104 Peter 78
I want to have some NULLs or a unique number in EmpCode column as above,
I tried to define Uniqe index or constraint but it wasn't successfull
because of null,
now how can I prevent of inserting a record of (105,'Tom',3) and not prevent
of inserting a record like (105,'Tom',null)
Any help would be greatly thankful.Here are three possible methods (in each case, Colx is the nullable,
unique column):
1. Use an indexed view:
CREATE VIEW SomeTable_Unique_Non_NULL
WITH SCHEMABINDING
AS
SELECT colx
FROM dbo.SomeTable
WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
(colx)
2. Use an index with a calculated column:
CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
foo
AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
3. Use a trigger:
CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
ON SomeTable FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM Inserted AS I
JOIN SomeTable AS S
ON I.colx = S.colx AND I.keycol<>S.keycol)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate values not permitted',16,1)
END
David Portas
SQL Server MVP
--|||David
I think violation of UNIQUE KEY constraint will terminate the batch , i mean
we will not see the error generated by the trigger.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108554393.210913.172600@.z14g2000cwz.googlegroups.com...
> Here are three possible methods (in each case, Colx is the nullable,
> unique column):
> 1. Use an indexed view:
> CREATE VIEW SomeTable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS
> SELECT colx
> FROM dbo.SomeTable
> WHERE colx IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
> (colx)
> 2. Use an index with a calculated column:
> CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
> foo
> AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
> 3. Use a trigger:
> CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
> --
> David Portas
> SQL Server MVP
> --
>|||I tried sth like below in the past ,but I think it can be dangrous in
sometimes
for example if we have a begin transaction in our statement and the this
trigger
fires and duplicated value was inserted then it will rollback to the poit
which made the other activities which are done before this trigger to be
rollbacked too!!!!!!!
what's your openion?
CREATE TRIGGER trg_SomeTable_Unique_Non_NULLred">
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
>
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108554393.210913.172600@.z14g2000cwz.googlegroups.com...
> Here are three possible methods (in each case, Colx is the nullable,
> unique column):
> 1. Use an indexed view:
> CREATE VIEW SomeTable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS
> SELECT colx
> FROM dbo.SomeTable
> WHERE colx IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
> (colx)
> 2. Use an index with a calculated column:
> CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
> foo
> AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
> 3. Use a trigger:
> CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
> --
> David Portas
> SQL Server MVP
> --
>|||On Thu, 17 Feb 2005 08:47:56 +0330, maryam rezvani wrote:

>I tried sth like below in the past ,but I think it can be dangrous in
>sometimes
>for example if we have a begin transaction in our statement and the this
>trigger
>fires and duplicated value was inserted then it will rollback to the poit
>which made the other activities which are done before this trigger to be
>rollbacked too!!!!!!!
>what's your openion?
Hi Maryam,
Transactions are used to encapsulate a number of statements that should
either be executed as a whole, or none at all.
So if I have several statements and one of them is an insert that fails as
a result of duplicate data, I would expect and want SQL Server to undo all
changes made since the BEGIN TRANSACTION.
If you want to rollback only the insert but none of the preceding
activities, then simply commit the transaction for these other activities
first, before executing the insert.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,now imagine we don't define any begin trans before and we fire this
trigger and it will fail because of duplicated rows and rollback happen
too,but to which poit ?just to prevet inserting duplicated rows or......?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:13o811tiqus3gpgt2glj5tn74sp16utraa@.
4ax.com...
> On Thu, 17 Feb 2005 08:47:56 +0330, maryam rezvani wrote:
>
> Hi Maryam,
> Transactions are used to encapsulate a number of statements that should
> either be executed as a whole, or none at all.
> So if I have several statements and one of them is an insert that fails as
> a result of duplicate data, I would expect and want SQL Server to undo all
> changes made since the BEGIN TRANSACTION.
> If you want to rollback only the insert but none of the preceding
> activities, then simply commit the transaction for these other activities
> first, before executing the insert.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||In that case only the INSERT operation itself will be rolled back.
--
David Portas
SQL Server MVP
--|||Yes, if you define colx as UNIQUE then the trigger will not fire. The
trigger is something you can use instead of a unique constraint.
David Portas
SQL Server MVP
--

Saturday, February 11, 2012

(Urgent) How Get a Text Field and put the result into a text Var

Hi All

Iam trying to Get a text field value i wrote this code

DECLARE @.ptrval varbinary(16)
DECLARE @.length bigint
SELECT @.ptrval = TEXTPTR(Template), @.length = LEN(Template)
FROM #TEMPLATE
READTEXT Template.#TEMPLATE @.ptrval 0 @.length

but i need to put the result into a text var
is that possible or not and if it possible any one could help me with thatYou could return a text column as part of a result set. Are you trying to get the text back to ASP.NET?

(Target Server Memory)Perf. Monitor Counters

Why does'nt my Target Server Memory value (2.7 GB) within
Memory Manager in the Performance Monitor Counters match
the values in SQL Server Memory Maximum Value (5.2 GB)
My Target Server Memory displays a value of 2.7 GB and the
SQL Memory Maximum displays 5.2 GB.
The boot.ini is set with the following parameters:
[boot loader]
timeout=10
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microso ft
Windows 2000 Advanced Server" /fastdetect /3gb /PAE
The server initially had 6GB of RAM and 2GB were added to
the server. SQL Server displays 5.2 GB is the maximum alot
memory and Dynamically configure SQL Server memory.
How can I fix the memory problem.
Thanks,
Mike
Do you have AWE enabled in SQL?
Are you rinning Enterprise Edition SQL?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:8ea801c432f6$f47ddf50$a501280a@.phx.gbl...
> Why does'nt my Target Server Memory value (2.7 GB) within
> Memory Manager in the Performance Monitor Counters match
> the values in SQL Server Memory Maximum Value (5.2 GB)
> My Target Server Memory displays a value of 2.7 GB and the
> SQL Memory Maximum displays 5.2 GB.
> The boot.ini is set with the following parameters:
> [boot loader]
> timeout=10
> default=multi(0)disk(0)rdisk(0)partition(1)\WINNT
> [operating systems]
> multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microso ft
> Windows 2000 Advanced Server" /fastdetect /3gb /PAE
> The server initially had 6GB of RAM and 2GB were added to
> the server. SQL Server displays 5.2 GB is the maximum alot
> memory and Dynamically configure SQL Server memory.
> How can I fix the memory problem.
> Thanks,
> Mike
>