Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Friday, March 30, 2012

help- how to retrive image from sql server 2000 database

hi,
i m use asp.net 1.1.

i want to retrive image or picture from sql server 2000 database.

what should i do?

plz give "sample code" and solution.

it's urgent.

thanks in advanceone more thing,

i want to put that retrived image into asp.net's "image web control".

what should i do?
give solution.

thanks one's again.

Wednesday, March 28, 2012

Help for joining tables (2nd try ;o) )

Okay so I'll give it another try
I have an assigment design a web solution with a presention of energy
consumptions from some appartments.
All appartments are identical and have 5 meters, the only difference is the
appartment number (3 digits) e.g. "105" all meters gets logged once every
night at 02.00 AM.
My idea is to work with a collection of "predefined" meters e.g. and join
them with the logged data, that's why I have the meters table, then I can
also add additional meters without changeing code.
The consumption presentation is done per appartment, meaning that the owner
of the appartment logs on to the system and the appartment ID gets
identified e.g. 105. When the owner has logged on the energy data for the
related appartment will be presented.
I have three tables
Meters m (defined by me)
Analog register (defined by the log system)
Analog values (defined by the log system)
the relation between analog values is the field "ID" PK in Analog Register
AR, FK in Analog Values AV
this script joins and summerize the energy data, so far so good!
Select ar.[name], av.id, count(*) as NoOfLogs,
Sum(Case WHEN Datediff(Mi, timedate, getdate()) < 60 THEN av.Val ELSE 0
END) as [Last Hour],
Sum(av.val) as Total
From [Analog register] AR INNER JOIN [analog values] AV
On AR.[ID] = AV.[ID]
--where ar.[name] in (select [name] from meters m where type = 1)
GROUP by ar.[name], av.id
select * from meters
--where ar.[name] in (select [name] from meters m where type = 1)
The where caluse is the part I try to replace with something like
"where meters.name+@.appartmentnumber like ar.name"
What I need is to see if meters.name plus the appartmendnumber is a
substring of ar.name.
With the above "where clause" it only works if there's an exact match.
@.appartmentnumber is a variable I add to the query.
I hope this makes my needs more clear'
regards
Henry
****** DDL
CREATE TABLE [dbo].[Meters] (
[Meter_id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [int] NULL ,
[Unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DA234BA-12AA-43E6-B94C-004F265F82D8','Solfanger',2,
'kwh','Solenergi')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('EDC2D54C-704D-4DE3-AFB2-1A2584BFB077','And',1,'Kwh',
'Energi')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('F3C42A01-098C-48F8-A4D3-2F4F120AE597','Anders',1,'Mw
h','Elmler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0A4FF86E-0F66-44FB-8022-B9FC90630F22','X5991_P07_inp
ut_Accum',1,'m3','Gas-mler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('046B8001-795D-4C61-8B45-CA9B3BB02EEF','X5991_P06_inp
ut_Accum',1,'m3','Varmtvandsmler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DBE4542-F741-4D60-A9B1-DAE4DA69AD10','X5991_P04_inp
ut_Accum',1,'Kwh','El-mler')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('20C1A7CB-041E-47E0-B0C7-DD7F54C6CC41','SupplyTemp.Ma
x',1,'m3','Testmler1')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('A8E1FA35-225C-414D-849C-F5256D2A2845','ReturnTemp',1
,'kwh','Testmler2')
INSERT INTO [meters]
([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('FDDE06DB-E501-4748-A514-FCB9B99594C4','X5991_P05_inp
ut_Accum',1,'m3','Koldtvandsmler')
CREATE TABLE [dbo].[Analog Register] (
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID] [int] NOT NULL ,
[Category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [smallint] NOT NULL ,
[Val] [float] NULL ,
[ValueTime] [datetime] NULL ,
[MinValue] [float] NULL ,
[MaxValue] [real] NULL ,
[SignalText] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SigType] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unit] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaxRawTimeLength] [int] NULL ,
[CompactTimeLength0] [int] NULL ,
[CompactTime0] [datetime] NULL ,
[CompactVal0] [float] NULL ,
[CompactFlags0] [real] NULL ,
[CompactTimeLength1] [int] NULL ,
[CompactTime1] [datetime] NULL ,
[CompactVal1] [float] NULL ,
[CompactFlags1] [real] NULL ,
[CompactTimeLength2] [int] NULL ,
[CompactTime2] [datetime] NULL ,
[CompactVal2] [float] NULL ,
[CompactFlags2] [real] NULL ,
[CompactTimeLength3] [int] NULL ,
[CompactTime3] [datetime] NULL ,
[CompactVal3] [float] NULL ,
[CompactFlags3] [real] NULL
) ON [PRIMARY]
GO
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. OutdoorTemp',1,'LogSignals','Library2',1
,-1.042895
793914795e+000,'Jun
7 2006 12:00:00:000AM','Mler1')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTemp',2,'LogSignals','Library2',1,
6.07073631
2866211e+001,'Jun
7 2006 12:00:00:000AM','Mler2')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTempControl_SetP',3,'LogSignals','
Library2',
1,7.500000000000000e+001,'Jun
7 2006 12:00:00:000AM','Mler3')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. ReturnTemp',4,'LogSignals','Library2',1,
2.83133945
4650879e+001,'Jun
7 2006 12:00:00:000AM','Mler4')
INSERT INTO [Analog Register]
([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2.SupplyTemp.Max',5,'LogSignals','Library2',3,6.0707
56912231445e+001,'Jun
6 2006 12:00:00:000AM','Mler5')
I had to ommit some of the irelevant data from the AR table.
CREATE TABLE [dbo].[Analog Values] (
[ID] [int] NOT NULL ,
[TimeDate] [datetime] NOT NULL ,
[TimeLength] [int] NULL ,
[Flags] [int] NULL ,
[Val] [float] NULL ,
[Count] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
5:49:00:903PM',3600,1,1.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
5:52:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
5:53:00:560PM',3600,1,1.903790714638927e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
7:34:00:547PM',3600,1,1.903548486012570e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
7:34:00:750PM',3600,1,3.907330979177984e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
7:34:00:983PM',3600,1,5.911672461481145e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
7:36:00:653PM',3600,1,2.905523580965939e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
7:36:00:827PM',3600,1,4.908765717964866e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
7:36:00:903PM',3600,1,5.910181823780489e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 24 2006
12:06:00:857AM',3600,1,1.909324707102612e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 24 2006
12:06:00:920AM',3600,1,2.910498584291879e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 24 2006
12:06:00:997AM',3600,1,3.911933323078760e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 24 2006
12:06:01:043AM',3600,1,4.912790439756638e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 24 2006
12:06:01:090AM',3600,1,5.913666189405772e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
2:23:00:140AM',3600,1,1.895964866710483e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
2:23:00:263AM',3600,1,3.898256722175241e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
2:23:00:373AM',3600,1,5.900306349013643e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
2:24:00:200AM',3600,1,2.897082844985975e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
2:24:00:327AM',3600,1,4.899449232335766e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
2:26:00:140AM',3600,1,1.895964866710483e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
2:26:00:200AM',3600,1,2.897082844985975e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
2:26:00:263AM',3600,1,3.898256722175241e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
2:26:00:327AM',3600,1,4.899449232335766e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
2:26:00:390AM',3600,1,5.900623109525032e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
1:08:00:687AM',3600,1,1.906157101988718e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
1:08:00:763AM',3600,1,2.907573207804341e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
1:08:00:840AM',3600,1,3.909007946591222e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
1:08:00:950AM',3600,1,4.911057573429624e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
1:08:01:030AM',3600,1,5.912548211130281e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
1:09:00:670AM',3600,1,1.905840341477328e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
1:09:00:797AM',3600,1,3.908206728827120e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
1:09:00:920AM',3600,1,5.910498584291879e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
1:10:00:733AM',3600,1,2.907014218666595e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
1:10:00:797AM',3600,1,3.908206728827120e+000,1)
INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
6:27:00:687AM',3600,1,1.906157101988718e+000,1)
INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
6:27:00:747AM',3600,1,2.907275080264210e+000,1)
INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
6:27:00:810AM',3600,1,3.908448957453476e+000,1)
INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
6:27:00:873AM',3600,1,4.909622834642743e+000,1)
INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
6:27:00:937AM',3600,1,5.910815344803268e+000,1)Henry,

> What I need is to see if meters.name plus the appartmendnumber is a
> substring of ar.name.
...
where ar.name like '%' + meters.name + @.appartmentnumber + '%'
AMB
meters.name+@.appartmentnumber like
"Henry" wrote:

> Okay so I'll give it another try
> I have an assigment design a web solution with a presention of energy
> consumptions from some appartments.
> All appartments are identical and have 5 meters, the only difference is th
e
> appartment number (3 digits) e.g. "105" all meters gets logged once every
> night at 02.00 AM.
> My idea is to work with a collection of "predefined" meters e.g. and join
> them with the logged data, that's why I have the meters table, then I can
> also add additional meters without changeing code.
> The consumption presentation is done per appartment, meaning that the owne
r
> of the appartment logs on to the system and the appartment ID gets
> identified e.g. 105. When the owner has logged on the energy data for the
> related appartment will be presented.
> I have three tables
> Meters m (defined by me)
> Analog register (defined by the log system)
> Analog values (defined by the log system)
> the relation between analog values is the field "ID" PK in Analog Register
> AR, FK in Analog Values AV
> this script joins and summerize the energy data, so far so good!
> Select ar.[name], av.id, count(*) as NoOfLogs,
> Sum(Case WHEN Datediff(Mi, timedate, getdate()) < 60 THEN av.Val ELSE 0
> END) as [Last Hour],
> Sum(av.val) as Total
> From [Analog register] AR INNER JOIN [analog values] AV
> On AR.[ID] = AV.[ID]
> --where ar.[name] in (select [name] from meters m where type = 1)
> GROUP by ar.[name], av.id
> select * from meters
> --where ar.[name] in (select [name] from meters m where type = 1)
> The where caluse is the part I try to replace with something like
> "where meters.name+@.appartmentnumber like ar.name"
> What I need is to see if meters.name plus the appartmendnumber is a
> substring of ar.name.
> With the above "where clause" it only works if there's an exact match.
> @.appartmentnumber is a variable I add to the query.
> I hope this makes my needs more clear'
>
> regards
> Henry
>
> ****** DDL
>
> CREATE TABLE [dbo].[Meters] (
> [Meter_id] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [int] NULL ,
> [Unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Designation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DA234BA-12AA-43E6-B94C-004F265F82D8','Solfanger',
2,'kwh','Solenergi')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('EDC2D54C-704D-4DE3-AFB2-1A2584BFB077','And',1,'Kwh
','Energi')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('F3C42A01-098C-48F8-A4D3-2F4F120AE597','Anders',1,'
Mwh','Elmler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0A4FF86E-0F66-44FB-8022-B9FC90630F22','X5991_P07_i
nput_Accum',1,'m3','Gas-mler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('046B8001-795D-4C61-8B45-CA9B3BB02EEF','X5991_P06_i
nput_Accum',1,'m3','Varmtvandsmler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('0DBE4542-F741-4D60-A9B1-DAE4DA69AD10','X5991_P04_i
nput_Accum',1,'Kwh','El-mler')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('20C1A7CB-041E-47E0-B0C7-DD7F54C6CC41','SupplyTemp.
Max',1,'m3','Testmler1')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('A8E1FA35-225C-414D-849C-F5256D2A2845','ReturnTemp'
,1,'kwh','Testmler2')
> INSERT INTO [meters]
> ([Meter_id],[Name],[Type],[Unit],[Design
ation])VALUES('FDDE06DB-E501-4748-A514-FCB9B99594C4','X5991_P05_i
nput_Accum',1,'m3','Koldtvandsmler')
>
> CREATE TABLE [dbo].[Analog Register] (
> [Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ID] [int] NOT NULL ,
> [Category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [smallint] NOT NULL ,
> [Val] [float] NULL ,
> [ValueTime] [datetime] NULL ,
> [MinValue] [float] NULL ,
> [MaxValue] [real] NULL ,
> [SignalText] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SigType] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Unit] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaxRawTimeLength] [int] NULL ,
> [CompactTimeLength0] [int] NULL ,
> [CompactTime0] [datetime] NULL ,
> [CompactVal0] [float] NULL ,
> [CompactFlags0] [real] NULL ,
> [CompactTimeLength1] [int] NULL ,
> [CompactTime1] [datetime] NULL ,
> [CompactVal1] [float] NULL ,
> [CompactFlags1] [real] NULL ,
> [CompactTimeLength2] [int] NULL ,
> [CompactTime2] [datetime] NULL ,
> [CompactVal2] [float] NULL ,
> [CompactFlags2] [real] NULL ,
> [CompactTimeLength3] [int] NULL ,
> [CompactTime3] [datetime] NULL ,
> [CompactVal3] [float] NULL ,
> [CompactFlags3] [real] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. OutdoorTemp',1,'LogSignals','Library2',1
,-1.0428
95793914795e+000,'Jun
> 7 2006 12:00:00:000AM','Mler1')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTemp',2,'LogSignals','Library2',1,
6.070736
312866211e+001,'Jun
> 7 2006 12:00:00:000AM','Mler2')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. SupplyTempControl_SetP',3,'LogSignals','
Library2
',1,7.500000000000000e+001,'Jun
> 7 2006 12:00:00:000AM','Mler3')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2. ReturnTemp',4,'LogSignals','Library2',1,
2.831339
454650879e+001,'Jun
> 7 2006 12:00:00:000AM','Mler4')
> INSERT INTO [Analog Register]
> ([Name],[ID],[Category],[Area],[Type],[V
al],[ValueTime],[SignalText])VALUES('Lib
rary2.SupplyTemp.Max',5,'LogSignals','Library2',3,6.07
0756912231445e+001,'Jun
> 6 2006 12:00:00:000AM','Mler5')
> I had to ommit some of the irelevant data from the AR table.
>
> CREATE TABLE [dbo].[Analog Values] (
> [ID] [int] NOT NULL ,
> [TimeDate] [datetime] NOT NULL ,
> [TimeLength] [int] NULL ,
> [Flags] [int] NULL ,
> [Val] [float] NULL ,
> [Count] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
> 5:49:00:903PM',3600,1,1.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 5:52:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 5:53:00:560PM',3600,1,1.903790714638927e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 23 2006
> 7:34:00:547PM',3600,1,1.903548486012570e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 23 2006
> 7:34:00:750PM',3600,1,3.907330979177984e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 7:34:00:983PM',3600,1,5.911672461481145e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 23 2006
> 7:36:00:653PM',3600,1,2.905523580965939e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 23 2006
> 7:36:00:827PM',3600,1,4.908765717964866e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 23 2006
> 7:36:00:903PM',3600,1,5.910181823780489e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 24 2006
> 12:06:00:857AM',3600,1,1.909324707102612e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 24 2006
> 12:06:00:920AM',3600,1,2.910498584291879e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 24 2006
> 12:06:00:997AM',3600,1,3.911933323078760e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 24 2006
> 12:06:01:043AM',3600,1,4.912790439756638e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 24 2006
> 12:06:01:090AM',3600,1,5.913666189405772e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
> 2:23:00:140AM',3600,1,1.895964866710483e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
> 2:23:00:263AM',3600,1,3.898256722175241e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
> 2:23:00:373AM',3600,1,5.900306349013643e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
> 2:24:00:200AM',3600,1,2.897082844985975e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
> 2:24:00:327AM',3600,1,4.899449232335766e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 25 2006
> 2:26:00:140AM',3600,1,1.895964866710483e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 25 2006
> 2:26:00:200AM',3600,1,2.897082844985975e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 25 2006
> 2:26:00:263AM',3600,1,3.898256722175241e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 25 2006
> 2:26:00:327AM',3600,1,4.899449232335766e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 25 2006
> 2:26:00:390AM',3600,1,5.900623109525032e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
> 1:08:00:687AM',3600,1,1.906157101988718e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
> 1:08:00:763AM',3600,1,2.907573207804341e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 1:08:00:840AM',3600,1,3.909007946591222e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
> 1:08:00:950AM',3600,1,4.911057573429624e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
> 1:08:01:030AM',3600,1,5.912548211130281e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
> 1:09:00:670AM',3600,1,1.905840341477328e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 1:09:00:797AM',3600,1,3.908206728827120e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
> 1:09:00:920AM',3600,1,5.910498584291879e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
> 1:10:00:733AM',3600,1,2.907014218666595e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 1:10:00:797AM',3600,1,3.908206728827120e+000,1)
> INSERT INTO [Analog values] VALUES(1,'Jun 26 2006
> 6:27:00:687AM',3600,1,1.906157101988718e+000,1)
> INSERT INTO [Analog values] VALUES(2,'Jun 26 2006
> 6:27:00:747AM',3600,1,2.907275080264210e+000,1)
> INSERT INTO [Analog values] VALUES(3,'Jun 26 2006
> 6:27:00:810AM',3600,1,3.908448957453476e+000,1)
> INSERT INTO [Analog values] VALUES(4,'Jun 26 2006
> 6:27:00:873AM',3600,1,4.909622834642743e+000,1)
> INSERT INTO [Analog values] VALUES(5,'Jun 26 2006
> 6:27:00:937AM',3600,1,5.910815344803268e+000,1)
>
>sql

Friday, March 23, 2012

Help Deciding an AV solution

Greetings all,
General question: I'm used to Symantec, but my boos says McAfee is better
for SQL . . . any help?
David
Hi
In general the AV software on your server is decided by the company security
policy rather than having a multitude of different products. You may have
bulk licences to cover all servers. Server versions of AV products may differ
also from Client versions.
Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
"dkon" wrote:

> Greetings all,
> General question: I'm used to Symantec, but my boos says McAfee is better
> for SQL . . . any help?
> --
> David
|||Thanks for the info John.
I was just wondering if anyone had seen performance hits from one app to
another, as I am currently lucky enough to be able to choose which AV
solution to go with.
David
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> In general the AV software on your server is decided by the company security
> policy rather than having a multitude of different products. You may have
> bulk licences to cover all servers. Server versions of AV products may differ
> also from Client versions.
> Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
> "dkon" wrote:
|||Hi
If set up correctly most AV software should not have too much of an impact.
The main cause of performance issues tends to be filter drivers slowing down
the I/O.
John
"dkon" wrote:
[vbcol=seagreen]
> Thanks for the info John.
> I was just wondering if anyone had seen performance hits from one app to
> another, as I am currently lucky enough to be able to choose which AV
> solution to go with.
> --
> David
>
> "John Bell" wrote:
|||John, thanks for the response.
Understood. I was just wondering if anyone had seen differences, as my boss
said he thought Symantec slowed SQL down as compared to McAfee . . . .
David
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If set up correctly most AV software should not have too much of an impact.
> The main cause of performance issues tends to be filter drivers slowing down
> the I/O.
> John
> "dkon" wrote:
|||Hi
If configured incorrectly both can slow SQL Server down.
Some people seem to have their favourites.
I would not accept a blanket statement without justification and/or
validation.
John
"dkon" wrote:
[vbcol=seagreen]
> John, thanks for the response.
> Understood. I was just wondering if anyone had seen differences, as my boss
> said he thought Symantec slowed SQL down as compared to McAfee . . . .
> --
> David
>
> "John Bell" wrote:
|||ok
thanks
David
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If configured incorrectly both can slow SQL Server down.
> Some people seem to have their favourites.
> I would not accept a blanket statement without justification and/or
> validation.
> John
> "dkon" wrote:
|||"dkon" <dkon@.discussions.microsoft.com> wrote in message
news:119E80D6-6862-4519-B168-14A3445B3033@.microsoft.com...
> Greetings all,
> General question: I'm used to Symantec, but my boos says McAfee is better
> for SQL . . . any help?
> --
> David
I wouldn't really care since I wouldn't use either on the database files.
(in fact in many environments, I might not use any).
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Help Deciding an AV solution

Greetings all,
General question: I'm used to Symantec, but my boos says McAfee is better
for SQL . . . any help?
--
DavidHi
In general the AV software on your server is decided by the company security
policy rather than having a multitude of different products. You may have
bulk licences to cover all servers. Server versions of AV products may differ
also from Client versions.
Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
"dkon" wrote:
> Greetings all,
> General question: I'm used to Symantec, but my boos says McAfee is better
> for SQL . . . any help?
> --
> David|||Thanks for the info John.
I was just wondering if anyone had seen performance hits from one app to
another, as I am currently lucky enough to be able to choose which AV
solution to go with.
--
David
"John Bell" wrote:
> Hi
> In general the AV software on your server is decided by the company security
> policy rather than having a multitude of different products. You may have
> bulk licences to cover all servers. Server versions of AV products may differ
> also from Client versions.
> Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
> "dkon" wrote:
> > Greetings all,
> >
> > General question: I'm used to Symantec, but my boos says McAfee is better
> > for SQL . . . any help?
> > --
> > David|||Hi
If set up correctly most AV software should not have too much of an impact.
The main cause of performance issues tends to be filter drivers slowing down
the I/O.
John
"dkon" wrote:
> Thanks for the info John.
> I was just wondering if anyone had seen performance hits from one app to
> another, as I am currently lucky enough to be able to choose which AV
> solution to go with.
> --
> David
>
> "John Bell" wrote:
> > Hi
> >
> > In general the AV software on your server is decided by the company security
> > policy rather than having a multitude of different products. You may have
> > bulk licences to cover all servers. Server versions of AV products may differ
> > also from Client versions.
> >
> > Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
> >
> > "dkon" wrote:
> >
> > > Greetings all,
> > >
> > > General question: I'm used to Symantec, but my boos says McAfee is better
> > > for SQL . . . any help?
> > > --
> > > David|||John, thanks for the response.
Understood. I was just wondering if anyone had seen differences, as my boss
said he thought Symantec slowed SQL down as compared to McAfee . . . .
--
David
"John Bell" wrote:
> Hi
> If set up correctly most AV software should not have too much of an impact.
> The main cause of performance issues tends to be filter drivers slowing down
> the I/O.
> John
> "dkon" wrote:
> > Thanks for the info John.
> >
> > I was just wondering if anyone had seen performance hits from one app to
> > another, as I am currently lucky enough to be able to choose which AV
> > solution to go with.
> > --
> > David
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > In general the AV software on your server is decided by the company security
> > > policy rather than having a multitude of different products. You may have
> > > bulk licences to cover all servers. Server versions of AV products may differ
> > > also from Client versions.
> > >
> > > Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
> > >
> > > "dkon" wrote:
> > >
> > > > Greetings all,
> > > >
> > > > General question: I'm used to Symantec, but my boos says McAfee is better
> > > > for SQL . . . any help?
> > > > --
> > > > David|||Hi
If configured incorrectly both can slow SQL Server down.
Some people seem to have their favourites.
I would not accept a blanket statement without justification and/or
validation.
John
"dkon" wrote:
> John, thanks for the response.
> Understood. I was just wondering if anyone had seen differences, as my boss
> said he thought Symantec slowed SQL down as compared to McAfee . . . .
> --
> David
>
> "John Bell" wrote:
> > Hi
> >
> > If set up correctly most AV software should not have too much of an impact.
> > The main cause of performance issues tends to be filter drivers slowing down
> > the I/O.
> >
> > John
> >
> > "dkon" wrote:
> >
> > > Thanks for the info John.
> > >
> > > I was just wondering if anyone had seen performance hits from one app to
> > > another, as I am currently lucky enough to be able to choose which AV
> > > solution to go with.
> > > --
> > > David
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > In general the AV software on your server is decided by the company security
> > > > policy rather than having a multitude of different products. You may have
> > > > bulk licences to cover all servers. Server versions of AV products may differ
> > > > also from Client versions.
> > > >
> > > > Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
> > > >
> > > > "dkon" wrote:
> > > >
> > > > > Greetings all,
> > > > >
> > > > > General question: I'm used to Symantec, but my boos says McAfee is better
> > > > > for SQL . . . any help?
> > > > > --
> > > > > David|||ok
thanks
--
David
"John Bell" wrote:
> Hi
> If configured incorrectly both can slow SQL Server down.
> Some people seem to have their favourites.
> I would not accept a blanket statement without justification and/or
> validation.
> John
> "dkon" wrote:
> > John, thanks for the response.
> >
> > Understood. I was just wondering if anyone had seen differences, as my boss
> > said he thought Symantec slowed SQL down as compared to McAfee . . . .
> > --
> > David
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If set up correctly most AV software should not have too much of an impact.
> > > The main cause of performance issues tends to be filter drivers slowing down
> > > the I/O.
> > >
> > > John
> > >
> > > "dkon" wrote:
> > >
> > > > Thanks for the info John.
> > > >
> > > > I was just wondering if anyone had seen performance hits from one app to
> > > > another, as I am currently lucky enough to be able to choose which AV
> > > > solution to go with.
> > > > --
> > > > David
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > In general the AV software on your server is decided by the company security
> > > > > policy rather than having a multitude of different products. You may have
> > > > > bulk licences to cover all servers. Server versions of AV products may differ
> > > > > also from Client versions.
> > > > >
> > > > > Guidelines can be found at http://support.microsoft.com/kb/309422/en-us
> > > > >
> > > > > "dkon" wrote:
> > > > >
> > > > > > Greetings all,
> > > > > >
> > > > > > General question: I'm used to Symantec, but my boos says McAfee is better
> > > > > > for SQL . . . any help?
> > > > > > --
> > > > > > David|||"dkon" <dkon@.discussions.microsoft.com> wrote in message
news:119E80D6-6862-4519-B168-14A3445B3033@.microsoft.com...
> Greetings all,
> General question: I'm used to Symantec, but my boos says McAfee is better
> for SQL . . . any help?
> --
> David
I wouldn't really care since I wouldn't use either on the database files.
(in fact in many environments, I might not use any).
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Monday, March 19, 2012

HELP : Remote query from 2005 to 2000 returns wrong recordset when

Hi there
I came across this bug and could not find a solution, and I'm blocked.
Can anyone help ?
I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
SP4) server.
When I make a remote query from a (linked) SQL 2005 server (SP1 or
SP2), the returned recordset is totally wrong (rows are missing).
The problem does not occur between two SQL 2000, nor between two SQL
2005.Eric,
Can you define the condition of the missing rows better? That might give a
clue.
One suggestion is to compare the collations of the SQL 2000 server and
database with the collations of the SQL 2005 server and database. If that
is a problem, you might be able to resolve it with a COLLATE clause in the
query.
RLF
<EricBello@.eurofins.com> wrote in message
news:9c42bd9d-8d35-4370-bcd5-293830ac27f7@.l1g2000hsa.googlegroups.com...
> Hi there
> I came across this bug and could not find a solution, and I'm blocked.
> Can anyone help ?
> I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
> SP4) server.
> When I make a remote query from a (linked) SQL 2005 server (SP1 or
> SP2), the returned recordset is totally wrong (rows are missing).
> The problem does not occur between two SQL 2000, nor between two SQL
> 2005.
>|||Here is the script to reproduce the problem
At SQL 2000 side (SP3 or SP4) :
--cleanup
--DROP TABLE MyPackagesTranslations
--DROP TABLE MyPackages
GO
USE pubs
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
-- Create the table MyPackages,
CREATE TABLE [dbo].[MyPackages](
[packageIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[packageSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackages_packageSqlId] DEFAULT (1121),
[packageName] [nvarchar](60) NULL,
[isDeleted] [bit] NOT NULL CONSTRAINT
[DF_MyPackages_isDeleted_Default] DEFAULT (0x00),
CONSTRAINT [PK_MyPackages] PRIMARY KEY CLUSTERED
(
[packageIncId] ASC,
[packageSqlId] ASC
)
)
GO
-- Create linked table MyPackagesTranslations
CREATE TABLE [dbo].[MyPackagesTranslations](
[packageTranslationIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION
NOT NULL,
[packageTranslationSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackagesTranslations_packageTranslationSqlId] DEFAULT (1121),
[packageIncId] [int] NOT NULL,
[packageSqlId] [smallint] NOT NULL,
[packageTranslationName] [nvarchar](60) NULL,
CONSTRAINT [PK_MyPackagesTranslations] PRIMARY KEY CLUSTERED
(
[packageTranslationIncId] ASC,
[packageTranslationSqlId] ASC
)
)
GO
-- ... with a foreign key between them
ALTER TABLE [dbo].[MyPackagesTranslations] WITH NOCHECK ADD CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
FOREIGN KEY([packageIncId], [packageSqlId])
REFERENCES [dbo].[MyPackages] ([packageIncId], [packageSqlId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[MyPackagesTranslations] CHECK CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
GO
-- Insert some data with "packageSqlId = 1"
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Foo')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Bar')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Cat')
INSERT MyPackagesTranslations (packageIncId, packageSqlId,
packageTranslationName)
SELECT packageIncId, packageSqlId, 'Translated ' + packageName
FROM dbo.MyPackages
GO
-- Now Add a CHECK CONSTRAINT for MyPackages.packageSqlId = 1121
ALTER TABLE [dbo].[MyPackages] WITH NOCHECK ADD CONSTRAINT
[CK_MyPackages] CHECK NOT FOR REPLICATION
(([packageSqlId] = 1121))
GO
ALTER TABLE [dbo].[MyPackages] CHECK CONSTRAINT [CK_MyPackages]
GO
=> And now at SQL 2005 side (SP1 or SP2)
Execute the query :
-- Now connect to another server with SQL 2005 SP1 or SP2, and launch
the remote query
-- (first verify you have a linked server)
SELECT count(*)
from <remoteserver>.pubs.dbo.MyPackages pack
INNER JOIN <remoteserver>.pubs.dbo.MyPackagesTranslations trans
ON (pack.packageIncId=trans.packageIncId and
pack.packageSqlId=trans.packageSqlId)
where pack.isDeleted=0x0
--=> returns "0" (KO) : should be "3"|||The collations are the same.
I really suspect a bug like the one on the optimizer that considered
some constraints trustworthy whereas they were "not for replication".|||I drilled down to the execution plan, and I can clearly see the
optimizer including the condition "where ...packageSqlId = 1121",
which shows it actually considers the table constraint
"trustworthy" (or sth alike) and restricts the recordset accordingly.|||The missing rows are those that don't comply with the TABLE CONSTRAINT.|||Eric,
Sorry, I really don't know. I used your repro script and get the same
results that you do.
Other things that I tried, on the SQL 2000 server create the RemoteServer
linked server pointing back to itself. Here were my results trying this:
SQLOLEDB connection: Returned 3 as desired.
SQLNCLI connection: I get the following error
OLE DB error trace [Non-interface error: Column 'packageTranslationName'
(compile-time ordinal 5) of object '"pubs"."dbo"."MyPackagesTranslations"'
was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
And, no matter what I try from the SQL 2005 server, I always get 0 returned.
In addition to SQLNCLI connect, I tried to script in a SQLOLEDB connect, but
it converted to SQLNCLI. I tried creating the remote server using the SQL
Server radio button, also. No change in query result.
Is SQLNCLI at the root of the problem? I don't know enough to say.
So, I would say that you have probably found a bug that you could choose to
report to Microsoft. At least a bit of googling did not help me find an
answer.
RLF
<erbellico@.gmail.com> wrote in message
news:06af6c0c-6f36-4e3d-b671-64a27caa12d2@.s12g2000prg.googlegroups.com...
> The missing rows are those that don't comply with the TABLE CONSTRAINT.

Sunday, February 19, 2012

Hellp Please: Running 2 projects under deferent crystal versions

Please need help.

I have spent 2 days and still did not find a solution. I have 2 differnt web applications. One has reports contracted unde Cr 10 and the other Cr 11. For some reasons i can not make it run together. I can install both version and I see all files of both versions in GAC but only one runs at a time if I unintsall lets say Cr 11 and install 10 then only application running under 10 will run, then if I install 11 application under 11 will only run. So it looks like when I install Cr it overrides some file. I cheked machine.cfg nothing in there. I do not have web.cfg in default web site.

This is the error I get:
[COMException (0x80040154): Class not registered - DTSConnection]
CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocumentClass.Open(Object& DocumentPath, Int32 Options) +0
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.Open(Object& DocumentPath, Int32 Options) +72
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.EnsureDocumentIsOpened() +218

I searched entire web, nothinig helpfull.

If anybody knwos how to solve thise issue, i will be very very thankfullI got some more insight on teh problem that gave me an idea but still no answr. I can set up both web application ussing 2 different crystal versions either on deferent web sistes on the same PC like one on default and the other on some other site and it works and also it works when I set up both of then on other web site than default one. It looks like default site using some regestry or cfg file settings on higher hierarchy level that overrerides web.cfg in virtual dircetory. I cheked machine.cfg what did not seem to have any info for crystal and also I do not have any default web.cfg in default site, at least I do not see it.

Anybody, ideas, please!!!|||See if you find solution here
http://support.businessobjects.com/

HelloWorldCS Sample

Is anyone is familiar with the HelloWorldCS solution that is available for download at www.SQLServiceBroker.com?

If so, can someone tell me what this method does?
-
private void BuildCallbackMap()
{
Type t = GetType();
//this member variable is for retrieving a collection of key/value pairs
//BrokerMethodAttribute is a base class for all custom attributes
m_dispatchMap = new Dictionary<BrokerMethodAttribute, MethodInfo>();
//get all public methods of Instance type...
MethodInfo[] methodInfoArray = t.GetMethods(BindingFlags.Public | BindingFlags.Instance);

//iterate through the collection...
foreach (MethodInfo methodInfo in methodInfoArray)
{
object[] attributes = methodInfo.GetCustomAttributes(typeof(BrokerMethodAttribute), true);
foreach (BrokerMethodAttribute statefulTransition in attributes)
{
BrokerMethodAttribute statelessTransition =
new BrokerMethodAttribute(statefulTransition.Contract, statefulTransition.MessageType);
if (m_dispatchMap.ContainsKey(statefulTransition) || m_dispatchMap.ContainsKey(statelessTransition))
{
string exceptionMessage = "Method '" + methodInfo.Name +
"' redefines a handler for message type '" + statefulTransition.MessageType + "'";
if (statefulTransition.State != -1)
exceptionMessage += " in state " + statefulTransition.State;

throw new NotSupportedException(exceptionMessage);
}
m_dispatchMap[statefulTransition] = methodInfo;
}
}
}

-
Sorry about the TERRIBLE formatting.

I'm just a little confused as to what this is doing.

thanks.

Doug

Service class is a general class designed to provide all needs of service handling. If you want to use it for particular service you need to create a new class inherited from Service (as it is done in HelloWorldCS) and add handler methods that will be called upon receiving specific message type on a specific contract.
To accomplish this task (calling handler methods) Service class uses its own attribute, BrokerMethodAttribute. The attibute is designed to mark these handlers so that Service can find out what method it should call for pair (contract, message type).
So we came to your question: BuildCallbackMap() is the method that uses reflection to parse its own class (derived from Service) for methods that have BrokerMethodAttribute and builds a map (contract, message type, service state) -> (method that handles that stuff) for them so that it would be easier to find a correct handler later. If it finds two methods that are marked to handle the same thing - throws an exception.
Please note that your implementation of Service may use a service state (just a property of Service) to process messages (calling different handlers) depending on service state's value.

Hope it will help you :)
|||

Ok that sheds some light on the problem.

However, I still am confused on this bit:
-
foreach (MethodInfo methodInfo in methodInfoArray)
{
object[] attributes = methodInfo.GetCustomAttributes(typeof(BrokerMethodAttribute), true);
foreach (BrokerMethodAttribute statefulTransition in attributes)
{
BrokerMethodAttribute statelessTransition = new BrokerMethodAttribute(statefulTransition.Contract, statefulTransition.MessageType);
if (m_dispatchMap.ContainsKey(statefulTransition) || m_dispatchMap.ContainsKey(statelessTransition))
{
string exceptionMessage = "Method '" + methodInfo.Name +
"' redefines a handler for message type '" + statefulTransition.MessageType + "'";
if (statefulTransition.State != -1)
exceptionMessage += " in state " + statefulTransition.State;

throw new NotSupportedException(exceptionMessage);
}
m_dispatchMap[statefulTransition] = methodInfo;
}
}

1). I understand the foreach() statment.
2). This line is not clear:

object[] attributes = methodInfo.GetCustomAttributes(typeof(BrokerMethodAttribute), true);
3). This loop is unclear:

foreach (BrokerMethodAttribute statefulTransition in attributes)
{}

4). This line is unclear:

BrokerMethodAttribute statelessTransition = new BrokerMethodAttribute(statefulTransition.Contract, statefulTransition.MessageType);

5). And, if all of that weren't enough...This is confusing as well:

if (m_dispatchMap.ContainsKey(statefulTransition) || m_dispatchMap.ContainsKey(statelessTransition))

6). What is a stateful/stateless transition?

7). Is someone publishing some help files, tutorials or books on this subject soon?

...Suffice to say, the whole Service Broker thing is kinda kicking me in the head at this point...

Thanks for your help!

God Bless...

Doug

|||

drdexter33 wrote:

1). I understand the foreach() statment.


good :). Just to make it clear: we are walking through each public instance (not static) method (stored in variable methodInfo) of our derived-from-Service class.

drdexter33 wrote:

2). This line is not clear:
object[] attributes = methodInfo.GetCustomAttributes(typeof(BrokerMethodAttribute), true);


GetCustomAttributes returns method's attributes of the specified type, in our case we get all BrokerMethodAttribute attributes of the method: BrokerMethodAttribute allows multiple instances of itself for each method that means that single method can handle several message types/contracts/service states.

drdexter33 wrote:

3). This loop is unclear:
foreach (BrokerMethodAttribute statefulTransition in attributes)
{}


So... we got an array of BrokerMethodAttribute attributes, and we are looping through each of them (stored in variable statefulTransition).

drdexter33 wrote:

6). What is a stateful/stateless transition?


I put this question here because I guess it confuses you mostly. As I mentioned in the previous post you can use or not use the state of the service. By using the state I mean that if you need your service to react on the same message types differently depending on lets say receiving some special messages or something like that than you may find service's state useful. To react differently depending on service state you can mark you message handler methods with BrokerMethodAttribute specifying not only the message type and contract it handles, but also the state of the service for which it it should be called. So that now you can provide different handlers for the same message type and contract for different states.
So... statefulTransition means that this attibute may have a state for which it should be active, while statelessTransition declares the same message type/contract, but doesn't have a defined state for sure.

drdexter33 wrote:

4). This line is unclear:
BrokerMethodAttribute statelessTransition = new BrokerMethodAttribute(statefulTransition.Contract, statefulTransition.MessageType);


Here we just create a "copy" of statefulTransition removing information about state.

drdexter33 wrote:

5). And, if all of that weren't enough...This is confusing as well:
if (m_dispatchMap.ContainsKey(statefulTransition) || m_dispatchMap.ContainsKey(statelessTransition))


OK, finally we came to the map: one of the features we have to guarantee is that in any case there should be only one handler of the message. So this line checks for existence of other methods (already included in the map) that are marked to handle the same message type/contract/state as current method. If we have a defined state in the attribute then: map shouldn't contain an attribute with the same message type/contract/state, and also it shouldn't contain "stateless" attribute that is marked to handle all states for message type/contract. That's why the map is tested twice for statefulTransition and statelessTransition.

drdexter33 wrote:

7). Is someone publishing some help files, tutorials or books on this subject soon?


Don't know, I'm just a programmer (actually software architector :) ), and I just looked through HelloWorldCS earlier.

|||There's a lot of Service Broker information available in Books on Line. In addition, Rushi maintains a blog with tips, samples, and techniques:

http://blogs.msdn.com/rushidesai/

There are some white papers around:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp
There is an independant forum

http://www.sqlservicebroker.com/forums/

and there is at least one Service Broker book available:

http://www.amazon.com/gp/product/1932577203/002-7167452-4906463?v=glance&n=283155&n=507846&s=books&v=glance