tag:blogger.com,1999:blog-92077855420164015662024-03-14T02:13:17.888-07:00Information Management Enabled'http://www.blogger.com/profile/01012943142843553939noreply@blogger.comBlogger29125tag:blogger.com,1999:blog-9207785542016401566.post-51053215292579065052011-10-17T04:32:00.000-07:002011-10-17T13:54:24.087-07:00Handling historical data with SQL - Common errors in datawarehousingGreetings,<br /><br />It has been a while since my last post. Here is an old post I put here because it comes up in mature enterprise data warehouses. Two easy points that are too hard to put in Power Point, but easy enough to take care of in the warehousing solution. Problems are intervened.<br /><br /><br />1) GETDATE() used directly<br />In MS SQL Server GETDATE() is nondeterministic which is suboptimal when it comes to performance and schema binding.<br /><br /><br />2) BETWEEN is used<br />BETWEEN is an inclusive operator, which means that one can't really step with it the way I see it. Say you step one day at the time, you would have to use for date d: "x BETWEEN d AND d+.9999999" or risk having <br /><br />a) the same data at two consecutive dates<br />b) two historic versions for the same BK<br /><br />to give an example, for <br /> - 1000002; "1901-01-01 00:00"; "2009-10-24 00:00"; "1234"; "Helly hansen";...<br /> - 1000003; "2009-10-24 00:00"; "2049-12-31 23:59"; "1234"; "Helly Hansen";...<br /><br />the query "d BETWEEN Invoice.ValidFrom AND Invoice.ValidTo" would for d="2009-10-24 00:00" give both rows, which should crash the ETL job or pollute the result.<br /><br /><br />3) One usual practice<br />For this reason some use between statements like <br /><br />"d BETWEEN '09:00:00.0000000' AND '16:59:59.9999999';" <br />or<br />"d BETWEEN '09:00:00.0000000' AND '16:59:59';" <br /><br />which might be working as long as you are absolutely sure that this will not be cast, or it might be rounded up, which is even worse (looks like it works but don't). <br /><br />SELECT CAST(N'2009-12-12 16:59:59.199' AS DATETIME) -> "2009-12-12 16:59:59.200"<br /><br /><br />4) The simple solution<br />"d >= '09:00' AND d < '17:00';"<br />will work no matter what data type is used and therefore is more portable, I think.<br /><br /><br />So, now that’s documented<br />Have a nice one,<br />Gorm Braarvig'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-24484904025955687722011-04-17T14:29:00.000-07:002011-04-17T14:34:40.295-07:00Alike twitter-appThis is the initial page for the "Alike" twitter-app. That's all.'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-35781901461408263912011-03-18T05:36:00.000-07:002011-03-18T06:01:13.197-07:00"Domain Driven Design" and "Information Islands"Domain driven design, as it has been explained to me, dictates that the data shall have the truth in the domain model layer at the same time as you must have a bounded context. <br /><br />This way you create an information Island bound on the application layer!<br /><br />The integration work between these systems, as they grow in fast iterations (say Scrum-sprints) will surely be huge, and the "single version of the truth" across the value chains will be a distant dream, evens when it comes to central dimensions like "Customer" and "Product"!<br /><br />Will be exciting to see the solutions to this, so far I have heard<br />- Shared Kernel<br /> Well... you need it in the database to solve all the integration points, you can't expect ETL and EAI to use an application layer that changes all the time, you want access to the databases in most cases. Many reasons for that across scalability, security, ease of implementation and testing etc<br /><br />- Anticorruption Layer<br />Well, in a siloed system this would in my mind include context tables on every single shared entity, which I haven't really seen samples of yet, I expect this is not thought through. Might be promissing.<br /><br />- Explicit model delimination<br />I didn't understand the consept, which tells me that it might be too complex for a simple common problem. The solution shouldn't be so much more complex than the problem<br /><br />I believe the DDD is great for Cabin-rental systems and Access card systems, but the SOA-solution? Well prove that you can handle a strategy of the common version of your data. I personally like to be threated as the same identity across the functions of the companys I interact with...<br /><br />...this is a reason for working on MDM (Master data management).<br /><br />just my 2 cent<br /><br />have a good one<br />/G'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-28658676647257821982010-11-14T15:04:00.001-08:002010-11-14T15:08:52.099-08:00Attempting to program C++ once more<p>I have been trying (for a few hours) to create a good checksum function using C++ and found a nice MD5 implementation I thought would be a nice starting point.</p> <p>The business case for this is that I do enterprise data warehousing and fast hashing combined with streaming (in concert with loading and smart indexing) is a way of solving all hard performance problems I have seen.</p> <p>Compiling a DLL and using it in SQL Server (SQL Server 2011 CTP1) was harder than it should, I actually need to either… <br /> - find an installation of SQL Server 2005x64 and steal a lib-file or <br /> - use LoadLibrary and GetProcAddress (which is a lot of work)  <br />…to be able to do this.</p> <p>This is sad and unacceptable, I will have to abort and be happy with the SQL CLR’s I have created already. Sub-optimal because <br /> - the number of parameters can not be variable and <br /> - performance is, of course, not as good as what I can do with native code <br />I haven’t given up but need to devert solution until I write a desent parallell bulk-loader, I think,</p> <p>have a good one <br />/G</p> 'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-12696889780172070542010-07-02T05:09:00.001-07:002010-07-02T05:19:57.464-07:00Excel: read data from stored procedure, with great difficulty<span xmlns="">Working with data (and information), I use Excel extensively. In my current project I use it to show results from tests. Having automated the tests I thought that reading the results from a stored procedure that actually performed the test was a good idea. It was not a good idea. My stored procedure was suddenly not usable from excel when data began to grow. I started<br /><br /><ul><li>Restarting Excel<br /></li><li>Restarting my computer<br /></li><li>Reducing my environment<br /></li><li><div>Reducing my code<br /></div><ul><li>This finally gave me a positive result<br /></li></ul></li></ul>I found out that my memory table (table-valued parameter) worked fine until I started filling it with data. Stored procedures has more outputs than the actual rowsets. This creates a problem when a "read" operation reports that it has written rows (side effect). Even if the side-effect is in a "memory table" (which obviously is automatically trashed afterwards). The fix was easy:<br /><br />Use "SET NOCOUNT ON" in my Interface procedure (it calls other procedures, but these does not need NOCOUNT.<br /><br />Here is a simple (working standalone) sample for SQL Server 2008 and Excel 2007/2010:<br /><br /><tsql><br /><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:green;">/* Create some temp db */<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">CREATE</span><br /><span style="color:blue;">DATABASE</span> tvfTest<br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">GO<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">USE</span> tvfTest<br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">GO<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">CREATE</span><br /><span style="color:blue;">TYPE</span> TTable <span style="color:blue;">AS</span><br /><span style="color:blue;">TABLE </span><span style="color:gray;">(</span>SomeKey <span style="color:blue;">INT</span><span style="color:gray;">,</span> SomeValue <span style="color:blue;">SYSNAME</span></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:gray;">)<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">GO</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">--*/<br /></span><br /><br /><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:green;">/* Create "Internal" proc that takes tvp */<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">CREATE</span><br /><span style="color:blue;">PROC</span> Internal @t TTABLE <span style="color:blue;">READONLY</span><br /><span style="color:blue;">AS</span><br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> SET</span><br /><span style="color:blue;">NOCOUNT</span><br /><span style="color:blue;">ON</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">-- Not imnportant here, but removes extra noice.<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> SELECT</span> SomeKey<span style="color:gray;">=</span>0<span style="color:gray;">,</span>SomeValue<span style="color:gray;">=</span><span style="color:red;">'Zero'</span><br /><span style="color:blue;">UNION</span><br /><span style="color:gray;">ALL</span><br /><span style="color:blue;">SELECT</span><br /><span style="color:gray;">*</span><br /><span style="color:blue;">FROM</span> @t<br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">GO</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">--*/<br /></span><br /><br /><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:green;">/* Create "Interface" proc that uses tvp and return result set */<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">CREATE</span><br /><span style="color:blue;">PROC</span> Interface @test <span style="color:blue;">INT</span><span style="color:gray;">=</span>0 </span></span><span style="font-size:78%;"><span style="font-family:courier new;color:blue;">AS<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> DECLARE</span> @t TTable<br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> IF</span> @test<span style="color:gray;">=</span>1<br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> BEGIN<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> INSERT</span> @t <span style="color:blue;">VALUES</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span><span style="color:red;">'One'</span><span style="color:gray;">)</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">-- Fails (because rowcount is returned and it indicates that write operation performed?)<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> END<br /></span><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> ELSE</span><br /><span style="color:blue;">IF</span> @test<span style="color:gray;">=</span>2<br /><br /></span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> BEGIN</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">-- Same as above, but "SET NOCOUNT ON" helps avoiding error!<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"> SET</span><br /><span style="color:blue;">NOCOUNT</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:blue;">ON<br /></span><br /><span style="font-family:courier new;"><span style="color:blue;"> INSERT</span> @t <span style="color:blue;">VALUES</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span><span style="color:red;">'One'</span></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:gray;">)<br /></span><br /></span><span style="font-size:78%;"><span style="font-family:courier new;"><span style="color:blue;"> END<br /></span><br /></span><span style="font-family:courier new;"><span style="color:blue;"> EXEC</span> Internal</span></span><span style="font-size:78%;"><span style="font-family:courier new;"><span style="color:blue;"><br /></span>@t<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">GO</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">--*/<br /></span><br /><br /><br /></span><span style="font-size:78%;"><span style="font-family:courier new;"><span style="color:green;">/* Call with tvp and test */<br /></span><br /></span><span style="font-family:courier new;"><span style="color:blue;">EXEC</span> Interface</span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"><br /></span>0 </span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">-- 0,Zero -- Command(s) completed successfully.<br /></span><br /><span style="font-family:courier new;"><span style="color:blue;">EXEC</span> Interface</span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"><br /></span>1 </span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">-- 0,Zero;1,One -- (1 row(s) affected)<br /></span><br /><span style="font-family:courier new;"><span style="color:blue;">EXEC</span> Interface</span></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;"><br /></span>2 </span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">-- 0,Zero;1,One -- Command(s) completed successfully.<br /></span><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:blue;">GO</span><br /></span></span><span style="font-size:78%;"><span style="font-family:courier new;color:green;">--*/<br /></span><br /><br /><br /></span><span style="font-family:courier new;"><span style="font-size:78%;"><span style="color:green;">/* Map up in Excel */<br /></span><br /></span></span></tsql><br /><br /><excel><br /><br /><div><table style="BORDER-COLLAPSE: collapse" border="0"><colgroup><col style="WIDTH: 88px"><col style="WIDTH: 107px"></colgroup><tbody valign="top"><tr style="HEIGHT: 20px"><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom" colspan="2"><span style="color:black;"><strong>Without insert</strong></span><br /></td></tr><tr style="BACKGROUND: #4f81bd; HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"><span style="color:white;"><strong>SomeKey</strong></span><br /></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"><span style="color:white;"><strong>SomeValue</strong></span><br /></td></tr><tr style="BACKGROUND: #dbe5f1; HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"><span style="color:black;">0</span><br /></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"><span style="color:black;">Zero</span><br /></td></tr><tr style="HEIGHT: 20px"><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"></td><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"></td></tr><tr style="HEIGHT: 20px"><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom" colspan="2"><span style="color:black;"><strong>With insert without NOCOUNT</strong></span><br /></td></tr><tr style="BACKGROUND: #4f81bd; HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"><span style="color:white;"><strong>SomeKey</strong></span><br /></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"><span style="color:white;"><strong>SomeValue</strong></span><br /></td></tr><tr style="BACKGROUND: #dbe5f1; HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"></td></tr><tr style="HEIGHT: 20px"><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"></td><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"></td></tr><tr style="HEIGHT: 20px"><td style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom" colspan="2"><span style="color:black;"><strong>With insert with NOCOUNT</strong></span><br /></td></tr><tr style="BACKGROUND: #4f81bd; HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"><span style="color:white;"><strong>SomeKey</strong></span><br /></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"><span style="color:white;"><strong>SomeValue</strong></span><br /></td></tr><tr style="BACKGROUND: #dbe5f1; HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"><span style="color:black;">0</span><br /></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"><span style="color:black;">Zero</span><br /></td></tr><tr style="HEIGHT: 20px"><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: medium none" valign="bottom"><span style="color:black;">1</span><br /></td><td style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: medium none; PADDING-LEFT: 5px; PADDING-RIGHT: 5px; BORDER-TOP: medium none; BORDER-RIGHT: #95b3d7 0.5pt solid" valign="bottom"><span style="color:black;">One</span><br /></td></tr></tbody></table></div></excel><br /><br /><excel_description><br />Add three external TSQL connections with command type SQL and commands:<br /><br />- EXEC Interface 0<br /><br />- EXEC Interface 1<br /><br />- EXEC Interface 2<br /><br /><br /></excel_description><br /><br />The middle one (Interface 1) should not work because it returns a non-empty "rowcount"!<br /><br />I do not know if the bug in Excel is by design to lower the danger of SQL Injection attacks or because of a suboptimal data retrieval function.<br /><br />Have a nice day<br />/G<br /></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-17881588655599952542010-05-24T08:58:00.001-07:002010-05-24T09:30:40.992-07:00Enable host to image network in Hyper-V<br/>Hyper-V, while better than the alternatives, has some short comings when it comes to the client; copying files is hard and screen support is not ideal.<br/>That is why I prefer using the host to access the image through the network stack with Remote Desktop or just the browser.<br/>Here are the steps to set up the communication:<br /><ol><br /><li>Add a virtual adapter<br />In Hyper-V this is accomplished on the host in "Virtual Network Manager" by adding an "Internal" network I usually name "example.com"<br /><br/><img src="http://2.bp.blogspot.com/_LzOglmr9-uE/S_qjSLqd_NI/AAAAAAAABd4/QsZojFVYr1A/s400/1-Add+virtual+adapter.PNG" /><br /></li><br /><li>Set up adapter inside the image<br />Ensuring the image is shut down (and not in save state mode) I add "Network Adapter" and select the net created in step 1.<br />After restarting the image I open "Network Connections" and set the IP to 192.0.2.110, mask 255.255.255.0 and optionally default gateway to 192.0.2.100.<br />Last I turn off firewall completely for this card (steps depends).<br /><br/><img src="http://3.bp.blogspot.com/_LzOglmr9-uE/S_qlamGojLI/AAAAAAAABeA/1DabC-55cnY/s1600/2-Set+up+adapter+in+image.PNG" /><br /></li><br /><li>Set up adapter on host<br />On "Network Connections" I set up the adapter manually with IP 192.0.2.100, mask 255.255.255.0. Advanced: if I have AD inside the image and the host is part of the domain I set DNS server to 192.0.2.110.<br />If I don't use DNS in an image (or the host) I change the "hosts" file (or lmhosts) with notepad c:\Windows\System32\drivers\etc\hosts and add lines for the names we want the inside of the image to be known, for instance:<br /><ul><br /><li>intranet.contoso.com 192.0.2.110</li><br /><li>www.contoso.com 192.0.2.110</li><br /><li>contoso.com 192.0.2.110</li><br /><li>contosoApp 192.0.2.110</li><br /></ul><br /><img src="http://4.bp.blogspot.com/_LzOglmr9-uE/S_qlfXy2qqI/AAAAAAAABeI/KGw_dHcpRrk/s1600/3-Set+up+adapter+on+host.PNG" /><br /></li><br /><li>Test<br />In host start cmd and "ping 192.0.2.110"<br />In image start cmd and "ping 192.0.2.100"<br />From host start mstsc and open "contosoApp"<br />From host start iexplore and navigate "intranet.contoso.com"<br /></li><br /><li>Result<br />I now browse the image from the host and use remote desktop. This way screen works better and browser app (and Visual studio etc) works much much better.<br /></li></ol>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-17048192312664894432009-11-13T09:13:00.000-08:002009-11-13T09:25:54.244-08:00SQL Server 2008 R2 MDS - Microsoft MDM SolutionPlayed two hours with this on the just released SQL 2008 R2 CTP nov.<br /><br />Two hours effective work was enough to<br />- install the product (including reading how to do it),<br />- installing IIS on Windows 7 (you need a modern advanced OS)<br />- implementing security<br />- adding a model (PoC) with a hierarchy<br />- reading most of the documentation<br /><br />Books on-line is sparse, so I haven't really done a real walk-through yet. I hope a tutorial is coming soon, or I might post one here (after getting to know the thing better).<br /><br />Wonder if I can run a hybrid model where I can have some entities work as in the "registry"-model, and some as in the "Central Master Data Repository"-model.<br /><br />I would like to put all my meta data into this thing, hope it is mature enough.<br /><br />I went to a couple of sessions on SQL PASS, but they were introductory, can't be many that have experience with this thing.<br /><br />Anyhow, have a nice day.<br />G.'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com2tag:blogger.com,1999:blog-9207785542016401566.post-43174863284894582462009-11-03T23:57:00.000-08:002009-11-04T00:00:48.802-08:00Performance hit for SELECT because of fragmentation?SQL PASS - I got help from an expert on SQL PASS, Seattle<br /><br />Q: Is there a performance hit on the "select"-side because of some fragmentation of the data base files caused by auto growth?<br /><br />A: No. But you might want to enable "Perform volume maintenance tasks"<br /><br /><strong>Question details</strong><br />We have a generic script-based deployment of many data bases, and therefore have auto-growth on them, so they are created in exactly the same way. There are a total of ten data bases with twelve files each, so 120 files. The files all start at 100MB and grow by 25%. To give each data base special treatment makes changes more expensive (We charge by the hour).<br /><br /><strong>Solution details</strong><br />1) Keep scripts as is.<br />2) Enable "Instant Initialization"<br /> a) open a command window and run "sc qc <servicename>", for me it is "sc qc sqlserver$datawarehouse"<br /> b) note the user name from "SERVICE_START_NAME" attribute.<br /> c) Fire up ocal policy editor, start -> run -> gpedit.msc<br /> d) navigate to "Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignments", open "Perform volume maintenance tasks"<br /> e) add the user from step b)<br /> f) restart your sql server instance (not sure if this is needed, but seems a good idea)<br /><br />and thats all,have a nice day<br /><br />btw:I will not mention the name of the expert that helped me, because the person can not make public advice because of legal reasons. And things might have been distorted by me translating the advice into this post.'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-32403327403424288222009-09-17T16:14:00.000-07:002009-10-18T23:56:35.310-07:00Handling history failures in data warehousing<span xmlns=""><p>I work with Business Intelligence and use slowly changing history (<a href="http://en.wikipedia.org/wiki/Slowly_changing_dimension">wikipedia</a>) extensively. We have a multi-layered topology and use multiple modeling techniques, including "data-vault", "star-schema" and relational model.<br /></p><p style="MARGIN-LEFT: 36pt">The way we implement history…<br />- It requires one or more identified unique key fields in every source system table<br />- One row in the original source system initially produces two rows in the destination (for each layer in the topology layer)<br />- Change in any field in the source produces a new row in the destination<br />- Deletes in the source creates new rows in the destination<br /></p><p>This way a row in the source system can easily be ten or more in the data warehouse. With modern hardware this is not really a problem, even if we get into the terabytes.<br /></p><h3><span style="font-size:78%;">Problem<br /></span></h3><p>The problem comes when we get errors in the history-mechanisms. A typical problem can be that one static row in a source system could result in a new row in the destination for every load even if there are no real changes.<br /></p><p>If introduced in an early layer, this will spread like the plague in the warehouse (and not like bird flu, either).<br /></p><p>These errors take time to solve and requires expensive talent.<br /></p><h3><span style="font-size:78%;">Solution<br /></span></h3><p>We now have a set of functionality that automatically fixes things up and centralize any misbehaving data in our meta data repository or elsewhere.<br /></p><h3><span style="font-size:78%;">Result </span><br /></h3><p>We can use the talent to solve more problems interesting to the customer and be less proactive (more execution-focused and reactive) on this technical problem because problems will not grow severe.<br /></p><p>; )<br /></p><p>I find data warehousing pretty nice, since there is untouched ground here and there with great promise. Of course, the technical side of these projects is not necessary the hardest to deliver on, but it would be sad if it was the show stopper.<br /></p><p>I also like some metaphors used in the sport, especially the time line (I'll use it next time).</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-47337062104218585112009-08-12T16:19:00.000-07:002009-10-18T23:56:35.310-07:00SQL Server 2008 R2 CTP / Windows 7 x32 – Initial experience<span xmlns=''><p>No problems.<br /></p><p>Hardware is Virtual PC image on Vista/64 laptop.<br /></p><p>Setup is <br /></p><ul><li>No UAC<br /></li><li>1GB RAM for the image<br /></li><li>10 logical disks on three image files, 64Kb cluster size for data drives<br /></li><li>Separated prog files db files, temp files and log files<br /></li></ul><p>I installed SQL Server engine and Integration services and tried some complex SSIS packages created for 2008. I use<br /></p><ul><li>custom data flow components<br /></li><li>script based deployment<br /></li><li>hierarchy of packages (packages calling other packages)<br /></li><li>custom Meta Data Repository<br /></li><li>advanced configuration<br /></li><li>SQL CLR TVF<br /></li><li>…other potentially problematic things<br /></li></ul><p>Packages implements ETL for an enterprise data warehouse.<br/>Things seem to work as is, product looks better than I expected. I get a RC feeling using this thing, if only things performed in the virtual environment, but it still does not, all GUI tools are IMO slow, but this might be because of fragmentation on disk holding vhd. I'll fix fragmentation and have another look. I'll also try an ongoing "appliance project" I have, that demands compatibility with on many parts of the internal SQL Server meta models.<br /></p><p>I have a good feeling with the new SQL Server.<br /></p><p>That's it; have a good one,<br/>Gorm</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-25115265346651432262009-05-25T03:25:00.000-07:002009-10-18T23:56:35.311-07:00The shortest useful program I have written: %1<span xmlns=''><p>This is the shortest program I have written, it is two bytes:<br /></p><p><ExecuteCommand.cmd>%1</ExecuteCommand.cmd><br /></p><p>The task of this program is to execute the parameter as a program, I use this inside Visual Studio on the "Open With"-menu to call cmd-scripts inside the GUI.<br /></p><p>The only program that comes close to this is a 5-byte boot program I wrote for 8086 assembly about twenty years ago.<br /></p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-25896622011565643522009-03-07T17:07:00.000-08:002009-10-18T23:56:35.311-07:00Get gigs of disk space with a single command in Windows Server 2008R2 (and many others)<span xmlns=''><p><span style='color:black; font-family:Courier New; font-size:10pt'>powercfg.exe /hibernate off<br /></span></p><p><span style='color:black; font-family:Courier New; font-size:10pt'>This will remove your hibernation file, which, in 2008 server makes no sense anyway. Gains the amount of disk space as you have installed RAM. I just got another 4GB with this command, and feel like starting a defrag.<br /></span></p><p><span style='color:black; font-family:Courier New; font-size:10pt'>Enjoy, Gorm</span></p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-84567792793116723772008-07-25T03:12:00.000-07:002009-10-18T23:56:35.311-07:00Hyper-V on the laptop (Compaq nc6320)<span xmlns=''><p>I want to run Hyper-visor and other software (SQL Server 2008) on my laptop. I consider running Team Foundation Server, Windows XP, SQL Server versions, old Visual Studio versions, Visio, BizTalk and others inside Hyper-V images on my laptop. At the same time I want the laptop to function as normally (internet explorer, media player, office, etc).<br /></p><p>Hyper-V runs on 64-bit Windows Server 2008. The machine needs to support hardware-assisted virtualization and data execution prevention, and these must be enabled in BIOS.<br /></p><ol><li>Download driver packages for Vista x64 and save on flash-drive<br /></li><li>Install Windows Server 2008<br /></li><li>Rename computer<br /></li><li>start control userpasswords2 and enable automatic logon<br /></li><li><div>Install drivers and enable wireless service (not automatic on server)<br /></div><ol><li>Install Intel Graphics sp37719<br /></li><li>Install "Integrated module with Bluetooth wireless technology for Vista" SP34275 don't care if it says "no bluetooth device"<br /></li><li>Replace wireless driver: \Intel Wireless sp38052a\v64<br /></li><li>Add feature "Wireless..."<br /></li></ol></li><li>Connect to internet<br /></li><li>Start updates<br/>get updates for more programs<br/>automatic<br/>important updates, only<br /></li><li>View available updates<br/>Add optional: Hyper-V<br /></li><li>Install Hyper-V<br /></li><li>Add role Hyper-V<br /></li></ol><p>The machine is now a Hypervisor. Next steps are to get Windows Server 2008 to act more like a client OS.<br /></p><ol><li><div>Install other drivers<br /></div><ol><li>SoundMAX SP36683<br /></li></ol></li><li>Install Feature "Desktop Experience" (Media Player, etc)<br /></li><li>Create shortcut "net start vmms" for starting Hyper-V and "net stop vmms" for stopping Hyper-V<br /></li><li>Set startup for service "Hyper-V Virtual Machine Management" to manual<br /></li><li>stop vmms<br /></li><li>Install Virtual PC 2007 (in case I want wireless network from a vm)<br /></li><li>Enter product key and Activate windows<br /></li></ol><p>Since I have a working version of Windows, this is the time to activate, next step would ideally be to install office. I don't have office handy (am on vacation), so I install the viewers for Word, PowerPoint and Excel<br /></p><ol><li><a href='http://www.microsoft.com/downloads/details.aspx?FamilyId=3657CE88-7CFA-457A-9AEC-F4F827F20CAC&displaylang=en'>Word viewer</a><br /> </li><li><a href='http://www.microsoft.com/downloads/details.aspx?familyid=048DC840-14E1-467D-8DCA-19D2A8FD7485&displaylang=en'>PowerPoint Viewer</a><br /> </li><li><a href='http://www.microsoft.com/downloads/details.aspx?FamilyID=1cd6acf9-ce06-4e1c-8dcf-f33f669dbc3a&DisplayLang=en'>Excel viewer</a><br /> </li></ol><p>I am now ready to install Visual Studio and the servers, first out is SQL Server 2008 rc.<br /></p><ol><li>Set up IIS (with all the iis6 stuff)<br /></li><li>Set up Windows SharePoint Services 3.0 x64 with Service Pack 1 to run "stand-alone"<br /></li><li>(optionally) set up Windows SharePoint Services 3.0 Application Templates – 20070221<br /></li><li>Create user ServiceAccount to run all the sql server services<br /></li><li>Install SQL (complete) with RS in SharePoint integrated mode<br /></li><li>Open firewall (<a href='http://go.microsoft.com/fwlink/?LinkId=94001'>http://go.microsoft.com/fwlink/?LinkId=94001</a>)<br/>open tcp 1433 (engine), 2383 (ssas), 80 (sp), 135 (ssis), 443 (https), 4022 (service broker)<br/>udp 1434 (ssas)<br/>and "notify me when new program blocked"<br /></li></ol><p>Now get all updates.<br /></p><p>Next step is to install Visual Studio, which should be straight-forward.<br /></p><p>OK, I'm off to the beach,<br/>Gorm</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-51980235885256801792008-07-15T14:41:00.000-07:002009-10-18T23:56:35.311-07:00CMD: redirect error output<span xmlns=''><p>I write quite a few batch-files these days and want the error messages to be supressed. I had a vague recollection telling me that this was possible, and found it googling, so here it is:<br /></p><p>"2>nul".<br /></p><p>In a sample:<br /></p><p>Del *.log /f /q 1>nul 2>nul<br /></p><p>Deletes all log-files, hiding output (1>nul) and error output (2>nul). If I remember correctly (3>nul) was hiding printer output. In the old days. You can, of course redirect to files as well:<br /></p><p>Del *.txt /f /q 1>out.txt 2>err.txt<br /></p><p>Del *.log /f /q 1>>out.txt 2>>err.txt<br /></p><p>This sample is supposed to put all the output in out.txt and all the errors in err.txt.<br /></p><p>Gorm<br /></p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-43634671799298085622008-07-13T10:14:00.000-07:002009-10-18T23:56:35.311-07:00ETL top-down 1 – Architecting abstraction layering<span xmlns=''><p>I work at a very exciting BI-program where we have been able to do things right. The project is part of a larger program that contains Business Consulting, Master Data Management and Business Intelligence and, who knows, maybe even Information Lifecycle Management.<br /></p><p>My work on the project includes ETL on the Enterprise Data Warehouse, a central project in the program.<br /></p><p>My trade is, and has for some time been architecting developer. This gives me advantages early in the ETL process when it comes to what I like to call "Abstraction Layering"<br /></p><h4>Abstraction Layering – customer perspective (why)<br /></h4><p>Abstraction layering helps to set the balance between "keeping things open" vs. "delivering as soon as possible".<br /></p><p>For my current project we need to deliver quickly while handling a few issues (from the top of my head):<br /></p><ul><li>Loosely defined long term goal – I think<br /></li><li>Distributed developers, both geographically, and experience-wise<br /></li><li>Many source systems<br /></li><li>Large master data management and other projects changing the environment<br /></li><li>Real-time <br /></li><li>Right time<br /></li><li>Traceability<br /></li></ul><p>When implementation is done, we need to focus on simple measurable tasks. One way to do this is to model the work on well defined levels of abstraction. This way we can design top-down by having the most abstract discussions first, then some intermediate discussions and lastly the implementation details.<br /></p><p>Things we do interesting to ETL includes:<br /></p><ol><li>Selecting reference architecture.<br/>Master Data Management, Hub and spoke EDW with 2G, full archive of source systems, data marts, custom Meta Data Repository.<br /></li><li>Create ETL "horizontal" layering – interfaces and documentation.<br/>Packages take data from one architecture layer to another, grouping functionality and enabling measurability.<br /></li><li>Create ETL "vertical" layering – restrictions and grouping.<br/>Jobs uses "job packages" uses "aggregated packages" that groups functionality in measurable chunks.<br /></li><li>Specify update intervals and delivery.<br/>We plan for nightly job, hourly job and real-time job. Monthly, weekly reports, operational BI and more.<br /></li><li>Define deployment, operations, etc.<br/>Operations implements ITIL, we should interface with it as it matures.<br /></li></ol><p>We deliver.<br /></p><h4>Abstraction Layering – architect perspective (how)<br /></h4><p>Architecting abstraction layering is done to serve the data integration projects by empowering a few roles, these includes:<br /></p><ul><li>Project manager<br/>Work breakdown structure gets easier because one for any integration task have some nice metaphors.<br /></li><li>Developer<br/>Gets assignments with a predictable scope.<br /></li><li>Tester<br/>Can reuse tests because many atomic items has the same scope.<br /></li></ul><p>The architect gets a bag of expression for reuse in the modeling of all the ETL jobs and test templates. It gets possible to create templates for kinds of functionality used often or placeholders for functionality other systems depends on.<br /></p><h4>Abstraction Layering – project manager perspective (what)<br /></h4><p>The developer lead gets measurability and some nice metaphors from the abstraction layering, in our current project they are<br /></p><ul><li>Job<br/>Roughly equivalent to executable, e.g.: "Nightly job".<br /></li><li>Agg<br/>Typically one for each of the different states a job goes through, e.g.: "Source extract"<br /></li><li>Fun<br/>Specific function for an entity, e.g.: "Extract <<customer tables>> from source system X"<br /></li><li>Task / Tsk<br/>Task is part of a function, it moves, changes or collects data. Data in the warehouse knows where it comes from by reference to a such task id, a sample task might be "Extract customer address from source system X".<br /></li></ul><p>The project manager must choose when these metaphors are appropriate, of course.<br /></p><h4>Abstraction Layering – developer perspective (what)<br /></h4><p>When assigned a task a developer can by the name of the delivery see how it fits into the wide picture on three dimensions<br /></p><ul><li>Job/Agg/Fun/Tsk<br/>Dictates the level along the low-level to high-level axis.<br /></li><li>Context<br/>Horizontal layers in the architecture touched, for instance SourceDsa or DmsaDm.<br /></li><li>Function<br/>Typically the 'T' in "ETL".<br /></li></ul><p>Most work repetitive by nature should have current templates controlled by the architect.<br /></p><h4>Abstraction Layering – test perspective (what) <br /></h4><p>Too early to say, but it looks like a combination of the abstract layering, "data haven" and MR shall make test-driven development beyond simple testing possible. Looks like integration and performance testing shall come relatively cheap.<br /></p><h4>Fading out<br /></h4><p>OK, this grew pretty long, looks like I'll have to do more on parts of this later, with more concrete samples. Hope I find the time.<br /></p><p>G.</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-78480844676372494322008-05-01T13:43:00.000-07:002009-10-18T23:56:35.312-07:00Business Intelligence VPC Release 6<span xmlns=''><p>This is very nice indeed. You need at least 2GB RAM and you really want at least 3GB.<br /></p><div><table border='0' style='border-collapse:collapse'><colgroup><col style='width:13px'/><col style='width:271px'/></colgroup><tbody valign='top'><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Overview.docx'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Overview</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part01.exe'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part01.exe</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part02.rar'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part02.rar</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part03.rar'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part03.rar</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part04.rar'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part04.rar</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part05.rar'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part05.rar</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part06.rar'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part06.rar</span></a></p></td></tr><tr><td style='padding-top: 6px; padding-left: 4px; padding-right: 4px'><p><span style='color:#a6a6a6; font-family:Verdana; font-size:6pt'>•</span></p></td><td style='padding-top: 3px'><p><a href='http://download.microsoft.com/download/4/4/d/44ded3bb-6b7c-495b-9ab0-10ec0a4adbd6/Business%20Intelligence%20VPC%20Release%206.part07.rar'><span style='color:#0033cc; font-family:Verdana; font-size:8pt; text-decoration:underline'>Business Intelligence VPC Release 6.part07.rar</span></a></p></td></tr></tbody></table></div><p><br /> </p><p>1) Download and unpack (by running the .exe). This image runs fine from USB disk (for me, anyway), I never use compression (some do).<br /></p><p>2) Download and install Virtual PC 2007 (google is your friend)<br /></p><p>3) Configure your hardware (turn on virtualization in BIOS if you can, and the protection flag too)<br /></p><p>4) Create your own .vmc file (choose new vm in Virtual PC), set RAM size to about 2/3 of your total RAM and remove network adapter. I'm told that the amount of RAM should be dividable with 64M, can't really back that up with knowledge or measurements, but I do it anyway.<br /></p><p>5) Run the image<br /></p><p>6) Log on with "Administrator" and "pass@word1", remember that you have English keyboard in the image (I use Norwegian on the mother system, so " becomes @...)<br /></p><p>7) Enable auto logon (see older post).<br /></p><p>8) Disable System Event Tracker (older post)<br/><br /> </p><p>From now on just turning on the image will get you ready to play with…<br/><br/><br/><strong>Presenter Scripts<br /></strong></p><p>Business Intelligence "All-Up" Presenter Script<br /></p><p> Financial Analyst <br /></p><p> VP of Sales <br /></p><p> Sales Representative<br /></p><p> Chief Financial Officer<br /></p><p> Data Analyst<br /></p><p>2007 Microsoft Office System Launch Demo<br /></p><p>Data Mining Deep Dive<br /></p><p>Enterprise Manufacturing Intelligence Demo<br /></p><p>Banking Demo<br /></p><p>Federal Government Demo<br /></p><p>State & Local Government Demo<br /></p><p>Healthcare Demo<br /></p><p>Oil & Gas Demo<br /></p><p>Retail Demo<br /></p><p>Project REAL Sample Demos<br /></p><p>SQL Server 2005 Demos<br /></p><p>PerformancePoint Launch Demo<br /></p><p>PerformancePoint Budgeting Demo<br /></p><p>PerformancePoint CRM Demo<br /></p><p>PerformancePoint MAP (Monitoring, Analytics, and Planning) Demo<br /></p><p>PerformancePoint Planning Demo<br /></p><p>PerformancePoint Planning Consolidation Demo<br /></p><p>PerformancePoint Management Reporter Demo<br /></p><p>PerformancePoint Sales Forecasting Demo<br /></p><p>PerformancePoint Strategic Planning Demo<br /></p><p>OfficeWriter Demo<br /></p><p>PerformancePoint Hands-On Labs<br /></p><p>New Account Load Demo<br /></p><p><br /> </p><p><strong>Applications Installed<br /></strong></p><p>Windows Server 2003 R2 Service Pack 2<br /></p><p>Internet Explorer 7<br /></p><p>Office Enterprise 2007<br /></p><p>Office Visio 2007<br /></p><p>SharePoint Portal Server 2007<br /></p><p>SQL Server 2005 Service Pack 2 (w/ SSIS, SSAS, SSRS)<br /></p><p>SQL Server 2005 Data Mining Add-ins<br /></p><p>SQL Server 2005 SharePoint Integration Add-in<br /></p><p>ProClarity Analytics Server 6.3<br /></p><p>ProClarity Dashboard Server 6.3<br /></p><p>ProClarity Desktop Professional 6.3<br /></p><p>ProClarity Web Professional 6.3<br /></p><p>PerformancePoint Planning Server 2007<br /></p><p>PerformancePoint Planning Add-In for Excel<br /></p><p>PerformancePoint Planning Business Modeler<br /></p><p>PerformancePoint Monitoring Server<br /></p><p>PerformancePoint Monitoring Scorecard Builder<br /></p><p>OfficeWriter for Word Plug-In<br /></p><p>OfficeWriter for Excel Plug-In<br /></p><p>Project REAL Reference Implementation<br /></p><p>Business Intelligence Metadata Samples<br /></p><p><br /> </p><p><strong>Sample Databases / Cubes<br /></strong></p><p>AdventureWorks<br /></p><p>AdventureWorks_Mfg (for Lean Manufacturing)<br /></p><p>AdventureWorksDW (for AdventureWorks data warehouse)<br /></p><p>Camden (Local Government)<br /></p><p>Contoso (for PerformancePoint Planning)<br /></p><p>EP<br /></p><p>Government_AppDB (PerformancePoint Planning)<br /></p><p>HC<br /></p><p>Healthcare_AppDB (PerformancePoint Planning)<br /></p><p>OSS (Healthcare)<br /></p><p>PDW (for PerformancePoint Planning)<br /></p><p>PeoplSoft Financials_AppDB (PerformancePoint Planning)<br /></p><p>PPS Launch (for PerformancePoint Planning)<br /></p><p>PSFIN_Data_Mart<br /></p><p>REAL_Source_Sample_V6 (for Project REAL)<br /></p><p>REAL_Warehouse_Sample_V6 (for Project REAL)<br /></p><p><br /> </p><p>Personally I'll jump on "Business Intelligence Metadata Samples" first (as it is most relevant to what I do now). Hopefully I'll blog more about that later<br /></p><p><br /> </p><p>Enjoy until August 17. 2009<br/>Gorm Braarvig<br /></p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-38288776503140732932008-05-01T12:42:00.000-07:002009-10-18T23:56:35.312-07:00Enable auto-logon<span xmlns=''><p>Enabling automatic logon for Windows can be done in two ways (that I know of) for installations connected to workgroup and one way for installations connected to domain.<br /></p><p>This works for Windows XP, 2003, Vista, 2008 and most likely NT 4 and Windows 2000. This will not work for Windows 9x/ME.<br/><br /> </p><p><strong>1. The mouse-clicking way (should work with workgroup installations, only)<br /></strong></p><p>Start -> Run -> "control userpasswords2"<br /></p><p>Opens up a GUI where you should be able to turn off "Require users to log on".<br/>This also works on Windows Server 2008 Core.<br/><br /> </p><p><strong>2. Edit registry<br /></strong></p><p>This can be done with regedit.exe or reg.exe (Start -> Run -> "cmd /kreg.exe").<br /></p><p>The values to change or add are under:<br /></p><p><span style='font-family:Courier New'>HKLM\Software\Microsoft\Windows NT\CurrentVersion\Winlogon<br /></span></p><p>They are:<br /></p><p><span style='font-family:Courier New'>AutoAdminLogon REG_SZ "1"<br/>DefaultUserName REG_SZ "Username"<br/>DefaultPassword REG_SZ "Password"<br/>DefaultDomainName REG_SZ "Domain or machinename"<br /></span></p><p>One easy mistake to make here is to define AutoAdminLogon as REG_DWORD (if you are used to program against registry, that is): This will not work; AutoAdminLogon should be of type REG_SZ.<br /></p><p>Obviously you should replace <span style='font-family:Courier New'>"Username" </span>with a username and <span style='font-family:Courier New'>"Password" </span>with a password.<br /></p><p><span style='font-family:Courier New'>"Domain or machinename" </span>should be replaced with name of machine if you log in with a local account (always if you use workgroup) or shorthand domainname if you log in to a domain.<br /></p><p>Warning: Your system administrator might not be impressed with having your username and password here. Always adhere to company policy in this regard.<br /></p><p><br /> </p><p>Remember that if your machine often fails with blue screen, power failure or disk removal (typical when you run virtual from external drive and disconnect the drive), you want to disable the shutdown event tracker warning, see post bellow.<br /></p><p><br /> </p><p>Regards,<br/>Gorm Braarvig<span style='font-family:Courier New'><br/></span></p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-79517741093162589902008-05-01T08:56:00.000-07:002009-10-18T23:56:35.312-07:00Disable shutdown event tracker warning<div><div><div><span xmlns=""><p><strong>Problem: unwanted display of shutdown event tracker<br /></strong></p><p>Working with vpc images, I some times turn off the images without caring about "clean" shut down. This enforces a warning the next time I boot. Since I some times use autologon (later post), this is annoying.<br /></p><p><strong>Solution: Disable "Display Shutdown Event Tracker"<br /></strong></p><p>1. Start "Group Policy Object Editor"<br /></p><br /><a href="http://3.bp.blogspot.com/_LzOglmr9-uE/SBnqdC5XqkI/AAAAAAAAA7U/YFMMMKmq6ig/s1600-h/2008-05-01_1748.png"><img id="BLOGGER_PHOTO_ID_5195441429863180866" style="CURSOR: hand" alt="" src="http://3.bp.blogspot.com/_LzOglmr9-uE/SBnqdC5XqkI/AAAAAAAAA7U/YFMMMKmq6ig/s400/2008-05-01_1748.png" border="0" /></a><br /><p>Start -> Run -> gpedit.msc:<br /><br />Navigate to the setting:<br /></p><a href="http://3.bp.blogspot.com/_LzOglmr9-uE/SBnqIC5XqiI/AAAAAAAAA7E/34sCd9zQgPs/s1600-h/2008-05-01_1754_001.png"><img id="BLOGGER_PHOTO_ID_5195441069085927970" style="CURSOR: hand" alt="" src="http://3.bp.blogspot.com/_LzOglmr9-uE/SBnqIC5XqiI/AAAAAAAAA7E/34sCd9zQgPs/s400/2008-05-01_1754_001.png" border="0" /></a><br /><br /><br /><p>Select "Disabled" and press OK:<br /></p><br /><br /><a href="http://2.bp.blogspot.com/_LzOglmr9-uE/SBnqSy5XqjI/AAAAAAAAA7M/bYeb2W6IESs/s1600-h/2008-05-01_1754.png"><img id="BLOGGER_PHOTO_ID_5195441253769521714" style="CURSOR: hand" alt="" src="http://2.bp.blogspot.com/_LzOglmr9-uE/SBnqSy5XqjI/AAAAAAAAA7M/bYeb2W6IESs/s400/2008-05-01_1754.png" border="0" /></a><br /><p><strong>Result: "Shutdown Event Tracker" is not displayed anymore.<br /></strong></p><br /><p>Gorm Braarvig</p></span></div></div></div>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-89817734174034849532008-04-29T14:31:00.000-07:002009-10-18T23:56:35.312-07:00Coconuts and seeded grapes<a href="http://1.bp.blogspot.com/_LzOglmr9-uE/SBeUuC5XqhI/AAAAAAAAA68/likPj2lxVTs/s1600-h/quadrant+fruit.png"><img style="cursor:pointer; cursor:hand;" src="http://1.bp.blogspot.com/_LzOglmr9-uE/SBeUuC5XqhI/AAAAAAAAA68/likPj2lxVTs/s400/quadrant+fruit.png" border="0" alt=""id="BLOGGER_PHOTO_ID_5194784213967546898" /></a><br /><span xmlns=''><p>If you can't see the picture: stop reading, you are wasting your time.<br/>img src=xksd.com<br /></p><p><br /> </p><p>I used to do "custom bi", creating my own import programs (in a multitude of languages with more technologies than I care to recall), using Excel and print outs for analysis.<br /></p><p>I now do Microsoft BI.<br /></p><p>Loose analysis of Gartners quadrant should place custom BI close to coconuts (bellow to the left of the visible range) and MS BI close to the seeded grapes (moving towards peaches).<br /></p><p><br /> </p><p>Gorm Braarvig</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-90701724525326020622008-04-29T14:12:00.000-07:002009-10-18T23:56:35.312-07:00SQL Server - Drop database, drop current connections first<span xmlns=''><p>Hi!<br /></p><p>I write scripts to generate my database, I want my scripts to run from command line using SQLCMD and (at the same time) use SSMS to edit the scripts and some times run them there. This creates a problem when dropping databases. Databases will not be dropped while they have open connections.<br /></p><p>The solution is easy; here goes:<span style='font-family:Courier New; font-size:9pt'><br /> </span></p><p><span style='font-family:Courier New; font-size:9pt'>ALTER DATABASE x SET SINGLE_USER WITH ROLLBACK IMMEDIATE<br /></span></p><p>In a sample:<span style='color:blue; font-family:Courier New; font-size:10pt'><br /> </span></p><p><span style='font-family:Courier New; font-size:9pt'>PRINT 'Recreate database...'<br/>IF EXISTS (SELECT name FROM sys.databases WHERE name='</span><br /> <span style='font-family:Courier New; font-size:9pt'>Some_DB')<br/>BEGIN<br/> ALTER DATABASE Some_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE<br/> DROP DATABASE Some_DB<br/>END<br/>GO<br/>CREATE DATABASE Some_DB<br/>GO<br /></span></p><p>Gorm Braarvig</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-16024630397939557792008-04-26T04:46:00.000-07:002009-10-18T23:56:35.312-07:00SQL Server 2005: High definition timer (technical)<span xmlns=''><p><strong>Timing with extreme precision</strong><br/><strong><br /> </strong></p><p><strong>I) Preface </strong><br/><br/>I played with writing a generic metadata repository (prototyping) to prepare for ETL at Nortura. I'm not doing the meta data design, but want to understand what we talk about, and to get some experience it's one of two ways I could think of doing it:<br/><br /> </p><ol><li>Adaption - "Monkey see monkey do": Watch other do stuff and try to copy the submodalities (did I write this)<br /></li><li>Brute force - "Trial and error and success": Try, fail, try again, fail a little less, try again, succeed.<br /></li></ol><p>I want to do both. <br /></p><p><strong>a) Brute force</strong><br /> </p><p>I started with the hands-on and created a generic meta data table with support for versioning (of the model) and hierarchy:<br/>(<br/>GenMetumId<br/>,GenMetumType<br/>,GenGrupperendeMetumId<br/>,GenMetumForrigeId -- GenMetumPreviousId<br/>,GenMetumGyldigFra -- GenMetumValidFrom<br/>,Navn -- Name<br/>,EkstraXml<br/>,StartTid -- StartTime<br/>,SluttTid -- EndTime<br/>,Suksess -- Success<br/>,Nyeste -- Newest<br/>) <br/>then I created some stored procedures and tried to insert some rows and update them with status etc. surprise surprise: <br /></p><p>The timestamps were mostly equal! <br /></p><p>BOL reveals that datetime has a resolution of some 3 ms, but this does not account for the results I got. My resolution was 10-15 ms, which to me is an etarnity (I used to do low-level stuff) <br /></p><p>This needs to be fixed. By me. Off hours. (as in hobby).<br/>See solution in point II).<br/><br /> </p><p><strong>b) Adaption</strong><br /> </p><p>I am so lucky that I am to implement ETL on one of Europes largest BI initiatives, so I'll get to be good with operational or technical meta data (I'm not versed in the jargon, yet)<br /></p><p><br /> </p><p><strong>II) Implementation (my favourite)</strong><br /> </p><p>(I know design preceedes implementation in real life, but we're playing here)<br /></p><p><br /> </p><p><strong>a) A better .NET "DateTime.Now"</strong><br /> </p><p>I know DateTime.Now is sufficient (10M ticks per sec). but we are playing, so let's do it properly.<br /></p><p><strong>WINAPI QueryPerformanceCounter</strong><br /> </p><p>I've written some timing routines far back for Windows CE, because the .NET timer routines does not work very well on Pocket PC and Smartphone (resolution is not good). QueryPerformanceCounter and QueryPerformanceFrequency are the WINAPI methods of reading the OEM high resolution counter, which is the lowest level of time-taking on modern computers.<br /></p><p><span style='font-family:Courier New; font-size:9pt'>#region "Timer routines"<br/>[DllImport("kernel32.dll")] static extern public bool QueryPerformanceCounter(out UInt64 i64);<br/>[DllImport("kernel32.dll")] static extern public bool QueryPerformanceFrequency(out UInt64 i64);<br/>static protected UInt64 i64Freq = 0;<br/>public static UInt64 GetNowI64()<br/>{<br/>UInt64 i64;<br/>if (!QueryPerformanceCounter(out i64))<br/>i64 = 0;<br/>return (i64 * 10000000) / GetFreq();<br/>}<br/>public static UInt64 GetFreq()<br/>{<br/>if (0 == i64Freq)<br/>if (!QueryPerformanceFrequency(out i64Freq))<br/>i64Freq = 1;<br/>return i64Freq;<br/>}<br/>protected static DateTime dtBaseline = DateTime.MinValue;<br/>protected static UInt64 i64Baseline = 0;<br/>public static DateTime GetNowDt()<br/>{<br/>UInt64 i64Now = GetNowI64();<br/>DateTime dt;<br/>if (dtBaseline == DateTime.MinValue i64Baseline == 0 i64Baseline > i64Now)<br/>{ // We have no reference point, or reference point is no longer sufficient: create one.<br/>dtBaseline = DateTime.Now;<br/>i64Baseline = i64Now;<br/>dt = dtBaseline;<br/>}<br/>else<br/>{ // We have a sufficient baseline, return baseline plus timespan for diff<br/>dt = new DateTime(dtBaseline.Ticks + (Int64)(i64Now - i64Baseline));<br/>}<br/>return dt;<br/>}<br/>#endregion "Timer routines"<br /></span></p><p>Converting these routines to SQL CLR is a non-brainer.<br /></p><p><span style='font-family:Courier New; font-size:9pt'>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlDateTime GetTime()<br/>{<br/>//return (SqlDateTime)DateTime.Now;<br/>return (SqlDateTime)GetNowDt();<br/>}<br/>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlString GetTimeAsString()<br/>{<br/>return (SqlString)(GetNowDt().ToString("O"));<br/>}<br/>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlInt64 GetFrequency()<br/>{<br/>return (SqlInt64)(Int64)GetFreq();<br/>}<br/>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlInt64 GetTickCount()<br/>{<br/>UInt64 i64;<br/>if (!QueryPerformanceCounter(out i64))<br/>i64 = 0;<br/>return (SqlInt64)(Int64)i64;<br/>}<br/>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlInt64 GetMs()<br/>{<br/>return (SqlInt64)(Int64)GetNowI64();<br/>}<br/>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlInt64 GetTicksPast(Int64 zero)<br/>{<br/>UInt64 uiRes = GetNowI64() - (UInt64)zero;<br/>return (SqlInt64)(Int64)uiRes;<br/>}<br/>[Microsoft.SqlServer.Server.SqlFunction]<br/>public static SqlInt64 GetMicrosPast(Int64 zero)<br/>{<br/>UInt64 uiRes = GetNowI64() / 10;<br/>uiRes -= (UInt64)zero;<br/>return (SqlInt64)(Int64)uiRes;<br/>}<br /></span></p><p><br /> </p><p>SQL Server import:<br /></p><p><br/><span style='font-family:Courier New; font-size:9pt'>CREATE ASSEMBLY MeasureTime AUTHORIZATION [dbo] FROM ... WITH PERMISSION_SET = UNSAFE<br/>CREATE FUNCTION Meta.GetFrequency() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetFrequency<br/>CREATE FUNCTION Meta.GetMicrosPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMicrosPast<br/>CREATE FUNCTION Meta.GetMs() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetMs<br/>CREATE FUNCTION Meta.GetTickCount() RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTickCount<br/>CREATE FUNCTION Meta.GetTicksPast(@zero bigint) RETURNS bigint AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTicksPast<br/>CREATE FUNCTION Meta.GetTime() RETURNS datetime AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTime<br/>CREATE FUNCTION Meta.GetTimeAsString() RETURNS nvarchar(MAX) AS EXTERNAL NAME MeasureTime.UserDefinedFunctions.GetTimeAsString<br /></span></p><p>Testing with some simple queries shows me that it worked. (hope link to image is not broken)<br/><br/><a href='http://2.bp.blogspot.com/_LzOglmr9-uE/SBMoWQb_n3I/AAAAAAAAA60/vLwYA3vDSbQ/s1600-h/sample.png'><img border='0' alt='' src='http://2.bp.blogspot.com/_LzOglmr9-uE/SBMoWQb_n3I/AAAAAAAAA60/vLwYA3vDSbQ/s400/sample.png'/></a><br/><br/><strong>Green arrows</strong><br/>These demonstrates that you can get a higher granularity on timestamps than using GetDate() with Meta.GetTime(). GetDate jumps from ...573 to ...587, while Meta.GetTime() jumps 2, 4 and 6 ms. Two other facts stands out: times differ, so .NET-time seams not equal to T-SQL time, update of 4ms means that the granularity of datetime can't be exactly 3ms.<br/><br/><strong>Blue arrows</strong><br/>GetTicksPast() (10 000 000 pr sec) and GetMicrosPast() (1 000 000 pr sec) works as expected. Calling CLR, getting the time and putting it into a cell in temp looks to take less than 50 microseconds or less than 1/20000 second, which is faster than I expected (but we have very desent laptops).<br/><br/><strong>Red arrows</strong><br/>Putting the same functions multiple times on the same SELECT ensured me that they are called every time (without caching). I particulary liked GetTimeAsString() which measured time in readable format exact with seven digits after secounds... higher granularity than GetDate(), no?<br/><br/><strong>III) What's the business case</strong><br/>You tell me, I just needed to write som code and wanted to share the result.<br/><br/><strong>IV) Where is the code</strong><br/>http://www.codeplex.com/SqlClrMeasureTime<br/>this got kind of nerdy, next one will be better<br/>have a nice week end<br/><br/>Gorm Braarvig</p></span>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-66495185201388849482008-04-26T04:38:00.000-07:002009-10-19T13:16:38.493-07:00SQL Server 2005: High definition timer (technical)<div></div>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-88711665380885991252005-11-24T08:54:00.000-08:002009-10-18T23:56:35.313-07:00Access database from SQL 2005/64<strong>How to read Access database data from 64-bit SQL Server 2005.</strong><br />1) Install SQL Server Express 2005, 32-bit<br />2) Configure both SQL Servers to use distributed queries<br />3) Create a database with a stored procedure in SQL Server Express to handle OLEDB requests<br />4) Link up SQL Server Express to SQL Server<br />5) Test it<br /><br />OK. Let’s get to the details…<br /><br /><strong>1) Install SQL Server Express 2005, 32-bit</strong><br />http://www.microsoft.com/sql/editions/express/default.mspx<br /><br /><strong>2) Configure both SQL Servers to use distributed queries</strong><br />On both servers, run:<br /><span style="font-family:Courier New;font-size:85%;">sp_configure 'show advanced options', 1</span><br /><span style="font-family:Courier New;font-size:85%;">go</span><br /><span style="font-family:Courier New;font-size:85%;">reconfigure</span><br /><span style="font-family:Courier New;font-size:85%;">go</span><br /><span style="font-family:Courier New;font-size:85%;">sp_configure 'Ad Hoc Distributed Queries', 1</span><br /><span style="font-family:Courier New;font-size:85%;">go</span><br /><span style="font-family:Courier New;font-size:85%;">reconfigure</span><br /><span style="font-family:Courier New;font-size:85%;">go</span><br /><br /><strong>3) Create a database with a stored procedure in SQL Server Express to handle OLEDB requests</strong><br />On SQL Server Express, run:<br /><span style="font-family:Courier New;font-size:85%;">USE [master]</span><br /><span style="font-family:Courier New;font-size:85%;">GO</span><br /><span style="font-family:Courier New;font-size:85%;">IF NOT EXISTS (SELECT name FROM sys.databases WHERE name=N'oledb32')</span><br /><span style="font-family:Courier New;font-size:85%;">CREATE DATABASE [oledb32] ON ( NAME=N'oledb32', FILENAME=N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\oledb32.mdf' , SIZE=3072KB , MAXSIZE=UNLIMITED, FILEGROWTH=1024KB )</span><br /><span style="font-family:Courier New;font-size:85%;">LOG ON ( NAME=N'oledb32_log', FILENAME=N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\oledb32_log.ldf' , SIZE=1024KB , MAXSIZE=2048GB , FILEGROWTH=10%)</span><br /><span style="font-family:Courier New;font-size:85%;">GO</span><br /><span style="font-family:Courier New;font-size:85%;">USE [oledb32]</span><br /><span style="font-family:Courier New;font-size:85%;">GO</span><br /><span style="font-family:Courier New;font-size:85%;">IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name=N'oledb32')</span><br /><span style="font-family:Courier New;font-size:85%;">EXEC ('CREATE SCHEMA [oledb32] AUTHORIZATION [dbo]')</span><br /><span style="font-family:Courier New;font-size:85%;">GO</span><br /><span style="font-family:Courier New;font-size:85%;">IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[oledb32].[select_from_openrowset]') AND type in (N'P', N'PC'))</span><br /><span style="font-family:Courier New;font-size:85%;">EXEC dbo.sp_executesql @statement=N'CREATE PROCEDURE [oledb32].[select_from_openrowset](</span><br /><span style="font-family:Courier New;font-size:85%;">@select nvarchar(max)=''SELECT *'',</span><br /><span style="font-family:Courier New;font-size:85%;">@provider nvarchar(max)=''Microsoft.Jet.OLEDB.4.0'',</span><br /><span style="font-family:Courier New;font-size:85%;">@datasource nvarchar(max)='''',</span><br /><span style="font-family:Courier New;font-size:85%;">@user_id nvarchar(max)=''Admin'',</span><br /><span style="font-family:Courier New;font-size:85%;">@password nvarchar(max)='''',</span><br /><span style="font-family:Courier New;font-size:85%;">@provider_string nvarchar(max)=NULL,</span><br /><span style="font-family:Courier New;font-size:85%;">@query_or_object nvarchar(max)='''')</span><br /><span style="font-family:Courier New;font-size:85%;">AS</span><br /><span style="font-family:Courier New;font-size:85%;">BEGIN</span><br /><span style="font-family:Courier New;font-size:85%;">SET NOCOUNT ON;</span><br /><span style="font-family:Courier New;font-size:85%;">DECLARE @sql AS nvarchar(max)</span><br /><span style="font-family:Courier New;font-size:85%;">SET @sql=@select + </span><br /><span style="font-family:Courier New;font-size:85%;">'' FROM OPENROWSET('' +</span><br /><span style="font-family:Courier New;font-size:85%;">'''''''' + @provider + '''''',''</span><br /><span style="font-family:Courier New;font-size:85%;">IF NOT @provider_string IS NULL </span><br /><span style="font-family:Courier New;font-size:85%;">SET @sql=@sql + '''''''' + @provider_string + '''''',''</span><br /><span style="font-family:Courier New;font-size:85%;">ELSE</span><br /><span style="font-family:Courier New;font-size:85%;">SET @sql=@sql + '''''''' + @datasource + '''''';'''''' + @user_id + '''''';'''''' + @password + '''''',''</span><br /><span style="font-family:Courier New;font-size:85%;">SET @sql=@sql + @query_or_object + '')''</span><br /><span style="font-family:Courier New;font-size:85%;">PRINT @sql</span><br /><span style="font-family:Courier New;font-size:85%;">EXEC (@sql)</span><br /><span style="font-family:Courier New;font-size:85%;">END</span><br /><span style="font-family:Courier New;font-size:85%;">' </span><br /><br /><strong>4) Link up SQL Server Express to SQL Server</strong><br />On your 64-bit SQL Server, run:<br /><span style="font-family:Courier New;font-size:85%;">sp_addlinkedserver @server='SqlOledb32', @srvproduct='', @provider='SQLNCLI', @datasrc='(local)\sqlexpress'</span><br /><span style="font-family:Courier New;font-size:85%;">go</span><br /><span style="font-family:Courier New;font-size:85%;">sp_serveroption 'SqlOledb32', 'rpc out', TRUE</span><br /><span style="font-family:Courier New;font-size:85%;">go</span><br /><br /><strong>5) Test it</strong><br />On your 64-bit SQL Server, run:<br /><span style="font-family:Courier New;font-size:85%;">SqlOledb32.oledb32.oledb32.select_from_openrowset @datasource='D:\some_valid_db_name.mdb', </span><br /><span style="font-family:Courier New;font-size:85%;">@query_or_object='some_query_or_table'</span><br /><br />I would like to explain the steps, but time is up.<br />Share and enjoy.'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com3tag:blogger.com,1999:blog-9207785542016401566.post-34271149244278791292005-11-02T03:12:00.000-08:002009-10-18T23:56:35.313-07:00SQL XML (Re: SQL cursors: This was a funny one)Just so that I have said it: Don’t come here for XQuery expert advice, I don’t understand any part of XML. I just copy other people. Still, I am pretty good at copying. I got a reply on my post “SQL cursor: This was a funny one” that was so nice, I think it deserves a new post:<br/><br/>--------------------------------------------<br/><a href="http://www.sqljunkies.com/weblog/mrys">Michael Rys</a><strong><span style="font-family:Arial;">:</span></strong><br/><br/>You mean you could write the following simple expression with XML:<br/><br/>SELECT (SELECT * FROM Customers FOR XML AUTO, ELEMENTS, TYPE).query(<br/>'for $c in /Customers <br/>return <br/><Customers>{<br/>for $p in $c/*<br/>order by local-name($p)<br/>return $p<br/>}</Customers>'<br/>)<br/><br/>:-) <br/><strong><span style="font-family:Arial;">eo</span></strong><a href="http://www.sqljunkies.com/weblog/mrys">Michael Rys</a><strong><span style="font-family:Arial;">:</span></strong><br/>--------------------------------------------<br/><br/>USE AdventureWorks<br/>GO<br/><br/>CREATE PROCEDURE pXmlAllFieldsSorted(@tableName nvarchar(MAX))<br/>AS<br/>DECLARE @sql AS nvarchar(MAX)<br/>SET @sql = 'SELECT (SELECT * FROM ' + @tableName + ' FOR XML AUTO, ELEMENTS, TYPE)' +<br/>'.query(''for $c in /' + @tableName + ' return <' + @tableName + <br/>'>{ for $p in $c/* order by local-name($p) return $p' +<br/>'}</' + @tableName + '>'')'<br/><br/>PRINT @sql<br/>EXEC (@sql)<br/>GO<br/><br/>pXmlAllFieldsSorted 'HumanResources.Employee'<br/>'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com0tag:blogger.com,1999:blog-9207785542016401566.post-1710938848777525392005-10-29T07:57:00.000-07:002009-10-18T23:56:35.313-07:00-web-database-server OH YEAH database-web-server-Oh yeah.<br />Heard about a web database server?<br />Or a database web server?<br />Take a look at mine, a mean machine search server (made for AJAX solutions).<br /><br />Test bed for 32-bit version and a URI for searching for “microsoft” in Norway:<br /><a href="http://searchpalette.com/g/g.html">http://searchpalette.com/g/g.html</a><br /><a href="http://searchpalette.com/SPaletteGorm/0/25/microsoft">http://searchpalette.com/SPaletteGorm/0/25/microsoft</a><br /><br />My 64-bit version searching from 10Million records getting page 25 of people called “Bil” with page size 25.<br /><a href="http://62.89.36.69/spalette/24/25/bil">http://62.89.36.69/spalette/24/25/bil</a><br />Outputing an unbelievable 3151 bytes (scripts not included) for representing 25 persons with address and all phone numbers in a millisecond (before it gets to the router).<br /><br />See it in AJAX action. Type “bil” at <a href="http://firmakatalogen.com/">http://firmakatalogen.com/</a> and hit page 9. The 1400-1500 bytes needed to represent the hits should come more or less instantly after you let go of the mouse button. If not, it is a network latency problem.<br /><br />The server, working on a XEON 64 takes up 50MB RAM for serving 5GB of raw data. I guess I could have a hundred of them on the same (single processor) machine. Integration is done with SQL Server 2005 (and has been done for about a year now in production, hope I am not breaking NDAs etc. etc. but it was the best solution, so I did it)<br /><br />Well, my 64-bit <u>webservices</u> database server will be even more fun.<br /><br />Thank you, Microsoft and Intel.'http://www.blogger.com/profile/01012943142843553939noreply@blogger.com1