hi my dear,
I tried tried to restore the data base from enteprize manager but the permission error apeared.the manager of the host said me you have permission but yuo should restore tnhe database by using restore command from sql query analyser ?is him advice is true?if yes how can i do this,what command should i use?in the below x is the databse that i want to restore and a is the new name ?in query analyser what database must i select in change database?
restore database a from f:\x.mdb
I believe that what is meant is that you should not:
1. issue "set implicit_transactions on" at any point in your code (sql statements) as this turns on autocommit and defeats the possibility of rollback
2. Use transactions inside stored procedures or SQL statements, since commits inside the sproc/SQL will break rollback of the containing (external .NET) transactions.
Basically you have three choices:
1. do all transactions with the API.
2. Use autocommit and forget transactions (bad idea most of the time).
3. do transactions in stored procedures/SQL and not the API.
any mixing of these will be broken in strange ways.
this quote from books online is also pertinent:
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
[Edit] modified to clarify that Transactions inside SQL statements should not be mixed either Absolute faith corrupts as absolutely as absolute power
Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke
There are three reasons only to use stored procs.
#1 Security. You can grant users the ability to update records without having permissions to the table. It prevents users from connecting to your database from say access to update the tables.
#2 Prevents SQL Injection attacks.
#3 Performance is improved because the queries' execution plans are cached on the server.
If you have to write out your sql commands because you are dynamically generating sql statements then so be it, but that sounds hairy to me.
"People who never make mistakes, never do anything."
ToddHileHoffer wrote: #1 Security. You can grant users the ability to update records without having permissions to the table. It prevents users from connecting to your database from say access to update the tables.
In our system, the user doesn't have direct access to the database. All activity between the client and the server (not the DB server, but the application server) is secured, and only the application server on the remote machine has access to the DB.
I am having a hard time using a correlated subquery. I have a table
id - int (identity)
ForwardDate - datetime
EffectiveDate - datetime
Price - float
I am trying to return the record for each ForwardDate where the EffectiveDate is equal to the max(EffectiveDate) for that particular ForwardDate. (There are many different ForwardDate's, each with a different max(EffectiveDate)) I tried many variations of this:
SELECT ForwardDate, EffectiveDate, Value
FROM dbo.ArchiveForward f1
WHERE (EffectiveDate = (SELECT MAX(f2.EffectiveDate)
FROM dbo.ArchiveForward f2
WHERE f1.ForwardDate = f2.ForwardDate))
ORDER BY ForwardDate
but all it does is return the values for those records where EffectiveDate is equal to the max(EffectiveDate) for the entire table.
SELECT f1.ForwardDate, f1.EffectiveDate, f1.Value
from dbo.ArchiveForward f1 join
(select ForwardDate, max(EffectiveDate) as EffectiveDate
from dbo.ArchiveForward group by ForwardDate) A
on (f1.ForwardDate = A.ForwardDate and f1.EffectiveDate = a.EffectiveDate)
"People who never make mistakes, never do anything."
Hi, I hope someone can come up with an explanation to this.
In order to populate my DB with ramdom records I execute this code:
(Parameters are generated using Randomize and a loop calls this function)
Private Function Insert_FakeTRans(ByRef TransNum As Long, ByRef Descrip As Byte, ByRef DDate As Date, ByRef Amount As Integer) As Boolean
Dim Cmd1 As New ADODB.Command() 'To Insert in Table1
Dim Cmd2 As New ADODB.Command()'To Insert in Table2
ID is the Id of an article. Each user that see the Article can give it a rank ; (1 =< rank <= 9)
For a given ID , I want all the ranks of it as a ordered table. For example :
For ID = 3 I want to have
Rank | Count
I have wrote a stored procedure as:
@ID int<br />
select ID , Rank into #temp1 from T where ID = @ID<br />
select Rank , count(Rank) as [count] from #temp1 group by rank<br />
This procedure works peoperly when for each ID , each number of 1 to 9 at least one time presents in table T.
How i change my sp?
Thanks a lot!
First, create a Ranks table with the possible ranks (1 to 9), just one column called Rank.
Now try this query:
SELECT Ranks.Rank, CASE WHEN [count] IS NULL THEN 0 ELSE [count] END AS [count]
RIGHT OUTER JOIN (
SELECT Rank, COUNT(*) AS [count]
WHERE ID = @ID
GROUP BY Rank) AS sub ON sub.Rank = Rank.Rank
* DISCLAIMER: I get my left and right outer joins mixed up. So, you may need to change the RIGHT OUTER JOIN to a LEFT OUTER JOIN.