@@Identity in Stored procedure

Hi,

I have a stored procedure that insert data into 2 temp tables. The problem that I have is when I insert a data into a first table, how do I get the @.@.identity value from that table and insert it into the second table?? The following code is what I have:

Create #Temp1
@.StateID Identity,
@.State nvarchar(2),
@.wage money

INSERT INTO #Temp1 (State, wage)

SELECT State, Wage FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table2_ID

Create #Temp2
@.ID Identity
@.EmployeeID int
@.StateID Int
@.Field1 Money
@.Field2 Money

INSERT INTO #Temp2 (EmployeeID, StateID, Field1, Field2)

SELECT EmployeeID, StateID, Field1, Field2 FROM SomeTable

So, The first part I created a #Temp1 table and insert data into the table. Then after the insert, I want the @.@.Identity value and insert into the #Temp2 table. This is my first time doing stored procedure, so, I am wondering how do I retrieve the @.@.identity value and put into the second select statement. Please help.

ahTan

DECLARE @.NewID INT

SET @.NewID = @.@.IDENTITY

OR

DECLARE @.NewID INT

SELECT @.NewID = @.@.IDENTITY

|||

instead of @.@.IDENTITY use SCOPE_IDENTITY() like

declare @._ID int

Select @._ID = SCOPE_IDENTITY()

to know why to use it, please search for SCOPE_IDENTITY in SQL SERVER books online.

thanks,

satish.

|||

SCOPE_IDENTITY() is not necessary here since the table is being created in the same stored proc. There are no triggers that would be run or no side effects to doing an insert into the table, therefore @.@.IDENTITY will work fine.

SCOPE_IDENTITY() is useful when you may have a trigger that inserts into table2 when you insert into table1, in that case getting the value used in the IDENTITY column should be retrieved with SCOPE_IDENTITY() since @.@.IDENTITY will get you value from table2 and not table1. However you don't have to worry about that here.

Regards,

Tim

|||

Thank you guys for the answer. Now, the next problem is, how do I insert the @.@.Identity value into the #TempPayroll table?

|||

ahTan:

how do I insert the @.@.Identity value into the #TempPayroll table

just save the @.@.scope_identity from the first insert into another variable and use it as input to the 2nd insert

insert into table1....
select @.Identity1 = @.@.SCOPE_IDENTITY
insert into table2 (...) values(..., @.Identity1,...)

|||

Thank you all of you for the responses. I have managed to get the stored procedure to work when I tested it using the SQL analyzer. I am getting close to what I want. Ok, the stored procedure will be called using a sqldatasource. The following code call the stored procedure:

1For Each rowAs GridViewRowIn GridView1.Rows2 SqlDataSource2.InsertCommand ="_payroll"3 SqlDataSource2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure4 SqlDataSource2.InsertParameters.Add("pniID", 410)5 SqlDataSource2.Insert()6 SqlDataSource2.InsertParameters.Clear()7Next8 GridView1.Visible =False9 SqlDataSource2.SelectCommand ="_payroll"10 SqlDataSource2.SelectCommandType = SqlDataSourceCommandType.StoredProcedure11''SqlDataSource2.SelectParameters.Add("pniID", CInt(row.Cells(0).Text.Trim))12 GridView2.DataSourceID ="SqlDataSource2"13 GridView2.DataBind()

After the for each loop, I want to display all the data in a gridview.  The following code is the sql code in the stored procedure:

 
1CREATE PROCEDURE [dbo].[_payroll]2(3@.pniIDint4)56AS78SET NOCOUNT ON910CREATE TABLE #TempStates11(12StateIDInt IDENTITY,13Statenvarchar(2),14Wagemoney15)1617Create Table #TempPayrolls18(19TempPayrollIDint IDENTITY,20PNI_IDint,21EmployeeIDint,22StateIDint,23Deptnvarchar(50),24PerDiemmoney,25Mileagemoney,26Phonemoney,27Computermoney,28Cameramoney29)3031INSERT INTO #TempStates (State, Wage)32SELECT PaySheets.StateOfProject,33 PNI_Payroll.Amount * PNI_Payroll.QuantityAS Amount34FROM PNI_Payroll35INNERJOIN PNION PNI_Payroll.PNI_ID = PNI.PNI_ID36INNERJOIN TimeSheetsON PNI.TimeSheetID = TimeSheets.TimeSheetID37INNERJOIN PaySheetsON TimeSheets.PaysheetID = PaySheets.PaysheetID38WHERE (PNI.PNI_ID = @.pniID)AND (PNI_Payroll.DescriptionLIKE'%Salary%')3940DECLARE @.NewStateIDint41SELECT @.NewStateID =@.@.IDENTITY4243--SELECT * FROM #TempStates4445INSERT INTO #TempPayrolls (PNI_ID, EmployeeID, StateID)46SELECT PNI.PNI_ID, InspectorID, StateID = @.NewStateID47FROM PNI48WHERE PNI.PNI_ID = @.pniID4950INSERT INTO #TempPayrolls (PNI_ID, EmployeeID, Dept, PerDiem, Mileage, Phone, Computer, Camera)51SELECT DISTINCT PNI.PNI_ID,52 PNI.InspectorID,53 StateOfProject +' Inspector'AS Dept,54 (SELECT (Quantity * Amount)As PerDiemAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Per Diem (YES OR NO)')AS PerDiem,55 (SELECT (Quantity * Amount)As MileageAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Mileage')AS Mileage,56 (SELECT (Quantity * Amount)As PhoneAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Cell Phone (YES OR NO)')AS Phone,57 (SELECT (Quantity * Amount)As ComputerAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Computer')AS Computer,58 (SELECT (Quantity * Amount)As DigitalCamAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Camera')AS Camera59FROM PNI_PayRoll60INNERJOIN PNION PNI_PayRoll.PNI_ID = PNI.PNI_ID61INNERJOIN TimeSheetsON PNI.TimeSheetID = TimeSheets.TimeSheetID62INNERJOIN PaySheetsON TimeSheets.PaysheetID = PaySheets.PaysheetID63WHERE PNI.PNI_ID = @.pniID6465--Display data from the temp tables66SELECT TempPayrollID, PNI_ID, EmployeeID,67(SELECT StateFROM #TempStatesWHERE #TempStates.StateID = #TempPayrolls.StateID)AS State,68(SELECT WageFROM #TempStatesWHERE #TempStates.StateID = #TempPayrolls.StateID)AS Wage,69PerDiem, Mileage, Phone, Computer, Camera70FROM #TempPayrolls717273-- Turn NOCOUNT back OFF7475SET NOCOUNT OFF76GO77
  So, I am wondering how am I going to display all the data in the temp tables in the gridview2. Please help

|||

Satish is correct, use SCOPE_IDENTITY(). Although fevir's explaination is correct on why @.@.IDENTITY might work, it's misusing the command, and the wrong command to use.

Just because there are no triggers TODAY, doesn't mean that there won't ever be.

|||

Motley, what about the whole YAGNI thought? Today, as it sit, the temp table it being created directly above and so no triggers exits. If you eventually did put a trigger on a temp table (not sure why you'd do this) but you'd adjust at that time.

Ultimately you're correct in that either can be used, but I'm curious about your thoughts in regard the YAGNI argument.

Regards,

Tim

|||

YAGNI would only apply if there was an easy way and more difficult approach. Since we are talking about using:

SELECT @.NewStateID =@.@.IDENTITY

or

SELECT @.NewStateID =SCOPE_IDENTITY()

I see no point in using a function that really should be reserved for someone who is very well versed in T-SQL. It's rare that @.@.IDENTITY is the correct function to use when you need an identity value. Again, in this case, we want the identity value that was just inserted by the insert just above us. The function for that is SCOPE_IDENTITY. If we wanted to know the last identity value that the system generated in the scope of our connection, that would be @.@.IDENTITY. I have yet to EVER need @.@.IDENTITY in any project I've ever done. And while using @.@.IDENTITY can cause maintenaince problems, and unexpected results, the same is not true of SCOPE_IDENTITY. It will always return the result that we are expecting whether we have a trigger on the table today, tomorrow, or never.

I say this, because I can't count the number of times that people have gotten bitten because they had to add auditting tables to track changes to a table (or replication), only to find that the application is now creating bogus data because someone decided to use @.@.IDENTITY when they should have used SCOPE_IDENTITY().

@@IDENTITY in code

Hi all,

I encountered a problem with SQL server mobile.

I am inserting a row inside a database table through C# code and need to obtain the ID of the last inserted row if successful. I have the following in my code:

SqlCeHelper.ExecuteNonQuery(connectionString, "*INSERT STATEMENT*");

Int64 id = (Int64)SqlCeHelper.ExecuteScalar(connectionString, "SELECT @.@.IDENTITY");

The variable id always ends up NULL. Is this the correct approach to this? Is there anything else I can use to obtain the id of the last inserted row in SQL server mobile?

-- The class SqlCeHelper is a wrapper that I wrote, it works 100%.

Thanks

you should be using Identity_Scope() - its better. I havent worked with SqlCE but this is what I use, and is best used than @.@.Identity, and no idea if this works for SqlCe

My apologies if it does not

I also believe, perhaps I am wrong again, that you are executing 2 different queries, hence why you are receiving null for your last query - again, I may well be totally wrong here. It's a learning curve for me also

|||

Hi,

No...unfortunately SQL mobile doesn't support the function.

I though of the two separate statements as well. Is it possible to run two different SQL statements inside one command?

Thanks

|||

OK,

Fixed the problem. It really was executing these two statements totally separately.

The correct way to fix this is to add a couple lines inside my SQLCeHelper...

I now have something like this inside the SQLCeHelper:

publicstaticobject ExecuteInsert(string connectionString, string command, paramsSqlCeParameter[] commandParameters){

object retval = null;

try{

SqlCeConnection cn = newSqlCeConnection(connectionString);

SqlCeCommand c = newSqlCeCommand(command, cn);

foreach (SqlCeParameter p in commandParameters)

c.Parameters.Add(p);

cn.Open();

c.ExecuteNonQuery();

c = newSqlCeCommand("SELECT @.@.IDENTITY AS [IDENTITY]", cn);

retval = c.ExecuteScalar();

}

catch

{

throw;

}

return retval;

}

sql

@@Identity being over-written by Insert Trigger in stored procedure.

Hi All

I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

I have been using the @.@.Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an after insert Trigger has been added to the table being updated.

Now the @.@.Identity is returning the identity value of the trigger that was called instead of the original table insert.

Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

Thank in advance.

Eamon.Look at SCOPE_IDENTITY and/or IDENT_CURRENT

Regards,

hmscott|||Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!

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

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 have a problem hopefully someone can help with...
I have a stored procedure which ...
Writes to table A (table A has an identity column)
Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
Updates a table; setting column X to the IDENTITY value just retrieved
Table A has a couple of triggers on it, one of which writes to a different
table - and that table has an identity column.
Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
identiy column for the table I wrote to (TABLE A), it returns the identity
value of the insert inside the triggers.
Any suggestions on how I get the correct value after writing to table A?
Hope this makes some sense!
Thanks> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
There is a pretty good explanation in the BOL about how it works
If you insert a value into TableA by using @.@.scope_identity() function you
will get the last values of the identity column of the TableA
"..." <...@.nowhere.com> wrote in message
news:emnME2LAGHA.3496@.TK2MSFTNGP11.phx.gbl...
>I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>|||.... wrote:

> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
If you reference SCOPE_IDENTITY() in the proc then it will return the
last IDENTITY value inserted in the current scope (not in the trigger).
If you reference @.@.IDENTITY it will return the IDENTITY value from ANY
scope (including the trigger).
To reference the TableA IDENTITY value in the trigger itself don't use
either of those functions. Refer to the INSERTED virtual table instead.
Your trigger code should accommodate the fact that there may be more
than one row inserted.
There is no such function as @.@.SCOPE_IDENTITY.
David Portas
SQL Server MVP
--|||SCOPE_IDENTITY should work for you
If you want the latest identity regardless of scope and session then
take a look at IDENT_CURRENT
IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
You would use it like this: SELECT IDENT_CURRENT('TableA') --TableA
would be your table name
http://sqlservercode.blogspot.com/|||not sure what @.@.scope_identity is -- SCOPE_IDENTITY() is the correct
function.
@.@.IDENTITY should have the last inserted identity value (the one
inserted into from the trigger)
SCOPE_IDENTITY() has last inserted identity from your session, so when
you check it, it should be as soon after the related insert as possible.
If this still doesn't help, could you post the proc and trigger code?
This will help posters give better answers.
... wrote:
> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>

@@ERROR Handling

To All Gurus:
I am trying to log some information to another table when @.@.Error returns an
error. Below is the sample code to see how @.@.error works. In this sample
code, I am purposely failing INSERT INTO TEST command and want to log
informaiton in TestLog. Even though there is a error but the insert to
TestLog never works. What is the catch here ?
/* Create Sample Tables */
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
/*Fail the Insert statement */
INSERT INTO TEST Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TESTLOG Values (10, 20)
END
/* No Records are found*/
Select * from testlog
If I run this code in pieces one by one, the code works. When I highlight
the entire code and run it, it never makes it to TestLog table.
Thanks in advance.Sorry, the second Insert statment was
INSERT INTO TestLog Values (10)
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||Mark,
See Erland's articles:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:87927DFA-D785-42B2-BF22-972996A8F852@.microsoft.com...
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
> an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed.
Any comments on that one ?
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a
multi-batch technique inside of an object (e.g. stored procedure).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Mark,
I think it has to do with the type of error you're creating. If you add a
CHECK constraint to Test and perform your insert and it violates the insert
then the value IS inserted into the TestLog table.
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Thanks to both Jerry and Aaron. You are right, it has to do with what kind o
f
insert error it is. It either rollsback the batch or logs the info based on
the type of error. I created two scenarions and in one case it works and in
other one, it doesnt.
Once again thanks to both
"Jerry Spivey" wrote:

> Mark,
> I think it has to do with the type of error you're creating. If you add a
> CHECK constraint to Test and perform your insert and it violates the inser
t
> then the value IS inserted into the TestLog table.
> HTH
> Jerry
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>
>|||Mark (Mark@.discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what
> kind of insert error it is. It either rollsback the batch or logs the
> info based on the type of error. I created two scenarions and in one
> case it works and in other one, it doesnt.
As noted in my article, an error can lead to termination on three
different levels:
1) Statement
2) Scope
3) Bacth.
Your error was of the second kind. Scope-aborting errors are, as far as
I know, always compilation errors. What is tricky, is that due to
deferred named resolution, compilation errors can happen at run-time.
Consider:
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
--go
print 'Hello!'
/*Fail the Insert statement */
INSERT INTO Test Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TestLog Values (10)
END
go
SELECT * FROM TestLog
This script will print Hello!, but if you uncomment the go, it will not.
This is because in the script as it stands, Test does not exist, so SQL
Server defers compilation of that statement. If you uncomment the go,
the tables exists when the batch is compiled, and thus you get the error
directly, so execution never starts.
Finally, note that if you write:
INSERT INTO Test(ID) Values(10, 20)
the batch always fails to compile, as SQL Server does not need to know
the table definition. Incidently, most people agree that INSERT without
a column list is not good practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

@@ERROR & CREATE TABLE

Hi

I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:

DECLARE @.ERRCODE INT

CREATE TABLE x
SET @.ERRCODE = @.@.ERROR

This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.

Is there a way that I can capture the error using @.@.ERROR and still let the script run ?

Thanks in advance

MicksterWell, you shouldn't. "On Error Resume Next" is not present in T-SQL. What you should do is perform a validation for presence of object before attempting to create it.if objectproperty(object_id('dbo.x'), 'isusertable')=1 drop table dbo.x

create table dbo.x (f1 int, ...)|||Thanks, but I already understand that I should check for the existance of the object - as stated in my last mail. I want to check the table is being created correctly incase of other events, like permissions, file full, etc.

@@ERROR

Hi Folks,
I try to save the error number when a restore fails. In case a device error
I'd like to save the error number into a table. Let's say I have a RESTORE
as below
RESTORE LOG xxxxx
FROM DISK='xxxxxxxxxxx.bak'
PRINT @.@.ERROR
Server: Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'xxxxxxxxx'. Device error or device off-line. See
the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
If a device errors I want to get the error number 3201, which is more
specific about the cause of failure. However @.@.ERROR is always 3013 which
doesn't help much. How do I get it right as expected?
Thank you.Hmm, this may be tricky.
@.@.ERROR, as I understand it, always has the last immediate error that
occured. So, if you're printing @.@.error after the backup fails, it's always
going to be the last error, which is 3013 in this case.
I don't really see a way to just grab the error directly, other than logging
it to an output file in the job or writing to the event log. It seems to me
the event log is a more approprate place to put the entire message. You
could then import the event log into a table and do a search on that
specific error number.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:ePQlImsUDHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I try to save the error number when a restore fails. In case a device
error
> I'd like to save the error number into a table. Let's say I have a
RESTORE
> as below
> RESTORE LOG xxxxx
> FROM DISK='xxxxxxxxxxx.bak'
> PRINT @.@.ERROR
>
> Server: Msg 3201, Level 16, State 2, Line 3
> Cannot open backup device 'xxxxxxxxx'. Device error or device off-line.
See
> the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> If a device errors I want to get the error number 3201, which is more
> specific about the cause of failure. However @.@.ERROR is always 3013 which
> doesn't help much. How do I get it right as expected?
> Thank you.
>

@@DBTS correct usage

Hi,

Suppose my Test table has a timestamp (rowversion) column, and I have the following code:

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.

If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?

Thanks.

Can I be sure the TS return column value contains the value of the timestamp column of the Test table?

If the BOL said "@.@.DBTS Returns the value of the current timestamp data type for the current database", then you couldn't.

Anyway, there are some other ways to find the records have been inserted.

|||

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

You are correct, this will not work in a lot of cases since the timestamp (better to start using the rowversion synonym too) value is shared amongst all tables. It would require a database lock to prevent this from possibly failing

Doing a SELECT is the best way, and will almost always be really fast since:

1. You will likely have the primary key value
2. The row will be in cache since you just created it.

@ local variable makes query slower?

Hi all,

I have a query that scans huge table consists of 8 or more millions
records. The funny thing is that if I use the query with local
variable, the query takes more than 1 minutes, whereas if I hard code
the value into the query, it takes about 1 second. Here are the
queries:

WITH VARIABLE:
------

DECLARE @.i_StartDate DATETIME
DECLARE @.i_EndDate DATETIME
SET @.i_StartDate = '2004-04-26'
SET @.i_EndDate = '2004-04-28'

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)

...And some other conditions

-------

WITHOUT VARIABLE:

SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')

...And some other conditions

-------

The later one runs significantly faster than the first one. I've
isolated the problem at the local variable @.i_StartDate and
@.i_EndDate. Can somebody help me out, Please...

Thank you,
Michelle."Michelle" <michelletran@.harmonyremote.com> wrote in message
news:56c5b7ab.0404260656.281cfc40@.posting.google.c om...
> Hi all,
> I have a query that scans huge table consists of 8 or more millions
> records. The funny thing is that if I use the query with local
> variable, the query takes more than 1 minutes, whereas if I hard code
> the value into the query, it takes about 1 second. Here are the
> queries:
> WITH VARIABLE:
> ------
> DECLARE @.i_StartDate DATETIME
> DECLARE @.i_EndDate DATETIME
> SET @.i_StartDate = '2004-04-26'
> SET @.i_EndDate = '2004-04-28'
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= @.i_StartDate AND A.[Date] <= @.i_EndDate)
> ...And some other conditions
> -------
> WITHOUT VARIABLE:
>
> SELECT DISTINCT A.EventId, A.[Date], A.UserId, C.[Name] AS
> EventTypeName, D.[Name] AS EventSubTypeName, A.[Text], A.Data
> FROM TableEvent A, TableCSRep B, TableEventType C, TableEventSubType D
> WHERE (A.[Date] >= '2004-04-26' AND A.[Date] <= '2004-04-28')
> ...And some other conditions
> -------
> The later one runs significantly faster than the first one. I've
> isolated the problem at the local variable @.i_StartDate and
> @.i_EndDate. Can somebody help me out, Please...
> Thank you,
> Michelle.

This may be an example of parameter sniffing - see this post, for example,
which describes an almost identical case:

http://groups.google.com/groups?hl=...ftngp13.phx.gbl

Simon|||Thanks Simon.
Michelle.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

onflit_MyPubName_MyArticle ; MSmerge_genhistory

Hi,
1 ) is there a way to drop the conlict table ? (named
?onflit_Pubname_ArticleName)
where ? can a;b;c etc...
Why aren't they erased when the conflict is solved ?
2) Why do i have to execute this
delete
from MSmerge_genhistory
where generation not in (select generation from msmerge_contents)
and generation not in (select generation from msmerge_tombstone)
dbcc dbreindex ('msmerge_genhistory')
Why SQL does not do it itself ?
SQL 2K SP2
Thanks
1) use do a drop aconflict_... however, I would not do this as you might
inadvertently drop a table which is in use.
2) Replication metadata is purged by default every two weeks. This should
take care of your first statement. If msmerge_genhistory does get too
fragmented you might want to reindex it. I can't comment on why Microsoft
decided not to do this automatically, but I am sure they had a good reason.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pierre Marie" <PierreMarie@.discussions.microsoft.com> wrote in message
news:DF63D725-F345-4FA2-9F4A-C70D9D9ED3ED@.microsoft.com...
> Hi,
> 1 ) is there a way to drop the conlict table ? (named
> ?onflit_Pubname_ArticleName)
> where ? can a;b;c etc...
> Why aren't they erased when the conflict is solved ?
>
> 2) Why do i have to execute this
> delete
> from MSmerge_genhistory
> where generation not in (select generation from msmerge_contents)
> and generation not in (select generation from msmerge_tombstone)
> dbcc dbreindex ('msmerge_genhistory')
> Why SQL does not do it itself ?
> SQL 2K SP2
>
> Thanks
>

Moving table with data to another database in sql Express 2005?

How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.

i did by scripting but i transfer tables and procedures and not data

data is the problem.

tnx

I'm already discussing this with you here

http://forums.asp.net/thread/1299924.aspx

sql

HOW: Auotincrement without using autoincrement

I have a master table which I have been updating in multiple queries to
create a new row. It has a primary key, call it WorkID. I can't make it
autoincrement. And it is more complicated because...
WorkID can also appear in two other tables, as references to the master
record, but I need to provide for the master record being gone, and as
such the records in the other two tables are orphaned. That is fine.
To create a new master record I select MAX(WorkID) unioned on all 3
tables, and increment the result, and this is my new WorkID. However,
even if in a transaction, this query work can result in duplicate WorkID
numbers when new masters are created in extremely rapid succession. I
see the flaw here. Don't think this approach can be salvaged when the
MAX and INSERT queries are done separately.
I have been racking my brain to find a way to deal with this WITHOUT
resorting to auto-increment on the master table. I can think of no
expression for an INSERT value which will take the MAX+1 of WorkID on
the three tables, but it seems there must be a way to do this in a
single statement. Something like:
INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
Maybe I need a new single row, single column table that arbitrates new
WorkID values? Or maybe someone has a favorite trick?
Thx - Lee>> I have a master table .. <<
I have not heard that term since I moved from navigational DBMS to
RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
are not records; fields are not columns; tables are not files. The
differences are VITAL!!!
Of course not! An exposed physical locator created inside the hardware
cannot be a relational key by definition.
Well, you need a new brain :) What is the natrual, relational key in
the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
assume that you are not soooooo ignorant that you want to have a
"magical one-size-fits-all" auto numbering.|||Wow.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123641244.396767.117310@.g14g2000cwa.googlegroups.com...
> I have not heard that term since I moved from navigational DBMS to
> RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
> are not records; fields are not columns; tables are not files. The
> differences are VITAL!!!
>
> Of course not! An exposed physical locator created inside the hardware
> cannot be a relational key by definition.
>
> Well, you need a new brain :) What is the natrual, relational key in
> the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
> assume that you are not soooooo ignorant that you want to have a
> "magical one-size-fits-all" auto numbering.
>|||On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:

> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on
> the three tables, but it seems there must be a way to do this in a
> single statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - Lee
As Celko said, you could probably do well with a full redesign that uses a
natural key. Failing that, how about this:
INSERT WORK (WorkID)
SELECT 1+MAX(WorkID)
FROM (
SELECT MAX(WorkID) WorkID FROM WORK
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable1
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable2
)
A better way might be to keep your available unused workIDs in yet another
table. First gather all used WorkIDs in the table:
CREATE TABLE WorkIDs (WorkID int, Used bit)
INSERT INTO WorkIDs (WorkID, Used)
SELECT WorkID, 1 FROM WORK
UNION
SELECT WorkID, 1 FROM OtherTable1
UNION
SELECT WorkID, 1 FROM OtherTable2
Now add some new ones for future use:
DECLARE @.maxworkID int
DECLARE @.x int
SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
SET @.x = @.maxworkID
WHILE @.x < @.maxworkID + 10000
BEGIN
INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
SET @.x = @.x + 1
END
Now here's your procedure for creating a new workID:
BEGIN TRANSACTION
DECLARE @.newWorkID int
SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
COMMIT TRANSACTION|||> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION
Actually this is the worst case scenario for a busy system unless you use
exclusive locks. Say two users start the select at the same time. Boom,
deadlock. They both have a shared read lock on the workId's, the both
insert into work and then the UPDATE tries to WorkIDs to the same value
requiring exclusive locks. (unless the Unique Key fails them in the Work
table.)

> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
This would have to single thread over ALL tables! The SELECT by default
would only take shared locks, so any two users could be getting the max
values at the same time.
This gets even worse if this transaction gets called in another transaction.
There is not a good way to do this without single threading.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:85kyr6seugm6$.dlg@.rosspresser.dyndns.org...
> On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:
>
> As Celko said, you could probably do well with a full redesign that uses a
> natural key. Failing that, how about this:
> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
> A better way might be to keep your available unused workIDs in yet another
> table. First gather all used WorkIDs in the table:
> CREATE TABLE WorkIDs (WorkID int, Used bit)
> INSERT INTO WorkIDs (WorkID, Used)
> SELECT WorkID, 1 FROM WORK
> UNION
> SELECT WorkID, 1 FROM OtherTable1
> UNION
> SELECT WorkID, 1 FROM OtherTable2
> Now add some new ones for future use:
> DECLARE @.maxworkID int
> DECLARE @.x int
> SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
> SET @.x = @.maxworkID
> WHILE @.x < @.maxworkID + 10000
> BEGIN
> INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
> SET @.x = @.x + 1
> END
> Now here's your procedure for creating a new workID:
> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION|||On Thu, 11 Aug 2005 21:39:02 -0500, Louis Davidson wrote:
[snip]

> This gets even worse if this transaction gets called in another transactio
n.
> There is not a good way to do this without single threading.
Thanks for your comments. So, is there any decent answer to the problem of
generating new WorkIDs without using IDENTITY?

Can you save XML to a Database?

The XML generated by notifications object needs to be saved. Where do I save it - in the database. That will still be stored in a table - with drawbacks!Not sure what the question is. You can save XML to a database or to a file.|||Sorry, I meant to say what does Integration services do when I deploy the project? Does it use XML to write to the database or the file system?|||You can store the packages in either the file system or the SQL Server MSDB database, your choice.|||How do I define the choice for later only database format? Is that something I have to specify as properties from Analysis services?|||I'm sorry, but I'm not sure what this has to do with Analysis Services. I'm talking about SSIS packages (files with a .dtsx extension). These can be deployed to either the database or the file system - you can't enforce that they are only deployed to one or the other when you are building the package. That's really an issue for your organization's policies and procedures.sql

Can you save XML to a Database?

The XML generated by notifications object needs to be saved. Where do I save it - in the database. That will still be stored in a table - with drawbacks!Not sure what the question is. You can save XML to a database or to a file.|||Sorry, I meant to say what does Integration services do when I deploy the project? Does it use XML to write to the database or the file system?|||You can store the packages in either the file system or the SQL Server MSDB database, your choice.|||How do I define the choice for later only database format? Is that something I have to specify as properties from Analysis services?|||I'm sorry, but I'm not sure what this has to do with Analysis Services. I'm talking about SSIS packages (files with a .dtsx extension). These can be deployed to either the database or the file system - you can't enforce that they are only deployed to one or the other when you are building the package. That's really an issue for your organization's policies and procedures.

:new.<variable_name>

I am trying to write an 'after insert' trigger for a very dynamic database that inserts all the new values put into a table into an audit table. To do this, I have to write :new.col1, :new.col2, :new.col3 etc into the audit table. I have a loop that puts ['col1','col2','col3', etc.] into a variable named column_name, but I don't seem to be able to dynamically generate the variable name. I guess I am looking for something like an eval function in PL/SQL that could do

stmt := 'new_value := :new.'||column_name;
eval(stmt);
insert into audit_table (:new.id,column_name,new_value,SYSDATE);

But I haven't found it yet, and I can't select column_name from table_name because table 'table_name' is mutating and Oracle won't let you select on a table that is changing. If anyone can help, it would be greatly appreciated. Thanks in advance.Hello,

I dont know such a command in PL/SQL ... but what do you think about dynamic SQL. Use the package methods DMBS_SQL.

I hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Hey, I can use dynamic SQL for something like the following

sql_stmt = 'select :col_name from :table_name where id=:id';
execute immediate into new_value using col_name, table_name, id

But this is an actual PL/SQL statement that I need to build on the fly like this

stmt := 'new_value=:new.'||col_name;
eval(stmt);
insert into audit_table values(:new.id,new_value,SYSDATE,col_name);

Any thoughts would be greatly appreciated. Thanks.|||Hello,

whats about

DECLARE
cVar VARCHAR2(500) := 0;
BEGIN
cVar := 'DECLARE ' ||
' cThis VARCHAR2(200) := ''' ||
'BEGIN ' ||
' cThis := :new.' || column_name ||
' INSERT INTO and so on and so on ' ||
'END;';

.
.
.
do the dynamic stuff
.
.
.

END;

Is this what you want to do ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

.txt file to SQL Server 2005

My package inserts a text file into a database table. It first deletes the table rows, reseeds identity to 0 then inserts data from a .txt file.

Runs fine in development. When the package is run on the live server, it appears to have trouble connecting. I've checked the connection strings and they should be fine.

Error: An OLE DB error has occurred. Error code: 0x8000FFFF
Error: The AcquireConnection metod call to the connection manager "MyServer.MyDB" failed with error code 0x8000FFFF

Any suggestions?Issue resolved: it didn't occur to me until now that I needed to change the configuration Data Source to "localhost" once it was on the server!!! Works like a charm now! My first SSIS pkg installed and working, now to create a job to run it each morning...the adventure continues.

.SQL file

I have install.sql, that i want to run...so I can add to my existing asp.net 2.0 project. How do can I add the table to my database using that file (contents below)? Database resides in SQL Server 2000.

create table[dbo].[CustomProfile](

[UserID] [uniqueidentifier]not null,

[FirstName] [nvarchar](25)not null,

[LastName] [nvarchar](25)not null,

[Address1] [nvarchar](75)not null,

[Address2] [nvarchar](75)null,

[City] [nvarchar](50)not null,

[State] [nvarchar](2)not null,

[Zip] [nvarchar](10)not null,

[Phone] [nvarchar](12)null,

[ProfileVersion] [int]not null,

[LastUpdatedDate] [datetime]not null

)on[PRIMARY]

alter table[dbo].[CustomProfile]add

constraint[PK_CustomProfile_UserProfile]primary key clustered

(

[UserID]

)with(IGNORE_DUP_KEY =OFF)on[PRIMARY]

Easiest way is to run it in the Query Analyzer. Open up SQL management tools, connect to the sevrver/db of your choice. Then you start the Query Analyzer (or new Query if it's 2005) from a menu. There you can paste the code and just click the green arrow to execute it. Voila!

|||

Thanks for the suggestion. However, I got the following error listed below. Any idea?

Msg 170, Level 15, State 1, Line 19

Line 19: Incorrect syntax near '('.

|||

I removed 'with(IGNORE_DUP_KEY =OFF)on[PRIMARY]'....and it worked. Is this not an SQL option?

Thanks again for your help.

|||

johram:

Easiest way is to run it in the Query Analyzer. Open up SQL management tools, connect to the sevrver/db of your choice. Then you start the Query Analyzer (or new Query if it's 2005) from a menu. There you can paste the code and just click the green arrow to execute it. Voila!

In a perfect world, perhaps...

The OP is saying he needs to create the table on SQL 2000. The script wil fail at the end ".. WITH (IGNORE_DUP_KEY).. " part. This is 2005 syntax. I believe the script was generated from Management Studio connected to 2000 box?

.rdlc table use of SUM and SWITCH together?

Hello everyone, thanks in advance for reading. I'm new to reports and have tried searching for my answer with no luck. Any direction would be great.

Here is the issue, I have 3 colums in the dataset which display String data ("High", "Medium", or "Low")

I've added a column to the table on the report where I would like to convert the value of "High", "Medium" and "Low" to 3,2,1 respectively then SUM these numbers.

Is this possible? I've tried several variations of the code below--maybe it's not possible to do what I want? Thanks again for your time and help.

Fields!PP.Value is set to:

=Switch(Fields!Avail_Rank.Value = "High", 3, Fields!Avail_Rank.Value = "Medium", 2, Fields!Avail_Rank.Value = "Low", 1)

=Switch(Fields!Volume.Value = "High", 3, Fields!Volume.Value = "Medium", 2, Fields!Volume.Value = "Low", 1)

=Switch(Fields!Integ_Rank.Value = "High", 3, Fields!Integ_Rank.Value = "Medium", 2, Fields!Integ_Rank.Value = "Low", 1)

=Sum(Fields!Avail_Rank.Value + Fields!Volume.Value + Fields!Integ_Rank)

One way of doing this is to define calculated fields on your dataset.

For example, define a calculated field called Avail_Rank_Numeric with the value expression: =Switch(Fields!Avail_Rank.Value = "High", 3, Fields!Avail_Rank.Value = "Medium", 2, Fields!Avail_Rank.Value = "Low", 1)

Then, in the Sum aggregate function in the report body use it as follows:
=Sum(Fields!Avail_Rank_Numeric.Value + ...)

-- Robert

|||Thank you very much! Your idea worked perfectly!