escaping a transaction

The code is
proc1
begin tgan
insert
exec proc2
commit tran
end
proc2
begin tran
commit tran
exec proc3 -- do not want to it be in the proc1's tran
end
The desire is to escape the execution of proc3 from the transaction started
by proc1.
If you like, the apology for the decision is: The proc3 does some complex
things (a couple of records are inserted, deleted, updated). This must be
done 1) atomically (all or nothing) but the operations are 2) expensive and
3) must always commit. So, for performance reasons, I have decieded to do it
transceding the transaction. In case of failure, which may happen only
because of system shut down, the state can be restored manually.I'm not sure I completely understand your question, however you may want to
take a look at the following stuff.
Check out ROLLBACK TRAN
http://msdn2.microsoft.com/en-us/library/ms181299.aspx
and TRY ... CATCH (SQL 2005 only)
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
--
Ekrem Önsoy
"valentin tihomirov" <V_tihomirov@.best.ee> wrote in message
news:eNi74gCFIHA.3400@.TK2MSFTNGP03.phx.gbl...
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction
> started by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive
> and 3) must always commit. So, for performance reasons, I have decieded to
> do it transceding the transaction. In case of failure, which may happen
> only because of system shut down, the state can be restored manually.
>|||Hi
I am not sure why you would want to do this as it would prolong the length
of the transaction without participating in it!
You may want to look at service broker, you could call xp_cmdshell to run a
query.
John
"valentin tihomirov" wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
>
>|||On 21 Oct, 22:21, "valentin tihomirov" <V_tihomi...@.best.ee> wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
proc1
begin tran
insert
commit tran
exec proc2
end
--
David Portas

Escape characters

What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.Hi
In as search condition there is the ESCAPE clause that allows you to specify
the escape character, you can also specify a literal character by using
square brackets.
See the topic "Pattern Matching in Search Conditions" in Books online.
When you are inserting the characters there should not be a need to escape
them e.g
CREATE TABLE #fred ( col1 varchar(10) )
INSERT INTO #fred values ( '%_*' )
INSERT INTO #fred select '%_*'
select * from #fred
col1
--
%_*
%_*
John
"Sathyaish" wrote:
> What should one pass as a field value into a table in the insert
> statement if the value contained a percentage symbol (%) or the
> asterisk symbol (*), since both of these have special wildcard
> meanings. What if I want to pass the special meanings and pass them as
> literals? Is there any escape character that I must use? I am using
> ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
> SQL Server 2000.
>|||While INSERTing data, you can simply pass data containig % inside single
quotes. % has a meaning in the context of LIKE, not in INSERT.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Sathyaish" <sathyaish@.gmail.com> wrote in message
news:1125988873.864860.108830@.g44g2000cwa.googlegroups.com...
What should one pass as a field value into a table in the insert
statement if the value contained a percentage symbol (%) or the
asterisk symbol (*), since both of these have special wildcard
meanings. What if I want to pass the special meanings and pass them as
literals? Is there any escape character that I must use? I am using
ADO.NET v1.1 of the framework with VB.NET. The database is Microsoft
SQL Server 2000.

escape characters

Hi,

I need to insert this command into a table, but I can't because.

insert XXX

set column1 = isnull(Title,'') +

case when (case when title is null then 1 else 0 end) = 1 then '' else ' ' end

+ isnull(Last_name,'') +

case when (case when First_name is null then 1 else 0 end) = 1 then '' else ' ' end

+ isnull(First_name,'') +

case when (case when Middle_initial is null then 1 else 0 end)= 1 then '' else ' ' +

isnull(Middle_initial,'') END

I don't know how to use escape sequence, I need insert just once

thanks

hi

i really dont understnd your problem.

does this is what you want

set column1 = isnull(Title,'') +

case when title is null then '' else ' ' end

+ isnull(Last_name,'') +

case when First_name is null then '' else ' ' end

+ isnull(First_name,'') +

case when Middle_initial is null then '' else ' ' end +

isnull(Middle_initial,'') END

OR

if u are facing the quote problem then check this out

SET QUOTED_IDENTIFIER ON

SELECT '"hi"'

SET QUOTED_IDENTIFIER OFF

SELECT """hi"""

SELECT "'hi'"

this might give you the hint of escape sequence.

Regards,

Thanks.

Gurpreet S. Gill

|||

hi,

please give us sample source table

and the output

thanks,

joey

|||

Alessandro,

You could do something like this instead:

update XXX
set Column1 = replace(replace(Title + ' ' + Last_Name + ' ' + First_Name + ' ' + Middle_initial, '@.@.', '@.'), '@.@.', '@.')

NOTE: Use Spaces instead of @. symbols.

ERRORS on Insert/Update DB2 from SQL Enterprise Manager (linked server)

I've set up an AS400/DB2 as a linked server, all standard configs (V4R5) on a SQL server 2000 machine and can select all day long. When I go to update or insert it throws an error referencing my driver. I'm using the HIS/OLEDB2 driver. Any ideas? Someone mentioned turning on Journaling on the 400?

Anything at this point would helpRE:
Q1 I'm using the HIS/OLEDB2 driver and can select all day long. When I go to update or insert it throws an error referencing my driver. Any ideas? Anything at this point would help

A1 Perhaps DB2 Connect provides a better odbc driver? Per IBM, DB2 Connect: "Provides extensive application programming tools for developing client-server and web applications using industry standard APIs such as ODBC, ADO, OLE DB, JDBC, SQLJ, DB2 CLI and Embedded SQL."

See: http://www-3.ibm.com/software/data/db2/db2connect/|||I found an answer that worked from a colleague. Thanks.

error-checking on INSERT

I'm writing a simple stored procedure in which I use INSERT/VALUES.
What is the best way to do error-checking to ensure that my rows got
written to the table? Is checking @.@.rowcount the best way?Check @.@.ERROR = 0 directly after the INSERT.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1ee281022fb6137798993b@.msnews.microsoft.com...
> I'm writing a simple stored procedure in which I use INSERT/VALUES.
> What is the best way to do error-checking to ensure that my rows got
> written to the table? Is checking @.@.rowcount the best way?|||http://www.sommarskog.se/error-handling-I.html
Madhivanan

error_number() won't return error code

hi all,

i have sql statement in ExecuteSQLTask, connecting to AdventureWorks

BEGIN TRY
insert into person.contacts(contactid) values (1);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

i purposely put a table that does not exist in the database to catch
the error. however, the error number that is 208 won't appear, instead
the whole error message will be displayed.

if i change the query in try block to
insert into person.contact(contactid) values (1);

it'll return the error number 544, which is correct.

what did i do wrong?
thanks!

You did nothing wrong.

Try/Catch doesn't catch all errors, specifically compile time errors (there are a few others). Try running the following, "more obvious" compile time error. This compile time error won't be caught either, since the execution of the statements never happens.

BEGIN TRY

PRINT 'Inside Try-Block'

'This is not sql'

END TRY

BEGIN CATCH

SELECT error_number() as 'Error In Try Block'

END CATCH

|||

thanks jaegd for your reply.. it didn't say in BOL that try/catch doesn't catch all errors or maybe i left that out.

anyhow, my problem is not solved. is there an alternative where i can catch any errors that occur?

|||See the following BOL reference for the constructs which Try/Catch does not trap.
http://msdn2.microsoft.com/en-us/library/ms175976.aspx

However, To catch almost every error (including compile time and deferred name resolution errors), you can use dynamic SQL in combination with sp_executesql.

BEGIN TRY
DECLARE @.not_sql NVARCHAR(100)
SET @.not_sql = 'This is not sql'
EXEC sp_executesql @.not_sql

END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
END CATCH

Note that almost every error is caught with this method, because broken client connections are not

trapped, hopefully for relatively obvious reasons (you have no

connection).|||thanks a lot jaegd

error:the string was not recognized as a valid DateTime.

hi all, i'm trying to insert the time/date a button was clicked on a gridview and it generates an error:the string was not recognized as a valid DateTime.There is an unknown word starting at index 0 i have changed the culture to en-US but it still doesn't work. i actually created the date/time column after some data had been entered into the table so the column allows nulls. this is my code:

InsertCommand="INSERT INTO test101(Surname,Names,Registration,Login Time)VALUES (@.Surname, @.Names, @.Registration,@.Login_Time)"

<Insert Parameters><asp:Parameter DefaultValue= DateTime.Now Type=DateTime Name="Login_Time" /></Insert Parameters
any suggestions?

When do you recieve that error? You mentioned button on a GridView, so I suppose that you're trying to update some values and, if that's the case, use this code:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
e.NewValues["Login_Time"] = DateTime.Now;
}

If, on the other hand, you're trying to insert some values in database, use this code (this is example for inserting values with DetailsView ):

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
e.Values["Login_Time"] = DateTime.Now;
}

|||

sorry, this message was supposed to be:

hi all, i'm trying to insert the time/date at the time a user clicks the submit button but it generates an error:the string was not recognized as a valid DateTime.There is an unknown word starting at index 0 i have changed the culture to en-US but it still doesn't work. this is my code:

InsertCommand="INSERT INTO test101(Surname,Names,Registration,Login Time)VALUES (@.Surname, @.Names, @.Registration,@.Login_Time)"

<Insert Parameters><asp:Parameter DefaultValue= DateTime.Now Type=DateTime Name="Login_Time" /></Insert Parameters
any suggestions?

|||

Where is that submit button (in DetailsView or in FormView)?

|||

it's on a login page. so i want the time the user logged on to be saved on a database

|||

Can you post your whole code?

|||

One way to achieve your goal is to change your InsertParameter into this:

<Insert Parameters><asp:Parameter Type="DateTime" Name="Login_Time" /></Insert Parameters>

and put this in Page_Load event:

SqlDataSource1.InsertParameters["Login_Time"].DefaultValue = DateTime.Now.ToString();

|||<asp:ButtonID="login1"runat="server"Text="Login"

Height="31px"OnClick="login1_Click"Width="65px"BackColor="#E0E0E0"Font-Size="Medium"/>

<asp:ButtonID="Reset1"runat="server"BackColor="#E0E0E0"Font-Size="Medium"

Height="32px"OnClick="Reset1_Click"Text="Reset"Width="63px"/>

<br/>

<br/>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>"

InsertCommand="INSERT INTO test101(Surname,Names,Registration,[Course code],[Login Time]) VALUES (@.Surname,@.Names,@.Registration,@.Course_code,@.Login_Time)">

<InsertParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue="Textbox1.Text"Name="Surname"

PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"DefaultValue="TextBox2.Text"Name="Names"

PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox3"DefaultValue="TextBox3.Text"Name="Registration"

PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox4"DefaultValue="TextBox4.Text"Name="Course_code"

PropertyName="Text"/>

<asp:Parameter Name="Login_Time" type=DateTime/>

</InsertParameters>

</asp:SqlDataSource>

<asp:GridViewID="GridView1"

runat="server"AutoGenerateColumns="False"AutoGenerateDeleteButton="True"DataKeyNames="ID"

AutoGenerateEditButton="True"AllowSorting="True"BackColor="LightGoldenrodYellow"BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"GridLines="None"PageSize="20"

Height="374px"EmptyDataText="null"DataSourceID=SqlDataSource1Visible="False"AllowPaging="True">

<Columns>

<asp:BoundFieldDataField="ID"HeaderText="ID"InsertVisible="False"ReadOnly="True"

SortExpression="ID"/>

<asp:BoundFieldDataField="Surname"HeaderText="Surname"SortExpression="Surname"/>

<asp:BoundFieldDataField="Names"HeaderText="Names"SortExpression="Names"/>

<asp:BoundFieldDataField="Registration"HeaderText="Registration"SortExpression="Registration"/>

<asp:BoundFieldDataField="Course code"HeaderText="Course code"SortExpression="Course code"/>

<asp:BoundFieldDataField="Grade"HeaderText="Grade"SortExpression="Grade"/>

<asp:BoundFieldDataField="login Time"HeaderText="Login Time"SortExpression="login Time"/>

</Columns>

</asp:GridView>

and this is the code behind:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlConnection conn =newSqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");

conn.Open();

GridView1.DataBind();

conn.Close();

}

publicvoid login1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

if (TextBox1.Text !="" && TextBox2.Text !="" && TextBox3.Text !="" && TextBox4.Text !="")

{

Response.Redirect("test1.aspx");

}

|||

Just add SqlDataSource1.InsertParameters["Login_Time"].DefaultValue = DateTime.Now.ToString(); in login1_Click event like this:

public void login1_Click(object sender, EventArgs e)
{
SqlDataSource1.InsertParameters["Login_Time"].DefaultValue = DateTime.Now.ToString();
SqlDataSource1.Insert();
if (TextBox1.Text != "" && TextBox2.Text != "" && TextBox3.Text != "" && TextBox4.Text !="")
{
Response.Redirect("test1.aspx");
}

And one question: Why are you opening connection in Page_Load event?:

SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");
conn.Open();
GridView1.DataBind();
conn.Close();

If you're using SqlDataSource this is unnecessarily.

|||

THANK YOU SO MUCH!!!!!

error: when insert into table

Violation of PRIMARY KEY
constraint 'PK__tablename__182C9B23'.
Cannot insert duplicate key in object 'tablename'.
The statement has been terminated.
Thanks reply
The error that you are getting is related to the violation of "PRIMARY KEY"
constraint. To know what primary key is defined on the table run the
following statment.
sp_pkeys <table name>
Make sure that you are inserting non duplicate values in the primary key.
ex:
create table t(idd int primary key)
insert into t values (1)
insert into t values (2)
insert into t values (3)
insert into t values (3) --error
in above case 4th insert into will fail because of duplication of primary
key value.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Error: The index entry exceeds the maximum length of 900 bytes

Hi,
while trying to insert I get the error msg. (despite of Microsoft SQL Server
2000 sp3)
"Server: Msg 1946, Level 16, State 4, Line 3
Operation failed. The index entry of length 986 bytes for the index
'IndexName' exceeds the maximum length of 900 bytes."
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
The number of entries is about 11400, last id = 11889.
Any ideas? What is a temporary index? Is it possible to extend this limit?
Regards,
BerndBernd,
I suspect you have a varying column (n/varchar or varbinary) indexed. While
you can add these to an index, index rows cannot exceed 900 bytes.
Therefore, if the combined datalength of all columns in your insert that are
included in a single index is > 900, the insert will fail. Try the
following example to see what I mean:
CREATE TABLE #IndexTest(ColA INT, ColB VARCHAR(1000))
GO
CREATE INDEX VARCHARINDEX ON #IndexTest(ColB)
Go
INSERT #IndexTest VALUES (1, REPLICATE('0', 900))
INSERT #IndexTest VALUES (2, REPLICATE('0', 901))
GO
SELECT * FROM #IndexTest
GO
"Bernd Binder" <bernd@.binders.de> wrote in message
news:udocFIRkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> while trying to insert I get the error msg. (despite of Microsoft SQL
Server
> 2000 sp3)
> "Server: Msg 1946, Level 16, State 4, Line 3
> Operation failed. The index entry of length 986 bytes for the index
> 'IndexName' exceeds the maximum length of 900 bytes."
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
> The number of entries is about 11400, last id = 11889.
> Any ideas? What is a temporary index? Is it possible to extend this limit?
> Regards,
> Bernd
>|||FYI - In SQL Server 2005 this restriction is lifted - kind of. The total
length of all key columns in an index cannot exceed 900 bytes but using the
new INCLUDE syntax, you can add extra columns of any length to an index
record - thus creating a query covering index.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e3NOaiRkEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Bernd,
> I suspect you have a varying column (n/varchar or varbinary) indexed.
While
> you can add these to an index, index rows cannot exceed 900 bytes.
> Therefore, if the combined datalength of all columns in your insert that
are
> included in a single index is > 900, the insert will fail. Try the
> following example to see what I mean:
>
> CREATE TABLE #IndexTest(ColA INT, ColB VARCHAR(1000))
> GO
> CREATE INDEX VARCHARINDEX ON #IndexTest(ColB)
> Go
> INSERT #IndexTest VALUES (1, REPLICATE('0', 900))
> INSERT #IndexTest VALUES (2, REPLICATE('0', 901))
> GO
> SELECT * FROM #IndexTest
> GO
>
> "Bernd Binder" <bernd@.binders.de> wrote in message
> news:udocFIRkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > while trying to insert I get the error msg. (despite of Microsoft SQL
> Server
> > 2000 sp3)
> >
> > "Server: Msg 1946, Level 16, State 4, Line 3
> > Operation failed. The index entry of length 986 bytes for the index
> > 'IndexName' exceeds the maximum length of 900 bytes."
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
> >
> > The number of entries is about 11400, last id = 11889.
> >
> > Any ideas? What is a temporary index? Is it possible to extend this
limit?
> >
> > Regards,
> > Bernd
> >
> >
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:e3NOaiRkEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Bernd,
> I suspect you have a varying column (n/varchar or varbinary) indexed.
While
Yes.
Thank you.
[...]
> >
> > "Server: Msg 1946, Level 16, State 4, Line 3
> > Operation failed. The index entry of length 986 bytes for the index
> > 'IndexName' exceeds the maximum length of 900 bytes."
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280744
> >
> > The number of entries is about 11400, last id = 11889.
> >
> > Any ideas? What is a temporary index? Is it possible to extend this
limit?
> >
> > Regards,
> > Bernd
> >
> >
>