Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...

Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...

Data Source and Connection Manager

Hi all,

I don't really understand the difference between the "data source" and the "connection manager" in a SSIS solution.

I tried to create a data source (if I understood correctly, I will ba able to share that connection between all packages in the solution), then I create a new connection manager inside a package. But when I make a modification in the data source, nothing change in the connection manager ?!!?

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ? If it is I dont't understand the avantage of making a new data source ....

Thanks for your response

In my personal opinion, there is no advantage of data sources. They were created by the Analysis Services team and the Integration Services thought tthey looked like something nice they could use. Rmemeber that they were designed for SSAS though.

In my opinion - don't use them.

-Jamie

|||

jh0483 wrote:

Hi all,

What I think is that when I'm making a new connection manager, what's really happen is a copy of the data source, is that correct ?

That's correct.

I don't use them either. You can run into additional issues when the properties either from the connection manager or from the data source changes; you will start receiving annoying notifications/warnings that your data source and connection manager are not sync every time you open the package.

|||

I think of it like this. You will always a have a connection, but you can attribute the connection as being linked to the data source. See the DataSourceID of a connection. This property maintains the link between the physical package related structure and the ephemeral design-time only data source.

Much like the others I never found it offered much. The design-time management of connections can be achieved through the same methods as you would use for the deployed run-time stuff, i.e. configurations.

|||But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place. If you use only connection manager, then you have to check all your packages.|||

jh0483 wrote:

But for me there is a big advantage : if you have many packages sharing the same connections and you have to change the connection, then you just have to change it in one place.

Have you ever used or at least looked into configurations? They allow you to manage connections through a single point, across multiple packlages and even solutions. They also have the huge benefit of working at runtime, and are a very valuable tool when it comes to deploying your packages.

Use both if you want, but without configurations you will have to make changes as packages get deployed between environments. Data sources do not solve that for you in any effecitive manner. Configurations do, and for my money work just as well when devloping packages.

|||Of course I use configurations ...

But still in your configuration file, if you have 5 packages with 5 connection manager you will have to configure 5 time the connection, which is not the case with the Data source.|||

Ok, good so you hopefully see the benefit of configurations. Your point seemed to be stating that there was a benefit of data sources that was not available with configurations, that was what I disagreed with.

If you have 5 packages and 5 connections, that are the same, presumably linked to the same Data Source to give you that change once, update all effect. This is not solely a benefit of data sources. You can get the same effect by using the same configuration for all five connections. If the connection string changes you update the one configuration store. Whenever the 5 packages are now opened the configuration applies the change, and without the annoying dialog you get with a data source warning you or the discrepancy between the updated data source and soon to be updated connection.

The benefit for me of configurations over data sources is that I can use that same mechanism to change connection strings simply as packages move between development, testing and production for example. Using data sources would require you to open the package in the designer and actually modify the package, albeit with minimal user input since the data source does the hard work, but still having to change packages as part of a deployment process is just wrong in my opinion.

Is your point that that each time you add a distinct connection to a package you need to set that connection as being controlled by a configuration? Data sources will avoid that I agree. Still for me I will always use configurations to solve the deployment issue, so I will incur that cost anyway. It is minimal since there is virtually no reason to have more than one connection object per connection string as SSIS will by default create multiple instances of that connection. This is of course a change from the DTS world if you used that previously, where it was actually good practice to duplicate connections.

Do you see any other benefits of data sources over configurations for connection management?

|||

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

|||

Zadoras wrote:

I am using data connections ( in each package) which is referencing to one of the datasource.... i used this becuase i thought there would be one object i.e. Data Source (for creating connection with SQL sever or ne other database) and that object reference would be used in each package via Connection manager, and that makes it memory efficient because u would not have more objects (connection objects) in memory as connection manager is just a reference to that Data Source Object... connection string can be set up dynamicaly to point that connection object to a database and if connection string changes at run time even then there would be only one object (just pointing to differet database)

can ne one tell me isnt that right? as i hv read on this thread that it copies connection from data source to connection manager .... can ne one tell me ne article in that issue?

ne comments?

regards,

Anas

ne comments please...

Data Pump

My client is using a sql server 7.0 to store realtime data like heat,temp,pressure etc inserted every second.He wants me to provide a solution to transfer the summarised data to oracle server on a regular basis..say once on every 5 min..
what approach i should take...help me out plsSet up a DTS package to send the data from SQL Server to Oracle.
Create a scheduled job to run the package every five minutes.|||In SQL Server 2000 replication can solve your problem but I do not know about SQL Server 7.0.

Blindman's idea is easy & will be work for you...|||I can't recommend that anybody include DTS packages as part of any long-term solution now that SQL Server 2005 no longer supports them.|||I can't recommend that anybody include DTS packages as part of any long-term solution now that SQL Server 2005 no longer supports them.I suppose that by the strictest definition that is true, but I tend to think of SSIS (http://msdn.microsoft.com/SQL/bi/integration/default.aspx) as being the descendant of DTS.

-PatP|||Descendent, bastard child, unholy spawn, however you want to call it...|||Descendent, bastard child, unholy spawn, however you want to call it...So, how do you really feel about SSIS? You don't have to hold back, we understand. ;)

-PatP|||select @.Functionality/@.Documentation

----
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Data ordering in a Column

Get day...
I am new to SQL Server. I am building an .Net solution that uses SQL as the Backoffice. I have a Primary key column that uses char as data type. I use the following example as my key values:
1-1-1
1-1-2
1-x-x
1-1-10
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire this result. Is there a way to FORCE the sequence of my index (values) model.
Thank you in advance.
Sincerely,
Tony D. Abel
That sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel
|||Good day Steve...
Thanks for your reply to my question.
I was hoping that it would be a simple solution to handle in SQL. I will manage the query now on the application side because of the quantity of data that has to be inputted for the SQL backend.
Have a great SQL day.
Sincerely,
Tony

Data ordering in a Column

Get day...
I am new to SQL Server. I am building an .Net solution that uses SQL as the
Backoffice. I have a Primary key column that uses char as data type. I use t
he following example as my key values:
1-1-1
1-1-2
1-x-x
1-1-10
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire t
his result. Is there a way to FORCE the sequence of my index (values) model.
Thank you in advance.
Sincerely,
Tony D. AbelThat sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel|||Good day Steve...
Thanks for your reply to my question.
I was hoping that it would be a simple solution to handle in SQL. I will man
age the query now on the application side because of the quantity of data th
at has to be inputted for the SQL backend.
Have a great SQL day.
Sincerely,
Tony

Data ordering in a Column

Get day..
I am new to SQL Server. I am building an .Net solution that uses SQL as the Backoffice. I have a Primary key column that uses char as data type. I use the following example as my key values
1-1-
1-1-
1-x-
1-1-1
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire this result. Is there a way to FORCE the sequence of my index (values) model
Thank you in advance
Sincerely
Tony D. AbelThat sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel

data modification logging solution

Anyone has a data modification logging solution such that reverting back is
always an option should a need arises for 'undo-ing' a committed transaction
?
--
bicHi
LogPI http://www.logpi.com
Log Explorer http://www.lumigent.com/products/le_sql.html
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bic" wrote:

> Anyone has a data modification logging solution such that reverting back i
s
> always an option should a need arises for 'undo-ing' a committed transacti
on?
> --
> bic|||Hi,
One solution is to audit the data via triggers . A simple approach (one
that uses lots of space) is to dupicate the existing table and add the
following columns
UserName
ChangedDate
ChangeType
Then create three (or one if you like) triggers which will copy the data
from the event in to the table from the trigger tables(Inseted and Deleted)
This approach does use a lot of space but you can get the rows back very
simply
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"bic" <bic@.discussions.microsoft.com> wrote in message
news:FDB88507-3F35-4F09-9816-58D10E6D9165@.microsoft.com...
> Anyone has a data modification logging solution such that reverting back
> is
> always an option should a need arises for 'undo-ing' a committed
> transaction?
> --
> bic

Data Modelling Question with Cascade Deletes

Hi, I have this scenario that keeps coming up on different models and
I'm yet to find a solution.
In this model I have Customers, Customer Recognitions, Sectors and
Divisions.
Sectors and Divisions are related and reference data.
Customers become recognized in sectors, and then within divisions in
that sector.
The problems I have are these:
1) I need to be able to delete data at all levels & can create all but
1 of the cascade rules - because of circular references. In this case
when I delete a Sector I would want to Cascade and delete all related
CustomerSectors
2) Its feels to me like an unnecessary relationships exists in the
database because of the circular design - but I cant decide how and
exactly to remove a relationship.
Can anyone advise?.
Thanks.
CREATE TABLE [dbo].[CustomerSectorDivision] (
[CSD_ID] [smallint] NOT NULL ,
[D_ID] [smallint] NOT NULL ,
[CS_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerRecognition] (
[CR_ID] [smallint] NOT NULL ,
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerSector] (
[CS_ID] [smallint] NOT NULL ,
[CR_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Division] (
[D_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sector] (
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
(
[CSD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[C_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
(
[CR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
(
[CS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
(
[D_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sector] ADD
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[S_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
(
[CS_ID]
) REFERENCES [dbo].[CustomerSector] (
[CS_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
(
[D_ID]
) REFERENCES [dbo].[Division] (
[D_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
(
[C_ID]
) REFERENCES [dbo].[Customer] (
[C_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
(
[CR_ID]
) REFERENCES [dbo].[CustomerRecognition] (
[CR_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
)
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [FK_Division_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
) ON DELETE CASCADE
GOIf customers are in sectors and sectors are in divisions, then the
CustomerSectorDivision table breaks normal form.
Please elaborate on the actual (real-life, not current database model)
relationships between the entities.
ML
http://milambda.blogspot.com/|||Remove the FK from CustomerSector to Sector.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1138707797.465718.285110@.g47g2000cwa.googlegroups.com...
Hi, I have this scenario that keeps coming up on different models and
I'm yet to find a solution.
In this model I have Customers, Customer Recognitions, Sectors and
Divisions.
Sectors and Divisions are related and reference data.
Customers become recognized in sectors, and then within divisions in
that sector.
The problems I have are these:
1) I need to be able to delete data at all levels & can create all but
1 of the cascade rules - because of circular references. In this case
when I delete a Sector I would want to Cascade and delete all related
CustomerSectors
2) Its feels to me like an unnecessary relationships exists in the
database because of the circular design - but I cant decide how and
exactly to remove a relationship.
Can anyone advise?.
Thanks.
CREATE TABLE [dbo].[CustomerSectorDivision] (
[CSD_ID] [smallint] NOT NULL ,
[D_ID] [smallint] NOT NULL ,
[CS_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customer] (
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerRecognition] (
[CR_ID] [smallint] NOT NULL ,
[C_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerSector] (
[CS_ID] [smallint] NOT NULL ,
[CR_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Division] (
[D_ID] [smallint] NOT NULL ,
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sector] (
[S_ID] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
(
[CSD_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[C_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
(
[CR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
(
[CS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
(
[D_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sector] ADD
CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
(
[S_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerSectorDivision] ADD
CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
(
[CS_ID]
) REFERENCES [dbo].[CustomerSector] (
[CS_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
(
[D_ID]
) REFERENCES [dbo].[Division] (
[D_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerRecognition] ADD
CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
(
[C_ID]
) REFERENCES [dbo].[Customer] (
[C_ID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CustomerSector] ADD
CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
(
[CR_ID]
) REFERENCES [dbo].[CustomerRecognition] (
[CR_ID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
)
GO
ALTER TABLE [dbo].[Division] ADD
CONSTRAINT [FK_Division_Sector] FOREIGN KEY
(
[S_ID]
) REFERENCES [dbo].[Sector] (
[S_ID]
) ON DELETE CASCADE
GO|||Because there are two paths to CustomerSectorDivision, this cascading delete
scenario isn't supported. I prefer to avoid all cascading referential
actions because they cede control over the order in which locks are
obtained, which can make it more difficult to minimize deadlocks.
As far as I can tell, there aren't any unnecessary relationships in this
model. Cascading referential actions in SQL Server require that the
relationships form a tree, but networks can and do occur in perfectly valid
models.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1138707797.465718.285110@.g47g2000cwa.googlegroups.com...
> Hi, I have this scenario that keeps coming up on different models and
> I'm yet to find a solution.
> In this model I have Customers, Customer Recognitions, Sectors and
> Divisions.
> Sectors and Divisions are related and reference data.
> Customers become recognized in sectors, and then within divisions in
> that sector.
> The problems I have are these:
> 1) I need to be able to delete data at all levels & can create all but
> 1 of the cascade rules - because of circular references. In this case
> when I delete a Sector I would want to Cascade and delete all related
> CustomerSectors
> 2) Its feels to me like an unnecessary relationships exists in the
> database because of the circular design - but I cant decide how and
> exactly to remove a relationship.
> Can anyone advise?.
> Thanks.
> CREATE TABLE [dbo].[CustomerSectorDivision] (
> [CSD_ID] [smallint] NOT NULL ,
> [D_ID] [smallint] NOT NULL ,
> [CS_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Customer] (
> [C_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerRecognition] (
> [CR_ID] [smallint] NOT NULL ,
> [C_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerSector] (
> [CS_ID] [smallint] NOT NULL ,
> [CR_ID] [smallint] NOT NULL ,
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Division] (
> [D_ID] [smallint] NOT NULL ,
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Sector] (
> [S_ID] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSectorDivision] ADD
> CONSTRAINT [PK_CustomerSectorDivision] PRIMARY KEY CLUSTERED
> (
> [CSD_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Customer] ADD
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [C_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerRecognition] ADD
> CONSTRAINT [PK_CustomerRecognition] PRIMARY KEY CLUSTERED
> (
> [CR_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSector] ADD
> CONSTRAINT [PK_CustomerSector] PRIMARY KEY CLUSTERED
> (
> [CS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Division] ADD
> CONSTRAINT [PK_Division] PRIMARY KEY CLUSTERED
> (
> [D_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Sector] ADD
> CONSTRAINT [PK_Sector] PRIMARY KEY CLUSTERED
> (
> [S_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerSectorDivision] ADD
> CONSTRAINT [FK_CustomerSectorDivision_CustomerSecto
r] FOREIGN KEY
> (
> [CS_ID]
> ) REFERENCES [dbo].[CustomerSector] (
> [CS_ID]
> ) ON DELETE CASCADE ,
> CONSTRAINT [FK_CustomerSectorDivision_Division] FOREIGN KEY
> (
> [D_ID]
> ) REFERENCES [dbo].[Division] (
> [D_ID]
> ) ON DELETE CASCADE
> GO
> ALTER TABLE [dbo].[CustomerRecognition] ADD
> CONSTRAINT [FK_CustomerRecognition_Customer] FOREIGN KEY
> (
> [C_ID]
> ) REFERENCES [dbo].[Customer] (
> [C_ID]
> ) ON DELETE CASCADE
> GO
> ALTER TABLE [dbo].[CustomerSector] ADD
> CONSTRAINT [FK_CustomerSector_CustomerRecognition] FOREIGN KEY
> (
> [CR_ID]
> ) REFERENCES [dbo].[CustomerRecognition] (
> [CR_ID]
> ) ON DELETE CASCADE ,
> CONSTRAINT [FK_CustomerSector_Sector] FOREIGN KEY
> (
> [S_ID]
> ) REFERENCES [dbo].[Sector] (
> [S_ID]
> )
> GO
> ALTER TABLE [dbo].[Division] ADD
> CONSTRAINT [FK_Division_Sector] FOREIGN KEY
> (
> [S_ID]
> ) REFERENCES [dbo].[Sector] (
> [S_ID]
> ) ON DELETE CASCADE
> GO
>|||It appears to me that divisions are in sectors. I see nothing wrong with
the model.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:C08CF6D6-993B-4C78-9AD0-C6EC4CE9BCBA@.microsoft.com...
> If customers are in sectors and sectors are in divisions, then the
> CustomerSectorDivision table breaks normal form.
> Please elaborate on the actual (real-life, not current database model)
> relationships between the entities.
>
> ML
> --
> http://milambda.blogspot.com/|||I don't see what the CustomerSectorDivision is there for. The relationship i
s
already defined thorugh the CustomerSector and Division tables, isn't it?
ML
http://milambda.blogspot.com/|||Sectors are areas of industry, Divisions are just sub-sectors within
e.g Finance has several divisions Basic Accounts, Taxation, Assets &
Liabilities. Customers s recognition first in a sector and then for
divisions within the sector.
A customer cannot have recognition for a division without the
corresponsing sector.|||Not necessarily. Since there's a 1:0..n relationship between Sector and
Division, it follows that there could also be a 1:0..n relationship between
CustomerSector and Division. CustomerSectorDivision provides a place to
store information that is related only to the juxtaposition of a
CustomerSector and a Division.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DEB5E66A-CE4D-43A3-AA63-7A2CB1A15CF1@.microsoft.com...
>I don't see what the CustomerSectorDivision is there for. The relationship
>is
> already defined thorugh the CustomerSector and Division tables, isn't it?
>
> ML
> --
> http://milambda.blogspot.com/|||I see. Now I understand. However, I'd try to further isolate individual
relationships.
Entities:
Sectors
Divisions
Customers
Relationships:
Sector <-- Customer (actual relationship, but I wouldn't implement it
directly in the physical model)
Sector <-- Division
Sector <-- Division <-- Customer (actual relationship, but I wouldn't
implement it directly)
Tables:
Sectors (SectorId : ...)
Divisions (DivisionId : ...)
Customers (CustomerId : ...)
SectorDivisions (SectorDivisionId : SectorId : DivisionId)
DivisionCustomers (SectorDivisionId : CustomerId)
Views:
SectorDivisionCustomers (SectorId : DivisionId : CustomerId)
SectorCustomers (SectorId : CustomerId)
I believe this model supports cascading foreign keys better. But one
question remains - can a Customer belong to a Sector without belonging to a
Division?
ML
http://milambda.blogspot.com/|||The relationship between a Customer and a Sector is implied by the foreign
key constraint between CustomerSector and CustomerRecognition and the
foreign key constraint between CustomerRecognition and Customer.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:002BDCD5-0F8E-4BBE-A4AC-1F996289717F@.microsoft.com...
> That is if the direct relationship between a Customer and a Sector is
> valid.
>
> ML
> --
> http://milambda.blogspot.com/

Data mining licensing requirements

I am developing a data mining solution for a client using SQL 2005.

Do they have to purchase a separate license for a replicated environment? Like a hot site....

Thanks, ]

BI Joe

Data Mining is part of SQL Server and has the same licensing requirements. E.g. if it's on the web, than you need the per processor license, etc.

I'm not an expert on all of the licensing issues, but, in short, there's no difference between using the relational engine, reporting services, analysis services, etc - the licensing requirements are the same.

data mining

1)In solution explorer,what is data source folder?

I assume this is in the context of an Analysis Services project.

The data source folder contains data sources, connections to relational databases that contain the data to be analyzed by your project