How old is the Database if restored from the Backup file
SELECT DISTINCT TOP 100
RH.destination_database_name,
RF.file_number,
RH.restore_date,
RF.destination_phys_drive,
RF.destination_phys_name,
[Backup Set Name]=BS.name,
BS.description,
BS.database_creation_date,
BS.backup_start_date,
BS.database_name,
BS.server_name,
RH.restore_type,
RH.replace,
RH.stop_at
FROM msdb.dbo.restorehistory AS RH
LEFT OUTER JOIN msdb.dbo.restorefile AS RF
ON RF.restore_history_id = RH.restore_history_id
LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
ON RG.restore_history_id = RH.restore_history_id
LEFT OUTER JOIN msdb.dbo.backupset AS BS
ON BS.backup_set_id = RH.backup_set_id
WHERE 1=1
-- AND RH.destination_database_name = N'MyDatabaseName' -- SELECT DB_NAME()
-- restore_type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- AND RH.restore_type = 'D'
ORDER BY RH.restore_date DESC,
RF.file_number DESC
Wednesday, November 22, 2006
Creating Simple Search Stored Procedure:
Create the Table Candidate:
CREATE TABLE dbo.Candidate
(
CandidateID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
FirstName NVARCHAR(255) DEFAULT NULL,
LastName NVARCHAR(255) DEFAULT NULL,
Address1 NVARCHAR(255) DEFAULT NULL,
Address2 NVARCHAR(255) DEFAULT NULL,
Address3 NVARCHAR(255) DEFAULT NULL,
City NVARCHAR(255) DEFAULT NULL,
ZIP NVARCHAR(255) DEFAULT NULL
)
GO
Search Procedure:
This search procedure,we can input any combination of input parameters. Based on input parameter ,I construct the dynamic SQL and use SP_Executesql to execute the dynamic string.
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('SP_GetCandidateDetails')
AND type = 'P' )
BEGIN
DROP PROCEDURE dbo.SP_GetCandidateDetails
END
go
CREATE PROCEDURE [dbo].[SP_GetCandidateDetails]
(
@FirstName NVARCHAR(255) = NULL,
@LastName NVARCHAR(255) = NULL,
@Address1 NVARCHAR(255) = NULL,
@Address2 NVARCHAR(255) = NULL,
@Address3 NVARCHAR(255) = NULL,
@City NVARCHAR(255) = NULL,
@ZIP NVARCHAR(255) = NULL
)
AS
BEGIN
SET nocount ON
-- DECLARE @FirstName NVARCHAR(255),
-- @LastName NVARCHAR(255),
-- @Address1 NVARCHAR(255),
-- @Address2 NVARCHAR(255),
-- @Address3 NVARCHAR(255),
-- @City NVARCHAR(255),
-- @ZIP NVARCHAR(255)
--SANITIZE THE USER INPUT
SET @FirstName = dbo.fn_CheckSQLInjection(@FirstName)--='FirstName'
SET @LastName = dbo.fn_CheckSQLInjection(@LastName)--'LastName'
SET @Address1 = dbo.fn_CheckSQLInjection(@Address1)--'Address1'
SET @Address2 = dbo.fn_CheckSQLInjection(@Address2)--'Address2'
SET @Address3 = dbo.fn_CheckSQLInjection(@Address3)--''
SET @City = dbo.fn_CheckSQLInjection(@City)--'City'
SET @ZIP = dbo.fn_CheckSQLInjection(@ZIP)--'Zip'
DECLARE @dsql NVARCHAR(4000)
--set @dsql='select * from Candidate'
--DECLARE @dsql NVARCHAR(4000)
IF ( ( @FirstName IS NULL )
OR ( LEN(@FirstName) = 0 )
)
BEGIN
SET @dsql = NULL
END
ELSE
BEGIN
IF ( CHARINDEX('*', @FirstName) > 0 )
BEGIN
SET @FirstName = REPLACE(@FirstName, '*', '%')
SET @dsql = 'SELECT * from Candidate where FirstName LIKE ''' + @FirstName + ''''
END
ELSE
BEGIN
SET @dsql = 'SELECT * from Candidate where FirstName = ''' + @FirstName + ''''
END
END
-- SELECT @dsql
IF ( ( @LastName IS NULL )
OR ( LEN(@LastName) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @LastName) ) > 0
BEGIN
SET @LastName = REPLACE(@LastName, '*', '%')
SET @dsql = ISNULL(@dsql + ' and LastName LIKE ''' + @LastName + '''',
'SELECT * from Candidate where LastName LIKE ''' + @LastName + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and LastName = ''' + @LastName + ''' ',
'SELECT * from Candidate where LastName = ''' + @LastName + '''')
END
END
--SELECT @dsql
IF ( ( @Address1 IS NULL )
OR ( LEN(@Address1) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address1) ) > 0
BEGIN
SET @Address1 = REPLACE(@Address1, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address1 LIKE ''' + @Address1 + '''',
'SELECT * from Candidate where Address1 LIKE ''' + @Address1 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address1 = ''' + @Address1 + ''' ',
'SELECT * from Candidate where Address1 = ''' + @Address1 + '''')
END
END
--SELECT @dsql
IF ( ( @Address2 IS NULL )
OR ( LEN(@Address2) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address2) ) > 0
BEGIN
SET @Address2 = REPLACE(@Address2, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address2 LIKE ''' + @Address2 + '''',
'SELECT * from Candidate where Address2 LIKE ''' + @Address2 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address2 = ''' + @Address2 + ''' ',
'SELECT * from Candidate where Address2 = ''' + @Address2 + '''')
END
END
--SELECT @dsql
IF ( ( @Address3 IS NULL )
OR ( LEN(@Address3) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address3) ) > 0
BEGIN
SET @Address3 = REPLACE(@Address3, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address3 LIKE ''' + @Address3 + '''',
'SELECT * from Candidate where Address3 LIKE ''' + @Address3 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address3 = ''' + @Address3 + ''' ',
'SELECT * from Candidate where Address3 = ''' + @Address3 + '''')
END
END
-- SELECT @dsql
IF ( ( @City IS NULL )
OR ( LEN(@City) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @City) ) > 0
BEGIN
SET @City = REPLACE(@City, '*', '%')
SET @dsql = ISNULL(@dsql + ' and City LIKE ''' + @City + '''',
'SELECT * from Candidate where City LIKE ''' + @City + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and City = ''' + @City + ''' ',
'SELECT * from Candidate where City = ''' + @City + '''')
END
END
--SELECT @dsql
IF ( ( @ZIP IS NULL )
OR ( LEN(@ZIP) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @ZIP) ) > 0
BEGIN
SET @ZIP = REPLACE(@ZIP, '*', '%')
SET @dsql = ISNULL(@dsql + ' and ZIP LIKE ''' + @ZIP + '''',
'SELECT * from Candidate where ZIP LIKE ''' + @ZIP + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and ZIP = ''' + @ZIP + ''' ',
'SELECT * from Candidate where ZIP = ''' + @ZIP + '''')
END
END
EXECUTE SP_EXECUTESQL @dsql
SELECT @dsql
END
FUNCTION USED TO HANDLE SINGLE QUOTES:
CREATE FUNCTION [dbo].[fn_CheckSQLInjection] ( @string NVARCHAR(255) )
RETURNS NVARCHAR(255)
AS BEGIN
IF ( ( @string IS NULL )
OR ( LEN(@string) ) = 0
)
RETURN @string
ELSE
SELECT @string = REPLACE(@string,'''','''''' )
RETURN @string
END
Create the Table Candidate:
CREATE TABLE dbo.Candidate
(
CandidateID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
FirstName NVARCHAR(255) DEFAULT NULL,
LastName NVARCHAR(255) DEFAULT NULL,
Address1 NVARCHAR(255) DEFAULT NULL,
Address2 NVARCHAR(255) DEFAULT NULL,
Address3 NVARCHAR(255) DEFAULT NULL,
City NVARCHAR(255) DEFAULT NULL,
ZIP NVARCHAR(255) DEFAULT NULL
)
GO
Search Procedure:
This search procedure,we can input any combination of input parameters. Based on input parameter ,I construct the dynamic SQL and use SP_Executesql to execute the dynamic string.
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('SP_GetCandidateDetails')
AND type = 'P' )
BEGIN
DROP PROCEDURE dbo.SP_GetCandidateDetails
END
go
CREATE PROCEDURE [dbo].[SP_GetCandidateDetails]
(
@FirstName NVARCHAR(255) = NULL,
@LastName NVARCHAR(255) = NULL,
@Address1 NVARCHAR(255) = NULL,
@Address2 NVARCHAR(255) = NULL,
@Address3 NVARCHAR(255) = NULL,
@City NVARCHAR(255) = NULL,
@ZIP NVARCHAR(255) = NULL
)
AS
BEGIN
SET nocount ON
-- DECLARE @FirstName NVARCHAR(255),
-- @LastName NVARCHAR(255),
-- @Address1 NVARCHAR(255),
-- @Address2 NVARCHAR(255),
-- @Address3 NVARCHAR(255),
-- @City NVARCHAR(255),
-- @ZIP NVARCHAR(255)
--SANITIZE THE USER INPUT
SET @FirstName = dbo.fn_CheckSQLInjection(@FirstName)--='FirstName'
SET @LastName = dbo.fn_CheckSQLInjection(@LastName)--'LastName'
SET @Address1 = dbo.fn_CheckSQLInjection(@Address1)--'Address1'
SET @Address2 = dbo.fn_CheckSQLInjection(@Address2)--'Address2'
SET @Address3 = dbo.fn_CheckSQLInjection(@Address3)--''
SET @City = dbo.fn_CheckSQLInjection(@City)--'City'
SET @ZIP = dbo.fn_CheckSQLInjection(@ZIP)--'Zip'
DECLARE @dsql NVARCHAR(4000)
--set @dsql='select * from Candidate'
--DECLARE @dsql NVARCHAR(4000)
IF ( ( @FirstName IS NULL )
OR ( LEN(@FirstName) = 0 )
)
BEGIN
SET @dsql = NULL
END
ELSE
BEGIN
IF ( CHARINDEX('*', @FirstName) > 0 )
BEGIN
SET @FirstName = REPLACE(@FirstName, '*', '%')
SET @dsql = 'SELECT * from Candidate where FirstName LIKE ''' + @FirstName + ''''
END
ELSE
BEGIN
SET @dsql = 'SELECT * from Candidate where FirstName = ''' + @FirstName + ''''
END
END
-- SELECT @dsql
IF ( ( @LastName IS NULL )
OR ( LEN(@LastName) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @LastName) ) > 0
BEGIN
SET @LastName = REPLACE(@LastName, '*', '%')
SET @dsql = ISNULL(@dsql + ' and LastName LIKE ''' + @LastName + '''',
'SELECT * from Candidate where LastName LIKE ''' + @LastName + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and LastName = ''' + @LastName + ''' ',
'SELECT * from Candidate where LastName = ''' + @LastName + '''')
END
END
--SELECT @dsql
IF ( ( @Address1 IS NULL )
OR ( LEN(@Address1) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address1) ) > 0
BEGIN
SET @Address1 = REPLACE(@Address1, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address1 LIKE ''' + @Address1 + '''',
'SELECT * from Candidate where Address1 LIKE ''' + @Address1 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address1 = ''' + @Address1 + ''' ',
'SELECT * from Candidate where Address1 = ''' + @Address1 + '''')
END
END
--SELECT @dsql
IF ( ( @Address2 IS NULL )
OR ( LEN(@Address2) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address2) ) > 0
BEGIN
SET @Address2 = REPLACE(@Address2, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address2 LIKE ''' + @Address2 + '''',
'SELECT * from Candidate where Address2 LIKE ''' + @Address2 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address2 = ''' + @Address2 + ''' ',
'SELECT * from Candidate where Address2 = ''' + @Address2 + '''')
END
END
--SELECT @dsql
IF ( ( @Address3 IS NULL )
OR ( LEN(@Address3) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @Address3) ) > 0
BEGIN
SET @Address3 = REPLACE(@Address3, '*', '%')
SET @dsql = ISNULL(@dsql + ' and Address3 LIKE ''' + @Address3 + '''',
'SELECT * from Candidate where Address3 LIKE ''' + @Address3 + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and Address3 = ''' + @Address3 + ''' ',
'SELECT * from Candidate where Address3 = ''' + @Address3 + '''')
END
END
-- SELECT @dsql
IF ( ( @City IS NULL )
OR ( LEN(@City) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @City) ) > 0
BEGIN
SET @City = REPLACE(@City, '*', '%')
SET @dsql = ISNULL(@dsql + ' and City LIKE ''' + @City + '''',
'SELECT * from Candidate where City LIKE ''' + @City + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and City = ''' + @City + ''' ',
'SELECT * from Candidate where City = ''' + @City + '''')
END
END
--SELECT @dsql
IF ( ( @ZIP IS NULL )
OR ( LEN(@ZIP) = 0 )
)
BEGIN
SET @dsql = @dsql
END
ELSE
BEGIN
IF ( CHARINDEX('*', @ZIP) ) > 0
BEGIN
SET @ZIP = REPLACE(@ZIP, '*', '%')
SET @dsql = ISNULL(@dsql + ' and ZIP LIKE ''' + @ZIP + '''',
'SELECT * from Candidate where ZIP LIKE ''' + @ZIP + '''')
END
ELSE
BEGIN
SET @dsql = ISNULL(@dsql + ' and ZIP = ''' + @ZIP + ''' ',
'SELECT * from Candidate where ZIP = ''' + @ZIP + '''')
END
END
EXECUTE SP_EXECUTESQL @dsql
SELECT @dsql
END
FUNCTION USED TO HANDLE SINGLE QUOTES:
CREATE FUNCTION [dbo].[fn_CheckSQLInjection] ( @string NVARCHAR(255) )
RETURNS NVARCHAR(255)
AS BEGIN
IF ( ( @string IS NULL )
OR ( LEN(@string) ) = 0
)
RETURN @string
ELSE
SELECT @string = REPLACE(@string,'''','''''' )
RETURN @string
END
Monday, September 11, 2006
SOFTWARE DEVELOPMENT AND LEAN SYSTEM CONCEPTS:
I have transitioned from Lean Manufacturing Backgroud to software Field.I am amazed at the lean concepts applicability to the software development Methods.
Lean Primary preaches any system to identify the process into these two categories:
1.Value added processes
2.Non-Value added processes.
Value added processes:
It is simple as it sounds.Identify the process which adds value to the product.
Eliminate the Non-value added processes.As we all know,it is not possible to identify and eliminate all the non-value added processes.
For example Testing Does not really add value to the Customers.Still all the corporations ubiquitiously have the Test Team.Why do they have Test Team if they do not add value to the Products?
Does it mean,the management does not have enough confidence on their development team to deliver the bug free product?
Does it mean,the test team improves the quality of the products delivered to the customers ?
Does it mean,every corporation is having the test team and let us have one too?
So what is the purpose of the Test Team in Software Development life cycle?
Most of the testers think,The responsibility of the Team to ensure the Product meets/exceeds the requirements given in the functional specification,to identify the deviations from the expected behavior,so developers can fix those issues.
Why can't developers do the task of developing the applications which meets/exceeds the expections provided in the functional specification(Customer expectations).
If you ask any tester,what's your core responsibility?
Most of the testers answer ,to find the deviations (defects,bugs,etc) and let the developers and analysts know those issues and make them fix those issues.
Does it mean the job of the testers is to find the defects inDevelopers/Analysts WOrk?
Does it mean the Devlopers/Analysts are not delivering what they suppose to deliver?
If the product goes to the customers with the deviations from the expected behavior,who owns the responsibility? Testers or Devlopers or Analysts or Product management or Program Management?
Over the years,the Manufacturing has developed and matured where as the Software developement has not matured.
Lean system preaches the responsisbility and ownership ;focus on people;commitment in the team;etc.It all sounds really nice.why it is so hard to follow those principles?
It is more cultural.We have always been taught the idea of following hierarchial structure.
Higher up in the Hierarchy means they have more power and they are always right.
How many times,do you confront your manager or some one higher up about the designs,methodology,etc.
We blindly follow the organization hierarchy structure whether it is good or bad.We feel insecure about our jobs and reviews.We have the feeling,what would happen if I say this,What would happen if I say that,etc?
Why is it not possible to create the transparent work environment where every one is part of the team and given the equal importance,foster their creative thinking,be original,not to alter their opionions and thoghts to suit the opinions and thoughts of their bosses?
Lean teaches us treat everyone with respect ,dignity and disregard thier designation,race,color,etc.How many of us are able to practice these principles?
I have transitioned from Lean Manufacturing Backgroud to software Field.I am amazed at the lean concepts applicability to the software development Methods.
Lean Primary preaches any system to identify the process into these two categories:
1.Value added processes
2.Non-Value added processes.
Value added processes:
It is simple as it sounds.Identify the process which adds value to the product.
Eliminate the Non-value added processes.As we all know,it is not possible to identify and eliminate all the non-value added processes.
For example Testing Does not really add value to the Customers.Still all the corporations ubiquitiously have the Test Team.Why do they have Test Team if they do not add value to the Products?
Does it mean,the management does not have enough confidence on their development team to deliver the bug free product?
Does it mean,the test team improves the quality of the products delivered to the customers ?
Does it mean,every corporation is having the test team and let us have one too?
So what is the purpose of the Test Team in Software Development life cycle?
Most of the testers think,The responsibility of the Team to ensure the Product meets/exceeds the requirements given in the functional specification,to identify the deviations from the expected behavior,so developers can fix those issues.
Why can't developers do the task of developing the applications which meets/exceeds the expections provided in the functional specification(Customer expectations).
If you ask any tester,what's your core responsibility?
Most of the testers answer ,to find the deviations (defects,bugs,etc) and let the developers and analysts know those issues and make them fix those issues.
Does it mean the job of the testers is to find the defects inDevelopers/Analysts WOrk?
Does it mean the Devlopers/Analysts are not delivering what they suppose to deliver?
If the product goes to the customers with the deviations from the expected behavior,who owns the responsibility? Testers or Devlopers or Analysts or Product management or Program Management?
Over the years,the Manufacturing has developed and matured where as the Software developement has not matured.
Lean system preaches the responsisbility and ownership ;focus on people;commitment in the team;etc.It all sounds really nice.why it is so hard to follow those principles?
It is more cultural.We have always been taught the idea of following hierarchial structure.
Higher up in the Hierarchy means they have more power and they are always right.
How many times,do you confront your manager or some one higher up about the designs,methodology,etc.
We blindly follow the organization hierarchy structure whether it is good or bad.We feel insecure about our jobs and reviews.We have the feeling,what would happen if I say this,What would happen if I say that,etc?
Why is it not possible to create the transparent work environment where every one is part of the team and given the equal importance,foster their creative thinking,be original,not to alter their opionions and thoghts to suit the opinions and thoughts of their bosses?
Lean teaches us treat everyone with respect ,dignity and disregard thier designation,race,color,etc.How many of us are able to practice these principles?
Sunday, September 10, 2006
BackUp and Restore SQL Server Databases:
Backing up the SQL Server Database:
BackUp Types:
Full backups:
This back up type backs up the entire database including part of the transaction log
Full differential backups :
A full differential backup records only the data that has changed since the last database backup.
Partial backups:
A partial backup is similar to a full database backup, but need contain only the primary filegroup and all read-write filegroups.
Partial differential backup:
A partial differential backup contains only data that has changed in the primary and all read-write filegroups.
File and filegroup backup:
A filegroup backup is a single backup of all files in the filegroup and is equivalent to explicitly listing all files in the filegroup when creating the backup. Files in a filegroup backup can be restored individually or as a group.
Simple Backup Command:
BACKUP DATABASE [DATABASENAME]
TO DISK=[BACKUPPATH]
Its always recommeded to save the backup with the .bak file extension.
Backup options:
MIRROR TO: Creates the another backup in the MirrorTo disk.
BACKUP DATABASE [DATABASENAME]
TO DISK=[BACKUPPATH]
MIRROR TO DISK=[MIRROR TO PATH NAME]
WITH [OPTIONS]
WITH OPTIONS:
FORMAT NOFORMAT
BLOCKSIZE
CHECKSUM NO_CHECKSUM
EXPIREDATE
RETAINDAYS
PASSWORD
INITNOINIT
SKIPNOSKIP
REWIND NOREWIND
UNLOAD NOUNLOAD
RESTART
STATS
COPY_ONLY
Backing up the SQL Server Database:
BackUp Types:
Full backups:
This back up type backs up the entire database including part of the transaction log
Full differential backups :
A full differential backup records only the data that has changed since the last database backup.
Partial backups:
A partial backup is similar to a full database backup, but need contain only the primary filegroup and all read-write filegroups.
Partial differential backup:
A partial differential backup contains only data that has changed in the primary and all read-write filegroups.
File and filegroup backup:
A filegroup backup is a single backup of all files in the filegroup and is equivalent to explicitly listing all files in the filegroup when creating the backup. Files in a filegroup backup can be restored individually or as a group.
Simple Backup Command:
BACKUP DATABASE [DATABASENAME]
TO DISK=[BACKUPPATH]
Its always recommeded to save the backup with the .bak file extension.
Backup options:
MIRROR TO: Creates the another backup in the MirrorTo disk.
BACKUP DATABASE [DATABASENAME]
TO DISK=[BACKUPPATH]
MIRROR TO DISK=[MIRROR TO PATH NAME]
WITH [OPTIONS]
WITH OPTIONS:
FORMAT NOFORMAT
BLOCKSIZE
CHECKSUM NO_CHECKSUM
EXPIREDATE
RETAINDAYS
PASSWORD
INITNOINIT
SKIPNOSKIP
REWIND NOREWIND
UNLOAD NOUNLOAD
RESTART
STATS
COPY_ONLY
Wednesday, August 23, 2006
Creating the Build Scripts to Automate the Incremental Daily Database Builds:
I had the Requirement to run multiple scripts to create the Database, Stored Procedures, Functions, Triggers; etc.It was time consuming to run the scripts manually.
So I thought of using the Batch Files to automate the process.
Tools Used:
SQLCMD: This command line utility tool is shipped with SQL Server Database Engine and provides efficient integration with Batch files and SQL Server Databases.
Before running the scripts, I have to make sure the Database Exists.
If the database does not exist, I have to create the database with the Name passed as Parameter and then execute the T-SQL Scripts. I selected SQLCMD Tool to accomplish this task.
SYNTAX:
SQLCMD
-S Server Name
-d Database Name
-b Exit on Error in the Batch
-Q Run the Query in Database and close the Connection with the Database
-q Run the Query in Database and leave the Connection Open with the Server
SQLCMD Uses the OLEDB Interface to connect to the Server where as the
SQL Management Studio uses the SQLCLIENT to connect to the SQL Server.
It might cause some difference in the Query Execution.
Passing the variables to the command batch file:
In the Batch file all the variables are treated as strings. It’s different from any other procedural languages. So we do not have to define the variable data type.
Set DatabaseServer =%1
It means the first argument we pass in the command line would be the Database Server Name.
Set DatbaseName=%2 .
The second argument would be the Database Name. While passing the arguments in the command line, there has to be space between the arguments.
The final Build Script:
ECHO OFF
REM: -b On Error Batch Abort in sqlcmd
REM: >> or > this operator can be used in place of -o option to append the output.
set ServerName=%1
set DatabaseName=%2
set WorkDirectory =%3
set LogfileName =%4
echo The ServerName used in applying this Build: %1
echo The DatabaseName used in applying this Build:%2
echo The directory used in the Build: %3
echo The Logfile Used to log Errors in this Build: %4
echo: The Build Start time %date% %time%
ECHO: Checking the existence of database: %2 in server: %1
Echo: If not exists creating the Database: %2 in Server: %1
set Query="IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='%2')
BEGIN CREATE DATABASE %2 END"
sqlcmd -S %1 -d Master -Q %Query%
echo: Checking the connection to the Server: %1 and Database: %2
set qry ="set nocount on"
sqlcmd -S %1 -d %2 -Q "%qry%"
IF %Errorlevel% NEQ 0 goto FailureExit:
Echo Applying the Scripts at Server: %1 and Database %2
for %%B in (%3\*.sql) do (sqlcmd -S %1 -d %2 -b -i "%%B" >> %4)
IF %Errorlevel% NEQ 0 goto FailureExit:
REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:
REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:
Echo The Build successfully applied at Server: %1 and Database: %2
Echo The Build completed Time %date% %Time%
:FailureExit echo The build Cancelled at Server: %1 and Database: %2
Processing the SQL Files in the Subdirectory
The following batch file processes the .sql files in the Given Server Name, DatabaseName, and WorkingDirectory.The log file is passed as the fourth Parameter where the output of the command execution saved.
echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /d %%B in (%3\*) do (
for %%C in ("%%B\*.sql") do sqlcmd -S %1 -d %2 -i %%C >>%4)
Iterating through the Files without the Double loop:
echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /r %3 %%B in (*.sql) do (sqlcmd -S %1 -d %2 -i %%B >>%4)
Using the /r command to search the .sql extension files in the Foldername passed as 3rd
parameter.
I had the Requirement to run multiple scripts to create the Database, Stored Procedures, Functions, Triggers; etc.It was time consuming to run the scripts manually.
So I thought of using the Batch Files to automate the process.
Tools Used:
SQLCMD: This command line utility tool is shipped with SQL Server Database Engine and provides efficient integration with Batch files and SQL Server Databases.
Before running the scripts, I have to make sure the Database Exists.
If the database does not exist, I have to create the database with the Name passed as Parameter and then execute the T-SQL Scripts. I selected SQLCMD Tool to accomplish this task.
SYNTAX:
SQLCMD
-S Server Name
-d Database Name
-b Exit on Error in the Batch
-Q Run the Query in Database and close the Connection with the Database
-q Run the Query in Database and leave the Connection Open with the Server
SQLCMD Uses the OLEDB Interface to connect to the Server where as the
SQL Management Studio uses the SQLCLIENT to connect to the SQL Server.
It might cause some difference in the Query Execution.
Passing the variables to the command batch file:
In the Batch file all the variables are treated as strings. It’s different from any other procedural languages. So we do not have to define the variable data type.
Set DatabaseServer =%1
It means the first argument we pass in the command line would be the Database Server Name.
Set DatbaseName=%2 .
The second argument would be the Database Name. While passing the arguments in the command line, there has to be space between the arguments.
The final Build Script:
ECHO OFF
REM: -b On Error Batch Abort in sqlcmd
REM: >> or > this operator can be used in place of -o option to append the output.
set ServerName=%1
set DatabaseName=%2
set WorkDirectory =%3
set LogfileName =%4
echo The ServerName used in applying this Build: %1
echo The DatabaseName used in applying this Build:%2
echo The directory used in the Build: %3
echo The Logfile Used to log Errors in this Build: %4
echo: The Build Start time %date% %time%
ECHO: Checking the existence of database: %2 in server: %1
Echo: If not exists creating the Database: %2 in Server: %1
set Query="IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='%2')
BEGIN CREATE DATABASE %2 END"
sqlcmd -S %1 -d Master -Q %Query%
echo: Checking the connection to the Server: %1 and Database: %2
set qry ="set nocount on"
sqlcmd -S %1 -d %2 -Q "%qry%"
IF %Errorlevel% NEQ 0 goto FailureExit:
Echo Applying the Scripts at Server: %1 and Database %2
for %%B in (%3\*.sql) do (sqlcmd -S %1 -d %2 -b -i "%%B" >> %4)
IF %Errorlevel% NEQ 0 goto FailureExit:
REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:
REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:
Echo The Build successfully applied at Server: %1 and Database: %2
Echo The Build completed Time %date% %Time%
:FailureExit echo The build Cancelled at Server: %1 and Database: %2
Processing the SQL Files in the Subdirectory
The following batch file processes the .sql files in the Given Server Name, DatabaseName, and WorkingDirectory.The log file is passed as the fourth Parameter where the output of the command execution saved.
echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /d %%B in (%3\*) do (
for %%C in ("%%B\*.sql") do sqlcmd -S %1 -d %2 -i %%C >>%4)
Iterating through the Files without the Double loop:
echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /r %3 %%B in (*.sql) do (sqlcmd -S %1 -d %2 -i %%B >>%4)
Using the /r command to search the .sql extension files in the Foldername passed as 3rd
parameter.
Wednesday, August 09, 2006
IMPORTING AND EXPORTING DATA FROM SQL SERVER TO EXCEL FILE:
Today I had the requirement to Read the Excel File and Extract the Data from SQL Server based on the Excel File and then Export the Data to the Different Excel Files.
So I break down the Task into Three Aspects:
Importing the Data From Excel File: OPENDATASOURCE:
Invoking the Command Prompt :XP_CMDSHELL
Using the Bcp.exe to Export the Data to the Excel File: BCP.EXE
Before running those commands,the SQL server should be enabled to run xp_cmdshell and OpenDataSource Commands.Those are server wide settings.
1.Reading the Data From Excel:
select * into temp from opendatasource
('microsoft.jet.oledb.4.0','data source= "G:Test.xls";extended properties=excel 5.0')...sheet1$
This reads the Data From the Excel File: G:\Test.xls
The sheetName :sheet1$
2.Using the XP_CMDSHELL:
I use xp_cmdshell with the BCP Option.
This invokes the command Prompt.
3.Using the BCP.EXE:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TestSp_ExcelFile]
(@file varchar(255))
as
begin
set nocount on
if exists(select * from sysobjects where id=object_id('temp')and xtype='u')
begin
print cast('dropping the table'as varchar(255))
drop table temp
end
declare @filename varchar(255)
declare @string nvarchar(2000)
declare @id int
declare @commandstring varchar(2000)
declare @vtargetfilename varchar(255)
declare @targetfilename varchar(255)
declare @targetpath varchar(255)
declare @position int
set @filename=@file
set @string='('+'''microsoft.jet.oledb.4.0'''+','+''+'''data
source= '+'"'+@filename+'"'+';'+'extended properties=excel 5.0'')'+'...sheet1
set @string='select * into temp from opendatasource '+ @string
execute sp_executesql @string
Print @string
declare c scroll cursor for
select columnname from temp
open c
fetch absolute 1 from c into @id
while @@fetch_status=0
begin
set @commandstring='select * from Adventureworks.Person.Address where AddressID ='+ cast(@id as varchar(20))
set @vtargetfilename=@filename
set @position=(datalength(@vtargetfilename)-charindex ('\',reverse(@vtargetfilename),0))
select @targetpath=left(@vtargetfilename,@position+1)
set @targetfilename=@targetpath +cast(@id as varchar(20))+'.xls'
set @commandstring = 'bcp.exe '+'"'+@commandstring+'"'+' queryout '+'"'+ @targetfilename +'"'+'-T -c'
print @commandstring
execute xp_cmdshell @commandstring
fetch next from c into @id
end
close c
deallocate c
end
I am using the dynamic SQL to create the Excel File for Each Row available in the Source Excel File.
Today I had the requirement to Read the Excel File and Extract the Data from SQL Server based on the Excel File and then Export the Data to the Different Excel Files.
So I break down the Task into Three Aspects:
Importing the Data From Excel File: OPENDATASOURCE:
Invoking the Command Prompt :XP_CMDSHELL
Using the Bcp.exe to Export the Data to the Excel File: BCP.EXE
Before running those commands,the SQL server should be enabled to run xp_cmdshell and OpenDataSource Commands.Those are server wide settings.
1.Reading the Data From Excel:
select * into temp from opendatasource
('microsoft.jet.oledb.4.0','data source= "G:Test.xls";extended properties=excel 5.0')...sheet1$
This reads the Data From the Excel File: G:\Test.xls
The sheetName :sheet1$
2.Using the XP_CMDSHELL:
I use xp_cmdshell with the BCP Option.
This invokes the command Prompt.
3.Using the BCP.EXE:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TestSp_ExcelFile]
(@file varchar(255))
as
begin
set nocount on
if exists(select * from sysobjects where id=object_id('temp')and xtype='u')
begin
print cast('dropping the table'as varchar(255))
drop table temp
end
declare @filename varchar(255)
declare @string nvarchar(2000)
declare @id int
declare @commandstring varchar(2000)
declare @vtargetfilename varchar(255)
declare @targetfilename varchar(255)
declare @targetpath varchar(255)
declare @position int
set @filename=@file
set @string='('+'''microsoft.jet.oledb.4.0'''+','+''+'''data
source= '+'"'+@filename+'"'+';'+'extended properties=excel 5.0'')'+'...sheet1
set @string='select * into temp from opendatasource '+ @string
execute sp_executesql @string
Print @string
declare c scroll cursor for
select columnname from temp
open c
fetch absolute 1 from c into @id
while @@fetch_status=0
begin
set @commandstring='select * from Adventureworks.Person.Address where AddressID ='+ cast(@id as varchar(20))
set @vtargetfilename=@filename
set @position=(datalength(@vtargetfilename)-charindex ('\',reverse(@vtargetfilename),0))
select @targetpath=left(@vtargetfilename,@position+1)
set @targetfilename=@targetpath +cast(@id as varchar(20))+'.xls'
set @commandstring = 'bcp.exe '+'"'+@commandstring+'"'+' queryout '+'"'+ @targetfilename +'"'+'-T -c'
print @commandstring
execute xp_cmdshell @commandstring
fetch next from c into @id
end
close c
deallocate c
end
I am using the dynamic SQL to create the Excel File for Each Row available in the Source Excel File.
Sunday, August 06, 2006
Friday I had some need for using the Format files to load the data into the sql server using the BCP Utility.I was getting some weired errors.(EOF File Error,Data Truncation Error,I/O Error,etc).I googled to find out the reason for those Errors and I found few reasons.
So I thoght of writing about using the format files in BCP Utility.
Creating the Format Files:
The easiest way I found is to Create the Format file using the Existing Table.Then it's easy to Edit the format file as the requirement changes.
a.Creating the format file with char Dataset:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -c -f G:\FormatFile.fmt -T'
Sample Format File:
9.0
13
1 SQLCHAR 0 16 "\t" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\t" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 35 "\t" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "\t" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 24 "\t" 5 EffectiveStartDate ""
6 SQLCHAR 0 50 "\t" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\t" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "\t" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 255 "\t" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "\t" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 255 "\t" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 30 "\t" 12 FeePerUnit ""
13 SQLCHAR 0 3 "\r\n" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
b.Creating the format file for Unicode DataSet:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 0 32 "\t\0" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 5 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 12 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
Here the data type SQLNCHAR is selected to support the UNICODE.This is done through the -w option while creating the format file.
c.Creating the Native Format Format Files:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -N -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 2 32 "" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 6 "" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 70 "" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 6 "" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 1 8 "" 5 EffectiveStartDate ""
6 SQLNCHAR 2 100 "" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 2 6 "" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 2 6 "" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 510 "" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 6 "" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "" 12 FeePerUnit ""
13 SQLNCHAR 2 6 "" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
In the native format,you can see the different data types from the char and widechar types.
Once done with creating the format files,the format files can be used to import the data into the existing tables.This provides the greater flexibility in importing the selective columns into the existing tables.
1.SameNumber of columns in the DataFile and the Table structure:
If the same number of columns exist between the data file and the table structure,its easy to create the one to one Relationship between the Data File Structure and the Table Structure.
The format file definies the relationship between the datafile and the table structure.
The first column in the above structure defines the Order of the columns in the DataFile.
We have to specify the all the Columns available in the DataFile.But we can skip specifying all the columns available in the Table.
I am using the Data File with 13 columns and the format file created using the -w option above.
Create the CharFormat Table with 13 Columns using the following script.
CREATE TABLE [dbo].[charFormat](
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
After creating the table,Insert the data into the table:
FormatFile:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
Loding Process:
xp_cmdshell 'bcp Test.dbo.CharFormat in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Options: in -- For the Input File Name
f -- FormatFile..It should be avialble at the time of loading process
T --Trusted Connection
2.Loading into the Table which has different Structure than the available DataFile:
DataFile: 13 Fields
Table : 12 Fields
In this case ,we have to change the Format File,so we can direct the data we want into the Required Columns into the Table.
create the table with the following scripts:
CREATE TABLE [dbo].lessfields
([ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format File:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 0 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 1 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 2 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 3 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 4 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 5 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 6 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 7 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 8 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 9 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 10 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
I have routed the first column avilable in the data file to 0 th column in the Table Structure.So we neglect this column in the datafile.
If you want to neglect the data in the FIle ,Put 0 in the sixth column in the format File.
First column corresponds to the Datafile Structure.Sixth Column corrosponds to the Table Column Order.So its easy to direct the data file to the required Column structure in the Table Structure.
Loading the Data:
xp_cmdshell 'bcp Test.dbo.lessfields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
3.Working with the Table,where Table is having more columns than the Data File:
Table: 15 Columns
DataFile: 13 Columns
Create the Table with the FollowingScripts:
CREATE TABLE [dbo].MoreFields(
Field1 int Identity(1,1),
Field2 nvarchar(255),
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format FIle.We only need all the fields avialable in the DataFile. It's not required to specify all the fields available in the Table.
Change the FormatFile:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 3 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 4 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 5 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 6 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 7 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 8 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 9 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 10 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 11 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 12 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 13 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
First Column in the Data File corresponds to the 3 rd column in the Table. when you specify 0 in the 6 th column,We are going to Neglect the Data available in the file for those columns.
Load the Data into the Table:
xp_cmdshell 'bcp Test.dbo.MoreFields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Format files provide greater flexibility when interacting with different systems.
So I thoght of writing about using the format files in BCP Utility.
Creating the Format Files:
The easiest way I found is to Create the Format file using the Existing Table.Then it's easy to Edit the format file as the requirement changes.
a.Creating the format file with char Dataset:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -c -f G:\FormatFile.fmt -T'
Sample Format File:
9.0
13
1 SQLCHAR 0 16 "\t" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\t" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 35 "\t" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "\t" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 24 "\t" 5 EffectiveStartDate ""
6 SQLCHAR 0 50 "\t" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\t" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "\t" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 255 "\t" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "\t" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 255 "\t" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 30 "\t" 12 FeePerUnit ""
13 SQLCHAR 0 3 "\r\n" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
b.Creating the format file for Unicode DataSet:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 0 32 "\t\0" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 5 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 12 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
Here the data type SQLNCHAR is selected to support the UNICODE.This is done through the -w option while creating the format file.
c.Creating the Native Format Format Files:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -N -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 2 32 "" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 6 "" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 70 "" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 6 "" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 1 8 "" 5 EffectiveStartDate ""
6 SQLNCHAR 2 100 "" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 2 6 "" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 2 6 "" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 510 "" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 6 "" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "" 12 FeePerUnit ""
13 SQLNCHAR 2 6 "" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
In the native format,you can see the different data types from the char and widechar types.
Once done with creating the format files,the format files can be used to import the data into the existing tables.This provides the greater flexibility in importing the selective columns into the existing tables.
1.SameNumber of columns in the DataFile and the Table structure:
If the same number of columns exist between the data file and the table structure,its easy to create the one to one Relationship between the Data File Structure and the Table Structure.
The format file definies the relationship between the datafile and the table structure.
The first column in the above structure defines the Order of the columns in the DataFile.
We have to specify the all the Columns available in the DataFile.But we can skip specifying all the columns available in the Table.
I am using the Data File with 13 columns and the format file created using the -w option above.
Create the CharFormat Table with 13 Columns using the following script.
CREATE TABLE [dbo].[charFormat](
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
After creating the table,Insert the data into the table:
FormatFile:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
Loding Process:
xp_cmdshell 'bcp Test.dbo.CharFormat in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Options: in -- For the Input File Name
f -- FormatFile..It should be avialble at the time of loading process
T --Trusted Connection
2.Loading into the Table which has different Structure than the available DataFile:
DataFile: 13 Fields
Table : 12 Fields
In this case ,we have to change the Format File,so we can direct the data we want into the Required Columns into the Table.
create the table with the following scripts:
CREATE TABLE [dbo].lessfields
([ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format File:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 0 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 1 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 2 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 3 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 4 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 5 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 6 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 7 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 8 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 9 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 10 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
I have routed the first column avilable in the data file to 0 th column in the Table Structure.So we neglect this column in the datafile.
If you want to neglect the data in the FIle ,Put 0 in the sixth column in the format File.
First column corresponds to the Datafile Structure.Sixth Column corrosponds to the Table Column Order.So its easy to direct the data file to the required Column structure in the Table Structure.
Loading the Data:
xp_cmdshell 'bcp Test.dbo.lessfields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
3.Working with the Table,where Table is having more columns than the Data File:
Table: 15 Columns
DataFile: 13 Columns
Create the Table with the FollowingScripts:
CREATE TABLE [dbo].MoreFields(
Field1 int Identity(1,1),
Field2 nvarchar(255),
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format FIle.We only need all the fields avialable in the DataFile. It's not required to specify all the fields available in the Table.
Change the FormatFile:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 3 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 4 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 5 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 6 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 7 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 8 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 9 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 10 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 11 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 12 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 13 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
First Column in the Data File corresponds to the 3 rd column in the Table. when you specify 0 in the 6 th column,We are going to Neglect the Data available in the file for those columns.
Load the Data into the Table:
xp_cmdshell 'bcp Test.dbo.MoreFields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Format files provide greater flexibility when interacting with different systems.