Create a login table question

Hey all- I have been reading this forum for quite some time now but I have never posted- this place is a great tool for young developers.

Anyway, I have a specific question.

I have a table (members)- with PK member_id- that holds member names and personal info etc.

My goal is for each member_id in the members table, insert that member_id into my fk column in a logins table. then, when each member_id is inserted, i want to insert the first initial of the member_name and first 4 characters of the last_name, along with the member_id into the logins table in the login_name column.

i also want to insert a unique 6 digit string into the logins table (login_password). I'm not great with script, so any help would be appreciated.

Thanks,

sudeep.Suppose you have this situation:

CREATE TABLE members
(member_id NUMBER(2) CONSTRAINT pk_mem PRIMARY KEY,
member_name VARCHAR2(10),
last_name VARCHAR2(10)
);

CREATE TABLE logins
(member_id NUMBER(2) CONSTRAINT fk_log_mem
REFERENCES members (member_id)
INITIALLY DEFERRED DEFERRABLE,
login_name VARCHAR2(20),
login_password VARCHAR2(6)
);

CREATE SEQUENCE seq_mem_login START WITH 100000;
/* The sequence is used to generate unique login passwords in this example */

CREATE OR REPLACE TRIGGER trg_member
AFTER INSERT
ON members
FOR EACH ROW
BEGIN
INSERT INTO logins
(member_id,
login_name,
login_password
)
VALUES (:NEW.member_id,
SUBSTR (:NEW.member_name, 1, 1)
|| SUBSTR (:NEW.last_name, 1, 4)
|| TO_CHAR (:NEW.member_id),
seq_mem_login.NEXTVAL
);
END;
/

insert into members values (1, 'john', 'smith');

select * from members;
MEMBER_ID MEMBER_NAM LAST_NAME
---- ---- ----
5 john smith

select * from logins;
MEMBER_ID LOGIN_NAME LOGIN_
---- ------- --
5 jsmit5 100000|||thanks man- i'll let you know how it goes in a few.|||whats wrong with this code? anyone?

it gives me the following error:

Server: Msg 128, Level 15, State 1, Procedure trigPassword_CreateMember, Line 9
The name 'member_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

--Create the trigger
CREATE TRIGGER trigPassword_CreateMember ON Accounts
AFTER Insert
AS
BEGIN
BEGIN TRAN
INSERT INTO Logins(member_id, login_name, login_password)
VALUES (inserted.member_id, SUBSTRING(inserted.first_name,1,1) + SUBSTRING(last_name,1,1) + CONVERT(char(5),inserted.member_id),
inserted.account_id)
COMMIT TRAN
END
GO

Create a flat file for each record in a table

I'm rather new to ssis and I've been reading and testing but didn't find a solution for this problem.Supose I've got a table Customer with some fields. One of the fields is CustID.I want to create as many flat files as there are Customers in the table with flat file name set to the CustID.If you could point me in a good direction, It would be nice.Greetings from Belgium

Do you want any data in these files or just the file to be created? If you just want the file to be created you could aggregate the data and then use the FileExtractor to create the file (You would need to add 2 columns one for the file name and one for the file data (empty)). If you actually want the data to flow into these files then you would most likely want to use a script component.

HTH,

Matt

|||

It sounds like you want to run a exectue SQL task to get an ADO object which holds the records you want. Then for each of these you what to push thenm to a for loop and have a dataflow inside the for loop. In the Dataflow the source would be the ADO object and you can push it to the text file.

These pages should help a bit

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/2458.aspx

http://sqljunkies.com/WebLog/knight_reign/archive/category/458.aspx

|||Thanks!

Create a column in RecordSet with the Record Count...

Hello

I'm reading a XML file and the next operation need a column with the row count.

I don't want to know how many rows there is in the recordset, but, the row count of each record.

How can I implement this?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

That issue was discussed here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074253&SiteID=1

CPU maximums & multicore CPUs

I have been reading the licensing information for SQL 2005 as it relates to
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?
The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>
|||Thanks for the response. Just one thing. Do you know if this is spelled out
anywhere on Microsoft's site. I realize it can be inferred by what is stated
in most of the papers they have on licensing models but I have management who
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:

> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL will
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>
>
|||Try this:
http://download.microsoft.com/downlo...5Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
|||http://www.microsoft.com/sql/prodinf...-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
|||You win. Your link is better than my link.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/downlo...5Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>

CPU maximums & multicore CPUs

I have been reading the licensing information for SQL 2005 as it relates to
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>|||Thanks for the response. Just one thing. Do you know if this is spelled ou
t
anywhere on Microsoft's site. I realize it can be inferred by what is state
d
in most of the papers they have on licensing models but I have management wh
o
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:

> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL wi
ll
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>
>|||Try this:
http://download.microsoft.com/downl...05Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>|||http://www.microsoft.com/sql/prodin...e-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>|||You win. Your link is better than my link.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/downl...05Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>

CPU maximums & multicore CPUs

I have been reading the licensing information for SQL 2005 as it relates to
logical CPUs (hyperthreading or multicores) but all of the white papers I am
reading cover the Per Processor model when addressing this.
Does anyone know, when not using the Per Processor model (i.e. Per Server
with either Device or User CALs) does the same logic apply? In other words,
if I have a server with 4 physical CPUs that do hyperthreading, can I run
Standard Edition and use all eight or do I need to go to Enterprise Edition
to use more than 4 CPUs?The processor limits apply regardless of the licensing model. Standard
Edition SQL 2005 supports up to four processors. This is counted as
processor sockets, just like for per-processor licensing, so ignore extra
logical processors due to hyperthreading or multi-core processors. SQL will
see the logical processors and use them correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>I have been reading the licensing information for SQL 2005 as it relates to
> logical CPUs (hyperthreading or multicores) but all of the white papers I
> am
> reading cover the Per Processor model when addressing this.
> Does anyone know, when not using the Per Processor model (i.e. Per Server
> with either Device or User CALs) does the same logic apply? In other
> words,
> if I have a server with 4 physical CPUs that do hyperthreading, can I run
> Standard Edition and use all eight or do I need to go to Enterprise
> Edition
> to use more than 4 CPUs?
>|||Thanks for the response. Just one thing. Do you know if this is spelled out
anywhere on Microsoft's site. I realize it can be inferred by what is stated
in most of the papers they have on licensing models but I have management who
would feel better if it was spelled out.
"Geoff N. Hiten" wrote:
> The processor limits apply regardless of the licensing model. Standard
> Edition SQL 2005 supports up to four processors. This is counted as
> processor sockets, just like for per-processor licensing, so ignore extra
> logical processors due to hyperthreading or multi-core processors. SQL will
> see the logical processors and use them correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
> >I have been reading the licensing information for SQL 2005 as it relates to
> > logical CPUs (hyperthreading or multicores) but all of the white papers I
> > am
> > reading cover the Per Processor model when addressing this.
> >
> > Does anyone know, when not using the Per Processor model (i.e. Per Server
> > with either Device or User CALs) does the same logic apply? In other
> > words,
> > if I have a server with 4 physical CPUs that do hyperthreading, can I run
> > Standard Edition and use all eight or do I need to go to Enterprise
> > Edition
> > to use more than 4 CPUs?
> >
> >
>
>|||Try this:
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensing.doc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it relates
>> >to
>> > logical CPUs (hyperthreading or multicores) but all of the white papers
>> > I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>|||http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Notice the comments section across from Number of CPUs in the section titled
"Scalability and Performance" indicating support for multi-core processors.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Debbie" <Debbie@.discussions.microsoft.com> wrote in message
news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
> Thanks for the response. Just one thing. Do you know if this is spelled
> out
> anywhere on Microsoft's site. I realize it can be inferred by what is
> stated
> in most of the papers they have on licensing models but I have management
> who
> would feel better if it was spelled out.
> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it relates
>> >to
>> > logical CPUs (hyperthreading or multicores) but all of the white papers
>> > I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>|||You win. Your link is better than my link. :)
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uQDAdMHHGHA.648@.TK2MSFTNGP14.phx.gbl...
> Try this:
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensing.doc
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
> news:821BCA71-9E02-4E82-AB54-C2105B227503@.microsoft.com...
>> Thanks for the response. Just one thing. Do you know if this is spelled
>> out
>> anywhere on Microsoft's site. I realize it can be inferred by what is
>> stated
>> in most of the papers they have on licensing models but I have management
>> who
>> would feel better if it was spelled out.
>> "Geoff N. Hiten" wrote:
>> The processor limits apply regardless of the licensing model. Standard
>> Edition SQL 2005 supports up to four processors. This is counted as
>> processor sockets, just like for per-processor licensing, so ignore
>> extra
>> logical processors due to hyperthreading or multi-core processors. SQL
>> will
>> see the logical processors and use them correctly.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Debbie" <Debbie@.discussions.microsoft.com> wrote in message
>> news:AC408A92-B26A-4C5C-A72D-9D52341034B9@.microsoft.com...
>> >I have been reading the licensing information for SQL 2005 as it
>> >relates to
>> > logical CPUs (hyperthreading or multicores) but all of the white
>> > papers I
>> > am
>> > reading cover the Per Processor model when addressing this.
>> >
>> > Does anyone know, when not using the Per Processor model (i.e. Per
>> > Server
>> > with either Device or User CALs) does the same logic apply? In other
>> > words,
>> > if I have a server with 4 physical CPUs that do hyperthreading, can I
>> > run
>> > Standard Edition and use all eight or do I need to go to Enterprise
>> > Edition
>> > to use more than 4 CPUs?
>> >
>> >
>>
>