Create Login Script (using email address)

I have some code that was given to me to create user logins based on email addresses.  I'm having issues getting it to run more than once and create a multiple logins (using different login types).  I'm thinking this code just isn't going to work for me, especially as I will need to run it as a nightly job to create logins automatically for new accounts that are created.  Does anyone have any code or a report/utility they are willing to share that will create web logins from using email addresses or can anyone take a look at the code I have and give me some advice on how to edit to make it work?

Thanks everyone!!!!

Teresa

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_CREATE_LOGINS_FROM_EADDRESS]    Script Date: 02/22/2011 13:52:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    Stored procedure to search for primary email addresses that do
-- not have a corresponding entry in T_CUST_LOGIN and create one.
-- Optional parameter of start_dt (being the create_dt to look for on existing
-- primary records in t_eaddress which don't have a corresponding record in t_cust_login,
-- then creates a login for them).
-- =============================================
ALTER PROCEDURE [dbo].[LP_CREATE_LOGINS_FROM_EADDRESS] @start_dt datetime = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

if @start_dt is null
 begin 
 truncate table lt_login_info
   insert lt_login_info (customer_no, n1n2_ind, login_type, login, password, eaddress_no,
  primary_ind, inactive)
 select customer_no,'3','2',address,customer_no,eaddress_no,'N','N'
  from t_eaddress 
  where primary_ind = 'Y'
  and create_dt between getdate()-1 and getdate()
    and eaddress_no not in (select eaddress_no from t_cust_login)
    and customer_no > 0
      and address like '%@%'
end
 else 
 truncate table lt_login_info
   insert lt_login_info (customer_no, n1n2_ind, login_type, login, password, eaddress_no,
  primary_ind, inactive)
 select customer_no,'3','2',address,customer_no,eaddress_no,'N','N'
  from t_eaddress 
  where primary_ind = 'Y'
   and create_dt between @start_dt and getdate()
    and eaddress_no not in (select eaddress_no from t_cust_login)
    and customer_no > 0
      and address like '%@%' 

select * from lt_login_info

insert t_cust_login
 (login_no, customer_no, n1n2_ind, login_type, login, password, eaddress_no,
  primary_ind, inactive)
select a.customer_no + (select max(login_no) from t_cust_login), a.customer_no,
  a.n1n2_ind,a.login_type,a.login,a.password,a.eaddress_no,a.primary_ind,a.inactive
  from lt_login_info a
   
update t_next_id
set next_id = (select max(login_no) from t_cust_login) + 10
 where type = 'LO'

END

/*
create table lt_login_info
    (customer_no int,
    n1n2_ind int,
    login_type int,
    login varchar(80),
    password varchar(32),
    eaddress_no int,
    primary_ind char(1),
    inactive char(1))
*/

/*
EXEC LP_CREATE_LOGINS_FROM_EADDRESS '2000-01-01'

select * from t_cust_login
select * from tr_login_type
select * from t_next_id where type = 'LO'

select login, count(login) from t_cust_login
group by login
having count(login) >1

update t_cust_login
set temporary_ind = 'Y'
where create_dt > '2011-01-03'

delete from t_cust_login
where customer_no in
    (select customer_no
    from t_customer
    where inactive in (2,5))
*/

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

I have some code that was given to me to create user logins based on email addresses.  I'm having issues getting it to run more than once and create a multiple logins (using different login types).  I'm thinking this code just isn't going to work for me, especially as I will need to run it as a nightly job to create logins automatically for new accounts that are created.  Does anyone have any code or a report/utility they are willing to share that will create web logins from using email addresses or can anyone take a look at the code I have and give me some advice on how to edit to make it work?

Thanks everyone!!!!

Teresa

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LP_CREATE_LOGINS_FROM_EADDRESS]    Script Date: 02/22/2011 13:52:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    Stored procedure to search for primary email addresses that do
-- not have a corresponding entry in T_CUST_LOGIN and create one.
-- Optional parameter of start_dt (being the create_dt to look for on existing
-- primary records in t_eaddress which don't have a corresponding record in t_cust_login,
-- then creates a login for them).
-- =============================================
ALTER PROCEDURE [dbo].[LP_CREATE_LOGINS_FROM_EADDRESS] @start_dt datetime = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

if @start_dt is null
 begin 
 truncate table lt_login_info
   insert lt_login_info (customer_no, n1n2_ind, login_type, login, password, eaddress_no,
  primary_ind, inactive)
 select customer_no,'3','2',address,customer_no,eaddress_no,'N','N'
  from t_eaddress 
  where primary_ind = 'Y'
  and create_dt between getdate()-1 and getdate()
    and eaddress_no not in (select eaddress_no from t_cust_login)
    and customer_no > 0
      and address like '%@%'
end
 else 
 truncate table lt_login_info
   insert lt_login_info (customer_no, n1n2_ind, login_type, login, password, eaddress_no,
  primary_ind, inactive)
 select customer_no,'3','2',address,customer_no,eaddress_no,'N','N'
  from t_eaddress 
  where primary_ind = 'Y'
   and create_dt between @start_dt and getdate()
    and eaddress_no not in (select eaddress_no from t_cust_login)
    and customer_no > 0
      and address like '%@%' 

select * from lt_login_info

insert t_cust_login
 (login_no, customer_no, n1n2_ind, login_type, login, password, eaddress_no,
  primary_ind, inactive)
select a.customer_no + (select max(login_no) from t_cust_login), a.customer_no,
  a.n1n2_ind,a.login_type,a.login,a.password,a.eaddress_no,a.primary_ind,a.inactive
  from lt_login_info a
   
update t_next_id
set next_id = (select max(login_no) from t_cust_login) + 10
 where type = 'LO'

END

/*
create table lt_login_info
    (customer_no int,
    n1n2_ind int,
    login_type int,
    login varchar(80),
    password varchar(32),
    eaddress_no int,
    primary_ind char(1),
    inactive char(1))
*/

/*
EXEC LP_CREATE_LOGINS_FROM_EADDRESS '2000-01-01'

select * from t_cust_login
select * from tr_login_type
select * from t_next_id where type = 'LO'

select login, count(login) from t_cust_login
group by login
having count(login) >1

update t_cust_login
set temporary_ind = 'Y'
where create_dt > '2011-01-03'

delete from t_cust_login
where customer_no in
    (select customer_no
    from t_customer
    where inactive in (2,5))
*/

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!