Copying data from SQL Server to Excel using BCP utility

Below is the sample code to copy data from SQL server to Excel through BCP (Bulk Copy Program) uitlity.

declare @logtext varchar(100)
declare @cmd varchar(1000)
--set @logtext = 'Select top 10 * from Server.DB.Table'
SET @cmd = 'bcp "Select top 10 * from Server.DB.Table" queryout "c:\test\check.csv" -c -S Test/BK -T -t,'
EXEC master..XP_CMDSHELL @cmd

Set and Verify the Retention Value for Change Data Capture

To set and verify the retention period from the default 3 days to 5 days.

sp_cdc_change_job @job_type='cleanup', @retention=7200

The value 7200 represents the number of minutes in a period of 5 days. All was well, but they recently asked how they can verify that the change was made. Well, it turns out the values are stored in the msdb database, in a table called dbo.cdc_jobs. Below is the quick query to allow them to test any database with CDC enabled, to find the retention value:

SELECT [retention]

FROM [msdb].[dbo].[cdc_jobs]

WHERE [database_id] = DB_ID()

    AND [job_type] = 'cleanup'

To set multiple line in a textbox in SSRS

If we want to have multiple line in a single text box in report like below:

Name:      Address:   City:      State:    ...

Jon Doe    Addr1      ThisTown    XX       ...    

               Addr2

               Addr3

-----------------------------------------------

Jane Doe   Addr1      ThisTown    XX       ...

               Addr2

               Addr3

-----------------------------------------------

We can use the & VbCrLf & between the expression as given below which works fine. 

= Fields!Addr1.Value & VbCrLf & 

   Fields!Addr2.Value & VbCrLf &

   Fields!Addr3.Value