Issueing a CREATE TABLE statement within a transaction

Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?

OracleCommand cmd = new OracleCommand();

OracleConnection wConnection = new OracleConnection(wConnString);

wConnection.Open();

OracleTransaction trans = wConnection.BeginTransaction();

string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";

cmd.CommandText = wQry;

cmd.Connection = wConnection;

cmd.Transaction = trans;

try

{

cmd.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";

OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);

cmd4.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('testss')";

OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);

cmd2.ExecuteNonQuery();

wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";

OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);

cmd3.ExecuteNonQuery();

trans.Commit();

wConnection.Dispose();

}

catch

{

trans.Rollback();

wConnection.Close();

}

Thanks in advance,

Wade Sharp

This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.

Issue with passing a parameter to Stored Procedure using IN keywor

Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:

> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>

Issue on SQL in MS SQL2000 and Access

I am not a programmer so anyone who can help, please do.

I have downloaded an application written for MS SQL2000 but I want to use my desktop for testing and configuration where I have Access and XP Pro IIS. I was told that the SQL in MS SQL2000 is not the same as SQL in Access, so I will face problem even if I change the connection string to point to Access instead of MS SQL2000.

Is this true? If yes, how can I change the SQLs so that it works with Access?Yes, it is decidely true. While Jet (the database engine that MS-Access uses by default) uses an SQL-like language, it isn't the real McCoy. MS-SQL implements nearly all of the SQL-92 standard, and plays by the rules nearly all of the time.

With the Professional version of MS-Access, you get MSDE included on the CD, but not automajikally installed. You can install it (and I'd strongly recommend applying Service Pack 3), and it will give you almost exactly the same functionality as MS-SQL, but pared down to run on a workstation.

-PatP|||Does that mean if I buy a MS Office Pro (which I supposed have MSDE in it) and install the software in my desktop, it should run fine? Or should I buy other software from MS?

And you mentioned the MSDE not installed automatically, how do I then installed it?|||Operative word being should, yes, the application should work using MSDE.

MSDE is included in all of the Professional versions of MS-Access, including the one that ships as part of MS-Office Professional.

The MSDE installation instructions vary slightly from one product to another. To install the version shipped with MS-Office, you double click the SETUP icon in the MSDE folder on the CD.

-PatP|||Thanks again for the information.

Just to further my knowledge on SQL, do you have samples (or do you know a website that has samples) that shows the differences between the SQL in an Access query and that in MS SQL2000?

It would be nice to know some basic differences....

Issue connecting to a remote SQL Server 2000

I get the following error message in quotes. I have a web application written in ASP.NET 2.0 through which I am trying to connect to a remote SQL Server 2000. My operating system is Windows Vista .

I looked at many different sites to find a solution for this but without any luck. I am sure someone would have seen this issue here and I expect them to shed some light.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

The connection string is located in my web.config file which looks like

<add name="AMSConn" connectionString="Data Source=xxxx;
Initial Catalog=testdb;User ID=sa;Password=xxxx" providerName="System.Data.SqlClient" />

I want to get this issue resolved and I need you guys help.

Please let me know if you need any more information.

Thanks

Bharat

Hello my friend,

First try to access the database from the actual database server using the username and password within your connection string. If this fails, you are using the wrong username and password.

If this works, then it is a remote issue. Try and ping the database server from the computer running your application and see if you get a response or request timed out.

Kind regards

Scotty

|||

Hi Scotty,

First, thanks for the reply. I already tried your first suggestion. It's working fine in the database server with the same credentials. So, not a problem with username and password.

But, when I tried Pinging the server as you indicated I am getting the request timed out error. I don't know what is the reason. I don't have any problems logging into the machine using remote desktop.

Any more suggestions.

Thanks

Bharat

|||

hi Scotty,I have also same problem.

I can ping to remote sql server and can connect with sql query analyser with same credentials.

But can't connect from my aspx application.