Pass table name dynamically to a Cursor

DROP TABLE table1
DROP TABLE table2
DROP PROCEDURE DynamicTableSP

USE kalyandb
CREATE TABLE table1 (id INT IDENTITY (1,1), NAME VARCHAR(20))
INSERT INTO table1 VALUES ( 'Apple')
INSERT INTO table1 VALUES ( 'BenQ')

CREATE TABLE table2 (id INT IDENTITY (1,1), NAME VARCHAR(20))
INSERT INTO table2 VALUES ('Samsung')
INSERT INTO table2 VALUES ('Sony')


CREATE PROCEDURE DynamicTableSP (@tname varchar(20)) AS
BEGIN
DECLARE @table_name VARCHAR(10)
DECLARE @sql VARCHAR(500)
SET @table_name = @tname
SET @sql = ' DECLARE @name VARCHAR(20) DECLARE db_cursor CURSOR FOR SELECT name FROM ' +@table_name + ' OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor '
EXEC (@sql)
END

EXEC DynamicTableSP 'table1'
EXEC DynamicTableSP 'table2'

Copy files between two different domains

We can copy files between two different domains using remote desktop local resources.

For example we have two different domains like India, USA, Now we need to copy files from a server which is in India to USA domain.

1. Create a network drive with India domain share path (using map network drive option on my computer)
2. Run -- MSTSC (Open Remote Desktop)
3. Type Server Name (USA Domain)
4. Click on options
5. Click on Local Resources Tab
6. Under local drives and resources -- More
7. Select the drive which you want to access on remote machine, we can select mapped drives as 
    well as local drives
8. Connect to remote machine
9. In Mycomputer you will see new drive along with local drives.
 

RunAs Examples

RunAs command used to execute any executable using different user credentials.

This can be done in two ways 1) Using windows 2) Using Command Prompt

From Command Prompt

runas /user:Domain\LoginName \\Sharepath
runas /user:windomain\kalyan \\ftpshare\\suresh    -- Provide credentials of given folder to access it from command prompt.




 

Cell Pointer Missing in Excel 2007

To hide / enable cell pointer in excel 2007


1. Office Button / File -- Options
2. Select Advanced
3. Check the option as per the screen shot

 

For Each Loop Container in SSIS

It defines a repeating control flow in a package. Loop implementation in the for each loop container is similar to the ForEach loop concept in programming language.
Types of EnumeratorsForeach File Enumerator – This enumerate files in a folder. We can get list of files which has extension .jpg from any folder and its subfolders
Foreach Item Enumerator – Enumerate values in an item
Foreach ADO Enumerator – Enumerate rows in tables
Foreach ADO.Net Schema Rowset Enumerator – Enumerate a schema
Foreach from variable Enumerator – Enumerate the value in a variable
Foreach nodelist Enumerator – Enumerates nodes in an XML document
Foreach SMO Enumerator – Enumerate a SMO Object.



Foreach Loop Container Properties1. Enumerator – Select appropriate enumerator (select Foreach File Enumerator)
2. Folder – Select source folder from where you want to enumerate files
3. Files – Specify extension (*.xls)
4. Drag the script task inside the for each loop container
5. Bind the variable User::File_Name to ReadOnly Variable
6. Below code snippet displays list of files which are matched with .xls extension from the specified folder.


MsgBox(Dts.Variables("File_Name").Value)
Dts.TaskResult = ScriptResults.Success

SSIS : Test connection failed because of an error in initializing provider. Unrecognized database format

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\Testdata.xlsx'.

 

If you encounter excel connection error then we need to type Excel 12.0 on Extended properties as below

 

 

Variables in SSIS

Variables
We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)
Below are few examples to create variables

BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons
1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables
We can declare a variable with required data type and we can initialize value at the time of declaration itself.

To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.

Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. Whenever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.

Example using Script Task
1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script
Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub
To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script.
3) Insert the below snippet to get the variable names
        For i = 0 To Dts.Variables.Count - 1
            MsgBox(Dts.Variables(i).Name.ToString())
        Next
 

How to Rollup days to months and years in Excel Pivot

If we have multiple dates, we can rollup them into months and years in excel pivot but we need to make sure it satisfies the below things

1. Format should be same for all dates to verify this, we need to apply filter for date column and then click on dropdown, generally excel will group rows by years and months, in the first image the few values are grouped to years few are not, that means there is an issue with the format, first correct it and verify, the second image doesn’t displays any extra records means you can go a head with 2nd step.

2. Insert – Pivot
3. Drag the date column into columns or rows section
4. Right click on any date --- select group – hold ctrl and select months and years
5. Move the column to columns section or rows section as per your requirement.

How to change script language in SSIS

From 2008 onwards we can implement c# script along with vb script in SSIS.

To change language for script task.
BIDS -- Tools - Options
Business Intelligence Designers - Integration Service Designers -- General
Script Language -- Choose the script language from here.

Working with variables in SSIS

Variables

We can define variables in SSIS using variables window with specific scope. We can define variables at different scope levels and with different datatype, for example defining variable at package level, defining variable at tool level (specific to that particular tool for example variable at for loop level, variable at Execute SQL Task level etc.,)

Below are few examples to create variables

BIDS – View – Other Windows – Variables – It displays variables window and it consists the following icons

1. Add variable
2. Delete Variable
3. Show System Variables
4. Show All Variables

We can declare a variable with required data type and we can initialize value at the time of declaration itself.

To make use of variable we need to bind variable to tool, for example if you are working with script task below is the example.
Create two variables varInt (Int) = 123, varString (string) = “hello” at package scope level. When ever if you create a variable then it should be bind with any control, otherwise we can’t access that variable.

Example using Script Task

1. Drag the script task to control flow window
2. Right Click – Edit – Edit Script

Public Sub Main()
' Add your code here
MsgBox(Dts.Variables.Count) -- This code returns zero because no binding done
Dts.TaskResult = ScriptResults.Success
End Sub

To bind the variables to script task
1) Right click on script task – Edit
2) ReadOnlyVariable – Click on Ellipse and browse the variables which you want to use in the script. 
3) Insert the below snippet to get the variable names
       For i = 0 To Dts.Variables.Count - 1
                MsgBox(Dts.Variables(i).Name.ToString())
       Next

Restore database with restart

Restore database with restart option is available since SQL Server 2005 onwards. This option is very helpful while restoring very large databases. If a restore was failed due to some reason then using Restart
option we can start restore from the failed point which helps to save time in VLDB.

If a restore operation fails then database will be in restoring mode, to bring it to online then we cam use restore with replace or we can use restore with restart

Restore with Replace -- It performs restore from starting onwards.

Restore with Restart -- It performs restore from failed point onwards. (we can save time in this option)

Restore database kalyandb from disk = 'c:\fullbackups\kalyandb.bak' with restart

The database principal owns a schema in the database, and cannot be dropped - Drop User

I got the below error while removing user from a database.

The database principal owns a schema in the database, and cannot be dropped

There is a orphan user in my database, no schemas or no objects were defined by that user. No login is associated with it, even though some times we could n't delete it.

For suppose you have a user with name "kalyan" owns db_owner schema, to drop the user first you need to move that schema to another user. If you dont have another user to move schema then use the following syntax to do that.

use database name
Alter Authorization On Schema::db_owner TO db_owner

If you have datareader schema then use datareader in place of db_owner. Now drop the user it will allow you to drop the user from the database.

The job failed. The owner (sa) of job does not have server access.

The job failed.  The owner (sa) of job does not have server access.

If you change any job owner to domain user or any sql server internal user, you need to restart the SQL Agent for executing jobs without any interruption.

How to convert redgate backup into native backup format

Redgate has a utility to convert compression backup into Microsoft native backup format.

SQBConverter is the utility using which we can convert the .sqb file into .bak file

Copy the below files from any redgate installed machine and do conversion using SQBConverterGUI on target machine to convert sqb file into bak file.

RgSqbConvHelper.dll
SQBConverter.exe
SQBConverterGUI

Creating an instance of the COM component with CLSID

We may get the below error when we are trying to edit the job steps created earlier.

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

To fix the issue we need to re-register the dts.dll

From command prompt
c:> regsvr32.exe dts.dll

Hint : Browse dts.dll in Microsoft SQL Server\version number\DTS\binn

SQL Agent Job Notifications

If we have a mission critical application then all Agent jobs which support that application is also that much critical, if any SQL Job fails then immediately we need to notify to a particular person or a group. Using SQL Server Operators we can achieve this.

Below is the procedure

Initially we need to configure database mail to send notifications from SQL Agent Jobs.
Refer - http://calyansql.blogspot.in/2012/10/configure-database-mail-sending-status.html

Steps
1. Configure Database Mail
2. SSMS –SQL Server Agent – Operators – Right Click – New Operator
Name – Notification Operator
Email name – test@yahoo.com
Pager e-mail name – test@yahoo.com
3. Under Jobs – Select job for which you want to configure notifications
4. Job – Properties – Notifications
 – Check Email – Select Operator from dropdown list and condition
 – Check Email – Select Operator from dropdown list and condition
5. SQL Server Agent – Properties
6. Select Alert System – Check Enable mail profile -- choose appropriate profile name from the mail profile dropdown list
7. Check Enable fail-safe operator – select operator name and check Email, Pager under Notify using section.
8. Click OK
   

Top Jobs -- SQL Agent Standard Reports

This standard report helps to identify the following things very quickly

20 Most Frequently Executed Jobs   -- Provides information about frequently executed jobs.
20 Most Frequently Failing Jobs  -- Provides information about frequently failed jobs, which helps to fix the job failures quickly.
20 Slowest Jobs  -- Provides information about the jobs which takes long time to execute, which helps to identify the performance.

Implement SQL Server Agent Job Output Files

For SQL Server Agent Jobs we can setup text files to store the output of the job.

If we want to see the output of any log we normally use the below procedure for verification.

SSMS -- SQL Server Agent -- Job Name -- Properties -- Steps -- Edit --  Advanced

Click on View beside the log to table check box, It displays output of the job that is executed recently.

If there is any critical job we need to maintain daily job output in a separate file with date and time stamp, we can achieve with using SQL Server Tokens

Suppose we have job called DB_Integrity_Verification, follow the below steps to create a job output file using the below procedure.

SSMS -- SQL Server Agent -- DB_Integrity_Verification -- Steps -- Edit -- Advanced

In Output file Text Box paste the below syntax

C:\JobLogs\DB_Integrity_Verification_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Click on OK and close the job.

If you execute the job it creates a text file with date and time stamp in the C:\JobLogs folder, make sure JobLogs folder exists on C: Drive.

Configure Database Mail in SQL Server 2008

1. SQL Server SSMS -- Management -- Database Mail Right Click -- Select Configure database mail
2. Click on Next Button
3. Select Setup Database Mail by performing the following tasks -- Next
4. Profile Name -- Kalyan_Mail_Profile
    Description -- This profile is to test SQL Server Job Notifications -- Add
5. Provide valid email address and smtp address along with port number - Ok
6. Click n Next Button
7. Select the profile which you recently added -- Next -- Next -- Finish

With the above steps database will be configured in SQL Server, You can test this by using below steps


1. SQL Server SSMS -- Management -- Database Mail -- Right Click -- Send Test Mail
2. Ensure mail profile name is correct
3. Specify email Id
4. Hit Send Test Mail Button

Database Mail Catalog views

select * from msdb..sysmail_allitems
select * from msdb..sysmail_sentitems
select * from msdb..sysmail_unsentitems
select * from msdb..sysmail_faileditems
select * from msdb..sysmail_mailattachments
select * from msdb..sysmail_event_log
select * from msdb..sysmail_profile
select * from msdb..sysmail_account

A database snapshot cannot be created because it failed to start

A database snapshot cannot be created because it failed to start

a) When you receive that error message you can run the checkdb on all databases and try what are trying to invoke.