RE: CREATE DATABASE inside a transaction with Microsoft SQL 2005
- From: evanba@xxxxxxxxxxxxxxxxxxxx (Evan T. Basalik (MSFT))
- Date: Fri, 23 Jun 2006 17:32:16 GMT
I was able to reproduce your problem, but I think that you are running into two things:
1) A by-design limitation of SQL Server 2005
2) An issue with the SQL 2000 JDBC driver
According to SQL Server BOL (Transact-SQL Statements Allowed in Transactions), "Create Database" is NOT allowed inside an explicit transaction.
Therefore, the resulting error message is expected. You can also reproduce this issue by running TSQL in Management Studio:
BEGIN TRAN
CREATE DATABASE MyDB
COMMIT TRAN
When I used the 2000 JDBC driver, I did not get any error message, but my Create Database call did NOT get rolled back. This would imply that we are
doing something odd with our transactions in the 2000 driver.
Regardless, the response from the 2005 driver looks to be the correct response.
I ran this code:
----------------------------------------------------
import java.sql.*;
public class DML2005
{
public static void displayRows(ResultSet rs)
{
try
{
System.out.println("=================================================================");
ResultSetMetaData rsmd = rs.getMetaData();
int numOfColumns = rsmd.getColumnCount();
int r = 0;
for(int i=1; i <= numOfColumns; i++ )
{
//System.out.print(rsmd.getColumnName(i));
System.out.print(rsmd.getColumnLabel(i));
if(i != numOfColumns)
System.out.print(" , ");
}
System.out.println("");
while(rs.next())
{
r++;
System.out.print("Row: " + r + ": ");
for(int i=1; i <= numOfColumns; i++ )
{
System.out.print(rs.getString(i));
if(i != numOfColumns)
System.out.print(" , ");
}
System.out.println("");
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
try
{
// Load the JDBC driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Connect to SQL Server
Connection conn = null;
String url = "jdbc:sqlserver://evanbagx620\\sql2005;DatabaseName=tempdb;SelectMethod=cursor;";
// Process the command line input
if (args.length == 0)
{
// Connect with default id/password
conn = DriverManager.getConnection(url, "sa", "");
}
else if (args.length == 2)
{
// Connect with user-provided username and password
String userName = args[0];
String password = args[1];
conn = DriverManager.getConnection(url, userName, password);
}
else
{
// Invalid command -> throw an exception
throw new Exception("\nUsage: java DML [userName password]\n");
}
conn.setAutoCommit(false); // requires an explicit call to commit() on the connection
// Create the schema
String sql = "CREATE Database Evan2005";
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
boolean rc=stmt.execute(sql);
//conn.commit();
System.out.println("Database created.");
System.out.println();
// Introduce a 3-second delay
java.lang.Thread.sleep(3000);
conn.rollback();
System.out.println();
System.out.println("database creation rolled back");
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}
----------------------------------------------------
Evan
--------------------
From: zunilp@xxxxxxxxxpostnews.google.com!u72g2000cwu.googlegroups.com!not-for-mail
Newsgroups: microsoft.public.sqlserver.jdbcdriver
Subject: CREATE DATABASE inside a transaction with Microsoft SQL 2005
Date: 30 May 2006 01:20:11 -0700
Message-ID: <1148977211.312156.74080@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>
Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!news.glorb.com!
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:7818
X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
Hi,
Till now I am using SQL server 2000 with Microsoft JDBC SP3 for 2000.
Everything is fine.
But now I moved to SQLServer 2005 with Microsoft JDBC for 2005. But it
throws
an error "CREATE DATABASE statement not allowed within multi-statement
transaction" when our application tries to create a new DB inside a
transaction. All other DDL statements like CREATE TABLE, ALTER etc
works well inside a transaction with JDBC. We found that only CREATE
DATABASE has problem.
This problem is not there with Old JDBC driver with SQL 2000.
I know such a problem is there with SYBASE in which by default sybase
wn't allow DDL statements inside a transaction. But they provide an
option DDL_IN_TRAN or something like that. Enabling this will allow
DDL.
How I can make CREATE DATBASE work inside a transaction. Is there any
options available.
Regards
zunilp
Cordys
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.
.
- Prev by Date: Re: Deadlock occurs on my application frequently
- Next by Date: Windows Registry entries needed for JDBC XA?
- Previous by thread: Re: Locking problem with 2005
- Next by thread: Windows Registry entries needed for JDBC XA?
- Index(es):
Relevant Pages
|
