In SQL Server, you can rollback a TRUNCATE from a transaction.  It does write page deallocation to the log, as you mentioned. 


In Oracle, TRUNCATE TABLE is a DDL statement that cannot be used in a transaction (or, more accurately, cannot be rolled back).  AFAIK, if there is a transaction in progress when the statement is executed, the transaction is committed and then the TRUNCATE is executed and cannot be undone.

In Informix, the behaviour of TRUNCATE is slightly different; you can use TRUNCATE in a transaction, but the only statements permissible after that are COMMIT and ROLLBACK.

Other DBMS probably have their own idiosyncratic interpretations of the behaviour of TRUNCATE TABLE.


Is there a way to get the dimensions of an image without reading the entire file?

				
    URL url=new URL(&lt;BIG_IMAGE_URL&gt;);
    BufferedImage img=ImageIO.read(url);
    System.out.println(img.getWidth()+&quot; &quot;+img.getHeight());
    img=null;

Java/ImageIO getting image dimensions without reading the entire file?

I want to capture the &lt;kbd&gt;Ctrl&lt;/kbd&gt;+&lt;kbd&gt;D&lt;/kbd&gt; signal in my program and write a signal handler for it.
How can I do that?
I am working on *C* and using a *Linux* system.

How to capture Control+D signal?

Can the SQL &quot;truncate table&quot; command be used within a transaction?  I am creating an app and my table has a ton of records.  I want to delete all the records, but if the app fails I was to rollback my transaction.  Deleting each record takes a very long time.  I&#39;m wondering if I use truncate table, can I still rollback the transaction and get my data back in the event of a failure.  I realize that truncate table doesn&#39;t write each delete to the transaction log, but I&#39;m wondering if it writes the page deallocation to the log so that rollback works.  

Truncate Table Within Transaction

<p>Can the SQL "truncate table" command be used within a transaction?  I am creating an app and my table has a ton of records.  I want to delete all the records, but if the app fails I was to rollback my transaction.  Deleting each record takes a very long time.  I'm wondering if I use truncate table, can I still rollback the transaction and get my data back in the event of a failure.  I realize that truncate table doesn't write each delete to the transaction log, but I'm wondering if it writes the page deallocation to the log so that rollback works.</p>


I&#39;d like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?

Determine a table&#39;s primary key using TSQL

Is there a way to grab the columns name of a table in MySQL using PHP?


Get table column names in MySQL?

I have three tables to define users:

    USER: user_id (int), username (varchar)
    USER_METADATA_FIELD: user_metadata_field_id (int), field_name (varchar)
    USER_METADATA: user_metadata_field_id (int), user_id (int), field_value (varchar)

I&#39;d like to create a middle tier user that has certain access to other users within the application. To determine which users the logged in use can access, I am using a subquery like the following:

    SELECT user_id FROM user WHERE user_id 
         IN (SELECT user_id 
             FROM user_metadata 
             WHERE user_metadata_field_id = 1 AND field_value = &#39;foo&#39;)

Currently I am storing the subquery string in a variable and then dynamically inserting it into the outer query each time I need to pull a list of users. After doing this I thought, &quot;it has got to be better to just store  a string of the actual `user_id`s&quot;.

So instead of storing this in a variable...

    $subSql = &quot;SELECT user_id FROM user_metadata WHERE user_metadata_field_id = 1 AND field_value = &#39;foo&#39;&quot;;

... I actually perform the query and store the result like this...

    $subSql = &quot;12, 56, 89, 100, 1234, 890&quot;;

Then when I need to pull a lit of users that the logged in user has access to, I can do so with:

    $sql = &quot;SELECT user_id FROM user WHERE user_id IN ($subSql)&quot;;


**And finally the questions:**

How many items can you use in a MySQL `IN` CLAUSE? Storing the actual ids instead of the sub-sql statement has got to be faster for performing that outer query each time, right?

MySQL number of items within &quot;in clause&quot;

Aside from context menu -&gt; &quot;Edit Top 200 Rows&quot; from Object Explorer, is there a quick way to open a table in Edit mode where I can just quickly modify the value of a cell?  

I need to be able to page past the first 200 rows. And I dont want to write &quot;insert&quot; script for every minor tweak I need to do... I don&#39;t understand why SMS doesn&#39;t offer quick access to a paged table editor (like Navicat).

How to quickly edit values in table in SQL Server Management Studio?

In mysql, can I add a column and foreign key in the same statement?  And what is the proper syntax for adding the fk?

Here is my SQL:

    ALTER TABLE database.table
     ADD COLUMN columnname INT DEFAULT(1),
     FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE;

...and the accompanying error message:

&gt;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;FOREIGN KEY (fk_name) REFERENCES reftable(refcolumn) ON DELETE CASCADE&#39; at line 4


How to add a column and make it a foreign key in single MySQL statement?

I have a file with `.bak` extension.

How can I import this date to a database in SQL Server?



Import .bak file to a database in SQL server

How do I make a composite key with SQL Server Management Studio?

I want two *INT* columns to form the identity (unique) for a table

How do I make a composite key with SQL Server Management Studio?

I&#39;m currently searching for an easy way to serialize objects (in C# 3).

I googled some examples and came up with something like:

    MemoryStream memoryStream = new MemoryStream ( );
    XmlSerializer xs = new XmlSerializer ( typeof ( MyObject) );
    XmlTextWriter xmlTextWriter = new XmlTextWriter ( memoryStream, Encoding.UTF8 );
    xs.Serialize ( xmlTextWriter, myObject);
    string result = Encoding.UTF8.GetString(memoryStream .ToArray());

After reading this [question][1] I asked myself, why not using StringWriter? It seems much easier.

    XmlSerializer ser = new XmlSerializer(typeof(MyObject));
    StringWriter writer = new StringWriter();
    ser.Serialize(writer, myObject);
    serializedValue = writer.ToString();

Another Problem was, that the first example generated XML I could not just write into an XML column of SQL Server 2005 DB.

The first question is: Is there a reason why I shouldn&#39;t use StringWriter to serialize an Object when I need it as a string afterwards? I never found a result using StringWriter when googling.

The second is, of course: If you should not do it with StringWriter (for whatever reasons), which would be a good and correct way?

---

Addition:

As it was already mentioned by both answers, I&#39;ll further go into the XML to DB problem.

When writing to the Database I got the following exception:

&gt; System.Data.SqlClient.SqlException:
&gt; XML parsing: line 1, character 38,
&gt; unable to switch the encoding

For string

    &lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;&lt;test/&gt;

I took the string created from the XmlTextWriter and just put &lt;test/&gt; as xml there. This one did not work (neither with manual insertion into the DB).

Afterwards I tried manual insertion (just writing INSERT INTO ... ) with encoding=&quot;utf-16&quot; which also failed.
Removing the encoding totally worked then. After that result I switched back to the StringWriter code and voila - it worked.

Problem: I don&#39;t really understand why.

at Christian Hayter: With those tests I&#39;m not sure that I have to use utf-16 to write to the DB. Wouldn&#39;t setting the encoding to UTF-16 (in the xml tag) work then?

  [1]: https://stackoverflow.com/questions/1138414/can-i-serialize-xml-straight-to-a-string-instead-of-a-stream-with-c

Using StringWriter for XML Serialization

How to change column order in a table using SQL query in SQL Server 2005?

I want to rearrange column order in a table using SQL query.

How to change column order in a table using sql query in sql server 2005?

I&#39;d like to pass a table as a parameter into a scaler UDF. 

I&#39;d also prefer to restrict the parameter to tables with only one column. (optional)

Is this possible?

**EDIT**

I don&#39;t want to pass a table name, I&#39;d like to pass the table of data (as a reference I presume)

**EDIT**

I would want my Scaler UDF to basically take a table of values and return a CSV list of the rows.

IE

    col1  
    &quot;My First Value&quot;  
    &quot;My Second Value&quot;
    ...
    &quot;My nth Value&quot;

would return

    &quot;My First Value, My Second Value,... My nth Value&quot;

I&#39;d like to do some filtering on the table though, IE ensuring that there are no nulls and to ensure there are no duplicates. I was expecting something along the lines of:

    SELECT dbo.MyFunction(SELECT DISTINCT myDate FROM myTable WHERE myDate IS NOT NULL)

Pass table as parameter into sql server UDF

If a connection string specifies `Trusted_Connection=true` with SQL Server authentication mode, will performance of my web application be impacted? 



When using Trusted_Connection=true and SQL Server authentication, will this affect performance?

How can I easily insert a blob into a `varbinary(MAX)` field?

As an example:

thing I want to insert is: c:\picture.png&lt;br&gt;
the table is mytable&lt;br&gt;
the column is mypictureblob&lt;br&gt;
the place is recid=1

How to insert a blob into a database using sql server management studio

I want to list all sales, and group the sum by day.

    Sales (saleID INT, amount INT, created DATETIME)

NOTE: I am using SQL Server 2005.


SQL query to group by day

I&#39;m trying to atomically increment a simple counter in Django. My code looks like this:

    from models import Counter
    from django.db import transaction

    @transaction.commit_on_success
    def increment_counter(name):
        counter = Counter.objects.get_or_create(name = name)[0]
        counter.count += 1
        counter.save()

If I understand Django correctly, this should wrap the function in a transaction and make the increment atomic. But it doesn&#39;t work and there is a race condition in the counter update. How can this code be made thread-safe?

Atomic increment of a counter in django

I&#39;m experimenting with using the command pattern to allow my web layer to work with Hibernate entities within the context of a single transaction (thus avoiding lazy loading exceptions). I am, however, confused now with how I should deal with transactions.

My commands call service layer methods that are annotated with `@Transactional` annotations. Some of these service layer methods are read-only - e.g. `@Transactional(readOnly = true)` - and some are read/write.

My service layer exposes a command handler that executes commands passed to it on behalf of the web layer.

    @Transactional
    public Command handle(Command cmd) throws CommandException

I assume I am right in making the command handler&#39;s `handle` method transactional. This is where the confusion comes in. If the implementation of a command makes calls to multiple service layer methods, there is no way for the command handler to know whether operations called within the command will be read-only, read/write or a combination of the two.

I don&#39;t understand how propagation works in this example. If I were to make the `handle()` method `readOnly = true`, then what happens if the command then calls a service layer method that is annotated with `@Transactional(realOnly = false)`?


Spring @Transactional read-only propagation

In our project we&#39;re using TransactionScope&#39;s to ensure our data access layer performs it&#39;s actions in a transaction. We&#39;re aiming to *not* require the MSDTC service to be enabled on our end-user&#39;s machines. 

Trouble is, on half of our developers machines, we can run with MSDTC disabled.  The other half must have it enabled or they get the *&quot;MSDTC on [SERVER] is unavailable&quot;* error message. 

It&#39;s really got me scratching my head and has me seriously considering rolling back to a home-spun TransactionScope-like solution based on ADO.NET transaction objects. It&#39;s seemingly insane - the same code that works (and does not escalate) on half of our developer&#39;s **does** escalate on the other developer&#39;s.

I was hoping for a better answer to [https://stackoverflow.com/questions/506733/][1] but unfortunately it doesn&#39;t. 

Here&#39;s a sample bit of code that will cause the trouble, on the machines that try to escalate, it tries to escalate on the second connection.Open() (and yes, there is no other connection open at the time.)

    using (TransactionScope transactionScope = new TransactionScope() {
       using (SqlConnection connection = new SqlConnection(_ConStr)) {
          using (SqlCommand command = connection.CreateCommand()) {
             // prep the command
             connection.Open();
             using (SqlDataReader reader = command.ExecuteReader()) {
                // use the reader
                connection.Close();
             }
          }
       }
       
       // Do other stuff here that may or may not involve enlisting 
       // in the ambient transaction
    
       using (SqlConnection connection = new SqlConnection(_ConStr)) {
          using (SqlCommand command = connection.CreateCommand()) {
             // prep the command
             connection.Open();  // Throws &quot;MSDTC on [SERVER] is unavailable&quot; on some...
    
             // gets here on only half of the developer machines.
          }
          connection.Close();
       }
    
       transactionScope.Complete();
    }

We&#39;ve really dug in and tried to figure this out.  Here&#39;s some info on the machines that it works on:

 - Dev 1: Windows 7 x64 SQL2008
 - Dev 2: Windows 7 x86 SQL2008 
 - Dev 3: Windows 7 x64 &lt;strike&gt;SQL2005&lt;/strike&gt; **SQL2008**

Developers it doesn&#39;t work on:

 - Dev 4: Windows 7 x64, &lt;strike&gt;SQL2008&lt;/strike&gt; **SQL2005**
 - Dev 5: Windows Vista x86, SQL2005
 - Dev 6: Windows XP X86, SQL2005
 - My Home PC : Windows Vista Home Premium, x86, SQL2005

I should add that all machines, in an effort to hunt down the problem, have been fully patched with everything that&#39;s available from Microsoft Update.

Update 1:
========

 - [http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/a5462509-8d6d-4828-aefa-a197456081d3/][2] describes a similar problem...back in 2006!
 - [http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28VS.80%29.aspx][3] - read that code sample, it clearly demonstrates a nested-second connection (to a second SQL server, actually) which will escalate to DTC.  *We are not doing this in our code* - we&#39;re not using different SQL servers, nor different connection strings, nor do we have nested secondary connections opening - *there should not be escalation to DTC*.
 - [http://davidhayden.com/blog/dave/archive/2005/12/09/2615.aspx][4] (from 2005) talks about how escalation to DTC will always happen when connecting to SQL2000.  We&#39;re using SQL2005/2008
 - [http://msdn.microsoft.com/en-us/library/ms229978.aspx][5] MSDN on transaction escalation. 

That MSDN transaction-escalation page states that the following conditions will cause a transaction to escalate to DTC:

 1. At least one durable resource that does not support single-phase notifications is enlisted in the transaction.
 2. At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with does not cause a transaction to be promoted. However, whenever you open a second connection to a database causing the database to enlist, the System.Transactions infrastructure detects that it is the second durable resource in the transaction, and escalates it to an MSDTC transaction.
 3. A request to &quot;marshal&quot; the transaction to a different application domain or different process is invoked. For example, the serialization of the transaction object across an application domain boundary. The transaction object is marshaled-by-value, meaning that any attempt to pass it across an application domain boundary (even in the same process) results in serialization of the transaction object. You can pass the transaction objects by making a call on a remote method that takes a Transaction as a parameter or you can try to access a remote transactional-serviced component. This serializes the transaction object and results in an escalation, as when a transaction is serialized across an application domain. It is being distributed and the local transaction manager is no longer adequate.

We&#39;re not experiencing #3. #2 is not happening because there is only ever one connection at a time, and it&#39;s also to a single &#39;durable resource&#39;.  Is there any way that #1 could be happening?  Some SQL2005/8 configuration that causes it to not support single-phase notifications?


Update 2:
========
Re-investigated, personally, everyone&#39;s SQL Server versions - &quot;Dev 3&quot; actually has SQL2008, and &quot;Dev 4&quot; is actually SQL2005.  That&#39;ll teach me to never trust my coworkers again. ;)  Because of this change in data, I&#39;m pretty sure we&#39;ve found our problem.  Our SQL2008 developers weren&#39;t experiencing the problem because SQL2008 has copious amounts of awesome included that SQL2005 does not have.

It also tells me that because we&#39;re going to be supporting SQL2005 that we can&#39;t use TransactionScope like we have been, and if we want to use TransactionScope we&#39;re going to need to be passing a single SqlConnection object around...which seems problematic in situations where the SqlConnection can&#39;t easily be passed around...it just smells of global-SqlConnection instance.  Pew!


Update 3
========

Just to clarify up here in the question:

SQL2008:

 - Allows multiple connections within a single TransactionScope (as demonstrated in the above sample code.)
 - Caveat #1: If those multiple SqlConnections are nested, that is, two or more SqlConnections are opened at the same time, TransactionScope will immediately escalate to DTC.  
 - Caveat #2: If an additional SqlConnection is opened to a different *&#39;durable resource&#39;* (ie: a different SQL Server,) it will immediately escalate to DTC

SQL2005:

 - Does not allow multiple connections within a single TransactionScope, period.  It will escalate when/if a second SqlConnection is opened.

Update 4
========
In the interest of making this question even more &lt;strike&gt;of a mess&lt;/strike&gt; useful, and just for more clarity&#39;s sake, here&#39;s how you can get SQL2005 to escalate to DTC with a _single_ `SqlConnection`:

    using (TransactionScope transactionScope = new TransactionScope()) {
       using (SqlConnection connection = new SqlConnection(connectionString)) {
          connection.Open();
          connection.Close();
          connection.Open(); // escalates to DTC
       }
    }

This just seems broken to me, but I guess I can understand if every call to `SqlConnection.Open()` is grabbing from the connection pool.  

*&quot;Why might this happen, though?&quot;*  Well, if you use a SqlTableAdapter against that connection before it&#39;s opened, the SqlTableAdapter will open and close the connection, effectively finishing the transaction for you because you now can&#39;t re-open it.

So, basically, in order to successfully use TransactionScope with SQL2005 you need to have some sort of global connection object that remains open from the point of the first TransactionScope is instantiated until it&#39;s no longer needed.  Besides the code-smell of a global connection object, opening the connection first and closing it last is at odds against the logic of opening a connection as late as possible and closing it as soon as possible.  


  [1]: https://stackoverflow.com/questions/506733/
  [2]: http://social.msdn.microsoft.com/forums/en-US/windowstransactionsprogramming/thread/a5462509-8d6d-4828-aefa-a197456081d3/
  [3]: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28VS.80%29.aspx
  [4]: http://davidhayden.com/blog/dave/archive/2005/12/09/2615.aspx
  [5]: http://msdn.microsoft.com/en-us/library/ms229978.aspx


TransactionScope automatically escalating to MSDTC on some machines?

We have client app that is running some SQL on a SQL Server 2005 such as the following:

    BEGIN TRAN;
    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
    INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
    COMMIT TRAN;

It is sent by one long string command.

If one of the inserts fail, or any part of the command fails, does SQL Server roll back the transaction?  If it does not rollback, do I have to send a second command to roll it back?

I can give specifics about the api and language I&#39;m using, but I would think SQL Server should respond the same for any language.

SQL Server - transactions roll back on error?

I&#39;m trying to figure out when to use the bang! versions for saving and updating records? I&#39;ve read and heard that you don&#39;t need them if you&#39;re just saving one record or updating a single attribute, if you&#39;re confident nothing should go wrong, or to always use them outside of a controller. I guess I&#39;m paranoid about having multiple things getting saved then something fails then there is incomplete data in the DB. The current Rails project I&#39;m working on is over 50% complete and currently doesn&#39;t contain any bangs. I have some custom methods I&#39;m calling in models that update or create multiple records and worry if they should be in some sort of transaction. 

Sorry if this seems scattered but I&#39;m just trying to figure how to use the saving capabilities in ActiveRecord correctly and make my life easier and little more stress free in the end. Thanks for your time.

When do I use save!, create! and update_attributes! in Rails?

I am trying to truncate some long text in C#, but I don&#39;t want my string to be cut off part way through a word. Does anyone have a function that I can use to truncate my string at the end of a word?

E.g:

    &quot;This was a long string...&quot;

Not:

    &quot;This was a long st...&quot;

Truncate string on whole words in .NET C#

I would like to truncate a string such that its length is not longer than a given value.  I am writing to a database table and want to ensure that the values I write meet the constraint of the column&#39;s datatype.

For instance, it would be nice if I could write the following:

    string NormalizeLength(string value, int maxLength)
    {
        return value.Substring(0, maxLength);
    }

Unfortunately, this raises an exception because `maxLength` generally exceeds the boundaries of the string `value`.  Of course, I could write a function like the following, but I was hoping that something like this already exists.

    string NormalizeLength(string value, int maxLength)
    {
        return value.Length &lt;= maxLength ? value : value.Substring(0, maxLength);
    } 

Where is the elusive API that performs this task?  Is there one?

How do I truncate a .NET string?

I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?

At the moment I&#39;ve managed to come up with a SQL statement that returns all the commands I need to execute: 

    SELECT &#39;TRUNCATE TABLE &#39; ||  tablename || &#39;;&#39; FROM pg_tables WHERE tableowner=&#39;MYUSER&#39;;

But I can&#39;t see a way to execute them programmatically once I have them.

Truncating all tables in a Postgres database

To empty a database table, I use this SQL Query:

    TRUNCATE TABLE `books`

How to I truncate a table using Django&#39;s models and ORM?

I&#39;ve tried this, but it doesn&#39;t work:

    Book.objects.truncate()

How to TRUNCATE TABLE using Django&#39;s ORM?

I have a `UILabel` that can be varying lengths depending on whether or not my app is running in portrait or landscape mode on an iPhone or iPad. When the text is too long to show on one line and it truncates I want the user to be able to press it and get a popup of the full text. 

How can I check to see if the `UILabel` is truncating the text? Is it even possible? Right now I&#39;m just checking for different lengths based on what mode I&#39;m in but it does not work super well.

Content Type	Original Author	Original Content on Stackoverflow
Question	Davin Studer	View Question on Stackoverflow
Solution 1 - Sql	womp	View Answer on Stackoverflow
Solution 2 - Sql	Jonathan Leffler	View Answer on Stackoverflow

Truncate Table Within Transaction

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

How to capture Control+D signal?

Java/ImageIO getting image dimensions without reading the entire file?

Attributions