Inserting Data in to a SQL Table from different Server.

Inserting Data in to a SQL Table from different Server.

We have different methods: I have used few of below

1)      Insert into script if there is Linked server created between the source and destination servers.

Execute below query from Source Server

insert into ServerName.DatabaseName.dbo.TableName

select * from dbo.TableName

2)       Impot/Export Wizard

Go to Start->All Programs-> SQL Server->Import and Export Data

Follow the below screenshots.

Choose the appropriate server and database in the above window and click next.

 Leave the default option and click next.

Select the table you want to export/Import and click on Edit mappings.

Check the button Drop and re-create destination table if you want.

You must check Enable identity Insert check box. Click Ok and Next.

Check the execution option you want to use on the below window and click next and next and Finish. You will see the error if any or it will load the data successfully.

Error: THE SERVER PRINCIPAL ALREADY EXISTS – MSG 1...

Error: THE SERVER PRINCIPAL ALREADY EXISTS – MSG 1...: Error: THE SERVER PRINCIPAL ALREADY EXISTS – MSG 15025, LEVEL 16, STATE 2, LINE 1 I cannot find the login physically though but gettin...

Different Process offset values in SSIS packages

You must use SSMS 2012 or better Version. SSMS for 2008 R2 will not work.

Expand Integration Services Catalogs-> SSISDB-> Folder-> Projects-> Project Name-> Packages-> select the package to run.

Right click the Package and choose Execute… 

On the Parameters tab of Execute Package page check the Environment checkbox.

Process Offset options are:

        0 – Run for the current day (Start and End dates are ignored

       n – Where n is the number of days of the offset. The formula is (Current date-n). Only a single day is    processed (Start and End dates are ignored)

       -1 – Use the Start and End date range.

        1 - Executes the package for Yesterdays date.

      Choose one which applicable to your execution and click OK to execute.

You may view the package execution status by answering Yes to the prompt shown below.

   

DataBase Renaming in SQL Server 2008 R2

I had renamed my Databases recently and wanted to share the experiences.

1) I have documented the steps need to be followed.
2) Prepared the scripts to rename the Databases.
3) Scripted the objects which are getting impacted by the renaming of DBs
3) Scripted the Jobs to Drop and Recreate.

Objects that are getting Impacted with the Renaming of DB:
Procedures
Views
Database Triggers
SQL Jobs

Finding Dependencies: Use the table "sys.sql_expression_dependencies" to find the cross Database dependencies. We have to make all dependent objects while renaming the Database

Renaming the DB:

-- Backup Database

--Set Database as a Single User 

ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

/* Change Logical File Name */

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'DBName_data'

, NEWNAME=N'DBName_data')

GO

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'DBName_log'

, NEWNAME=N'DBName_log')

GO

/* Detach Current Database */

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'DBName'

GO

/* Rename the files manually

DBName_data.mdf

DBName_log.ldf

*/

/* Attach Renamed Database Online */

USE [master]

GO

CREATE DATABASE DBName ON

(FILENAME = N'path'),

(FILENAME = N'path')

FOR ATTACH

GO

/* Set Database to Multi User*/

ALTER DATABASE DBName SET MULTI_USER

GO

/* Identify Database File Names */

USE master

GO

SELECT

name AS [Logical Name],

physical_name AS [DB File Path],

type_desc AS [File Type],

state_desc AS [State]

FROM sys.master_files

WHERE database_id = DB_ID(N'DBName')

GO

/*

Run the scripts in Views

Run the scripts in SPs

*/

Script out all objects by Alter statement and execute them after renaming the Database.
We cannot alter SQL Jobs hence take a script to drop and recreate and execute it.

Error: At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients". [SQLSTATE 42000] (Error 14624). The step failed.

Error: At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients". [SQLSTATE 42000] (Error 14624).  The step failed.

I had an issue this morning while using the procedure "msdb.dbo.sp_send_dbmail". Actually I have already the procedure developed in other server and am trying to use the same in different server. Received below error while executing the SQL Job which is calling this procedure.

I Investigated and came to know what the issue is: Below is my code using in procedure to send an alert. The profile DBFile_Notifications is not created in the current server hence the issue. I have created the account in msdb.dbo.sysoperators table and it works fine.

declare @email_address varchar(100)

select @email_address =email_address from msdb.dbo.sysoperators where name='DBFile_Notifications'

exec msdb.dbo.sp_send_dbmail 

@profile_name = 'JobMail',

@subject='Job Alert: CheckDBFileSize: CheckForExtendedFiles',

@recipients=@email_address,

@body = @tableHTML,

@body_format = 'HTML' ;