Thursday, November 27, 2014

Things you learrn hands on in my book on SSRS 2012

With over 65 hands-on exercises
  • Install SQL Server 2012; SharePoint Server 2010; and configure Reporting Services for both native and SharePoint Integrated mode on Windows 7.
  • Install SQL Server Data Tools and author reports both local and remote. Deploy reports to report servers.
  • Develop desktop and web applications using Report Viewer Control.
  • Author different kinds of reports with the latest gadgets working with Report Builder 3.0
  • Manage access to the report server; report scheduling; report delivery; report uploads and downloads with Report Manager
  • Create Tabular Models with SQL Server Analysis Services and deploy them to SQL Server Analysis Server using SQL Server Data Tools.
  • Author Power View Reports from your SharePoint Site and export them as PowerPoint Files.

Tuesday, November 25, 2014

How to overcome error while importing the SQLPS module into Powershell?

In the previous post you learnt how to use SQL Server PowerShell Provider using the SQLPS.exe on your computer.

You could also run the SQL Server PowerShell Provider by importing the SQLPS module into PowerShell. It may produce the following error as shown:
--------------------
PS C:\Windows\system32> Import-Module “sqlps” -DisableNameChecking

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' fa

iled with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with

the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
 
Now how do you overcome this error.

The error is telling that the RPC server is unavailable. However you would notice that this service is started automatically on your machine and in fact there is no Start/Stop for this service.

 
 
However you notice that the RPCLocator service is manual and it can be started and stopped. Start this service as shown.
 

 
Now Import the module again from PowerShell prompt as shown in the next listing:
 
PS SQLSERVER:\> Import-Module “sqlps” -DisableNameChecking
PS SQLSERVER:\>

As you can see the exception is not raised.
Now you can happily look at the SQL Server as shown:
 
PS SQLSERVER:\> cd SQL
PS SQLSERVER:\SQL> dir

MachineName
-----------
HODENTEK8

PS SQLSERVER:\SQL> cd LocalHost
PS SQLSERVER:\SQL\LocalHost> dir
Instance Name
-------------
REGENCYPARK

Monday, November 24, 2014

Accessing SQL Server via SMO using PowerShell - A quick workout

In order to understand SQL Server the surest way is to get to understand the SQL Server Management Objects known by its acronym 'SMO'. You can programmatically access and manage SQL Servers with this object model.

I have described with code listing as to how you may access Azure SQL Database in my comprehensive Azure SQL Database related book.
"A step-by-step procedure to connect to SQL Azure using SMO" is described on page 119, Chapter 3 of this book.
Microsoft SQL Azure: Enterprise Application Development, Packt Publishing,  Dec 2010.


SMO is the foundation using which the SSMS was built. The name space for the SMO is Microsoft.SqlServer.SMO.

In a previous post, I showed you how to run PowerShell in SQL Server Management Studio. Here is a quick screen shot to refresh your memory.

As you can imagine the SMO object model is quite large and starts off with the Server at the top. This image shows only a part of this object model.



You should be able to access the complete model at this link:
http://msdn.microsoft.com/en-us/library/ms162209.aspx

In order to access SMO using PowerShell you need to load the assembly as shown in the next image.


With the following you can get all the members as shown.

Once you do that you can access the top-level object, the Server.

SMO is object based and hierarchical. You define the SQL Server as a new object using the next listing where "servername" is your computer or the server and instance name is the SQL Server instance. In the present listing it is "Hodentek\RegencyPark"
-----------
PS C:\Users\Jayaram> $sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "Hodentek8\RegencyPark"

Since you defined  the server, you can find its version as shown in the following listing:
----
PS C:\Users\Jayaram> $sqlServer.version
Major  Minor  Build  Revision
-----  -----  -----  --------
11     0      2218   -1


---------------
The next two lines of code finds the status of the server as well as the root directory of the instance. 
-------------
PS C:\Users\Jayaram> $sqlserver.status
Online
PS C:\Users\Jayaram> $sqlserver.RootDirectory
c:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL
----------
Accessing the databases:

Databases are also objects and you need declare a variable for them as shown here:
----
PS C:\Users\Jayaram> $sqlDatabase=$sqlServer.Databases
----
The above declaration will not display a response but you have indeed declared the variable $sqlDatabase
Since there are more than one database, you need to get information from this collection as shown in the next listing:
-------------
PS C:\Users\Jayaram> foreach($sqlDatabse in $sqlServer.Databases){$sqlDatabase.name}
AdventureWorks2012
master
model
msdb
ReportServer$REGENCYPARK
ReportServer$REGENCYPARKTempDB
tempdb


These are the databases in my SQL Server Instance named RegencyPark.


In a future post we will see how to query database and other tasks

 

Wednesday, November 12, 2014

A quick note on writing PowerShell scripts -Part 2

In Part 2 we will script a PowerShell file and execute it. It is a very simple example. However there are a few preliminary things you should be aware of.

Launch Windows PowerShell in Windows 8.1

Launching Windows PowerShell in Windows 8.1 is easy. Windows 8.1 comes with PowerShell. Click the Search charm and type in 'Windows PowerShell' or, in the all apps, pick Windows PowerShell and click.

The Windows host launches the Windows PowerShell as shown.


You type in the command $PSVersionTable.psversion to get the version on your computer as shown. It will be probably version 4.0.
------------
 
Now let us create a file, a simple one and save it with the extension ps1. I will show you later what this file look like.

However to run a script file you need to know about the execution policy. You can get a good handle on this here:
http://technet.microsoft.com/library/hh847748.aspx

How do you know what policy is in force?
To see the policy in force, just type the commandlet Get-Policy as shown and you get an immediate answer.
====
PS C:\Users\Jayaram\Desktop> Get-ExecutionPolicy
Restricted
PS C:\Users\Jayaram\Desktop>
======
'Restricted' is the default policy in Window 8, Windows 8.1 and Windows Server 2012

What does 'Restricted' mean to you?
            - Permits individual commands, but will not run
              scripts.
            - Prevents running of all script files, including
              formatting and configuration files (.ps1xml), module
              script files (.psm1), and Windows PowerShell
              profiles (.ps1). 

Well, you see with 'Restricted' policy enforced, you cannot run a script file (I created one called Hello.ps1 and tried to run with default policy). Actually Power Shell tells you this, if you try to run one as shown,
=====
.\Hello.ps1 : File C:\Users\Jayaram\DEsktop\Hello.ps1 cannot be loaded because running scripts is disabled on this
system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ .\Hello.ps1
+ ~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [], PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess
====
OK. How do you change this policy?

Well, you need the policy changed to 'Remote signed'.
What does 'Remote Signed' mean to you?
It means,
- Scripts can run. This is the default execution
              policy in Windows Server 2012 R2.
            - Requires a digital signature from a trusted
              publisher on scripts and configuration files that
              are downloaded from the Internet (including
              e-mail and instant messaging programs).
          - Does not require digital signatures on scripts that
              you have written on the local computer (not
              downloaded from the Internet).
            - Runs scripts that are downloaded from the Internet
              and not signed, if the scripts are unblocked, such
              as by using the Unblock-File cmdlet.
            - Risks running unsigned scripts from sources other
              than the Internet and signed, but malicious, scripts.

How do we change the execution policy?
Well, Power Shell has this "Set-ExecutionPolicy" command. The syntax is:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

There is one more thing you need to take care of before you change the policy and that is, you should run the command with elevated permissions (you should run the script as an Administrator of the local machine). If you do not ,you will get this exception:
================
PS C:\Users\Jayaram\DEsktop> Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic at
http://go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): y
Set-ExecutionPolicy : Access to the registry key
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell' is denied. To change the execution
policy for the default (LocalMachine) scope, start Windows PowerShell with the "Run as administrator" option. To
change the execution policy for the current user, run "Set-ExecutionPolicy -Scope CurrentUser".
At line:1 char:1
+ Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : PermissionDenied: (:) [Set-ExecutionPolicy], UnauthorizedAccessException
    + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetExecutionPolicyComma
   nd
===============================
After launching Windows PowerShell as an administrator you will have more powers.

The following code listing shows how to run the command.
====
PS C:\Windows\system32> Set-ExecutionPolicy -Scope CurrentUser
cmdlet Set-ExecutionPolicy at command pipeline position 1
Supply values for the following parameters:
ExecutionPolicy: RemoteSigned

PS C:\Windows\system32>
==========================
After you type in RemoteSigned and return, you get a message from the ExecutionPolicyChange as follows:
"The execution policy helps protect you fromscripts that you do not trust. Changing the execution policy might expose you to security risks described in the about_Execution_Policies help topic at http://go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy? with three ptions, yes,no and suspend.
Click Yes.
You will not get a message that the policy has been changed but it will have changed.
========
Now we run the file as shown in the next listing.
=========
PS C:\windows\system32> C:\Users\Jayaram\Desktop\Hello.ps1
Hello World
PS C:\windows\system32>
================
Finally what was the content of the 'Hello.ps1'?

It is the 'Hello World' example and the file has only the following content with just two lines typed into Notepad and saved to Desktop as 'Hello.ps1':
============
$StringToPrint="Hello World"
Write-output $StringToPrint

 

A quick note on writing PowerShell scripts - Part 1

PowerShell scripts are a sequence of PowerShell commands written one after another (executed in that order) using a text editor such as Notepad. The extension of a PowerShell script file is <filename>.ps1. The 1 ins ps1 has nothing to do with the version of PowerShell on your computer and you may have more than one in
your computer,
 http://hodentekhelp.blogspot.com/2014/08/can-multiple-versions-of-powershell-can.html

You also have the option of choosing which version you want to run
(http://hodentekhelp.blogspot.com/2014/08/if-you-have-multiple-versions-of-power.html).

In addition to Notepad there are other dedicated script editors like,
and many others. For a complete listing follow this link:

In order to run a PowerShell script file you need to create a file with extension ps1 and then run
this script in Windows PowerShell or a similar programme.

In Part 2 you will learn writing a simple script file.

Monday, November 10, 2014

SQL Server Driver for PHP released

SQL Server driver (SQLServer31.exe, 499KB, version 3.1.CTP) for PHP is now available as a CTP. Now PHP scripts can access Microsoft SQL Server and Microsoft Azure SQL Database.

You can download the CTP here:
http://www.microsoft.com/en-us/download/details.aspx?id=20098&WT.mc_id=Blog_SQL_Announce_DI

Details of this version:
  • This version supports PHP Version 5.5 and requires Microsoft ODBC driver 11 (or higher)
  • It supports SQL Server 2012; localDB abd AlwaysOn features
  • Buffered Queries are included
All SQL Server user can get it at no extra cost.

This widens the interoperability range.
The soon to be released JDBC driver compatible with JDK7 for SQL Server will enable applications to connect to SQL Server widening connectivity with the Java Platform.

SQL Server 2025 ready to go

 I have not yet done looking at SQL Server 2022, SQL Server 2025 is ready to go. Microsoft is indeed relentless!  Microsoft announced SQL Se...