membership, roles and login controls with sql server 2000

hello, I have developed a website using asp.net 2.0 and sql server 2005 express edition. I have used the built-in membership, roles and login controls in my website. now i need to change the database server. i have to use sql server 2000 instead. as asp.net 2.0 uses the ASPNETDB.mdf for member,roles and login controls, so how can i do it? i can easily export the database from 2005 to 2000 but how my application controls will know where to look at for those data. I am clueless. please help me.

thanks in advance.

See if this link at ASPNet101.com helps:

http://aspnet101.com/aspnet101/tutorials.aspx?id=63

|||thanks a lot. this is exactly what i was looking for

Membership Timeline Spanning

Span example:
--M--
--Rx--
Needs to b converted to this:
--M--|--M & Rx--|--Rx--
/* What current base data displays
MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
-- -- -- -- --
27 20050101 20050412 Y N
27 20050201 20050813 N Y
27 20050603 99991231 Y N
*/
/* Should be converted as this?
MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
-- -- -- -- --
27 20050101 20050131 Y N
27 20050201 20050412 Y Y
27 20050413 20050602 N Y
27 20050603 20050813 Y Y
27 20050814 99991231 Y N
*/
DDL to create the table and then some sample data
CREATE TABLE [dbo].[ODS_BLK_Member] (
[BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID,
CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY,
MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
PLN_CAT_PRF, FILE_IDENTIFIER)
SELECT
'123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','1'
,'5','20060306','99991231','1','1','1','
A','B','B','1','B','Y','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','1'
,'5','20050101','20050912','1','1','2','
A','B','B','1','B','N','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','1'
,'5','20050310','20051120','1','1','2','
A','B','B','1','B','Y','Y','N','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050101','20050412','1','1','27
','A','B','B','1','B','N','Y','Y','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050201','20050813','1','1','27
','A','B','B','1','B','Y','Y','N','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3','
3','7','20050603','99991231','1','1','27
','A','B','B','1','B','N','Y','Y','Y
','2001','2001','12','12','M'
UNION
SELECT
'123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','2'
,'6','20060101','99991231','1','1','4','
A','B','B','1','B','N','Y','Y','Y','
2001','2001','12','12','M'
UNION
SELECT
'123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','2'
,'6','20060301','99991231','1','1','4','
A','B','B','1','B','Y','Y','N','Y','
2001','2001','12','12','M'
If Itzak is out there I would appreciate your magic.Forgot to mention this is a Medical and Pharmacy benefits table.
"FredG" wrote:

> Span example:
> --M--
> --Rx--
> Needs to b converted to this:
> --M--|--M & Rx--|--Rx--
> /* What current base data displays
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050412 Y N
> 27 20050201 20050813 N Y
> 27 20050603 99991231 Y N
> */
>
> /* Should be converted as this?
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050131 Y N
> 27 20050201 20050412 Y Y
> 27 20050413 20050602 N Y
> 27 20050603 20050813 Y Y
> 27 20050814 99991231 Y N
> */
> DDL to create the table and then some sample data
> CREATE TABLE [dbo].[ODS_BLK_Member] (
> [BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID
,
> CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY
,
> MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
> COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
> MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
> MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
> PLN_CAT_PRF, FILE_IDENTIFIER)
> SELECT
> '123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','
1','5','20060306','99991231','1','1','1'
,'A','B','B','1','B','Y','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050101','20050912','1','1','2'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050310','20051120','1','1','2'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050101','20050412','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050201','20050813','1','1','
27','A','B','B','1','B','Y','Y','N',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050603','99991231','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060101','99991231','1','1','4'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060301','99991231','1','1','4'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> If Itzak is out there I would appreciate your magic.
>
>|||Fred,
This is an example for a task I'd probably end up using a cursor for. I
might be wrong, but experience with similar problems and intuition tells me
that set-based solutions using existing features in the product (both 2000
and 2005) are going to be more expensive than the cursor solution because of
excessive I/O. Using features from ANSI SQL:2003 that were not yet
implemented in SQL Server (particularly OVER clause with ORDER BY for
aggregations) there might be a set-based solution that would run faster than
the cursor. But that's for future versions of SQL Server... :-)
Anyhow, I'd declare a cursor based on the following query:
SELECT MEM_ID, COV_BEGIN_DT AS DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
0 AS BEGIN_END
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_BEGIN_DT
UNION ALL
SELECT MEM_ID, COV_END_DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
1
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_END_DT
ORDER BY MEM_ID, DT, BEGIN_END;
The idea is to produce a series of events in chronological order: events
that increase or decrease the count of benefit conditions.
Here's what the cursor's query produces:
MEM_ID DT MED_CHANGE PHRM_CHANGE BEGIN_END
-- -- -- -- --
1 20060306 1 1 0
1 99991231 -1 -1 1
2 20050101 1 0 0
2 20050310 0 1 0
2 20050912 -1 0 1
2 20051120 0 -1 1
27 20050101 1 0 0
27 20050201 0 1 0
27 20050412 -1 0 1
27 20050603 1 0 0
27 20050813 0 -1 1
27 99991231 -1 0 1
4 20060101 1 0 0
4 20060301 0 1 0
4 99991231 -1 -1 1
The cursor should scan the events in chronological order and produce a new
period whenever there's a change in the state of benefits.
I just scribbled the following code as an example of how such cursor code
might look like. But note that I didn't bother to test it thoroughly, and as
with most code snippets that contain more than 0 characters, this one most
probably has bugs. So please use this just to get the general idea of the
logic, but make sure you examine, understand, and test it thoroughly, and
make the required revisions before putting it into production.
SET NOCOUNT ON;
DECLARE
@.MEM_ID AS VARCHAR(22),
@.DT AS CHAR(8),
@.MED_CHANGE AS INT,
@.PHRM_CHANGE AS INT,
@.BEGIN_END AS INT,
@.COV_BEGIN_DT AS CHAR(8),
@.MED_CNT AS INT,
@.PHRM_CNT AS INT,
@.PRV_MEM_ID AS VARCHAR(22),
@.PRV_MED_CNT AS INT,
@.PRV_PHRM_CNT AS INT;
DECLARE @.Results TABLE
(
MEM_ID VARCHAR(22) NOT NULL,
COV_BEGIN_DT CHAR(8) NOT NULL,
COV_END_DT CHAR(8) NOT NULL,
MED_BEN_IND CHAR(1) NOT NULL,
PHRM_BEN_IND CHAR(1) NOT NULL
--PRIMARY KEY(MEM_ID, COV_BEGIN_DT)
);
DECLARE CEvents CURSOR FAST_FORWARD FOR
SELECT MEM_ID, COV_BEGIN_DT AS DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
0 AS BEGIN_END
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_BEGIN_DT
UNION ALL
SELECT MEM_ID, COV_END_DT,
SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
1
FROM ODS_BLK_Member
GROUP BY MEM_ID, COV_END_DT
ORDER BY MEM_ID, DT, BEGIN_END;
OPEN CEvents;
SELECT
@.PRV_MEM_ID = NULL,
@.PRV_MED_CNT = 0,
@.PRV_PHRM_CNT = 0;
FETCH NEXT FROM CEvents
INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- New MEM_ID or new period; init variables
IF @.MEM_ID <> @.PRV_MEM_ID
OR @.PRV_MEM_ID IS NULL
OR @.PRV_MED_CNT + @.PRV_PHRM_CNT = 0
SELECT
@.MED_CNT = @.MED_CHANGE,
@.PHRM_CNT = @.PHRM_CHANGE,
@.COV_BEGIN_DT = @.DT;
ELSE
-- New period
BEGIN
SELECT
@.MED_CNT = @.MED_CNT + @.MED_CHANGE,
@.PHRM_CNT = @.PHRM_CNT + @.PHRM_CHANGE;
-- Change in MED or PHRM benefit state
-- means close of existing period and open of new one
IF SIGN(@.MED_CNT) <> SIGN(@.PRV_MED_CNT)
OR SIGN(@.PHRM_CNT) <> SIGN(@.PRV_PHRM_CNT)
BEGIN
INSERT INTO @.Results
(MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND)
VALUES(
@.MEM_ID,
@.COV_BEGIN_DT,
CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END-1, @.DT), 112),
CASE WHEN @.PRV_MED_CNT > 0 THEN 'Y' ELSE 'N' END,
CASE WHEN @.PRV_PHRM_CNT > 0 THEN 'Y' ELSE 'N' END);
IF @.MED_CNT + @.PHRM_CNT > 0
SET @.COV_BEGIN_DT =
CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END, @.DT), 112);
END
END
SELECT
@.PRV_MEM_ID = @.MEM_ID,
@.PRV_MED_CNT = @.MED_CNT,
@.PRV_PHRM_CNT = @.PHRM_CNT
FETCH NEXT FROM CEvents
INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
END
CLOSE CEvents;
DEALLOCATE CEvents;
SELECT * FROM @.Results;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:67C2CD4A-D517-418A-A11B-BE53773B831A@.microsoft.com...
> Span example:
> --M--
> --Rx--
> Needs to b converted to this:
> --M--|--M & Rx--|--Rx--
> /* What current base data displays
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050412 Y N
> 27 20050201 20050813 N Y
> 27 20050603 99991231 Y N
> */
>
> /* Should be converted as this?
> MEM_ID COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND
> -- -- -- -- --
> 27 20050101 20050131 Y N
> 27 20050201 20050412 Y Y
> 27 20050413 20050602 N Y
> 27 20050603 20050813 Y Y
> 27 20050814 99991231 Y N
> */
> DDL to create the table and then some sample data
> CREATE TABLE [dbo].[ODS_BLK_Member] (
> [BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID,
> MEM_ID,
> CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY,
> MEM_COUNTY,
> MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, [GROUP], SUBGROUP,
> COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST,
> MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND,
> MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC,
> PLN_CAT_PRF, FILE_IDENTIFIER)
> SELECT
> '123','012001','1','1','ODS','19820706',
'19348','US','FIL','F','NON','1','
1','5','20060306','99991231','1','1','1'
,'A','B','B','1','B','Y','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050101','20050912','1','1','2'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','2','2','ODS','19861223',
'19348','US','FIL','M','NON','1','
1','5','20050310','20051120','1','1','2'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050101','20050412','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050201','20050813','1','1','
27','A','B','B','1','B','Y','Y','N',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','27','27','ODS','19471110
','79935','US','FIL','F','NON','3'
,'3','7','20050603','99991231','1','1','
27','A','B','B','1','B','N','Y','Y',
'Y','2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060101','99991231','1','1','4'
,'A','B','B','1','B','N','Y','Y','Y'
,'2001','2001','12','12','M'
> UNION
> SELECT
> '123','012001','4','4','ODS','19740314',
'19103','US','FIL','F','NON','2','
2','6','20060301','99991231','1','1','4'
,'A','B','B','1','B','Y','Y','N','Y'
,'2001','2001','12','12','M'
> If Itzak is out there I would appreciate your magic.
>
>|||Hi Itzik, First, my applogizes for butchering your first name, I feel ashame
d.
Secondly, thank you very much for taking the time to respond.
When I first read that you have steered away from set based solution to
cursor based I was a suprised. All of your writings clearly state to use
cursors as a last resort. In any event, you are the MASTER of T-SQL and I
will adher to your suggestions.
The table in question contains 5 million rows, using a cursor may just take
a very long time. However, I will give it a go at it.
If you see Kalen Delaney and Andrew Kelly tell them I said hello.
Alfredo Giotti
"Itzik Ben-Gan" wrote:

> Fred,
> This is an example for a task I'd probably end up using a cursor for. I
> might be wrong, but experience with similar problems and intuition tells m
e
> that set-based solutions using existing features in the product (both 2000
> and 2005) are going to be more expensive than the cursor solution because
of
> excessive I/O. Using features from ANSI SQL:2003 that were not yet
> implemented in SQL Server (particularly OVER clause with ORDER BY for
> aggregations) there might be a set-based solution that would run faster th
an
> the cursor. But that's for future versions of SQL Server... :-)
> Anyhow, I'd declare a cursor based on the following query:
> SELECT MEM_ID, COV_BEGIN_DT AS DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
> 0 AS BEGIN_END
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_BEGIN_DT
> UNION ALL
> SELECT MEM_ID, COV_END_DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> 1
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_END_DT
> ORDER BY MEM_ID, DT, BEGIN_END;
> The idea is to produce a series of events in chronological order: events
> that increase or decrease the count of benefit conditions.
> Here's what the cursor's query produces:
> MEM_ID DT MED_CHANGE PHRM_CHANGE BEGIN_END
> -- -- -- -- --
> 1 20060306 1 1 0
> 1 99991231 -1 -1 1
> 2 20050101 1 0 0
> 2 20050310 0 1 0
> 2 20050912 -1 0 1
> 2 20051120 0 -1 1
> 27 20050101 1 0 0
> 27 20050201 0 1 0
> 27 20050412 -1 0 1
> 27 20050603 1 0 0
> 27 20050813 0 -1 1
> 27 99991231 -1 0 1
> 4 20060101 1 0 0
> 4 20060301 0 1 0
> 4 99991231 -1 -1 1
> The cursor should scan the events in chronological order and produce a new
> period whenever there's a change in the state of benefits.
> I just scribbled the following code as an example of how such cursor code
> might look like. But note that I didn't bother to test it thoroughly, and
as
> with most code snippets that contain more than 0 characters, this one most
> probably has bugs. So please use this just to get the general idea of the
> logic, but make sure you examine, understand, and test it thoroughly, and
> make the required revisions before putting it into production.
> SET NOCOUNT ON;
> DECLARE
> @.MEM_ID AS VARCHAR(22),
> @.DT AS CHAR(8),
> @.MED_CHANGE AS INT,
> @.PHRM_CHANGE AS INT,
> @.BEGIN_END AS INT,
> @.COV_BEGIN_DT AS CHAR(8),
> @.MED_CNT AS INT,
> @.PHRM_CNT AS INT,
> @.PRV_MEM_ID AS VARCHAR(22),
> @.PRV_MED_CNT AS INT,
> @.PRV_PHRM_CNT AS INT;
> DECLARE @.Results TABLE
> (
> MEM_ID VARCHAR(22) NOT NULL,
> COV_BEGIN_DT CHAR(8) NOT NULL,
> COV_END_DT CHAR(8) NOT NULL,
> MED_BEN_IND CHAR(1) NOT NULL,
> PHRM_BEN_IND CHAR(1) NOT NULL
> --PRIMARY KEY(MEM_ID, COV_BEGIN_DT)
> );
> DECLARE CEvents CURSOR FAST_FORWARD FOR
> SELECT MEM_ID, COV_BEGIN_DT AS DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS MED_CHANGE,
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN +1 ELSE 0 END) AS PHRM_CHANGE,
> 0 AS BEGIN_END
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_BEGIN_DT
> UNION ALL
> SELECT MEM_ID, COV_END_DT,
> SUM(CASE WHEN MED_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> SUM(CASE WHEN PHRM_BEN_IND = 'Y' THEN -1 ELSE 0 END),
> 1
> FROM ODS_BLK_Member
> GROUP BY MEM_ID, COV_END_DT
> ORDER BY MEM_ID, DT, BEGIN_END;
> OPEN CEvents;
> SELECT
> @.PRV_MEM_ID = NULL,
> @.PRV_MED_CNT = 0,
> @.PRV_PHRM_CNT = 0;
> FETCH NEXT FROM CEvents
> INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- New MEM_ID or new period; init variables
> IF @.MEM_ID <> @.PRV_MEM_ID
> OR @.PRV_MEM_ID IS NULL
> OR @.PRV_MED_CNT + @.PRV_PHRM_CNT = 0
> SELECT
> @.MED_CNT = @.MED_CHANGE,
> @.PHRM_CNT = @.PHRM_CHANGE,
> @.COV_BEGIN_DT = @.DT;
> ELSE
> -- New period
> BEGIN
> SELECT
> @.MED_CNT = @.MED_CNT + @.MED_CHANGE,
> @.PHRM_CNT = @.PHRM_CNT + @.PHRM_CHANGE;
> -- Change in MED or PHRM benefit state
> -- means close of existing period and open of new one
> IF SIGN(@.MED_CNT) <> SIGN(@.PRV_MED_CNT)
> OR SIGN(@.PHRM_CNT) <> SIGN(@.PRV_PHRM_CNT)
> BEGIN
> INSERT INTO @.Results
> (MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND)
> VALUES(
> @.MEM_ID,
> @.COV_BEGIN_DT,
> CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END-1, @.DT), 112),
> CASE WHEN @.PRV_MED_CNT > 0 THEN 'Y' ELSE 'N' END,
> CASE WHEN @.PRV_PHRM_CNT > 0 THEN 'Y' ELSE 'N' END);
> IF @.MED_CNT + @.PHRM_CNT > 0
> SET @.COV_BEGIN_DT =
> CONVERT(VARCHAR(8), DATEADD(day, @.BEGIN_END, @.DT), 112);
> END
> END
> SELECT
> @.PRV_MEM_ID = @.MEM_ID,
> @.PRV_MED_CNT = @.MED_CNT,
> @.PRV_PHRM_CNT = @.PHRM_CNT
> FETCH NEXT FROM CEvents
> INTO @.MEM_ID, @.DT, @.MED_CHANGE, @.PHRM_CHANGE, @.BEGIN_END;
> END
> CLOSE CEvents;
> DEALLOCATE CEvents;
> SELECT * FROM @.Results;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:67C2CD4A-D517-418A-A11B-BE53773B831A@.microsoft.com...
>
>|||Hi Fred,
No need to apologize. :-)
It's true that for the most part, set-based solutions are faster than
cursor-based ones. There's a lot of overhead involved with the
record-by-record manipulation of the cursor. However, there are types of
problems where using cursors, your code ends up incurring much less I/O than
the set-solution. Remember that a cursor can rely on sorted data while set
manipulation cannot.
Take running aggregates as an example; the set-based solutions have an
O(N^2) complexity since portions of the data need to be rescanned in order
to calculate the running aggregates. The cursor on the other hand performs a
single scan of the data.
In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
option). It is really brilliant, and I wonder if the designers of the
feature themselves knew how profound it is. I believe this option to be the
bridge between cursors and sets; sort of the holy grail of SQL. :-)
You can perform ordered calculations without forcing any particular order of
the output, and without involving the cursor overhead. The technology
already exists in the SQL Server 2005 engine; it's just that OVER + ORDER BY
with aggregates was not implemented yet, and this combination is where the
real power lies.
Anyhow, today, set-based solutions to some problems (including some
solutions to temporal problems) have complexities that end up being much
more expensive than cursor solutions.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:9228A601-9A06-43B8-8AA1-7CE4AA5B91C1@.microsoft.com...
> Hi Itzik, First, my applogizes for butchering your first name, I feel
> ashamed.
> Secondly, thank you very much for taking the time to respond.
> When I first read that you have steered away from set based solution to
> cursor based I was a suprised. All of your writings clearly state to use
> cursors as a last resort. In any event, you are the MASTER of T-SQL and I
> will adher to your suggestions.
> The table in question contains 5 million rows, using a cursor may just
> take
> a very long time. However, I will give it a go at it.
> If you see Kalen Delaney and Andrew Kelly tell them I said hello.
> Alfredo Giotti
> "Itzik Ben-Gan" wrote:
>|||"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net|||Here's another method, you'll need SQL Server 2005
and a calendar table as per
http://www.aspfaq.com/show.asp?id=2519
Ensure the calendar covers all dates in your data.
(Do not put '99991231' into the calendar!)
I don't know how the performance of this compares
to the cursor based solution already posted.
WITH Daily(MEM_ID,dt,MED_PHRM)
AS
(SELECT m.MEM_ID,
t.dt,
MAX(CASE m.MED_BEN_IND WHEN 'Y' THEN 1 ELSE 0 END) +
MAX(CASE m.PHRM_BEN_IND WHEN 'Y' THEN 2 ELSE 0 END)
FROM Calendar t
INNER JOIN ODS_BLK_Member m ON CAST(t.dt AS DATETIME) BETWEEN
CAST(m.COV_BEGIN_DT AS DATETIME)
AND
CAST(m.COV_END_DT AS DATETIME)
GROUP BY t.dt,m.MEM_ID),
RankedDaily(MEM_ID,dt,MED_PHRM,RankDiff)
AS
(SELECT MEM_ID,
dt,
MED_PHRM,
DATEADD(day,-RANK() OVER (PARTITION BY MEM_ID,MED_PHRM ORDER BY
dt),dt)
FROM Daily)
SELECT MEM_ID,
CONVERT(CHAR(8),MIN(dt),112) AS COV_BEGIN_DT,
CASE WHEN MAX(dt) = (SELECT MAX(dt) FROM Calendar)
THEN '99991231'
ELSE CONVERT(CHAR(8),MAX(dt),112)
END AS COV_END_DT,
CASE WHEN MED_PHRM % 2 <> 0 THEN 'Y' ELSE 'N' END AS
MED_BEN_IND,
CASE WHEN MED_PHRM / 2 <> 0 THEN 'Y' ELSE 'N' END AS
PHRM_BEN_IND
FROM RankedDaily
GROUP BY MEM_ID,MED_PHRM,RankDiff
ORDER BY 1,2;
You can create a simplified calendar using this
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED
)
DECLARE @.dt SMALLDATETIME
SET @.dt = '20040101'
WHILE @.dt < '20080101'
BEGIN
INSERT dbo.Calendar(dt) SELECT @.dt
SET @.dt = @.dt + 1
END|||"Truly, you have A dizzying intellect." 8-)
Doesn't change the way I feel about OVER + ORDER BY, though.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Steve Dassin" <rac4sqlnospam@.net> wrote in message
news:u8%23pGNSXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> "Itzik Ben-Gan" writes
> To quote Bob Dylan:
> 'I would not feel so alone if everyone where getting stoned':)
> Yes I agree with you in principal.The 'real' paradign shift has
> little to do with the clr and everything to do with exploding
> the perverted myth of the exclusivity of'set based' constructs.
> The idea one can legitimately think in terms of rows without being
> labelled an sql Jodus has arrived.But calling this windowing a
> 'profound' kind of insight and bestowing on the designers the aura
> of 'brilliance' would be a mistake.It is at best an example of
> 'better late than never'.Calling this state of affairs profound
> would surely overshadow the accountability that the commericial
> database world should be held to.The fact that this mindset change
> has taken almost 30 years should be seen as appalling.Neo-cons of
> the industry had hijacked sense with sql creationism and marketing.
> WMD was replaced with client/server and a tiered approach.A theory
> was misapplied to a retrival mechanism and unapplied to a design
> mechanism.An approach that vendors marketted that allowed them to
> hide both their intellectual and creative shortcomings.Their db
> failures made for the 'client'.And now the clr in the db has replaced
> the client.And of course the dreaded cursor.This demanded regime
> change and the field was bankrupted for 30 years.For this we are to
> praise Ceasar?I think not.
> It is interesting to look at the fanfare that vendors are using
> to usher in this new paradign.In their documentation Oracle refers
> to their analytic functions in windows as an example of
> 'data densification'.This phrase is supposed to illustrate the
> flip side of the Group By.It was obviously borrowed from the idea
> of pacification,right out of the Pentagon.This is the best they could
> come up with?Any army of engineers berefit of language and concepts.
> Not to be out done,MS in its highly touted BOL offers the next best
> thing - absolutely Nothing!No explanations,no history no seqways.
> The functions are thrown around like so much spaghetti on a wall.
> If you write about concepts someone may quote you.MS needn't worry
> now.Least I be accused of favortism,IBM was too busy pleasing its
> shareholders to write anything intelligible.
> Finally,to your point about MS leaving out a large chunk of analytic
> material this was obviously not an oversight but just insurance
> that anything done with sql-99 could most definitly be easily ported
> to the competition.Less is more.Please!If they weren't sure of
> what they were doing they could have at least looked at Oracle
> which is probably about 8 years ahead.Or even looked at RAC to see what
> you and I are really talking about :)
> Interested readers maybe surprised that many of the ideas in sql
> analytics can be found in the SAS (Statistical Analysis System) Data
> Step...introduced about 20 years ago!Many of the Oracle extensions
> (First/Last) can also be found here.MySql allows mixing of variables
> and columns in a SELECT.Most of the analytics can be easily simulated
> in a single SELECT.And of course little RAC, way ahead of its time:)
> Some musing from:
> www.rac4sql.net
>
>|||We really are of the same mind! :)
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uhToaBfXGHA.1476@.TK2MSFTNGP03.phx.gbl...
> "Truly, you have A dizzying intellect." 8-)
> Doesn't change the way I feel about OVER + ORDER BY, though.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "Steve Dassin" <rac4sqlnospam@.net> wrote in message
> news:u8%23pGNSXGHA.3936@.TK2MSFTNGP05.phx.gbl...
>

membership on sql server 2000?

Hello,

I'm programming asp.net 2.0 with a mySql database.
Now I want to implement membership-features.
I consider converting mySql to SQL server 2000 because then I don't have to build a custom membershipprovider.
my question is: does membership work as good on sqlserver2000 as it does on sqlserver2005? (my host doesn't support sqlserver2005 (yet).

another question: can I expect problems converting the database from mysql to sqlserver2000?

leonvr:

my question is: does membership work as good on sqlserver2000 as it does on sqlserver2005? (my host doesn't support sqlserver2005 (yet).

Sure. I also use SQL2000 as backend database for membership. You can take a look at this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

leonvr:

another question: can I expect problems converting the database from mysql to sqlserver2000?

Sorry I have no idea on migrating mysql to sql2000.

sql

Membership GetAllUsers SP

Hello,

I am using the Membership application for my project. In the database (MS SQL 2005) the program created alot of SPs for the Membership, Roles, Profiles ...

Is there a function to return a list of users that are online? There is this functionaspnet_Membership_GetNumberOfUsersOnline andaspnet_Membership_GetAllUsers.

What i want is a Table with the usernames of all the users that are online in my application. No page index, just a big table with all the online users. I need to get all the usernames for another SP that i just build.

Any ideas?

To my knowledge there is not, but this would be a good one to add to the library. Realize that this would be a method that would be specific to the SQL or Custom Membership provider because AD would not be able to support this.

|||

I tried this, but i cant get it to return any rows, even tho atleast 4 people is logged in.

CREATEPROCEDURE [dbo].[aspnet_Membership_GetAllOnlineUsers]
@.ApplicationNamenvarchar(256),
@.MinutesSinceLastInActiveint,
@.CurrentTimeUtcdatetime
AS
BEGIN
DECLARE @.DateActivedatetime
SELECT @.DateActive=DATEADD(minute,-(@.MinutesSinceLastInActive), @.CurrentTimeUtc)

CREATETABLE #PageIndexForUsersNames
(
Usernamenvarchar(20)NOTNULL
)

INSERTINTO #PageIndexForUsersNames(Username)
SELECT u.Username
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId= a.ApplicationIdAND
LastActivityDate> @.DateActiveAND
a.LoweredApplicationName=LOWER(@.ApplicationName)AND
u.UserId= m.UserId
SELECT UsernameFROM #PageIndexForUsersNames
END

And then i tried to run the following:

EXEC aspnet_Membership_GetAllOnlineUsers
@.ApplicationName= N'//',
@.MinutesSinceLastInActive= 10,
@.CurrentTimeUtc='2007-11-15 18:45'

The applicationname did i take from the applications table.

Any ideas?

|||

Sorry that was the old, this is what i am trying:

aspnet_Membership_GetAllOnlineUsers
@.ApplicationNamenvarchar(256),
@.MinutesSinceLastInActiveint,
@.CurrentTimeUtcdatetime
AS
BEGIN
DECLARE @.DateActivedatetime
SELECT @.DateActive=DATEADD(minute,-(@.MinutesSinceLastInActive), @.CurrentTimeUtc)

SELECT u.Username
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId= a.ApplicationIdAND
LastActivityDate> @.DateActiveAND
a.LoweredApplicationName=LOWER(@.ApplicationName)AND
u.UserId= m.UserId
END

|||

You've specified a UTC time that is still in the future.

@.CurrentTimeUtc='2007-11-15 18:45' won't be for another 30 minutes.

|||

To be honest, I'm still not sure why the ASP.NET team decided to do it that way, it seems against best practices to have the client application trying to send in times when the window we're looking for is normally minutes, especially when it's extremely easy to make sure that we always use a single clock for reference:

aspnet_Membership_GetAllOnlineUsers
@.ApplicationNamenvarchar(256),
@.MinutesSinceLastInActiveint

AS
BEGIN
DECLARE @.DateActivedatetime
SELECT @.DateActive=DATEADD(minute,-(@.MinutesSinceLastInActive), GetUtcDate())

SELECT u.Username
FROM dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE u.ApplicationId= a.ApplicationIdAND
LastActivityDate> @.DateActiveAND
a.LoweredApplicationName=LOWER(@.ApplicationName)AND
u.UserId= m.UserId
END

membership deployment

hi

I am trying to add membership to my site. i use visual web developer and the set controls. i.e. login etc. I also used the asp web config tool which is where my test users are. Everything works locally. but when i upload it and try to log on I get this error message:

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735043 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Can anyone out ther please help me with this.

thanks

Nick

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Not sure if this will help, but you might want to try the tool I wrote that works well for deployment. It's also an MSDN article and can be found at:

http://peterkellner.net/2006/01/09/microsoft-aspnet-20-memberrole-management-with-iis/

|||

Thanks for that. Unfortunately i am very new to coding and your article is some feet over my head. I use Visual web developer, asp.net 2.0 and sql server 2005 Express. I understand that I do not need IIS. Am i right? Also is the admin tool that comes with VWD files and creates the database only available locally for testing. can it not be used remotely? I have a database with my host and i have uploaded the membership tables to that database. is it possible to connect to my hosts database and still use asp config tool in VWD and the controls?

sorry for all the questions but thanks for your time

nick

|||

Hi Nick,

You mentioned that you have a database with your host, is it SQL2005 Express or other editions? If for SQL2005 Express, just upload your database(aspnetdb) to the app_data folder on your host and modify your connection string according the information which provided by your hoster.(your hoster will give you such information like "data source" and etc.). If for other editons, there will be some other steps required. I suggest you to export your whole aspnetdb into SQLServer 2000(for example) instead of just exporting some tables which memebership uses, because except tables, views, stored procedure in aspnetdb may be required in the application. And then, upload the database to the one on your host and make the connection string look like "DataSource=ServerName\InstanceName;Initial Catelog=DatabaseName;..." It points to a database on server instead of a .mdf file.

Hope that helps. Thanks.

Membership Database Question (Under a deadline - please respond soon!)

So I can't quite understand what's going on here... I've watched the video tutorial on ASP.NET about creating and securing my site using Membership (configuring roles and users), and I noticed that it creates a database in my App_Data folder (ASPNETDB, I think it's called). And I've got all that working on my local machine, but I'm confused as to how to move it to my server.

My hosting provider is M6.net, and they've told me that if I want to upload a database to their server, I have to create it using my admin panel, then upload the file and they'll fill it in for me. My question is this: if I give them the database that's automatically created when I use login controls on my website LOCALLY, will that database still provide the correct membership functionality for my website when it's run from the web server? Such that I can give them (M6) the ASPNETDB (containing all the usernames/passwords/rules? I've already specified for my local site), have them populate the similarly titled database on the server, and have it still work with my pages?

I'm also confused as to why that database doesn't have a connection string specified in the web.config file...can anyone shed some light on that?

As I have no direct access to the database server on my hosting provider, I need to create the membership database with all the correct details prior to uploading it to my server - if simply using the locally generated copy won't work, what should I do?

Lastly, the video tutorial says to go to the ASP.NET Configuration page to manage users, roles, security settings, etc., but I don't seem to have access to that through M6. Instead, I have access to a FrontPage configuration page that is strikingly similar in content to that ASP.NET Configuration page featured in the video, in that it also allows user and role management capability - is this the same thing under a different guise?

Hi the ev,

the ev:

My hosting provider is M6.net, and they've told me that if I want to upload a database to their server, I have to create it using my admin panel, then upload the file and they'll fill it in for me. My question is this: if I give them the database that's automatically created when I use login controls on my website LOCALLY, will that database still provide the correct membership functionality for my website when it's run from the web server? Such that I can give them (M6) the ASPNETDB (containing all the usernames/passwords/rules? I've already specified for my local site), have them populate the similarly titled database on the server, and have it still work with my pages?

What format's they'll accept it in will be up to them. To be sure you should probably provide them a script instead of a database as it will guarantee they can use it. You can generate the script within Management Studio or by using something like thedatabase publishing wizard.aspnet_regsql.exe also has a command line switch to generate the statements required to setup the database but won't include your data.

the ev:

I'm also confused as to why that database doesn't have a connection string specified in the web.config file...can anyone shed some light on that?

If you don't define the providers in your web.config the defaults will be used, which includes trying to use an sql instance running out of your app_data folder. This will need to be changed to work on your hosted platform. You'll need to specify a connection string matching what M6 tell you to use and setup the providers to use that. Seehere for an example of configuring membership in your web.config.

To make use of the existing information you'll have to make sure your applicationName in the config file matches, I believe it defaults to "/".

the ev:

Lastly, the video tutorial says to go to the ASP.NET Configuration page to manage users, roles, security settings, etc., but I don't seem to have access to that through M6. Instead, I have access to a FrontPage configuration page that is strikingly similar in content to that ASP.NET Configuration page featured in the video, in that it also allows user and role management capability - is this the same thing under a different guise?

I doubt it. I have always steered clear of Frontpage so I can't say for sure, but I'd assume this is going to manage permissions at the Webserver level and won't interact with your membership provider.

To provide some sort of user/role management after it's been deployed to your host you'll have to implement it yourself. There's atwopart article on doing just that which you may find interesting.

I hope that helps.

|||

Thanks for the reply! I'm checking out what you wrote now and will report back later! :)

|||

Its funny -- your questions seem very familar to me--

I find MS's way of handleing these things to be perplexing and scary all at the same time - so far i have not been able to get a membership DB to work on a server.

To be honest I don't really see how the thing works at all. Farther I love the tools that make my life easier but there is something fairly Freaky about giving anything this much controll of the development process. Granted theres no need to re-invent the wheel but why the hell is the wheel so inflexable you'd think you would be able to mount it onto anything and have a wheel that works...

Yet microsoft shrouds fundimental things such as this in total mistery...

Maybe im missing the ahh-hah moment here but if you've ever developed in Linux or with any other DB platform you'd know that getting to the db is stored some place and connected to with a db account. Why complicated with attaching, local file permissions and membership 'provider' voodoo?

</rant>

Membership database installation SQL Express

I am trying to run one SQL Express database for two separate applications that will also use the Membership framework. My database is set up fine and works between both applications 100% as needed with the exception of Membership which I am fairly new to.

From my understanding given the following link

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

I need to install membership on the database. So I did the following

(allow remote connections with TCIP/IP and named pipes)

Started SQL and even re-booted

Ran: aspnet_regsql.exe -E -S localhost -A m

I even tried to run it by replacing localhost with MSSQL$SQLEXPRESS

All attempts gave me an error saying it could not connect and most likely because remote connections is disabled.

I think the reason for all this is because I don't actually have the "real" IIS installed but rather I am using the instantiated version that automatically comes up when starting a ASP.NET 2.0 application.

I am assuming I need to set everything up such that I am using the real IIS rather then the default one in visual studio.

Is this correct? Or do I have something else?

Use only aspnet_regsql.exe and create membership services for your database through the wizard.

Hope that helps!

|||I am using aspnet_regsql.exe only regardless of what i do aspnet_regsql.exe says it cant connect and most likely becuase of remote connections being disabled even thought it explicitly turned remote connections on.sql

Membership Database

I need to create a membership database that includes levels and premiums for each level. Can anyone offer any examples of how this should be done? What tables I would need and how they would be related to each other?

Thank you for any suggestions,Assuming that there are multiple premiums for each membership level, the following would be a basic approach.

Membership Level Table (Level ID, Level Name)
Premium Table (Premium ID, Premium Description, Level ID)
Members (Member ID, Level ID, First Name, Last Name, Address, City, State, Zip, Phone, Email, Date Joined)

Premium relates to Membership Level through the Level ID field, and Membership Level to Member through the Level ID field.

Lots of options, but this should get you started.

Jeff|||Thank you, this will help greatly. I just have one other question. If the databse is setup to allow multiple premiums for each level, how do we know what premiums the member received?

Thanks again,|||If the member can only receive one of a number of premiums, simply have a Premium Received field in the Member table that references the Premium ID. If the member can receive more than one premium, have a join table called Member Premiums, which will have a composite primary key of Member ID and Premium ID. This combination will always be unique, so long as the same Member cannot receive the same premium twice.

Jeff

Membership database

Hi all,

First ASP.NET project - please be gentle !

OK. Ready to add personalisation and membership. I have VS2005 Professional, including SQL Server 2005 developer, but I understand that VS2005 defaults to creating a local SQL Express file in the App_Data directory and that suits me just fine for now. Unfortunately I can't get VS to create it for me and the ASP.NET Configuration tool complains it can't connect to the database.

First, some REALLY dumb questions to show how little I really know ...

if the application is using a SQL Express file, does the SQL Server 2005 process need to be started in order to access the local database file (or does it just get opened like an access database)does the Broswer process need to be started - this is all just running on my local machinewhen I finally publish this site on my hosted service I could do with understanding what will need to be done to get THAT database accessed ... maybe cross that bridge later

I can go to the VS command prompt and run aspnet_regsql.exe in wizard mode. Going through the steps on that I could give it my SQL Server 2005 server name (after I start it) but that's not what I'm trying to do. I just want it to create and connect to a local file in the App_Data.

machine.config shows ...

<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
<!-- <add name="LocalSql2005" connectionString="Data Source=MICKSPC\SSLMJ;Initial Catalog=aspnetdb;Integrated Security=True"
providerName="System.Data.SqlClient" />
-->
</connectionStrings>

The commented connection string I used to temporarily prove that I could access SQL2005. It's no longer used and I've put the other references back to "LocalSqlServer" as below ...

 <membership> <providers> <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" /> </providers> </membership> <profile> <providers> <add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> </providers> </profile> <roleManager> <providers> <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> </providers> </roleManager>

The profile provider is clearly pointing to "LocalSqlServer" which in turn points to : connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

What am I missing please ?

Do you have sql express installed?|||

(Shrugs embarrassment off).

I originally had it installed - but removed it (along with VS Studio Express) when I bought VS2005. Sorry.

Would you mind clarifying SQL Express a little for me. Is there any point in installing and using it if I have Sql 2005 ? The only reason I could see was to have a file which was portable within the App_Data directory rather than running scripts to update a remote database. However if I use Sql Express then presumably that software would have to be installed on my remote shared host and I can't guarantee that.

Thanks - and sorry for the dumb question.

|||Sql express is a free version of sql server 2005. Visual Studio is setup to attach the database to sql express by default. In tools--> options--> database tools if you change the Sql Server Instance name to a blank string it will use your SQL Server 2005

Membership Data Provider

I created a membership database on my local computer. DB is SQL Server 2005. I created create sripts for that database and created this database in a SQL Server 2000 instance on another box.

As soon as I changed my web.config fileto point to the new database, I got this error below. What is causingit?

The 'System.Web.Security.SqlMembershipProvider' requires adatabase schema compatible with schema version '1'. However, thecurrent database schema is not compatible with this version. You mayneed to either install a compatible schema with aspnet_regsql.exe(available in the framework installation directory), or upgrade theprovider to a newer version.

Do I have to create a local Membership database in a SQL 2000 instance, script it out and then create in the other box?

Thanks
Runaspnet_regsql.exe to create the database on your SQL 2000.

membership control, MSDE, SQLExpress

can I implement the membership control (provided by ASP.NET) on MSDE? my web hoster is not supporting SQL2005 that is why i am asking this. my web project is using ASP.NET 2.0, C#, SQLExpress2005. i am not sure if i migrate to MSDE it can implement the membership control. im sorry if i sound dumb. im really a newbie.

thanks.
Already answered here:

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

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Membership Configuration

I am attempting to follow the instructions:

Walkthrough: Creating a Web Site with Membership and User Login (Visual Studio)

When I attempt to create a membership user with the ASP.NET Configuration wizard nothing happens - it attempts to launch my browser and if I am not connected to the intenet it insists on connecting. If I am connected then nothing happens - it is as if I clicked on air. A wizard is supposed to launch so that I can set up users.

Any help would be appreciated - I am really trying to get to creating an application that authenticates users for admitance to a restricted area in a website.

Thanks!

I would try posting you question in the http://forums.asp.net membership and security groups as the question you are asking relates to Web development and the membership providers.

But To start with I would check to make sure you have the correct provider set up and configured, you could also check to see if the membership tables and such have been created, this could be inside your database or inside another database called aspnetdb from memory. Did you try following the wizard inside the asp configuration pages to set up the security.. if not try that..

sql

Membership and Report Server Access... Help!!

I have a problem that I'm hoping someone can give me some advice with: I have created a website that uses .Net 2.0, SQL 2005, and ASP.NET Membership to manage users. Everything works fine until it comes to calling reports, in particular setting report parameters:

Exception Details: Microsoft.Reporting.WebForms.ReportServerException: The permissions granted to user 'NT AUTHORITY\NETWORK SERVICE' are insufficient for performing this operation. (rsAccessDenied)

Source Error:

Line 30: End If Line 31: Line 32: Me.rvInvoice.ServerReport.SetParameters(p) Line 33: Me.rvInvoice.Visible = True Line 34:

The report worked fine before the security model was changed to use the Membership classes which leads me to conclude that I have not configured the Report Server to deal with the network account that the ASP pages are now using.

I have looked at the example project for configuring extensions with no success. Any help would be greatly appreciated.

Thanks,

Mike

Did you ever find a solution to this? I am having the same problem..

Membership and Report Server Access... Help!!

I have a problem that I'm hoping someone can give me some advice with: I have created a website that uses .Net 2.0, SQL 2005, and ASP.NET Membership to manage users. Everything works fine until it comes to calling reports, in particular setting report parameters:

Exception Details: Microsoft.Reporting.WebForms.ReportServerException: The permissions granted to user 'NT AUTHORITY\NETWORK SERVICE' are insufficient for performing this operation. (rsAccessDenied)

Source Error:

Line 30: End If Line 31: Line 32: Me.rvInvoice.ServerReport.SetParameters(p) Line 33: Me.rvInvoice.Visible = True Line 34:

The report worked fine before the security model was changed to use the Membership classes which leads me to conclude that I have not configured the Report Server to deal with the network account that the ASP pages are now using.

I have looked at the example project for configuring extensions with no success. Any help would be greatly appreciated.

Thanks,

Mike

Did you ever find a solution to this? I am having the same problem..