349 reputation
28
bio website
location
age
visits member for 1 year, 10 months
seen 6 hours ago

Mar
7
comment How to clear an an Error 15404 after a DB restart (besides rebooting)
Update, this has happened two more times since the post.
Feb
10
comment c# Backup sql server database, error Operating system error 5(Access is denied.)
I think a filename (SomeFilename.bak) is missing at the end of the backup command. Isn't "Backup" the directory name? MS Backup w/examples at bottom
Feb
10
comment c# Backup sql server database, error Operating system error 5(Access is denied.)
Of course you could try and copy/paste the trace result and run manually to ensure there is no typo. The resolution in this link about setup mentions the backup group for purposes of setup but I don't think it matters for backup. Sorry I think I'm out of ideas here.
Feb
7
comment c# Backup sql server database, error Operating system error 5(Access is denied.)
Is this a web based program? Those can run with/without impersonation turned on (see web.config). If you want to be you it would need to be "on" (but this is not recommended for deployment). Check the PID of the C# exe owner in Task Manager. I suggest running a trace using SQL Server Profiler which should be able to list the command being sent from the C# side. It will list the SQL Server User also. How about double checking path names (must be escaped): "C:\\abc\\a.txt"
Feb
3
comment Scheme review/Normalization level/query optimization
Some SO links on over normalization: 1, 2. Common views can be created for repeated "long" joins. For each join on dir_id, you usually want to have an index on dir_id or a pk leading with dir_id. The "explain plan" should be able to help verify usage of pk/indicies. Avoid using left join as replacement for inner join. Hoping someone can review more specifically for you.
Jan
30
accepted Is it possible to raiserror within a For Delete Trigger that rolls back the deletes
Jan
30
revised Is it possible to raiserror within a For Delete Trigger that rolls back the deletes
title typo
Jan
30
comment Is it possible to raiserror within a For Delete Trigger that rolls back the deletes
Yes! You made my day. Want to post as an answer? I modified the trigger body: "as begin try, ..., end try, begin catch, throw; end catch"
Jan
30
asked Is it possible to raiserror within a For Delete Trigger that rolls back the deletes
Jan
28
comment c# Backup sql server database, error Operating system error 5(Access is denied.)
Are you logged in as an Admin to the Database? Within SSMS, right click on Security -> Logins -> Properties and check under ServerRoles to verify you are listed as "sysadmin" Could you post your backup command in the original question? If you are using a GUI usually there is an option to output the command as "SQL Script."
Jan
27
answered c# Backup sql server database, error Operating system error 5(Access is denied.)
Jan
24
comment Restoring Differential Backups
Yes, the restore job needs to first restore the full (.bak) first with REPLACE, NORECOVERY. Then a second command to restore for the diff part (better name is .dak) shouldn't need REPLACE but should include RECOVERY. Alternately the RECOVERY part can be run as a 3rd separate step (restore database D with recovery) in case there are logs that need to be restored after the .dak. mslink
Jan
23
comment Why does subquery use parallelism and join doesn't?
@MartinSmith - Great--it fixes the estimate number within the actual plan (your screenshot, oddly the old number still shows up when directly requesting an estimated plan). Anyway, sounds like he is happy and didn't need the unique constraint although seems like it should be there in terms of design anyway.
Jan
23
comment Why does subquery use parallelism and join doesn't?
Yes. Identical query plan, both estimates 8748, both actuals 14276. Btw, I thought pre-computing the locationId would resolve that difference but it does not.
Jan
23
comment Why does subquery use parallelism and join doesn't?
If a unique constraint is added on location then it becomes obvious that "=" will return exactly one row. Then in your example the query plans become identical (scans -> seeks): alter table Location add constraint U_Location_Location unique nonclustered (Location);
Jan
14
accepted Why does implicit conversion from sql_variant (basetype decimal) not work well with float
Jan
14
comment Why does implicit conversion from sql_variant (basetype decimal) not work well with float
I had always interpreted that statement to mean an implicit cast occurred to the higher family (somehow) but its truly literal! Thanks.
Jan
14
asked Why does implicit conversion from sql_variant (basetype decimal) not work well with float
Jan
11
accepted How to use proper hints for Upsert involving a top 1 subquery
Jan
11
comment How to use proper hints for Upsert involving a top 1 subquery
It looks as though the Range lock may be unavoidable when both windows are given new (higher) SessionIds because SQL Server requires use of existing keys (href). Since there aren't any existing keys as the SessionId continues to increase, it must be using infinity for the open ended side even though the first query is clearly limited to a single @pSessionId. I left the code as written in the question. Accepting your answer.