|
Don't take the job - the question does not make sense as stated.
However
Alter and Create modify the the database the others modify the the data is the closest you will get.
|
|
|
|
|
Hi Guys,
Havent been working much on triggers and well have to get one to work now, so need some help to get the holy grail.
I have a table called username and when ever a record have been changed I need to update the last update time in column lastvisittime. The problem is that with this trigger below it updates every record in the table and not just the single one that is being updated.
CREATE TRIGGER [TimeUpdate] ON [dbo].[username] <br />
FOR UPDATE<br />
AS<br />
UPDATE username set lastvisittime = dbo.TINT(getdate())
Generally I need to know how do I find out what row was currently supposed to be updated? Isteadof updating the whole table as it does now.
Have been trying good old uncle google but didnt find any clue to it, so hope the experts here can help 
N.b. dbo.TINT(getdate()) is just a function that changes the datetime to an int value.
Cheers,
Ronni
modified on Saturday, April 5, 2008 6:57 AM
|
|
|
|
|
Okay found out myself.
CREATE TRIGGER [TimeUpdate] ON [dbo].[username]
FOR UPDATE
AS
DECLARE @ID int
Select @ID = id from inserted
UPDATE username set lastvisittime = dbo.TINT(getdate()) where id = @ID
|
|
|
|
|
I design Data base in access 2007 and move it to Sqlexpress 2005 Database.
I can see the tables and all in sqlserver management studio.
But when I connect to the sqlexpress from Vs 2005 I do not see any stuff from access.
I am not sure what is going on here.
Both sqlexpress and Vs are on the same machine.
Cheers.
Tapas Shome
System Software Engineer
Keen Computer Solutions
1408 Erin Street
Winnipeg, Manitoba
Canada R3E 2S8
http://www.keencomputer.com
|
|
|
|
|
how you connect on vs.net?
|
|
|
|
|
Hi all, I'm having a problem with updating a row in the table when the value was originally NULL. Using c# and Visual Studio 2005 by the way.
Through my front end I can modify existing values and commit those changes to the database just fine, but the problem arises when I try and change a field that was NULL to a non-NULL value.
(Oh, I can also change a field that has a value to NULL just fine)
When I get to my DbAdapter.Update(DATASET HERE); line I catch an exception with the following text:
"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
And the thing is, when I comment out (of the update stored procedure in SQL) the part of the WHERE clause that states ((@IsNull_FIELDNAME = 1 AND [FIELDNAME] IS NULL) OR ([FIELDNAME] = @Original_FIELDNAME))where FIELDNAME is the one field that I'm trying to update, it works fine.
I can't figure out why that WHERE clause is failing. I'm not updating the database anywhere else in the code other than the one spot that is failing out when I try and fill a previously NULL field. I've used VS2005's auto-generating wizard to create my stored procedures and command generators so there isn't any disagreement between the table/SP/c# that I can find.
When I breakpoint at the DbAdapter.Update(DATASET HERE); line my dataset has the right values in it.
Does anyone have any ideas? I'm pretty stuck here.
Thanks
|
|
|
|
|
you can using isnull fungtion to check on null
isnull([FIELDNAME], @Original_FIELDNAME,FIELDNAME)
|
|
|
|
|
Hello!
I will like to know following in context of SQL Server 2005:
1. Is 1 SP executed as 1 transaction?
2. In case of BEGIN TRAN/TRY/CATCH blocks, what happens if the following scenario occurs:
create proc...
declare x....
BEGIN TRAN
BEGIN TRY
....
COMMIT TRAN
END TRY
BEGIN CATCH
....
ROLLBACK TRAN
END CATCH
....
<some exception="" occurs="" here="">
....
end
will the SP be rolled back till start? or just the part after END CATCH? or just the statement causing exception?
Tried playing with SP, but seriously confused myself!!!
If anyone could provide useful link related to the problem, it will be great! Tried googling but could'nt find anything useful. 
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
It depends. If your procedure is called from w/in an existing transaction then commit/rollback will affect both the inner and outer transactions.
You could use @@TRANCOUNT to determine wither or not you need to rollback or commit.
<br />
BEGIN TRY<br />
<br />
IF @@TRANCOUNT = 1<br />
COMMIT TRANSACTION<br />
END TRY<br />
BEGIN CATCH<br />
IF @@TRANCOUNT > 0<br />
ROLLBACK TRANSACTION<br />
<br />
END CATCH<br />
<br />
This way if your stored proc is the outer transaction it will commit on no errors. Then if there is an error in a nested transaction that was rolled back you won't get an error trying to rollback.
Search google for: sql server nested transactions
You should get plenty of results explaining this topic.
|
|
|
|
|
Just a simple question: Will SP be rolledback if any of its statements fails?
Example:
create proc xyz
insert....
update....
insert....
update....
end
exec xyz
Will top 3 statements be rolled back if last update fails (no begin/end tran or try/catch involved)?
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
No, outside of a transaction each statement is considered its own transaction. If the last statement fails all the others before it will still be persisted.
|
|
|
|
|
I am an SQL newbee & not understanding the results I am getting from a seemingly simple query. The query itself is returning the correct result but taking far too long. So I ran Explain to see what was happening. Sure enough more data is being examined than expected. But I can't figure out why. I have tried reordering the joins, but get the same result. Perhaps someone could educate me why it is returning so much data.
Note: All ID's are integer types. The ID of the respective tables are the primary key and the ID's used in the Assemdata table are indexed. MySQL ver 5.x
select
assemdata.AssemNum
innerpack.Descrip
hanger.Descrip
from
hanger join innerpack join assemdata
where
assemdata.Assemnum >=60000 and assemdata.Assemnum <=60050
and assemdata.InnerPackTypeID = innerpack.InnerPackID
and assemdata.hangerID = hanger.HangerID
Explain Result:
ID select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE innerpack ALL PRIMARY (Null) (Null) (Null) 3 Using where
1 SIMPLE assemdata ALL (Null) (Null) (Null) (Null) 2798 Using where
1 SIMPLE hanger ALL PRIMARY (Null) (Null) (Null) 2 Using where
Query Result:
AssemNum Descrip Descrip
60001 None Sawtooth
60002 None Sawtooth
60003 None Sawtooth
...... 49 Rows of data.
Thanks for any suggestions!
|
|
|
|
|
Shouldn't you be inner joining on condition?
|
|
|
|
|
I have tried specifying the joins as "inner" - it made no difference.
(My understanding is that MySQL interperts a join as "inner" unless otherwise specified.)
I have also tried being specific on the join member i.e. join on hangerID.
Also made no difference.
Thanks!
|
|
|
|
|
Have you tried selecting from the smallest table first?
|
|
|
|
|
Yes - I have tried all combinations I could think of. The Explain output is identical no matter what order I use.
Frustrating!
Thanks
|
|
|
|
|
Hello Mr. Member 4723455,
I always think it's better to put relational condition in "ON" clause of JOIN.
You can try:
select assemdata.AssemNum,innerpack.Descrip,hanger.Descrip
from hanger
join assemdata on assemdata.hangerID = hanger.HangerID
join innerpack on assemdata.InnerPackTypeID = innerpack.InnerPackID
where assemdata.Assemnum >=60000 and assemdata.Assemnum <=60050
Bye, (sorry for my bad English) 
|
|
|
|
|
Suppaman,
I have tried it both ways i.e. using "join on table1.id=table2.id" & using "where table1.id=table2.id".
The results from an Explain Select are identical. But I agree the on method is easier to read.
Thanks!
|
|
|
|
|
You can try using views:
first create a view to filter out elements of assemdata with Assemnum >=60000 and Assemnum <=60050 (50 elems) then join this view with the other two table. In this way the query plan should be different and the query execution faster (I think).
Bye ^__^
|
|
|
|
|
I am trying to run windows application against SQL Server 2000 and get error message dot net 2005 is developement environment
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)
Both app and SQL server are local
connection string is
""Data Source={m};Initial Catalog=dbname;Integrated Security = SSPI; Trusted_Connection=Yes;"
I have tried enabling TCPIP protocoll on 2005 server but it does not help.
Please help
Thanks
Manish
|
|
|
|
|
hrrty wrote: I am trying to run windows application against SQL Server 2000
hrrty wrote: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005
The error message says it all, your connection appears to think it should be connecting to SQL Server 2005.
You always pass failure on the way to success.
|
|
|
|
|
I have a column of telephone numbers that I am trying to get the most dialled number from. Can anyone help?
|
|
|
|
|
SELECT COUNT(*), TelephoneNumber
From MyTable
GROUP BY TelephoneNumber
You might want to put in an ORDER BY there too. If you are only interested in the highest value then:
SELECT TOP 1 COUNT(*), TelephoneNumber
From MyTable
GROUP BY TelephoneNumber
ORDER BY COUNT(*) DESC
|
|
|
|
|
What is the difference between Difference between Correlated and Simple Subqueries
|
|
|
|
|
.NET- India wrote: What is the difference between Difference between Correlated and Simple Subqueries
Have you not heard of a search engine[^]? I typed your question into Google and got plenty of results.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
