Sunday, November 14, 2010

Attempting to program C++ once more

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.

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.

Compiling a DLL and using it in SQL Server (SQL Server 2011 CTP1) was harder than it should, I actually need to either…
- find an installation of SQL Server 2005x64 and steal a lib-file or
- use LoadLibrary and GetProcAddress (which is a lot of work) 
…to be able to do this.

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
- the number of parameters can not be variable and
- performance is, of course, not as good as what I can do with native code
I haven’t given up but need to devert solution until I write a desent parallell bulk-loader, I think,

have a good one
/G

Friday, July 2, 2010

Excel: read data from stored procedure, with great difficulty

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

  • Restarting Excel
  • Restarting my computer
  • Reducing my environment
  • Reducing my code
    • This finally gave me a positive result
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:

Use "SET NOCOUNT ON" in my Interface procedure (it calls other procedures, but these does not need NOCOUNT.

Here is a simple (working standalone) sample for SQL Server 2008 and Excel 2007/2010:

<tsql>
/* Create some temp db */

CREATE
DATABASE tvfTest

GO

USE tvfTest

GO

CREATE
TYPE TTable AS
TABLE (SomeKey INT, SomeValue SYSNAME
)

GO
--*/



/* Create "Internal" proc that takes tvp */

CREATE
PROC Internal @t TTABLE READONLY
AS

SET
NOCOUNT
ON
-- Not imnportant here, but removes extra noice.

SELECT SomeKey=0,SomeValue='Zero'
UNION
ALL
SELECT
*
FROM @t

GO
--*/



/* Create "Interface" proc that uses tvp and return result set */

CREATE
PROC Interface @test INT=0
AS

DECLARE @t TTable

IF @test=1

BEGIN

INSERT @t VALUES(1,'One')
-- Fails (because rowcount is returned and it indicates that write operation performed?)

END

ELSE
IF @test=2

BEGIN
-- Same as above, but "SET NOCOUNT ON" helps avoiding error!

SET
NOCOUNT
ON

INSERT @t VALUES(1,'One'
)

END

EXEC Internal

@t

GO
--*/



/* Call with tvp and test */

EXEC Interface

0
-- 0,Zero -- Command(s) completed successfully.

EXEC Interface

1
-- 0,Zero;1,One -- (1 row(s) affected)

EXEC Interface

2
-- 0,Zero;1,One -- Command(s) completed successfully.

GO
--*/



/* Map up in Excel */

</tsql>

<excel>

Without insert
SomeKey
SomeValue
0
Zero
With insert without NOCOUNT
SomeKey
SomeValue
With insert with NOCOUNT
SomeKey
SomeValue
0
Zero
1
One
</excel>

<excel_description>
Add three external TSQL connections with command type SQL and commands:

- EXEC Interface 0

- EXEC Interface 1

- EXEC Interface 2


</excel_description>

The middle one (Interface 1) should not work because it returns a non-empty "rowcount"!

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.

Have a nice day
/G

Monday, May 24, 2010

Enable host to image network in Hyper-V


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.
That is why I prefer using the host to access the image through the network stack with Remote Desktop or just the browser.
Here are the steps to set up the communication:

  1. Add a virtual adapter
    In Hyper-V this is accomplished on the host in "Virtual Network Manager" by adding an "Internal" network I usually name "example.com"



  2. Set up adapter inside the image
    Ensuring the image is shut down (and not in save state mode) I add "Network Adapter" and select the net created in step 1.
    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.
    Last I turn off firewall completely for this card (steps depends).



  3. Set up adapter on host
    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.
    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:

    • intranet.contoso.com 192.0.2.110

    • www.contoso.com 192.0.2.110

    • contoso.com 192.0.2.110

    • contosoApp 192.0.2.110




  4. Test
    In host start cmd and "ping 192.0.2.110"
    In image start cmd and "ping 192.0.2.100"
    From host start mstsc and open "contosoApp"
    From host start iexplore and navigate "intranet.contoso.com"

  5. Result
    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.