Thursday 24 January 2013

Enabling MS SQL Extensions in WAMP

While working on a problem recently, I needed to enable the MS SQL extensions in WAMP. It turned out to be more complicated than simply hitting the checkbox in the Extensions menu…

I found this great post which explained the process.. http://forum.ragezone.com/f724/get-wamp-work-mssql-673301/

The basic Idea is;

  1. Download ntwdblib.dll: HERE
  2. Click on the WAMP icon -> PHP -> PHP Extensions then check “php_mssql” and “php_pdo_mssql”. (Wamp will restart and give you few errors, ignore them)
  3. Restart WAMP one more time to ensure settings are saved.
  4. Finally, place ntwdblib file in the two following directories:
    wamp\bin\php\php5.3.1 (Or the directory relating to whichever version of PHP you are currently using)
    wamp\bin\apache\apache2.2.11\bin (Or the directory relating to whichever version of Apache you are currently using)
  5. Restart wampserver, and you're finished!

Hopefully this helps somebody else too!

WPF MVVM ComboBox Binding to XML Data.

Once again, while browsing StackOverflow.com, I came across a question from a user who needed to fix their ComboBox Binding. The user in question was Binding XML data in Key/Value pairs to the combo box, displaying the Key and wanted to retrieve the Value.

This was achieved using the following example;

My XML Test Data was saved as XMLFile1.xml;

<?xml version="1.0" encoding="utf-8"?>
<Colours>
  <Colour ID="1" Name="None" />
  <Colour ID="2" Name="Red" />
  <Colour ID="3" Name="White" />
</Colours>


My XAML was;






<Window x:Class="MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="MainWindow" Height="74" Width="150">
    <Window.Resources>
        <XmlDataProvider x:Key="ColourData" Source="/XMLFile1.xml" XPath="Colours" />
    </Window.Resources>
    <Grid>
        <ComboBox x:Name="cmbColour" HorizontalAlignment="Left" Margin="10,10,0,0" 
                  VerticalAlignment="Top" Width="120" DisplayMemberPath="@ID" 
                  ItemsSource="{Binding Source={StaticResource ColourData}, XPath=./Colour}" 
                  SelectedValuePath="@Name"
                  SelectedValue="{Binding SelectedColourValue}"
                  />
    </Grid>
</Window>


And finally my ViewModel was;






Imports System.ComponentModel
Imports System.Xml
 
Class MainWindow : Implements INotifyPropertyChanged
 
    Private _SelectedColourValue As String
    Public Property SelectedColourValue As String
        Get
            Return _SelectedColourValue
 
        End Get
        Set(value As String)
 
            If value.Equals(_SelectedColourValue) = False Then
 
                _SelectedColourValue = value
                RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs("SelectedColourValue"))
 
            End If
 
        End Set

End Property


    Public Event PropertyChanged(sender As Object, e As PropertyChangedEventArgs) Implements INotifyPropertyChanged.PropertyChanged
 
    Public Sub New()
 
        ' This call is required by the designer.
        InitializeComponent()
 
        ' Add any initialization after the InitializeComponent() call.
        me.DataContext = Me
 
    End Sub
End Class


This successfully displayed the ID property in the ComboBox, and when selected the “SelectedColourValue” Property held the Value.

Exporting MS SQL Table Data using PHP

I regularly export table data from MySQL using PHP, however a question on StackOverflow.com came up where the user wanted to export data from MS SQL rather than MySQL.

Handily, PHP has support for many of the same functions for MS SQL as it does for MySQL.

So with a bit of reworking I was able to come up with the following script;

<?php
    
    $servername = '.\SQLSERVER';
    $username = 'databaseuser';
    $password = 'database';
    $database = 'CriticalPath';
    $table = 'Users';
 
    if (!function_exists('mssql_fetch_row'))
    {
        $output .= "MSSQL functions are not available.<br />\n";
        exit;
    }
 
    // Connect database
    if(!$dbconnect = mssql_connect($servername, $username, $password))
    {
        $output .= "Connection failed to the host 'localhost'.";
        exit;
    } // if
        
    if (!mssql_select_db($database))
    {
        $output .= "Cannot connect to database '$database'";
        exit;
    } // if
    
    $result=mssql_query("select * from $table");
    
    $out = '';
 
    $columns = mssql_num_fields($result);    
 
    for ($i = 0; $i < $columns; ++$i) {
        // Fetch the field information
        $field = mssql_fetch_field($result, $i);
        $out .= '"'.$field->name.'",';
    }
       
    $out .= "\r";
    
    // Add all values in the table to $out.
    while ($l = mssql_fetch_array($result)) 
    {
       for ($i = 0; $i < $columns; $i++) 
       {
          $out .='"'.$l["$i"].'",';
       }
 
       $out .= "\r";
    }
    
    // Open file export.csv.
    $f = fopen ('export.csv','w');
    
    // Put all values from $out to export.csv.
    fputs($f, $out);
    fclose($f);
    
    header('Content-type: application/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    readfile('export.csv');
 
    mssql_free_result($result);
 
?>

Monday 21 January 2013

SQL Server 2008 - Retrieving a Users’ Privileges for all tables in a databse

I recently answered a question on StackOverflow.com by a user who needed to retrieve a list of a given users Privileges for each Table in a given Database.

After some trial an error I ended up with the following T-SQL Script;

declare @Proc nvarchar(50)
declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)
 
select @RowCnt = 1
select @Proc = 'SELECT * from fn_my_permissions'
 
declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))
insert into @Import (TableName) select name from sys.Tables
 
declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))
 
select @MaxRows=count(*) from @Import
 
while @RowCnt <= @MaxRows
begin
    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt 
    insert into @Output exec sp_executesql @ExecSql
    Select @RowCnt = @RowCnt + 1
end
 
select * from @Output


This will give the Permissions for the current user. If you want to find the permissions for a given user then use the following script;



EXECUTE AS LOGIN = N'username'
GO
 
declare @Proc nvarchar(50)
declare @RowCnt int
declare @MaxRows int
declare @ExecSql nvarchar(255)
 
select @RowCnt = 1
 
select @Proc = 'SELECT * from fn_my_permissions'
 
declare @Import table (rownum int IDENTITY (1, 1) Primary key NOT NULL , TableName varchar(50))
insert into @Import (TableName) select name from sys.Tables
 
declare @Output table (entity_name varchar(50), subentity_name varchar(50), permission_name varchar(50))
 
select @MaxRows=count(*) from @Import
 
while @RowCnt <= @MaxRows
begin
 
    select @ExecSql = @Proc + '(N''' + TableName + ''', N''OBJECT'') where subentity_name = ''''' from @Import where rownum = @RowCnt 
    insert into @Output exec sp_executesql @ExecSql
 
    Select @RowCnt = @RowCnt + 1
end
 
select * from @Output
 
GO
 
REVERT
GO


These scripts are based on;

fn_my_permissions procedure: 

This returns a list of permissions for the current user, for the given table in a database. Here we are executing the following code for each table in the database;



SELECT * from fn_my_permissions(N'tablename', N'OBJECT') where subentity_name = ''


More information can be found at http://msdn.microsoft.com/en-us/library/ms176097.aspx and http://books.google.co.uk/books?id=5_AEiJXbyiEC&pg=PA452&lpg=PA452&dq=tsql+HAS_perms_by_name+for+each+table&source=bl&ots=tSArrzcfa4&sig=HmPsF9vEFQC4g2hC8lWvCmJ8qao&hl=en&sa=X&ei=Nrr5UKvkCKLB0gXhtYD4Ag&ved=0CEEQ6AEwAg#v=onepage&q=tsql%20HAS_perms_by_name%20for%20each%20table&f=false and http://stackoverflow.com/a/497368/1305169


The sys.tables collection: 

This contains a list of tables for the given database. More information can be found at http://blog.sqlauthority.com/2007/06/26/sql-server-2005-list-all-tables-of-database/


A Simple Loop:

In order to loop through all of the tables, I modified a handy script I found here; http://weblogs.aspnet05.orcsweb.com/jgalloway/archive/2006/04/12/442618.aspx