@@IDENTITY issues with SQL2000 on GoDaddy

Okay... GoDaddy's driving menuts!!! Anyone... please help.

I am doing an insert into a database with:

INSERT blah blah blah; SELECT @.@.IDENTITY;

On EVERY OTHER SERVER I have tried this on (local SQLExpress 2005's and my companies various SQL2000 servers, this always returns for me the row number (of autoIndex number of the row just added to the database). My autoIndex was created with the following code:

ALTER TABLE sites ADD autoIndex INT NOT NULL IDENTITY (1,1) PRIMARY KEY

The code I have tried getting the identity with is:

cmd.CommandText ="INSERT blah blah; SELECT @.@.IDENTITY;";

string autoIndex =Convert.ToString(cmd.ExecuteScalar());

cmd.Dispose();

I also tried:

// bulding of SQL INSERT here

cmd.ExecuteNonQuery();

cmd.Dispose();

cmd =

newSqlCommand(SQLDataSource_pics.SelectCommand, conn);

cmd.CommandType =

CommandType.Text;

cmd.CommandText =

"SELECT @.@.IDENTITY;";string autoIndex =Convert.ToString(cmd.ExecuteScalar());

Session.Add(

"autoIndex", autoIndex);

Most of the time, I get a value of "0" (zero) for autoIndex. Every now an then (5%) of the time I get back a correct value.

I don't know where to go from here.

Thank you.

Hayedid

Are they using MS SQL 2000 or MySQL?

Now I had a problem before doing that when I had a cluster of SQL servers. My Intermediate layer sent INSERTS to computer A, UPDATE to computer B and SELECT to computer C.

So as you can see C did not know about A until after a few seconds was received on the replication.

Al

|||

Have you thought of making it a Stored Procedure? do the insert inside the sProc, and then, inside the SProc, having Declared an Integer variable (like Set @.NewID), the last line (after the insert) would be Select @.NewID, returning the integer

Why are you converting the 'autoIndex ' to a string? Try making it an integer. If it's an identity field, it would return an integer

Then, you can do with it whatever you'd like after that.

|||

SQL2000. I am in the process of converting to MySQL to see if that resolves the problem.

I originally had autoIndex as an integer. I converted to a string simply because that the debugging procedure I am throwing the value into a Textbox to view it until I get this problem resovled.

I'll try the stored procedure.

Thanks.

|||

For anyone who's interested, I figured this one out. When I was testing this on my production server (hosted with GoDaddy), I went to the site through a masked domain. If I tested through the primary domain everything worked fine.

Not sure why this makes a difference. Any ideas? I was able to resolve the issue by forwarding my second domain without masking it.

Thank you everyone.

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

i have found loads of topics on this but have yet to find one that gives the answer i need.

I want to display the last insert id into a label/textbox after the INSERT function

how do i do this?

A question back at you -

Which database are you using?

|||

if the value you are using as an id is an idetity column and your database is sql server 2000 or 2005 then you can do something like this to get the last inserted value

strSQL = "";
strSQL += " Set Nocount on ";
strSQL += " Insert TableX(...) VALUES (...) ";
strSQL += " select IdentityInsert = IDENT_CURRENT( 'TableX' ) ";
strSQL += " set nocount off ";

hope this helps

|||

In sqlserver you can also do it with stored procedure:

CREATE procedure [dbo].[textbookop_insert])
as
begin
INSERT INTO [cosolu].[dbo].[cosolu_textbookop]
           ([textbookop_textbookid])
     VALUES
           (@.textbookop_textbookid)
return @.@.identity
end;
GO

You can insert the value using the following code:

 SqlConnection sqlconn = DB.CreateConn(); SqlCommand sqlcmd =new SqlCommand("textbookop_insert", sqlconn); sqlcmd.CommandType = CommandType.StoredProcedure; SqlParameter sqlpar = sqlcmd.Parameters.Add("ReturnValue", SqlDbType.Int); sqlpar.Direction = ParameterDirection.ReturnValue; sqlcmd.Parameters.AddWithValue("@.textbookop_textbookid", textbookop_textbookid); sqlconn.Open(); sqlcmd.ExecuteNonQuery();int camid = Convert.ToInt32(sqlcmd.Parameters["ReturnValue"].Value); sqlconn.Close();

Hope this helps.|||i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object|||im using sql server 2005|||

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

|||

After you do an insert you can return the key for the insert like:

INSERT INTO [dbo].[table] (name, phone)VALUES ("Denny", "555-1234")Return@.@.IDENTITY

Also be sure to read on the differences of SCOPE_IDENTITY() and @.@.IDENTITY - to be sure which one you should use to return the correct id.

|||so after i set Return @.@.IDENTITY how would that display it into a label / textbox?|||

Lee Dumond:

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

where is this file? i dont have any xsd files in my folders!!

|||

Sorry, I thought you were using an ObjectDataSource. I should have read more closely. My bad. (With the OnjectDataSource, you can use a strongly-typed Dataset that contains TableAdapters.)

In the case of using an SqlDataSource, you'll can use one of the methods above to return the identity value with the insert command.

|||

Lee Dumond:

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Question: doesn't this risk getting a later insert's identity value? How can I get the scope_identity value through the table adapter like this but from the same database call that actually makes the insert (less risky)? Regardless, thanks for a great solution using table adapters!

|||

Yes, is which why you should call itimmediately, right after you call the Update method. As you correctly point out, it surely doesn't make sense to use it in any other manner.

Here is an example where it could come in handy. Say, for instance, you have two tables: Orders and OrderItems. The OrderItems table is going to contain an OrderID column that is a foreign key back to the Orders table (OrderID would be a primary key column in Orders).

When you place OrderItems in your dataset, you have no idea what value to put in that foreign key OrderID column, because the current order hasn't been saved yet. You have a classic chicken-and-egg situation. What to do?

Easy... when you create the OrderItems, just put in any dummy value you want (it is going to be replaced with the correct value in a minute).

When you've added all the items you want, the current order is finished and you'll want to save it. To do that, do an Update on the OrdersTableAdapter, then immediately call the GetLastIdentityValue() method you've set up:

ordersTableAdapter.Update(myDBDataSet.Orders);int orderId = (int) ordersTableAdapter.GetLastIdentityValue();

This gives you the number to put in that foreign key column of the OrderItems table. So, now you loop through the dataset of the OrderItems, set each OrderID value of each accordingly, then update that:

foreach (myDBDataSet.OrderItemsRow rowin myDBDataSet.OrderItems){ row.OrderID = orderId;}orderItemsTableAdapter.Update(myDBDataSet.OrderItems);

Voila! All the foreign keys match, anf the integrity of the database is maintained.

|||

i did this in the end by using mysql and puttin it into the code behind.

All works good,

cheers for all help

|||

That is exactly the answer I needed... all the other projects here had been done in Deklarit, and after an update the DataRow held the new identity value... not so, apparently, with the xsd dataset in studio. This is exactly what I needed. Thanks so much!

Thomas

@@identity

I am running an insert sp that looks like this
*****************************
@.x int,
@.x1 int,
@.x2 int,
@.NewID int
AS
BEGIN
INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
SET @.New ID = (Select @.@.IDENTITY)
INSERT INTO TABLE2 (@.newID,@.x)
END
***********************
I am getting a conversion error, "Error converting data type varchar to
int.". I cannot figure out the converstion process. I have tried a ton of
different options but nothing works.
I want to run both INSERTs in the same sp.
Any ideas...>SET @.New ID = (Select @.@.IDENTITY)
Try: @.NewID = @.@.IDENTITY
If you are on SQL Server 2000, look at Scope_Identity() in BOL.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
M.Smith wrote:
> I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>|||worked with SCOPE, thanks
"Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Try: @.NewID = @.@.IDENTITY
> If you are on SQL Server 2000, look at Scope_Identity() in BOL.
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
> M.Smith wrote:|||Hi
Unless you want to keep @.NEWID for later use then you may want to use
SCOPE_IDENTITY() directly in the insert statement.
John
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:uxQhOmFPFHA.3372@.TK2MSFTNGP10.phx.gbl...
> worked with SCOPE, thanks
> "Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
> news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
>|||Don't forget to specify the fileds in your INSERT statement.
INTO into TheTable (Field1,Field2) values (@.Value1,@.Value2)
JN.
"M.Smith" <martys@.bennyhinn.org> a crit dans le message de news:
%23Z$GiMFPFHA.1088@.TK2MSFTNGP14.phx.gbl...
>I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>

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

Does this only work with an identity column?

my :

INSERT INTO [Contacts].[ekr].[Usrs]([id], [Uid], [Pw], [writ], [Maint])
VALUES(7, 'MM', 'WW', 1, 0)

IF @.@.ERROR > 0
RAISERROR('String, Severity level, State',16,122)
ELSE
SELECT @.@.Identity as NewOne

Returns : NewOne
NULLYes. See the BOL entry on @.@.Identity.

Regards,

hmscott

Does this only work with an identity column?

my :

INSERT INTO [Contacts].[ekr].[Usrs]([id], [Uid], [Pw], [writ], [Maint])
VALUES(7, 'MM', 'WW', 1, 0)

IF @.@.ERROR > 0
RAISERROR('String, Severity level, State',16,122)
ELSE
SELECT @.@.Identity as NewOne

Returns : NewOne
NULL

@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

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

@@ identity insert

Is it possible to create a trigger that inserts the @.@.identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-yes it's possible. Create a trigger on Table1 for insert and execute an insert-statement using @.@.identity. Are you stuck?

datetime Parameter issue

I'm using a sproc to insert the time (Now()) into a datetime field. Somehow the time "seconds" are not making it into the field. For example, the variable grabs the value of Now() - "9/16/2005 01:58:15 AM" to insert into the field. But when I view the records, the datetime value is "2005-09-16 13:58:00.000".
What am I doing wrong?
Thanks for your help!
Lynnette
Here is a snippet of the code and the sproc:

Dim recDateAs DateTime = Now()
cmd.Parameters.Add(New SqlParameter("@.DELastChg", SqlDbType.DateTime)).Value = recDate

ALTER PROC usp_AddTmpFLSAfromPT
@.dtWdate datetime,
@.whereString as varchar(255),
@.DEuser as varchar(30),
@.DELastChg datetime

AS

DECLARE @.strSQL as varchar(2000)

SET @.strSQL =
'
INSERT INTO tmpFLSAEmpInfo
( Emp_Number,
PT_ID,
Emp_Division,
Emp_Dept,
--Emp_DeptInfo,
Emp_Supervisor,
Emp_Location,
Emp_Union,
Emp_SG,
Emp_Shift,
DEusername,
DELastChg,
WrkDate
)
SELECT
Employee2.Emp_Number,
Employee2.[ID],
Employee2.Division,
Employee2.Job_Dept_Code,
Employee2.Job_Supervisor,
Employee2.Loc_Name,
Employee2.[Union],
Employee2.Sched_Group,
Employee2.Shift,
''' + @.DEUser + ''',
''' + CONVERT(varchar, @.DELastChg) + ''',
''' + CONVERT(varchar, @.dtWdate) + '''

FROM Employee2
WHERE '
+ @.whereString

EXEC(@.strSQL)

You are prbly losing the seconds because of the conversion to varchar in your code above. Put in the size also. Perhaps something like this :CONVERT(varchar(20), @.dtWdate) might help.|||Thanks for the response! I tried your suggestion, but it had no effect - still losing the seconds. I tried using CONVERT(datetime, @.DELastChg, 131) but then I get an error that I cannot convert string to DATETIME. I am hopelessly stuck.
Any other suggestions?
Thanks!
Lynnette|||Changed it toCONVERT(varchar, @.DELastChg, 113) and it works!

: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

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

.net StoredProc

Hi,

I'm trying to use a LDAP request to simply fill a table. I want to use the database agent to schedule the insert every night. So I was trying to make a storedproc to do it, but I can't make a import of "System.DirectoryServices". Many references types look to be unavailable. How could I do it ? Some ideas ?
Regards
Jacques

Hi.

I find it easier to create a normal classlib and do the "create assembly", "create function", etc. myself. Your assembly must be marked EXTERNAL_ACCESS.

- Snippet
-- Delete all dependent objects from the assembly
DROP FUNCTION SomeXYZ

-- Drop and recreate the assembly
DROP ASSEMBLY MyImportClr
CREATE ASSEMBLY MyImportClr ...

-- Create the functions
CREATE PROC SomeXYZ EXTERNAL NAME ...

-- Test the new version of my stuff.

- EoSnippet

This is not to everyones taste, of course.
Hope this helps.

|||System.DirectoryServices is not in the SQL Server's supported list of framework libraries. The supported list of libraries have gone through heavy reliability testing to ensure that they are as reliable as other components of SQL Server.
If you want to use System.DirectoryServices you can register it as an unsafe user assembly in your database (as you would do for assemblies that you develop) and use it thereafter:
Create Assembly DirSvc from 'c:\Windows\Microsoft.NET\...\System.DirectoryServices.dll'
with permission_set=unsafe

however, you need to perform adequate reliability testing to make sure that the API's you are using from System.DirectoryServices (or any such non supported assembly) meet the reliability requirements of your application.

Thanks,
-Vineet.|||I'm experiencing a problem with this also. I've added the System.DirectoryServices assembly to SQL Server as UNSAFE (using a command like the one above), and then I've added my assembly (which uses System.DirectoryServices). Since my assembly is strong-named, I didn't use the UNSAFE permission set.

Everything imported fine, but when I try to run a function from my assembly, I get an error that says I don't have permission to access active directory:

System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.

This assembly runs fine in the development environment, which raises the question - who is this assembly running as when I execute the function from SQL Server? Is it using my trusted login to access active directory, which should work, or is it using a different login, which may not have access to read from AD?

Any help available would be appriciated.|||It is using the account SQL Server runs under. In your code, before the call to AD, try and do an impersonation (look in BOL for SqlContext.WindowsIdentity) and see if that doesn't help.

Niels|||

It turns out that, since I had to import the System.DirectoryServices assembly as "UNSAFE", I couldn't access it from my other assembly, which was imported as "SAFE". In order to access the "UNSAFE" assembly, my assembly also needed to be marked as "UNSAFE".

Not the best solution, but since I'm only doing AD reads and not writes, I'm not worried about the security implications of marking my assembly as UNSAFE. I'm glad the problem is resolved, though I'd like to see System.DirectoryServices, along with other classes from the framework, trusted in future versions of SQL2005. Perhaps we'll get that with SP1.

|||

Thank,

I was able to include the system.directory assembly in the SQL sever, but I can fully work with it.

I use a "DirectoryEntry" in conjunction with a "DirectorySearcher" and when I try to link them, I received a error message. "Unknown mechanism of authentification". I has tried to impersonate and get the same error. The DirectoryEntry look to be initialize correctly, but when I set the Search Root, the DirectoryEntry seam to loose all it's references. Some Ideas ?

There the code :

Dim AdDirEntry As New DirectoryEntry()
Dim MySearcher As New System.DirectoryServices.DirectorySearcher()
Dim WIdentity As WindowsImpersonationContext

WIdentity = SqlContext.WindowsIdentity.Impersonate()

AdDirEntry.Path = LDAP://AdServer
AdDirEntry.Username = "AdUser"
AdDirEntry.Password = "AdPassword"

' Set the research criteria for Active Directory.
MySearcher.SearchRoot = AdDirEntry
MySearcher.PropertiesToLoad.Add("location")
MySearcher.PropertiesToLoad.Add("portName")
MySearcher.PropertiesToLoad.Add("drivername")
MySearcher.PropertiesToLoad.Add("description")
MySearcher.PropertiesToLoad.Add("printername")
MySearcher.PropertiesToLoad.Add("servername")

WIdentity.Undo()

Thank for your help

Jack

.net StoredProc

Hi,

I'm trying to use a LDAP request to simply fill a table. I want to use the database agent to schedule the insert every night. So I was trying to make a storedproc to do it, but I can't make a import of "System.DirectoryServices". Many references types look to be unavailable. How could I do it ? Some ideas ?
Regards
Jacques

Hi.

I find it easier to create a normal classlib and do the "create assembly", "create function", etc. myself. Your assembly must be marked EXTERNAL_ACCESS.

- Snippet
-- Delete all dependent objects from the assembly
DROP FUNCTION SomeXYZ

-- Drop and recreate the assembly
DROP ASSEMBLY MyImportClr
CREATE ASSEMBLY MyImportClr ...

-- Create the functions
CREATE PROC SomeXYZ EXTERNAL NAME ...

-- Test the new version of my stuff.

- EoSnippet

This is not to everyones taste, of course.
Hope this helps.

|||System.DirectoryServices is not in the SQL Server's supported list of framework libraries. The supported list of libraries have gone through heavy reliability testing to ensure that they are as reliable as other components of SQL Server.
If you want to use System.DirectoryServices you can register it as an unsafe user assembly in your database (as you would do for assemblies that you develop) and use it thereafter:
Create Assembly DirSvc from 'c:\Windows\Microsoft.NET\...\System.DirectoryServices.dll'
with permission_set=unsafe

however, you need to perform adequate reliability testing to make sure that the API's you are using from System.DirectoryServices (or any such non supported assembly) meet the reliability requirements of your application.

Thanks,
-Vineet.|||I'm experiencing a problem with this also. I've added the System.DirectoryServices assembly to SQL Server as UNSAFE (using a command like the one above), and then I've added my assembly (which uses System.DirectoryServices). Since my assembly is strong-named, I didn't use the UNSAFE permission set.

Everything imported fine, but when I try to run a function from my assembly, I get an error that says I don't have permission to access active directory:

System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.

This assembly runs fine in the development environment, which raises the question - who is this assembly running as when I execute the function from SQL Server? Is it using my trusted login to access active directory, which should work, or is it using a different login, which may not have access to read from AD?

Any help available would be appriciated.
|||It is using the account SQL Server runs under. In your code, before the call to AD, try and do an impersonation (look in BOL for SqlContext.WindowsIdentity) and see if that doesn't help.

Niels
|||

It turns out that, since I had to import the System.DirectoryServices assembly as "UNSAFE", I couldn't access it from my other assembly, which was imported as "SAFE". In order to access the "UNSAFE" assembly, my assembly also needed to be marked as "UNSAFE".

Not the best solution, but since I'm only doing AD reads and not writes, I'm not worried about the security implications of marking my assembly as UNSAFE. I'm glad the problem is resolved, though I'd like to see System.DirectoryServices, along with other classes from the framework, trusted in future versions of SQL2005. Perhaps we'll get that with SP1.

|||

Thank,

I was able to include the system.directory assembly in the SQL sever, but I can fully work with it.

I use a "DirectoryEntry" in conjunction with a "DirectorySearcher" and when I try to link them, I received a error message. "Unknown mechanism of authentification". I has tried to impersonate and get the same error. The DirectoryEntry look to be initialize correctly, but when I set the Search Root, the DirectoryEntry seam to loose all it's references. Some Ideas ?

There the code :

Dim AdDirEntry As New DirectoryEntry()
Dim MySearcher As New System.DirectoryServices.DirectorySearcher()
Dim WIdentity As WindowsImpersonationContext

WIdentity = SqlContext.WindowsIdentity.Impersonate()

AdDirEntry.Path = LDAP://AdServer
AdDirEntry.Username = "AdUser"
AdDirEntry.Password = "AdPassword"

' Set the research criteria for Active Directory.
MySearcher.SearchRoot = AdDirEntry
MySearcher.PropertiesToLoad.Add("location")
MySearcher.PropertiesToLoad.Add("portName")
MySearcher.PropertiesToLoad.Add("drivername")
MySearcher.PropertiesToLoad.Add("description")
MySearcher.PropertiesToLoad.Add("printername")
MySearcher.PropertiesToLoad.Add("servername")

WIdentity.Undo()

Thank for your help

Jack

.net StoredProc

Hi,

I'm trying to use a LDAP request to simply fill a table. I want to use the database agent to schedule the insert every night. So I was trying to make a storedproc to do it, but I can't make a import of "System.DirectoryServices". Many references types look to be unavailable. How could I do it ? Some ideas ?
Regards
Jacques

Hi.

I find it easier to create a normal classlib and do the "create assembly", "create function", etc. myself. Your assembly must be marked EXTERNAL_ACCESS.

- Snippet
-- Delete all dependent objects from the assembly
DROP FUNCTION SomeXYZ

-- Drop and recreate the assembly
DROP ASSEMBLY MyImportClr
CREATE ASSEMBLY MyImportClr ...

-- Create the functions
CREATE PROC SomeXYZ EXTERNAL NAME ...

-- Test the new version of my stuff.

- EoSnippet

This is not to everyones taste, of course.
Hope this helps.

|||System.DirectoryServices is not in the SQL Server's supported list of framework libraries. The supported list of libraries have gone through heavy reliability testing to ensure that they are as reliable as other components of SQL Server.
If you want to use System.DirectoryServices you can register it as an unsafe user assembly in your database (as you would do for assemblies that you develop) and use it thereafter:
Create Assembly DirSvc from 'c:\Windows\Microsoft.NET\...\System.DirectoryServices.dll'
with permission_set=unsafe

however, you need to perform adequate reliability testing to make sure that the API's you are using from System.DirectoryServices (or any such non supported assembly) meet the reliability requirements of your application.

Thanks,
-Vineet.|||I'm experiencing a problem with this also. I've added the System.DirectoryServices assembly to SQL Server as UNSAFE (using a command like the one above), and then I've added my assembly (which uses System.DirectoryServices). Since my assembly is strong-named, I didn't use the UNSAFE permission set.

Everything imported fine, but when I try to run a function from my assembly, I get an error that says I don't have permission to access active directory:

System.Security.SecurityException: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.

This assembly runs fine in the development environment, which raises the question - who is this assembly running as when I execute the function from SQL Server? Is it using my trusted login to access active directory, which should work, or is it using a different login, which may not have access to read from AD?

Any help available would be appriciated.|||It is using the account SQL Server runs under. In your code, before the call to AD, try and do an impersonation (look in BOL for SqlContext.WindowsIdentity) and see if that doesn't help.

Niels|||

It turns out that, since I had to import the System.DirectoryServices assembly as "UNSAFE", I couldn't access it from my other assembly, which was imported as "SAFE". In order to access the "UNSAFE" assembly, my assembly also needed to be marked as "UNSAFE".

Not the best solution, but since I'm only doing AD reads and not writes, I'm not worried about the security implications of marking my assembly as UNSAFE. I'm glad the problem is resolved, though I'd like to see System.DirectoryServices, along with other classes from the framework, trusted in future versions of SQL2005. Perhaps we'll get that with SP1.

|||

Thank,

I was able to include the system.directory assembly in the SQL sever, but I can fully work with it.

I use a "DirectoryEntry" in conjunction with a "DirectorySearcher" and when I try to link them, I received a error message. "Unknown mechanism of authentification". I has tried to impersonate and get the same error. The DirectoryEntry look to be initialize correctly, but when I set the Search Root, the DirectoryEntry seam to loose all it's references. Some Ideas ?

There the code :

Dim AdDirEntry As New DirectoryEntry()
Dim MySearcher As New System.DirectoryServices.DirectorySearcher()
Dim WIdentity As WindowsImpersonationContext

WIdentity = SqlContext.WindowsIdentity.Impersonate()

AdDirEntry.Path = LDAP://AdServer
AdDirEntry.Username = "AdUser"
AdDirEntry.Password = "AdPassword"

' Set the research criteria for Active Directory.
MySearcher.SearchRoot = AdDirEntry
MySearcher.PropertiesToLoad.Add("location")
MySearcher.PropertiesToLoad.Add("portName")
MySearcher.PropertiesToLoad.Add("drivername")
MySearcher.PropertiesToLoad.Add("description")
MySearcher.PropertiesToLoad.Add("printername")
MySearcher.PropertiesToLoad.Add("servername")

WIdentity.Undo()

Thank for your help

Jack

.NET Performance - Native vs OLE DB

Using a local instance of SQL Server 2000 and .NET I have managed to insert 4000 rows across multiple tables using the OLE DB provider in 11 seconds but when using the native provider it takes 13 seconds.
I was under the impression the native provider should enhance performance not decrease it? Why is this?
Thanks, Robby
By native provider, do you mean System.Data.SqlClient? In general SqlClient
is a good deal faster than using the combination of System.Data.OleDb and
the native oledb provider for sql server. To comment further, I'd need to
know more about your scenario.
Thanks,
Dave
"Robby White" <Robby White@.discussions.microsoft.com> wrote in message
news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> Using a local instance of SQL Server 2000 and .NET I have managed to
insert 4000 rows across multiple tables using the OLE DB provider in 11
seconds but when using the native provider it takes 13 seconds.
> I was under the impression the native provider should enhance performance
not decrease it? Why is this?
> Thanks, Robby
|||Yes I mean the System.Data.SqlClient provider.
Inserting 4000 records into SQL Server 2000
using the System.Data.SqlClient took 14 seconds
using the System.Data.Odbc took 13 seconds
using the System.Data.OleDb took 12 seconds
I have tried running these tests in different orders also and get the same results. I realise the times are close but I am concerned about scalibility.
Robby White
"David Schleifer [MSFT]" wrote:

> By native provider, do you mean System.Data.SqlClient? In general SqlClient
> is a good deal faster than using the combination of System.Data.OleDb and
> the native oledb provider for sql server. To comment further, I'd need to
> know more about your scenario.
> Thanks,
> Dave
> "Robby White" <Robby White@.discussions.microsoft.com> wrote in message
> news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> insert 4000 rows across multiple tables using the OLE DB provider in 11
> seconds but when using the native provider it takes 13 seconds.
> not decrease it? Why is this?
>
>
|||The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
"Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
> Inserting 4000 records into SQL Server 2000
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.[vbcol=seagreen]
> Robby White
> "David Schleifer [MSFT]" wrote:
SqlClient[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
performance[vbcol=seagreen]
|||Thanks for your help...
"David Schleifer [MSFT]" wrote:

> The insert operation is a fairly simple one, compared to other provider
> operations such as reading data, so it's hard for any one provider to exceed
> another once it's been reasonbly optimized. If you want to compare overall
> provider performance, whatever benchmark you choose would have to include a
> fair measure of read operations, which is one of the areas where SqlClient
> really outperforms the oledb managed provider.
> That said, when I tried a simple test with 16000 distinct insert operations
> (i.e. seperate round trip for each), I got results for SqlClient that were
> 1-2% better than the OleDb provider. I think it's fair to say that the two
> providers are basically equivalent for many types of insert operations, but
> overall for best performance you will be better off with SqlClient.
> If you are only interested in insert performance, you will probably want to
> check out the Whidbey release of .NET, where SqlClient supports a bulk load
> api.
>
> "Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
> news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> results. I realise the times are close but I am concerned about
> scalibility.
> SqlClient
> and
> to
> performance
>
>

.NET Performance - Native vs OLE DB

Using a local instance of SQL Server 2000 and .NET I have managed to insert
4000 rows across multiple tables using the OLE DB provider in 11 seconds but
when using the native provider it takes 13 seconds.
I was under the impression the native provider should enhance performance no
t decrease it? Why is this?
Thanks, RobbyBy native provider, do you mean System.Data.SqlClient? In general SqlClient
is a good deal faster than using the combination of System.Data.OleDb and
the native oledb provider for sql server. To comment further, I'd need to
know more about your scenario.
Thanks,
Dave
"Robby White" <Robby White@.discussions.microsoft.com> wrote in message
news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> Using a local instance of SQL Server 2000 and .NET I have managed to
insert 4000 rows across multiple tables using the OLE DB provider in 11
seconds but when using the native provider it takes 13 seconds.
> I was under the impression the native provider should enhance performance
not decrease it? Why is this?
> Thanks, Robby|||Yes I mean the System.Data.SqlClient provider.
Inserting 4000 records into SQL Server 2000
using the System.Data.SqlClient took 14 seconds
using the System.Data.Odbc took 13 seconds
using the System.Data.OleDb took 12 seconds
I have tried running these tests in different orders also and get the same r
esults. I realise the times are close but I am concerned about scalibility.
Robby White
"David Schleifer [MSFT]" wrote:

> By native provider, do you mean System.Data.SqlClient? In general SqlClien
t
> is a good deal faster than using the combination of System.Data.OleDb and
> the native oledb provider for sql server. To comment further, I'd need to
> know more about your scenario.
> Thanks,
> Dave
> "Robby White" <Robby White@.discussions.microsoft.com> wrote in message
> news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> insert 4000 rows across multiple tables using the OLE DB provider in 11
> seconds but when using the native provider it takes 13 seconds.
> not decrease it? Why is this?
>
>|||The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
"Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
> Inserting 4000 records into SQL Server 2000
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.[vbcol=seagreen]
> Robby White
> "David Schleifer [MSFT]" wrote:
>
SqlClient[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
performance[vbcol=seagreen]|||Thanks for your help...
"David Schleifer [MSFT]" wrote:

> The insert operation is a fairly simple one, compared to other provider
> operations such as reading data, so it's hard for any one provider to exce
ed
> another once it's been reasonbly optimized. If you want to compare overall
> provider performance, whatever benchmark you choose would have to include
a
> fair measure of read operations, which is one of the areas where SqlClient
> really outperforms the oledb managed provider.
> That said, when I tried a simple test with 16000 distinct insert operation
s
> (i.e. seperate round trip for each), I got results for SqlClient that were
> 1-2% better than the OleDb provider. I think it's fair to say that the two
> providers are basically equivalent for many types of insert operations, bu
t
> overall for best performance you will be better off with SqlClient.
> If you are only interested in insert performance, you will probably want t
o
> check out the Whidbey release of .NET, where SqlClient supports a bulk loa
d
> api.
>
> "Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
> news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> results. I realise the times are close but I am concerned about
> scalibility.
> SqlClient
> and
> to
> performance
>
>

.NET assembly for BULK INSERT

Hi,

Is there any .NET package/module/assembly for BCPing the data into SQL
Server.
The BULK INSERT utility works fine, but we are kind of worried on
placing the data.

If there is any utility to build the data into a data structure and BCP
in into the SQL Server, please let know. It would be of great use.

Let know if I should post this elsewhere

Regards,
ThyaguThyagu (tdelli@.gmail.com) writes:

Quote:

Originally Posted by

Is there any .NET package/module/assembly for BCPing the data into SQL
Server.
The BULK INSERT utility works fine, but we are kind of worried on
placing the data.
>
If there is any utility to build the data into a data structure and BCP
in into the SQL Server, please let know. It would be of great use.

SqlClient has classes for bulk-copying. I have not used it, but a quick
look in MSDN Library telles me that you write from a datarow.

--
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|||To add to Erland's response, the SqlBulkCopy class was introduced with .Net
Framework 2.0 (VS 2005 et. al.). SqlBulkCopy includes a WriteToServer
method that allows you to pass a DataTable, DataRow array or IDataReader.
The ColumnMappings property specifies the mappings between source and
destination columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Thyagu" <tdelli@.gmail.comwrote in message
news:1157727628.683912.128730@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
Is there any .NET package/module/assembly for BCPing the data into SQL
Server.
The BULK INSERT utility works fine, but we are kind of worried on
placing the data.
>
If there is any utility to build the data into a data structure and BCP
in into the SQL Server, please let know. It would be of great use.
>
Let know if I should post this elsewhere
>
Regards,
Thyagu
>
>

|||Thanks very much guys. This is awesome!

Regards,
Thyagu.

Dan Guzman wrote:

Quote:

Originally Posted by

To add to Erland's response, the SqlBulkCopy class was introduced with .Net
Framework 2.0 (VS 2005 et. al.). SqlBulkCopy includes a WriteToServer
method that allows you to pass a DataTable, DataRow array or IDataReader.
The ColumnMappings property specifies the mappings between source and
destination columns.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Thyagu" <tdelli@.gmail.comwrote in message
news:1157727628.683912.128730@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Hi,

Is there any .NET package/module/assembly for BCPing the data into SQL
Server.
The BULK INSERT utility works fine, but we are kind of worried on
placing the data.

If there is any utility to build the data into a data structure and BCP
in into the SQL Server, please let know. It would be of great use.

Let know if I should post this elsewhere

Regards,
Thyagu