Friday, March 27, 2015

Creating a table using Power Shell and SQL Server Management Objects

Review this previous post

In the above post you will get an introduction to Power Shell and SQL Server
Management Objects. You also learn how to access the SQL Server and carry out a
few tasks like listing out all the databases.

Review this post for creating a database using Power Shell and SQL Server
Management Objects:

In the above post you will learn how to create a new database in SQL Server using
SMO and Power Shell. Using Transact-SQL or the SSMS you can easily create a
database. Using PowerShell is another option to create a database using a script.

Having created a database you would want to create tables in the database.

This post shows how you may create a table. This script has been tested to work with
Power Shell ver. 4.0.

Launch SQLPS from Start | Run and type-in SQLPS and click OK.


Let us get connected to the instance of SQL Server (RegencyPark) on this computer
(Hodentek8) by (declaring) running the command shown.

PS SQLSERVER:\>  $server= new-object

Let us create a database named 'Feb6'.
Declare a name for the database as shown here:

Now create a database object variable in the instance using SMO:
PS SQLSERVER:\> $db=new-object

Now create the database using the create () function.
PS SQLSERVER:\>  $db.Create()
When you run the above command you will have created a database called Feb6 in the
SQL Server instance as shown (this may take a few seconds and you will not get any response except if there are errors) in SQL Server Management Studio:


In creating the table run the following lines of command in PS SQLSERVER:\>:

We declare a name for the table as 'SmoCustomers' and instantiate the table object
by running the following:
$tbname = "SmoCustomers"
$tb = new-object ($db, $tbname)

We assume the table to have three columns (fields) named 'Field1, Field2 and
Field3. Field1 is of int data type and the two others are nvarchar(num) data type.

Typically instantiate a column object and its data type and add that column to the
$tb.Columns. Carry out this for the three columns by running these lines in PS

$colField1 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field1")
 $colField1.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::Int

$colField2 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field2")
 $colField2.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)

$colField3 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field3")
$colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)

Now create the table by running the following code:
$tb.create ()

Here is the complete line-by-line code:

Again do not look out for a response but check for the table in Feb6 database you
created earlier.


Monday, March 16, 2015

Manage your investments with SQL Server 2014 backend using Abacus

Manage your investments with SQL Server 2014 backend using Abacus
Prism Cybersoft has launched an Azure Cloud based investment accounting software with SQL Server 2014 backend. It will cost you $600/yr.

The novelty is it is cloud based. Is security a problem, Perhaps?
With abacus you can monitor your investment portfolio across various asset classes like equities, derivatives, mutual funds etc. and generate various kinds of reports. Managing it is easy from manual to file uploads or direct integration with stock brokers back office.
According to the CEO of Abacus,
“Abacus allows investors to monitor their financial assets across various asset classes like equities, derivatives, mutual funds, and bank fixed deposits. It also generates various financial statements like balance sheet and profit and loss account,”
More here:

Wednesday, March 11, 2015

Latest: Microsoft PHP drivers for SQL Servers

Microsoft Drivers 3.2, 3.1, 3.0, and 2.0 for PHP for SQL Server provide connectivity to Microsoft SQL Server from PHP applications.

If you are interested in yet older versions of PHP review these posts:

You may install PHP from XAMPP here:

Recently 3/7/2015 Microsoft released Php drivers for SQL Servers. This single download consists of a number of drivers. Details area as shown:

These drivers are PHP 5 extensions that allow you to connect to SQL Server from PHP applications/scripts.

The SQLSRV3X extension provides a procedural interface while the PDO_SQLSRV extension implements PDO for accessing data in all editions of SQL Server 2005 and later (versions 3.2 and 3.1 require SQL Server 2008 and later).

The following OS are supported:
Windows 7, Windows 8, Windows 8.1, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2

Of course you need PHP 5x to make use of these drivers.

Driver / PHP versions support:
  • Version 3.2 supports PHP 5.6, 5.5, and 5.4
  • Version 3.1 supports PHP 5.5 and 5.4
  • Version 3.0 supports PHP 5.4.

  • When you run the executable (SQLSRV32.exe for example) you will be asked to point to the path of the extension. You may have multiple version related paths as shown here on Windows 8.1 here:

    C:\Program Files (x86)\PHP\v5.6  
    C:\Program Files (x86)\PHP\v5.3

    Download the driver to mathc your PHP version here:

    What is the latest MySQL Version available?

    The latest version for Windows (x86) version of MSI installer can be found here: ...