|
Hi Guys,
I have a three process - database scenario
- The first process inserts data continuously on a 24 hr (TMPDATA). basis, for every 1 second my program insert 6-10 records. It is very fast I'm using a socket connection to get the data.
- 2nd process is to transfer from TMPDATA to ORIGDATA every 5 mins. But Here I do the Locking Isolation level SERIALIZABLE so that the first process will not interfer with this process becuase after the transfer I truncate the TMPDATA table so as to free some space. (24 hrs. continuous)
DTS PROCESS:
begin transaction<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
insert into TMPDATA select * from ORIGDATA<br />
truncate table rop_tmp<br />
SET TRANSACTION ISOLATION LEVEL READ COMMITTED<br />
commit transaction
- 3rd process when I get an Error of "Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction". My program downloads data from ORIGDATA using certain criterias but it failed becuase of this locking. Run only during 12 midnight.
Question: Can you please suggest any isolation level that stops the INSERT for process 1 and allow my process 3 to download data into a file. Please I really need the solution, any idea is welcome. I will try also to look for solution.
/Dabuskol
|
|
|
|
|
Not that this will solve your deadlock but,
I believe you have to set your isolation levels prior to starting the transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
insert into TMPDATA select * from ORIGDATA
truncate table rop_tmp
commit transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
|
|
|
|
|
Is there a way to detect if a specific DB can support @@IDENTITY ?
Problem is, its possible to change my connection string depending on the database i want to connect to, as a client choice. But not all DB systems make use of @@IDENTITY. My tests here are with MSAccess and MSDE and both support it.
I'm using ADO from C++ in VC 2003.
Thx for any replies, even if negative.
|
|
|
|
|
Although I must admit I don't have first-hand experience with anything but Access or MSDE, you could simply create a test function where you execute an insert and catch any exceptions your SELECT @@IDENTITY throws.
If there's no exception thrown (and you receive a meaningful result) then @@IDENTITY should work later on as well...
Or:
Do not rely on a DB supplying such an information in a certain way but create a stored procedure to perform your inserts and let the procedure return the ID of the newly created element. That way you might be more flexible than hard-coding it in your program.
Regards,
mav
|
|
|
|
|
Assuming you have field like:
intmotyid int Foreign Key
Dttrade date()
Price float
Calculate Sum of price like
Intmotyid |weekly |MTD|Yearly
1 | 10 | 38 | 80
2 | 8 | 19 | 34
3 |32 |72 |73
Group by intmotyid
I need SQL Query.
placement
|
|
|
|
|
Hello 
Would be really grateful if someone can help me out. I'm no pro in SQL, but currently I have to build a search function. I'm using ASP and Access 2000 database.
Currently the search i've managed to built can search thru all the tables in the database. Unfortunately the search is not flexible enough.
Here is an example of one of my tables.
--------------------------------------
Product | Model |
------------|-------------------------|
BenQ | FP767 |
Samsung | SyncMaster LCD 192T |
Samsung | SyncMaster LCD 172B |
LG | L1710s |
---------------------------------------
strSearch = Request.form("strsearch")
& "SELECT Product, Model " _
& "FROM LCDMonitor " _
& "WHERE Product LIKE '%" & strSearch & "%' " _
& "OR Model LIKE '%" & strSearch & "%' " _
& "ORDER BY Product;"
Here's the situation. If my search term is samsung then 2 rows will be returned. If I search for syncmaster, 2 rows will also be returned. But if i search for samsung syncmaster, no records are returned. I want it to be able to search for samsung syncmaster as well. And i want it to work even if i search for syncmaster 192T. Currently no records are returned if search for syncmaster 192T.
Do I need to use arrays? If so, how do i do that? Need help with coding.
Quite new to all this stuff, so need all the help I can get. Urgent. If need more information or wat i've posted is not clear, let me know. Thanks
|
|
|
|
|
you should try to parse the search string, I mean split the string to single words, and then try to build different possible sql statement, run all of them, and analyse the result set and pick one or more to the client.
|
|
|
|
|
Hi
Is there any chance you could help me with the coding on how to split the words then build sql statement for each word?
Your help will be very much appreciated.
|
|
|
|
|
Sorry if it's wrong forum but there isn't one for OLEDB... Anyway. Is OLEDB simultaneous-multithread-access safe? Also ... using OLEDB - if different threads need to access the same database is it better to open that database from each thread or is it OK to use the same opened database object from different threads at the same time - reuse the connection?
Sorry for such a basic question. 
*Reposted from ATL forum*
|
|
|
|
|
It depends on your provider. When you use OLE DB, you use some system objects to locate a provider, but after that you're talking directly to the provider object, a component supplied by the vendor.
If they've implemented it properly, if the provider is sensitive to threading issues it should be marked as Apartment model threading. If it isn't, it should be marked as Free or Both.
A connection can usually only process one command at a time, so if you want commands to run concurrently, you'll need to open another connection.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Ok. I need to use Access (assuming microsoft's provider), SQL server (assuming microsoft's provider) and Oracle (assuming oracle's provider). Do you know how good are they? I'm guessing Access is not as good as the two big ones, right? But the big ones should be fine with multithreading, aren't they?
|
|
|
|
|
Since SQL Servre is kinda expensive (5000 euro's per processor), and we don't want to obligate our customers to do that investment, even though SQL Server is, in my opinion, the only way to go.
One of my colleagues came across an Interbase offspring called FireBird, which is open-source and is based on the Interbase code released a long while back by Borland. It looks promising, but it's not there yet as far as I can see. It has a .NET connector already, so switching shouldn't be a big problem...
|
|
|
|
|
If it is mission critical I would be more inclined to go with something more proven.
I use sql Server and Mysql. Mysql is a really good open source database with a lot of documentation and users behind it - worth checking out. Were I to start a business today I would most likely go with Mysql over Sql Server.
B
|
|
|
|
|
Hie all, I have 3 tables - Assessment, Unit, Department.
How can I find :
The numbers of those students who, while attending one or more units run by the department named 'computing', are not attending every unit run by the department named 'computing'.
Hope to hear from you soon.
Thanks.
(ASSESSMENT - Table)
s_no u_no final-grade
S001 Q2 95
S002 Q1 61
S003 Q3 65
S004 Q4 53
S005 Q5 63
S006 Q1 78
S007 Q4 95
S001 Q1 45
S001 Q3 75
S001 Q5 98
S001 Q6 85
(UNIT - Table)
u_no d_no uname
Q1 D1 Programming
Q2 D2 French Language
Q3 D1 Hardware
Q4 D2 French Politics
Q5 D1 Logic
Q6 D1 Operating Systems
Q7 D3 Finance
Q8 D2 French Literature
(DEPARTMENT - Table)
d_no d_name hod(head of dept)
D1 Computing Steve
D2 Language Smith
D3 Business Anne
D4 Accountacy Joe
D5 Art Carol
|
|
|
|
|
The solution lies in Set logic (Math). But I don't see a students table? SO how would I know which student got which grade on which course?
Anyway, If you look at the wanted result set, it's the superset of students who attend AT LEAST one unit that's part of the computing department, minus the students that follow all of em. I.e. students who have a COUNT(attended units in computing) = COUNT(units in computing)
But to show the involved SQL, i'll need the students table and how it's 'connected' to the other tables
|
|
|
|
|
The student's grade is on the ASSESSMENT table, final-grade - column.
|
|
|
|
|
Sorry, didn't see the s_no column I'll see if I can scribble down some SQL
|
|
|
|
|
How about this:
declare @iCount int
select @iCount = count(1) from unit where d_no='D1'
select
s_no,
count(1) UnitCount
from
Assessment
where
u_no in (select u_no from unit where d_no='D1')
group by s_no
having count(1)<@iCount
I didn't test it...but it should be close....
|
|
|
|
|
So the output is 3 records.
How can I display as:
No. Students
------------
3
How's the SQL?
|
|
|
|
|
Hi, I want to know how can i add 2 CRecordset Derived class to my application.. I tried to 2 CRecordset class to my application, but when i make pointers to these classes. It gives me runtime error.. Anyone can help me with that??
I just want to open 2 CRecordset classes and each one point to only 1 table. So i can control them sepeartely... Thanks!
|
|
|
|
|
if you use VC++6,
you can try this way:
use appwizard to derive 2 classes, and use CRecordset as the base class.
and just follow the wizard.
good luck.
|
|
|
|
|
Hey guys, got a question for you all! I've got a DataSet/DataTable, created by using a DataAdapter.Fill call. I'm writing a C# class that handles all the ADO.NET work I need, and a different class will call the public methods of this class.
So; I've created the two public properties I need- Public String _custId and Public DateTime _orderDate. But, I can't get the values out of the DataSet/DataTable/DataColumn without doing (in my mind) useless casting! For instance, this doesn't work:
_custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0];
I get the message "Cannot implicitly convert type 'object' to 'string' ". To make it work, I have to do:
_custID = (String)dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0];
....or....
_custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0].ToString();
I'm confused as to why? The following line displays "System.String":
Console.WriteLine(dsOrders.Tables["tblOrd"].Rows[iCurrentRow].Table.Columns[0].DataType);
So, shouldn't that mean that I don't have to do anything else? Why do I need to use (String) or .ToString() ?
And I also can't get the DateTime into a variable. Trying
_orderDate = dsHousing.Tables["tblOrd"].Rows[iCurrentRow][1];
gives me "Cannot implicitly convert type 'object' to 'System.DateTime' ". And, again, if I display the value for Columns[1].DataType, it shows me "System.DateTime". Worse, there's no ".ToDateTime()" method of the Column object, so the only way I can get the DateTime field out is to cast it to a string, and then convert it back to a DateTime. Compared to my problem with the String column, I'm doing twice the un-necessary work just to get the DateTime column!
Is this really what's supposed to happen? If the DataType shows string, why do I need to perform an explicit conversion from object to string? It seems like I'm doing un-necessary work; if the value is stored as a certain datatype, I should be able to pull it out of the DataTable and put it into a variable of the same type.
I even toyed with the idea of .NET assuming I wanted the DataColumn being put into a String- but I can't find a property of DataColumn that just gives me the value. In classic ADO, there was a Field object, which had a Value property. I can understand "DataColumn" being an object, but is there a specific property for pulling the value out of DataColumn? For instance, if those code samples I just gave would be in classic ADO, I would be trying to say _CustID = rs.Fields(x).Value.
Oh I'm using VS 2003.NET EA, with WinXP, .NET 1.1, and have "Using System.Data" and "Using System.Data.OracleClient" at the top of my C# class definition. At first I thought this might be Oracle related, but DataSet/DataTable/DataColumn is part of the System.Data namespace.
Thanks!
-Thomas
|
|
|
|
|
Hello all:
I have an XML file I am parsing. I will need to have some sort of "signal" or event that will notify the databaseautomatically start the table insert process.
Thought A: I could pull data from the XML file and store this data in an ASP.NET data storage object. Some of the data is associated with look up tables, so I need to do some queries, therefore I am using the term processing to mean: getting data id's associated with the string values parsed from the XML file. So processed data are now id's (that are associated with the string values parsed from the XML file). After the data has been processed I want to insert the id's into the appropriate tables.
Thought B: Use some sort of DTS package or another method for the parsing and inserting of data.
Additionally: I will need to have some sort of "signal" or event that will notify the SQL Server that the file has been saved to the proper location and is ready for parsing. If I use Thought A then I can do the aforementioned programatically. I don't know how to do the aforementioned using Thought B. Any suggestions?
Here are my questions:
Thought A:
What type of ASP.NET data storage object would you use (a datatable, dataset etc.)?
How would you insert data in the appropriate database tables with ASP.NET (e.g. some type of bulk load or call a SPROC and load the dataset info)
Thought B:
Or would you skip the ASP.NET stuff, and use some sort of DTS package or another way of inserting the data? If so, any pointers on how would that would be done?
TYIA,
lonelobo
|
|
|
|
|
hello forum,
i want to store c# objects in a MS SQL database. I think the best way is to serialize the object to a byte array but i have problems to insert a c# byte array into the database...
First, the equivalent type of datum in MS SQL is the binary or varbinary type, isn´t it?
well, my code:
<br />
<br />
MyObject mo = new MyObject();<br />
MemoryStream ms= new MemoryStream();<br />
BinaryFormatter bf = new BinaryFormatter();<br />
b.Serialize(ms,mo);<br />
<br />
SqlConnection sc = new SqlConnection("server=localhost;uid=sa;pwd=;database=myDB");<br />
sc.Open();<br />
<br />
SqlDataAdapter sda = new SqlDataAdapter("select * from myTable",sc);<br />
DataSet ds = new DataSet();<br />
ad.Fill(ds,"tableFile");<br />
DataTable dt = ds.Tables["tableFile"];<br />
DataRow dr = dt.NewRow();<br />
<br />
dr["object"] = ms.GetBuffer();
dt.Rows.Add(dbRow);<br />
<br />
SqlCommandBuilder scb = new SqlCommandBuilder(sda);<br />
sda.Update(ds, "tableFile");<br />
<br />
The instruction:
dr["object"] = ms.GetBuffer();
throws the message exception "String or binary data would be truncated"
could you help me, please?
thanks in advance.
|
|
|
|
|
If the error says the string will be truncated, the size of the field you want to store it in, is to small.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
