Search

Advertisements

Showing posts with label SSMS. Show all posts
Showing posts with label SSMS. Show all posts

SQL Server - Transactional Replication - Configuring subscriber

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring subscriber at server 3

In SSMS - > Under Object Explorer - > Replication -> Local Subscriptions -> New Subscriptions...

SQL Server - Transactional Replication - Configuring Publisher

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring publisher at server 2

In SSMS - > Under Object Explorer - > Replication - > Local Publications -> New Publication...

SQL Server - Transactional Replication - Configuring distributor

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring distributor at server 1

In SSMS - > Under Object Explorer - > Replication - > right-click Configure Distribution...

SQL Server - Transactional Replication

As mentioned in MSDN BOL :

Transactional replication typically starts with a snapshot of the publication database objects and data.

As soon as the initial snapshot is taken, subsequent data changes made at the Publisher are usually delivered to the Subscriber as they occur (in near real time).

How Transactional Replication Works

SQL Server - Compare Execution Plan

In SQL Server 2016, one of the SSMS enhancement is option to compare query execution plan.

This is how we can compare execution plan in SQL Server Management Studio 2016:

SQL Server - How to find environment difference on SSMS Query window

This article is about finding environment difference on SSMS query window .

Suppose If we have access to all environments (Production,QA and Development) , we should be really carefull in executing DML scripts on respective environments .

On query window at the bottom , we can find the server name , user name , SPID and database details
as shown in below image :






We have one more option to find easily environment difference on SSMS query window
by defining colors for each  environment .

Options <<  - > Connection Properties - > Connection - > Use custom color - > Select ...




Note :
This setting applies to particular Server type , Server name , Authentication  type and User .

SSMS - How to connect to particular database when connecting to SQL Server database engine

After connecting into SQL Server Database engine and opening Query window , you will notice master database in the Available Databases list by default .

We can choose the required database from the Available Databases list .



To connect to particular database when connecting to SQL Server database engine :

Options <<  - > Connection Properties - >  Connect to database - >  <Browse Server ...>










Note :
This setting applies to particular Server type , Server name , Authentication  type and User .

SSMS - Query Designer

This option will be really helpful for beginners and even for advanced T-SQL developers if they are joining more number of tables.

You can find this option in SSMS - > Query - > Design Query in Editor ... 
or press CTRL+SHIFT+Q

Using this option,you can

add tables to be joined,
specify columns to be selected,
assign type of joins between tables,
mention sort type,
specify table alias names



you can copy the generated query in the designer and run it on new window .



SSMS - Object Explorer - Edit top n rows

If you don't want to write UPDATE statements and you just want to manually modfiy few records in a table ,

 Under Object Explorer ->right click on Tables -> Edit Top 'N' rows




For example,I will copy values from AddressLine1 and paste it on AddressLine2 for AddressID = 1,
click on some other row and run this query on new window

SELECT * FROM [Person].[Address] WHERE Addressid = 1

you will notice modified value on AddressLine2 for AddressID = 1

To edit top 10,100,200 rows...you can adjust that settings

Tools - > Options - >SQL Server Object Explorer





After editing rows,like we copy the query resultsets,you can click on top row first column(0,0) copy & paste the results on excel sheet if you want to manually export modified records to excel.







SSMS 2012 - Show/Hide Results Pane

In SQL Server Management Studio,developers can view or hide query results pane by using shortcut key CTRL + R in SSMS 2008.

But in SSMS 2012,after installing SQL Server 2012,shortcut key CTRL + R was not working to view/hide results pane.

Below images will show how to view/hide results pane :











Below images will show how to assign shortcut key CTRL + R to view/hide results pane:

In SSMS,Goto Tools -> Options... - > Keyboard



do the settings as shown,click Assign and Ok



Close & re-open SSMS to test working of Show/Hide Results Pane using shortcutkey CTRL + R

SQL Server - Database Mail

This post is about sending mail through SQL Server [Database Mail  feature in SSMS]







select the option to create profile & account for sending mail.




SQL Server 2008 - Table and Database Designers


In this post,i am going to explain about,how to automatically generate scripts ,when a table is created/altered through Design option(New Table...) in SSMS ,

& how to automatically prevent table definition changes,if you try to alter the table through
 Design option in SSMS

In SSMS 2008,Goto   Tools -> Options...





  


















Tools -> Options -> Table and Database Designers -> Auto generate change scripts

Look at the below image,


If you check this option - "Auto generate change scripts" - and then if you try to create /alter

table through Design option(New Table...) in SSMS,it will automatically prompt for saving the table definition scripts.

























 Example:Auto generate change scripts

Under Object Explorer,expand database and then right-click Tables - > New Table...
as shown in below image.











 

















Create a new table and then try to close the designer query window ,a screen will pop-up as shown in below image.



































Tools -> Options -> Table and Database Designers -> Prevent saving changes that require table re-creation


If you check this option - "Prevent saving changes that require table re-creation" - and then if you try to alter the existing

table definition through Design option in SSMS,it will automatically prevent from altering the table definition as shown in below image.




Advertisements