VS Lightswitch 2011 where is the SQLExpress Security Database

If you apply security to the Lightswitch project, once you debug for the first time Visual studio will use SQLEXPRESS and attach a database file, the database file will be created in you project folder under bin\data, with the name 'ApplicationDatabase.mdf' and 'ApplicationDatabase_log.ldf'.

Lightswitch needs SQL Express 2008 but no sysadmin rights

The PC I am using for my current assignment was given to me by my predecessor, it runs on Windows 7. He had installed VS2008 with SQL Express 2005. I needed to create a lightswitch app and therefore I installed VS2010, and then VS Lightswitch 2011.

The VS2010 SQL express installation failed, maybe because a SQL Express 2005 had been installed by my predecessor.

Since the SQL Express 2005 was there, I tried to develop a Lightswitch app with security. To use the security features on my developer PC, VS Lightswitch wants to use SQLEXPRESS. when you debug the app and try to create Roles under the Administration tab, the roles cannot be saved. I tried to make it use a named Developer version of SQL 2008 that was also installed on this PC, but to no avail (how is for the next topic). When trying to save a role on the SQL Dev version, I got the error message 'The user instance login flag is not supported on this version of SQL Server. The connection will be closed'.

Lightswitch needs SQL Express 2008 on the developer pc and to create databases in SQLEXPRESS I need admin rights on the SQLEXPRESS instance.

Solution:

• Download the SQL Express 2008 R2.
• Uninstalling the SQL 2005 express failed. Install as an upgrade on the SQL 2005 express worked.
• Run SQL server management studio to add my windows daomin account as sysadmin. Since my predecessor had done the initial installation, I did not have admin rights, so that did not work.
• Final Solution: run SQL server Management studio as Administrator, then add my account as sysadmin, this worked.

Finally: tested my Lightswitch project, added a role and saved it. Problem solved

SQL server index fragmentation – reorganize or rebuild example

SQL server index fragmentation – reorganize or rebuild

Below are some SQL scripts to reorganize or rebuild an index. 

DECLARE @dbname nvarchar(50);

DECLARE @tblname nvarchar(100);

DECLARE @dbid int;

DECLARE @tblid int;

SET @dbname = 'AdventureWorks2008R2';

SET @tblname = @dbname + N'.dbo.Customer'

SELECT @dbid = DB_ID(@dbname);

SELECT @dbid  AS [Database ID];

SELECT @tblid = OBJECT_ID(@tblname);

SELECT @tblid AS [Object ID];

GO

Database ID

-----------

44

(1 row(s) affected)

Object ID

-----------

1605580758

(1 row(s) affected)

USE master;

GO

DECLARE @dbname nvarchar(50);

DECLARE @tblname nvarchar(100);

SET @dbname = 'AdventureWorks2008R2';

SET @tblname = @dbname + N'.dbo.Customer'

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(@dbname), OBJECT_ID(@tblname), NULL, NULL , 'DETAILED')

WHERE avg_fragmentation_in_percent > 30;

GO

--- defragmentation of the index , reorganize ----------

USE AdventureWorks2008R2

GO

ALTER INDEX PK_Customer ON dbo.Customer

      REORGANIZE WITH ( LOB_COMPACTION = OFF );

GO

--- defragmentation of the index, rebuild ------------------------------------

USE AdventureWorks2008R2

GO

ALTER INDEX PK_Customer ON dbo.Customer

      REBUILD;

GO

SQl server 2008 script to create and populate a date dimension table

This script was based and improved upon an idea (with some bugs) on sqlservercentral.com. I put it here so that I can easily reuse it.

CREATE TABLE dbo.DateDim(
DateID int IDENTITY(1,1) NOT NULL,

ActualDate date NOT NULL,

[Year] smallint NOT NULL,

[Quarter] smallint NOT NULL,

[Month] smallint NOT NULL,

[Week] smallint NOT NULL,

[DayofYear] smallint NOT NULL,

[DayofMonth] smallint NOT NULL,

[DayofWeek] smallint NOT NULL,

IsWeekend bit NOT NULL,

IsHoliday bit NOT NULL,

CalendarWeek smallint NOT NULL,

BusinessYearWeek smallint NOT NULL,

LeapYear bit NOT NULL,
CONSTRAINT PK_DateDim PRIMARY KEY CLUSTERED
(

DateID ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY

) ON PRIMARY

GO
 ALTER TABLE dbo.DateDim WITH CHECK ADD CONSTRAINT FK_DateDim_DateDim FOREIGN KEY(DateID)

REFERENCES dbo.DateDim (DateID)

GO



ALTER TABLE dbo.DateDim CHECK CONSTRAINT FK_DateDim_DateDim

GO
 CREATE PROCEDURE dbo.sp_createTimeDim AS


--declare variables
DECLARE @DT DATE
DECLARE @YEAR smallint
DECLARE @QUARTER smallint
DECLARE @MONTH smallint
DECLARE @WEEK smallint
DECLARE @DayofYear smallint
DECLARE @DayofMonth smallint
DECLARE @DayofWeek smallint
DECLARE @IsWeekend BIT
DECLARE @IsHoliday BIT
DECLARE @CalendarWeek smallint
DECLARE @BusinessYearWeek INT
DECLARE @LeapYear BIT

--initialize variables


SELECT @BusinessYearWeek =0
SELECT @CalendarWeek = 1
SELECT @LeapYear =0

--the starting date for the date dimension
SELECT @DT = '2012-01-01'

--start looping, stop at ending date

WHILE (@DT <= '2020-12-31')


BEGIN
--get information about the data

SET @IsWeekend =0
SET @YEAR = DATEPART (YEAR, @DT)
SET @QUARTER = DATEPART (QUARTER, @DT)
SET @MONTH = DATEPART (MONTH , @DT)
SET @WEEK = DATEPART (WEEK , @DT)
SET @DayofYear = DATEPART (DY , @DT)
SET @DayofMonth = DATEPART (DAY , @DT)
SET @DayofWeek = DATEPART (DW , @DT)


--note if weeknd or not


IF ( @DayofWeek = 1 OR @DayofWeek = 7 )
BEGIN
SELECT @IsWeekend = 1
END


--add 1 every time we start a new week


IF ( @DayofWeek = 1)

BEGIN
SELECT @CalendarWeek = @CalendarWeek +1

END


--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)


IF ( @DayofWeek != 1 AND @DayofYear = 1)

BEGIN

SELECT @BusinessYearWeek = 0

END


IF ( @DayofWeek = 1)

BEGIN

SELECT @BusinessYearWeek = @BusinessYearWeek +1

END

--add business rule (start counting business weeks with first complete week)

IF (@BusinessYearWeek =53)

BEGIN

SELECT @BusinessYearWeek = 1

END

--check for leap year


IF ((@YEAR % 4 = 0) AND (@YEAR % 100 != 0 OR @YEAR % 400 = 0))

SELECT @LeapYear =1

ELSE SELECT @LeapYear =0

--insert values into Date Dimension table

INSERT Mgmt.DateDim (ActualDate, Year, Quarter, Month, Week, DayofYear, DayofMonth, DayofWeek, IsWeekend, CalendarWeek, BusinessYearWeek, LeapYear, IsHoliday)

VALUES (@DT, @YEAR, @QUARTER, @MONTH, @WEEK, @DayofYear, @DayofMonth, @DayofWeek, @IsWeekend, @CalendarWeek, @BusinessYearWeek, @LeapYear, @IsHoliday)

--increment the date one day


SELECT @DT = DATEADD(DAY, 1, @DT)

END
GO


DECLARE @RC int


EXECUTE @RC = dbo.sp_createTimeDim


GO