Powershell – Part 4 – Arrays and For Loops

Tome's Land of IT

Arrays

For those that have never worked with arrays here’s a great way to understand them:  If a variable is a piece of paper then the stack of papers is an array.  It’s a list of variables or objects, and every programming/scripting language has ways to store these variables or objects linearly so you can access them later via a number of different methods.

So let’s look at how we can create an array of string objects in powershell:

$array = @("test1", "test2", "test3")$array

You can also add an element to the end of an array:

$array = @("test1", "test2", "test3")$array += "test4"$array

You can also add arrays together:

$array = @("test1", "test2", "test3")
$array2 = @("test4", "test5")
$array = $array + $array2
$array

You can access an element of an array if you know the index number of the element you want.  Arrays are indexed by…

View original post 642 more words

Advertisements

Decrypting encrypted stored procedures, views, functions in SQL server 2005,2008 &R2

Sqljunkieshare

Any stored procedure or view or function can be encrypted when stored in the sql server , so a user can not see the code inside even if the user has SYSADMIN role and it is also not recommended to use this encryption option to hide the code because once you use the encryption option Microsoft sql server does not provide the option or the feature to reverse the setting or decrypt the object , so when you use the encryption option , save your code in a separate instance

TITLE: Microsoft SQL Server Management Studio
——————————

Script failed for StoredProcedure ‘dbo.test_encrp’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

——————————
ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure ‘[dbo].[test_encrp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

First we will see how…

View original post 988 more words

Powershell Script to Output All Tables in a Database to CSV for SQL Server


$server = "SQLCON-TEST02"
$database = "AdventureWorks"
$tablequery = "SELECT name from sys.tables"

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
$queryData = "SELECT * FROM [$($Row[0])]"

#Specify the output location of your dump file
$extractFile = "E:\PROJECTS\$($Row[0]).csv"

$command.CommandText = $queryData
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
}

#####################################################################

Run PowerShell
To run PowerShell from SQL Server Management Studio
Open Object Explorer.
Navigate to the node for the object to be worked on.
Right-click the object and select Start PowerShell.

DBA – Move master Database to another drive – in simple steps

SQL with Manoj


Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a spacial handling for this case of master DB.

–> Let’s first check the location of master DB:

Move Master 01

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Move Master 02

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files to E:SystemDatabasesMaster

View original post 75 more words

SQL Server 2016 full and final version available – Download it now !!!

SQL with Manoj

Its 1st June 2016 and finally Microsoft has released SQL Server 2016 full and final version, and is available for download!

Exactly a year back the first CTP version of SQL Server 2016 was released and we got a chance to get a glimpse of the new features coming in. Then after every month or two Microsoft kept rolling out the CTP versions with more new features, enhancements, and bug fixes on the previous CTP builds.

–> Download SQL Server:

To download SQL Server 2016 you can Register and Download the SQL Server 2016 Full or free Evaluation version (180 days) here.

Or you can Direct download the DVD ISO file image (~2.1 GB) SQLServer2016-x64-ENU.iso

–> Free Developer Version:

Microsoft on March 2016 announced that going forward the Developer version of SQL Server any release will be free for Developers and Learning purpose. Register and Download the Developer version.

Or…

View original post 204 more words

Creating Encryption Key Backup for SSRS 2008R2

Creating Encryption Key Backup for SSRS 2008R2

– r for taking backup of key

-f for file location

-i is the instance name of the Reporting Services

C:\Windows\system32>rskeymgmt -e -f m:\rsdbkey.snk -p Abc1234567 -i PAYAPPSGRP02

 
Are you sure you want to extract the key from the report server? Yes (Y)/ No (N)
: Y

SECURE THE FILE IN A SAFE LOCATION

The command completed successfully