Re: SQL Server Components..Management Studio & Business Intelligence Developer Studio. - Database Discussion Boards

vishnukamath9-Aug-08 1:36

9-Aug-08 1:36

hi all

we are developing windows application ,for this we have to take database backup from our application using C#,we wrote code for this ,but it is not working ,it is showing below error message.

error Message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.

please help me.

Re: databasebackup & restore error

Wendelius9-Aug-08 2:03

Wendelius

9-Aug-08 2:03

Could you post the t-sql statement for backup. Most likely the directory does not exist or the backup device does not exist.

Mika

Concurrency problem............

samrat.net9-Aug-08 1:04

samrat.net

9-Aug-08 1:04

Hello Friend

I have faced a problem in SQL Server

I have a stored procedure which works as follows

Declare @Id int

Select @id=max(id) from emp
Set @id=@id+1
update emp Set id=@id
return @id

Now, we call this stored procedure from our ASP.net application. It is working perfecly in single user environment,
but the problem is that when we are working in multi-user environment some time the procedure returns same id instead of unique id.

How can I solve it, so that every time the procedure returns unique value for each user.
Is there any technique to execute this stored procedure in queue.

Thanks in advance

Re: Concurrency problem............

Wendelius9-Aug-08 2:02

Wendelius

9-Aug-08 2:02

Hi,

I would suggest that you reconsider the logic in your procedure from multi-user point of view.

However, if you want to prevent simultaneous operations with this structure, simply start with the update to get an exclusive lock. For example:

DECLARE @Id int

UPDATE emp SET id=(SELECT MAX(id) FROM emp);
SELECT @id=MAX(id) FROM emp;
RETURN @id;

Mika

Stored Procedure problem

kusum_mca068-Aug-08 20:50

kusum_mca06

8-Aug-08 20:50

I have written an SP which is inserting in all the dat in all the tables of SP in subsequent way and while designing I have taken care of all the foreign key constraints too.

following is the SP

CREATE PROCEDURE dbo.usp_create_new_sysid(
@p_client_prefix varchar(20),
@p_client_name varchar(256),
@p_branch_name varchar(256),
@p_location_name varchar(256))
AS
BEGIN

DECLARE @v_sys_id int,
@v_prefix_exists int,
@v_email_id_name varchar(250),
@v_count int

select @v_count=0

/* Check if prefix already exists */
select @v_prefix_exists = 1 from tblsystem where prefix = @p_client_prefix;
if (@v_prefix_exists = 1)
begin
select 0 SYSID
return
end

/* Get max sys_id + 1 to assign sys_id for the new company */
select @v_sys_id = max(id) + 1 from tblSystem

/* 12 NOV 2007 Ravi Sankar 1. From address changed from hardcoded 'resume@netedgecomputing.com' to 'noreply@companyname.com' */

/* create email name for the new company */
select @v_email_id_name = 'noreply@' + lower(replace(@p_client_name, ' ','')) + '.com'

BEGIN TRANSACTION
---1. Replace all ClientSysId by the new sysId that is to be created.
---2. In tblsystem please change the client name and client's prefix
---3. In tblprincipal please modify the principal that is being inserted.
--- Make sure that principal has the same PREFIX as that added to tblSystem
---4. In tblRoleMapping please modify the principal that is being inserted.
--- Make sure that principal has the same PREFIX as that added to tblSystem
---5. In tblCompany please modify the name and prefix of company.
--- Make sure that name and prefix are the same as that added to tblSystem
---6. In tblBranch please modify the name and description of the branch.
---7. In tblStaff please modify the username, it should be same as that added in Principal.

/*
01 JUN 2007 : Ravi Sankar: Modified to add more tables to tblNext_ID
05 JUN 2007 : Sahil Gupta: Modified tblNext_id entry for tblcandidateevaluationround
05 JUN 2007 : Sahil Gupta: Added script for creating a default workflow with a test and an interview round.
*/

insert into tblcustom (sys_id,context_path,is_limited_version) values (@v_sys_id,'D:/jboss-4.0.1/server/rdproductconfig/deploy/rd.ear/rd.war','y');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
insert into tblsystem (id,name,prefix,created_on) values (@v_sys_id, @p_client_name, @p_client_prefix,getdate());
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 03 Aug 2007 Ravi Sankar data in tblRoles changed
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Admin',@v_sys_id,'y','1','y','n','Admin')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Candidate',@v_sys_id,'n','0','n','n','Candidate')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Consultant',@v_sys_id,'y','1','y','n','Consultant')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('ContactPerson',@v_sys_id,'n','0','c','n','Contact Person Role')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('HrExecutive',@v_sys_id,'y','2','y','n','HR Executive Role')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('HrHead',@v_sys_id,'y','1','y','y','HR Head Role')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Interviewer',@v_sys_id,'n','5','y','n','Interviewer')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('JobPortal',@v_sys_id,'y','1','y','n','Job Portal')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Sales',@v_sys_id,'n','1','y','n','Sales')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Staff',@v_sys_id,'y','10','y','n','Staff')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('SuperUser',@v_sys_id,'y','1','y','n','Super User')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Netedge',@v_sys_id,'y','1','y','n','Netedge')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Testadmin',@v_sys_id,'y','20','y','n','Testadmin')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION)
VALUES('Careers',@v_sys_id,'y','20','y','n','Careers')
*/

/*
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Admin',@v_sys_id,'y','1','n','n','Admin','a')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Candidate',@v_sys_id,'n','0','n','n','Candidate','n')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Consultant',@v_sys_id,'y','1','n','n','Consultant','n')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('ContactPerson',@v_sys_id,'n','0','n','n','Contact Person Role','n')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('HrExecutive',@v_sys_id,'y','4','y','n','HR Executive Role','y')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('HrHead',@v_sys_id,'y','2','y','y','HR Head Role','y')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Interviewer',@v_sys_id,'n','5','n','n','Interviewer','c')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Sales',@v_sys_id,'n','1','n','n','Sales','y')
*/
-- 15 Jan 2008 Ravi Sankar 1. when new sys_id is being created staff role is_limited should go as 'n'
-- INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
-- VALUES('Staff',@v_sys_id,'y','10','n','n','Staff','y')
/*
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Staff',@v_sys_id,'n','10','n','n','Staff','y')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('SuperUser',@v_sys_id,'y','1','n','n','Super User','s')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Netedge',@v_sys_id,'y','1','n','n','Netedge','s')
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)

VALUES('Testadmin',@v_sys_id,'y','20','n','n','Testadmin','y')
*/
-- 30 NOV 2007 Ravi Sankar New role 'Accounts' added to tblRoles
/*
INSERT tblRoles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Accounts',@v_sys_id,'y','2','n','n','Handles Billing','y')
--26 feb 2008 Kusum Bhardwaj New role 'Accounts' added to tblRoles
INSERT tblroles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
VALUES('Coordinator',@v_sys_id,'y','1','y','n','Screening Coordinator','y')
*/

INSERT tblroles(ROLE,SYS_ID,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal)
select ROLE,@v_sys_id,is_limited,max_users,is_hr_role,is_approver_role,DESCRIPTION,is_internal from tblroles where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblprincipal (principal,sys_id,password,first_name) values (@p_client_prefix + '.admin', @v_sys_id, 'password', 'admin');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.admin', 'Admin', @v_sys_id);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblcompany (sys_id,id,name,prefix,is_internal) values (@v_sys_id, 1, @p_client_name, @p_client_prefix,'Y');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT into tblcurrency(sys_id,id,name,symbol,is_deleted) values(@v_sys_id,'1','Rupees','Rs.','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT into tblcurrency(sys_id,id,name,symbol,is_deleted) values(@v_sys_id,'2','Dollar','$','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT into tblcurrency(sys_id,id,name,symbol,is_deleted) values(@v_sys_id,'3','Pound','GBP','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblregion (sys_id,id,name,description) values (@v_sys_id,1,'NR','North Region')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tbllocation(sys_id,id,state_id,name) values(@v_sys_id,1,null, @p_location_name)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 24 JUL 2007 Ravi Sankar Location/Region 'Others' added */
insert into tblregion (sys_id,id,name,description) values (@v_sys_id,2,'Others','Some other Region')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

--insert into tblState(sys_id,id,region_id,name,description) values(@v_sys_id,2,2, 'Others', 'Some other State')
insert into tbllocation(sys_id,id,state_id,name,description) values(@v_sys_id,2,null, 'Others', 'Some other Location')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblbranch (sys_id,id,name,description,company_id,location_id,currency_id) values (@v_sys_id,1, @p_branch_name, @p_branch_name,1,1,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 16 Jan 2008 Ravi Sankar 'Admin' user in tblstaff to be allowed for all companies/functions (ieflag4 and flag5 to be made 'Y')
-- insert into tblstaff (sys_id,id,company_id,branch_id,first_name,principal_id,username) values (@v_sys_id,1,1,1,'Administrator',[dbo].fnGetMaxID(), @p_client_prefix + '.admin')
insert into tblstaff (sys_id,id,company_id,branch_id,first_name,principal_id,username,flag4, flag5) values (@v_sys_id,1,1,1,'Administrator',[dbo].fnGetMaxID(), @p_client_prefix + '.admin', 'Y', 'Y')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblprincipal (principal,sys_id,password,first_name) values (@p_client_prefix +'.SuperUser',@v_sys_id,'password','SuperUser')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix +'.SuperUser','SuperUser',@v_sys_id)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblstaff (sys_id,id,company_id,branch_id,first_name,principal_id,username,is_super_user) values (@v_sys_id,2,1,1,'SuperUser',[dbo].fnGetMaxID(),@p_client_prefix +'.SuperUser','Y')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

--insert corresponding to TblJobtype
--08-aug-2008 Kusum Bhardwaj 0 sys_id is maintained for trial version so instead of inserting row by row we are directly inserting from 0 sys_id
INSERT tbljobtype(sys_id,id,name,description,created_by,created_on,modified_by,modified_on,is_deleted)
select @v_sys_id,id,name,description,created_by,created_on,modified_by,modified_on,is_deleted from tbljobtype where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

---In tblcountry id column should be the incremented id
-- 09-may-2008 Kusum Bhardwaj the commented inserts of TblCountry are uncommented
--08-aug-2008 Kusum Bhardwaj 0 sys_id is maintained for trial version so instead of inserting row by row we are directly inserting from 0 sys_id
INSERT tblcountry(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on)
select @v_sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on from tblcountry where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/*
insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,1,'India','India')
insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,2,'USA','USA')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,3,'China','China')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblcountry(sys_id,id,name,description) values (@v_sys_id,4,'UK','UK')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
*/

-- 09-may-2008 Kusum Bhardwaj inserts for TblState are added
INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'1',null,'Andaman & Nicobar','Andaman & Nicobar','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'2',null,'Andhra Pradesh','Andhra Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'3',null,'Arunachal Pradesh','Arunachal Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'5',null,'Bihar','Bihar','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'6',null,'Chandigarh','Chandigarh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'7',null,'Chhattisgarh','Chhattisgarh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'8',null,'Dadra & Nagar Haveli','Dadra & Nagar Haveli','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'9',null,'Lakshadweep','Lakshadweep','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'10',null,'Madhya Pradesh','Madhya Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'11',null,'Maharashtra','Maharashtra','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'12',null,'Manipur','Manipur','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'13',null,'Meghalaya','Meghalaya','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'14',null,'Mizoram','Mizoram','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'15',null,'Nagaland','Nagaland','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'16',null,'Orissa','Orissa','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'17',null,'Daman & Diu','Daman & Diu','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'18',null,'Delhi','Delhi','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'19',null,'Goa','Goa','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'20',null,'Gujarat','Gujarat','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'21',null,'Haryana','Haryana','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'22',null,'Himachal Pradesh','Himachal Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'23',null,'Jammu & Kashmir','Jammu & Kashmir','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'24',null,'Jharkhand','Jharkhand','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'25',null,'Karnataka','Karnataka','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'26',null,'Kerala','Kerala','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'27',null,'Pondicherry','Pondicherry','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'28',null,'Punjab','Punjab','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'29',null,'Rajasthan','Rajasthan','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'30',null,'Sikkim','Sikkim','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'31',null,'Tamil Nadu','Tamil Nadu','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'32',null,'Tripura','Tripura','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'33',null,'Uttar Pradesh','Uttar Pradesh','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'34',null,'Uttarakhand','Previous name Uttaranchal','N','0',null,'1',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'35',null,'West Bengal','West Bengal','N','0',null,'0',null,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblstate(sys_id,id,region_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on,country_id) VALUES(@v_sys_id,'36',null,'Others','Some Other State','N','0',null,'0',null,null)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblLanguage(sys_id,id,name,description) values (@v_sys_id,1,'Hindi','Hindi')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblLanguage(sys_id,id,name,description) values (@v_sys_id,2,'English','English')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

--Generate script for various keywords

insert into tblKeyword(id,sys_id,name,description) values (1,@v_sys_id,'IIT','Indian Institute of Technology')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblKeyword(id,sys_id,name,description) values (2,@v_sys_id,'IIM','Indian Institute of Management')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.
-- insert into tblKeyword(id,sys_id,name,description) values (3,@v_sys_id,'AIIMS','All India Institute of Madical Sciences')

--Generate script for various Synonyms
insert into tblSynonyms(id,sys_id,keyword_id,name,description) values (1,@v_sys_id,1,'I.I.T.','Indian Institute of Technology')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblSynonyms(id,sys_id,keyword_id,name,description) values (2,@v_sys_id,2,'I.I.M.','Indian Institute of Management')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.
-- insert into tblSynonyms(id,sys_id,keyword_id,name,description) values (3,@v_sys_id,3,'A.I.I.M.S.','All India Institute of Madical Sciences')

set IDENTITY_INSERT tblEscalation ON
insert into tblEscalation(sys_id,id,SLA,alert_before_days) values (@v_sys_id,1,7,3)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

set IDENTITY_INSERT tblEscalation OFF

insert into tblFunction(sys_id,id,name) values (@v_sys_id,1,'Marketing')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblFunction(sys_id,id,name) values (@v_sys_id,2,'Technical Support')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblFunction(sys_id,id,name) values (@v_sys_id,3,'Accounts')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblFunction(sys_id,id,name) values (@v_sys_id,4,'Administration')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- insert into tblFunction(sys_id,id,name) values (@v_sys_id,5,'Marketing/Solutions')
-- 20 Aug 2007 Ravi sankar 1. Standard functions to be added to tblFunction
INSERT tblFunction(sys_id,id,name) VALUES(@v_sys_id,'5','Sales')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblFunction(sys_id,id,name) VALUES(@v_sys_id,'6','IT software')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblFunction(sys_id,id,name) VALUES(@v_sys_id,'7','Human Resources')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 06 Sep 2007 Ravi sankar 2. Standard functions are modified in tblFunction
-- Uptill seven functions only to be sent
/*
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'8','Networking','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'9','Web Designing','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'10','Customer Support','','N','0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'11','project mgt','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'12','architect','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'13','globus release','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'14','UNIX','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'15','BPO','','N', '0',convert(datetime,0),'0',convert(datetime,0))
INSERT tblFunction(sys_id,id,name,description,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'16','testing','','N', '0',convert(datetime,0),'0',convert(datetime,0))
*/

-- 06 Sep 2007 Ravi sankar 3. Standard positions are modified in tblPosition
-- Only Four positions
-- 20 Aug 2007 Ravi sankar 2. Standard positions to be added to tblPosition
/*
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'1','Consultant','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'2','Senior Consultant','1','6','','N','1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'3','Project Manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'4','Developer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'5','Tester','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'6','Tech Support and Maintenance','1','6','','N','1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'7','Search Engine optimization','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'8','Technical Writer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'9','sales manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'10','Web Designer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'11','Architect','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'12','Recruiter','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'13','Operations','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'14','Senior Tester','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'15','Test Lead','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'16','Trainer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'17','Sales Executive','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'18','Telecaller','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'19','Software Engineer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'20','Sr Sales Executive','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'21','Sr Developer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'22','OSS Enginer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'23','Tech project manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'24','Technical project manager','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'25','Sn Software Engineer','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'26','Project Lead','1','6','Project Lead','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'27','Globus Developer ','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted,created_by,created_on,modified_by,modified_on) VALUES(@v_sys_id,'28','Junior level','1','6','','N', '1',convert(datetime,0),'0',convert(datetime,0))
*/
INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'1','Junior Management','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'2','Middle Management','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'3','Senior management','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblPosition(sys_id,id,name,band,TAT_in_weeks,remarks,is_deleted) VALUES(@v_sys_id,'4','Executive','1','6','','N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

--Generate script for various statuses

INSERT tblstatus(sys_id,id,name,description,type,sequence,screen_acronym,created_by,created_on,modified_by,modified_on,display_name,is_deleted,is_mandatory,Process_manual,UserDefinedStage_displayYN,DisplayName_externalInterface)
select @v_sys_id,id,name,description,type,sequence,screen_acronym,created_by,created_on,modified_by,modified_on,display_name,is_deleted,is_mandatory,Process_manual,UserDefinedStage_displayYN,DisplayName_externalInterface from tblstatus where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 6-05-2008 Kusum Bhardwaj Channeltype 'News paper ADs' is changed to 'Career site' added
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,1,'Career Site');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,2,'Staff');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,3,'Placement Consultants');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,4,'Job Portal');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 24 Jan 2008 Ravi Sankar Channeltype 'candidate' added
insert into tblchanneltype (sys_id,id,name) values (@v_sys_id,5,'Candidate');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- Add test types
insert into tbltesttype (sys_id,id,name) values (@v_sys_id,1,'TEST');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tbltesttype (sys_id,id,name) values (@v_sys_id,2,'INTERVIEW');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- Add test types in tbletesttypeRd
insert into tbltesttyperd (sys_id,test_type_id,is_screening_candidate_assessment_YN,screening_type) values (@v_sys_id,1,'N','TEST');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tbltesttyperd (sys_id,test_type_id,is_screening_candidate_assessment_YN,screening_type) values (@v_sys_id,2,'N','INT');
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

--Add Email Templates

/* 03 Jan 2008 Ravi Sankar In tblEmailtemplate, Acknowledgement spelling corrected for ids 2,3,5 for shortname/subject */
/* 12 NOV 2007 Ravi Sankar 1. From address changed from hardcoded 'resume@netedgecomputing.com' to 'noreply@companyname.com'
2. New Email template added for CandidateRegistration mail. */

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted,created_by,created_on,modified_by,modified_on)
select @v_sys_id,id,ShortName,Description,FileName,@v_email_id_name ,subject,body,toId,cc,bcc,is_deleted,created_by,created_on,modified_by,modified_on from tblemailtemplate where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'1','Forgot Password','When the user forgets his password','forgotPasswordTemplate.html',@v_email_id_name,'Login details for RD','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'2','Resume Upload Acknowledgement','When the candidate uploads his resume','resumeUploadAck.html',@v_email_id_name,'Resume Uploaded Acknowledgement','','','','','Y')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'3','Resume Upload Staff Candidate','When a staff member uploads a friends resume,this mail goes to the candidate.','resumeUploadStaffCandidate.html',@v_email_id_name,'Resume Uploaded Acknowledgement','','','','','Y')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'4','Resume Upload Staff ','When a staff member uploads a friends resume,this mail goes to the staff member. ','resumeUploadStaff.html',@v_email_id_name,'Thank you for your reference','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'5','Resume Upload Placement','When a placement consultant uploads a resume,this mail goes to the consultant.','resumeUploadPlacement.html',@v_email_id_name,'Resume Uploaded Acknowledgement','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'6','Resume Upload Bulk Admin','When resumes are uploaded by bulk mail utility,this mail goes to admin on preconfigured email id.','resumeUploadBulkAdmin.html',@v_email_id_name,'Details of Resumes uploaded by Bulk Upload','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'7','Resume Upload Bulk','When resumes are uploaded by bulk mail utility,this mail goes to uploader with resumes detail.','resumeUploadBulk.html',@v_email_id_name,'Details of Resumes uploaded by Bulk Upload ','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'8','Invitation Mail','This mail is sent to selected candidates inviting them to upload their latest resume. The mail also has UserId and Password.','invitationMail.html',@v_email_id_name,'Invitation mail for your latest Resume','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'9','Invitation Mail Against Requisition','This mail is sent to selected candidates inviting them to upload their latest resumes against a specific PR.','invitationMailAgainstPR.html',@v_email_id_name,'Invitation mail for your latest Resume against a PR','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'10','Resume Submission Alert ','This mail goes to candidate, when his resume is sent to a client by recruiter.','resumeSubmissionAlert.html',@v_email_id_name,'Alert for your Resume Submission','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'11','Availability Check','This mail goes to candidate to check his availability for a PR.','availabilityCheck.html',@v_email_id_name,'Your availability for a PR','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'12','Test Schedule','This mail goes to candidate giving him his link for appearing in the test.','testSchedule.html',@v_email_id_name,'Test Schedule','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'13','Interview Schedule Candidate','This mail goes to candidate informing him about interview schedule.','interviewScheduleCandidate.html',@v_email_id_name,'Interview Schedule','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'14','Interview Schedule Interviewer','This mail goes to interviewer informing him about interview schedule.','interviewScheduleInterviewer.html',@v_email_id_name,'Interview Schedule','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'15','Selection Information Candidate','This mail is sent to candidate informing him about his selection.','selectionInfoCandidate.html',@v_email_id_name,'You have been selected','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'16','Rejection Information Candidate','This mail is sent to candidate informing him about his rejection.','rejectionInfoCandidate.html',@v_email_id_name,'You have been Rejected','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'17','Selection Rejection Channel','This mail is sent to channel(Staff/Placement Consultant) informing him about the candidate status.','selectionRejectionChannel.html',@v_email_id_name,'Status of Candidate','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'18','Position Requisition Alert','This mail is sent to subscribers informing that a new position has been posted.','positionRequisitionAlert.html',@v_email_id_name,'Alert for new Position Requisition','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'19','Position Requition Skill Alert','This mail is sent to subscribers informing that a new position on their skill has been posted.','PRSkillAlert.html',@v_email_id_name,'Alert for new Skill','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'20','Resume Submission Alert to Recruiter','This mail goes to recruiter working on a requirement when a resume is posted against that requisition.','resumeSubmissionAlertRecruiter.html',@v_email_id_name,'Alert for resume Submission','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'21','Bill Alert to Candidate','This mail is sent to candidate has joined a bill becomes due.','billingAlert.html',@v_email_id_name,'Billing Alert','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'22','SLA Alert ','This mail is sent to the resource executive that the position has not been closed and needs to be closed in next n hours/days configurable.','SLAAlert.html',@v_email_id_name,'SLA Alert','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'23','Alert For No. of resumes sent to client','Alert to preconfigured emails giving number of resumes sent to client against requisition.','noOfResumeSenttoClient.html',@v_email_id_name,'Alert for No. of resume sent to client','','','','','N')
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'24','Resumes Sent to Client','This mail is sent to client with candidates resume as attachments.','resumesSenttoClient.html',@v_email_id_name,'Candidate Resumes','','','','','N')
*/
/* 06 Sep 2007 Ravi sankar 1. New Email template added for offerletter */
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'25','OfferLetter','This mail is sent to candidate for whom offer letter is generated.', 'OfferLettertoCandidate.html',@v_email_id_name,'Offer letter','','','','','N')
*/
/* 23 OCT 2007 Ravi Sankar Delete invitation mail template tblMailtemplate (id 26). */
/* 01 Oct 2007 Ravi sankar 1. New Email template added for Invitation mail */
/* INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'26','Invitation Mail','This mail is sent to candidate for invitation.', 'invitationMail.html',@v_email_id_name,'Invitation Mail','','','','','N')
*/
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'26','CandidateRegistration','When Candidate self Registered.', 'CandidateRegistrationTemplate.html',@v_email_id_name,'Please Find Your Login Information','','','','','N')
*/
/* 30 NOV 2007 Ravi Sankar Email template for interview Schedule for Interviewer added. (id 27) */
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'27','Automated Interview Schedule Interviewer','This automated mail goes to interviewer informing him about interview schedule.', 'automatedInterviewScheduleInterviewer.html',@v_email_id_name,'Interview Schedule','','','','','N')
*/
/* 06 May 2008 Kusum Bhardwaj (id 28 to 35) */
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'28','Candidate Birthday Template','Everyday Greeting','birthdayGreetingMail.html','@v_email_id_name','Happy Birthday','','','','','N')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'29','Test ReSchedule','This mail goes to candidate informing him about the rescheduled test.','testReSchedule.html','@v_email_id_name','Test ReScheduled','','','','','n')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,body,toId,cc,bcc,is_deleted)
VALUES(@v_sys_id,'30','Interview ReSchedule Candidate','This mail goes to candidate informing him about the rescheduled interview.','interviewReScheduleCandidate.html','@v_email_id_name','Interview ReScheduled','','','','','n')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'31','ResumeUploadChannelAgainstPR','This mail goes to channel who has referred a candidate.','resumeUploadChannelAgainstPR.html','@v_email_id_name','Resume Referral Acknowledgement','N')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'32','ReferCandidateAcknowledgement','This mail goes to candidate who has been referred by a channel.','ReferCandidateAcknowledgement.html','@v_email_id_name','Resume Referral Acknowledgement','N')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'33','Scheduler Job Failed Mail','This mail goes to Admin when Scheduler Job fails.','schedulerJobFailedMailAdmin.html','@v_email_id_name','Scheduler Job Failed','N')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,is_deleted)
VALUES(@v_sys_id,'34','Job Alert','Send mail to subscriber about matched fresh jobs.','JobAlert.html','@v_email_id_name','Job Alert - Fresh Jobs','N')

INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,cc,bcc,is_deleted)
VALUES(@v_sys_id,'35','Registration Verification','When a Candidate self Registered.','CandidateVerificationTemplate.html','@v_email_id_name','Candidate Registration Verification','','','n')
*/

-- 05-jun-2005 Kusum Bhardwaj TblEmailtemplate new entry
/*
INSERT tblemailtemplate(sys_id,id,ShortName,Description,FileName,fromId,subject,cc,bcc,is_deleted)
VALUES(@v_sys_id,'36','Request to reschedule interview','This mail goes to Interviewer to reschedule Interview.','RequestToRescheduleInterview.html','@v_email_id_name','Request to reschedule Interview','','','n')
*/
/*
21 JUN 2007 Ravi Sankar tblroledashboard entries modified such that admin will have no dashboard and
Hrhead to have default dashboard
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','1','5')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','2','6')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','3','3')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','4','4')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','5','1')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'Admin','6','2')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','1','5')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','2','6')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','3','3')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','4','4')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','5','1')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrHead','6','2')

INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','1','1')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','2','6')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','3','4')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','4','3')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','5','5')
INSERT into tblroledashboard(sys_id,role,dashboard_id,sequence) values (@v_sys_id,'HrExecutive','6','2')
*/

/*
26 JUL 2007 Ravi Sankar data in tblRoleDashBoard changed
*/

INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo)
select @v_sys_id,role,dashboard_id,sequence,dateFrom,dateTo from tblroledashboard where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 05-May-2008 Kusum Bhardwaj New role added in Tblroledashboard for Superuser and HrHead
/*
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'ContactPerson','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'ContactPerson','6','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','1','6','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','2','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','3','5','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','4','4','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','6','3','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrExecutive','8','8','0','0')

INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','1','6','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','2','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','3','5','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','4','4','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','6','3','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','7','7','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'HrHead','8','8','0','0')

INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'Interviewer','5','1','0','0')
-- 17 Jan 2008 Ravi Sankar Pending feedback dashboard added to Interviewer
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'Interviewer','3','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','1','6','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','2','2','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','3','5','0','0')

INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','4','4','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','5','1','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','6','3','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','7','7','0','0')
INSERT tblroledashboard(sys_id,role,dashboard_id,sequence,dateFrom,dateTo) VALUES(@v_sys_id,'SuperUser','8','8','0','0')
*/

SELECT @v_count=COUNT(*) FROM tblbill WHERE sys_id=@v_sys_id

INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblBill',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblbillreceipt WHERE sys_id=@v_sys_id

INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblbillreceipt',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblbranch WHERE sys_id=@v_sys_id

INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblBranch',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCandidate WHERE sys_id=@v_sys_id

INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidate',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblcandidateEvaluationRound WHERE sys_id=@v_sys_id

INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateEvaluationRound',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCandidateRequisition WHERE sys_id=@v_sys_id

INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateRequisition',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCandidateRequisitionHistory WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateRequisitionHistory',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCandidateRequisitionNotes WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCandidateRequisitionNotes',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/*
SELECT @v_count=COUNT(*) FROM tblChannel WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblChannel',@v_sys_id,@v_count)
*/
-- 24 Jan 2008 Ravi Sankar Channeltype 'candidate' added

SELECT @v_count=COUNT(*) FROM tblChannelType WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblChannelType',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCompany WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCompany',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCompetency WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCompetency',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblContactPerson WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblContactPerson',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCostHead WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCostHead',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCurrency WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCurrency',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblDesignation WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblDesignation',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblEvaluationWorkflow WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblEvaluationWorkflow',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 06 Sep 2007 Ravi sankar 2. Standard functions are modified in tblFunction
-- Only seven Functions

SELECT @v_count=COUNT(*) FROM tblFunction WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblFunction',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.

SELECT @v_count=COUNT(*) FROM tblKeyword WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblKeyword',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblLocation WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblLocation',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblOfferLetterDetails WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblOfferLetterDetails',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 06 Sep 2007 Ravi sankar 3. Standard positions are modified in tblPosition
-- Only Four positions

SELECT @v_count=COUNT(*) FROM tblPosition WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPosition',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblPositionRequisition WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPositionRequisition',@v_sys_id,isnull(@v_count,@v_count))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblPositionRequisitionExpenses WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPositionRequisitionExpenses',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblQualification WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblQualification',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblRegion WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblRegion',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblSearchAgent WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSearchAgent',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblSkill WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSkill',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblStaff WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblStaff',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 25 OCT 2007 Ravi Sankar 'tblState' ebtry in tblNext_id.

SELECT @v_count=COUNT(*) FROM tblState WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblState',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblStatus WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblStatus',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblSubCategoryExp WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSubCategoryExp',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 07 Sep 2007 Ravi sankar 1. AIIMS keyword and synonym related records to be deleted.

SELECT @v_count=COUNT(*) FROM tblSynonyms WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblSynonyms',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblTest WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblTest',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblTesttype WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblTestType',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 30 NOV 2007 Ravi Sankar Email template for interview Schedule for Interviewer added. (id 27) */
/* 12 NOV 2007 Ravi Sankar 1. From address changed from hardcoded 'resume@netedgecomputing.com' to 'noreply@companyname.com'
2. New Email template added for CandidateRegistration mail. */
/* 23 OCT 2007 Ravi Sankar Delete invitation mail template tblMailtemplate (id 26). */
/* 01 Oct 2007 Ravi sankar 1. New Email template added for Invitation mail */
/* 06 Sep 2007 Ravi sankar 1. New Email template added for offerletter */

SELECT @v_count=COUNT(*) FROM tblEmailtemplate WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblemailtemplate',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
/* 01 JUN 2007 : Ravi Sankar: Modified to add more tables to tblNext_ID */
SELECT @v_count=COUNT(*) FROM tblPotentialRecruit WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPotentialRecruit',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblPotentialClients WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblPotentialClients',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblDocument WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblDocument',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblDocumentHistory WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblDocumentHistory',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblCategory WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCategory',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 13 NOV 2007 Ravi Sankar 1. 'tblUnit' row is added to tblNext_id */

SELECT @v_count=COUNT(*) FROM tblUnit WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblUnit',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 15 NOV 2007 Ravi Sankar 1. 'tblCountry' row is added to tblNext_id */

SELECT @v_count=COUNT(*) FROM tblCountry WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblCountry',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 10 Jan 2008 Ravi Sankar In Next_id, rows added for 'tblIndustry', 'tblJobType', 'tblVisaStatus' */

SELECT @v_count=COUNT(*) FROM tblIndustry WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblIndustry',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tbljobtype WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblJobType',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

SELECT @v_count=COUNT(*) FROM tblVisaStatus WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblVisaStatus',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 09-may-2008 Kusum Bhardwaj Insert for TblVerifyRegistration

SELECT @v_count=COUNT(*) FROM tblVerifyRegistration WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblVerifyRegistration',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
-- 09-may-2008 Kusum Bhardwaj Insert for TblJobAlert

SELECT @v_count=COUNT(*) FROM tblJobAlert WHERE sys_id=@v_sys_id
INSERT tblnext_ID(TABLE_NAME,sys_id,id)
VALUES('tblJobAlert',@v_sys_id,@v_count)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 10 DEC 2007 Ravi sankar Added paymentmode entries and 'tblreceipt','tblpaymentmode', in tblnext_id */
Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 1, 'Cash', 'Cash')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 2, 'Credit Card', 'Credit Card')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 3, 'Cheque', 'Cheque')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

Insert into tblPaymentMode(sys_id, id, name, description) values (@v_sys_id, 4, 'Draft', 'Draft')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

Insert into tblNext_id(table_name, sys_id, id) values ('tblPaymentMode', @v_sys_id,5)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

Insert into tblNext_id(table_name, sys_id, id) values ('tblReceipt', @v_sys_id,0)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 24 DEC 2007 Ravi sankar Added 'tblCandidateDocument' in tblnext_id */
Insert into tblNext_id(table_name, sys_id, id) values ('tblCandidateDocument', @v_sys_id,0)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblEvaluationWorkflow (sys_id, id, name, description, is_deleted)
values (@v_sys_id,1, 'Default', 'Default', 'N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tblCompanyFunctionPositionEvaluation(sys_id, evaluation_workflow_id, is_deleted)
values(@v_sys_id,1,'N')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 20 Aug 2007 Ravi sankar 3. Default test table to contain only one test 'Face to face Interview round'
-- 20 Aug 2007 Ravi sankar 4. Default evaluation work flow to contain only one test 'Face to face Interview round'
-- insert into tbltest(sys_id, id, name, description, lockedYN, password, duration_minutes, is_deleted)
-- values (@v_sys_id, 1, 'Test' , 'Test', 'N', '', 30.0, 'N')
-- insert into tbltest(sys_id, id, name, description, lockedYN, password, duration_minutes, is_deleted)
-- values (@v_sys_id, 2, 'Interview' , 'Interview', 'N', '', 30.0, 'N')
INSERT tblTest(sys_id,id,Name,Description,LockedYN,password,duration_minutes,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'1','Face_to_face Interview round','',' ','','30','N','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

insert into tbltesttypemapping (sys_id, test_type_id, test_id)
values(@v_sys_id, 2,1)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- insert into tbltesttypemapping (sys_id, test_type_id, test_id)
-- values(@v_sys_id, 2,2)
insert into tblCFPEvaluationRound (sys_id, cfpe_id, screening_round_order, test_type_id, test_id, before_sending_resumes_to_client_YN, is_mandatory_to_clear_YN, pass_percentage, duration_minutes)
values (@v_sys_id, [dbo].fnGetMaxIDEvaluation(), 1, 2,1,'N', 'N',30.0, 30.0)
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- insert into tblCFPEvaluationRound (sys_id, cfpe_id, screening_round_order, test_type_id, test_id, before_sending_resumes_to_client_YN, is_mandatory_to_clear_YN, pass_percentage, duration_minutes)
-- values (@v_sys_id, [dbo].fnGetMaxIDEvaluation(), 2, 2,2,'N', 'N',30.0, 30.0)

insert into tblprincipal (principal,sys_id,password,first_name,channeltype_id) values (@p_client_prefix + '.Naukri',@v_sys_id,'password','Naukri',4);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.Naukri','JobPortal',@v_sys_id); */
insert into tblprincipal (principal,sys_id,password,first_name,channeltype_id) values (@p_client_prefix + '.TimesJob',@v_sys_id,'password','Timesjob',4);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.TimesJob','JobPortal',@v_sys_id); */
insert into tblprincipal (principal,sys_id,password,first_name,channeltype_id) values (@p_client_prefix + '.Monster',@v_sys_id,'password','Monster',4);
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* insert into tblrole_mapping (principal,role,sys_id) values (@p_client_prefix + '.Monster','JobPortal',@v_sys_id); */
/*
15 JUN 2007 Ravi Sankar tblStatus record where name is 'offerletter' Display name changed
from 'Offer Letter Generated' to 'Offer Letter'
*/

update tblStatus set display_name = 'Offer Letter' where sys_id = @v_sys_id and name = 'offerletter'
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/*
11 JUL 2007 Ravi Sankar tblWorkflow row in tblNext_Id changed to 1 from 0 since default is already added
*/
UPDATE tblnext_ID set id = 1 where sys_id = @v_sys_id and table_name = 'tblEvaluationWorkflow' and id = 0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

-- 20 Aug 2007 Ravi sankar 5. Default tblQuestionGroup to contain one question group
-- 20 Aug 2007 Ravi sankar 6. Default Question bank to contain default questions
-- 20 Aug 2007 Ravi sankar 7. For each question populate tables tblGroupQuestionmap, tblQuestionCorrectAnswer,
-- 20 Aug 2007 Ravi sankar tblAnswerType, tbltestQuestion

declare @v_question_group_id int,
@v_question_id int

INSERT tblQuestionGroup(sys_id,name,description,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Face_to_face','','n','1',convert(datetime,0),'0',convert(datetime,0))
select @v_question_group_id = @@identity
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Personality','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2','Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3','Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4','Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Confidence','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity
INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2',' Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3',' Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4',' Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Communication Skills','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity

INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2',' Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3',' Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4',' Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Job Knowledge','radiobutton','0','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity

INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'1','Very Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'2',' Good','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'3',' Satisfactory','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblAnswerType(questionbank_id,id,AnswerValue,RefField_Id,RefFieldValue_id)
VALUES(@v_question_id,'4',' Poor','0','0')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Recommendations','textbox','250','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity

INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblQuestionBank(sys_id,QuestionText,AnswerType,AnswerLength,Rangemin,Rangemax,is_deleted,created_by,created_on,modified_by,modified_on)
VALUES(@v_sys_id,'Technical Knowledge Skills','textbox','250','0','0','n','1',convert(datetime,0),'0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END
select @v_question_id = @@identity

INSERT tblQuestionCorrectAnswer(sys_id,question_id,correctAnswer)
VALUES(@v_sys_id, @v_question_id, '')
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblGroupQuestionMap(sys_id,group_id,question_id,expertiseLevel,created_by,created_on)
VALUES(@v_sys_id, @v_question_group_id, @v_question_id,' ','1',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

INSERT tblTestQuestion(sys_id,test_id,group_id,question_id,max_marks,display_seq,created_by,created_on)
VALUES(@v_sys_id, '1', @v_question_group_id, @v_question_id,'0','1','0',convert(datetime,0))
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

/* 04-aug-2008 Kusum Bhardwaj 0 sysid is maintained in all the tables to maintain data for trail version
Now the data would be directly uploaded from the tables for the new trail version*/

INSERT tblswitch(sys_id,id,switch_key,attribute)
select @v_sys_id,id,switch_key,attribute from tblswitch where sys_id=0
IF @@ERROR <> 0
BEGIN
ROLLBACK
SELECT 0 SYSID
RETURN
END

COMMIT TRANSACTION

SELECT @v_sys_id SYSID

END

GO

The problem is while inserting when I am making anonymous block of it then it is inserting in all the tables but when I am calling the block as an SP. then it is skipping one table.

I am not getting what exactly is teh problem.

Please help if anybody knows

Thanks
Kusum Bhardwaj

Re: Stored Procedure problem

Vimalsoft(Pty) Ltd8-Aug-08 22:53

Vimalsoft(Pty) Ltd

8-Aug-08 22:53

seriously no one is going to help you. you cannot post such a big statement

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

SQL Server Components..Management Studio & Business Intelligence Developer Studio.

JAnthonyRaj8-Aug-08 18:41

JAnthonyRaj

8-Aug-08 18:41

Can you please tell me how long SSAS, SSRS, SSIS will take us to get mastered?

Re: SQL Server Components..Management Studio & Business Intelligence Developer Studio.

Paul Conrad8-Aug-08 19:39

Paul Conrad

8-Aug-08 19:39

JAnthonyRaj wrote:
how long SSAS, SSRS, SSIS will take us to get mastered?

Depends on how fast of a learner you are.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: SQL Server Components..Management Studio & Business Intelligence Developer Studio.

Mycroft Holmes9-Aug-08 14:20

Mycroft Holmes

9-Aug-08 14:20

Thank you J Anthony Raj, I needed a good chuckle this morning. Yuo really do take the cake for dumb questions. Based on this I would say for you oh, about 14 years.

Please note there is no joke icon on this post!

Never underestimate the power of human stupidity
RAH

Re: SQL Server Components..Management Studio & Business Intelligence Developer Studio.

Paul Conrad9-Aug-08 14:25

Paul Conrad

9-Aug-08 14:25

That's real gem isn't it Laugh | :laugh:

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: SQL Server Components..Management Studio & Business Intelligence Developer Studio.

Mycroft Holmes9-Aug-08 14:33

Mycroft Holmes

9-Aug-08 14:33

I was tempted to reply with the question "how long is a piece of string" but I'm absolutely sure he would understand the reference.

Never underestimate the power of human stupidity
RAH

Re: SQL Server Components..Management Studio & Business Intelligence Developer Studio.

Paul Conrad9-Aug-08 14:35

Paul Conrad

9-Aug-08 14:35

Mycroft Holmes wrote:
"how long is a piece of string"

[Obi-wan Kenobi voice over] Depends on the point of view Roll eyes | :rolleyes:

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

about SQL query analyzer

alexyxj8-Aug-08 16:06

alexyxj

8-Aug-08 16:06

does SQL server 2005 include query analyzer? I installed it and couldn't find the query analyzer. anyone knows where I can download SQL query analyzer? many thanks..

Re: about SQL query analyzer

Paul Conrad8-Aug-08 19:41

Paul Conrad

8-Aug-08 19:41

It's still there. Just not called Query Analyzer anymore. Are you using SQL Management Studio?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: about SQL query analyzer

alexyxj9-Aug-08 9:36

alexyxj

9-Aug-08 9:36

yes, i am using that, so as you said, what is the query analyzer called in SQL Server management studio? thanks....

Re: about SQL query analyzer

Paul Conrad9-Aug-08 9:43

Paul Conrad

9-Aug-08 9:43

It should be in the query editor toolbar under Display Estimated Execution Plan, IIRC...

You might want to click on the button that says Include In Execution Plan, as well. This will give you query stats along side with any query results.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: about SQL query analyzer

alexyxj9-Aug-08 9:53

alexyxj

9-Aug-08 9:53

thanks Paul

Re: about SQL query analyzer

Paul Conrad9-Aug-08 9:54

Paul Conrad

9-Aug-08 9:54

You're welcome.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Assign Value to Parameter

JrunkDunc8-Aug-08 12:15

JrunkDunc

8-Aug-08 12:15

Hey,

So I am trying to get the Highest MenuOrder Value there is increment that by one and then use that value to insert a new object in with the next highest menu order

My attempt so far is as so:

Declare @MenuSeed as int

Select @MenuSeed = Top(1) MenuOrder
From Pages
Order By MenuOrder DESC

And of course that doesn't work, but I am kinda lost on what to do here, can anyone shed some light?

Thanks Alot

-Seth

Re: Assign Value to Parameter

Blue_Boy8-Aug-08 12:41

Blue_Boy

8-Aug-08 12:41

Declare @MenuSeed as int<br />
<br />
Set @MenuSeed = (select max(MenuOrder) From PagesOrder)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: Assign Value to Parameter

leoinfo8-Aug-08 14:25

leoinfo

8-Aug-08 14:25

SET @MenuSeed = (Select Top 1 MenuOrder From Pages Order By MenuOrder DESC )

Select Top 1 @MenuSeed = MenuOrder From Pages Order By MenuOrder DESC

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

SQL Import Wizard data types dont match

Hunter Barrington8-Aug-08 4:10

Hunter Barrington

8-Aug-08 4:10

While importing data from a text file using the wizard i get the error

Invalid character value for cast specification

There's a bad record in the file... but the file is huge so it's nearly impossible to find it by hand. Is there a way to import the data and ignore records that are malformed or the data isn't in the data type it should be?

TIA

Re: SQL Import Wizard data types dont match

DerekFL8-Aug-08 4:44

DerekFL

8-Aug-08 4:44

With SQL Server SSIS add a data transformation task and add a datasource (where the bad data is) and a destination (scrubbed data). drag green arrow from source to destination.
Add a second destination and set on failure of first destination to redirect to second (red arrow). You can use a flat file if you want.

Re: SQL Import Wizard data types dont match

Mycroft Holmes9-Aug-08 14:29

Mycroft Holmes

9-Aug-08 14:29

I NEVER use transforms in the ETL layer. I load the data in a table using large varchar fields and use a procedure to do the transforms. This maybe (is) old fashioned but I find I have much better control over the data using a procedure. I can manage the exceptions more to my liking and the bloody thing doesn't break in the middle of the load.

One reason I dislike SSIS and hate Biztalk is the complexity they instill into a basically simple operation.

Never underestimate the power of human stupidity
RAH

Authentication problems since server change

Jay Royall7-Aug-08 23:31

Jay Royall

7-Aug-08 23:31

Hi,

One of our clients have recently had one of their two servers changed. The server that got changed contained Active Directory with all the permissions for all users. The other server has MS SQL Server 2005 installed.

Since the server change, users cannot use the Management Studio from their client machines to log on to SQL Server using Windows Authentication. I'm presuming that this is because since the server change, there is some sort of security ID which has changed meaning that the SQL Server doesn't recognise it even though ther name of the new server is the same and the name of the domain is still the same.

My question is this, what can I do about this??

Thanks in advance for any help.

Last Visit: 31-Dec-99 18:00 Last Update: 5-May-24 13:58

Refresh

ᐊ Prev 1...1091 1092 1093 109410951096 1097 1098 1099 1100 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.