tag:blogger.com,1999:blog-75916314160896715802024-03-09T02:52:49.448+01:00Anders IvnerAnders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-7591631416089671580.post-69654387189703849952012-08-07T10:21:00.000+02:002012-08-07T10:21:07.984+02:00How to handle Microsoft.mshtml.dllThe assembly Microsoft.mshtml.dll is not part of the .Net installation, and so not generally available on the machines you install your software to.<br />
It is distributed with Visual Studio, and installed to the GAC, which is why this issue will often not be descovered until you deploy.<br />
<br />
This is how I handle the issue.<br />
<br />
First, copy the dll to the 'imports' folder in your souce control tree. (You should do this with all your dependencies anyway). This will ensure that it can be found on any other machine, such as a build server, which may not have VS installed.<br />
<br />
Then, when you add a reference to the dll, be sure to browse to the import location. In the properties of the reference, set 'Embed Interop Types' to False and 'Copy Local' to True. This will copy the dll to the output directory, so that it will be available, and not have to be separately installed on client machines.<br />
<br />Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-21157055718815671462011-05-19T19:58:00.012+02:002011-05-23T21:31:48.353+02:00Tip of the day: Reload your static or semistatic data continuously.Let's say you have a table of dates and properties of those dates (like a time dimension in a star-schema in a DW). Typically someone fills it with all the dates up to some date that seems far off enough in the future, thinking that he'll never have to worry about it again.<br /><br />Ok, so maybe there is some sort of script that was used to initially load the data. And when a change is necessary, such as when you need to add more dates or change the fiscal calendar, you can patch the script and reapply it, right?<br /><br />Then, after a few rounds of patching by various developers, the data starts to look slightly inconsistent, and it's no longer apparent where the different fields come from. Maybe someone patched the data without commiting his script changes to source control.<br /><br />The only way to avoid the risk of someone patching the static data by hand is to continuously reload it from a script. And by continuously I mean at least daily. That way, if you need to change the data, you have to change the script. And you don't have to wonder which script was used to load the data, because you can be 100% certain that it was the script that ran last night. And every night before that.<br /><br />From a technical standpoint, you typically cannot delete the data and then reinsert it, as you'll have referential integrity contraints that would break, and it wouldn't be a good idea anyway to risk getting different PKs. What I do instead is I first insert any missing rows (with null or default values for non-pk columns), and then do an update on the whole table, setting not just the columns for the new rows, but for every row in the table.<br /><br />If you cannot reload your data, it is at risk of being corrupted. And the only way to know that you can, is to do.Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-86134608784236557882009-08-31T20:07:00.012+02:002009-10-14T21:08:28.523+02:00Calling stored procedures using .net lambda expression trees<p>Calling stored procedures from ADO.Net leads to verbose code with lots of syntactic overhead. It tends to look something like this:</p><div id="codeSnippetWrapper"> <pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none">comm.CommandType = CommandType.StoredProcedure;<br />comm.Parameters.Add(<span style="color: #0000ff">new</span> SqlParameter()<br />{<br /> ParameterName = <span style="color: #006080">"@return"</span>,<br /> Direction = ParameterDirection.ReturnValue,<br /> DbType = DbType.Int32<br />});<br />comm.Parameters.Add(<span style="color: #0000ff">new</span> SqlParameter(<span style="color: #006080">"@param1"</span>, 17));<br />comm.Parameters.Add(<span style="color: #0000ff">new</span> SqlParameter()<br />{<br /> ParameterName = <span style="color: #006080">"@outparam"</span>,<br /> Direction = ParameterDirection.Output,<br /> DbType = DbType.String,<br /> Size = -1<br />});<br />comm.ExecuteNonQuery();<br /></pre></div><p>And all this to represent a call more succinctly written as:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none">result = MySProc 17, <span style="color: #0000ff">out</span> outparam</pre></div><p>We can achieve something close to this in C#, using lambda expression trees. Assuming that we will use a static method, and that we also need to pass in a connection, our call will look like:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none">StoredProcedures.Exec(<br /> () => MySProc(17, <span style="color: #0000ff">out</span> outparamvalue),<br /> connection);<br /></pre></div><p>For this to work, we will start by specifying the signature of the stored procedure with a normal .net method. Like so:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none"><span style="color: #0000ff">public</span> <span style="color: #0000ff">int</span> MySProc(<span style="color: #0000ff">int</span> param1, <span style="color: #0000ff">out</span> <span style="color: #0000ff">string</span> outparam)<br />{<br />}</pre></div><p>and so, the signature for our Exec-method becomes:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none"><span style="color: #0000ff">public</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">int</span> Exec(Expression<Func<<span style="color: #0000ff">int</span>>> expr,<br /> SqlConnection conn)<br /></pre></div><p>The <font face="Courier New">Expression<></font> thing is what causes the compiler to stop doing what it normally does, which is to compile C# into MSIL, and instead give you the expression tree.</p><p>Focusing on the interesting bits, and leaving the details for the full code sample at the end, we start by noting that we are only interested in expressions that are method calls:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none">var callExpr = (MethodCallExpression)expr.Body;</pre></div><p>The method name is simply <font face="Courier New">callExpr.Method.Name</font>. On to find the arguments!</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none"><span style="color: #0000ff">foreach</span> (var arg <span style="color: #0000ff">in</span> callExpr.Arguments)</pre></div><p>The arguments are themselves expressions, and that’s not really what we want right now. Although “17” in our example would be easy enough to handle, this really could be an arbitrarily complex expression. Thankfully, .Net lets us compile lambda expressions into delegates that we can then call to get the value of the expression. There are two minor details to get this working: First, the expression is strongly typed (as e.g. integer) and we need the boxed value, so we wrap the expression in a cast to object. Second, we create a lambda expression with our expression as the body. The type of the lambda expression is a function taking no arguments and returning an object. The code is:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none"><span style="color: #0000ff">private</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">object</span> GetValue(Expression expr)<br />{<br /> var valueExpr = Expression.Lambda<Func<<span style="color: #0000ff">object</span>>>(<br /> Expression.Convert(expr, <span style="color: #0000ff">typeof</span>(<span style="color: #0000ff">object</span>)),<br /> <span style="color: #0000ff">new</span> ParameterExpression[] { });<br /> var deleg = valueExpr.Compile();<br /> <span style="color: #0000ff">return</span> deleg();<br />}<br /></pre></div><p>A bit more of a problem is handling out parameters. The argument – outparamvalue in our example above – is (typically) a local variable in the <i>calling</i> method. How do we assign a value to that? We need to look deeper into how the C# compiler treats anonymous methods. While this topic is worthy of a blog post of its own, the following brief summary will have to do: The compiler creates a class for the anonymous method, and the local variables become fields on that class. Thus, the out parameter expression becomes a field reference.</p><p>With that in mind, we can find the field and assign it using reflection:</p><div id="codeSnippetWrapper"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none"><span style="color: #0000ff">private</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">void</span> SetValue(Expression outarg, <span style="color: #0000ff">object</span> <span style="color: #0000ff">value</span>)<br />{<br /> var member = (MemberExpression)outarg;<br /> <span style="color: #0000ff">object</span> source = GetValue(member.Expression);<br /> var field = (FieldInfo)member.Member;<br /> field.SetValue(source, <span style="color: #0000ff">value</span>);<br />}<br /></pre></div><p>And we are done. The full code, as promised, is: </p><div id="codeSnippetWrapper" style="border-right: silver 1px solid; padding-right: 4px; border-top: silver 1px solid; padding-left: 4px; font-size: 8pt; padding-bottom: 4px; margin: 20px 0px 10px; overflow: auto; border-left: silver 1px solid; width: 97.5%; cursor: text; direction: ltr; max-height: 200px; line-height: 12pt; padding-top: 4px; border-bottom: silver 1px solid; font-family: 'Courier New', courier, monospace; background-color: #f4f4f4; text-align: left"><pre id="codeSnippet" style="padding-right: 0px; padding-left: 0px; font-size: 8pt; padding-bottom: 0px; margin: 0em; overflow: visible; width: 200%; color: black; direction: ltr; border-top-style: none; line-height: 12pt; padding-top: 0px; font-family: 'Courier New', courier, monospace; border-right-style: none; border-left-style: none; background-color: #f4f4f4; text-align: left; border-bottom-style: none"><span style="color: #0000ff">using</span> System;<br /><span style="color: #0000ff">using</span> System.Collections.Generic;<br /><span style="color: #0000ff">using</span> System.Linq;<br /><span style="color: #0000ff">using</span> System.Linq.Expressions;<br /><span style="color: #0000ff">using</span> System.Text;<br /><span style="color: #0000ff">using</span> System.Data;<br /><span style="color: #0000ff">using</span> System.Data.SqlClient;<br /><span style="color: #0000ff">using</span> System.Reflection;<br /><br /><span style="color: #0000ff">namespace</span> StoredProcedureCaller<br />{<br /> <span style="color: #0000ff">public</span> <span style="color: #0000ff">class</span> StoredProcedures<br /> {<br /> <span style="color: #0000ff">private</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">object</span> GetValue(Expression expr)<br /> {<br /> var valueExpr = Expression.Lambda<Func<<span style="color: #0000ff">object</span>>>(<br /> Expression.Convert(expr, <span style="color: #0000ff">typeof</span>(<span style="color: #0000ff">object</span>)),<br /> <span style="color: #0000ff">new</span> ParameterExpression[] { });<br /> var deleg = valueExpr.Compile();<br /> <span style="color: #0000ff">return</span> deleg();<br /> }<br /><br /> <span style="color: #0000ff">private</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">void</span> SetValue(Expression outarg, <span style="color: #0000ff">object</span> <span style="color: #0000ff">value</span>)<br /> {<br /> var member = (MemberExpression)outarg;<br /> <span style="color: #0000ff">object</span> source = GetValue(member.Expression);<br /> var field = (FieldInfo)member.Member;<br /> field.SetValue(source, <span style="color: #0000ff">value</span>);<br /> }<br /><br /> <span style="color: #0000ff">private</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">object</span> ConvertToDbValue(<span style="color: #0000ff">object</span> val)<br /> {<br /> <span style="color: #0000ff">if</span> (val == <span style="color: #0000ff">null</span>)<br /> <span style="color: #0000ff">return</span> DBNull.Value;<br /> <span style="color: #0000ff">else</span><br /> <span style="color: #0000ff">return</span> val;<br /> }<br /><br /> <span style="color: #0000ff">private</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">object</span> ConvertFromDbValue(<span style="color: #0000ff">object</span> val)<br /> {<br /> <span style="color: #0000ff">if</span> (val == DBNull.Value)<br /> <span style="color: #0000ff">return</span> <span style="color: #0000ff">null</span>;<br /> <span style="color: #0000ff">else</span><br /> <span style="color: #0000ff">return</span> val;<br /> }<br /><br /> <span style="color: #0000ff">public</span> <span style="color: #0000ff">static</span> <span style="color: #0000ff">int</span> Exec(Expression<Func<<span style="color: #0000ff">int</span>>> expr,<br /> SqlConnection conn)<br /> {<br /> <span style="color: #0000ff">if</span> (expr.Body.NodeType != ExpressionType.Call)<br /> <span style="color: #0000ff">throw</span> <span style="color: #0000ff">new</span> ArgumentException(<span style="color: #006080">"StoredProcedures.Exec() can only be used on method calls"</span>);<br /><br /> var callExpr = (MethodCallExpression)expr.Body;<br /><br /> var command = <span style="color: #0000ff">new</span> SqlCommand(){<br /> CommandText = callExpr.Method.Name,<br /> CommandType = System.Data.CommandType.StoredProcedure,<br /> Connection = conn<br /> };<br /><br /> command.Parameters.Add(<span style="color: #0000ff">new</span> SqlParameter()<br /> {<br /> ParameterName = <span style="color: #006080">"@return"</span>,<br /> DbType = DbType.Int32,<br /> Direction = ParameterDirection.ReturnValue<br /> }); <br /><br /> var paramlist = callExpr.Method.GetParameters();<br /> var i = 0;<br /> <span style="color: #0000ff">foreach</span> (var arg <span style="color: #0000ff">in</span> callExpr.Arguments)<br /> {<br /> var param = paramlist[i];<br /> var sqlparam = <span style="color: #0000ff">new</span> SqlParameter();<br /> sqlparam.ParameterName = <span style="color: #006080">"@"</span> + param.Name;<br /><br /> <span style="color: #0000ff">if</span> (param.IsOut)<br /> {<br /> sqlparam.Direction = ParameterDirection.Output;<br /> sqlparam.Size = -1;<br /> }<br /> <span style="color: #0000ff">else</span><br /> {<br /> sqlparam.Direction = ParameterDirection.Input;<br /> sqlparam.Value = ConvertToDbValue(GetValue(arg));<br /> }<br /> command.Parameters.Add(sqlparam);<br /><br /> i++;<br /> }<br /><br /> command.ExecuteNonQuery();<br /><br /> i = 1;<br /> <span style="color: #0000ff">foreach</span> (var arg <span style="color: #0000ff">in</span> callExpr.Arguments)<br /> {<br /> var sqlparam = command.Parameters[i];<br /><br /> <span style="color: #0000ff">if</span> (sqlparam.Direction == ParameterDirection.Output)<br /> SetValue(arg, ConvertFromDbValue(sqlparam.Value));<br /><br /> i++;<br /> }<br /><br /> <span style="color: #0000ff">return</span> (<span style="color: #0000ff">int</span>)command.Parameters[0].Value;<br /> }<br /><br /> }<br />}<br /></pre><br /><br /></div> Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-47414886221164264462009-06-07T20:06:00.010+02:002009-06-08T20:15:01.477+02:00Asynchronously calling an SSIS package from ASP.NetThe following stored procedure allows you to invoke an Intergration Services package asynchronously and, additionally, send in parameters (variable values or any configurable property). I found it <a href="http://www.sqlteam.com/article/how-to-asynchronously-execute-a-dts-package-from-asp-or-aspnet">here</a>, and have made some updates to it. In short, it creates a SQL Server job to run the package. Be sure to read the details in the original article.<div><br /></div><div>Changes:</div><div><br /></div><div>1. I now invoke the package using a job step of type SSIS, rather than calling DTSEXEC. (DTSEXEC was not found in the specified folder on my installation. Presumably this should be more stable).</div><div>2. Because of this, the syntax for sending in values to the package has changed. </div><div><br /></div><div>I am also following Jeff Atwood's <a href="http://www.codinghorror.com/blog/archives/001257.html">modest proposal</a> to assign a guid to the code for future copy-paste reuse.</div><div><br /></div><div>Enjoy!</div><div><br /></div><div>-- codesnippet:5852E73F-5CE7-44CC-A5CE-D6420FF16B98</div><div><br /></div><div><div>declare @package varchar(128)</div><div>set @package = '\File System\MyProject\MyPackage'</div><div><br /></div><div>-- Initialize command</div><div>declare @cmd varchar(4000)</div><div>set @cmd = N'/DTS "' + @package + N'" /SERVER "." /CHECKPOINTING OFF /SET "\Package.Variables[User::MyVariable].Properties[Value]";"' + @variablevalue + '" /REPORTING E'</div><div><br /></div><div>-- ALLOW CONCURRENT DTS EXECUTIONS</div><div>declare @jname varchar(128)</div><div>set @jname = cast(newid() as char(36))</div><div><br /></div><div>-- Create job</div><div>declare @jid uniqueidentifier</div><div>exec msdb.dbo.sp_add_job</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@job_name <span class="Apple-tab-span" style="white-space:pre"> </span>= @jname,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@enabled<span class="Apple-tab-span" style="white-space:pre"> </span>= 1,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@category_name <span class="Apple-tab-span" style="white-space:pre"> </span>= '',</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@delete_level<span class="Apple-tab-span" style="white-space:pre"> </span>= 1,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@job_id <span class="Apple-tab-span" style="white-space:pre"> </span>= @jid OUTPUT</div><div><br /></div><div>exec msdb.dbo.sp_add_jobserver</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@job_id <span class="Apple-tab-span" style="white-space:pre"> </span>= @jid,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@server_name<span class="Apple-tab-span" style="white-space:pre"> </span>= '(local)'</div><div><br /></div><div>exec msdb.dbo.sp_add_jobstep</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@job_id<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>@jid,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@step_name<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>N'Execute Package', </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@step_id<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>1, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@cmdexec_success_code<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@on_success_action<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>1, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@on_success_step_id<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@on_fail_action<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>2, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@on_fail_step_id<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@retry_attempts<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@retry_interval<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@os_run_priority<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@subsystem<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>N'SSIS', </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@command<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>@cmd,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@database_name<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>N'master', </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@flags<span class="Apple-tab-span" style="white-space:pre"> </span>=<span class="Apple-tab-span" style="white-space:pre"> </span>0</div><div><span class="Apple-tab-span" style="white-space:pre"> </span></div><div>-- Start job</div><div>exec msdb.dbo.sp_start_job </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>@job_id <span class="Apple-tab-span" style="white-space:pre"> </span>= @jid</div></div>Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-73320362943342424952008-09-29T12:10:00.004+02:002008-09-29T12:36:53.899+02:00Do-not-break-the-testcases 101Before commiting code to source control:<div><br /></div><div>1. Ensure that you have the latest of every one else's changes.</div><div>2. Build and compile everything.</div><div>3. Run ALL testcases.</div><div>4. Should any testcase fail - take care of it BEFORE commiting.</div><div>5. Make sure to check in ALL files necessary, including .csproj, data files, and testcases. Do a "show all differences" to make sure you're not missing any changed or added files.</div><div>6. Do not be afraid to ask someone to retrieve your changes to verify that they are ok.</div><div><br /></div><div>I wrote these rules on a recent project I was on. These are the basic steps, there are more that have been said better by others before me, such as "Commit early, Commit often", and "Do not commit and run", and tools such as Cruise control to make your life easier.</div>Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-72502219639582637802008-09-05T08:31:00.002+02:002008-09-05T09:02:18.272+02:00ChromeSo, Google builds a web browser that is better able to handle javascript applications - such as Google Apps. The cynical part of me would say that Google is trying to solve a problem they themselves created. <div><br /></div><div>I use GMail, Google Reader and Google Docs regularly and I'm absolutely delighted at the improved performance and stability. </div><div><br /></div><div>Ofcourse, running Google's apps in Google's browser has the smell of monopoly attached to it. But I'm too high on all the fresh air to care.</div><div><br /></div><div>All hail our new evil overlords!</div>Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-27395732375815359512008-05-07T16:32:00.020+02:002008-05-15T20:29:30.924+02:00Nullable enumsEnums in .net are represented as integers, right? This means you can do the following (yes, casting the integer to <span style="font-family:courier new;">object</span> is superfluous. It is added for clarity): <span style="font-size:85%font-family:courier new;"><pre>public enum Foo { X, Y };<br />[...]<br />public Foo Bar { set { ... } }<br />[...]<br />PropertyInfo prop = obj.GetType().GetProperty("Bar");<br />prop.SetValue(obj, (object)1, null);</pre></span><p>I.e. you can use reflection to set the value of a property of an enum type using its integer value. (You could also just cast it like so: <span style="font-family:courier new;">obj.Bar = (Foo)1;</span>, but that's not quite as interesting, for reasons that will be revealed later.)</p><p>Also, a boxed value and a boxed nullable value have the same representation, right? They are both pointers to a small object containing the primitive. As the following code shows, boxed integers and boxed nullable integers are interchangeable:</p><p><span style="font-family:courier new;font-size:85%;"><pre>int intValue = 1;<br />int? nullableIntValue = 1;<br />object boxedInt = intValue;<br />object boxedNullableInt = nullableIntValue;<br />intValue = (int)boxedNullableInt;<br />nullableIntValue = (int?)boxedInt;</pre></span></p><p>And, not surprisingly, the same holds for enum values, now demonstrated using reflection as in our first example:</p><p><span style="font-family:courier new;font-size:85%;"><pre>public Foo? Bar { set { ... } }<br />[...]<br />prop.SetValue(obj, (object)Foo.X, null);</pre></span></p><p>Now, combining these two examples, what do you think the following code would do?</p><p><span style="font-family:courier new;font-size:85%;"><pre>public Foo? Bar { set { ... } }<br />[...]<br />prop.SetValue(obj, (object)1, null);</pre></span></p><p>If you, like me, thought it would happily set the property to the enum value correspoding to 1, in this case <span style="font-family:courier new;">Foo.X</span>, you'd be wrong. Instead it throws the following exception:</p><p>Object of type 'System.Int32' cannot be converted to type 'System.Nullable`1</p><p>Huh? The .net designers may know why this is, and they may even have a good reason for it, but to me it just seems plain weird. It is worth noting that <span style="font-family:courier new;">obj.Bar = (Foo?)1;</span> works just fine. </p><p>This issue came up when mapping objects from database tables, using integers to represent enum values. I've reluctantly added the following workaround to my mapping code:</p><p><span style="font-family:courier new;font-size:85%;"><pre>PropertyInfo prop = [...]<br />object val = [...]<br />if ((prop.PropertyType.IsGenericType) &&<br /> (prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)))<br />{<br /> Type targetType = prop.PropertyType.GetGenericArguments()[0];<br /> if (targetType.IsEnum)<br /> val = Enum.ToObject(targetType, val);<br />}<br />prop.SetValue(obj, val, null);</pre></span><br /></p>Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-35030530044571025402008-04-20T09:55:00.006+02:002008-04-21T14:44:14.549+02:00Bad serviceIt appears there is a problem with Sql Server Service Broker when executing DROP SERVICE. I had it choke the server to eventually fail with the message "There is insufficient system memory to run this query".<br /><br />The problem is that Service Broker needs to end all open conversations, and it does so in one transaction. If you have many open conversations troubles arise.<br /><br />I found the following <a href="http://gps678.com/21/e75802eda4787b23.html">link</a> which describes a fix for the problem. I'm reprinting the fix here in case the link goes bad:<br /><br /><span style="font-family:courier new;">declare crsConversations cursor for<br />select conversation_handle<br />from sys.conversation_endpoints e<br />join sys.services s on s.service_id = e.service_id<br />where s.name = 'myservicename';<br />open crsConversations;<br />declare @dh uniqueidentifier;<br />declare @batch int;<br />select @batch = 0;<br />begin transaction<br />fetch next from crsConversations into @dh;<br />while @@fetch_status =0<br />begin<br />-- replace this with end conversation @dh with error ... if<br />-- and error has to be sent to the peer<br />--<br />end conversation @dh with cleanup;<br />-- commit every 1000 conversations ended<br />select @batch = @batch + 1;<br />if @batch > 999<br />begin<br />commit transaction;<br />begin transaction;<br />end<br />fetch next from crsConversations into @dh;<br />end<br />commit transaction;<br />close crsConversations;<br />deallocate crsConversations;<br />go<br />drop service 'myservicename'<br />go</span><br /><span style="font-family:Courier New;"></span><br /><span >Now, if you have this problem the real question is, why are you leaking open conversations?</span>Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0tag:blogger.com,1999:blog-7591631416089671580.post-25515672508836723822008-04-19T20:08:00.001+02:002008-04-20T09:48:44.548+02:00(contd.)My old blog can be found here:<br /><a href="http://blogs.codegear.com/andersivner">http://blogs.codegear.com/andersivner</a>.<br />I've since then left Borland/CodeGear and now work for <a href="http://www.sigma.se/sv/">Sigma</a>.<br /><br />The product I worked on at Borland/CodeGear, ECO, is now available from <a href="http://www.capableobjects.com/">CapableObjects</a>, a company run by my friends and former collegues. They have recently released ECO for Visual Studio. Way cool!Anders Ivnerhttp://www.blogger.com/profile/17357180360382726804noreply@blogger.com0