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

hi,

I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:

SELECT
 @.OrderID = @.@.Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
 OrderID,
 ProductID,
 Quantity,
 UnitCost
)
SELECT
 @.OrderID,
 ShoppingCart.ProductID,
 ShoppingCart.Quantity,
 Prices.UnitCost
FROM
ShoppingCart INNER JOIN
 Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
 CartID = @.CartID

is there any way to rewrite this statement so that I can put it in the form insert()values(). ?is OrderID an autonumbering field?

if so - you shouldn't include it in your INSERT statement|||YEs it is ! Why do you not recommend putting it in the insert stetment ?

Anyways, I realized that my error wasn't really in the stored procedure, but in the function calling it.

Thanks.|||SQL will put the identity into the table automatically, what you're trying to do is over-write the value with the same number again...hence the duplicate key problem. So like the poster said, don't do it, get the help out and read what an identity column acutally is and how to use it.|||I'm not really overwriting the @.@.identity value, but assigning it to a variable. I then use the value of that variable in an insert statement on a totally separate table.
I solved my primary key issue and it wasn't related to the sql transaction i posted here, ....there was a logic error in my code.

So now even though its all working, you still think that the sql statement I'm using is incorrect ?|||Sorry I admit I was assuming the code that went before your example. The only suggest I would make would be to consider using SCOPE_IDENTITY rather than IDENTIY, tends to be safer in the long run.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
>
>

@@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 logic not working

Hi There

I am having a little trouble with something very simple:

I have a stored procedure with the following sql in it:

UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @.Guid

IF (@.@.ERROR <> 0)

BEGIN

RAISERROR ('Error setting Result_Id', 16, 1)

UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @.Guid

RETURN

END

To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work.

My issue is that all i get is the error:

cannot convert char to numeric etc etc

However the @.@.ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at the point where i try do the bogus update.

Is there something i should SET at the beginning of my sp or something ? How do i ensure that any error will be passed to the logic that checks @.@.ERROR and performs the appropriate actions ? This is SS2000, so i cannot use try catch.

Thanx

Unfortunately, you can't. There isn't a way to trap any and all kinds of errors in your T-SQL code, since some errors terminates the batch. T-SQL code after the point where the error happens, never gets executed. In your case, a conversion error are of the 'breaking' kind.

You can verify with a small example. Note that the last line never gets printed.

declare @.i int
set @.i = 'x'
print 'Error happened'
go

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'x' to a column of data type int.

In order to trap all kinds of errors, you need to trap the at the client (calling side) of the procedure. (if there is a 'client' of some sorts)

You can find more info on errorhandling here.
http://www.sommarskog.se/error-handling-II.html

/Kenneth

|||

Thanx Kenneth

So what can i do ?

What is the point of doing a IF (@.@.ERROR) check after a delete update or insert then?

I have to update a certain table to set the error code to 1 if an error occurs, is there no way to do this?

Thanx

|||

Well, it's a bit of a tight spot. T-SQL errorhandling possibilites are somewhat limited pre SS2005. The one certain point that all errors go to is to the 'client'. In the case of a procedure, the 'client' is whatever executed it. If this is another proc, then you still have the same problem. If it's some other 'external' program, then that's where all errors will land.

Please take some time and read through the link about errorhandling above. It's well spent time, I assure you. Errorhandling is like any other 'project', it needs afterthought and desicions must be made about what to do in various situations. It needs to be designed with understanding in order to serve it's purpose best - to give us robust and reliable code.

In this case, I don't think that the mission is impossible, but rather not as straightforward as we would like, due to the limitations and behaviour of some errors in T-SQL. But, to grant yourself some insight, and possibly a direction to go on how you want to handle your situattion, http://www.sommarskog.se/error-handling-II.html is a good place to start. =;o)

/Kenneth

|||

Thanx Kewin

I will definately read the link.

Yes it is a problem i have an sp that calls an sp, i have written it so that if @.@.ERROR <> 0 i RETURN @.@.ERROR and handle it in the calling sp, but in this case the execution will abort and not return the @.@.ERROR to the calling sp to be handled.

But let me first read the link and i will post again.

Thank You

|||

Hi Kewin

Am i correct in saying that my specific test which has to do with char to numeric implicit cast will cause execution to abort BUT if the update or insert for example violated a primary key constriant or something more general that the execution would continue and execute the T-SQL in the IF @.@.ERROR <> 0 logic ?

|||Correct. Not all errors are transaction aborting or batch aborting errors. For example, syntax errors or compile-time errors cannot be caught with exception handling in SQL Server 2005 also. Please check out Erland's home page and the error handling topics in BOL.|||

To add a pennies worth, the big twist to error handling (even in 2005, though you have the control of try...catch) is the client. If you have a well behaved client like Query Analyzer or SSMS, then the IF@.@.error logic is very important, because it will always return control after an error if the error is not batch terminating. However, almost every client I see stops executing on an error and ends the batch itself. In those cases it is important to handle the errors with that in mind.

Things like

<DML operation>

if @.@.error <> 0
begin
raiserror ('boom',16,1)
rollback transaction
return -100
end

Look good, but if the client never completes the message and returns control, you can be stuck with an open transaction. And even worse, you probably never reach the raiserror, rollback, or return if the error in the DML operation occurs.

But you have to be ready for it nevertheless...

|||

Thanx for all the feedback guys.

I have read the link Kewin and i know most of it but l did learn one or 2 new things, thanx.

I guess what i am really trying to ask is as far as SS2000 goes there is not anything more i can do in an stored procedure than :

IF @.@.ERROR <>0

BEGIN

RASIERROR

ROLLBACK TRAN

RETURN -1

END

These sp's are called by a .Net app i understand completely that the app must be ready for any kind of exception.

What i really want to confirm is that there is nothing more or better i could do from an sp error handling point of view?

Thanx

|||

You got it.
Basically, that's what is avaliable to us as far as errorhandling in T-SQL on SS 2000 goes.

A workaround of sorts around these limitations can be done, and when I think about it, that's pretty much what 'scrubbing' is all about. For known 'fatal' errors that you know you can't trap, and possibly could leave you in some sticky situation, it's common to try to ensure that the situation never happens, either by first validating thoroughly, or by different coding techniques.

eg, to avoid a PK violation when inserting a new row, you could first check for the existence, or (perhaps even better) do the insert based on a left joined select. In the latter case, if the PK didn't exist, 1 row would be affected, but should it already exist, 0 rows would be affected. But more importantly, it's not an error to do some DML and have zero rows affected. Approaching a problem from different angles may be of aid to circumvent some of the errorhandling limitations that we have.

/Kenneth

sql

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')
help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')
|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
located[vbcol=seagreen]
on
>
>.
>
|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> you will have to use
> message
> located
> on

can anyone tell me the cmd or store procedure that gives
me the space of the database log
Hi,
DBCC SQLPERF ( LOGSPACE )
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"?" <anonymous@.discussions.microsoft.com> wrote in message
news:2bf201c4287d$fe1c5830$a601280a@.phx.gbl...
> can anyone tell me the cmd or store procedure that gives
> me the space of the database log

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx
.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1ca6d01c452e8$ee0594d0$a501280a@.phx
.gbl...
located[vbcol=seagreen]
on[vbcol=seagreen]
>
>.
>|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> you will have to use
> message
> located
> on

can anyone tell me the cmd or store procedure that gives
me the space of the database logHi,
DBCC SQLPERF ( LOGSPACE )
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"'" <anonymous@.discussions.microsoft.com> wrote in message
news:2bf201c4287d$fe1c5830$a601280a@.phx.gbl...
> can anyone tell me the cmd or store procedure that gives
> me the space of the database log

can anyone tell me the cmd or store procedure that gives
me the space of the database logsp_helpdb dbname
will give you the sizes of the mdf and ldf file.
>--Original Message--
>can anyone tell me the cmd or store procedure that gives
>me the space of the database log
>.
>|||Hi,
DBCC SQLPERF ( LOGSPACE )
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"'" <anonymous@.discussions.microsoft.com> wrote in message
news:2bf201c4287d$fe1c5830$a601280a@.phx.gbl...
> can anyone tell me the cmd or store procedure that gives
> me the space of the database log

I created a store procedure in a database that is located
on my machine. i want to execute that store procedure on
another machine in which i have a linked server. Can
someone tell me how to execute it
ex: insert into #tmp openquery (link_server_name, 'exec
xpto')help
You have a good subject for this post
Try
EXEC sp_serveroption 'Server', 'Data Access', TRUE
GO
SELECT *
INTO tbl
FROM OPENQUERY('Server', 'EXEC usp') ;
If you have #temp tables used in your stored procedure, you will have to use
SET FMTONLY OFF while calling the procedure like:
SELECT * INTO tbl
FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> I created a store procedure in a database that is located
> on my machine. i want to execute that store procedure on
> another machine in which i have a linked server. Can
> someone tell me how to execute it
> ex: insert into #tmp openquery (link_server_name, 'exec
> xpto')|||Uri,
Doesn't work because he is still trying to execute the
procedure as if he is located on the other server and is
not. I want a query that will able me to execute a store
procedure on my machine on a remote server that doesnt
have that store procedure
>--Original Message--
>help
>You have a good subject for this post
>Try
>EXEC sp_serveroption 'Server', 'Data Access', TRUE
>GO
>SELECT *
> INTO tbl
> FROM OPENQUERY('Server', 'EXEC usp') ;
>If you have #temp tables used in your stored procedure,
you will have to use
>SET FMTONLY OFF while calling the procedure like:
>SELECT * INTO tbl
> FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
>
>"help" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
>> I created a store procedure in a database that is
located
>> on my machine. i want to execute that store procedure
on
>> another machine in which i have a linked server. Can
>> someone tell me how to execute it
>> ex: insert into #tmp openquery (link_server_name, 'exec
>> xpto')
>
>.
>|||Hi
Here is goes...
CREATE PROC spMyproc
AS
SELECT * FROM SERVER.DATABASENAME.DBO.TABLENAME
GO
EXEC spMyproc
GO
DROP PROC spMyproc
<anonymous@.discussions.microsoft.com> wrote in message
news:1cead01c452f0$e53f3510$a001280a@.phx.gbl...
> Uri,
> Doesn't work because he is still trying to execute the
> procedure as if he is located on the other server and is
> not. I want a query that will able me to execute a store
> procedure on my machine on a remote server that doesnt
> have that store procedure
> >--Original Message--
> >help
> >You have a good subject for this post
> >Try
> >EXEC sp_serveroption 'Server', 'Data Access', TRUE
> >GO
> >SELECT *
> > INTO tbl
> > FROM OPENQUERY('Server', 'EXEC usp') ;
> >
> >If you have #temp tables used in your stored procedure,
> you will have to use
> >SET FMTONLY OFF while calling the procedure like:
> >
> >SELECT * INTO tbl
> > FROM OPENQUERY('Server', 'SET FMTONLY OFF; EXEC usp') ;
> >
> >
> >
> >"help" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1ca6d01c452e8$ee0594d0$a501280a@.phx.gbl...
> >> I created a store procedure in a database that is
> located
> >> on my machine. i want to execute that store procedure
> on
> >> another machine in which i have a linked server. Can
> >> someone tell me how to execute it
> >>
> >> ex: insert into #tmp openquery (link_server_name, 'exec
> >> xpto')
> >
> >
> >.
> >

/L*v C:\temp\logfile

Hi!
I found that using /L*v with the setup procedure creates a verbose log
file. Is there any way to add a verbose log file to an existing database?
Stefan
hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfqz6rois9ddfw@.news.individual.de
> Hi!
> I found that using /L*v with the setup procedure creates a verbose
> log file. Is there any way to add a verbose log file to an existing
> database?
> Stefan
/L*v is a setup parameter, which enables the setup logging features...
I do not understand your requirements regarding "add a verbose log file to
an existing database"...
all SQL Server databases do have a transaction log file at least, and the
logging mode is dependent to the recovery setting, as described in
http://msdn.microsoft.com/library/de...kprst_6rqr.asp
...
can you please elaborate your requirements?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 11:05:25 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:

> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfqz6rois9ddfw@.news.individual.de
> /L*v is a setup parameter, which enables the setup logging features...
> I do not understand your requirements regarding "add a verbose log file
> to an existing database"...
> all SQL Server databases do have a transaction log file at least, and the
> logging mode is dependent to the recovery setting, as described in
> http://msdn.microsoft.com/library/de...kprst_6rqr.asp
> ..
> can you please elaborate your requirements?
I am having troubles when connecting to MSDE 1.0 after installing XP SP2:
The inital connection takes an eternity (10 seconds on lightning fast
machines, up to 90 seconds on my older working machine). In this time,
sqlsvr.exe causes 100% processor load and a lot of I/O stress. I'd simply
like to find out what is going on.
After the initial connection lag, operation continues normally.
We connect through Delphi 5 using ADO.
any pointers appreciated (upgrading to D7 is not an option; it's not my
decision)
Stefan
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq3c3uks9ddfw@.news.individual.de
> I am having troubles when connecting to MSDE 1.0 after installing XP
> SP2: The inital connection takes an eternity (10 seconds on lightning
> fast machines, up to 90 seconds on my older working machine). In this
> time, sqlsvr.exe causes 100% processor load and a lot of I/O stress.
> I'd simply like to find out what is going on.
> After the initial connection lag, operation continues normally.
> We connect through Delphi 5 using ADO.
> any pointers appreciated (upgrading to D7 is not an option; it's not
> my decision)
> Stefan
XP sp2 causes a lot of connection issues, see
http://www.michna.com/kb/WxSP2.htm for instance...
for related SQL Server issues please have a look at
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 13:04:27 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:

> XP sp2 causes a lot of connection issues, see
> http://www.michna.com/kb/WxSP2.htm for instance...
> for related SQL Server issues please have a look at
> http://support.microsoft.com/default.aspx?kbid=841249
Thanks for the pointers, Andrea. We've gone through the second one
already; I'll double check if we missed something there.
Do you think that an upgrade to MSDE 2000 would cure some issues?
Stefan, off reading
|||hi Stefan,
"Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
news:opsfq89i1ws9ddfw@.news.individual.de
> ..
> Thanks for the pointers, Andrea. We've gone through the second one
> already; I'll double check if we missed something there.
> Do you think that an upgrade to MSDE 2000 would cure some issues?
> Stefan, off reading
actually not, but MSDE 2000 can be worth upgrading becouse several
improvements in the database engine...
I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
related issue to connectivity..
opened the TCP port required for network connections and enlisted a range of
subnet IP addresses... all is ok..
... and I hope to be that lucky when upgrading the real physical machine
=;-D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 12 Oct 2004 15:39:23 +0200, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> wrote:

> hi Stefan,
> "Stefan M. Huber" <looseleaf@.gmx.net> ha scritto nel messaggio
> news:opsfq89i1ws9ddfw@.news.individual.de
> actually not, but MSDE 2000 can be worth upgrading becouse several
> improvements in the database engine...
Yes; as long as we can easily handle upgrading issues, like the password
in all our connection strings.

> I only installed a Virtual Machine of WinXP sp 2 but had (fortunately) no
> related issue to connectivity..
> opened the TCP port required for network connections and enlisted a
> range of subnet IP addresses... all is ok..
> ... and I hope to be that lucky when upgrading the real physical machine
> =;-D
I'd rather set up a complete testing machine beforehand
Good luck and thanks,
Stefan

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

.NET CLR stored procedure capabilities

I've want to create a typed DataSet and use it inside a .NET CLR stored
procedure. But the option to add a typed DataSet to the project
doesn't exist. Am I missing something? Or are .NET CLR stored
procedures not all they're hyped up to be?
Todd
toddpiltingsrud@.altoconsulting.com wrote in
news:1132776964.626979.93470@.g47g2000cwa.googlegro ups.com:

> I've want to create a typed DataSet and use it inside a .NET CLR
> stored procedure. But the option to add a typed DataSet to the
> project doesn't exist. Am I missing something? Or are .NET CLR
> stored procedures not all they're hyped up to be?
Course you can create a typed DataSet and use it in SQLCLR. Younhave to
create it manually though instead of drag 'n drop.
Just out of curiousity; why do you want a typed dataset inside SQL
Server?
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

.NET CLR stored procedure capabilities

I've want to create a typed DataSet and use it inside a .NET CLR stored
procedure. But the option to add a typed DataSet to the project
doesn't exist. Am I missing something? Or are .NET CLR stored
procedures not all they're hyped up to be?
Toddtoddpiltingsrud@.altoconsulting.com wrote in
news:1132776964.626979.93470@.g47g2000cwa.googlegroups.com:

> I've want to create a typed DataSet and use it inside a .NET CLR
> stored procedure. But the option to add a typed DataSet to the
> project doesn't exist. Am I missing something? Or are .NET CLR
> stored procedures not all they're hyped up to be?
Course you can create a typed DataSet and use it in SQLCLR. Younhave to
create it manually though instead of drag 'n drop.
Just out of curiousity; why do you want a typed dataset inside SQL
Server?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

.NET CLR stored procedure capabilities

I've want to create a typed DataSet and use it inside a .NET CLR stored
procedure. But the option to add a typed DataSet to the project
doesn't exist. Am I missing something? Or are .NET CLR stored
procedures not all they're hyped up to be?
Toddtoddpiltingsrud@.altoconsulting.com wrote in
news:1132776964.626979.93470@.g47g2000cwa.googlegroups.com:
> I've want to create a typed DataSet and use it inside a .NET CLR
> stored procedure. But the option to add a typed DataSet to the
> project doesn't exist. Am I missing something? Or are .NET CLR
> stored procedures not all they're hyped up to be?
Course you can create a typed DataSet and use it in SQLCLR. Younhave to
create it manually though instead of drag 'n drop.
Just out of curiousity; why do you want a typed dataset inside SQL
Server?
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

.neRe: Store procedures & Functions

hi,

what is the different between store procedure and function?

when it is right to use sp and when functions?

thanks in advanced

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233961&SiteID=1

This is link for post about difference between UDF and SP. You can use scalar-valued function anywhere when T-SQL command is expecting a value.

Thanks.

|||ok thanks for the help :)