question with a stored procedure

I have a stored procedure that inserts a new row if the user doesn't exist in the table...that part works. I then need it to check to see if the user has changed their program code and update the row if they have. the way I'm trying to accomplish this is by selecting their id and program code by what I'm passing it, and if it does not exist, I update the row. For some reason it's not updating the table. I'm new to stored procedures so I'm not sure if I'm doing this correctly or not. Can you please take a look and let me know if this looks ok? This is for MSSQL 2000:

CREATE PROCEDURE [dbo].[InsertUpdateProcedure] (@.LastNamevarchar(255),@.FirstNamevarchar(255),@.Emailvarchar(255),@.ColleagueIDvarchar(50),@.Programvarchar(50))AS IFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueID)BEGIN--The row doesn't exist. Insert code goes hereINSERT INTO ept_users (users_colleague_id,users_last_name,users_first_name,users_email_address,users_program)VALUES(@.ColleagueID,@.LastName,@.FirstName,@.Email,@.Program)ENDIFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueIDAND users_program = @.Program)BEGINUPDATE ept_usersSETusers_program = @.Program,users_email_sent = 0,users_billed_current = 0,users_second_email_sent = 0WHEREusers_colleague_id = @.ColleagueIDENDGO

nevermind...it was choking on some null values. All is working now.sql

Question on table footer

Will the table header row not print on the last page if the only output for
that page is the table footer row? This seems to be that case in my report,
but I would like the table header row to also print. Can this be done?No. The header and footer of a group are siblings. A repeatable header will
be repeated with its children (subgroups or details) not with its siblings.
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Kemerling" <roger.kemerling@.matcomcorp.com> wrote in message
news:ehnCCZlpEHA.3988@.tk2msftngp13.phx.gbl...
> Will the table header row not print on the last page if the only output
for
> that page is the table footer row? This seems to be that case in my
report,
> but I would like the table header row to also print. Can this be done?
>sql

Question on Row Count Transformation

Hi all,

i'm using Row Count Transformation downloaded from http://www.sqlis.com/93.aspx to generate surrogate key.

in my data flow, i have OLE Data Source connected to Row Count Transformation connected to SCD.

i've configured ok and the package ran without error.

my problem is my surrogate key column won't generate the next increment number if i run the package the 2nd time and there is new row in the dimension, e.g.

Id BusinessKey BusinessName

1 RDO Radio
2 MAG Magazine
3 TV Television
1 INT Internet } added new row from source

did i miss something? please help. thanks.

You need to set the Start Number (seed) to the max ID + 1 in your table, so that it starts incrementing at the proper key.

You might try using an Exec SQL in the control flow to store the max id in a variable, then set the seed through an expression on the data flow component.

|||

I believe in the control flow, before the data flow, you will have to "select max(id) from table" and store that in a variable. Then in the data flow, you'll have to use expressions to set the starting number to that of the variable plus one.

Also, it's the "Row Number Transformation," not the "Row Count Transformation" just to be clear for those tuning in. SSIS comes with a "Row Count Transformation" built-in.

|||What John said above... I was typing while he was posting.|||

opss.... ya.... it's Row Number Transformation.. sorry for the mistake... thanks for the replies....

in this Row Number Transformation package, start number(seed) cannot be filled with expression... so can i say that this package can only be used to generate surrogate key if it's run only once?

thanks again...

|||

minority80 wrote:

opss.... ya.... it's Row Number Transformation.. sorry for the mistake... thanks for the replies....

in this Row Number Transformation package, start number(seed) cannot be filled with expression... so can i say that this package can only be used to generate surrogate key if it's run only once?

thanks again...

Right click on the transformation and select properties. In that window you will find the "Expressions" box. Expand that and go from there.

|||

sorry Phil, i don't see any expressions box/row in the properties window... :?

is this component depend on the sql server service pack installed in the machine ?

|||

This is the built in Properties window for Visual Studio, not the component level UI form.

For Data Flow components, you don't see the Expressions on the component, you must first select the Data Flow Task, just click the icon if in Control Flow, or just click the background if you are in the Data Flow design tab. The Properties grid will no show properties for the task, and since is only tasks that can expose expressions, they should now be visible.

All component expressions are actually listed at the top of the properties grid, or can be accssed via the Expressions property as with normal tasks.

|||

DarrenSQLIS wrote:

For Data Flow components, you don't see the Expressions on the component, you must first select the Data Flow Task, just click the icon if in Control Flow, or just click the background if you are in the Data Flow design tab. The Properties grid will no show properties for the task, and since is only tasks that can expose expressions, they should now be visible.

Darren is correct, which is why I shouldn't answer questions unless I'm in front of SSIS to validate my response. I should've known better that you access data flow object's expressions via the control flow.|||

Thanks Phil, Darren, John...

i just found out someone has posted the same question before.

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

sorry for repeating. previously i've been searching for row count transformation and couldn't find the answer i'm looking for. thanks for answering...

i still couldn't get the answer.

i've assigned the max(id) value to a variable and place the variable+1 in the expression box.

works fine, no error but instead of returning the next number for new values, the id column for the dimension starts incrementing again..

e.g.

1st run:

Id BusinessKey BusinessName

1 RDO Radio
2 MAG Magazine
3 TV Television

2nd run:

Id BusinessKey BusinessName

4 RDO Radio
5 MAG Magazine
6 TV Television
7 NWS Newspapers

i've already set the Id as fixed attribute in SCD... :-? :-(

|||

I don't think the ID column should be referenced in the SCD component as a fixed attribute. That's just the surrogate key you are assigning to the dimension, right?

From your example, I'm guessing that the BusinessKey column is what defines a row as being a match for the existing row.

|||

yes, the id column works as a surrogate key, shouldn't it be a fixed attribute?

i defined the BusinessKey column as Business Key under Key Type in SCD while Id column is defined as Not a key column under Key Type.

|||I think you might want to add the row number transformation between the SCD and its Insert destination. You shouldn't have the primary key involved in the SCD -- only the BusinessKey. When a new record comes in (as a result of a historical attribute change or simply a new record) it will get assigned the next counter out of the row number transformation when it is placed between the SCD and the OLE DB insert destination.|||thanks everyone...

Question on Querys

Ok in Replication for sql 2000,'

what happens when a query such as 'update tableA set a=1 where id=5' affects 1 row. What will happen when it does not affect any row.

Aaron

hi

1) with one row

in case of a merge replication => the updated Data replicated to the subscriber

in case of a transaction replication => the statement replicated and affects 1 rows on the subscriber again

2) with no row

in case of a merge replication => NOTHING

in case of a transaction replication => the statement replicated and affects 0 rows on the subscriber again

In Case of only one row, there is not big difference

in case of 100000 rows there is a big difference, because in case of merge replication, all of 100000 rows replicated step by step, in case of transaction replication, the statement will block the replication a long time, because the statement (not the result) will be replicate to the subscriber

bye

Thorsten Ueberschaer

Question on Log Backup and NORECOVERY

Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>

Question on Log Backup and NORECOVERY

Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>

Question on Flat File Import

I have a flat file that uses tabs as the column delimiters and cr-lf as row delimiters. The first portion of the file consists of only two columns for approximately 10 rows and then the file changes to 4 columns for the balance of the file, about 21 rows. The column names are in the first column and the data of interest is in the second column for the first 10 rows and then in the third column for the last 21 rows. Is it possible to set up something like this for parsing in SSIS? I've tried using two columns in the data flow task but then I get columns 1 and 2 through the whole file. If I tell it there are 4 columns in the file, it appends rows to each other so that there is a total of 4 columns in the first 10 rows. This reduces the row count to less than 10 and the data in these rows isn't in the proper place. Is there a way to handle this file in SSIS?

TIA

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

|||

KirkHaselden wrote:

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

i believe you mean "script component" not "script task".|||

Duane Douglas wrote:

KirkHaselden wrote:

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

i believe you mean "script component" not "script task".

Ha ha. The teacher becomes the pupil

Nice one Duane!!