Tuesday, October 15, 2013

Random values in Select Query to hide information

There are situations when manager or BA want you to hide certain information in a report, there are a lot of programming ways to achieve this, for laziness it is also doable in SQL.

E.g. I have a list of sensitive customer name and their sales figure, I would like to show the other party how many sales I made, however hiding all the names.

the idea came to my mind is, using a string 'Customer' and a random number from 1-10 combine to get a random name such as 'Customer5', etc.


 'Customer' + CAST((CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1) as varchar(2))

Above provides a random number between 0 - 1, use it as root times 10 to get a random number between  1- 10. hence end up having 'Customer1' - 'Customer10' in the selection list.

Wednesday, July 17, 2013

Unlock System administrator account in SQL 2008 R2

If for some reason the system admin account got locked out of SQL server, you may take the following steps to unlock it.

Step 1 Add a new user using current ‘Windows Login user name’, e.g. PBTECH\Michael , then  Stop SQL server

Step 2 Change the server into single user mode, by add “;-m” at the end of path under SQL server property window advanced tab

Step 3: Save & restart, login to SSMS with windows authentication

Step 4: Give the new user added in step 1 System admin permission by executing :
sp_addsrvrolemember [domain\username],'sysadmin'

Step 5: Under security- login find the locked user, reset password and untick the lock, save and close.

Step 5: reverse step of step2, change SQL server back to multi user mode

The SA should be unlocked.

Thursday, April 18, 2013

To Make your window form show as topmost window in VB.Net

In VB6 we used to use API function like :
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

the equivalent function in .Net is something like:

<DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
Private Shared Function ShowWindow(ByVal hwnd As IntPtr, ByVal nCmdShow As  b      ShowWindowCommands) As Boolean
End Function

To make your window to topmost of your screen, there're a few simple steps to take:

  • Get your process ID when you call the program
Private Function GetProcessID() as Integer
    Dim ProcessProperties As New ProcessStartInfo
    ProcessProperties.FileName = "test.exe"
    ProcessProperties.Arguments = ""
    Dim myProcess As Process = Process.Start(ProcessProperties)
    Return myProcess.ID
End Function

  • Use the ProcessID to get window's Handle and set it to topmost

'SET API And variables
   <DllImport("user32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
Private Shared Function SetWindowPos(ByVal hWnd As IntPtr, ByVal hWndInsertAfter As IntPtr, ByVal X As Integer, ByVal Y As Integer, ByVal cx As Integer, ByVal cy As Integer, ByVal uFlags As Integer) As Boolean
    End Function

    Private Const SWP_NOSIZE As Integer = &H1
    Private Const SWP_NOMOVE As Integer = &H2

    Private Shared ReadOnly HWND_TOPMOST As New IntPtr(-1)
    Private Shared ReadOnly HWND_NOTOPMOST As New IntPtr(-2)

'Set  Window Pos to top most
  Public Shared Sub SwitchToProcess(ByVal PID As Integer)
        Dim hWnd As Integer
        hWnd = CType(System.Diagnostics.Process.GetProcessById(PID).MainWindowHandle, Integer)
        'ShowWindow(hWnd, SW_MAX)
        'ShowWindow(hWnd, ShowWindowCommands.ShowNoActivate)
        SetWindowPos(hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
  End Sub

Wednesday, February 6, 2013

Excel 2010 Querying(parameterised) SQL

Simple reports for end user may require you to run some query on an excel spreasheet, so that end user can refresh and get live data as they want and when they want.

Couple ways to do:
Create a View in SQL server, save it, then Open up Excel 2010

Choose 'From Microsoft Query' (You can, if you wish, to choose SQL server, what i noticed was you cannot change your query or put dynamic parameters if you choose that)

Point to the database server of your choice, Click 'Ok' then Click Cancel, it'll pop up asking whether you want to enter query editing window, click 'Yes'.
Then choose the table you wish to display in SQL Query edit window, you can then add criteria and modify the query and look of the output. even add parameters.

After you done with your query editing, click 'File --> Return Data to Excel' to go back to Excel main window.(Note: if you added parameters in your query, you'll need to fresh the connection to be able to set the parameter look up value, i.e. a cell or range of cells.)

User will be able to simply change the parameter to get the data refreshed. Easy as~

Monday, January 14, 2013

Auto Refreshing Excel data connection without open up excel

Ever wanted to get a bunch of pivot table data on excel files refreshed without the need of opening the file at all?
Here's a solution,
download the zip and extract. To use, just create a batch file and execute the .exe file on command line.


Import Excel into SQL 2008 64 bit

Last couple days, I was working on a solution to import some excel file into SQL and then generate report from the server.

There're a lot of ways to import an excel file, I tried a few approaches, unfortunately due to lack of permission to muck around with server settings in the company, I was unable to proceed with most of these approaches.

Approach 1 : (ISSUE: 32 bit office installed, 64bit SQL installed)

Step 1. Install 64-bit Microsoft.ACE.OLEDB.12.0 driver  (Failed on this one, Don't have server admin rights to install 64 bit version drivers.)

Step 2. Configure ACE OLE DB properties

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

Step 3. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider

SELECT * INTO #ExcelTemp FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=V:\database\Export\Service KPI Report 2013.xls','select * from [2013$]')

I did run step 2&3 on my local machine (XP sp3, 32bit SQL 2008 R2), work like a charm.
Possible issues:
you may come across:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

This will fix it :

sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;

Also this error is common:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


USE [master]
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1

-------------------------------SEPERATOR ------------------------------------------------------------

Approach 2: LINKED Server
Same issue with the first approach, 32 bit office and 64 bit SQL. on all 32 bit servers, everything goes smoothly, where as 64 bit SQL will have errors and not be able to proceed.

EXEC sp_addlinkedserver
    @server = 'ExcelServer',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\test.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

-------------------------------SEPERATOR ------------------------------------------------------------
Approach 3: SSIS
Easy enough, end up doing this.