Combine aspnetdb with the applications database

I have tried to find all the threads related to this topic. At this point I would like to find out what the best practices are for doing this.

I plan to use Membership, Roles, and the login controls to handle my authentication and permissions issues. I want to use the same database for my application data as for Application Services, using a standard SQL Server.

So far, the easiest way to set this up that I can think of is to:

1. Use aspnet_regsql.exe to create the Application Services Database schema

2. Edit the "LocalSqlServer" entry in <connectionStrings> to point to the above database

3. Add the tables, views, stored procedures, etc., that I need for my application into the database created in step 1

Is there any more configuration I need to do to get Membership and Roles working? When I did the Walkthrough, step 2 seemed to be the only requirement for getting Membership and Roles to work.

Will I run into any trouble using LocalSqlServer for my application data updates?

I would be interested in any other approaches to setting up a combined database, including opinions why and why not to do it.

There should be no other problem if you've configrue the application to connect to the right database using login which has proper permissions on that database. You cna take a look at Scott's article if you haven't read it yet:

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

Combination of Filegroup and Point in Time restore possible?

SQL2K5
Ive been doing some testing this morning and it appears that it is not
possible to combine PIT restores with Filegroup restores, is that correct? I
restore a Filegroup backup, and then the Trans Log backup, but there is no
option to stop at a specific time when doing this. It would sort of make
sense I suppose. It could be argued that doing so would leave the DB in an
inconsistent state if a table that resided outside of the restored Filegroup
had been updated before the restores began, but I just wanted to confirm my
findings(?).
I think the down side to this protection would be that if you are relying on
Filegroup backups/ restores, how would you recover a data loss that had
occured due to human error (someone deletes something they shouldn't have)?
After all, to bring the tables in the restored filegroup online I MUST take a
Trans Log backup and restore it, therefore my accidentally deleted data is
still not recovered.
Does this all sound right, or am I missing something here?
All insights are appreciated.Hi Chris
This sounds like your understanding is correct.
When doing a filegroup restore, SQL Server is assuming that other filegroups
are available, with transactions that might have occurred after the backup
of the filegroup that you are restoring. Restoring a FG requires that you
apply transaction logs to bring the FG up to the same point as the rest of
the database.
The point of FG restore is to recover from hardware errors on a single file
or fg. If you need to recover from user error, you'll need to restore the
whole db.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> SQL2K5
> Ive been doing some testing this morning and it appears that it is not
> possible to combine PIT restores with Filegroup restores, is that correct?
> I
> restore a Filegroup backup, and then the Trans Log backup, but there is no
> option to stop at a specific time when doing this. It would sort of make
> sense I suppose. It could be argued that doing so would leave the DB in an
> inconsistent state if a table that resided outside of the restored
> Filegroup
> had been updated before the restores began, but I just wanted to confirm
> my
> findings(?).
> I think the down side to this protection would be that if you are relying
> on
> Filegroup backups/ restores, how would you recover a data loss that had
> occured due to human error (someone deletes something they shouldn't
> have)?
> After all, to bring the tables in the restored filegroup online I MUST
> take a
> Trans Log backup and restore it, therefore my accidentally deleted data is
> still not recovered.
> Does this all sound right, or am I missing something here?
> All insights are appreciated.|||Thanks!
"Kalen Delaney" wrote:
> Hi Chris
> This sounds like your understanding is correct.
> When doing a filegroup restore, SQL Server is assuming that other filegroups
> are available, with transactions that might have occurred after the backup
> of the filegroup that you are restoring. Restoring a FG requires that you
> apply transaction logs to bring the FG up to the same point as the rest of
> the database.
> The point of FG restore is to recover from hardware errors on a single file
> or fg. If you need to recover from user error, you'll need to restore the
> whole db.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> > SQL2K5
> >
> > Ive been doing some testing this morning and it appears that it is not
> > possible to combine PIT restores with Filegroup restores, is that correct?
> > I
> > restore a Filegroup backup, and then the Trans Log backup, but there is no
> > option to stop at a specific time when doing this. It would sort of make
> > sense I suppose. It could be argued that doing so would leave the DB in an
> > inconsistent state if a table that resided outside of the restored
> > Filegroup
> > had been updated before the restores began, but I just wanted to confirm
> > my
> > findings(?).
> >
> > I think the down side to this protection would be that if you are relying
> > on
> > Filegroup backups/ restores, how would you recover a data loss that had
> > occured due to human error (someone deletes something they shouldn't
> > have)?
> > After all, to bring the tables in the restored filegroup online I MUST
> > take a
> > Trans Log backup and restore it, therefore my accidentally deleted data is
> > still not recovered.
> >
> > Does this all sound right, or am I missing something here?
> >
> > All insights are appreciated.
>
>

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
I just answered my own question about 5 minutes after posting. The sp_OAxxx stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
|||Look up OLE Automation in BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?
|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison|||Look up OLE Automation in BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored
procedure and call methods, etc, but now I can't remember how I did it. Can
someone point me in the right direction?I just answered my own question about 5 minutes after posting. The sp_OAxxx
stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored proced
ure and call methods, etc, but now I can't remember how I did it. Can someone point
me in the right direction?|||Look up OLE Automation in BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison

Column X is same, column Y is different?

Can someone point me in the right direction: How do I say: "Display all
rows from MYTABLE where column x is the same but column y is different"?
Thanks much.If I understand your question correctly then this will do it
select * from table where colX = someValue
and colY <> someValue
example
select * from employees where FirstName= 'John'
and Zipcode<> '10028'
http://sqlservercode.blogspot.com/|||SELECT a.*
FROM MYTABLE as a, MYTABLE as b
WHERE a.x = b.x AND a.y <> b.y
"Rick Charnes" wrote:

> Can someone point me in the right direction: How do I say: "Display all
> rows from MYTABLE where column x is the same but column y is different"?
> Thanks much.
>|||select distinct
x
,y
from table
?
DDL, sample data and expected results would help a lot.
ML
http://milambda.blogspot.com/|||I'm not sure what you mean.
Column X is the same as what?
Column Y is different than what?
Are you comparing rows against specific values?
Or are you comparing the rows against the rows from another table.
Can you give us a simple example?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Rick Charnes" wrote:

> Can someone point me in the right direction: How do I say: "Display all
> rows from MYTABLE where column x is the same but column y is different"?
> Thanks much.
>|||Sorry; my fault: I don't know the values that I'm comparing. I want to
say: give me all rows in the table where col X is the same but within
those matched rows col Y is different. But I don't have any specific
values of X or Y that I'm comparing.
I think I need to use:
GROUP BY x
HAVING count(*) > 1
...and something with column Y <> column Y...?
somehow, but I'm not sure how.
In article <1136564368.957582.7870@.o13g2000cwo.googlegroups.com>,
denis.gobo@.gmail.com says...
> If I understand your question correctly then this will do it
> select * from table where colX = someValue
> and colY <> someValue
> example
> select * from employees where FirstName= 'John'
> and Zipcode<> '10028'
> http://sqlservercode.blogspot.com/
>|||Rick Charnes wrote:
> Sorry; my fault: I don't know the values that I'm comparing. I want to
> say: give me all rows in the table where col X is the same but within
> those matched rows col Y is different. But I don't have any specific
> values of X or Y that I'm comparing.
> I think I need to use:
> GROUP BY x
> HAVING count(*) > 1
> ...and something with column Y <> column Y...?
> somehow, but I'm not sure how.
> In article <1136564368.957582.7870@.o13g2000cwo.googlegroups.com>,
> denis.gobo@.gmail.com says...
Try:
SELECT T1.* -- List the column(s) don't use *
FROM your_table AS T1
WHERE EXISTS
(SELECT *
FROM your_table AS T2
WHERE T1.x = T2.x
AND T1.y <> T2.y);
Or maybe:
SELECT x
FROM your_table
GROUP BY x
HAVING MIN(y)<MAX(y);
depending on what column(s) you want to output.
David Portas
SQL Server MVP
--|||Here is one way
create table mark (value varchar(50),id int)
insert into mark
select 'AA',1 union all
select 'AA',1 union all
select 'AB',2 union all
select 'AB',1
select distinct m.* from mark m join(
select value from mark
group by id,value
having count(*) =1) m2 on m.value =m2.value
http://sqlservercode.blogspot.com/|||Post DDL, sample data and expected results, then.
ML
http://milambda.blogspot.com/|||This does it. Thanks very much.
In article <C8F7E55C-707D-43FA-B6ED-BC2697B8D64F@.microsoft.com>,
Lee@.discussions.microsoft.com says...
> SELECT a.*
> FROM MYTABLE as a, MYTABLE as b
> WHERE a.x = b.x AND a.y <> b.y
> "Rick Charnes" wrote:
>
>

Column detach task ?

Sometimes you have some columns in the dataflow that you at some point in the flow doesn't need in the further flow. So maybe a tast or an option for disabling columns that are no longer in use

Why do you think this would be of benefit? If its for disabling those columns at design-time then I think its a good idea. I think "disabling|" is the wrong word however, "hiding" would be more appropriate!

This wouldn't have any benefit on runtime performance because the query plan will still allocate memory buffer space for those columns and that buffer space will still be occupied even after the components that use the column have finished executing.

As ever, if you have a suggestion for future enhancement then register it at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx). Then put a link to it up here - I would vote for a request to be able to hide columns in the designer.

-Jamie