Creating a table using Power Shell and SQL Server Management Objects

Review this previous post    http://hodentekmsss.blogspot.com/2014/11/accessing--
sql-server-via-smo-using.html

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:
http://hodentekmsss.blogspot.com/2015/02/creating-database-using-power-shell-
and.html

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.

PS_SMOTable01.png

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 Microsoft.Sqlserver.management.smo.server
'Hodentek8\RegencyPark'

Let us create a database named 'Feb6'.
Declare a name for the database as shown here:
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK> $dbname= "Feb6"

Now create a database object variable in the instance using SMO:
PS SQLSERVER:\> $db=new-object  Microsoft.Sqlserver.management.smo.Database
($server,$dbname)

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:

PS_SMOTable02.png

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  Microsoft.Sqlserver.management.smo.Table ($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
SQLSERVER:\>.

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

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

$colField3 = New-Object Microsoft.SqlServer.Management.Smo.Column ($tb, "Field3")
$colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)
$tb.Columns.Add($colField3)
Now create the table by running the following code:
$tb.create ()

 

Here is the complete line-by-line code:

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

PS_SMOTable04.png

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:
http://www.prism.in/services.html
http://www.infotechlead.com/cloud/microsoft-azure-powers-investment-software-abacus-28750

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:
http://hodentekmsss.blogspot.com/2014/11/php-driver-for-sql-server-released.html

http://hodentekmsss.blogspot.com/2010/05/phpbb-brings-interoperability-with-sql.html

You may install PHP from XAMPP here:
http://www.windows8downloads.com/win8-xampp.html

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:
http://www.microsoft.com/en-us/download/details.aspx?id=20098&WT.mc_id=Blog_SQL_Announce_DI