Reputation
777
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
8 15
Impact
~18k people reached

Aug
17
asked How to get SQL Server 2012 to use the invariant culture in format()?
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
While the asnwer solves the problem, it does not really connect the dots nicely (and while I think I did connect them, I'm not entirely sure). So let me restate: The problem is that truncate will take schema-stability locks in addition to data locks, whereas delete will only take data locks. When later SSIS goes to validate the schema, it hits the taken schema-stability lock and waits because the connection that verifies metadata is intentionally not enlisted in the distributed transaction. With delete there is no problem because no schema locks were taken in the first place. Correct?
Aug
7
accepted SSIS package blocks itself if uses TRUNCATE
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
@Kin sys.dm_exec_requests doesn't show what is blocking because it's -2. ValidateExternalMetadata solves the problem though, which is a bit surprising because all my OLE DB destinations already were 'Fast Load' which are said to be immune to the problem in the article.
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
@Kin Just tried that, made no difference. As for truncating outside SSIS, I don't really know, because I don't really use distributed transactions outside SSIS; within a non-distributed transaction it works, obviously.
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
@JonSeigel An interesting solution, will tuck away for future use, but will probably not apply here.
Aug
7
asked SSIS package blocks itself if uses TRUNCATE
Jul
26
comment Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way
@AaronBertrand I believe you misread the code; TestTest is scalar and is called as such, WillBeCalledByCLR is TV and is also called as such (with exists), no CLR-specific magic here. As for fixes, yes, the company is slow about fixes because they are believed to not only fix, but also amend and brake. Would actually be nice if this behaviour was confirmed as nonexistent on 2012, as it was last time.
Jul
26
comment Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way
@AaronBertrand I call it from a case that returns single value, but even if I didn't scalar would still be okay as it is documented to return first value of first column and ignore the rest. Edited in version.
Jul
26
revised Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way
added 217 characters in body
Jul
26
asked Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way
May
13
awarded  Caucus
Mar
27
answered How long does it take to do RESTORE WITH NORECOVERY
Dec
21
comment Inline table-valued function vs inline sql
the overall shape is quite different -- please be more specific here.
Dec
19
awarded  Yearling
Oct
31
comment SQL Server 2008 Performance issue with very strange resolution
> In order to find out what is running or going on, I have to run a batch--which makes the problem stop. SO the act of trying to observe the issue resolves it.
Sep
21
awarded  Custodian
Sep
6
accepted CATCH block is triggered when it should not
Sep
6
comment CATCH block is triggered when it should not
@AlexKuznetsov Ultimately, errors that client must see are passed to the client. But there are errors that require internal work. Passing them to the client would imply relying on the client to do that work, and that breaks the incapsulation. The database is not an exclusive data storage only used by one application. Many types of clients can connect.
Sep
6
comment CATCH block is triggered when it should not
Makes sense to me. The context of a try block erroneously survives, so the error functions can return their values, and another consequence would be that an error brings execution into the handler of the erroneously survived try context.