SQL Server 2008 Ad Hoc Queries Distributed

Configure ad hoc queries
(this needs to be done on the server you want to run the queries from).

sp_configure 'show advanced options', 1
reconfigure 

sp_configure 'Ad Hoc Distributed Queries', 1  
reconfigure 

I normally set these back to 0 afterwards as I use ad hoc queries rarely.

Sample ad hoc query 

SELECT RS.*
FROM OPENROWSET(
  'SQLNCLI',
  'SERVER=arkleserverws,6521;UID=username;PWD=password;DATABASE=dbname;',
  'SELECT Top (10) * FROM dbname.dbo.tablename'
) AS RS;





Visual Studio Memory Leak D'OH

Another memory leak.
Just installed .NET mem profiler and locating now how to fix.

Update to this blog.
Turns out memory leak was due to some tabs in a frequently opened form.
I decided to hide the tabs when they were not in use.  As a result the .NET framework did not garbage collect them when the tab was closed.  So I had to manually dispose them on form closing.

I have no idea why the .NET framework would assume these are to be kept in memory just because they aren't visible.

??????????????????

.NET FlowLayoutPanel Large Vertical Gaps (unwanted)

Ran into this issue and found a post to fix it thanks to LewisG (as this was doing my head in).


http://www.windows-tech.info/3/c9cb6b44b9529869.php


When two controls that would fit width-wise are added to a Flowlayoutpanel, and the first one has FlowBreak = true, the FlowLayoutPanel inserts a significant amount of unwanted vertical blank space between the controls.

Even in design time it can be reproduced. Steps are:

- Create a new form

- Add a flow layout panel

- Add a label

- Add a button

- Set flowbreak=true on the label

- Resize the button width-wise. It jumps up and down, the unwanted blank space appears and dissappears, depending on whether it is large enough to fit alongside the label or not.

I'm really surprised noone has made a reference to this bug in msdn forums, or any of the usual search engines.

We have an application that creates controls dynamically, and the client is hammering us with this bug. Help is appreciated.

Re: Windows Forms General Flowlayoutpanel flowbreak bug. It creates unwanted empty space

LewisG 

I realize this issue is pretty old, but I ran into this bug myself and I figured out a workaround I thought might be useful to someone else.

The vertical size of the gap seems to be the same as the height of the control that follows after the flow break. So, I just add a panel of Size(0,0) with no margins after the flow break. That gets rid of the gap.

Using File Streams SQL Server 2008

Had a bit of trouble getting these up and running. A lot of the samples out there missed steps are did them in wrong order (hopefully below is full list of steps).

Enable the file stream on the server:
http://msdn.microsoft.com/en-us/library/cc645923.aspx
To enable and change FILESTREAM settings

To enable and change FILESTREAM settings

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
   
2. In the list of services, right-click SQL Server Services, and then click Open.
   
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
   
4. Right-click the instance, and then click Properties.
   
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
   
6. Select the Enable FILESTREAM for Transact-SQL access check box.
   
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
   
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
   
9. Click Apply.
   
10. In SQL Server Management Studio, click New Query to display the Query Editor.
    
11. In Query Editor, enter the following Transact-SQL code:
    
EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Or use 1 for T-SQL access only


Enable Filestream in your database
ALTER DATABASE ArkleStream ADD FILEGROUP ArkleStream_Filegroup CONTAINS FILESTREAM

ALTER DATABASE ArkleStream ADD FILE  (
    NAME = ArkleStream_Filegroup
    ,FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\ArkleStream_Filegroup_Data'
    )
TO FILEGROUP ArkleStream_Filegroup

Create a table using a filesteam column
CREATE TABLE [dbo].[TestFileStreamTable](
    [SomeOtherColumn] [varchar](100) NULL,
    [FileStreamColumn] [varbinary](max) FILESTREAM  NULL,
    [UniqueID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Test] [varbinary](max) NULL,
UNIQUE NONCLUSTERED
(
    [UniqueID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [ArkleStream_Filegroup]