NET_ADDRESS in the master.dbo.sysprocesses table

Does anyone knows how the field NET_ADDRESS in the master.dbo.sysprocesses table is encoded ?
May I extract the IP address or MAC address from that field ?

Just ran it on my own computer. NET_ADDRESS is the MAC address of my network card. The only way I know to correlate this to an IP address would be to somehow get it from the DHCP server.

As I understand it, the only computer name/ip address in SQL Server is HOSTNAME and that's voluntarily provided by the client and the client can make up any name it wants.

|||I had the same thought as you but my sql server reports strange values for some PC:
B7D36284FF31 MICHELENEW

CBB6ACE3C807 CARLOP
are clearly strange values, CBB6ACE3C807 is My PC and my MAC is 00-0E-A6-1D-BE-29

do you have any idea about ?

here the complete report from master.dbo.sysprocesses table

NET_ADDRESS HOSTNAME
-

00096B67790E SERVER2003
000EA61DBBCE NICOLA
000EA61DBD4D EMANUELEXP
000EA61DBEFF MICHELENEW
000EA61DC006 ENRICO02
000EA6C49C83 BAMBINO999
000EA6C71205 DOMEXP
000EA6D0B069 MICHELA2
000FEA8184A8 ACQUARIO7
000FEA855CAC ACQUARIO8
00105AB3BBB1 MAURIZIO
00112FDCA644 FF
00142A98601E ROBERTO
00304841C27D ETEREW7
0030485449F5 MASSIMOXP
003048584C64 ETEREW10
0060082CE6D6 LAURAXP2
00D0B7E28366 ACQUARIO9
B7D36284FF31 MICHELENEW
CBB6ACE3C807 CARLOP|||

If you happen to be on SQL Server 2005, you can find out a lot more with;

select * from sys.dm_exec_connections

/Kenneth

|||I'm on 2000, but anyway thanks for the info, I will keep it in mind for the future. Thanks.|||1) If the net_Address starts with 00 the program which opened the connection is running in a simple environment or is a regular connection. If you have letter is because the connection was opened from SQl Analyzer or a server which shared sessions. Like using Terminal server or Remote desktop connection.

000EA61DBEFF MICHELENEW
B7D36284FF31 MICHELENEW

The "good" MAC address is 00-0E-...

2) How to map back the net_Address (MAC) to IP.
In any domain controller from the DOS command type:

ARP -a

look for your MAC address in the list. I know this is a pain but the point is that U can get the info from DHCP. Maybe a script could help. Remembert ARP is a cache list but it helps.

3) From SQL Server type
select net_address,loginame,hostname nt_username, * from master..sysprocesses order by net_address
It can help you to figure it our who is using the connection.sql

Nesting a SP inside a Query

This does not work:
select * from (exec sp_lock) as ex
I want to process sp_lock in code as a table. How can I do this?
SeanSean
Create a table that will contain all columns from sp_lock stored procedure
and the perform
insert into #t exec sp_lock
select * from #t
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:ePz9bgLUFHA.628@.tk2msftngp13.phx.gbl...
> This does not work:
> select * from (exec sp_lock) as ex
> I want to process sp_lock in code as a table. How can I do this?
> Sean
>|||Thanks but...
insert into #t exec sp_lock
gives...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#t'.|||ignore than last message... I'm doing 10 things at once
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:OD$E9WMUFHA.2540@.tk2msftngp13.phx.gbl...
> Thanks but...
> insert into #t exec sp_lock
> gives...
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#t'.
>|||You have to create the temp table first. Try this:
Create Table #Locks
(
spid int
, dbid int
, objId int
, indid int
, type nvarchar(10)
, resource ntext
, mode nvarchar(2)
, status nvarchar(25)
)
Insert #Locks
Exec sp_lock
Granted, I'm using the Force in determining the data types and sizes for the
various columns.
Thomas
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:ePz9bgLUFHA.628@.tk2msftngp13.phx.gbl...
> This does not work:
> select * from (exec sp_lock) as ex
> I want to process sp_lock in code as a table. How can I do this?
> Sean
>sql

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
Venkat
Have you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegr oups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>
|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.
|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.
|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>
|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat
|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat
|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat
sql

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
> >I tried running the views seperately and it works . As mentioned the
> > job also does not hang always .. it hangs at times , but the trend is
> > unpredictable.
> >
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
> >I tried running the views seperately and it works . As mentioned the
> > job also does not hang always .. it hangs at times , but the trend is
> > unpredictable.
> >
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat

Nested Tables

Hi

I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?

Any other way tath i could repeat 3 tables in some grouping method?

Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||

That's exactly what i did. Was hopping there was some other way not using SubReports.

Never the less it works fine with Sub-Reports , thank you for your time

Nested Tables

Using SSRS 2005, is there anyway to embed a table in a table and link the two together in a parent child relationship?

R

WHat do you want to achieve ? It sure can be accomplished by using the standard functionality.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Nested Tables

Hi

I have 3 tables each with a diferent dataset's, is there any way i can nest 2 of the tables inside one of the other table's group's?

Any other way tath i could repeat 3 tables in some grouping method?

Using different datasets inside one top-level table is not currently supported, so you cannot nest the two tables inside the other table. But you can create two subreports and put the two tables in the subreports respectively, then nest the subreports in the other table.|||

That's exactly what i did. Was hopping there was some other way not using SubReports.

Never the less it works fine with Sub-Reports , thank you for your time

Nested Tables

Hi can you insert nested tables in a table already exsisting in sql server and if so how would i go about do such a thing

Thanks in advance

Quote:

Originally Posted by Taftheman

Hi can you insert nested tables in a table already exsisting in sql server and if so how would i go about do such a thing

Thanks in advance

You can in SQL 2005. See http://msdn2.microsoft.com/en-us/library/ms175659.aspx. You could also insert an XML blob, but this would be more like nesting a row. I would not recomend nexting XML. It uses up too much space because you end up with non-normalized data and makes selects for values in the XML field very slow, at best.

That said, be careful. This is pretty advanced stuff and hard to support long term.

Nested Table Relations in SQLXML

Hi Folks

This question applies to SQL Server 2000 and SQLXML.

I have a table with columns as follows:

AccountID ~ ParentAccountID ~ AccountData

Sample data:

1 ~ NULL ~ This is a parent account

2 ~ 1 ~ This is a child account

As you can see, there is an inherant tree structure in the above data: ParentAccountID is a foreign key to Primary Key AccountID.

I wish to query these using a FOR XML and retrieve a document like the following:

<account>

<accountid>1</accountid>

<accountdata>I am a parent account</accountdata>

<account>

<accountid>1</accountid>

<accountdata>I am a child account</accountdata>

</account>

</account>

Is this possible and if so what would be the sql?

Many thanks

Iain A. Mcleod

In SQL Server 2000 this is not directly doable. You should just use a flat result (using FOR XML AUTO) and then use a mid-tier XSLT transform.

In SQL Server 2005, you can use nested user defined functions as described in http://msdn.microsoft.com/library/en-us/dnsql90/html/forxml2k5.asp

Best regards

|||

Many thanks for your reply Michael.

I had separately come to the same conclusion having begun to discover that sql server 2000's xml capabilities were a bit of a hacked add on. I ended up doing it by pulling out datasets and directly mapping the fields to my business objects.

Not great but at least I avoided the need to install sql xml libraries on our webserver... The less one has to ask of our systems people the better as far as I'm concerned :-)

Regards

Iain

Nested table concept in SQL Server

Hi all,

What is the equivalent for Oracle's nested table concept in SQL Server ?
Is there anything like TABLE( ) function to select from nested table as in Oracle ?

Eg in Oracle :

SELECT t.* FROM TABLE(nested_table_datatype) t;

( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )

Please give the equivalent for above...

Thanks,
Samthank $deity, there is no equivalent in sql server, for nested tables are evil|||MS-SQL is a relational database. Oracle is a database with an SQL-like command language. There is a significant difference, neither one is inherantly better or worse than the other. They aren't comparable.

No relational database can have anything like Oracle's nested tables. Nested tables implicitly violate the first normal form.

In a relational database like MS-SQL, you can do exactly the same thing as a nested table by using a foreign key relationship. Create a second table using the same schema you would use for an Oracle nested table, adding a "link" column. Include the value from the "link" column in the parent row, so that you can join the main table to the logically "nested" table.

-PatPsql

Nested Table

I have a table nested inside another table. Within the nesting, I want my
data to show me the detail, and not do a grouping. Unfortunately, the inner
table wants to keep doing an aggregate of the data, but I want to show the
detail. So I have a part number that has multiple customers being impacted
by supply limitations, I want to pull the part number with all of its data as
a single record, and then have a nested group that shows all the customer
name, like this:
AAAA
Acme Inc.
Acme Botanical
Acme Logistics
BBBB
Acme Inc.
Acme Environmental
When I add fields to the nested table, I keep having aggregate functions
appear, and if I don't use the aggregates, and specify scope, the data
element is acting like it is pulling from the parent table.
Thanks for any info.
ShaneI have a similar problem. I have a line of header information, then
multiple lines of detail, all coming from the same data source. RS
documentation says you can nest data regions, but I haven't found any
examples. I tried nesting groups and defined the header group as the parent
of the detail group. But, like Shane, I found that the RS insisted on
treating the detail lines as aggregates.
Is there example somewhere of a nested group that produces output in the
form that Shane shows below?
Thanks
Al
Shane Castle" <ShaneCastle@.discussions.microsoft.com> wrote in message
news:DB333B89-E614-48FC-943B-73163C9B735B@.microsoft.com...
> I have a table nested inside another table. Within the nesting, I want my
> data to show me the detail, and not do a grouping. Unfortunately, the
inner
> table wants to keep doing an aggregate of the data, but I want to show the
> detail. So I have a part number that has multiple customers being
impacted
> by supply limitations, I want to pull the part number with all of its data
as
> a single record, and then have a nested group that shows all the customer
> name, like this:
> AAAA
> Acme Inc.
> Acme Botanical
> Acme Logistics
> BBBB
> Acme Inc.
> Acme Environmental
> When I add fields to the nested table, I keep having aggregate functions
> appear, and if I don't use the aggregates, and specify scope, the data
> element is acting like it is pulling from the parent table.
> Thanks for any info.
> Shane|||My work around for this is to have your first report get all the
parts. You would add just one table. The first detail row you place
the part fields. In your second detail row place a subreport.
Make another report for companies. place only a table into this report
and have company fields in a single row. You might have to mess with
page border width stuff.
Use the part id or whatever and pass that into the subreport.
What will happen is that for each part. You'll print your part fields,
then it'll go to the second detail line and run the subreport which
gets all your companies for that part id.
Maybe not the best way to do this, but it's very simple, easy, and
clean.
Shane Castle <ShaneCastle@.discussions.microsoft.com> wrote in message news:<DB333B89-E614-48FC-943B-73163C9B735B@.microsoft.com>...
> I have a table nested inside another table. Within the nesting, I want my
> data to show me the detail, and not do a grouping. Unfortunately, the inner
> table wants to keep doing an aggregate of the data, but I want to show the
> detail. So I have a part number that has multiple customers being impacted
> by supply limitations, I want to pull the part number with all of its data as
> a single record, and then have a nested group that shows all the customer
> name, like this:
> AAAA
> Acme Inc.
> Acme Botanical
> Acme Logistics
> BBBB
> Acme Inc.
> Acme Environmental
> When I add fields to the nested table, I keep having aggregate functions
> appear, and if I don't use the aggregates, and specify scope, the data
> element is acting like it is pulling from the parent table.
> Thanks for any info.
> Shane

Nested Stored Procedure

Hi, I have two tables:
TABLE 1 Table 2
========== ==========
UID TeamName
TeamID <------> TeamID

and I am trying to obtain the value of TeamName via a nested stored procedure.
However, I am only allowed to obtain the @.UID as input parameter.
I tried writing the following:
Select table2.TeamName
From Table1, Table2
Where (table1.TeamID=table2.TeamID)and (UID = @. UID)
It seems to not work as TeamID was not given.
Thus, my other idea of doing this is to:
1) Execute a sql to first obtain the TeamID value with the @.UID parameter given.
then 2)Execute another sql to obtain the TeamName with the result from the first sql as the input parameter.

However, I have never worked with nested sql, so it would be great if someone can link me a site that they know. Better yet, write me an example code.
Thanks you very much!
You do not need to first obtain the TeamID value in the manner you areproposing. What you have should work OK. I'd rewrite it assuch, but it should function in the same manner:

Select Table2.TeamName
From Table1
Inner Join Table2 ON Table1.TeamID = Table2.TeamID
Where Table1.UID = @. UID

Let us know if you are still experiencing problems.|||let me post the code and see if you can shine some light in it:

Function GetSchoolName(ByVal UIDAsInteger)AsString

Dim mycmdAsNew SqlCommand("GetSchoolName", myconn)

mycmd.CommandType = CommandType.StoredProcedure

mycmd.Parameters.Add("@.UID", SqlDbType.Char, 3)

mycmd.Parameters("@.UID").Value = UID

myconn.Open()

Dim SchoolNameAsString = mycmd.ExecuteScalar

myconn.Close()

Return SchoolName

EndFunction
=============================

ALTER PROCEDUREdbo.GetSchoolName

(

@.UIDchar

)

AS

SELECTCOLLEGE_DIV1A.schoolNameASSchoolName

FROMPOWER_RANKINGS, COLLEGE_DIV1A

WHERE(POWER_RANKINGS.UID = @.UID)and(POWER_RANKINGS.TeamCode = COLLEGE_DIV1A.teamCode)

RETURN

================================

Dim dtUpdateTableAsNew DataTable

dtUpdateTable.Clear()

Dim DcAs DataColumn

Dim intTypeAs System.Type = System.Type.GetType("System.Int32")

'create column

Dc =New DataColumn("UID", intType)

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("Points",GetType(Decimal))

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("Week", intType)

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("SchoolName",GetType(String))

dtUpdateTable.Columns.Add(Dc)

Dc =New DataColumn("OldPoints",GetType(Decimal))

dtUpdateTable.Columns.Add(Dc)

dtUpdateTable.Rows.Add(dtUpdateTable.NewRow())

With dtUpdateTable.Rows(dtUpdateTable.Rows.Count - 1)

.Item("UID") = drPowerRankings.UID

.Item("Points") = drPowerRankings.Points

.Item("OldPoints") = Session("OLD_Points")

.Item("Week") = Session("WeekId")

.Item("SchoolName") = GetSchoolName(drPowerRankings.UID)

EndWith
============================
Problem: The value I got returned from the function, GetSchoolName, is always the first row's first value of the table. Regardless of the UID inserted.
I ran the sql with query analyzer, and the Stored Procedure works fine. But the actual coding doesn't work for some reason. kinda weird...
Thanks again for looking into my code.

|||couple of things.
(1)

mycmd.Parameters("@.UID").Value = UID

should be
mycmd.Parameters("@.UID").Value = "UID" --The value should be inquotes if it is a string.
(2) In your stored proc I'd recommend setting the SIZE for the parameter.

@.UIDchar

would be

@.UIDchar(3)

Other recommendations:
(1) Use SET NOCOUNT option. check out Books on line for more info.
(2) Use OUTPUT Parameter. Since you are only returning one parameter that would be faster with ExecuteScalar. SELECT statement returns a recordset versus OUTPUT parameter returning a single record. Prbly not very noticeable but would be more efficient. check out books on line about using OUTPUT parameter.

sql

Nested Sets Problem

I'm trying to see if this is possible. I have an employee table that
contains employee ID and manager ID. I'm trying to string together the
complete hierarchy for an employee in one row. So, if you have employee ID
1
reports to 2. Employee 2 reports to 3, then the result would be:
EmployeeID ReportingTo
1 2/3
Is this possible using nested sets? Can someone please give me some samples
of this?Have you searched google groups to see if there are solutions for similar
problems? If not look for "Joe Celko"+"nested sets".
Anith|||Susannah,
You should find a few useful things here:
http://groups.google.co.uk/groups? ...er+itzi
k
Steve Kass
Drew University
Susannah wrote:

>I'm trying to see if this is possible. I have an employee table that
>contains employee ID and manager ID. I'm trying to string together the
>complete hierarchy for an employee in one row. So, if you have employee ID
1
>reports to 2. Employee 2 reports to 3, then the result would be:
>EmployeeID ReportingTo
>1 2/3
>Is this possible using nested sets? Can someone please give me some sample
s
>of this?
>

Nested Select?

Hi all,
I have the following table
id (autonumber)
category1 (int)
category2 (int)
booking_month (int)
booking_year (int)
I have records in the table for booking_year = 2004 and booking_year = 2005,
for example
id, category1, category2, booking_month, booking_year
1, 20, 30, 4, 2004
1, 20, 31, 10, 2004
1, 20, 30, 4, 2005
I need a SQL statement there lists all those records that are in 2004 but no
in 2005 for a particular category1.
Any ideas?
Thanks,
IvanYou could use a nested sub-query, however, you can also just select from
[booking] as B04 for booking_year = 2004 and then left join [booking] as B05
on booking_year = 2005. Only include records where B05.id is NULL.
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> I have records in the table for booking_year = 2004 and booking_year =
2005,
> for example
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004 but
no
> in 2005 for a particular category1.
> Any ideas?
> Thanks,
> Ivan
>|||"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:

> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004
> but no in 2005 for a particular category1.
SELECT * FROM [tablename]
WHERE (booking_year <> 2005) AND (category1 = particular_value)
HTH|||This does not work:
SELECT o1.category2 FROM offline o1
LEFT JOIN offline o2
ON o2.booking_year = 2005
WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
2004)
and o2.id_no is null
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> You could use a nested sub-query, however, you can also just select from
> [booking] as B04 for booking_year = 2004 and then left join [booking] as
B05
> on booking_year = 2005. Only include records where B05.id is NULL.
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> 2005,
but
> no
>|||Or like this
SELECT * FROM [tablename]
WHERE booking_year =2004 AND category1 not in (select category1 from
[tablename] t1 where year = 2005)
Hth
"Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:
>
> SELECT * FROM [tablename]
> WHERE (booking_year <> 2005) AND (category1 = particular_value)
> HTH|||The compare between category1 and category2 should be in the join.. on..
clause. Try this:
select
B04.booking_year,
B04.category1,
B04.category2
from
offline as B04
left join
offline as B05
-- join both aliases of offline on category1 and category2. also
filter B05 on 2005.
on B05.booking_year = 2005 and
B05.category1 = B04.category1 and
B05.category2 = B04.category 2
where
B04.booking_year = 2004 and
B05.id is NULL -- Doesn't matter which B05 column is null
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
> This does not work:
> SELECT o1.category2 FROM offline o1
> LEFT JOIN offline o2
> ON o2.booking_year = 2005
> WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
> 2004)
> and o2.id_no is null
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> B05
> but
>|||The original B04 records total 112.
The original B05 records total 24.
Logic and simple math tell me that I should get 112-24 = 88!!
When I run your suggested statement I get 85 records.
When I run this statement:
SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2004 AND category2 NOT IN
(SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2005)
I get 87 records.
Strange indeed!!!
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
> The compare between category1 and category2 should be in the join.. on..
> clause. Try this:
> select
> B04.booking_year,
> B04.category1,
> B04.category2
> from
> offline as B04
> left join
> offline as B05
> -- join both aliases of offline on category1 and category2. also
> filter B05 on 2005.
> on B05.booking_year = 2005 and
> B05.category1 = B04.category1 and
> B05.category2 = B04.category 2
> where
> B04.booking_year = 2004 and
> B05.id is NULL -- Doesn't matter which B05 column is null
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
from
as
=
2004
>|||This was my original statement that I tried but I get always 1 less than the
expected result :(
"AM" <shahdharti@.gmail.com> schrieb im Newsbeitrag
news:e8PTwj3DFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Or like this
> SELECT * FROM [tablename]
> WHERE booking_year =2004 AND category1 not in (select category1 from
> [tablename] t1 where year = 2005)
> Hth
>
> "Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
> news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
>|||Ivan,
Can you be more precise about what you want? What does
"in 2004 but no in 2005" mean? I assume it means
Find all rows where booking_year = 2004 but for which
there is not a matching row with booking_year = 2005.
But ... you haven't made it clear what "matching row" means. Does
a matching 2005 row need to have the same category1, category2,
and booking_month, or just some of those columns? And your
data here says id is "autonumber", but you show three identical id
values - does the matching 2005 row have to have the same id value?
You said you think you should get a number of rows that is
the number of 2004 rows minus the number of 2005 rows, but
how do you know every one of the 2005 rows in your table
matches exactly one 2004 row? Maybe some 2004 rows appear
twice, and maybe some 2005 rows have no corresponding 2004
row.
You may know what it means for a 2004 row to be in 2005 as
well, but unless you describe it clearly in terms of the columns of
this table, you can't expect to be able to write a query that will
give you what you want.
Steve Kass
Drew University
Ivan Debono wrote:

>Hi all,
>I have the following table
>id (autonumber)
>category1 (int)
>category2 (int)
>booking_month (int)
>booking_year (int)
>I have records in the table for booking_year = 2004 and booking_year = 2005
,
>for example
>id, category1, category2, booking_month, booking_year
>1, 20, 30, 4, 2004
>1, 20, 31, 10, 2004
>1, 20, 30, 4, 2005
>I need a SQL statement there lists all those records that are in 2004 but n
o
>in 2005 for a particular category1.
>Any ideas?
>Thanks,
>Ivan
>
>|||Perhaps you should be joining on booking_month too ?
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OzdKJc4DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> The original B04 records total 112.
> The original B05 records total 24.
> Logic and simple math tell me that I should get 112-24 = 88!!
> When I run your suggested statement I get 85 records.
> When I run this statement:
> SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2004 AND category2 NOT IN
> (SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2005)
> I get 87 records.
> Strange indeed!!!
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
=
> from
[booking]
> as
booking_year
> =
> 2004
>

Nested Select to Join three tables into one result set

I'll simplify the table structure that I've inherited in order to try to
explain what I need.
Three tables - ISSUES, USERS and ASSIGN:
ISSUES
IDRecord - Primary Key
Description
DateEntered
USERS
IDRecord- Primary Key
LastName
FirstName
ASSIGN
IDRecord - Primary Key
IDDefRec - matches to IDRecord in ISSUES
IDUser - matches to IDRecord in USERS
What I want is a result set for all ISSUES entered after 7/1/2005 (for
example) that includes all of the columns from ISSUES and the FirstName and
LastName of the last user assigned to the ISSUE. The ASSIGN table can
contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
So I figure I just need to get the TOP 1 of the ASSIGN table that matches
the ISSUE and get the corresponding USER name. I just can't figure out how
to do it in one SELECT statement.
JeffWhat the first rule of a data model' A data element has one and only
one name in a schema. So what is this magical "record_id" that appears
to be everywhere?
And why don' t you know that a row and record are totally different
concepts? Why don't you use ISO-8601 Standard date formats? Why did
you put the qualifier in the front of the names, in violation of the
ISO-11179 rules for metadata?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
If you knew what a key was, followed ISO Standards, and underdstood DRI
action your non-existent DDL would look like this:
Why is there no resolution date in your issues? The model of time in
SQL is durations, not single dates.
CREATE TABLE Issues
(issue_nbr INTEGER NOT NULL PRIMARY KEY,
issue_description VARCHAR(255) NOT NULL);
Create a dummy user zero called "To Be Determined" or '{{TBD}}' for
when an issue arrived if you don't assign them immediately.
CREATE TABLE Users
(user_id INTEGER DEFAULT 0 PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL)
CREATE TABLE Assignments
(issue_nbr NOT NULL
REFERENCES Isuses (issue_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
user_id INTEGER DEFAULT '{{TBD}}' NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (issue_nbr, user_id)
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
resolved_date DATETIME, -- null means still open);
CHECK (assigned_date <= resolved_date));
Now you have a whole tracking history.
SELECT @.my_date, I1.issue_nbr, I1.issue_description,
U1.user_id, U1.last_name, U1.first_name
FROM Issues AS I1, Assignments AS A1, Users AS U1
WHERE @.my_date BETWEEN A1.assigned_date AND A1.resolved_date
AND U1.user_id = A1.user_id
AND A1.issue_nbr = I1.issue_nbr;|||> So what is this magical "record_id" that appears
> to be everywhere?
> And why don' t you know that a row and record are totally different
> concepts? Why don't you use ISO-8601 Standard date formats? Why did
> you put the qualifier in the front of the names, in violation of the
> ISO-11179 rules for metadata?
> Why is there no resolution date in your issues? The model of time in
> SQL is durations, not single dates.
>
It appears that you missed the part where I said that I INHERITED this
structure. This is an application that the school district I work for
purchased and I have NO control over its structure. It is what it is. I
simply need to know if, given the structure that I laid out, is there a way
to return for each item in the ISSUES table beyond a parameterized date the
first and last name of the User last assigned to the Issue in the ASSIGN
table as well as all of the details of that Issue.
js|||You are screwed. Would you like an expert witness for the lawsuit?|||> You are screwed. Would you like an expert witness for the lawsuit?
;}
I guess I'll just write a stored procedure to move the records to a temp
table then and look up the User name against the Temp result set. Just was
looking for a quicker way.
js|||SELECT I.*, U.FirstName, U.LastName
FROM Issues I
INNER JOIN
(SELECT IDDefRec,
MAX(IDUser) As IDUser
FROM Assign
GROUP BY IDDefRec) A
ON I.IDRecord = A.IDDefRec
INNER JOIN USERS U
ON A.IDUser=U.IDRecord
--The above example just get the max of userid. To get the last assigned
userid, you have to add another column
in the ASSIGN table to keep track of the time of assignment.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff Swanberg" <jswanberg@.swanbergcomputing.com> wrote in message
news:%23GMlmu$jFHA.3580@.TK2MSFTNGP09.phx.gbl...
> I'll simplify the table structure that I've inherited in order to try to
> explain what I need.
> Three tables - ISSUES, USERS and ASSIGN:
> ISSUES
> IDRecord - Primary Key
> Description
> DateEntered
> USERS
> IDRecord- Primary Key
> LastName
> FirstName
> ASSIGN
> IDRecord - Primary Key
> IDDefRec - matches to IDRecord in ISSUES
> IDUser - matches to IDRecord in USERS
>
> What I want is a result set for all ISSUES entered after 7/1/2005 (for
> example) that includes all of the columns from ISSUES and the FirstName
> and LastName of the last user assigned to the ISSUE. The ASSIGN table can
> contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
> So I figure I just need to get the TOP 1 of the ASSIGN table that matches
> the ISSUE and get the corresponding USER name. I just can't figure out
> how to do it in one SELECT statement.
> Jeff
>
>|||>> I'll just write a stored procedure to move the records [sic] to a temp table t
hen and look up the User name against the Temp result set. <<
That will not work. The schema does not show when someone was assigned
to an issue, only when the issue was first entered. Created on Monday,
assigned to Tom on Tuesday, handed off to Wendy on Wednesday and thrown
to Thomas on Thursday.
The design is flawed.|||> You are screwed. Would you like an expert witness for the lawsuit?
I'm not sure that someone that is mentally unstable would qualify as an expe
rt
witness. ;->
Thomas|||On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@.anywhere.
com>
wrote:
in <eeuGRJTkFHA.1444@.TK2MSFTNGP10.phx.gbl>

>I'm not sure that someone that is mentally unstable would qualify as an exp
ert
>witness. ;->
>
>Thomas
I sure hope your insults are tongue in ch because they have no place in a
professional newsgroup. Or are you NOT a professional?
Stefan Berglund|||> I sure hope your insults are tongue in ch because they have no place in ad">
> professional newsgroup. Or are you NOT a professional?
My sententious observations about Don Celko's behavior are as tongue and che
ek
as his remarks. ;->
Thomas

Nested SELECT query that also returns COUNT from related table

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!Use an aggregate query:

select Category.CatID,
Category.Name,
...
count(distinct Product.ProductID) ProductCount
from Categories
left outer join Products on Categories.CategoryID = Products.CategoryID
group by Category.CatID,
Category.Name,
...
order by count(distinct Product.ProductID)|||Absolutely brilliant, it works fantastically, thank you so much!! :D

Now to try and figure out how it actually works :)|||If you use Books Online to figure out how this query works, you can consider yourself to have passed SQL 101. It incorporates the most fundamental aspects of SQL programming.

Nested Repeater Query

Hello Everyone,

I am trying to create a query for the purpose of a nested repeater relation. The information needs to be pulled from one table. I have shortened the columns to the ones that are required.

table - Pages

ID
PageName
ParentPageID

So, take the following example:

ID 14, PageName - Service A, ParentPage ID = 6
ID 15, PageName - Service B, ParentPage ID = 6
ID 36 PageName - Client 1, ParentPage ID = 14
ID 37 PageName - Client 2, ParentPage ID = 14
ID 38 PageName - Client 3, ParentPage ID = 15
ID 39 PageName - Client 4, ParentPage ID = 15

So, I want to create a query that will get my nested repeater to display as follows:

Service A
Client 1
Client 2
Service B
Client 3
Client 4

What I have come up with so far is:

SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)

SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages p

The relation would be based off ParentPageID. I keep getting errors that either there is no unique value or the relation is null. What am I am missing here?

Replace

SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)

SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages p

with

SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID

Inform me if this works for u

|||

Thank you, the query worked great! I am now trying to make it work with a nested repeater however, and I am getting the following error:

System.IndexOutOfRangeException: Cannot find table 1.

I am assuming this is because this query places everything in 1 table, right? Here is my codebehind (I haven't placed the query in a stored procedure yet)

public partial class Controls_RightSideBarNavTree : BaseControl
{
protected void Page_Load(object sender, EventArgs e)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["dsn1"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connString);
SqlCommand MyCommand = new SqlCommand("SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID", myConnection);
SqlDataAdapter ad = new SqlDataAdapter(MyCommand);
DataSet ds = new DataSet();
ad.Fill(ds);

ds.Relations.Add(new DataRelation("NavigationRelation", ds.Tables[0].Columns["ParentPageID"], ds.Tables[1].Columns["ParentPageID"]));
Repeater1.DataSource = ds.Tables[0];
Repeater1.DataBind();
}
}

protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{
DataRowView drv = e.Item.DataItem as DataRowView;
Repeater childRepeater = e.Item.FindControl("childRepeater") as Repeater;
childRepeater.DataSource = drv.CreateChildView("NavigationRelation");
childRepeater.DataBind();
}
}

|||

try this

u have to add next to the repeater in an item template a label with the Eval("Id"), and lets give it an id="label1"

protected void Page_Load(object sender, EventArgs e)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["dsn1"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connString);
SqlCommand MyCommand = new SqlCommand("SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID", myConnection);
SqlDataAdapter ad = new SqlDataAdapter(MyCommand);
DataSet ds = new DataSet();
ad.Fill(ds);

Repeater1.DataSource = ds.Tables[0];
Repeater1.DataBind();
}
}

protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{

string connString = System.Configuration.ConfigurationManager.ConnectionStrings["dsn1"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connString);
SqlCommand MyCommand = new SqlCommand("SELECT * from tbl_Pages Parent inner join tbl_Pages Child on Parent.Id = Child.ParentPageID where Parent.Id = @.ID",myConnection);
Label lbl = (Label)e.Item.FindControl("Label1");

MyCommand.Parameters.Add("@.Id",lbl.Text);
SqlDataAdapter ad = new SqlDataAdapter(MyCommand);
DataSet ds = new DataSet();
ad.Fill(ds);
Repeater childRepeater = e.Item.FindControl("childRepeater") as Repeater;
childRepeater.DataSource = ds;
childRepeater.DataBind();
}
}

Inform me if this works for u.

|||

Thank you very much for your help. I see that you are from Lebanon. I am originally from Damascus :)

Anyway, I had to make a change to the first query in order to get the correct results for the first query...
SELECT * from tbl_Pages WHERE ParentPageID = @.ID (@.ID is taken from a class BasicPageInfo.ID)

However, when using the 2nd query for the childrepeater, I get the following error:
System.NullReferenceException: Object reference not set to an instance of an object. on Line 47 childRepeater.DataSource = ds;

When I run the query in SQL Server Management Studio I get the correct result when I pass, for example, 32 for the @.ID.

This error was happening when i used the first query the way you had indicated as well. Seems to me, that for some reason, the dataset is not being populated correctly.

This is what I intend on doing. Before trying to use a nested repeater, these were my functions to create a navigation menu. This worked just fine, but I couldn't figure out how to insert a nested repeater into the below code (I took this over from another developer).

public partial class Controls_RightSideBarNavTree : BaseControl
{
protected void Page_Load(object sender, EventArgs e)
{
PagesDB sdb1 = new PagesDB();
BasicPageData[] dataArray1 = null;
int num1 = -1;

if (base.BasicPageInfo != null)
{
dataArray1 = sdb1.Page_GetAllWithParentID(base.BasicPageInfo.ID);
num1 = base.BasicPageInfo.ID;
if (dataArray1.Length == 0)
{
dataArray1 = sdb1.Page_GetAllWithParentID(base.BasicPageInfo.ParentPageID);
num1 = base.BasicPageInfo.ParentPageID;
}

/* Checks to see if page is a content page. If so, applied Header Text */
if (num1 > 0)
{
BasicPageData data1 = sdb1.Page_GetItemByID(num1);
this.lblHeader.Text = data1.PageName;
this.Repeater1.DataSource = dataArray1;
this.Repeater1.DataBind();
}
else
{
this.lblHeader.Text = "";
}
}

}

protected void Repeater1_ItemCreated(object sender, RepeaterItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{
BasicPageData data1 = (BasicPageData)e.Item.DataItem;
HyperLink link1 = (HyperLink)e.Item.FindControl("hypItem");
HtmlGenericControl control1 = (HtmlGenericControl)e.Item.FindControl("liItem");
if (null == data1) return;
link1.Text = data1.PageName;
link1.NavigateUrl = GlobalData.TRCBaseURL + "content/default.aspx?" + BaseControl.PAGE_GUID + "=" + data1.GUID.ToString() + "&" + BaseControl.TOOLBARPAGE_GUID + "=" + base.ToolbarPageGUID.ToString();
if (base.PageGUID.Equals(data1.GUID))
{
control1.Attributes.Add("class", "subOn");
}
}
}

}

|||

Hi adarwich,

From the context, I didn't see the control childRepeater has been initialized. Please check if childRepeater is a valid control on the page and it has a valid reference to an object.

Nested Query Problem

I am writing a stored procedure that has to insert several rows from one table to another. The problem is that the table into which the rows will be inserted, has more columns than the table that the rows come from. When the stored procedure is called, the extra columns in each new row is supposed to be populated by the stored procedure's arguments. Example:

TableA:
Columns: ID, Group, Name, Email, NewletterSubscriber

TableB:
Columns: ID, Name, Email

The arguments provided are Group, and NewsletterSubscriber.

I need to insert into Table A all records from Table B where ID > 1000 and I need to insert the Group and NewsLetterSubscriber arguments at the same time because these columns do not allow nulls.

I think it might be something like

insert into TableA (ID, Group, Name, Email, NewletterSubscriber)
values (
select * from TableB where ID > 1000)

But how do I insert the stored procedure arguments into the correct columns of the rows?

Your insert statement should be something like

insert into TableA(group, newsletterSubscriber)
values (@.arg_group, @.arg_newsletterSubscriber)

|||

As per your example:

You can do the following :

insert TableA (ID, Group, Name, Email, NewletterSubscriber)

select ID,@.Group,Name,Email,@.NewsletterSubscriber from TableB where ID > 1000

Here @.Group and @.NewsletterSubscriber are a arguments from a Stored procedure.

Thanks

Naras.

|||

I think the procedure definition you want is:

Code Snippet

CREATE PROCEDURE TransRecords_AtoB

@.DefGroup AS int,

@.DefNewsLetterSub AS bit

AS

BEGIN

-- Do you need to clear existing records if so uncomment statement below

-- DELETE FROM TableA

-- WHERE [ID] IN (

-- SELECT ID

-- FROM TableB

-- WHERE (ID > 1000)

-- )

-- Insert the required rows

INSERT INTO TableA ([ID], [Group], [Name],

[Email], [NewsLetterSubscriber])

SELECT [ID], @.DefGroup, [Name], [Email], @.DefNewsLetterSub

FROM TableB

WHERE ([ID] > 1000)

END

If you need to remove records already existing for IDs in TableB then use the commented out delete. Change the types of the arguments to match your fields.

I would recomment that you consider changing some of your column names. It is a bad idea to use identifiers that are reserved words (or might become ones). Name and Group fall into that camp and ID is also suspect. These have to be delimited as shown (and this can cause problem with autogenerated SQL in some tools). Use RecID, RecName, RecGroup etc. or something more descriptive.

Nested Query

Hi All,

I am stuck to a senario in which i need the help of u all.

well the present senaio is i have two table xx and yy.

the table conatins data as below:

table xx

EmployeeID Departments

10014 A

10015 A

10002 A

10013 B

10019 B

10056 B

table yy

Empoyee ID ActuaDate Start time EndTime

10014 03/30/2007 0600 1445

10015 03/30/2007 0600 1445

10002 03/30/2007 0600 1445

10013 03/31/2007 1130 1300

10019 03/31/2007 0300 1300

10056 03/31/2007 0300 1100

--

conditions :

Actdate ='03/30/2007' and starttime>='600'

or actdate ='03/31/2007' and starttime<='0300' -for pick

Result should be as:

Department EmployeeCount Pick Drop

A 3 3 3

B 3 3 3

please help me in solving the problem.

Regards

sufian

Your requirements are a bit 'unclear'. Perhaps this is in the right direction:

Code Snippet

SET NOCOUNT ON

DECLARE @.xx table
( EmployeeID int,
Department char(1)
)

INSERT INTO @.xx VALUES ( 10014, 'A' )
INSERT INTO @.xx VALUES ( 10015, 'A' )
INSERT INTO @.xx VALUES ( 10002, 'A' )
INSERT INTO @.xx VALUES ( 10013, 'B' )
INSERT INTO @.xx VALUES ( 10019, 'B' )
INSERT INTO @.xx VALUES ( 10056, 'B' )

DECLARE @.yy table
( EmployeeID int,
ActuaDate datetime,
StartTime int,
EndTime int
)

INSERT INTO @.yy VALUES ( 10014, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10015, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10002, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10013, '03/31/2007', 1130, 1300 )
INSERT INTO @.yy VALUES ( 10019, '03/31/2007', 0300, 1300 )
INSERT INTO @.yy VALUES ( 10056, '03/31/2007', 0300, 1100 )
INSERT INTO @.yy VALUES ( 10013, '03/31/2007', NULL, NULL )
INSERT INTO @.yy VALUES ( 10056, '03/31/2007', 0300, NULL )

SELECT
x.Department,
EmployeeCount = count( DISTINCT y.EmployeeID ),
Picked = count( y.StartTime ),
Dropped = count( y.EndTime )
FROM @.XX x
JOIN @.YY y
ON x.EmployeeID = y.EmployeeID
GROUP BY x.Department

Department EmployeeCount Picked Dropped
- - -- --
A 3 3 3
B 3 4 3

|||

While I can't completely follow your requirements, I think this will give you the idea for what you want. The idea is to do a select with a group by and manufacture values using SUM

select department, count(distinct xx.employeeId) as EmployeeCount,
sum(case when actuadate ='03/31/2007' and starttime<='0300' then 1 else 0 end) as Pick
from xx
join yy
on xx.employeeId = yy.employeeId
group by department

The full code follows, and if you could provide this kind of structure (and change mine if it is wrong) it is a lot easier. Plus, if your results are what you expected to receive, it seems wrong. If it is just a basice feel for the results, I understand.

I would also consider making the start and end values a datetime. Use a constraint to make sure they are in the same day perhaps, but it will be easier to work with with date and time in the same column.

Code Snippet

create table xx
(
EmployeeId int primary key,
Department char(1)
)
insert into xx
select 10014,'A'
union all
select 10015,'A'
union all
select 10002,'A'
union all
select 10013,'B'
union all
select 10019,'B'
union all
select 10056,'B'

create table yy
(
employeeId int,
actuaDate datetime,
startTime int,
endTime int
)
insert into yy
select 10014,'03/30/2007',0600,1445
union all
select 10015,'03/30/2007',0600,1445
union all
select 10002,'03/30/2007',0600,1445
union all
select 10013,'03/31/2007',1130,1300
union all
select 10019,'03/31/2007',0300,1300
union all
select 10056,'03/31/2007',0300,1100
go

select department, count(distinct xx.employeeId) as EmployeeCount,
sum(case when actuadate ='03/31/2007' and starttime<='0300' then 1 else 0 end) as Pick
from xx
join yy
on xx.employeeId = yy.employeeId
group by department