More problems attaching a database

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop \*name of database \*

EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query, Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.

I havn't read your first post.... what is the summary of what you are trying to acheive.

I have used SQL Express with VWD for a while now and have had no problems moving the database from my VWD environment to a production system. The first thing is that when using the VWD the Databases are created in the APP_Data directory and the connection string used inside the web.config file has a parameter that attaches the database at runtine to the Database Engine it also configures the security so only the Windows Account that has attached the Database can access the Database Files (User Instances). When moving to production systems or attaching it to an existing Engine the first step I would do is to take all of the files (MDF and LDF) for the database and put them in another directory, away from the ASP_Data directory in the root of your web application. Normally this would be the default data directories for the Database server that you are going to use. Next inside the management studio attach the database files and set up the security for the user that you are going to be using.

In my case here I normally create an application pool for the Web site to use in IIS and set the app pool to run under a user, I would then configure this user to have the access needed to run the database system ie Stored Procs and Table access. The last stage is to change the connection string in the web.config file for your application to reflect the new server. Making sure that the attach Database parameters are removed.

Hope this helps.

More complex Sum and Group by a week period

First thanks to Mike and Baie for helping me before.
I have a set of data similar to the below one.
issueID IssueDate
1 1/17/2005
2 1/18/2005
3 1/19/2005
4 1/24/2005
5 1/27/2005
6 2/7/2005
I need to query the set between 2 dates for example,
1/3/2005 and 6/5/2005 and what I need to return is the
sum of issues per 7 day groups starting from 1/3/2005.
Even if there are no issues opened in the 7 days
following the 1/3/2005 date, I need the query to return
0.
I need The query output to look like this:
WStart Count
1/3/2005 0
1/10/2005 0
1/17/2005 3
1/24/2005 2
1/31/2005 1
The reason I need to see this output this way, is that I
need to compare it to a projected set that the managers
estimated per 7 day periods starting from 1/3/2005.
Is this doable as a query? I only have read access to
the database, so I won't be able to create stored
procedures or views.
Please let me know if you have ideas.Having a calendar table (http://www.aspfaq.com/show.asp?id=2519), you can tr
y
something like:
declare @.sd datetime, @.ed datetime
set @.sd = '20050103'
set @.ed = '20050605'
select
min(c.td),
count(t.IssueID)
from
calendar as c
left join
t
on c.dt = t.IssueDate
where
c.dt >= @.sd and c.dt < dateadd(day, 1, @.ed)
group by
datediff(day, c.dt, @.sd) / 7
go
Not tested.
AMB
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||If it's possible, (as your sample data indicates) to NOT have actual data in
some of the date range groups you need to output as aggregate "buckets", the
n
your best bet, if you can, is to create a calendar table, and do an Outer
Join from it to the data table(s)...
But you say you have only read access, so then the only real option is to,
within a batch, create a table variable that contains the range of dates you
want to group on, and join this table variable to the data table(s)
Declare @.Dts Table(Dt DateTime Not Null)
Declare @.DT DateTime Set @.DT = '20050103'
While @.DT < '20050630' Begin
Insert @.DTs (DT) Values(@.DT)
End
Select D.DT, Count(*)
From @.DTs D Left Join DataTable T
On T.IssueDate Between D.DT And DateAdd(w, 1, D.DT)
Group By D.DT
-- Something like that--
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||Just to throw one more option onto the pile...
To generate the dates for the ws that you wish to query for you could use
the following query
select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605'
This will generate start and end dates for the target ws which you could
put in a table variable as CBretana suggests. You can also embed this query
in your original query like so...
select start_dt, count(issueID)
from (select dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') start_dt,
dateadd(ww, 1+a0+a1*2+a2*4+a3*8+a4*16, '20050103') exclusive_end_dt from
(select 1 a0 union all select 0 ) x0
cross join (select 1 a1 union all select 0 ) x1
cross join (select 1 a2 union all select 0 ) x2
cross join (select 1 a3 union all select 0 ) x3
cross join (select 1 a4 union all select 0 ) x4
where dateadd(ww, a0+a1*2+a2*4+a3*8+a4*16, '20050103') < '20050605') ws
left outer join test1
on issuedate >= start_dt and issuedate < exclusive_end_dt
group by start_dt
This approach is likely only useful for small sets of ws.
"Danny Mansour" wrote:

> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
>|||Danny Mansour wrote:
> First thanks to Mike and Baie for helping me before.
> I have a set of data similar to the below one.
> issueID IssueDate
> 1 1/17/2005
> 2 1/18/2005
> 3 1/19/2005
> 4 1/24/2005
> 5 1/27/2005
> 6 2/7/2005
> I need to query the set between 2 dates for example,
> 1/3/2005 and 6/5/2005 and what I need to return is the
> sum of issues per 7 day groups starting from 1/3/2005.
> Even if there are no issues opened in the 7 days
> following the 1/3/2005 date, I need the query to return
> 0.
> I need The query output to look like this:
> WStart Count
> 1/3/2005 0
> 1/10/2005 0
> 1/17/2005 3
> 1/24/2005 2
> 1/31/2005 1
> The reason I need to see this output this way, is that I
> need to compare it to a projected set that the managers
> estimated per 7 day periods starting from 1/3/2005.
> Is this doable as a query? I only have read access to
> the database, so I won't be able to create stored
> procedures or views.
> Please let me know if you have ideas.
A table of (nonnegative) integers is often handy.
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
CREATE TABLE N
(
i INT NOT NULL CHECK (i>=0) PRIMARY KEY
)
INSERT INTO N (i)
SELECT Ones.d + 10*Tens.d + 100*Hundreds.d + 1000*Thousands.d +
10000*TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands
CREATE VIEW Calendar (d)
AS
SELECT CAST('19000101' AS DATETIME) + N.i
FROM N
CREATE TABLE Issues
(
issue_id INT NOT NULL PRIMARY KEY,
issue_date DATETIME NOT NULL
)
-- Sample data
INSERT INTO Issues (issue_id, issue_date)
VALUES (1, '20050117')
INSERT INTO Issues (issue_id, issue_date)
VALUES (2, '20050118')
INSERT INTO Issues (issue_id, issue_date)
VALUES (3, '20050119')
INSERT INTO Issues (issue_id, issue_date)
VALUES (4, '20050124')
INSERT INTO Issues (issue_id, issue_date)
VALUES (5, '20050127')
INSERT INTO Issues (issue_id, issue_date)
VALUES (6, '20050207')
-- This is a purely relational solution. If you need to do
-- more general queries, this might be helpful.
-- start_date and end_date is the overall period
-- interval is a number of days
-- period_start and period_end is the period we're tallying where
-- period_start is inclusive and period_end is exclusive
-- issue_tally is the tally for the period
CREATE VIEW IssueTally
(start_date, end_date, interval,
period_start, period_end, issue_tally)
AS
SELECT SD.d, ED.d, L.i,
SD.d + L.i * T.i, SD.d + L.i * (T.i + 1),
COUNT(I.issue_id)
FROM Calendar AS SD -- start date
INNER JOIN
Calendar AS ED -- end date
ON ED.d > SD.d
INNER JOIN
N AS L -- interval length
ON L.i > 0
INNER JOIN
N AS T -- number of intervals
ON T.i <= DATEDIFF(DAY, SD.d, ED.d) / L.i
LEFT OUTER JOIN
Issues AS I
ON I.issue_date >= SD.d + L.i * T.i AND
I.issue_date < SD.d + L.i * (T.i + 1)
GROUP BY SD.d, ED.d, L.i, T.i
SELECT start_date, end_date, interval,
period_start, period_end, issue_tally
FROM IssueTally
WHERE start_date = '20050103' AND
end_date = '20050605' AND
interval = 7
ORDER BY period_start
JAG|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.|||Thanks you very much. That helped.