Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Sometime if will try to delete a publication we may get error message like: 

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Solution: 

Step 1: Execute following query: 

sp_changedbowner sa

Step 2: 

USE <Database Name>

EXECUTE sp_droppublication @publication = N'Publication Name'

GO

Note: Replace <Database Name> and 'Publication Name' to correct value.

Difference between temporary table and table variable in sql server

1. The scope of a temporary variable in session. For example, if we will execute the following query in the same tab of SQL server it will execute fine.

CREATE TABLE #temp(

    ID INT,

    Name VARCHAR(50)

)

Go

INSERT INTO #temp VALUES(1,'Davis')

SELECT * FROM #temp

But if we will execute the following query in another tab

SELECT * FROM #temp

We will get an error message like Invalid object name '#temp'.

Note: When we open new query page or tab in SQL server management studio it create a new session.

The scope of table variable is the batch. For example, we will execute following SQL queries

DECLARE @table TABLE(

    ID INT,

    Name VARCHAR(50)

)

Go

INSERT INTO @table VALUES(1,'Davis')

SELECT * FROM @table

We get error like: Must declare the table variable "@table"

Note: Go keyword in SQL server always creates a new batch.

2. A temporary table is same as a normal table with the different scope. We can create non – clustered index on a temporary table. For example:

CREATE TABLE #temp(

    ID INT,

    Name VARCHAR(50)

)

CREATE NONCLUSTERED INDEX temp_table_index

    ON #temp(ID , Name)

We cannot create a non-clustered index on the table variable. For example:

DECLARE @table TABLE(

    ID INT,

    Name VARCHAR(50)

)

CREATE NONCLUSTERED INDEX temp_table_index

    ON @table(ID , Name)

We will get some error message.

Note: If we have a large amount of data and performing quires on it then the temporary table may be a better option than table variable since we can increase the performance of a query by creating indexes on it.  

3. There is not any table lock concept in the table variables. For example, it is correct to write:

SELECT * FROM #temp WITH(NOLOCK)

While it is incorrect to write:

SELECT * FROM @table WITH(NOLOCK)

3. Unlike to the temporary table, table variables also not support transaction and logging. For example:

CREATE TABLE #temp(

 ID INT,

 Name VARCHAR(50)

)

BEGIN TRANSACTION

    INSERT INTO #temp VALUES(1,'Davis')

    ROLLBACK

    SELECT * FROM #temp

Select query will not return any rows since it has been rollback while it will perform the same operation on table variable for example:

DECLARE @table AS TABLE(

 ID INT,

 Name VARCHAR(50)

)

BEGIN TRANSACTION

    INSERT INTO @table VALUES(1,'Davis')

    ROLLBACK

    SELECT * FROM @table

In this case, select query will return one row like

ID	Name
1	Davis

Since table variables don't support transaction and rows has not been rolled backed.

4. Tables variables in the stored procedures are pre-compiled while temporary tables are not.

5. We can pass the table variables as a parameter of stored procedures or functions. For example:

 CREATE TYPE InfoType AS TABLE(

    ID INT,

    Name VARCHAR(50)

)

GO

CREATE PROCEDURE GetInfo(

    @table AS InfoType READONLY

)

AS

BEGIN

    SELECT * FROM @table

END

Now we will execute the above stored procedure by passing table variable.

DEClARE @info AS InfoType

INSERT INTO @info VALUES(1,'Davis')

EXECUTE GetInfo @info

Output:

ID	Name
1	Davis

While we cannot pass the temporary table. For example:

CREATE TABLE #temp(

    ID INT,

    Name VARCHAR(50)

)

INSERT INTO #temp VALUES(1,'Davis')

EXECUTE GetInfo #temp

We will get the error message like nvarchar is incompatible with InfoType

6. A table value function can return table variable but it cannot return temporary table.

7. With help of SELECT INTO we can directly create the temporary table while it is not possible for temporary variables. For example:

SELECT * INTO #temp

FROM Student

While it is not possible in case of table variables. For example

SELECT * INTO @table

FROM Student

If we will execute above query we will get error message like: Incorrect syntax near '@table'

How to use a variable in ORDER BY clause in SQL server.
ORDER BY clause in common table expression in SQL server.
ORDER BY clause in view in SQL server.
OVER clause in aggregate function in sql server
Schema in sql server
Stored procedure parameters in sql server

Setting the File System Task properties dynamically in SSIS

We can set all the File System Task properties or values in variables, configuration file, database etc. For this it is necessary to set default values. 

Storing the File System Task properties in variables.

Step 1: Declare the variables for each property. In this example we will set the Source, Destination, Operation, and OverWriteDestinationFile properties.

Operation: This property is integer type which has following meaning:

Operation	Meaning
1	Move file
2	Delete File
3	Rename file
4	Set Attributes
5	Create directory
6	Copy directory
7	Move directory
8	Delete directory
9	Delete directory content
10	Copy file

Source: It is string type which the name of the file connection of source folder.

Destination: It is string type which the name of the file connection of destination folder.

OverWriteDestinationFile: It is Boolean type. If it is true then it allows the content in the destination folder. To create a Source and Destination file connection right click on Connection Manager and select New File Connection.

We will get File Connection Manager Editor. Select the Usage Type and Folder.

Now click on OK button. In the same way create the destination file connection.

Step 2:To declare variable go the SSIS -> Variables 

Click on Add Variable icon. Write down the variable name, scope, Data type and value for each property.

Step 3: Set the default values of all the properties of File System Task as shown in following figure:

Note: It is necessary to assign the default values it will be used only if we have not set the values of properties in the expression.

Step 3: Click on expression Tab. Click on the expression button. We will get the Property Expression Editor.

Step 4: Select Source property from Property combo box.  Click on expression button. We will get Expression Builder. Expand the Variable, drag and drop the SourceConn variable in the Expression text field and click on OK button.

Step 5: In the same way assign a variable to properties Destination, Operation, and OverwriteDestinationFile.

Click on Ok button.

Step 6: Right click on SSIS file connection in Connection manages and select the property. Click on the Expression button and set the Connection String property. In the same way assign DestinationPath variable to Destination Connection String property.

Step 7: Now run the package. If everything is fine we will get following screen:

Storing the File System Task variables in configuration file.

Step 1: Go to the SSIS -> Package Configuration

Step 2: Checked the Enable Package configuration and click on Add button.

Step 3: Click on Next button in the Welcome to the package configuration wizard. In the next window

Configuration type:  Choose XML configuration file

Configuration file name: Specify the configuration file name with path.

Click on Next button.

Step 4: Select the variables and properties which you want to keep in the configuration file. In this example we are selecting only value property of all variables.

Click on Next then Finish button.

Configuration file will look like this:

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo

      GeneratedBy="xxxx-PC\xxxx"

      GeneratedFromPackageName="Package"

      GeneratedFromPackageID="{A02ACEDC-9A5B-42C4-A207-C9ABA387853C}"

      GeneratedDate="11/13/2011 3:28:06 AM"/>

  </DTSConfigurationHeading>

  <Configuration

    ConfiguredType="Property"

    Path="\Package.Variables[User::DestinationConn].Properties[Value]"

    ValueType="String">

    <ConfiguredValue>Destination</ConfiguredValue>

  </Configuration>

  <Configuration

    ConfiguredType="Property"

    Path="\Package.Variables[User::DestinationPath].Properties[Value]"

    ValueType="String">

    <ConfiguredValue>C:\Destination</ConfiguredValue>

  </Configuration><Configuration

    ConfiguredType="Property"

    Path="\Package.Variables[User::Operation].Properties[Value]"

    ValueType="Int32">

    <ConfiguredValue>6</ConfiguredValue>

  </Configuration><Configuration

    ConfiguredType="Property"

    Path="\Package.Variables[User::SourceConn].Properties[Value]"

    ValueType="String">

    <ConfiguredValue>SSIS</ConfiguredValue>

  </Configuration>

  <Configuration

    ConfiguredType="Property"

    Path="\Package.Variables[User::SourcePath].Properties[Value]"

    ValueType="String">

    <ConfiguredValue>C:\SSIS</ConfiguredValue>

  </Configuration>

</DTSConfiguration>