Multiple installions of SQL Server

We have previously installed 2 applications on our server that automatically installed there own instances of MSDE.

We have also subsequently installed a further application that automatically installed its own instance of SQL Server 2005 Express.

We have now installed for our own use SQL Server 2005 Workgroup edition.

Hence we have now ended up with many versions/editions/instances of SQL Server installed and running.

I am concerened this is effecting the overall performance of the server.

Is there any way to consolidate all this to one installation (ideally the SQL Server 2005 Workgroup edition)?

Thanks

of course , it may create chaos.When you have many instances running(i assume that on single physical machine) it may create a memory contention. You can keep a instance and you can use detach /attach or Backup/restore method to transfer database from other instance database to this instance and later remove the unwanted instances.

Madhu

|||

Thank you for the reply.

Yes I thought it may cause problems.

Two further questions:

If I uninstall the applications that have installed their own instances of MSDE and SQL Server 2005 Express and then reinstall them now we have SQL Server 2005 Workgroup edition installed, would they automatically attach themselves to this instance rather than creating their own?

Is it possible to do what you suggest above and have all using one instance even for the MSDE applications?

Thanks

|||

no it will not attach automatically, you need to attach the database using sp_attach_db. Once it is attached its permanent. I guess , you uses User instance of sql server express. In that case , you need to change the connection string once you have attached that database to workgroup edtion.

Madhu

|||

OK thank you.

What about the MSDE instances? Can they be attached too? I notice we have instances for SBS 2003 services such as WSUS as well as for our two applications that use MSDE.

sql

Multiple foreign keys

Hi,
I wonder if it is possible to have multiple foreign keys between to tables
and delete data automatically. We have on table with a number of locations
and another table with a number of routes between them. The foreign keys go
from location to the beginning and the end of the route. How can I secure
that (1) only data from location is used in route and (2) if a dataset in
location is deleted both routes to and from are deleted. I can't have a
cascading key on both relations and i couldn't make a trigger work either.
Any suggestions?
Holgerjust a try...
parent table A
child Table B
grandchild table C :)
you have C refering to B refering to A
Create an ondelete cascade to table A
, Now write an intead of delete trigger for table B and table C each which
will delete from table A the corresponding row..
Let me know how it goes.. I am interested in finding out too :)|||Why wouldn't it be possible for both the 'from' and 'to' columns in the rout
e
table to reference the location column in the location table, with ON DELETE
CASCADE?
"HE" wrote:

> Hi,
> I wonder if it is possible to have multiple foreign keys between to tables
> and delete data automatically. We have on table with a number of locations
> and another table with a number of routes between them. The foreign keys g
o
> from location to the beginning and the end of the route. How can I secure
> that (1) only data from location is used in route and (2) if a dataset in
> location is deleted both routes to and from are deleted. I can't have a
> cascading key on both relations and i couldn't make a trigger work either.
> Any suggestions?
> Holger
>
>|||Why wouldn't it be possible for both the 'from' and 'to' columns in the rout
e
table to reference the location column in the location table, with ON DELETE
CASCADE?
"HE" wrote:

> Hi,
> I wonder if it is possible to have multiple foreign keys between to tables
> and delete data automatically. We have on table with a number of locations
> and another table with a number of routes between them. The foreign keys g
o
> from location to the beginning and the end of the route. How can I secure
> that (1) only data from location is used in route and (2) if a dataset in
> location is deleted both routes to and from are deleted. I can't have a
> cascading key on both relations and i couldn't make a trigger work either.
> Any suggestions?
> Holger
>
>|||I think the OP has something like this...
The second on delete cascade, on update cascade causes an error.
Maybe I am missing something in RD Theory, but why would this not be
allowed?
Create table Location
(
LocationID varchar(10) primary key not null
,LocationName varchar(30) not null
)
go
create Table Routes
(
FromLocationID varchar(10) not null
,ToLocationID varchar(10) not null
, RouteName varchar(30) not null
)
go
ALTER TABLE dbo.Routes ADD CONSTRAINT
PK_Routes PRIMARY KEY CLUSTERED
(
FromLocationID,
ToLocationID
)
GO
ALTER TABLE dbo.Routes ADD CONSTRAINT
FK_Routes_FromLocation FOREIGN KEY
(
FromLocationID
) REFERENCES dbo.Location
(
LocationID
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE dbo.Routes ADD CONSTRAINT
FK_Routes_ToLocation FOREIGN KEY
(
ToLocationID
) REFERENCES dbo.Location
(
LocationID
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:80BE38F5-A266-4593-A4C5-A69DAD837B89@.microsoft.com...
> just a try...
> parent table A
> child Table B
> grandchild table C :)
> you have C refering to B refering to A
> Create an ondelete cascade to table A
> , Now write an intead of delete trigger for table B and table C each
which
> will delete from table A the corresponding row..
> Let me know how it goes.. I am interested in finding out too :)
>|||Hi Jim,
Why do I have a feeling we both are beating the wrong bush (No pun
intended :)
Holger: Of course if its not too much of a pain for you, Can you give the
table definitions and the foriegn key contraints
with a few sample data and your delete statement and what should it inturn
delete and then we will try to find a way out.|||I was thinking along the same lines; I don't think there's anything that
would prevent two columns in one table referencing the same column in anothe
r
table, with both constraints created with ON DELETE CASCADE.
"Jim Underwood" wrote:

> I think the OP has something like this...
> The second on delete cascade, on update cascade causes an error.
> Maybe I am missing something in RD Theory, but why would this not be
> allowed?
> Create table Location
> (
> LocationID varchar(10) primary key not null
> ,LocationName varchar(30) not null
> )
> go
> create Table Routes
> (
> FromLocationID varchar(10) not null
> ,ToLocationID varchar(10) not null
> , RouteName varchar(30) not null
> )
> go
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> PK_Routes PRIMARY KEY CLUSTERED
> (
> FromLocationID,
> ToLocationID
> )
> GO
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> FK_Routes_FromLocation FOREIGN KEY
> (
> FromLocationID
> ) REFERENCES dbo.Location
> (
> LocationID
> ) ON UPDATE CASCADE
> ON DELETE CASCADE
> GO
> ALTER TABLE dbo.Routes ADD CONSTRAINT
> FK_Routes_ToLocation FOREIGN KEY
> (
> ToLocationID
> ) REFERENCES dbo.Location
> (
> LocationID
> ) ON UPDATE CASCADE
> ON DELETE CASCADE
> GO
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:80BE38F5-A266-4593-A4C5-A69DAD837B89@.microsoft.com...
> which
>
>|||Logically, I don't see an issue, but I may be missing something. I do know
that SQL Server will not allow this...
From BOL "ON UPDATE CASCADE"
The series of cascading referential actions triggered by a single DELETE or
UPDATE must form a tree containing no circular references. No table can
appear more than once in the list of all cascading referential actions that
result from the DELETE or UPDATE.
******
The tree of cascading referential actions must not have more than one path
to any given table.
******
Any branch of the tree is terminated when it encounters a table for which NO
ACTION has been specified or is the default.
The problem here is we have two paths to the same table, so one would expect
two deletes to be performed, one for each reference, or one delete where
either reference exists. I'm not sure if this is a DBMS thing or a SQL
Server implementation thing. I know that this must be a common issue. I
figure a trigger could accomplish the same thing (which I think omni was
suggesting) but it seems to go against DB design to resort to a trigger when
a simple constraint should be sufficent.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBA9B0E2-CA08-4277-8397-3298494C6F15@.microsoft.com...
> I was thinking along the same lines; I don't think there's anything that
> would prevent two columns in one table referencing the same column in
another
> table, with both constraints created with ON DELETE CASCADE.
>
> "Jim Underwood" wrote:
>|||Technically, this is okay in Standard SQL because of the constraints
that force one and only one execution path.
CREATE TABLE Locations
(location_id INTEGER NOT NULL PRIMARY KEY,
location_name VARCHAR(30) NOT NULL);
CREATE TABLE Routes
(route_name VARCHAR(30) NOT NULL PRIMARY KEY,
start_location_id INTEGER NOT NULL
CONSTRAINT starting
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
final_location_id INTEGER NOT NULL
CONSTRAINT ending
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UNIQUE(start_location_id, final_location_id),
CHECK (start_location_id <> final_location_id));
A smart SQL engine will detect that {ending, starting} and {starting,
ending} will give the same results. This would be legal and deleting a
location removing a node from a graph -- the edges would also
disappear.|||Hi Jim,
"Jim Underwood" <james.underwoodATfallonclinic.com> schrieb im Newsbeitrag
news:Oj6rIiReGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Logically, I don't see an issue, but I may be missing something. I do
> know
> that SQL Server will not allow this...
> From BOL "ON UPDATE CASCADE"
> The series of cascading referential actions triggered by a single DELETE
> or
> UPDATE must form a tree containing no circular references. No table can
> appear more than once in the list of all cascading referential actions
> that
> result from the DELETE or UPDATE.
> ******
> The tree of cascading referential actions must not have more than one path
> to any given table.
> ******
> Any branch of the tree is terminated when it encounters a table for which
> NO
> ACTION has been specified or is the default.
>
> The problem here is we have two paths to the same table, so one would
> expect
> two deletes to be performed, one for each reference, or one delete where
> either reference exists. I'm not sure if this is a DBMS thing or a SQL
> Server implementation thing. I know that this must be a common issue. I
> figure a trigger could accomplish the same thing (which I think omni was
> suggesting) but it seems to go against DB design to resort to a trigger
> when
> a simple constraint should be sufficent.
>
That's exactly my problem. The database looks like those tables you
described earlier and was ported from Oracle where it was no problems having
two cascading actions. I tried using an ON DELETE CASCADE on FromLocation ID
and additionally an AFTER DELETE trigger on Location but that would work
first after the deletion was successful. The deletion can't be succesful as
long as there are entries in Route referencing ToLocationID.
Holger
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBA9B0E2-CA08-4277-8397-3298494C6F15@.microsoft.com...
> another

Multiple DropDowns error

Hi:

I have two drop downs bound to the same data source.. These dropdowns are automatically populated from a database. When I click the button I get some sort of strange query error.

Not sure what I'm doing wrong here.

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SQLClient" %>

<scriptlanguage="VB"runat="server">

Dim sOrderbyasString

Dim sDirectionasString

Dim MySQLAsString

Dim MySQL1AsString

Dim sSubjectAsString

Dim sCategoryAsString

Sub Page_Load(ByVal SourceAsObject,ByVal EAs EventArgs)

IfNot Page.IsPostBackThen

Dim strConnAsString ="server=GAALP-DT-UHABB2\CFW;uid=sa;pwd=removed;database=NetG"

Dim MySQLAsString ="Select DISTINCT [Subject] from dbo_v_netG_courses"

Dim MySQL1AsString ="Select DISTINCT [Category] from dbo_v_netG_courses"

Dim MyConnAsNew SqlConnection(strConn)

Dim objDRAs SqlDataReader

Dim CmdAsNew SqlCommand(MySQL, MyConn)

Dim Cmd1AsNew SqlCommand(MySQL1, MyConn)

MyConn.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddl.DataSource = objDR

ddl.DataValueField ="Subject"

ddl.DataTextField ="Subject"

ddl.DataBind()

MyConn.Close()

MyConn.Open()

ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlDir.DataValueField ="Category"

ddlDir.DataTextField ="Category"

ddlDir.DataBind()

MyConn.Close()

ddl.Items.Insert(0,"-- Choose --")

ddlDir.Items.Insert(0,"-- Choose --")

EndIf

'ddl.Items.Insert(0, "-- Choose --")

EndSub

' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)

' MyDataGrid.CurrentPageIndex = e.NewPageIndex

' BindData()

'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs)

' MyDataGrid.CurrentPageIndex = 0

'End Sub

'Sub GetData(ByVal Source As Object, ByVal E As EventArgs)

' BindData()

' End Sub

Sub BindData(ByVal SourceAsObject,ByVal EAs EventArgs)

sSubject = ddlDir.SelectedItem.Text

sCategory = ddlDir.SelectedItem.Value

Dim strConnAsString ="server=GAALP-DT-UHABB2\CFW;uid=sa;pwd=removed;database=NetG"

If sSubject =""And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

Else ( THIS LINE IS GIVING ME THE ERROR)

MySQL ="Select * from dbo_v_netG_courses where [Subject] = & sSubject"

EndIf

Dim MyConnAsNew SqlConnection(strConn)

Dim dsAs DataSet =New DataSet()

Dim CmdAsNew SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds,"dbo_v_netG_courses")

MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView

MyDataGrid.DataBind()

EndSub

</script>

<html>

<head>

<metaname="GENERATOR"Content="ASP Express 3.0">

<title>Ad Hoc Sorting with a DataGrid</title>

</head>

<body>

<Formid="form1"runat="server">

<table>

<tr>

<tdalign="Left"valign="Top"><b><i>View Employee Data</i></b></td>

<tdalign="right"valign="Top">

Subject:<asp:dropdownlistid="ddl"runat="server">

</asp:dropdownlist>

Category:<asp:dropdownlistid="ddlDir"runat="server">

</asp:dropdownlist><br/>

<br/>

<asp:Buttonid="btn1"Text="View Records"onclick="BindData"runat="server"/><br/>

</td>

</tr>

<tr>

<tdalign="Left"valign="Top"Colspan="2">

<asp:Datagridrunat="server"

Id="MyDataGrid"

GridLines="Both"

cellpadding="0"

cellspacing="0"

Headerstyle-BackColor="#8080C0"

Headerstyle-Font-Bold="True"

Headerstyle-Font-Size="12"

BackColor="#8080FF"

Font-Size="10"

AlternatingItemStyle-BackColor="#EFEFEF"

AlternatingItemStyle-Font-Size="10"

BorderColor="Black">

</asp:DataGrid><br> </td>

</tr>

</table>

</form>

</body>

</html>

This code works now.. One question though.. How can I return all values by default when the page loads..

Thanks

Working code in VB for multiple dropdown selections and then button click to submit values

<%@.ImportNamespace="System.Data" %>

<%@.ImportNamespace="System.Data.SQLClient" %>

<scriptlanguage="VB"runat="server">

Dim sOrderbyasString

Dim sDirectionasString

Dim MySQLAsString

Dim MySQL1AsString

Dim sSubjectAsString

Dim sCategoryAsString

Sub Page_Load(ByVal SourceAsObject,ByVal EAs EventArgs)

IfNot Page.IsPostBackThen

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

Dim MySQLAsString ="Select DISTINCT [Subject] from dbo_v_netG_courses"

' Dim MySQL1 As String = "Select DISTINCT [Category] from dbo_v_netG_courses"

Dim MyConnAsNew SqlConnection(strConn)

Dim objDRAs SqlDataReader

Dim CmdAsNew SqlCommand(MySQL, MyConn)

' Dim Cmd1 As New SqlCommand(MySQL1, MyConn)

MyConn.Open()

objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddl.DataSource = objDR

ddl.DataValueField ="Subject"

ddl.DataTextField ="Subject"

ddl.DataBind()

MyConn.Close()

' MyConn.Open()

' ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

'ddlDir.DataValueField = "Category"

' ddlDir.DataTextField = "Category"

' ddlDir.DataBind()

'MyConn.Close()

ddl.Items.Insert(0,"-- Choose --")

'ddlDir.Items.Insert(0, "-- Choose --")

EndIf

'ddl.Items.Insert(0, "-- Choose --")

EndSub

Sub fillModel(ByVal SourceAsObject,ByVal EAs EventArgs)

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

If ddl.SelectedItem.Text <>"-- Choose --"Then

sSubject = ddl.SelectedItem.Value

Dim MySQL1AsString ="Select DISTINCT Category from dbo_v_netG_courses where [Subject]=" &"'" & sSubject &"'"

Dim MyConnAsNew SqlConnection(strConn)

'Dim objDR As SqlDataReader

Dim Cmd1AsNew SqlCommand(MySQL1, MyConn)

MyConn.Open()

ddlDir.DataSource = Cmd1.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

ddlDir.DataValueField ="Category"

ddlDir.DataTextField ="Category"

ddlDir.DataBind()

' ddl2.selectedindex=0

' tdmodel.visible = "true"

'lit1.text = "<b>Vehicle Type</b>: " & ddl1.selectedItem.text

' ddl2.items.insert(0, "-- Choose --")

' tdStyle.visible = "false"

MyConn.Close()

EndIf

ddlDir.Items.Insert(0,"-- Choose --")

EndSub

' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)

' MyDataGrid.CurrentPageIndex = e.NewPageIndex

' BindData()

'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs)

' MyDataGrid.CurrentPageIndex = 0

'End Sub

'Sub GetData(ByVal Source As Object, ByVal E As EventArgs)

' BindData()

' End Sub

Sub BindData(ByVal SourceAsObject,ByVal EAs EventArgs)

sSubject = ddl.SelectedItem.Value

sCategory = ddlDir.SelectedItem.Value

Dim strConnAsString ="server=LAPTOP;uid=sa;pwd=sa;database=NetG"

If sSubject =""And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

ElseIf sSubject ="-- Choose --"And sCategory =""Then

MySQL ="Select * from dbo_v_netG_courses"

'Page.IsPostBack = True

Else

MySQL ="Select * from dbo_v_netG_courses where [Subject]=" &"'" & sSubject &"'" &" and [Category] =" &"'" & sCategory &"'"

Dim MyConnAsNew SqlConnection(strConn)

Dim dsAs DataSet =New DataSet()

Dim CmdAsNew SqlDataAdapter(MySQL, MyConn)

Cmd.Fill(ds,"dbo_v_netG_courses")

MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView

MyDataGrid.DataBind()

EndIf

EndSub

</script>

<html>

<head>

<metaname="GENERATOR"Content="ASP Express 3.0">

<title>Ad Hoc Sorting with a DataGrid</title>

</head>

<body>

<Formid="form1"runat="server">

<table>

<tr>

<tdalign="Left"valign="Top"><b><i>View Employee Data</i></b></td>

<tdalign="right"valign="Top">

Subject:<asp:dropdownlistid="ddl"

runat="server"

onselectedindexchanged="fillModel"AutoPostBack="True"

>

</asp:dropdownlist>

Category:<asp:dropdownlistid="ddlDir"runat="server"DataTextField="Model">

</asp:dropdownlist><br/>

<br/>

<asp:Buttonid="btn1"Text="View Records"onclick="BindData"runat="server"/><br/>

</td>

</tr>

<tr>

<tdalign="Left"valign="Top"Colspan="2">

<asp:Datagridrunat="server"

Id="MyDataGrid"

cellpadding="0"

Headerstyle-BackColor="#8080C0"

Headerstyle-Font-Bold="True"

Headerstyle-Font-Size="12"

BackColor="#8080FF"

Font-Size="10pt"

AlternatingItemStyle-BackColor="#EFEFEF"

AlternatingItemStyle-Font-Size="10"

BorderColor="Black"AllowSorting="True">

<AlternatingItemStyleBackColor="#EFEFEF"Font-Size="10pt"/>

<HeaderStyleBackColor="#8080C0"Font-Bold="True"Font-Size="12pt"/>

</asp:DataGrid><br> </td>

</tr>

</table>

</form>

</body>

</html>

|||I assume this is an .NET 1.1 application?