SQL-BOX.COM

–SQL Server Storage

Always – ON Availability group Alerts

After setting up Always ON , we need to configure SQL server Alerts to know about the availability Group errors and other failover information you may want to know when occurred. Here is the list of error and scripts to create them.

You may don’t want to configure all alerts and flood inboxes when happened, you can choose which one to configure using below query,

SELECT message_id [error_number], severity, text
FROM sys.messages
WHERE text LIKE (‘%availability%’)
AND is_event_logged = 1;


Here is the Script to configure all alerts related to AG’s

————————————————————
— Always – ON HA Error Alerts
————————————————————

USE [msdb]
GO

/****** Object: Alert [HA Error – 35254] Script Date: 10/20/2014 1:55:32 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35254′,
@message_id=35254,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35262] Script Date: 10/20/2014 1:55:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35262′,
@message_id=35262,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35273] Script Date: 10/20/2014 1:56:01 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35273′,
@message_id=35273,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35274] Script Date: 10/20/2014 1:56:16 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35274′,
@message_id=35274,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35275] Script Date: 10/20/2014 1:56:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35275′,
@message_id=35275,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35276] Script Date: 10/20/2014 1:56:46 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35276′,
@message_id=35276,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35279] Script Date: 10/20/2014 1:57:03 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35279′,
@message_id=35279,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35299] Script Date: 10/20/2014 1:57:23 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35299′,
@message_id=35299,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41048] Script Date: 10/20/2014 1:57:38 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41048′,
@message_id=41048,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41049] Script Date: 10/20/2014 1:57:56 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41049′,
@message_id=41049,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41050] Script Date: 10/20/2014 1:58:13 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41050′,
@message_id=41050,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41051] Script Date: 10/20/2014 1:58:29 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41051′,
@message_id=41051,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41052] Script Date: 10/20/2014 1:58:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41052′,
@message_id=41052,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41053] Script Date: 10/20/2014 1:59:06 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41053′,
@message_id=41053,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41054] Script Date: 10/20/2014 1:59:21 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41054′,
@message_id=41054,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41055] Script Date: 10/20/2014 1:59:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41055′,
@message_id=41055,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41089] Script Date: 10/20/2014 1:59:47 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41089′,
@message_id=41089,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Resumed] Script Date: 10/20/2014 2:13:37 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Resumed’,
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Suspended] Script Date: 10/20/2014 2:13:45 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Suspended’,
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Role Change] Script Date: 10/20/2014 2:13:51 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Role Change’,
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

Make sure to enable e-mail operator for each alert after executing above script.

List All Server logins, DB users and roles

Here is one of the most useful script to script all users and logins to find whose having what access level, Use case is , finding all logins having sysadmin access.

–#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2####
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
— CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
IF OBJECT_ID(‘[TEMPDB].[DBO].[DB_ROLES]’) IS NOT NULL
DROP TABLE [TEMPDB].[DBO].[DB_ROLES]
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES](
[DBNAME] [SYSNAME] ,
[USERNAME] [SYSNAME] ,
[DB_OWNER] [VARCHAR](3) ,
[DB_ACCESSADMIN] [VARCHAR](3) ,
[DB_SECURITYADMIN] [VARCHAR](3) ,
[DB_DDLADMIN] [VARCHAR](3) ,
[DB_DATAREADER] [VARCHAR](3) ,
[DB_DATAWRITER] [VARCHAR](3) ,
[DB_DENYDATAREADER] [VARCHAR](3) ,
[DB_DENYDATAWRITER] [VARCHAR](3) ,
[DT_CREATE] [DATETIME] NOT NULL,
[DT_UPDATE] [DATETIME] NOT NULL,
[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3] DEFAULT (GETDATE())
) ON [PRIMARY]
GO

INSERT INTO [TEMPDB].[DBO].[DB_ROLES]
EXEC SP_MSFOREACHDB
‘ SELECT
”?” AS DBNAME,
USERNAME,
MAX(CASE ROLENAME WHEN ”DB_OWNER” THEN ”YES” ELSE ”NO” END) AS DB_OWNER,
MAX(CASE ROLENAME WHEN ”DB_ACCESSADMIN ” THEN ”YES” ELSE ”NO” END) AS DB_ACCESSADMIN ,
MAX(CASE ROLENAME WHEN ”DB_SECURITYADMIN” THEN ”YES” ELSE ”NO” END) AS DB_SECURITYADMIN,
MAX(CASE ROLENAME WHEN ”DB_DDLADMIN” THEN ”YES” ELSE ”NO” END) AS DB_DDLADMIN,
MAX(CASE ROLENAME WHEN ”DB_DATAREADER” THEN ”YES” ELSE ”NO” END) AS DB_DATAREADER,
MAX(CASE ROLENAME WHEN ”DB_DATAWRITER” THEN ”YES” ELSE ”NO” END) AS DB_DATAWRITER,
MAX(CASE ROLENAME WHEN ”DB_DENYDATAREADER” THEN ”YES” ELSE ”NO” END) AS DB_DENYDATAREADER,
MAX(CASE ROLENAME WHEN ”DB_DENYDATAWRITER” THEN ”YES” ELSE ”NO” END) AS DB_DENYDATAWRITER,
CREATEDATE,
UPDATEDATE,
GETDATE()
FROM (
SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE
FROM
[?].DBO.SYSMEMBERS A JOIN [?].DBO.SYSUSERS B ON A.MEMBERUID = B.UID
JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID
)S
GROUP BY USERNAME, CREATEDATE, UPDATEDATE
ORDER BY USERNAME’
— RETRIVE LOGINS, USERS AND ROLES TOGETHER

SELECT SERVERPROPERTY(‘SERVERNAME’) AS [SERVERNAME],
B.NAME AS [LOGINNAME],
CASE B.SYSADMIN WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS SYSADMIN,
CASE B.SECURITYADMIN WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS SECURITYADMIN,
CASE B.SETUPADMIN WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS SETUPADMIN,
CASE B.PROCESSADMIN WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS PROCESSADMIN,
CASE B.DISKADMIN WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS DISKADMIN,
CASE B.DBCREATOR WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS DBCREATOR,
CASE B.BULKADMIN WHEN ‘1’ THEN ‘YES’ ELSE ‘NO’ END AS BULKADMIN,
B.DBNAME AS [DEFAULT_DBNAME],
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME

–WHERE B.ISNTUSER=1 –INCLUDE TO EXCLUDE THE SQL LOGINS
SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
DROP TABLE [TEMPDB].[DBO].[DB_ROLES]
DROP TABLE #LOGINS
————–SCRIPT END———————–
 

 

 

 

 

 

 

 

 

 

 


All about SQL server Statistics in simple words:

What is SQL server Statistics?

SQL Server statistics will be used by optimizer to create the optimized execution plans where estimating the number of rows that can be returned, density of pages and statistics object will also hold a histogram of information about the distinctive number of rows and range of typical rows. All this information will be used by optimizer to estimate optimal execution plan to retrieve the data.

When do these statistics will be created?

SQL server will create statistics object when we create an index on table and statistics object will also be created by SQL server automatically when we use non-indexed column in a where condition of select queries (What this mean is we are missing an index there).  Also we could just create the statistics manually.

Let’s see this with an example:

I have created a database and a table called dbo.employee  (I copied it over from Adventurewroks2012 )which does not have any index after copied to a new database called statistics as below

Pic (1)

Pic1

When I run a simple select on this table like below pic (2) will result into a table scan in execution plan as it doesn’t have an index and will still create statistics  pic (3 )to make use of them when the same query run every time.

Pic (2)

pic2

Pic (3)

Pic3

Let’s create index pic (4) to get benefited as all we know seek is better than scan when we select for specific rows instead a whole set of rows.

Pic (4)

Pic4

When I created the index, now SQL server will create the statistics specifically for this index and these statistics will tell the optimizer how to use this index and get the data faster and optimal.

How these statistics will help Optimizer?

Let’s take the same above Simple select to see the estimated execution plan.  Interesting now, the estimated execution plan is showing that the plan was just created by using the statistics object that was creates along with the index and displays the estimated rows that will returned and other information which estimated and this information  was read by Optimizer from the statistics.

I’m not going into detail how the query was executed internally when the SQL hits the SQL engine to process the query but in general the optimizer which will be in relation engine will use this statistics to create the estimated plan and will handed over the plan to storage engine to get the data where the actual execution plan comes into picture.

As long as the estimated and actual plans are same, there will no performance issue as this means that optimizer had the updated statistics. When these statistics got not updated may result in actual plans different from estimated which result in not much accurate plans from optimizer and let the performance down.

Pic (5)

Pic5

When do these statistics get out of date?

Usually, statistics will be out of date or inaccurate when data in a table changes from time to time. By default statics for table will get updated when

  1. When an empty table gets a row
  2. A table had less than 500 and increased by 500 rows
  3. A table had less than 500 and increased by 500 rows +every 20 % of the total rows
  4. Trace flag 2371 which will change the fixed rate of the 20% threshold for update statistics into a dynamic percentage rate. The higher the number of rows in a table, the lower the threshold will become to trigger an update of the statistics. For example, if the trace flag is activated it means an update statistics will be triggered on a table with 1 billion rows when 1 million changes occur.

More info on this Trace flag can be found here TraceFlag2371 (I haven’t tested it though)

The best way to check the statistics are out of date is to verify the estimated number of rows in an execution plan to actual number of rows, if they both are almost same then we have the accurate stats and if not then time to update stats.

How to automate updating these statistics?

The database options to create and update statistics will let you do it automatically by SQL server or Optimizer. But how smart are they on a highly transactional databases and large TB databases with millions of data loads every minute to minute?

By default these are enabled.

How to perform manual updating these stats?

1.       Below stored procedure will update the stats within whole single database

      EXEC sp_updatestats; Or EXEC sp_updatestats ‘resample’  

–    Resample will use the most recent sample number rows to update the stats.

–    sp_updatestats updates only the statistics that require updating based on the rowmodctr (row modification counter)information in the sys.sysindexes

2. Below update command will update the all stats for specific table or specific index if specified. Also this command will provided with multiple options. I will explain few of them which are important to note while doing this update on large TB of data.

 UPDATE STATISTICS Table Name or View Name ,  index_name with Options 

FULLSCAN: Will scan the entire number of rows in a table to update the stats.

SAMPLE NUMBER: If you are not like to do whole scan of the table rows (Time consuming) and update the stats, we have option to specify the sample number of rows or percent of rows to scan to update or create the stats.

NORECOMPUTE: If this option is specified then query optimizer completes this statistics update and disables future updates (AUTO_UPDATE_Statistics). We must carefull while using this option as it will turn off the auto stats for the specified table.

What happens to these stats when re-indexing or Re-organizing indexes?

When re-indexing the stats will also be re-created and there is nothing changed to stats when re-organizing the indexes

How to check when your stats were last updated?

1. One way to query sys.stats system table, something like below

select object_name(object_id) TableName ,name ,stats_date(object_id,

stats_id)  Last_updated from sys.stats

where objectproperty(object_id, 'IsUserTable') = 1 

2. the other way is

 DBCC SHOW_STATISTICS (TableName, statsName) 

 

 

What is Parameter Sniffing in simple words?

I have been asked recently what is parameter sniffing in an interview. Well, we all know how Optimizer in SQL server works to retrieving the data from the tables. We do also know that Optimizer reads the statistics and internal pre-compiled plans instead generating a new plan for parameterized Queries and Store procedures. Here we are, how Optimizer knows we are executing a similar query as it searching for pre-compiled plan for us because it was reading the parameters we passed to the stored procedure or a parameterized query and it is called “sniffing”.

What it counts for Performance, Good or bad?

 “It depends “Yes “It depends” – I know we use this word many times in regards to databases

Good:

By default, Parameter sniffing is enabled and SQL Server caches the plans and reads the parameters those passed to the stored procedures or parameterized SQL Queries. We benefit from it as Optimizer will go and search for the plan it compiled earlier in cache when we execute the same query again. Optimizer will not need to re-create the plan to execute the same query multiple times.

Bad:

This is all good only when there are good statics are maintained and typical parameters are used. What changes day by day is the requirement for using the new parameters with different words and optimizer will tries to use the same old plan in cache and may not be good for the new parameters as data has grown a lot and statistics have not updated yet and may be the plan for new parameter was doing table scans.

Work Around:

  1. Until we see a performance issue with this parameter sniffing it is better to leave it by default and we really benefit from it.
  2. When we see lot of performance issue, we could disable it using a trace flag 4136, this will disable the parameter sniffing at the server level.
  3. We could use it to disable only for specific query then we could use the recompile  query hint

SQL Server 2012- Sample Databases

I was not able to find the sample databases for SQL server 2012 as most of the sites I have visited have only 2000  sample databases which we will be not able to restore to 2012. Here I have uploaded the link 2012 sample .bak files. (Easy to restore to 2012)

Pubs, Adventure Works, North Wind  -Download

How to bring a database online which is in restoring mode ?

For Example, In log shipping the secondary or a stand-by database will be in restoring mode while applying the logs from primary for every 15 minutes.

In order to perform a DR test, we need to bring the production down and DR database online, this can be achieved by running the below query on secondary server database.

–Run this below query to bring the database online which is in Restoring state

Restore Database [DATABASE NAME] with recovery

Configure SQL Server DBMAIL using T-SQL

Configuring dBMail involves 3 main steps

Step 1: Creating Mail Profile

Step2: Creating Mail Account

Step3: Mapping Account to Profile.

Below script will let you do this 3 steps and configures your dBmail successfully.

Source


-    ENABLE SQL DBMAIL, if diabled

EXEC sys.sp_configure N'Database Mail XPs', N'1'

GO

 

RECONFIGURE

GO

 

-    Add Mail Profile

 

EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'Profile Name'

GO

 

-    Set as Default Profile

 

EXEC msdb.dbo.sysmail_add_principalprofile_sp 

@profile_name=N'Profile Name',

@is_default =N'1'

GO

 

-    ADD MAIL ACCOUNT

 

EXEC msdb.dbo.sysmail_add_account_sp

 

   @account_name    = 'Account_Name',

   @email_address   = 'Email Address,EX:DBA@yourcompany.com',

   @display_name    = 'Account Name',

   @replyto_address = 'Email Address,EX:DBA@yourcompany.com',

   @mailserver_name = 'your SMTP Server',

   @mailserver_type = N'SMTP or if you use other mail protocol',

   @port                = 25,

   @use_default_credentials = 0,

   @enable_ssl = 0

GO

 

-    Mapping Account with Profile

 

EXEC  msdb.dbo.sysmail_add_profileaccount_sp   @profile_name=N'Profile Name', @account_name= N'Account Name',  @sequence_number=N'1'

GO

 

How to run a SQL Query on list of servers and save output to excel using power shell?

You will see  how much is powershell is useful for DBA  after using this script.

Step 1: Create a text file with list of servers you want to run the script on and name it ‘serverlist’ to C:\serverlist.txt

Step 2:  I have Created a folder called scripts in my C: then Copy the below script into another text file and save it as C:\Scripts\script.ps1


# Script Runs on servers listed in serverlist.txt
# use sqlps.exe to run this script
# In cmd, sqlps.exe c:\scripts\script.ps1

$query = " Your SQL Query"

#Path to the excel file to be saved.

$csvFilePath = "c:\Scripts\queryresults.csv"
$excelFilePath = "c:\scripts\queryresults.xls"

# Run Query against multiple servers, combine results
# Replace "Server1", "Server2\INSTANCE1" with names of your SQL Server instances

$instanceNameList = get-content c:\serverlist.txt
 $results=@()
 
foreach($instanceName in $instanceNameList)
{
        write-host "Executing query against server: " $instanceName
        $results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName
}

# Output to CSV

write-host "Saving Query Results in CSV format..."
$results | export-csv  $csvFilePath   -NoTypeInformation
# Convert CSV file to Excel
# Reference : <a href="http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa">http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa</a>

write-host "Converting CSV output to Excel..."

$excel = New-Object -ComObject excel.application
$excel.visible = $False
$excel.displayalerts=$False
$workbook = $excel.Workbooks.Open($csvFilePath)
$workSheet = $workbook.worksheets.Item(1)
$resize = $workSheet.UsedRange
$resize.EntireColumn.AutoFit() | Out-Null
$xlExcel8 = 56
$workbook.SaveAs($excelFilePath,$xlExcel8)
$workbook.Close()
$excel.quit()
$excel = $null

write-host "Results are saved in Excel file: " $excelFilePath

Step3: Copy and paste the SQL Query into the above script where it says like this 

$query = ” Your SQL Query to run against multiple servers”  

Step 4: Open CMD and run the script like this

Sqlps.exe c:\scripts\script.ps1

The results from the SQL Query from each server will be combined and saved as queryresults.xls into your scripts folder.

Note :The script uses windows authentication to connect to the SQL servers.

What “Open Table “in SQL Server 2005 replaced with in SQL Server 2008? How to change this Edit top 200 to edit all?

 

Today, a customer asked me how to open a table in SQL Server 2008 to edit the rows in a table manually as she is able to do it in SQL 2005 using open table option in right click of table but in SQL Server 2008 it was replaced with edit top 200 rows. Initially I though there is no way to change from the default option Edit top 200 to all but after searching in BOL ,I found this

 

SSMS -> Tools -> Options-> SQL Server Object Explorer-> Commands-> Set value 0 for Value for Edit Top Rows Command

 

Note: Setting value 0 lets you retrieve all or you can customize this value to your requirement.                 

 

Source: BOL

 

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: