|
Hi,
Environment - VB.NET, ASP.NET, SQL Server 2000.
In SQL 2000, I am sending an XML, which carries data for two tables. Let's say, I am inserting half of the fields in TABLE1 and rest in TABLE2.
Specifically, I want to use Transaction Processing for inserting the rows in both tables. If insertion in one table fails, any inserted data should ROLLBACK and come out of procedure with relevant error code.
Please advice or send me any example links. Thanks
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
In the query, assuming use of SQLXML facilities:
BEGIN TRAN
INSERT INTO Table1
SELECT
FROM OPENXML( )
WITH ( )
IF @@ERROR <> 0 THEN
ROLLBACK TRAN
ELSE
INSERT INTO Table2
SELECT
FROM OPENXML( )
WITH ( )
IF @@ERROR <> 0 THEN
ROLLBACK TRAN
ELSE
COMMIT TRAN Basic rule: if you want transactional behaviour, use a TRANSACTION. If your code is implemented as a number of SqlCommands, it might look more like:
Dim connection As SqlConnection
connection = New SqlConnection( "connectionstring" )
Dim trans As SqlTransaction
Dim cmd1 As SqlCommand
Dim cmd2 As SqlCommand
' Set CommandText for cmd1, cmd2
Try
trans = connection.BeginTransaction()
cmd1.Connection = connection
cmd1.Transaction = trans
cmd2.Connection = connection
cmd2.Transaction = trans
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
trans.Commit()
Catch ex As SqlException
trans.Rollback()
End Try
|
|
|
|
|
Thanks for the response.
I am inserting data in two seperate tables by getting data from one XML. So, I want to make sure data either goes in both tables or none. For that I think Transactions are best choice as you explained in Ist example. Right?
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Hi All!
Can anyone help me with this? It's driving me crazy!
I've reduced a problem application down to the following lines of code.
void TestFunc()
{
::CoInitialize(NULL);
char strTEST[1024];
_ConnectionPtr spConn(__uuidof(Connection));
spConn->Open( "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=TEST_DP;Data Source=(local)", OLESTR(""), OLESTR(""), -1 );
for ( int i = 0; i < 50000; i++ ){
strcpy( strTEST, "SELECT COUNT(*) FROM KGTEST WHERE COL1 = '" );
itoa( i, &strTEST[strlen(strTEST)], 10 );
strcat( strTEST, "'");
_RecordsetPtr spRS(__uuidof(Recordset));
spRS->CursorLocation = adUseClient;
spRS->Open( strTEST, spConn.GetInterfacePtr(), adOpenForwardOnly, adLockBatchOptimistic, -1);
spRS->Close();
spRS = NULL;
}
spConn->Close();
spConn = NULL;
::CoUninitialize();
}
This leaks memory like no tommorrow. It appears to be related to a known Microsoft problem, (Q312575) and the way that memory is cached in MDAC. However this was reported as fixed and does not occur with MDAC 2.7 (According to Microsoft, and this is running with 2.8, and I've re-installed just in case!)
The critical bit seems to be the SQL statement, if it is a constant (For example: SELECT COUNT(*) FROM KGTEST WHERE COL1 = '1' ) - everything is OK, however if the statement is constantly changing then the memory just leaks!
Can anyone shed any light on this?
|
|
|
|
|
I can't reproduce it on this system (Win2k SP4, SQL Server 2000 SP3, MDAC 2.7 SP1).
You should be able to hoist creation of the Recordset outside the loop.
As a matter of style, I would recommend using a Command object with a Parameters collection rather than concatenating strings.
If you still think it's leaking, take it up with MS.
|
|
|
|
|
Thanks for checking that, I'm using WinXP SP1 (+ Hot Fixes), SQL Server 2000 SP3, MDAC 2.8 - So it could be MDAC, I'm going to try and recreate your configuration on a seperate machine to try and proove it!
Moving the Recordset outside the loop does not solve it, but moving the connection inside nearly does (but obviously slows things down dramatically).
The concatentating came about because I was slowly reducing my program to the simpliest that it could be while still demonstrating the problem (I even removed changed from CString to char[] in case they were leaking!)
Regards,
Kevin.
|
|
|
|
|
Kevin,
Please! If you find an answer to this forward it to me.
Last time I was looking into this it wasn't listed as a
known problem. If you move the recordset creation outside
of the loop, as suggested by Mike Dimmick:
_RecordsetPtr spRS(__uuidof(Recordset));
spRS->CursorLocation = adUseClient;
for(...) {
spRS->Open(...);
spRS->Close();
}
spRS = NULL;
Does the situation change any?
Thanx for any info...
>>>-----> MikeO
|
|
|
|
|
Hi Mike,
I've spent hours looking at this and can get lots of differnet results.
The best solution (although no pratical for performance reasons) is to move the connection and Recordset inside the loop, this seems to leak less!
My understanding of MDAC is that it buffers memory allocations and the default setting is 500 queries * 1Mb. This is why running the test program the memory allocated seemed to drop rapidly to begin with then level off (the problem is I never seem to get it all back!). With the connection and Recordset inside the loop a much smaller leak is seen. The trick is the changing WHERE clause, if this is static then no problem (presumably because no malloc/free's are occuring)
According to a Microsoft artical you can adjust the 500 cached queries settings and the amount of memory allocated, I've tried setting these but cannot get any different results.
I let you know if I discover anything further.
Regards,
Kevin.
|
|
|
|
|
What is db scattered file read?? how do i rectify it??
venkat
Cheers,
Venkatraman Kalyanam
Bangalore - India
"Being Excellent is not a skill, it is an attitude"
Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
|
|
|
|
|
Hi all,
Is there any way to write an SQL SELECT statement that will tell me whether a column is NULL or not, without having to download the column's content?
I'm working on a content-management system that stores HTML pages as text records in a MS SQL Server table. Some pages have a 'default' page which is displayed by setting the text column to NULL. For the web interface I'd like a way to summarize the state of the content (e.g. whether it has been supplied or not), but it seems ridiculous to download the entire HTML content of a section just to see if it's NULL or not.
Oh, and I can't use Stored Procedures for reasons I won't go into 
TIA,
Pete
|
|
|
|
|
Well, you could always do something like this:
SELECT id
FROM tblContent
WHERE ...
AND Content IS NULL
Modify it as you want to, the most important thing is the condition "column IS NULL" can be used to find out whether a column is null or not without having to download it.
Hope it helps,
Edbert
|
|
|
|
|
Hi Edbert, thanks for the reply.
I'd thought of that, but I'm not sure how well it works if I'm displaying info about /every/ row in the table. I'd have to do a SELECT for each row. Plus, each row has about 5 different text fields (each 'page' of content has 5 'sub-pages'). So that would mean 5*num_rows SELECTs!
I guess a similar approach would be to initially do the SELECT you suggest (for each of the 5 content columns), and then programmatically compare each id to the results. Hmmm, maybe that's what you meant in the first place.
It's annoying that there doesn't seem to be a 'cleaner' way to do it, but then I guess SQL isn't really the most powerful thing for this kind of problem...
Thanks for you help,
Pete
|
|
|
|
|
If you post the database structure and how you want your query to be I might be able to help you build the query.
|
|
|
|
|
You could use ISNULL( column, valueIfNull ) although you will still end up with the data if the column is not null. Your best bet would be to use a case e.g. CASE WHEN col IS NULL THEN 1 ELSE 0 END AS IsColNull. Please note this is not checked for syntax.
|
|
|
|
|
Hi guys,
I ended up re-normalising the database schema, a side-effect of which was that my NULL-checking problem is no longer an issue. Which is nice. I just wanted to say thanks for your help.
Cheers,
Pete
|
|
|
|
|
hii all...
i'm facing a problem ...
i wanna insert a new name into my table (like ADAM SMITH)
but if there is an existing (ADAM SMITH) ...with two balnks it will produce a duplication in the table..(it's the same ADAM SMITH but one time with one balnk and one time with two balnks...so all a wanna do is to make the SQL treat (ADAM SMITH) like(ADAM SMITH)like(ADAM SMITH)
to prevent the duplication
how can i do it by SQL or by access or by SQL server...??
thx in advance
|
|
|
|
|
As far as I know, there is no SQL function to trim the spaces in between words.
Why don't you separate the name field into FirstName and LastName? I usually separate them into FName,MName and LName.
It's easier to use that way.
If you can't modify the table, then you have to write your own function to separate and trim the name you want to insert
(i.e. 'ADAM' and 'SMITH') and produce an SQL like this to check whether it exists:
SELECT COUNT(*)
FROM tblName
WHERE name LIKE('ADAM%SMITH')
but this may not be 100% correct, as names like 'ADAM SAMUEL SMITH' will be counted too.
You may also want to create a function to trim the names and put them back together before inserting them to the database, which is safer.
|
|
|
|
|
I have a dataset displayed in a particular view with some sorting. For some specific reasons I want to create a new dataset from this dataview so that the new dataset will have it's rows in actual physical sorted sequence just as in the view. How do i do it?
One solution will be to iterate through the view's rows and create a new dataset of your own in the same sequence within a foreach loop. But I feel it's not that 'graceful'.
Rakesh
|
|
|
|
|
Hi,
I want to get a boolean variable from a stored procedure into .aspx page. Can you send me a piece of code for this?(C# will be better)
Best wishes,
-
When in doubt, push a pawn!
-
|
|
|
|
|
You can write the code yourself but here is the idea.
Create a SqlConnection.
Create a SqlCommand, assing the connection to it and give it a command-text equal to your SPs name.
Set the CommandType of the command to StoredProcedure.
Add an output SqlParameter to the command, make it's name equal to the output param in your SP.
Execute the command
The parameter will then be assigned the value.
Use Convert.ToBoolean to get the value.
regards,
Paul Watson
Bluegrass
South Africa
Miszou wrote:
I have read the entire internet. on how boring his day was.
Crikey! ain't life grand?
|
|
|
|
|
I run an application in my computer with the sqlserve installed in my computer too.
But when my code goes to :
SqlConnection conn = new SqlConnection("...");
conn.Open();
It's very strange that sometime it took so long to open the connection, about 4 seconds. But sometime it's fast, it took no more than 1s to finish.
I tried it many times. All the tests are done after restarting the computer, so the environment should be very clean.
Any one know why? Please kindly tell me.
Thanks in advance!
|
|
|
|
|
Post your connection string (hash the server ip, password and username first) so we can see if there is a problem in there.
Otherwise where is this SQL server you are connecting to in relation to the box running the code?
regards,
Paul Watson
Bluegrass
South Africa
Miszou wrote:
I have read the entire internet. on how boring his day was.
Crikey! ain't life grand?
|
|
|
|
|
How is your computer resolving the location of the server? If your connection string uses the server name, it must be resolved to an IP address, which might take a bit of time if you are depending on netbios (windows workgroup) to get the job done. Will be worse if you have multiple newtork adapters...
Iry putting the servers IP info in LMHOSTS.
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
The first time a connection is made to the server, it has to perform authentication, set up various parameters, and perform other initialisation.
When using connection pooling, a 'closed' connection (from the application's perspective) is not in fact closed. It's kept in the pool waiting to be reused. When you 'open' a connection and there's at least one spare connection in the pool, the provider simply resets the connection ("EXEC sp_reset_connection" in SQL Profiler trace output) and hands it out. This explains the sub-second connection time.
When there aren't any connections, but the pool can hold more (maximum not yet reached), it will have to do a proper connection in the same way as the first one. This will take about the same amount of time as the first did.
|
|
|
|
|
How do I do this?
I need to receive the data this way:
12 10
I fI have the following structure.
select sum(total)as total1
from table1
12
select sum(total)as total2
from table1....
10
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
