Configurations

I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.

Pedro Martins

<quote>

By the way, where do you state that you want it "DontSaveSensitive", etc?

</quote>

Right-click in any empty area of the control flow tab, and select properties.

Under Security section, you will see "ProtectionLevel" property.

STEP 1:

If you want to use DontSaveSensitive level, then define a variable (say DBConnection) and sets its value to this entire string: Data Source=dbserver\dbinstance;User ID=dbuser;Password=dbpassword;Initial Catalog=YourDBName;Provider=SQLNCLI.1;Auto Translate=False

STEP 2:

Now, in the "Connection Managers" tab (usually in a thin window at the bottom), select your database connection manager, right-click and select properties. You will see "Expressions", select it - hit the ... (three dots) that appear next to it. A property expression editor window appears. In the "Property" dropdown, select "ConnectionString". Hit the ... for the expression next to it. From the list of variables in the left hand side pane, drag and drop the above variable (DBConnection) in the "Expressions:" area. Hit OK.

STEP 3: Right click in an empty area in the Control Flow tab, and select "Package Configurations...". Add a new configuration using the wizard and make sure that you select the above variable (DBConnection) as part of the configuration.

HTH,

Nitesh

|||

Can you be a little bit more specific about step 3? Must I create an XML file? What must I do specifically?

Regards,

Pedro Martins

|||

yes, you need to create an XML configuration file!

-Jamie

|||

i was trying to build my project when I got the following error:

Error 8 System.ApplicationException: Could not copy file "\\Bi4all002\cpm@.bi4all\Configura??es\BIALL2006Staging.dtsConfig" to the deployment utility output directory "D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment". > System.IO.IOException: The file 'D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment\BIALL2006Staging.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) End of inner exception stack trace at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0

As my project consists of various packages, I created configurations for all of them. Is that correct or not? Does it have something to do the problem?

Regards,

Pedro Martins

|||

Pedro Martins wrote:

As my project consists of various packages, I created configurations for all of them. Is that correct or not?

There is no right or wrong answer. Generally speaking if you are connecting to the same "thing" from multiple packages then you should share a single configuration file between them.

-Jamie

|||

Yes, I did just that. I just don't understand the error I am receiving. Do you have any hints?

Pedro Martins

Conditional SQL Triggers

Hi, I've been handed a task at work where I need to use SQL triggers to solve the problem.

I need to be able to run a Trigger when, and only when, a cell in a specific column in a row changes to a specific value.

For instance, say I have the following table:
CREATE TABLE source
(
ID tinyint NOT NULL,
contacttype tinyint NOT NULL,
)

If one of the rows is UPDATE'd to contacttype = 2, I want to fire a trigger, but not if it changes to anything else.

How can this be performed?You can attach a trigger to a table to respond to inserts, deletes or updates. I think you can narrow it down to a specific column (if columns_updated). Anything else goes in the trigger itself. See BOL for TRIGGER.|||create trigger blah on update
as
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype FROM inserted = 2
BEGIN
...insert code here...
END
END|||Understand, the TRIGGER will always fire. As shown, you want to control the logic inside the trigger.

What action do you need to take?

Just make sure the affected rows use the id of that row as the reason to modify that data|||Thanks for all the replies!

I tried mitchell007's code, and it helped a lot. I am now able to run SQL statements if a certain column is updated.
But I had a problem with the line: IF SELECT contacttype FROM inserted = 2
This results in a parse error. "Incorrect syntax near the keyword SELECT" and "Incorrect syntax near '='."

Here is my trigger code:
CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype from inserted = 2
BEGIN
print 'contacttype modified!'
END
END

Brett, my ultimate goal is to detect when a row in a contact table changes the value of the 'contacttype' column. If a row is created with contacttype = 2, or if an existing row is updated to that value, I want to create a new row in a different database.

Also, how can I know which row was altered? When all the trigger filters pass, I want to extract the updated row, and insert most of it's data into a different database.|||Okay, I've been working with this for some hours now, and have come a little further, but still have some obstacles to climb over.

First, when I try to use inserted.contacttype to get the value from the updated table, and into my new table, I get a "Error 128: The name 'contacttype' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

This works:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (1,2,3,4)
END

But this doesn't:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (inserted.contacttype,2,3,4)
END

Second, I am still struggeling with getting the trigger to run the INSERT statement only when contacttype changes to a specific number. Right now the INSERT fires when the contacttype field is updated to any value.|||The select statement after the update is not necessary. Try this:


CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF inserted.contacttype = 2
BEGIN
<perform needed operations here>
END
END|||Here is the complete trigger I've written so far, with tomh53's suggestion. With the "IF inserted.category_idx = 2" line I get a "Error 107: The column prefix 'inserted' does not match with a table name or alias name used in the query."

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
IF inserted.category_idx = 2
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma SELECT department, contact_id, name, number1, number2, business_idx, orgNr from inserted
END
END|||Scalpel ... my apologies for ** bad ** code. Try this:

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma
SELECT department, contact_id, name, number1, number2, business_idx, orgNr
FROM inserted
WHERE inserted.category_idx = 2
END

Concurrency Help

I need a little help figuring out how to design my database/web
services for a bidding system. I'm not sure how to solve the problem
of concurrency. I'll give an analogous example of what the system is
supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
of hard drive space each, which are sold to the highest bidder in 1
gig chunks. The bidder simply tells me how much they are willing to
spend and what state or country the computer should be located in. The
system should fill their order if they have the highest bid on a gig
with their criteria. How the hell do I assign those chunks to the
highest bidder efficiently. The problem with concurrency would be when
two or more people are getting data and submitting data at the same
time. If its not done synchronously or isn't thread safe, then the
amount of available space may be incorrectly read or written, for
example.
Is there a standard way to solve this type of database/web service
design problem?
David
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
SQL Server 2005 provides a new feature called SQL Service Broker
Link from BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
<Davidhere40@.gmail.com> wrote in message
news:1180226682.660613.122090@.h2g2000hsg.googlegro ups.com...
>I need a little help figuring out how to design my database/web
> services for a bidding system. I'm not sure how to solve the problem
> of concurrency. I'll give an analogous example of what the system is
> supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> of hard drive space each, which are sold to the highest bidder in 1
> gig chunks. The bidder simply tells me how much they are willing to
> spend and what state or country the computer should be located in. The
> system should fill their order if they have the highest bid on a gig
> with their criteria. How the hell do I assign those chunks to the
> highest bidder efficiently. The problem with concurrency would be when
> two or more people are getting data and submitting data at the same
> time. If its not done synchronously or isn't thread safe, then the
> amount of available space may be incorrectly read or written, for
> example.
> Is there a standard way to solve this type of database/web service
> design problem?
>
|||On May 27, 7:27 pm, Davidher...@.gmail.com wrote:
> On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
>
>
>
> Thank you very much! I really needed that.
> Dave
Turns out, I think the real answer to this question was to use
timestamps. I find out on my own. SQL broker is a pain to setup and
limits you to SQL server. I don't think it's necessary in my
situation. Especially because using timestamps allows you to do as
much work simultaneously as possible and if a different timestamp is
encounted for some data, you can simply reload the data and then try
again.
Dave

Concurrency Help

I need a little help figuring out how to design my database/web
services for a bidding system. I'm not sure how to solve the problem
of concurrency. I'll give an analogous example of what the system is
supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
of hard drive space each, which are sold to the highest bidder in 1
gig chunks. The bidder simply tells me how much they are willing to
spend and what state or country the computer should be located in. The
system should fill their order if they have the highest bid on a gig
with their criteria. How the hell do I assign those chunks to the
highest bidder efficiently. The problem with concurrency would be when
two or more people are getting data and submitting data at the same
time. If its not done synchronously or isn't thread safe, then the
amount of available space may be incorrectly read or written, for
example.
Is there a standard way to solve this type of database/web service
design problem?David
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
SQL Server 2005 provides a new feature called SQL Service Broker
Link from BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
<Davidhere40@.gmail.com> wrote in message
news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
>I need a little help figuring out how to design my database/web
> services for a bidding system. I'm not sure how to solve the problem
> of concurrency. I'll give an analogous example of what the system is
> supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> of hard drive space each, which are sold to the highest bidder in 1
> gig chunks. The bidder simply tells me how much they are willing to
> spend and what state or country the computer should be located in. The
> system should fill their order if they have the highest bid on a gig
> with their criteria. How the hell do I assign those chunks to the
> highest bidder efficiently. The problem with concurrency would be when
> two or more people are getting data and submitting data at the same
> time. If its not done synchronously or isn't thread safe, then the
> amount of available space may be incorrectly read or written, for
> example.
> Is there a standard way to solve this type of database/web service
> design problem?
>|||On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Davidhttp://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-...
> SQL Server 2005 provides a new feature called SQL Service Broker
> Link from BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
> <Davidher...@.gmail.com> wrote in message
> news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
> >I need a little help figuring out how to design my database/web
> > services for a bidding system. I'm not sure how to solve the problem
> > of concurrency. I'll give an analogous example of what the system is
> > supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> > of hard drive space each, which are sold to the highest bidder in 1
> > gig chunks. The bidder simply tells me how much they are willing to
> > spend and what state or country the computer should be located in. The
> > system should fill their order if they have the highest bid on a gig
> > with their criteria. How the hell do I assign those chunks to the
> > highest bidder efficiently. The problem with concurrency would be when
> > two or more people are getting data and submitting data at the same
> > time. If its not done synchronously or isn't thread safe, then the
> > amount of available space may be incorrectly read or written, for
> > example.
> > Is there a standard way to solve this type of database/web service
> > design problem?
Thank you very much! I really needed that.
Dave|||On May 27, 7:27 pm, Davidher...@.gmail.com wrote:
> On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
> > Davidhttp://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-...
> > SQL Server 2005 provides a new feature called SQL Service Broker
> > Link from BOL
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-066560476632.htm
> > <Davidher...@.gmail.com> wrote in message
> >news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
> > >I need a little help figuring out how to design my database/web
> > > services for a bidding system. I'm not sure how to solve the problem
> > > of concurrency. I'll give an analogous example of what the system is
> > > supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> > > of hard drive space each, which are sold to the highest bidder in 1
> > > gig chunks. The bidder simply tells me how much they are willing to
> > > spend and what state or country the computer should be located in. The
> > > system should fill their order if they have the highest bid on a gig
> > > with their criteria. How the hell do I assign those chunks to the
> > > highest bidder efficiently. The problem with concurrency would be when
> > > two or more people are getting data and submitting data at the same
> > > time. If its not done synchronously or isn't thread safe, then the
> > > amount of available space may be incorrectly read or written, for
> > > example.
> > > Is there a standard way to solve this type of database/web service
> > > design problem?
> Thank you very much! I really needed that.
> Dave
Turns out, I think the real answer to this question was to use
timestamps. I find out on my own. SQL broker is a pain to setup and
limits you to SQL server. I don't think it's necessary in my
situation. Especially because using timestamps allows you to do as
much work simultaneously as possible and if a different timestamp is
encounted for some data, you can simply reload the data and then try
again.
Dave

Concurrency Help

I need a little help figuring out how to design my database/web
services for a bidding system. I'm not sure how to solve the problem
of concurrency. I'll give an analogous example of what the system is
supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
of hard drive space each, which are sold to the highest bidder in 1
gig chunks. The bidder simply tells me how much they are willing to
spend and what state or country the computer should be located in. The
system should fill their order if they have the highest bid on a gig
with their criteria. How the hell do I assign those chunks to the
highest bidder efficiently. The problem with concurrency would be when
two or more people are getting data and submitting data at the same
time. If its not done synchronously or isn't thread safe, then the
amount of available space may be incorrectly read or written, for
example.
Is there a standard way to solve this type of database/web service
design problem?David
http://dimantdatabasesolutions.blog...er.ht
ml
SQL Server 2005 provides a new feature called SQL Service Broker
Link from BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6-0
66560476632.htm
<Davidhere40@.gmail.com> wrote in message
news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
>I need a little help figuring out how to design my database/web
> services for a bidding system. I'm not sure how to solve the problem
> of concurrency. I'll give an analogous example of what the system is
> supposed to do. In a nutshell, there are 10,000 computers with 10 gigs
> of hard drive space each, which are sold to the highest bidder in 1
> gig chunks. The bidder simply tells me how much they are willing to
> spend and what state or country the computer should be located in. The
> system should fill their order if they have the highest bid on a gig
> with their criteria. How the hell do I assign those chunks to the
> highest bidder efficiently. The problem with concurrency would be when
> two or more people are getting data and submitting data at the same
> time. If its not done synchronously or isn't thread safe, then the
> amount of available space may be incorrectly read or written, for
> example.
> Is there a standard way to solve this type of database/web service
> design problem?
>|||On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Davidhttp://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-
..
> SQL Server 2005 provides a new feature called SQL Service Broker
> Link from BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlmsg9/html/72caf5ae-4f7b-4e82-90f6
-066560476632.htm
> <Davidher...@.gmail.com> wrote in message
> news:1180226682.660613.122090@.h2g2000hsg.googlegroups.com...
>
>
Thank you very much! I really needed that.
Dave|||On May 27, 7:27 pm, Davidher...@.gmail.com wrote:
> On May 27, 2:56 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
>
>
>
>
>
>
>
> Thank you very much! I really needed that.
> Dave
Turns out, I think the real answer to this question was to use
timestamps. I find out on my own. SQL broker is a pain to setup and
limits you to SQL server. I don't think it's necessary in my
situation. Especially because using timestamps allows you to do as
much work simultaneously as possible and if a different timestamp is
encounted for some data, you can simply reload the data and then try
again.
Dave

Conceptual ideas - 2 tables one changes other complete Cursors?

I think cursors might help me, but I'm not sure. I'm looking for ideas
on how to solve a problem I have.

Consider two tables, one table contains student information (very wide
100 fields) , the other historical changes of the student information,
(narrow, just fields that record changes).

As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
RECORD_DT and has one student in it.

Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
records, since the student changed their major 2 times.

I want to end up with a table the contains 3 rows, the 2 changes to the
Major and the current student record. I want each row to be complete.
Everything that I have tried (joins, outer joins, union) I end up with
some field being null (in my example, the STUDENT_NAME would on be in
the original row, and null for the two changes)
I know this is pretty vague, but I am wondering if this is a place to
use CURSORS?
(Some of you may recognize this as a type 2 dimension or slowly
changing dimension as used in a data warehouse, which it is. I need to
build up my historical changes to I can feed it to my warehouse. I have
the current student record, and all the descreet changes made to the
student.)
TIA
RobHow about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJOR, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu|||How about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJOR, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1118684160.349709.100810@.z14g2000cwz.googlegr oups.com...
>I think cursors might help me, but I'm not sure. I'm looking for ideas
> on how to solve a problem I have.
> Consider two tables, one table contains student information (very wide
> 100 fields) , the other historical changes of the student information,
> (narrow, just fields that record changes).
> As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
> RECORD_DT and has one student in it.
> Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
> records, since the student changed their major 2 times.
> I want to end up with a table the contains 3 rows, the 2 changes to the
> Major and the current student record. I want each row to be complete.
> Everything that I have tried (joins, outer joins, union) I end up with
> some field being null (in my example, the STUDENT_NAME would on be in
> the original row, and null for the two changes)
> I know this is pretty vague, but I am wondering if this is a place to
> use CURSORS?
> (Some of you may recognize this as a type 2 dimension or slowly
> changing dimension as used in a data warehouse, which it is. I need to
> build up my historical changes to I can feed it to my warehouse. I have
> the current student record, and all the descreet changes made to the
> student.)
> TIA
> Rob

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1118684160.349709.100810@.z14g2000cwz.googlegr oups.com...
>I think cursors might help me, but I'm not sure. I'm looking for ideas
> on how to solve a problem I have.
> Consider two tables, one table contains student information (very wide
> 100 fields) , the other historical changes of the student information,
> (narrow, just fields that record changes).
> As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
> RECORD_DT and has one student in it.
> Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
> records, since the student changed their major 2 times.
> I want to end up with a table the contains 3 rows, the 2 changes to the
> Major and the current student record. I want each row to be complete.
> Everything that I have tried (joins, outer joins, union) I end up with
> some field being null (in my example, the STUDENT_NAME would on be in
> the original row, and null for the two changes)
> I know this is pretty vague, but I am wondering if this is a place to
> use CURSORS?
> (Some of you may recognize this as a type 2 dimension or slowly
> changing dimension as used in a data warehouse, which it is. I need to
> build up my historical changes to I can feed it to my warehouse. I have
> the current student record, and all the descreet changes made to the
> student.)
> TIA
> Rob

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.|||CREATE TABLE "dbo"."F_Student_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"STUDENT_LEAD_ID" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNAME" VARCHAR(40) NULL,
"STUDENT_FIRSTNAME" VARCHAR(40) NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL,
"STUDENT_MARKETCODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sample] VALUES
('100','900','2005-05-01','CAMARDA','ROBERT','HOST*001','TLS*123','I20')

CREATE TABLE "dbo"."Student_Changes_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"CHANGE_CODE" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS_ID
and STUDENT_ADMREP_ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*002','2001-01-03','HOST*002',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*003','2002-04-03','HOST*003',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*004','2003-02-13','HOST*004',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'DMI10','2003-02-13',NULL,'DMI10')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPLE, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks|||CREATE TABLE "dbo"."F_Student_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"STUDENT_LEAD_ID" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNAME" VARCHAR(40) NULL,
"STUDENT_FIRSTNAME" VARCHAR(40) NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL,
"STUDENT_MARKETCODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sample] VALUES
('100','900','2005-05-01','CAMARDA','ROBERT','HOST*001','TLS*123','I20')

CREATE TABLE "dbo"."Student_Changes_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"CHANGE_CODE" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS_ID
and STUDENT_ADMREP_ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*002','2001-01-03','HOST*002',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*003','2002-04-03','HOST*003',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*004','2003-02-13','HOST*004',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'DMI10','2003-02-13',NULL,'DMI10')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPLE, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks|||Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_id,
'' as student_lastname
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_campus_id,
t2.student_lastname
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'

I get:
1000139200NULL
1000139200003
1000139200006
1000139200016
1000139200HOST*006Iverson Iii

I need the last name (Iverson Iii) to be on all rows|||Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_id,
'' as student_lastname
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_campus_id,
t2.student_lastname
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'

I get:
1000139200NULL
1000139200003
1000139200006
1000139200016
1000139200HOST*006Iverson Iii

I need the last name (Iverson Iii) to be on all rows|||Try this:

SELECT S.student_id, S.student_lead_id, C.record_dt,
S.student_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--|||Try this:

SELECT S.student_id, S.student_lead_id, C.record_dt,
S.student_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--|||CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O-2dnduoiZPNdjDfRVn-oA@.giganews.com...
> Try this:
> SELECT S.student_id, S.student_lead_id, C.record_dt,
> S.student_lastname, S.student_firstname,
> COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
> COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
> S.student_marketcode_id
> FROM f_student_sample AS S,
> student_changes_sample AS C
> --
> David Portas
> SQL Server MVP
> --|||CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O-2dnduoiZPNdjDfRVn-oA@.giganews.com...
> Try this:
> SELECT S.student_id, S.student_lead_id, C.record_dt,
> S.student_lastname, S.student_firstname,
> COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
> COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
> S.student_marketcode_id
> FROM f_student_sample AS S,
> student_changes_sample AS C
> --
> David Portas
> SQL Server MVP
> --|||David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.student_campus_id,S*.student_campus_id) AS
student_campus_id,
COALESCE(C.student_admrep_id,S*.student_admrep_id) AS
student_admrep_id,
COALESCE(C.student_market_id,s.student_market_id) as student_market_id,
COALESCE(c.changeN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks|||David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.student_campus_id,S*.student_campus_id) AS
student_campus_id,
COALESCE(C.student_admrep_id,S*.student_admrep_id) AS
student_admrep_id,
COALESCE(C.student_market_id,s.student_market_id) as student_market_id,
COALESCE(c.changeN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks|||rcamarda (rcamarda@.cablespeed.com) writes:
> David, this is pretty cool (although I'm not sure whats going on...Ill
> have to read up on coalesce).
> It seems that eh coalesce is returning the first non-null field that
> it's given in the argument list.

That's it!

As for where to read about coalesce, CASE etc, see my signature.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||rcamarda (rcamarda@.cablespeed.com) writes:
> David, this is pretty cool (although I'm not sure whats going on...Ill
> have to read up on coalesce).
> It seems that eh coalesce is returning the first non-null field that
> it's given in the argument list.

That's it!

As for where to read about coalesce, CASE etc, see my signature.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(changed_records.<field>, current_records.<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records.business_id=changed_records.busine ss_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID",
"STUDENT_APPLICATION_DT",
"STUDENT_ETHNIC_ID",
"STUDENT_VISA_TYPE",
"STUDENT_GENDER",
"STUDENT_MARITAL",
"STUDENT_BIRTH_DT",
"STUDENT_BIRTH_PLACE",
"STUDENT_LEAD_ID",
"STUDENT_INPUT_DT",
"STUDENT_FINAID_REQ",
"STUDENT_VA_STATUS",
"STUDENT_VA_DT",
"STUDENT_EMAIL",
"STUDENT_FAX",
"STUDENT_COUNTRY_ID",
"STUDENT_COUNTRY_CAPTION",
"RECORD_DT",
"STUDENT_LASTNAME",
"STUDENT_FIRSTNAME",
"STUDENT_MI",
"STUDENT_ADDRESS1",
"STUDENT_ADDRESS2",
"STUDENT_CITY",
"STUDENT_STATE",
"STUDENT_ZIP",
"STUDENT_HOME_PHONE",
"STUDENT_WORK_PHONE",
"STUDENT_HS_NAME",
"STUDENT_EXTERNAL_ID",
"STUDENT_HS_GRAD_DT",
"STUDENT_FINANCIAL_AID",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
"STUDENT_OTHER_COMPANY_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
"STUDENT_WORK_EXTENSION",
"STUDENT_EXPECTED_START_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
"STUDENT_DO_NOT_EMAIL",
"STUDENT_VISA_EXCPT_SESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
"STUDENT_TALISMA_STATUS",
"STUDENT_TALISMA_SUBSTATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
"STUDENT_MARKETCODE_ID"
FROM "dbo"."STUDENT"
UNION
SELECT
STUDENT."STUDENT_ID",
STUDENT."STUDENT_APPLICATION_DT",
STUDENT."STUDENT_ETHNIC_ID",
STUDENT."STUDENT_VISA_TYPE",
STUDENT."STUDENT_GENDER",
STUDENT."STUDENT_MARITAL",
STUDENT."STUDENT_BIRTH_DT",
STUDENT."STUDENT_BIRTH_PLACE",
STUDENT."STUDENT_LEAD_ID",
STUDENT."STUDENT_INPUT_DT",
STUDENT."STUDENT_FINAID_REQ",
STUDENT."STUDENT_VA_STATUS",
STUDENT."STUDENT_VA_DT",
STUDENT."STUDENT_EMAIL",
STUDENT."STUDENT_FAX",
STUDENT."STUDENT_COUNTRY_ID",
STUDENT."STUDENT_COUNTRY_CAPTION",
STUDENT_CHANGES."RECORD_DT",
STUDENT."STUDENT_LASTNAME",
STUDENT."STUDENT_FIRSTNAME",
STUDENT."STUDENT_MI",
STUDENT."STUDENT_ADDRESS1",
STUDENT."STUDENT_ADDRESS2",
STUDENT."STUDENT_CITY",
STUDENT."STUDENT_STATE",
STUDENT."STUDENT_ZIP",
STUDENT."STUDENT_HOME_PHONE",
STUDENT."STUDENT_WORK_PHONE",
STUDENT."STUDENT_HS_NAME",
STUDENT."STUDENT_EXTERNAL_ID",
STUDENT."STUDENT_HS_GRAD_DT",
STUDENT."STUDENT_FINANCIAL_AID",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT."STUDENT_OTHER_COMPANY_CAPTION",
COALESCE(student_changes.student_campus_id,student .student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT."STUDENT_WORK_EXTENSION",
STUDENT."STUDENT_EXPECTED_START_DT",
STUDENT."STUDENT_SPONSOR_ID",
STUDENT."STUDENT_LOAD_DT",
STUDENT."STUDENT_DO_NOT_CALL",
STUDENT."STUDENT_DO_NOT_MAIL",
STUDENT."STUDENT_DO_NOT_EMAIL",
STUDENT."STUDENT_VISA_EXCPT_SESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT."STUDENT_TALISMA_STATUS",
STUDENT."STUDENT_TALISMA_SUBSTATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_changes.student_ADMREP_id,student .student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT."STUDENT_MARKETCODE_ID"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions|||Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(changed_records.<field>, current_records.<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records.business_id=changed_records.busine ss_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID",
"STUDENT_APPLICATION_DT",
"STUDENT_ETHNIC_ID",
"STUDENT_VISA_TYPE",
"STUDENT_GENDER",
"STUDENT_MARITAL",
"STUDENT_BIRTH_DT",
"STUDENT_BIRTH_PLACE",
"STUDENT_LEAD_ID",
"STUDENT_INPUT_DT",
"STUDENT_FINAID_REQ",
"STUDENT_VA_STATUS",
"STUDENT_VA_DT",
"STUDENT_EMAIL",
"STUDENT_FAX",
"STUDENT_COUNTRY_ID",
"STUDENT_COUNTRY_CAPTION",
"RECORD_DT",
"STUDENT_LASTNAME",
"STUDENT_FIRSTNAME",
"STUDENT_MI",
"STUDENT_ADDRESS1",
"STUDENT_ADDRESS2",
"STUDENT_CITY",
"STUDENT_STATE",
"STUDENT_ZIP",
"STUDENT_HOME_PHONE",
"STUDENT_WORK_PHONE",
"STUDENT_HS_NAME",
"STUDENT_EXTERNAL_ID",
"STUDENT_HS_GRAD_DT",
"STUDENT_FINANCIAL_AID",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
"STUDENT_OTHER_COMPANY_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
"STUDENT_WORK_EXTENSION",
"STUDENT_EXPECTED_START_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
"STUDENT_DO_NOT_EMAIL",
"STUDENT_VISA_EXCPT_SESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
"STUDENT_TALISMA_STATUS",
"STUDENT_TALISMA_SUBSTATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
"STUDENT_MARKETCODE_ID"
FROM "dbo"."STUDENT"
UNION
SELECT
STUDENT."STUDENT_ID",
STUDENT."STUDENT_APPLICATION_DT",
STUDENT."STUDENT_ETHNIC_ID",
STUDENT."STUDENT_VISA_TYPE",
STUDENT."STUDENT_GENDER",
STUDENT."STUDENT_MARITAL",
STUDENT."STUDENT_BIRTH_DT",
STUDENT."STUDENT_BIRTH_PLACE",
STUDENT."STUDENT_LEAD_ID",
STUDENT."STUDENT_INPUT_DT",
STUDENT."STUDENT_FINAID_REQ",
STUDENT."STUDENT_VA_STATUS",
STUDENT."STUDENT_VA_DT",
STUDENT."STUDENT_EMAIL",
STUDENT."STUDENT_FAX",
STUDENT."STUDENT_COUNTRY_ID",
STUDENT."STUDENT_COUNTRY_CAPTION",
STUDENT_CHANGES."RECORD_DT",
STUDENT."STUDENT_LASTNAME",
STUDENT."STUDENT_FIRSTNAME",
STUDENT."STUDENT_MI",
STUDENT."STUDENT_ADDRESS1",
STUDENT."STUDENT_ADDRESS2",
STUDENT."STUDENT_CITY",
STUDENT."STUDENT_STATE",
STUDENT."STUDENT_ZIP",
STUDENT."STUDENT_HOME_PHONE",
STUDENT."STUDENT_WORK_PHONE",
STUDENT."STUDENT_HS_NAME",
STUDENT."STUDENT_EXTERNAL_ID",
STUDENT."STUDENT_HS_GRAD_DT",
STUDENT."STUDENT_FINANCIAL_AID",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT."STUDENT_OTHER_COMPANY_CAPTION",
COALESCE(student_changes.student_campus_id,student .student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT."STUDENT_WORK_EXTENSION",
STUDENT."STUDENT_EXPECTED_START_DT",
STUDENT."STUDENT_SPONSOR_ID",
STUDENT."STUDENT_LOAD_DT",
STUDENT."STUDENT_DO_NOT_CALL",
STUDENT."STUDENT_DO_NOT_MAIL",
STUDENT."STUDENT_DO_NOT_EMAIL",
STUDENT."STUDENT_VISA_EXCPT_SESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT."STUDENT_TALISMA_STATUS",
STUDENT."STUDENT_TALISMA_SUBSTATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_changes.student_ADMREP_id,student .student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT."STUDENT_MARKETCODE_ID"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions

concatenation

Hi,

I have previously posted but the reply given didn't solve my purpose.
Please check & revert.

I am unable to concat 2 fields with a space between them in sql query.
I want to write my query in following fashion only as there are many conditions which I concat. Thus I am using variable @.sql_st and not the direct sql statement.

Following query works perfect
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

but when modified to get space between first & last name of employee I get an error
DECLARE @.SQL_ST VARCHAR(8000)
set @.SQL_ST = 'SELECT EM.EMPLOYEE_ID,
(EM.FIRST_NAME + ' ' + EM.LAST_NAME) as emp_name from employee_master em'
execute (@.SQL_ST)

Pls reply ASAP.

Thanks
Shubhangi/*Solo:*/
select (rtrim(EM.FIRST_NAME) + ' ' + rtrim(EM.LAST_NAME)) as pendejo from employee em

--Utilizando una variable:
declare @.x varchar(200)
set @.x = 'select (rtrim(casefunctionality) + '' '' + rtrim(casename)) as mames from employee em'
exec (@.x)

El problema es que le debes de poner doble comilla simple.

El RTrim es slo para quitarle los espacios.|||Hi,
The following code will solve your problem
instead of two single quotes use four single quotes to concatnate two fields
-------------------
declare @.str1 varchar(8000)
set @.str1=('select EM.EMPLOYEE_ID,EM.FIRST_NAME+'' ''+EM.LAST_NAME as EMPLOYEENAME from EMPLOYEE_MASTER EM')
print @.str1
exec (@.str1)