Archive

Archive for July, 2013

using powershell to select from Tables in database

July 28, 2013 1 comment

I was looking for some ways to connect to connect to SQL instance .. Sarabpreet had covered quite interesting ways to do the same … you may check his article over here (a big Thanks to Sarab for sharing the same …)

below is what I tried on windows 7 (SQL 2008 R2 )

once we are within shell .. shell treats all objects with SQL

as files and folders … like what we have while we are working with CMD

we can use different ways to connect to sql server via PowerShell tools ..PS1

1> type powershell.exe   in the start –> run prompt and hit enter

this will keep you PowerShell prompt

2> type SQLPS.EXE   in the start –> run prompt and hit enter

3> or right click on any tab in the SSMS and click on Start PowerShell

depending on were you have right clicked .. you be taken to appropriate

depth or hierarchy with in the SQL instance

PS1_1

for this example we will use step 3 …

refer the snapshot on the right …

if you right click on any of the object within the SQL instance .. we will

taken into the corresponding hierarchy level within the SQL

once we are within the database .. we will use command DIR .. to look for

list of folder and files within (comparing the database structure to OS file system )

Note :: after a certain level command will not return any results or output …

below is an example of a command ran at database level .. we can all objects that are available in SSMS for  a database

connect_2

we will need to use a SQLPS command named INVOKE-SQLCMD to get any meaning full data out of the SQL server ..

once OS based commands like dir (which will work same as LS and get-childitem) hits its limits ..

1> get-help invoke-sqlcmd -FULL

2> get-help invoke-sqlcmd -EXAMPLE

3> get-help invoke-sqlcmd

connect_7

invoke-SQLCMD has multiple parameters which help use to define query and connection options

so once we are within correct location (use cd or cd .. to move from one path to another .. )

we will select data from a table ..as below  .. command used is as below (we may try it on any table with some data in it)

invoke-sqlcmd “select top 10 * from dbo.T2 where id > 1000”

connect_3

below is an example of using SQLCMD to run another command to populate data ..

so you can run any t-sql command in SQLPS shell.. that use can run in SSMS

connect_4

so what is use of all this .. if we can run these commands in SSMS … well the power of powershell

really comes across when we have list of servers to manange .. and we want to automate some admin

tasks across all of them …. going forward I will share more examples on Powershell …

Enjoy:)

Advertisements

Check a Huge collection of books on Microsoft technology .. all FREEEEE

July 23, 2013 Leave a comment

Check a Huge collection of books on Microsoft technology ..

including SQL SERVER ,SSIS ,sharepoint and many more …

FREE_SQL_BOOK

Click on below link for more details :: Enjoy 🙂

http://blogs.msdn.com/b/mssmallbiz/archive/2013/06/18/huge-collection-of-free-microsoft-ebooks-for-you-including-office-office-365-sharepoint-sql-server-system-center-visual-studio-web-development-windows-windows-azure-and-windows-server.aspx?PageIndex=1#comments

Book Review – DBA Survivor: Become a Rock Star DBA

July 22, 2013 Leave a comment

Hi there,Review_book

check out Garland MacNeill’s review of Thomas LaRock’s

new book called “DBA Survivor” … which targets all aspects of

DBA’s duties …and  Thomas own journey as a DBA ….

should be on your MUST READ list for the year …as its on mine !!!!!!! 🙂

http://www.nyteshades.com/dba-survivor-become-a-rock-star-dba/

Thomas LaRock’s link

http://thomaslarock.com/

Categories: INDEXES

now thats called being focused

July 20, 2013 Leave a comment

 

This one is about been focused no matter what your conditions are …

no one will see the jewel in you .. until you are willing to get a feel of it enjoy 🙂

Categories: LOA Tags: ,

error while Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2}

July 12, 2013 1 comment

Hi There ,

got below error while trying to EDIT a job which .. i have created and edited few times earlier..

EDIT_JOB_Failure
COMPLETE Error –> TITLE: Microsoft SQL Server Management Studio
——————————

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

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

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

——————————
BUTTONS:

OK
——————————

it turns out that few people already faced this issue and reported on below connect linK

http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps

My SSMS and Shared features as below ::

Microsoft SQL Server Management Studio 10.50.1617.0
Microsoft Analysis Services Client Tools 10.50.1617.0
Microsoft Data Access Components (MDAC) 3.85.1132
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3634
Operating System 5.1.2600

Solution : Try any one of the below workaround as your environment permits (as no single one has worked for all)

(Restarting the SSMS is what works as the first solution … but as we may have few scripts already typed in SSMS .. we would prefer a longer fix)

1> Run your SSMS as administrator

2> disable UAC

3> if on SQL2008 .. then install SP3

4> if using SQL 2008 R2 .. then install SP1

refer post :: http://blogs.msdn.com/b/sqlagent/archive/2012/01/20/fix-ssms-can-no-longer-create-or-edit-job-steps-creating-an-instance-of-the-com-component-with-clsid-aa40d1d6-caef-4a56-b9bb-d0d3dc976ba2-from-the-iclassfactory-failed.aspx

related post :: http://support.microsoft.com/kb/2315727/en-us

hope this is helpful to you 🙂

corrupt allocation pages

July 9, 2013 Leave a comment

Hi There ,

                its heck of a pain to restore ,recover or do page level restore allocation pages and you have no other option other then doing a clean restore of your database ..

              some people can do a HEX editing on the file ,but that’s not recommenced

             for more info Check out DAVIS site .. and his 30 day series on Disaster and recovery

        http://www.sqlsoldier.com/wp/sqlserver/day6of31daysofdisasterrecoverydealingwithcorruptioninallocationpages# 

          Enjoy 🙂

 

list of all SQL SERVER MVP till date

July 7, 2013 Leave a comment

Hi There,

check below link about all our SQL SERVER MVP’s ,their blogs links and much more …

MVP_FullColor_ForScreen_thumb

 

http://mvp.microsoft.com/en-us/mvp/search-mvp.aspx?ty=a&ex=SQL+Server&sc=n

http://mvp.microsoft.com/en-us/default.aspx

MVP’s are great asset for the community and great role models for junior’s ..

they share their learning and experiences  without holding anything back …

A BIG CONGRATULATION to ALL MVP’s for the year 2013 ..

ENJOY 🙂