Table-valued parameters is a feature introduced in SQL Server 2008. In earlier versions of SQL Server it was not possible to pass a table variable into a stored procedure as a parameter.
In SQL SERVER 2008 we can use table-valued parameters to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing in multiple parameters. Continue reading #SqlServer interview question: What are table-valued parameters?
SQL Server blocking occurs when one connection places a lock on a table (or selected rows, pages, extent) and another connection attempts to read or modify the data when the lock is in effect. Continue reading #SqlServer interview question: What is blocking?
Scenario
One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc…
Solution
For this example, I have a SQL Server test environment configured and I want to test the alert mechanism using Hotmail. The following outlines the settings to do this. Continue reading Database mail setup for Gmail,Hotmail,Yahoo or AOL
There are situations where you might have a large SQL Server database that you need to replicate. Since the database is very large we want to initialize the Subscriber from the SQL Server backup and not use a snapshot. How do we bundle all the steps together to achieve this without performing them manually?
Solution
I want to cover the basic steps needed to setup a “Push Type Transactional Replication”. This tip assumes you already have an understanding of SQL Server Transactional Replication and I will show how to bundle all the steps into one process. Continue reading Automating Tranzactional Initialization from a backup in SQL Server
You’ve set up replication initially by using the GUI and accepting all defaults and now due to growth you need to change your default replication snapshot folder location to another drive since the current location is a local disk and cannot be expanded to accommodate the growth.
This concerns you as you cannot afford downtime to perform a re-initialization of all your existing subscribers as they are needed to run the business.
Solution
You can change the snapshot location without having to perform a re-initialization. The only caveat to this is you do need to generate a new snapshot. This snapshot will not be applied to your subscribers, but has to go through the generation process. I recommend doing this at night or weekends to minimize the impact of the snapshot agent locking tables and contention on the publisher database. Continue reading How to change default snapshot folder for Replication?