Help with data flow

I would like to use Integration Services to update data in my datawarehouse. I have a table called "AgentStats" that stores archived data from the past 3 years. I would like to import the current year's data from the production server into the same table in my datawarehouse and have my ETL update only the current year's day on a daily basis. The current year's data is constantly updated in the datasource, so I achive the data at the end of the year. Any ideas how I can accomplish this?

Thank You

-Sam

This seems like a simple task, so here goes-

Source (Filter on date to restrict to current year if more than that present) --> Lookup (Lookup to detect if current row exists, using PK columns) --1-> Insert when not exists, Destination

--2-> Update when exists, OLE-DB Command

Things are often more complicated than this, and there are several ways of doing the same job, but that outlines the simplest.

|||Read through this thread: Checking to see if a record exists if so update else insert

Help with CustomComponent in SSIS-DataFlow

Hello Trying to figure out a clever solution for splitting multivalued columns out into n-columns. For that I've build a custom component in SSIS using ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/4dc0f631-8fd6-4007-b573-ca67f58ca068.htm as an example. I need to be able to add columns to the OutputCollection in designtime, but the designer returns an error: Error at Data Flow Task [Uppercase [5910]]: The component "Uppercase" (5910) does not allow setting output column datatype properties. How do I enable the designer to accept designtime changes in the columncollection?  Kind regards

You have to override the method SetOutputColumnDataTypeProperties in your component and implement it like this:

public override void SetOutputColumnDataTypeProperties(int outputID, int outputColumnID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType, int length, int precision, int scale, int codePage)

{

IDTSOutputCollection90 outputColl = this.ComponentMetaData.OutputCollection;

IDTSOutput90 output = outputColl.GetObjectByID(outputID);

IDTSOutputColumnCollection90 columnColl = output.OutputColumnCollection;

IDTSOutputColumn90 column = columnColl.GetObjectByID(outputColumnID);

column.SetDataTypeProperties(dataType, length, precision, scale, codePage);

}

Help with custom total in matrix (hopefully easy adjustment)

Hey guys. I have a matrix that is working the way I want it as far as
groupings and data. I am doing a custom total that works for the first row
group with the following code:
Private currentTotal As Double = 0
Function SetCurrentValue(ByVal currentValue As Double) As Double
currentTotal += currentValue
return currentTotal
End Function
Function GetCurrentValue() As Double
return currentTotal
End Function
Function ResetTotal() As Boolean
currentTotal=0
return True
End Function
In my detail I have:
=IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
If I use only the first IIF statement I get the correct values 19, 38 , 57,
76, 95, 114, 133, 152, 171, 190, 209, and 228.
If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
361, 399, 437 in the first row and in the second row I get:
494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
I need to run the reset method in between row groupings to get it to go back
to 19 for the second row (it will be a different starting value eventually,
but for now I need it to reset).
I tried putting the code as a second row grouping and the column subtotal,
but it won't reset in the proper spot. Let me know!
Thanks!,
BJYou can use rownumber with scope/group so that it resets each and everytime
group changes. Provided your 19,38,57 etc.. are fixed values...
Amarnath
"bjkaledas" wrote:
> Hey guys. I have a matrix that is working the way I want it as far as
> groupings and data. I am doing a custom total that works for the first row
> group with the following code:
> Private currentTotal As Double = 0
> Function SetCurrentValue(ByVal currentValue As Double) As Double
> currentTotal += currentValue
> return currentTotal
> End Function
> Function GetCurrentValue() As Double
> return currentTotal
> End Function
> Function ResetTotal() As Boolean
> currentTotal=0
> return True
> End Function
> In my detail I have:
> =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> If I use only the first IIF statement I get the correct values 19, 38 , 57,
> 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> 361, 399, 437 in the first row and in the second row I get:
> 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> I need to run the reset method in between row groupings to get it to go back
> to 19 for the second row (it will be a different starting value eventually,
> but for now I need it to reset).
> I tried putting the code as a second row grouping and the column subtotal,
> but it won't reset in the proper spot. Let me know!
> Thanks!,
> BJ|||Where would I use the rownumber function? Would I keep track of this in my
custom code or when I call the function in my detail textbox?
"Amarnath" wrote:
> You can use rownumber with scope/group so that it resets each and everytime
> group changes. Provided your 19,38,57 etc.. are fixed values...
>
> Amarnath
> "bjkaledas" wrote:
> > Hey guys. I have a matrix that is working the way I want it as far as
> > groupings and data. I am doing a custom total that works for the first row
> > group with the following code:
> >
> > Private currentTotal As Double = 0
> > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > currentTotal += currentValue
> > return currentTotal
> > End Function
> >
> > Function GetCurrentValue() As Double
> > return currentTotal
> > End Function
> >
> > Function ResetTotal() As Boolean
> > currentTotal=0
> > return True
> > End Function
> >
> > In my detail I have:
> >
> > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> >
> > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> >
> > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > 361, 399, 437 in the first row and in the second row I get:
> >
> > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> >
> > I need to run the reset method in between row groupings to get it to go back
> > to 19 for the second row (it will be a different starting value eventually,
> > but for now I need it to reset).
> >
> > I tried putting the code as a second row grouping and the column subtotal,
> > but it won't reset in the proper spot. Let me know!
> >
> > Thanks!,
> >
> > BJ|||You can use in the textbox so that when it displays it resets exactly.
Amarnath
"bjkaledas" wrote:
> Where would I use the rownumber function? Would I keep track of this in my
> custom code or when I call the function in my detail textbox?
> "Amarnath" wrote:
> > You can use rownumber with scope/group so that it resets each and everytime
> > group changes. Provided your 19,38,57 etc.. are fixed values...
> >
> >
> > Amarnath
> >
> > "bjkaledas" wrote:
> >
> > > Hey guys. I have a matrix that is working the way I want it as far as
> > > groupings and data. I am doing a custom total that works for the first row
> > > group with the following code:
> > >
> > > Private currentTotal As Double = 0
> > > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > > currentTotal += currentValue
> > > return currentTotal
> > > End Function
> > >
> > > Function GetCurrentValue() As Double
> > > return currentTotal
> > > End Function
> > >
> > > Function ResetTotal() As Boolean
> > > currentTotal=0
> > > return True
> > > End Function
> > >
> > > In my detail I have:
> > >
> > > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> > >
> > > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> > >
> > > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > > 361, 399, 437 in the first row and in the second row I get:
> > >
> > > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> > >
> > > I need to run the reset method in between row groupings to get it to go back
> > > to 19 for the second row (it will be a different starting value eventually,
> > > but for now I need it to reset).
> > >
> > > I tried putting the code as a second row grouping and the column subtotal,
> > > but it won't reset in the proper spot. Let me know!
> > >
> > > Thanks!,
> > >
> > > BJ

Help with Custom Security Extension and Application Pool Identity

This is an interesting one.
We have implemented a custom security extension that we call from
within an application. We pass in a userid and password into the
LogonUser method, which is then checked against our database.
However, we have a Master database, and then a few other small
databases for different clients, so the extension uses another
supplied value to go to the master database, and lookup which database
it needs to verify the user in.
So, in the RSReportServer.config file, we store the connection string
to the Master DB.
This is used to connect to the Master DB, and then lookup the
connection string for the secondary DB.
The connection string for the secondary DB uses Integrated
Authentication (as our application requires this).
When the Application Pool (in IIS) that ReportServer runs under, is
set to the NetworkUser, we get an error returned from the WebService
(called from within our application), which says that the Network
Service was not authorized to access the secondary DB. Understandable
:)
So, we changed the Application Pool, so that it uses a Domain account
as it's Identity. Now we receive the following error back (and in all
the log files I can find)..
System.Web.Services.Protocols.SoapException: An internal error
occurred on the report server. See the error log for more details.
--> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified. at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo) at
RSManagedCrypto.RSCrypto.ExportPublicKey() at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection() at
Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName) at
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name) at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTranslator
pathTranslator, String userSuppliedPath, String parameterName) at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) -- End of inner
exception stack trace -- at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items)
The Application Pool User is a member of the IIS_WPG group, and has
been granted write access to the Windows\Temp folder, and the MSSQL
folder where RS is installed (and sub folders).
I also tried adding it to the local machine admin group, and it made
no difference.
That user also has DBO access to all the DBs on our database server.
Can anyone help please?
Thanks
RichardWill you send the report server web service log file?
Also, can you verify that the new identity has a user profile on the machine
(the user should have an entry under c:\Documents and Settings)?
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Greenwell" <lazygun@.gmail.com> wrote in message
news:42b7583d.0406251224.18af1b8@.posting.google.com...
> This is an interesting one.
> We have implemented a custom security extension that we call from
> within an application. We pass in a userid and password into the
> LogonUser method, which is then checked against our database.
> However, we have a Master database, and then a few other small
> databases for different clients, so the extension uses another
> supplied value to go to the master database, and lookup which database
> it needs to verify the user in.
> So, in the RSReportServer.config file, we store the connection string
> to the Master DB.
> This is used to connect to the Master DB, and then lookup the
> connection string for the secondary DB.
> The connection string for the secondary DB uses Integrated
> Authentication (as our application requires this).
> When the Application Pool (in IIS) that ReportServer runs under, is
> set to the NetworkUser, we get an error returned from the WebService
> (called from within our application), which says that the Network
> Service was not authorized to access the secondary DB. Understandable
> :)
> So, we changed the Application Pool, so that it uses a Domain account
> as it's Identity. Now we receive the following error back (and in all
> the log files I can find)..
> System.Web.Services.Protocols.SoapException: An internal error
> occurred on the report server. See the error log for more details.
> -->
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified. at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo) at
> RSManagedCrypto.RSCrypto.ExportPublicKey() at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at
Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection() at
> Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName) at
>
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(Stri
ng
> key) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty
(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(Stri
ng
> name) at
Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate) at
> Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTransl
ator
> pathTranslator, String userSuppliedPath, String parameterName) at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) -- End of inner
> exception stack trace -- at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) at
> Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items) -- End of inner exception stack
> trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items)
> The Application Pool User is a member of the IIS_WPG group, and has
> been granted write access to the Windows\Temp folder, and the MSSQL
> folder where RS is installed (and sub folders).
> I also tried adding it to the local machine admin group, and it made
> no difference.
> That user also has DBO access to all the DBs on our database server.
> Can anyone help please?
> Thanks
> Richard|||I have emailed you the log file for this problem.
The user that the application pool runs under does not have an entry
under Docs and Settings, but it is a different user to that which the
Report Server Windows Service runs under, which Does have an entry
under Docs and Settings.
I have never seen an app pool user have a Docs and Settings folder :)
Thank you
Richard
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message news:<OTBYLlYXEHA.748@.TK2MSFTNGP11.phx.gbl>...
> Will you send the report server web service log file?
> Also, can you verify that the new identity has a user profile on the machine
> (the user should have an entry under c:\Documents and Settings)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
<snip>sql

Help with Cursor to insert 100 rows at a time

Hi all,

Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.

Here is what I have till now:

CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON

declare @.Col1 int
declare @.Col2 char(9)
DECLARE @.RETURNVALUE int
DECLARE @.ERRORMESSAGETXT varchar(510)
DECLARE @.ERRORNUM int
DECLARE @.LOCALROWCOUNT int

declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)

set @.RETURNVALUE = 0
set @.ERRORNUM = 0

BEGIN

open Insert_Cur
fetch NEXT from Insert_Cur into @.Col1, @.Col2
while (@.@.FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @.Col1,@.Col2

SELECT @.ERRORNUM = @.@.ERROR, @.LOCALROWCOUNT = @.@.ROWCOUNT
IF @.ERRORNUM = 0
BEGIN
IF @.LOCALROWCOUNT >= 1
BEGIN
SELECT @.RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
SELECT @.RETURNVALUE = 1
END

fetch NEXT from Insert_Cur into @.Col1, @.Col2
end

close Insert_Cur
deallocate Insert_Cur

RETURN @.RETURNVALUE
ENDFirst of all, I don't understand what you really want to do so I can't give you a usable or correct response. I can tell you almost certainly that a cursor is not the correct answer.

You have a PK. A cursor isn't needed and it will probably hurt you in terms of both complexity and performance.

Can you describe what you really want in terms of the real world? In business or end-user terms, not in geek speak.

There are definitely ways to do what you want. They are probably simple and fast. I don't know enough to help you yet, but if you describe what you are trying to do a bit better then I'd bet that someone here can help.

-PatP|||It seems to me your not inserting all rows with 100 rows at the time, you're inserting 100 rows one at the time. After row no 100, the cursor is finished and your procedure is done.

This should be more like what you descibe (albeit not the most efficient way, but at least it eliminates the cursor):
WHILE EXISTS (
SELECT 1
FROM Table1
WHERE Col1 NOT IN (SELECT Col1 FROM Table2)
)
BEGIN
INSERT table2 (Col1, Col2)
SELECT top 100 Col1,Col2
FROM Table1
WHERE Col1 NOT IN (SELECT Col1 FROM Table2)

-- Maybe do some error checking here
END

A question about your code:
SELECT @.ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @.@.ERROR
RAISERROR (@.ERRORMESSAGETXT, 16, 1)
What's this supposed to do?
1) The message is already raised the moment the error occurs
2) What about the placeholders in the messages?
3) @.@.ERROR at that moment is always 0|||Table 1 has over 500 million rows. The task is To select data from Table 1 (based on business rules) and insert into Table 2. The concern was Selecting all data may take a long time to execute and in case of any issue with the quety, a long time to roll back. Hence 100 rows at a time using a cursor.|||As mentioned, forget the cursor! They are for row-by-row processing.

Help with cursor and decimal values

The following table when using a cursor gives 0 for decimals where the value
s
are below 1. I am trying to use this to apply a factor and always get a zero
if the factor below 1, as a result my logic fails.
Can some one help me on this?
-- ========== Table & data =============
CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
INSERT INTO TestFactor VALUES (1, 0.25)
INSERT INTO TestFactor VALUES (2, 0.50)
INSERT INTO TestFactor VALUES (3, 0.75)
INSERT INTO TestFactor VALUES (4, 0.125)
INSERT INTO TestFactor VALUES (5, 0.25)
INSERT INTO TestFactor VALUES (6, 2)
INSERT INTO TestFactor VALUES (7, 1)
-- ========== Table & data =============
DECLARE @.Factor decimal
DECLARE my CURSOR
FOR SELECT FactorValue from TestFactor
OPEN my
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my INTO
@.Factor
SELECT @.Factor
END
CLOSE my
DEALLOCATE my
Thankschange DECLARE @.Factor decimal to
DECLARE @.Factor decimal (5,2) -- same as in the table
http://sqlservercode.blogspot.com/|||Change
DECLARE @.Factor decimal
to
DECLARE @.Factor decimal(5,2)
"Ram" wrote:

> The following table when using a cursor gives 0 for decimals where the val
ues
> are below 1. I am trying to use this to apply a factor and always get a ze
ro
> if the factor below 1, as a result my logic fails.
> Can some one help me on this?
> -- ========== Table & data =============
> CREATE Table TestFactor (TestFactorId int, FactorValue decimal(5,2))
> INSERT INTO TestFactor VALUES (1, 0.25)
> INSERT INTO TestFactor VALUES (2, 0.50)
> INSERT INTO TestFactor VALUES (3, 0.75)
> INSERT INTO TestFactor VALUES (4, 0.125)
> INSERT INTO TestFactor VALUES (5, 0.25)
> INSERT INTO TestFactor VALUES (6, 2)
> INSERT INTO TestFactor VALUES (7, 1)
> -- ========== Table & data =============
>
> DECLARE @.Factor decimal
> DECLARE my CURSOR
> FOR SELECT FactorValue from TestFactor
> OPEN my
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM my INTO
> @.Factor
> SELECT @.Factor
> END
> CLOSE my
> DEALLOCATE my
> Thanks
>|||Thanks Mark & SQL
I was going crazy on this. I think I need a vacation
"Mark Williams" wrote:
> Change
> DECLARE @.Factor decimal
> to
> DECLARE @.Factor decimal(5,2)
> "Ram" wrote:
>

Help with CURSOR

We have a tree structure containing section names. Each node is a section name and each section can have subsections. I have to copy the tree structure but need to maintain the parent-child relationship established within the id / parent_id fields. How do i acheive this?

For example i have the tree
Section 1
|-Section 1.1
Section 2
|-Section 2.1

The "Section" table contains 3 fields: id, parent_id, and caption. ID is the identity of the section record and parent_id contains NULL or the ID of this record's parent to create a child. So "Section 1" (id=1, parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1" (id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).

I would like to copy this sucture to create 4 new sections but they need to maintain their id/parent_id relationships BUT with new IDs. For this i created the following stored procedure:
-
CREATE PROCEDURE [dbo].[CopySection]
AS
-- Declare a temporary variable table for storing the sections
DECLARE @.tblSection TABLE
(
id int,
parent_id int,
caption varchar(max),
)

DECLARE @.newAgendaID int, @.newSectionID int;
DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max);

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table
INSERT INTO @.tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID
DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FOR UPDATE OF parent_id;
OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Insert the new Section and record the identity
INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@.tparent_id, @.tcaption);
SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection
-- Update all temp variable records to point to the new parent_id
UPDATE @.tblSection SET parent_id = @.newSectionID WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
END

CLOSE c1
DEALLOCATE c1

END
-

The critical "UPDATE @.tblSection" part doesnt seem to update the temp variable table with the @.newSectionID (the actual section identity obtained after inserting a real record into the tblSection table). So in the end the inserted records into tblSection still point to the incorrect parent_id instead of the copied record's parent_id.

Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset?

You can use the following query.. Here we are not using any cursor..

Declare @.Section Table
(
ParentId int,
Caption varchar(100)
)
Declare @.ParentId as int;
Declare @.NewParentId as int;
Declare @.Caption as varchar(100);

Insert into @.Section
Select Id,Caption from tblSection Where Parent_ID is NULL -- you can filter your desired id
While Exists(Select 1 From @.Section)
Begin
Select Top 1 @.ParentId = ParentId, @.Caption = Caption From @.Section;
Insert Into tblSection(parent_id, caption) values(null, @.Caption);

Select @.NewParentId =Scope_Identity();
Delete From @.Section Where ParentId = @.ParentId ;

Insert Into tblSection(parent_id, caption)
Select @.NewParentId, caption From tblSection Where parent_id = @.ParentId;
End

Select * From tblSection;

|||

I have tried quantass 's code and it does not do the parentid properly as it keeps using the old parentid of the previous tree structure - can you please show me the actual code you got it working or how can it be solved please?

I really am in desperate need of getting one that actually works properly.

Thanks

|||Which version of SQL server are you running?|||

I am using SQL server 2005 - I think I have tried to get it working but I don't know if this is the correct approach which I have modified quanass's sql.

-- Declare a temporary variable table for storing the sections

DECLARE @.tblSection TABLE

(

id int,

parent_id int,

caption varchar(max),

old_id int

)

DECLARE @.newSectionID int;

DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max),@.told_id int;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table

INSERT INTO @.tblSection SELECT id, parent_id, caption, old_id FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID

DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FOR UPDATE OF parent_id;

OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption,@.told_id;

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Insert the new Section and record the identity

INSERT INTO tblSection (parent_id, caption) VALUES (@.tparent_id, @.tcaption);

SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection

-- Update all temp variable records to point to the new parent_id

UPDATE @.tblSection SET parent_id = @.newSectionID, old_id = @.tid WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption,@.told_id;

END

CLOSE c1

DEALLOCATE c1

SELECT * FROM @.tblSection <from this point I tried to get the parentid done properly and not hanging onto the old parentID

WHILE EXISTS(SELECT 1 FROM @.tblSection)

BEGIN

SELECT @.tparent_id=parent_id,@.told_id=old_id FROM @.tblSection

UPDATE tblSection SET[parent_id] = @.tparent_id WHERE parent_id = @.told_id

DELETE FROM @.tblSection WHERE UpLevelID = @.tUpLevelID

END

END

I could do with some feedback and whether it can be improved and what can be changed on this?

Thanks

|||

I have tried with the modified SQL as above and it did work with small data. But I found if I tried with 100 records - the parentid gets really messed up and doesn't look right.

I would really appreciate any help and examples or show me the corrections on my modified SQL please as I have spent a week struggling in vain to actually get it working properly.

ID ParentID MenuID Name
====================
1 0 1 sun
2 1 1 mars
3 2 1 charon
4 2 1 flubbie
5 3 1 blub

6 0 2 sun <-new menu as menuID of 1as it is a copy of it
7 6 2 mars
8 7 2 charon
9 7 2 flubbie
10 8 2 blub

Please help as I am in desperate situation on this. Thanks

|||I will give it a look and see what I can come up with; sorry I didn't respond yesterday. I've been off in DB2 land most of this week.|||

This is my first pass and I feel like I didn't do a good job with it. It sure looks WAY more long-winded than your version. Also, I don't care for the WHILE loop in my stored procedure, but I have to somehow retrieve the IDENTITY values; nonetheless, it at least seems to to work. First, I defined this table for the mock-up:

create table dbo.jHierMenu
( ID integer identity (21, 1)
constraint pk_jHierMenu primary key,
ParentID integer null,
MenuID integer null,
[Name] varchar(20) not null
)
go

create index jHierMenu_Menu_ndx
on dbo.jHierMenu (MenuID, ParentID, ID, [Name])
go

insert into dbo.jHierMenu values (0, 1, 'sun')
insert into dbo.jHierMenu values (21, 1, 'mars')
insert into dbo.jHierMenu values (22, 1, 'charon')
insert into dbo.jHierMenu values (22, 1, 'flubbie')
insert into dbo.jHierMenu values (23, 1, 'blub')
select * from dbo.jHierMenu

I then created a function that I could use as a building block:

create function dbo.relativeMenuHierarchy
( @.pm_MenuID integer
)
returns @.menuHierarchy table
( rid integer identity primary key,
ParentRid integer null,
ID integer not null,
ParentID integer null,
MenuID integer not null,
[Name] varchar (20) null,
hierLevel integer not null,
unique (hierLevel, rid)
)
as
begin

if ( select count(*) from dbo.jHierMenu
where menuId = @.pm_menuID
) < 1
return;

;with menuHierCTE
as
(
select id,
ParentID,
MenuID,
[Name],
0 as hierLevel
from dbo.jHierMenu
where MenuID = @.pm_menuID
and ParentID = 0
union all
select a.ID,
a.ParentID,
a.MenuID,
a.[Name],
cte.hierLevel + 1
from menuHierCTE as cte
inner join dbo.jHierMenu a
on a.menuId = @.pm_menuID
and a.parentID = cte.id
)
insert into @.menuHierarchy
select case when parentId = 0 then 0 else null end,
id,
parentId,
menuId,
[Name],
hierLevel
from menuHierCTE

declare @.secondHierarchy table
( rid integer primary key,
ParentRid integer null,
ID integer not null,
ParentID integer null,
MenuID integer not null,
[Name] varchar (20) null,
hierLevel integer not null,
unique (hierLevel, rid)
)

insert into @.secondHierarchy
select * from @.menuHierarchy

update @.menuHierarchy
set ParentRid = a.rid
from @.secondHierarchy a
inner join @.menuHierarchy b
on a.hierLevel = b.hierLevel - 1
and a.id = b.parentId
inner join small_iterator (nolock) i
on iter = b.hierLevel
and iter <= ( select max(hierLevel)
from @.secondHierarchy
)

return

end

go

select rid,
parentRid,
id,
parentId,
menuId,
[name],
hierLevel
from relativeMenuHierarchy (1);

-- rid parentRid id parentId menuId name hierLevel
-- -- -- --
-- 1 0 21 0 1 sun 0
-- 2 1 22 21 1 mars 1
-- 3 2 23 22 1 charon 2
-- 4 2 24 22 1 flubbie 2
-- 5 3 25 23 1 blub 3

Once I had the function working, I assembled a stored procedure to perform the actual work:

create procedure dbo.replicateMenu
( @.pm_oldMenuId integer,
@.pm_newMenuId integer
)
as

if exists
( select 0 from jHierMenu
where menuId = @.pm_newMenuId
)
begin
raiserror ('Menu ID ''%d'' is NOT a new menu id!', 0, 1, @.pm_newMenuID)
return 21001
end

declare @.menuHierarchy table
( rid integer primary key,
ParentRid integer null,
ID integer null,
ParentID integer null,
MenuID integer not null,
[Name] varchar (20) null,
hierLevel integer not null,
unique (hierLevel, rid)
)

insert into @.menuHierarchy
select rid,
parentRid,
null as ID,
null as ParentId,
menuId,
[name],
hierLevel
from dbo.relativeMenuHierarchy (@.pm_oldMenuID)

declare @.nextRid integer
declare @.lastId integer
declare @.maxRid integer

set @.maxRid = ( select max(rid) from @.menuHierarchy )

if @.maxRid = 0
return 0

insert into dbo.jHierMenu
select 0 as parentId,
@.pm_newMenuId as [MenuId],
[Name]
from @.menuHierarchy
where rid = 1
set @.lastId = scope_identity()

update @.menuHierarchy
set id = @.lastId,
parentId = 0
where rid = 1

set @.nextRid = 1
while @.nextRid < @.maxRid
begin

set @.nextRid = @.nextRid + 1

insert into dbo.jHierMenu
select p.id as ParentId,
@.pm_newMenuId as [MenuId],
a.[name]
from @.menuHierarchy a
inner join @.menuHierarchy p
on a.parentRid = p.rid
where a.rid = @.nextRid

set @.lastId = scope_identity()

update @.menuHierarchy
set id = @.lastId,
parentId = h.parentId
from @.menuHierarchy a
inner join dbo.jHierMenu h
on h.id = @.lastId
and a.rid = @.nextRid

end

return 0

go

exec replicateMenu 1, 101

select * from jHierMenu

-- ID ParentID MenuID Name
-- -- -- --
-- 21 0 1 sun
-- 22 21 1 mars
-- 23 22 1 charon
-- 24 22 1 flubbie
-- 25 23 1 blub

-- 65 0 101 sun
-- 66 65 101 mars
-- 67 66 101 charon
-- 68 66 101 flubbie
-- 69 67 101 blub

delete from jHierMenu where menuId = 101

I will try this out with a 100 and 200 row test. I'm kinda fried right now and I must step back for at least a little while.

|||

I tried the stored procedure with a 100-row menu and I feel better about all of this -- especially the function. I was able to use the function compare the two menus and verify that the replication had worked correctly. This also means that it would be relatively easy to create a "compareMenu" stored procedure or function. I started by truncating the mock-up table and inserting 100 random rows:

truncate table dbo.jHierMenu
go

set identity_insert tempdb.dbo.jHierMenu ON

insert into dbo.jHierMenu
( id,
parentId,
menuId,
name
)
select 1 as id,
0 as parentId,
1 as menuId,
'Name 1' as [Name]

insert into dbo.jHierMenu
( id,
parentId,
menuId,
name
)
select iter as id,
floor(1 + (cast (iter as float) - 1.99999) * dbo.randValue(iter))
as parentId,
1 as menuId,
'Name ' + convert (varchar (5), iter) as [Name]
from small_iterator
where iter > 1
and iter <= 100

go

set identity_insert tempdb.dbo.jHierMenu Off

go

select * from dbo.jHierMenu order by id

-- ID ParentID MenuID Name
-- -- --
-- 1 0 1 Name 1
-- 2 1 1 Name 2
-- 3 1 1 Name 3
-- 4 1 1 Name 4
-- 5 3 1 Name 5
-- 6 4 1 Name 6
-- 7 5 1 Name 7
-- ...
-- 99 84 1 Name 99
-- 100 12 1 Name 100

I then ran the "replicateMenu" stored procedure and then ran the query that follows to verify that the new menu was identical in structure to the old menu:

set nocount on
--delete from dbo.jHierMenu where menuId = 101
exec replicateMenu 1, 101

-- -
-- Uncomment the AND statement if you want to display
-- only the exceptions.
-- -
select a.rid,
a.[name],
b.rid,
b.[name]
from relativeMenuHierarchy (1) a
inner join relativeMenuHierarchy (101) b
on a.rid = b.rid
-- and a.name <> b.name

-- rid name rid name
-- - -
-- 1 Name 1 1 Name 1
-- 2 Name 2 2 Name 2
-- 3 Name 3 3 Name 3
-- ...
-- 99 Name 84 99 Name 84
-- 100 Name 99 100 Name 99

|||

Hi

I had read through your examples and it seems much cleaner and efficient than mine (a compliment). I have tested out my stored procedure several times and it does work (at least I think it does). However I am going try yours as well and test it out again.

I also realise supposing someone comes a long and deletes an item and the rest of items becomes obselete if you like as the first item is missing so the uplevel gets skewed up. So if they copy that menu with the missing item and the rest of the items with obselete uplevel, theoretically, the copied menu would have the rest of the items with the old obselete uplevelID - would that break the stored procedure coding or mess the structure when copying a menu? I noticed it does happen with my old stored proc - could that easily be remedied?

I really appreciate the time and effort you have put in this stored procedure and my gratitude of thanks goes towards you. I found what you have done with the stored procedure and testing has been a big lesson for me and hopefully I will improve my 'thinking out of the box'.

Thanks again, will keep you posted to let you know how I get on with the testing on this.