|
You can do it with ADOX. (requires COM Interop)
|
|
|
|
|
We use transactions in many of our stored procedures, and it seems I've either been lulled into a false sense of security, or am implementing them incorrectly.
We have an sp, called from an ASP page. It, briefly, does the following:
Select from table1 into a cursor
Begin Transaction
Insert into table2
exec sp_2
exec sp_3
Insert into table3
Delete from table1 (data collected and now inserted into the new tables)
IF @@error = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
I was under the impression this meant that either everything in the transaction would occur, or nothing. It's critical (for our data) that it works this way. However, when setting up a new server over the last couple of days I carelessly forgot to put permissions on a couple of the tables (it's a staging server, not a live one). This resulted in the delete (final item in the transaction) occuring, but none of the inserts occurred. Very worrying. Surely an error should have been generated when the inserts weren't allowed, and everything, including the delete, should have been rolled back?
As I say, it's not a problem, all nicely fixed once I realised the error of my ways. But I am curious about this. Is there perhaps something I am overlooking in my transactions? Or an option which should be set on SQL Server 2000 to keep transactions behaving the way I want them to?
Thanks for any info 
To honor you, and your sick games, this smiley  will now represent licking chocolate off candy - David Chamberlain
|
|
|
|
|
You need to check the error status after each operation!
BEGIN TRAN T1
Insert into table2
if @@error != 0
BEGIN
ROLLBACK TRAN T1
raiserror 50000 'Insert into table2 failed'
return
END
exec sp_2
if @@error != 0
BEGIN
ROLLBACK TRAN T1
raiserror 50000 'exec of sp2 failed'
return
END
exec sp_3
if @@error != 0
BEGIN
ROLLBACK TRAN T1
raiserror 50000 'exec of sp3 failed'
return
END
Insert into table3
if @@error != 0
BEGIN
ROLLBACK TRAN T1
raiserror 50000 'Insert into table3 failed'
return
END
Delete from table1 (data collected and now inserted into the new tables)
if @@error != 0
BEGIN
ROLLBACK TRAN T1
raiserror 50000 'Delete from table1 failed'
return
END
COMMIT TRANSACTION
onwards and upwards...
|
|
|
|
|
Thanks
To honor you, and your sick games, this smiley will now represent licking chocolate off candy - David Chamberlain
|
|
|
|
|
You could also do the transaction handling on the other side of ADO, in your non-SQL code...
Here's an example of how you would do it in C# (simplified):
<br />
SqlConnection cn = new SqlConnection("connectionstring");<br />
SqlTransaction trans = cn.BeginTransaction();<br />
<br />
SqlCommand cmd = new SqlCommand("storedprocedurename");<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
<br />
try<br />
{<br />
cmd.ExecuteNonQuery();<br />
trans.Commit();<br />
}<br />
catch(SqlException e)<br />
{<br />
trans.Rollback();<br />
}<br />
|
|
|
|
|
Thanks Arjan
Just out of interest, have you found advantages for doing this from the front end rather than inside the stored procedures?
To honor you, and your sick games, this smiley will now represent licking chocolate off candy - David Chamberlain
|
|
|
|
|
It saves me of writing all the if's in the stored proc, so it saves some typing and preserves readability...
This is OK since I'm only using these SPs from within my app.
Backside of this is that the whole SP will be run, before doing the rollback. Even if it errs at the first SQL statement...
|
|
|
|
|
And the performance will not be as good and the procedure will not be reusable.
People need to think of stored procedures as public functions that you call, just like any other function in your program. The only difference is that this function resides in a public library and the mechanics of calling it are a little different. You can achieve an API-like environment with your stored procedures, including 'private', interal procedures and public, user procedures, just like you can in a *real* programming language. But, unfortunately, most people don't look at sp this way. Back to your problem: would you code a C# function that didn't throw an exception until all it's statements completed, regardless of which one failed? I hope not. Same with stored procedures.
onwards and upwards...
|
|
|
|
|
Both points are good points, so I'll keep them in mind.
Thanx...
|
|
|
|
|
I am developing a .NET application which accesses data in a Lotus Notes Domino database using the NotesSQL ODBC Driver and System.Odbc.OdbcDataReader.
If I connect to the datasource via DSN in Microsoft Access, I get a complete list of the tables. I did this just to check whether the tables I wanted existed.
Though, when I try to access the tables programmatically via the OdbcConnection.ExecuteReader method, I get the following exception:
Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name
I don't understand this as I can see in the Microsoft Access tablelist that the table exists.
Does anyone have any ideas to how I can fix this problem?
Thanks in advance!
/sinus-c
|
|
|
|
|
I solved the problem myself.
The problem was that NotesSQL couldn't handle the underscore character in tablenames, so the name had to be enclosed in doublequotes in SQL statements.
/sinus-c
|
|
|
|
|
ok i work for a company and we use msde for our software that we distribute. This means we need to run creation scripts on every new customer. I know there is a tool called osql. we have the msde database using windows auth. where i run into a problem is when i put this on someones machine and try to run osql it asks me for a password. it does not ask for a user name and you can't type anything in for the password so i hit enter and then it tells me that it is invalid so how do i go about fixing this problem?
|
|
|
|
|
Why do things the hard way? Why not just create a database with an empty structure, do a sp_detachdb and ship the MDF file as part of your install... Then, write a program that does a sp_attachsinglefiledb and your DONE.
onwards and upwards...
|
|
|
|
|
i have tried that but cant quite get the syntax right could you provide a little more help and by the way thanks
|
|
|
|
|
I assume that you looked up sp_detach_db in the Books Online, so....
Assuming your db name is MyDB:
exec sp_detach_db 'MyDB'
then, simply include the MyDB.mdf file on your install and copy it to your install dir (assumes c:\MyApp\Data)
after the install, call a program that connects to MSDE and issues the following command:
exec sp_attach_single_file_db 'MyDB', 'c:\MyApp\Data\MyDB.mdf'
then you are DONE.
Just make sure that MSDE is installed with the same charset and sort order as you are using in-house.
onwards and upwards...
|
|
|
|
|
|
Look at the help: osql -?
Examples:
osql -Smyserver -Umyusername -Pmypassword for supplying username and password
or
osql -E to use a trusted connection.
Then you can also specify the scriptfile to be executed by adding the -iscriptfile parameter.
These options are case-sensitive.
|
|
|
|
|
With ODBC, I can't write :
'CREATE TABLE table1(field1,field2);CREATE TABLE table2(field1,field2);'
because ODBC doesn't accept the ';'. So I have to execute the first statement and then the second.
Therefore, I'd want to know if there is a possibility to execute a multi-statement line command with ODBC : Is there another separator ?
In fact, I write program that can communicate with several database servers.
I've got a file with all queries I need:
[MySQL]
QUERY1:SELECT * FROM table1
QUERY2:INSERT INTO table1( ....)
.....
[MSSQL]
QUERY1: .....
QUERY2: .....
.....
[ORACLE]
QUERY1: .....
QUERY2: .....
.....
The problem is that I can create a table with 'auto-increment' field with only one SQL statement on MySQL and SqlServer but on Oracle, it needs 3 statements to do it ... and I want to have only one line of SQL code.
If you've got a solution to my problem, please help me !
jpeg
|
|
|
|
|
Yes, you can do that for DBMS's that support query batches. MSSQL does query batches, just leave out the semicolon.
onwards and upwards...
|
|
|
|
|
I read the article Code Project/C# Programming - ADO Data Access through COM Interop in .NET by Kannan Kalyanaraman and it is great.
The only thing that I have not got is the open method of the connection object when I use the Interop Component in C#. What I am coding is:
cnnCustomer.Open(strConn, "", "", (int)ConnectModeEnum.adModeUnknown);
What is wierd is that in VB.Net it works fine. I am missing something here?
|
|
|
|
|
Okay..heres what my goal is. I have 2 tables with a parent-child relationship in an Oracle DB. I want the XML file to look like this. (I want to use XSLT to format the data).
<parents>
<parent>
<col1>
<col2>
<children>
<child1>
<att1>AAA
<child1>
<att1>BBB
...
Is there a way to create this type of XML easily using the DataSet. Here's what I tried. I tried adding both tables to a dataset, and then adding the relationship. Then I called the WriteToXml method of the dataset but it did not keep that format. I also have a XSD file for my dataset that I created with that format but no luck.
Any ideas??
|
|
|
|
|
i use ADO in MFC to access my database using SQL
my problem is that i can't add a variable using INSERT INTO
like,
---------
char mon[50]
pRs->Open("INSERT INTO moshtarayat1(elsanf) VALUES(mon)",m_pConn.GetInterfacePt(),adOpenForwardOnly,adLockOptimistic,adCmdText);
-------------
the problem here is that the INSERT INTO statment inserting the varibale name not it contents !
is there anyway to solve this problem ?
|
|
|
|
|
SomeOne_SurVive wrote:
Open("INSERT INTO moshtarayat1(elsanf) VALUES(mon)",m_pConn.GetInterfacePt
Are you using a stored procedure to add this value?
Or, are you using a form field within an ASP.NET, or ASP page?
How are you getting the value of this variable?
If using a stored proc, the variable should be declared as @mon.
Please post more details !!!!
HTH
Tony
|
|
|
|
|
after alot of tries, i got an clue as below :
CString mon;
lCommand.Format("INSERT INTO moshtarayat1(dates) VALUES('%s')",(CString)mon);
pRs.CreateInstance(__uuidof(Recordset));
pRs->Open((variant_t)lCommand,m_pConn.GetInterfacePtr(),adOpenForwardOnly,adLockOptimistic,adCmdText);
pRs->Update();
----------------------------------
i didn't try ur way yet, but i'll.
thanks for response.
|
|
|
|
|
Sure it will...
If I read this C++ code right, you're sending the following string to the DB:
INSERT INTO moshtarayat1(elsanf) VALUES(mon)
Aren't you trying to send the contents of the mon variable into the DB?
Copy the value of mon into the SQL string, so that it would look like:
INSERT INTO moshtarayat1(elsanf) VALUES(value_of_mon)
where value_of_mon represents the value of
mon<code>.<br />
<br />
(I'm not sure how you combine strings in C++, so no actual sample code...)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
