|
I'm pretty sure it will, I've used the same technique in the past. A quick way to check, start a transaction, do the update and without a commit or rollback, see (on another connection) if you can do anything with the table. If the optimiser was smart enough to know not to do an update the table will not be locked.
My best guess (without seeing the code) is that the trigger is not working correctly - maybe expecting single records in the inserted/deleted tables?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
David Mujica wrote: I'm not 100% convinced that your UPDATE statement would do anything, the optimizer might be smart enough to see that no action is actually required
That would be a serious bug. Optimizer cannot make decisions which would compromise or change the logic.
If it would eliminate row updates based on the fact that no data is changed in the statement itself, it also would have to understand trigger logic. Also what if the table contains a timestamp column, foreign keys etc.
Typically optimizers ensure that the logic is not changed and if there is even a slight chance that the logic may be affected, optimizer revokes such permutations because of the uncertainty.
|
|
|
|
|
hi,
I am trying to create a custom installation package that won't take my end users very many steps.
Currently there are
-- Install .dot net (which I am not worried about because most of the machine have it already (and it runs with the setup.exe that I created from Visual Studio)
-- Install SQL Server Express with management studio Create the database by running the sql scripts within Management Studio and install the software itself. Though this process does work, I would like to make it a little easier. Our end users aren't exactly technically expert and will run into problems. Also with my limited experience of SQL Server, it is hard to rectify created instances, etc without a lot lof work that they won't understand.
my question is can we have a custom package, which consists of all the above and runs in single step.
modified on Monday, December 22, 2008 1:59 AM
|
|
|
|
|
Hi,
I'm fairly new to database programming. So, here is the problem I'm having.
Here is a basic description of the two tables I'm using. (The database is a MySQL RDBMS)
PersonTable
PersonId, INT, AUTO_INCREMENT (Primary Key)
Name, VARCHAR(32)
ImageTable
ImageId, INT, AUTO_INCREMENT (Primary Key)
ImageBlob, LARGE_BLOB
PersonId, INT (Foreign Key)
Basically, I want to insert the person record into the person table, next insert the image for that person into the image table, and finally link the ImageTable.PersonId to the PersonTable.PersonId.
So, here is the code I'm currently using
CRecordset personTable;
CRecordset imageTable;
personTable.Open(CRecordset::snapshot, "SELECT * FROM person");
imageTable.Open(CRecordset::snapshot, "SELECT * FROM image");
personTable.AddNew();
personTable.m_Name = "Joe";
personTable.Update();
personTable.Requery();
personTable.MoveLast();
long personId_ForeignKey = personTable.m_PersonId;
imageTable.AddNew();
imageTable.m_PersonId = personId_ForeignKey;
imageTable.Update();
The above code is not safe when clients concurrently submit records to the database. This is due to my faulty assumption that the last record is the one that the current client inserted, which may or may not be true depending on the interleaving of operations.
My question is this, is there a way to safely get the value of the primary key after inserting the record into the table, so that I can use it to populate the foreign key columns of the other table?
-Will
|
|
|
|
|
Have a look at LAST_INSERT_ID() function. That should give you the generated value for auto increment column. Also you don't need to requery the row anymore if you just get the value of PersonId.
|
|
|
|
|
Hi,
Probably this is not a question to ask in this forum . but I could not decide. Just putting things in abstract level. I have a primary function which insert some rows to DB0 tables and calls subFun1 and SubFUn2 who connection to different databases and insert some rows in tables in there.
Say I get error in subFun2 function I need to rollback everything what was inserted till that point from DB0 tables, DB1 tables and may from DB2 tables also. How to achive that here?
If exception occurs in subFun2, it will throw exception to primary function and only DB2 and DB0 transactions will be rolled back but not from the DB1 transactions. Any workaround for this
sub primaryfunction()
try
SqlConnection0 = Connection to DB0
SqlTransaction0
Insert/Update some table
call function SubFun1
call function subFun1
call function subFun2
SqlTransaction0.commit()
catch exception
SqlTransaction0.rollback()
end sub
sub SubFun1
try
SqlConnection1 = Connection to DB 1
SqlTransaction1
call function SubFun1
call function subFun1
call function subFun2
SqlTransaction1.commit()
catch exception
SqlTransaction1.rollback()
throw exception
end sub
sub SubFun2
try
SqlConnection2 = connection to DB2
SqlTransaction2
Insert/Update some table
SqlTransaction2.commit()
catch exception
SqlTransaction2.rollback()
throw exception
end sub
|
|
|
|
|
If I understood correctly, based on your description you need distributed transactions. You start a transaction, perform all operations on all databases and commit after that. MS DTC will take care of the transaction coordination.
In the semi-code you have commit at the end of SubFun1 which will result to commit point in DB1, and DB2 will have it's own transaction. Instead of using database scoped SqlTransaction, have a look at System.Transactions Integration with SQL Server[^].
Mika
|
|
|
|
|
i m working in VS2005, while creating database
locally no error but remotely it is showing the following error
Retrieving the COM class factory for component with CLSID
{10020200-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154
|
|
|
|
|
SQLDMo.dll will only exist (and be registered as a COM object) on a machine with SQL Server installed. Is SQL Server 2005 installed on the "remote" machine?
|
|
|
|
|
In remote machine SQL server is installed(where i want to create DB and DSN) but no SQL server in my server.
Exact working detail:
Local Machine RDP to Network Server ( Here i m running Installation Setup to create DB and DSN(This DB and DSN i want to create in other Server machine))
|
|
|
|
|
Sql server must be installed on the machine that you are running your program in order for you to be able to load and use SQLDMO. You will need to install it on the SQL Server machine, not just any arbitrary server.
|
|
|
|
|
Hello there,
I have developed an application in VB 2005 backend SQL 2005 Reporting Crystal report 9.0
well now the application is tested everything is done
Now in end simply i want to make a setup for an entire application so that it can be burn into cd's and and be installed on other computers..
Any one can help me with brief.???
Thanks
Kenny.
|
|
|
|
|
|
hi every one i want to develop a hotel reservation system. please can someone help, tell me all the requirements i am not very food in programming. i don't know where to start from. thank you. Steven
|
|
|
|
|
My idea:
You have to store information about the hotels:
name,
city,
country,
number of stars,
room prices,
total number of double rooms,
total number of single rooms,
number of available rooms,
etc
You have to store information about the customers:
name,
email,
telephone,
address,
username,
password,
etc
You have to store information about the reservations:
username of the customer who made the reservation,
Hotel ID,
date of arrival,
date of departure,
total cost,
etc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
1. Don't cross post
2. As you obviously don't have a clue, employ someone to do it for you - but check them out on CP first to see what questions they ask 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Table values import to excel Sheet Using only procedure Sql2005 for the purpose to run job please advise me ASAP
|
|
|
|
|
thangarajappa wrote: Table values import to excel Sheet Using only procedure Sql2005 for the purpose to run job please advise me ASAP
Perhaps you should take the time to post your problem with an understandable description.
|
|
|
|
|
uid class section doj status
75 L.K.G A 2008-12-09 PRESENT
81 L.K.G A 2008-12-09 ABSENT
75 L.K.G A 2008-12-10 PRESENT
81 L.K.G A 2008-12-09 PRESENT
82 L.K.G A 2008-12-09 PRESENT
84 L.K.G A 2008-12-09 PRESENT
57 L.K.G A 2008-12-09 PRESENT
58 L.K.G A 2008-12-09 PRESENT
64 L.K.G A 2008-12-09 PRESENT
65 L.K.G A 2008-12-09 PRESENT
66 L.K.G A 2008-12-09 PRESENT
67 L.K.G A 2008-12-09 PRESENT
69 L.K.G A 2008-12-09 PRESENT
68 L.K.G A 2008-12-09 PRESENT
70 L.K.G A 2008-12-09 PRESENT
71 L.K.G A 2008-12-09 PRESENT
72 L.K.G A 2008-12-09 PRESENT
73 L.K.G A 2008-12-09 PRESENT
74 L.K.G A 2008-12-09 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 ABSENT
select top 5 * from TBL_SATTENDANCE_lkg where uid not in (select top 7 uid from TBL_SATTENDANCE_lkg)
58 L.K.G A 2008-12-09 PRESENT
64 L.K.G A 2008-12-09 PRESENT
65 L.K.G A 2008-12-09 PRESENT
66 L.K.G A 2008-12-09 PRESENT
67 L.K.G A 2008-12-09 PRESENT
*************************************************************
75 L.K.G A 2008-12-09 PRESENT
81 L.K.G A 2008-12-09 ABSENT
75 L.K.G A 2008-12-10 PRESENT
81 L.K.G A 2008-12-09 PRESENT
82 L.K.G A 2008-12-09 PRESENT
84 L.K.G A 2008-12-09 PRESENT
57 L.K.G A 2008-12-09 PRESENT
58 L.K.G A 2008-12-09 PRESENT
64 L.K.G A 2008-12-09 PRESENT
65 L.K.G A 2008-12-09 PRESENT
66 L.K.G A 2008-12-09 PRESENT
67 L.K.G A 2008-12-09 PRESENT
69 L.K.G A 2008-12-09 PRESENT
68 L.K.G A 2008-12-09 PRESENT
70 L.K.G A 2008-12-09 PRESENT
71 L.K.G A 2008-12-09 PRESENT
72 L.K.G A 2008-12-09 PRESENT
73 L.K.G A 2008-12-09 PRESENT
74 L.K.G A 2008-12-09 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 ABSENT
select top 5 * from TBL_SATTENDANCE_lkg where uid not in (select top 2 uid from TBL_SATTENDANCE_lkg where uid = 81)
75 L.K.G A 2008-12-09 PRESENT
75 L.K.G A 2008-12-10 PRESENT
82 L.K.G A 2008-12-09 PRESENT
84 L.K.G A 2008-12-09 PRESENT
57 L.K.G A 2008-12-09 PRESENT
***********************************************
I NEED THIS RESULT(i.e) i want result except top 3 record with uid 81 (other records with uid 81)
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 PRESENT
81 L.K.G A 2008-12-12 ABSENT
*****************************************************
select top 3 * from TBL_SATTENDANCE_lkg where uid=81 not in (select top 3 uid from TBL_SATTENDANCE_lkg where uid = 81)
i try this but it won,t work...
how can get this result...
|
|
|
|
|
You was near to result wich you want to get.
I have modify your query and it shows right result.
select top 3 * from TBL_SATTENDANCE_lkg where uid=81 or <br />
uid not in (select top 3 uid from TBL_SATTENDANCE_lkg where uid = 81)
Hope it will helps you...
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
what is the command on the MYSQL promt to invoke editor to write Stored procedure in MYSQL
|
|
|
|
|
ptr_Electron wrote: what is the command on the MYSQL promt to invoke editor to write Stored procedure in MYSQL
If you mean MySql COmmand Line client, I'm not aware of such command. However you can write the procedure for example in the notepad and the paste it to the prompt or use source option to run a file as a script.
|
|
|
|
|
Just want to get some feedback on an execution plan
if 86% of the cost of a query is a clustered table scan I assume there is little or no options for optimising the query. On a 10 minute query this is the highest single cost.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think it depends on the surrounding operations and how many rows are you expecting from that operation. For example if just a few rows are fetched using clustered index scan and they do not reside near each other, it would be more efficient to fetch them using the tree vertically.
However, it's hard to say if the plan can be better without seeing the whole plan and the sql statement (well actually also info about the indices and row amounts). Do you have any possibility to post the plan and the sql?
|
|
|
|
|
Thanks Mika - I was hoping for a quick confirmation but I found some ways to improve the performance.
This is actually 1 of 9 queries in the procedure and it takes 46% of the total, 2 queries take over 90% of the cost and each has a clustered index scan cost over 80%
The query retuens 3.4m rows from a base table of 16m with lots of linkages. I went through and turned all the joins into inner joins and did lots of preparation and reduced the query from 34 min to 10 min so I'll have to settle for that for now.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
