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.

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.

Help with CURRENT DATE Query

Hello, I have a table that lists my bank transactions. I have a uniqueid in
the first field and several other fields...one of which is DATE... now, I
want to run a query where my view is based upon the DATE. however I want to
only show transactions that are the same date as the system date (this is to
see how much work my employees have done). Please see my example below, this
code works...
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE ([date] = '9/9/2007')
However, where it has 9/9/2007, I want it to be TODAYS Date
Thanks for your help in advance!WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
"SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> Hello, I have a table that lists my bank transactions. I have a uniqueid
> in
> the first field and several other fields...one of which is DATE... now,
> I
> want to run a query where my view is based upon the DATE. however I want
> to
> only show transactions that are the same date as the system date (this is
> to
> see how much work my employees have done). Please see my example below,
> this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Try:
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE
[date] >= convert(char(8), getdate(), 112) and
and [date] < dateadd(day, 1, convert(char(8), getdate(), 112))
go
AMB
"SQL Brad" wrote:
> Hello, I have a table that lists my bank transactions. I have a uniqueid in
> the first field and several other fields...one of which is DATE... now, I
> want to run a query where my view is based upon the DATE. however I want to
> only show transactions that are the same date as the system date (this is to
> see how much work my employees have done). Please see my example below, this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Aaron...thanks for your help, it worked perfectly!! I also changed the 0 to
a 1 and it went to yesterday....very much appreciated!
"Aaron Bertrand [SQL Server MVP]" wrote:
> WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
>
> "SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
> news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> > Hello, I have a table that lists my bank transactions. I have a uniqueid
> > in
> > the first field and several other fields...one of which is DATE... now,
> > I
> > want to run a query where my view is based upon the DATE. however I want
> > to
> > only show transactions that are the same date as the system date (this is
> > to
> > see how much work my employees have done). Please see my example below,
> > this
> > code works...
> >
> > SELECT transid, [date], description, amt, taxamt
> > FROM dbo.taxtransactions
> > WHERE ([date] = '9/9/2007')
> >
> > However, where it has 9/9/2007, I want it to be TODAYS Date
> >
> > Thanks for your help in advance!
> >
>
>sql

Help with cupe partitioning/processing

Hello guys

I have an issue with one of our cubes which i hoped someone could help me with... The problem is a historical cube which is currently growing quite drastically, and includes daily history from 2004 until today. The cube is just processed full every single day, and this means processing 80+ million rows every day, when actually most of the data is not changed at all. I decided I want to split my cube in data for 2004, 2005, 2006, 2007 and then the current month. Once the current month is over then i would merge it with the year to date partition and create a new partition for the new month.

My problem is how to implement the processing in a simple way? Right now the processing is done in SSIS by just processing full the db, so I guessed i have to make 2 tasks, one to process full all the dimensions first , and one to process full the cubes afterwards in which i process for this specific cube only the latest partition. But this means at the end of the month I would have to change the package manually, since i need to merge some partitions and then process full only the newest partition again...

Is there any simpler way of doing this? Is my approach correct in the partitioning way and processing full of only the newest partition, except at the end of the month when i would need to merge and process the ytd partition?

I think you are headed down the right path, but I'd suggest you read through the partitioning white paper from Project REAL. It provides some good examples and thoughts related to partitioning within SSAS (and within SQL Server) and the ETL implementation for the solution there. I think you could probably use some of the same ideas and design patterns to solve your problem...

The partitioning white paper can be found at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realpart.mspx

Good luck!

Dave Fackler

Help with cube measures

I'm relatively new to AS but have managed to get a data mart, dimensions and
cubes up and running in AS 2005 (SP1).

I'm having difficulty trying to do something seemingly easy with respect to
measures.

One of my dimensions is Accounts. I have about 7 additional dimensions
including Time.
I have 5 measures. One of these measures is heavily used most all of the
time.
I need to filter this measure by the Account dimension to include only
measures >= <defined_number>.
The other measures should reflect this change also, but I also need to
analyze other dimensions with or without the Accounts dimension in the
crosstab.
I need this to be put in the aggregations, not a temporary dynamic
calculation.

Aside from doing this at the database level, is their a way to do this in AS
either with a calculated member, named calculation or other?
I've been trying for a few days but just can't seem to get it.

Please, any help would be great.

-Troy

Let me see if I can go at this a different way.

If I try to create a calculated member as follows in the VS 2005 designer:

Name: Filtered Volumes

Parent Hierarchy: MEASURES

Expression: [Measures].[Export TEUS] >= 500.00

Format String: "#,#.0"

Visible: True

Non-Empty Behavior: Export TEUS

After processing the cube, the values for any and all cells is -1.0

Maybe if someone could explain the why to me, it could start the wheels turning more and perhaps help anyone viewing theis post.

Thanks,

-Troy

|||

What's happening with our calculated member is that the Expression "[Measures].[Export TEUS] >= 500.00" is evaulated and returns True which is being displayed as a numberic value and True is -1.0 while False is 0 in numeric terms. (Actually any non-zero value is consider True, but True generally converts to 1.0 or -1.0 depending on the system.)

I don't quite understand what you're trying to achieve, but what you might want to try doing is using something like if([Measures].[Export TEUS] >= 500.00, [Measures].[Export TEUS], Null). (If you are using Analysis Services 2000 both the second and third parameters to the iff functions will need to be of the same type so you'll need to change the null to something like 0.)

Another possibility, depending on what you are trying to do, is to use a the Filter() mdx function in an MDX query.

|||

Matt,

Your suggestion is exactly what I am trying to do. Thank you.

The expression is evaluating as I would have hoped. However, the measure is displaying NULL cells, which is one thing I don't want.

Could you suggest a way to prevent this? I have looked at NONEMPTY, but because of my lack of experience writing MDX I've had no luck.

Again, thanks for the help!!

|||

Here's an example of the use of non empty in an MDX Query in order to filter rows containing only nulls:

First the query that returns rows with null:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
[Customer].[City].members on 1

Then the query with non empty added to remove the null rows:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
non empty [Customer].[City].members on 1

Help with Crystal Reports

Hi, ,am fairly new to crystal reports......pretty much new...

I have a simple report that is to be done......

I need to generate a report....with columns...emp_num,name,ssn

I pull up these columns thru sql server 2000 from Employee table...

The tricky part is here....

The report has to be in such a way that...

1.)First it prompts asking if u have ssn number with u ,if yes enter the ssn no., display the name and emp_num corresponding to that,

2.)IF NO...prompt the user asking for emp_num, if yes ,enter the emp_num ,display the name and ssn corresponding to that...

I've written a stored procedure for this, but this is taking only one value..while connecting to the crystal....

Plz help me on this.......I dont think you can prompt user this way
Are you calling the report from any front end?

Help with Cross-tab queries

How would you write the SQL statement to do a cross-tab queries.

My Table
DCNID-unique ID
AuditID-FK to the main table
DCN-int
Error Type (combo box with values tooth, date, provider, etc)
Line ID (combo box with values 1-40)
Adjustment Code-text

I would like my report to look like this.
DCN
LineID Tooth Date Provider
1 1 1
2 1
3
4
5
..
40

Thank you so much!

CristyYou could have done some research, this question has been asked and aswered thousands of times, here are some solutions from asktom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:rolleyes:|||How would you write the SQL statement to do a cross-tab queries.that depends -- what database system are you using?

Help with crosstab (was "Query Help Needed!")

Hey,

i have a table which has the foll data:

employeecode Amount AmountDescription
1 100 x
2 200 y
3 150 x
4 300 z

now i need to fetch this data such that i can display the output as :

empcode x y z
1 100
2 200
3 150
4 300

any suggestions???

platform: SQL Server 2000

thanx!sorry, no suggestions.... unless we know

1) why do u need it (the practical scenario)
2) how do u ensure that the string "x" fits a column name
3) how do u ensure that the number of columns is within the max limit for select/table
4) what would be the value against row 1, col x of the output|||I think this will do what you want.

SELECT empcode
, Max(CASE WHEN AmountDescription = 'x' THEN amount ELSE 0 END) As 'x'
, Max(CASE WHEN AmountDescription = 'y' THEN amount ELSE 0 END) As 'y'
, Max(CASE WHEN AmountDescription = 'z' THEN amount ELSE 0 END) As 'z'
FROM MyTable
GROUP BY empcode|||no, george, that will put 0s where they didn't exist in the data|||just tweak that last response to use null values instead of 0's

select empcode, case when AmountDescription = 'x' then amount else null end as X,
Case when AmountDescription = 'y' then amount else null end as Y,
Case when AmountDescription = 'z' then amount else null end as Z
from MyTable
Group by Empcode|||but don't lose your MAXes ;)|||Max means that it becomes aggregated and doesn't have to be used in the GROUP BY clause ;)|||i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
SELECT empcode
, Amount
, x = NULL
, y = NULL
, z = NULL
FROM MyTable


:)|||<sigh />

pootle, please forgive the lack of proper spacing in the original post

this is what was intended (and you can see this if you open up the original post in Edit) --

empcode x y z
1 100
2 200
3 150
4 300sql

Help with crosstab

I need to create a crosstab report. I have never done it before and need some help with it. I would appreciate any help and guidance.

I have a report that has the grouping as below

Region
Sector
Interval
Area
Crew

I need to add a crosstab report in the interval group header that will summarize the data by area and crew. I go to Insert crosstab and select Area as my column heading and Crew as my rows. Then I want to use @.PercentComplete formula as the summarized field but I don't see it in available fields and even if I create new formula from within the crosstab window I still don't see it. Any suggestions as to why I am not seeing this formula. Formula is as below

If {@.ScheduledTasks} = 0 then
"N/S"
Else If {@.TotalTasks} = 0 then
"N/D"
Else
cStr( (sum({@.TimelyCOmplete},{@.Group_CrewUnit})/(sum({@.TimelyCOmplete},{@.Group_CrewUnit}) + sum({@.MissedTasks},{@.Group_CrewUnit}) + sum({@.LateComplete},{@.Group_CrewUnit}))) * 100, 2)

Sample data for Crosstab is below

Crew Area1 Area2 Area3 %Complete
AAA 100 100 N/S 97.61
BBB 100 N/S N/S 100.00
CCC 0.00 100 N/S 81.25
DDD N/S 100 N/S 100
EEE N/S 96.87 N/D N/D
-- --- ---
%Complete 98.28 100.00 N/Danyone?

Help with cross products and functions

Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:

> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>

Help with creating xml shema

can somebody, please help me with my problem. I want to create xml shema to
use it with bulkload in vb.net application. I read about it and I must have
xsd, and xml file. So, if anybody can create for me that files, I would be
very thankful. These are my example tables.
T1
ID name city
1 john NY
2 mark NY
3 eric LA
T2
ID car price color
1 mazda 10000 black
1 honda 12000 blue
2 toyota 9000 green
3 audi 4000 black
1 ford 7800 red
please, please...if someone can help me.
thanks!
What does your XML look like?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bernard" <bernard@.form.hr> wrote in message
news:clm1td$hbv$1@.ls219.htnet.hr...
> can somebody, please help me with my problem. I want to create xml shema
> to use it with bulkload in vb.net application. I read about it and I must
> have xsd, and xml file. So, if anybody can create for me that files, I
> would be very thankful. These are my example tables.
> T1
> --
> ID name city
> 1 john NY
> 2 mark NY
> 3 eric LA
> --
> T2
> --
> ID car price color
> 1 mazda 10000 black
> 1 honda 12000 blue
> 2 toyota 9000 green
> 3 audi 4000 black
> 1 ford 7800 red
> --
> please, please...if someone can help me.
> thanks!
>
|||Bernard:
It looks like you already have data in those tables, so the easy thing to might to be issue a query on them like this
select * from t1 for xml auto
Then save those results out to file. You'll have to edit out the extra line feeds and add the XML prolog to make that a valid XML file.
Once you have that, and assuming you have the .NET SDK install, you should be able to use XSD.EXE to generate usable schmea.
If you already have the XML files you want to import, you can just feed them to XSD.EXE one at a time to get a matching W3C schema.
> Irwon: What does your XML look like?
If I'm reading this right, he doesn't have any.
Thanks,
Kent Tegels
MVP - SQL Server
The SSX FAQ & Blog:
http://tinyurl.com/6r4gb
Looking for XM, the GUI for SSX? See both:
http://tinyurl.com/4dfee and http://tinyurl.com/53hts
My Blog:
http://www.tegels.org/

Help with creating SQL Statement to get data from single table...

Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:

I have the following table of data

__User___Votes___Month

__A_______14______2
__A_______12______3
__A_______17______4
__A_______11______5

__B_______19______2
__B_______12______3
__B_______15______4

I want to beable to pull out the total number of votes a user has had over a period of months.

eg Total up each users users votes for months 4 and 5

that would give:

__User____TotalVotes

___A________28
___B________15

An added complecation is that user B does not have any data for month 5

Any help or pointers would be fanstatic

Many thanks

select sum(Votes)
from table
where month in (4, 5)
group by User|||Of cource! thank you.|||rather,
select
[user], sum(Votes) as TotalVotes
from
yourTable
where
month in (4, 5)
group
by [User]

Help with CREATE TRIGGER syntax

Any help would be appreciated. What's wrong with the following syntax?
CREATE TRIGGER tr_CMR_Client_Status_Confirm
ON [CMR Client Numbers]
FOR INSERT, UPDATE
AS
IF UPDATE [Current Status]
CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
Client Number ID] ELSE [Status Flag] = NULL END1. IF UPDATE should have parentheses around the column name - note: if
any row's [Current Status] is updated, this will evaluate to true.
2. not a syntax error, but for clarity, should put a BEGIN..END after the IF
3. [biggest problem] There's no DML statement to do anything, just a
CASE expression...
4. ... which is incorrectly written
Look up UPDATE, CREATE TRIGGER and CASE in BOL
It is quite unclear from the code given what table the [Status Flag]
column belongs to, otherwise I could give an example of possible correct
trigger code.
Please provide DDL of the table.
mike wrote:

>Any help would be appreciated. What's wrong with the following syntax?
>
>CREATE TRIGGER tr_CMR_Client_Status_Confirm
>ON [CMR Client Numbers]
>FOR INSERT, UPDATE
> AS
> IF UPDATE [Current Status]
> CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
>Client Number ID] ELSE [Status Flag] = NULL END
>
>
>
>|||What are you trying to do? It looks like you want to duplicate a value in
another column of your table, which isn't a good thing to do.
Please post DDL and sample data.
David Portas
SQL Server MVP
--sql

Help with counting query

This is a counting issue following an inner join.
I have two tables (simplifying it for clarity)
Offering Table: OfferingID, year
Registration Table: RegistrationID, OfferingID, Registration_Status
-- Start Table Definitions
--
CREATE TABLE [dbo].[Test1] (
[OfferingID] [int] NOT NULL ,
[Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[RegistrationID] [int]NOT NULL ,
[OfferingID] [int] NOT NULL ,
[Registration_Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Test1 Values (1,2005)
INSERT INTO Test1 Values (2,2005)
INSERT INTO Test1 Values (3,2006)
INSERT INTO Test2 Values (1,1,1)
INSERT INTO Test2 Values (2,1,1)
INSERT INTO Test2 Values (3,1,2)
INSERT INTO Test2 Values (4,1,2)
INSERT INTO Test2 Values (5,1,2)
INSERT INTO Test2 Values (6,2,1)
INSERT INTO Test2 Values (7,2,1)
INSERT INTO Test2 Values (8,3,1)
INSERT INTO Test2 Values (9,3,2)
-- END Table Definitions
--
Now I want to do some counting, basically I want to count the number of
offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
I also want to count the total number of registrations of
Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 and
4
respectivly)
This issue is I want them all in a single record. I came up with this SQL
Query
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Returns:
Attended Cancelled 2005 2006
-- -- -- --
5 4 7 2
It returns the correct Registration Status counts, but not the correct
number of offerings per year because the records are counted over and over
due to the join. What I really want is to count only distinct records in
test1.
Attended Cancelled 2005 2006
-- -- -- --
5 4 2 1
Thanks!You need to use COUNT(distinct).
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
THEN t1.OfferingID ELSE NULL END) AS [2005],
COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
THEN t1.OfferingID ELSE NULL END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Note that the ELSE NULL is optional; if there is no ELSE clause the
CASE defaults to NULL when not matched. But it is a bit clearer with
the explicit assignment. COUNT does not count NULLs.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 11:05:02 -0700, Ramez
<Ramez@.discussions.microsoft.com> wrote:

>Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
>Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
>Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005]
,
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
>FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID|||Actually you don't need a join.
try this
select * from
(Select SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled from test2) as t2,
(select SUM(CASE WHEN Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
from test1) t1
Hope this helps.
--
"Ramez" wrote:

> This is a counting issue following an inner join.
> I have two tables (simplifying it for clarity)
> Offering Table: OfferingID, year
> Registration Table: RegistrationID, OfferingID, Registration_Status
> -- Start Table Definitions
> --
> CREATE TABLE [dbo].[Test1] (
> [OfferingID] [int] NOT NULL ,
> [Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[test2] (
> [RegistrationID] [int]NOT NULL ,
> [OfferingID] [int] NOT NULL ,
> [Registration_Status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Test1 Values (1,2005)
> INSERT INTO Test1 Values (2,2005)
> INSERT INTO Test1 Values (3,2006)
> INSERT INTO Test2 Values (1,1,1)
> INSERT INTO Test2 Values (2,1,1)
> INSERT INTO Test2 Values (3,1,2)
> INSERT INTO Test2 Values (4,1,2)
> INSERT INTO Test2 Values (5,1,2)
> INSERT INTO Test2 Values (6,2,1)
> INSERT INTO Test2 Values (7,2,1)
> INSERT INTO Test2 Values (8,3,1)
> INSERT INTO Test2 Values (9,3,2)
> -- END Table Definitions
> --
> Now I want to do some counting, basically I want to count the number of
> offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
> I also want to count the total number of registrations of
> Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 an
d 4
> respectivly)
> This issue is I want them all in a single record. I came up with this SQL
> Query
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005
],
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006
]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Returns:
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 7 2
> It returns the correct Registration Status counts, but not the correct
> number of offerings per year because the records are counted over and over
> due to the join. What I really want is to count only distinct records in
> test1.
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 2 1
> Thanks!|||Hello, Ramez
To get the desired result, you can simply use something like this:
SELECT
(SELECT COUNT(*) FROM test2 WHERE Registration_status=1) AS Attended,
(SELECT COUNT(*) FROM test2 WHERE Registration_status=2) AS Cancelled,
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2005) AS [2005],
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2006) AS [2006]
If you really want to use a join (but I don't see any good reason for
this), you can use the following query:
SELECT
SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS Cancelled,
COUNT(DISTINCT CASE WHEN Offering_Year = '2005' THEN t1.OfferingID
END) AS [2005],
COUNT(DISTINCT CASE WHEN Offering_Year = '2006' THEN t1.OfferingID
END) AS [2006]
FROM test1 t1 INNER JOIN test2 t2 ON t1.OfferingId = t2.OfferingID
However, this comes at the expense of a warning: "Warning: Null value
is eliminated by an aggregate or other SET operation."; the warning can
be eliminated by using SET ANSI_WARNINGS OFF, but this is not
recommended (one of the reasons is because ANSI_WARNINGS is required to
be ON for using indexes on computed columns and indexed views).
Razvan|||Roy,
Do we need the join and case and distinct ops for this scenario. I
seriously doubt it.
--
"Roy Harvey" wrote:

> You need to use COUNT(distinct).
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
> THEN t1.OfferingID ELSE NULL END) AS [2005],
> COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
> THEN t1.OfferingID ELSE NULL END) AS [2006]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Note that the ELSE NULL is optional; if there is no ELSE clause the
> CASE defaults to NULL when not matched. But it is a bit clearer with
> the explicit assignment. COUNT does not count NULLs.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 11:05:02 -0700, Ramez
> <Ramez@.discussions.microsoft.com> wrote:
>
>

Help with counting Query

I am trying to find a query to count rows with multiple conditions.
I have files which are suffixed with -R and then a number from 1 to 3 and
then two digits 00-14.
the field is char
i.e.:
-R101
-R102
-R114
-R201
-R302
I need to determine how many 101, how many 102, etc through 114 and then the
same for
the 201-214 series and then
the 301-314 series.
I will be sending the query from Visual Basic using ADODC so I am not sure
how the data will be returned. If it was written to a temp table that would
be great.
Thanks,
Bob Hiller
Lifts for the Disabled LLCselect right(columnName, 3), count(columnName)
from tableName
group by right(columnName, 3)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>I am trying to find a query to count rows with multiple conditions.
> I have files which are suffixed with -R and then a number from 1 to 3 and
> then two digits 00-14.
> the field is char
> i.e.:
> -R101
> -R102
> -R114
> -R201
> -R302
> I need to determine how many 101, how many 102, etc through 114 and then
> the same for
> the 201-214 series and then
> the 301-314 series.
> I will be sending the query from Visual Basic using ADODC so I am not sure
> how the data will be returned. If it was written to a temp table that
> would be great.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
>|||Thank you,
That worked great but I did not ask the full question. Maybe you can help
again.
if these strings are in a column
12345678-R101
12345678-R201
12345678-R301
98564512-R112
18752381-R101
18752381-R201
18752381-R110
18752381-R111
18752381-R211
If there is a -R2 there will always be a -R1. Likewise if there is a -R3
there will always be a -R2.
In the above example I need to return
12345678-R301
98564512-R112
18752381-R201
18752381-R110
18752381-R211
I hope I have explained this well enough.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
> select right(columnName, 3), count(columnName)
> from tableName
> group by right(columnName, 3)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>|||select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Thank you,
> That worked great but I did not ask the full question. Maybe you can help
> again.
> if these strings are in a column
> 12345678-R101
> 12345678-R201
> 12345678-R301
> 98564512-R112
> 18752381-R101
> 18752381-R201
> 18752381-R110
> 18752381-R111
> 18752381-R211
> If there is a -R2 there will always be a -R1. Likewise if there is a -R3
> there will always be a -R2.
> In the above example I need to return
> 12345678-R301
> 98564512-R112
> 18752381-R201
> 18752381-R110
> 18752381-R211
> I hope I have explained this well enough.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
>|||OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1),
MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(substring(columnName,charindex('-R',columnName)+2,10))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results.
For one thing it is returning 2 expressions. I would expect only one.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample:
12345678-R301
98564512-R112
18752381-R211
I am missing:
18752381-R201
18752381-R110
Think of the first number after the -R as a counter for the last 2 numbers
that represent a group.
When these are in the table
18752381-R101 01 is the group and 1 is the counter
18752381-R201 01 is the group and 2 is the counter
we want to return the largest counter for group 01 for the number to the
left of -R
return (18752381-R201)
18752381-R110 10 is the group and 1 is the counter
return (18752381-R110) it is the only group 10 for the number to the left
of -R
18752381-R111 11 is the group and 1 is the counter
18752381-R211 11 is the group and 2 is the counter
return (18752381-R211)
Thank in advance,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||select max(a)
from(select '12345678-R101'
union all select '12345678-R201'
union all select '12345678-R301'
union all select '98564512-R112'
union all select '18752381-R101'
union all select '18752381-R201'
union all select '18752381-R110'
union all select '18752381-R111'
union all select '18752381-R211')x(a)
group by left(a,8),right(a,2)
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Ok, I can live with the 2 returned expressions, they will work fine.
> Here is what is returned when I run the sample:
> 12345678-R301
> 98564512-R112
> 18752381-R211
> I am missing:
> 18752381-R201
> 18752381-R110
> Think of the first number after the -R as a counter for the last 2 numbers
> that represent a group.
> When these are in the table
> 18752381-R101 01 is the group and 1 is the counter
> 18752381-R201 01 is the group and 2 is the counter
> we want to return the largest counter for group 01 for the number to the
> left of -R
> return (18752381-R201)
> 18752381-R110 10 is the group and 1 is the counter
> return (18752381-R110) it is the only group 10 for the number to the
> left of -R
> 18752381-R111 11 is the group and 1 is the counter
> 18752381-R211 11 is the group and 2 is the counter
> return (18752381-R211)
> Thank in advance,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
>|||oj,
Thanks for the suggestion but the values where just given as samples. There
are thousands of rows that I have to search through. I don't think this
approach will work.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"oj" <nospam_ojngo@.home.com> wrote in message
news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
> select max(a)
> from(select '12345678-R101'
> union all select '12345678-R201'
> union all select '12345678-R301'
> union all select '98564512-R112'
> union all select '18752381-R101'
> union all select '18752381-R201'
> union all select '18752381-R110'
> union all select '18752381-R111'
> union all select '18752381-R211')x(a)
> group by left(a,8),right(a,2)
>
> --
> -oj
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
>|||Bob,
You need to adapt the technique to your data.
e.g.
select max(your_col)
from tb
group by left(your_col,8),right(your_col,2)
If it does not give you the desired result, you'd want to post ddl + sample
data + expected result here so we can help.
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uRMPBI8TGHA.5836@.TK2MSFTNGP10.phx.gbl...
> oj,
> Thanks for the suggestion but the values where just given as samples.
> There are thousands of rows that I have to search through. I don't think
> this approach will work.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
>