We are in process of using new Kimball toolkit book as our guide to create another DM. Since we only have one data source, we think we need to pull in all the tables into the staging area. At first we wanted to just pull in minimal data to populate the Dimension/Fact tables and any information needed to derive columns.
However the business area wants a complete snapshot of ALL THE data for this given subject matter. Their reason is that sometimes our CUBES don't have all the information they want, and they want to be able to query the data mart tables against the staging area tables using SQL or Access. Since our data mart load times is first Saturday of the month - this argues in favor of this. If the load process was every day or once a week - it might be better to just query across the data mart and the reporting (replicated production) database - the the data mart tables would be older than the information they were linking against on the replicated report DB using the 1st Saturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Staging to OLAP DB - which is really just another OLTP DB but the tables are modeled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access, has a big manual step. After all the ETL happens and the Dim/Fact tables have been updated - we are having to open up SSAS and process the cubes, the first business day after the 1st Saturday, to get them refreshed - can't this be automated within SSIS?
4. There have been some major production changes to our existing data source for our existing production data mart. All the ETL was done on SQL2k with stored proc's and DTS was only used as the policeman to control the flow. All the SCD processes were done and are working fine using SP's. We now have to make changes and are faced with having two complete different systems to manage - one with an ETL using the old method and the 2nd using SSIS. Some of the CUBES from the original data mart are going to be combined with our new cubes we are creating in our new system. To try and make it clearer - we have for example a Client dimension and a Client fact table that first is refreshed monthly with the old ETL, and now our new subject matter is now also going to affect the Client dimension and Client fact table that was created the first project. The old production changes it appears may be many weeks to adjust using the old ETL methods - so it begs the question, since all the ETL is working fine with DTS as flow master - is it perhaps better to get it all working with SSIS calling all the SP's and incorporate all our new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL package or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services Administration
I think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> We are in process of using new Kimball toolkit book as our guide to create another DM. Since we only have one data source, we think we need to pull in all the tables into the staging area. At first we wanted to just pull in minimal data to populate the Dimension/Fact tables and any information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE data for this given subject matter. Their reason is that sometimes our CUBES don't have all the information they want, and they want to be able to query the data mart tables against the staging area tables using SQL or Access. Since our data mart load times is first Saturday of the month - this argues in favor of this. If the load process was every day or once a week -it might be better to just query across the data mart and the reporting (replicated production) database - the the data mart tables would be older than the information they were linking against on the replicated report DB using the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to Staging to OLAP DB - which is really just another OLTP DB but the tables aremodeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to access, has a big manual step. After all the ETL happens and the Dim/Fact tables have been updated - we are having to open up SSAS and process the cubes, the first business day after the 1st Saturday, to get them refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing data source for our existing production data mart. All the ETL was done on SQL2k with stored proc's and DTS was only used as the policeman to control the flow. All the SCD processes were done and are working fine using SP's. We now have to make changes and are faced with having two complete different systems to manage - one with an ETL using the old method and the 2nd using SSIS. Some of the CUBES from the original data mart are going to be combined with our new cubes we are creating in our new system. To try and make it clearer - we have for example a Client dimension and a Client fact table that first is refreshed monthly with the old ETL, and now our new subject matter is now also going to affect the Client dimension and Client fact table that was created the first project. The old production changes it appears may be many weeks to adjust using the old ETL methods - so it begs the question, since all the ETL is working fine with DTS as flow master - is it perhaps better to get it all working with SSIS calling all the SP's and incorporate all our new ETL within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL package or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:
>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>
|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Thursday, March 29, 2012
ETL / Data mart / Cube question
We are in process of using new Kimball toolkit book as our guide t
o create another DM. Since we only have one data source, we think we need t
o pull in all the tables into the staging area. At first we wanted to just
pull in minimal data to populate the Dimension/Fact tables and any informati
on needed to derive columns.
However the business area wants a complete snapshot of ALL THE dat
a for this given subject matter. Their reason is that sometimes our CUBES d
on't have all the information they want, and they want to be able to query t
he data mart tables against the staging area tables using SQL or Access. Si
nce our data mart load times is first Saturday of the month - this argues in
favor of this. If the load process was every day or once a week - it might
be better to just query across the data mart and the reporting (replicated p
roduction) database - the the data mart tables would be older than the infor
mation they were linking against on the replicated report DB using the 1st S
aturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Stag
ing to OLAP DB - which is really just another OLTP DB but the tables are mod
eled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access,
has a big manual step. After all the ETL happens and the Dim/Fact tables ha
ve been updated - we are having to open up SSAS and process the cubes, the f
irst business day after the 1st Saturday, to get them refreshed - can't this
be automated within SSIS?
4. There have been some major production changes to our existing data
source for our existing production data mart. All the ETL was done on SQL2k
with stored proc's and DTS was only used as the policeman to control the fl
ow. All the SCD processes were done and are working fine using SP's. We no
w have to make changes and are faced with having two complete different syst
ems to manage - one with an ETL using the old method and the 2nd using SSIS.
Some of the CUBES from the original data mart are going to be combined wit
h our new cubes we are creating in our new system. To try and make it clear
er - we have for example a Client dimension and a Client fact table that fir
st is refreshed monthly with the old ETL, and now our new subject matter is
now also going to affect the Client dimension and Client fact table that was
created the first project. The old production changes it appears may be m
any weeks to adjust using the old ETL methods - so it begs the question, sin
ce all the ETL is working fine with DTS as flow master - is it perhaps bette
r to get it all working with SSIS calling all the SP's and incorporate all o
ur new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL packag
e or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services AdministrationI think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> =B7 We are in process of using new Kimball toolkit book as our gu=
ide to create another DM. Since we only have one data source, we think we =
need to pull in all the tables into the staging area. At first we wanted t=
o just pull in minimal data to populate the Dimension/Fact tables and any i=
nformation needed to derive columns. =20
> =B7 However the business area wants a complete snapshot of ALL TH=
E data for this given subject matter. Their reason is that sometimes our C=
UBES don't have all the information they want, and they want to be able to =
query the data mart tables against the staging area tables using SQL or Acc=
ess. Since our data mart load times is first Saturday of the month - this =
argues in favor of this. If the load process was every day or once a week -=
it might be better to just query across the data mart and the reporting (r=
eplicated production) database - the the data mart tables would be older th=
an the information they were linking against on the replicated report DB us=
ing the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach? =20
> 2. According to a Microsoft diagram, the load goes from Source to S=
taging to OLAP DB - which is really just another OLTP DB but the tables are=
modeled as Dimension/Fact tables, right? =20
> 3. Our existing data mart which everyone mostly uses CUBES to acces=
s, has a big manual step. After all the ETL happens and the Dim/Fact table=
s have been updated - we are having to open up SSAS and process the cubes, =
the first business day after the 1st Saturday, to get them refreshed - can'=
t this be automated within SSIS?
> 4. There have been some major production changes to our existing da=
ta source for our existing production data mart. All the ETL was done on S=
QL2k with stored proc's and DTS was only used as the policeman to control t=
he flow. All the SCD processes were done and are working fine using SP's. =
We now have to make changes and are faced with having two complete differe=
nt systems to manage - one with an ETL using the old method and the 2nd usi=
ng SSIS. Some of the CUBES from the original data mart are going to be com=
bined with our new cubes we are creating in our new system. To try and mak=
e it clearer - we have for example a Client dimension and a Client fact tab=
le that first is refreshed monthly with the old ETL, and now our new subjec=
t matter is now also going to affect the Client dimension and Client fact t=
able that was created the first project. The old production changes it ap=
pears may be many weeks to adjust using the old ETL methods - so it begs th=
e question, since all the ETL is working fine with DTS as flow master - is =
it perhaps better to get it all working with SSIS calling all the SP's and =
incorporate all our new ETL within SSIS using the new features that SSIS ca=
n offer? =20
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL pac=
kage or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:
>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:
>
o create another DM. Since we only have one data source, we think we need t
o pull in all the tables into the staging area. At first we wanted to just
pull in minimal data to populate the Dimension/Fact tables and any informati
on needed to derive columns.
However the business area wants a complete snapshot of ALL THE dat
a for this given subject matter. Their reason is that sometimes our CUBES d
on't have all the information they want, and they want to be able to query t
he data mart tables against the staging area tables using SQL or Access. Si
nce our data mart load times is first Saturday of the month - this argues in
favor of this. If the load process was every day or once a week - it might
be better to just query across the data mart and the reporting (replicated p
roduction) database - the the data mart tables would be older than the infor
mation they were linking against on the replicated report DB using the 1st S
aturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Stag
ing to OLAP DB - which is really just another OLTP DB but the tables are mod
eled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access,
has a big manual step. After all the ETL happens and the Dim/Fact tables ha
ve been updated - we are having to open up SSAS and process the cubes, the f
irst business day after the 1st Saturday, to get them refreshed - can't this
be automated within SSIS?
4. There have been some major production changes to our existing data
source for our existing production data mart. All the ETL was done on SQL2k
with stored proc's and DTS was only used as the policeman to control the fl
ow. All the SCD processes were done and are working fine using SP's. We no
w have to make changes and are faced with having two complete different syst
ems to manage - one with an ETL using the old method and the 2nd using SSIS.
Some of the CUBES from the original data mart are going to be combined wit
h our new cubes we are creating in our new system. To try and make it clear
er - we have for example a Client dimension and a Client fact table that fir
st is refreshed monthly with the old ETL, and now our new subject matter is
now also going to affect the Client dimension and Client fact table that was
created the first project. The old production changes it appears may be m
any weeks to adjust using the old ETL methods - so it begs the question, sin
ce all the ETL is working fine with DTS as flow master - is it perhaps bette
r to get it all working with SSIS calling all the SP's and incorporate all o
ur new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL packag
e or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services AdministrationI think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> =B7 We are in process of using new Kimball toolkit book as our gu=
ide to create another DM. Since we only have one data source, we think we =
need to pull in all the tables into the staging area. At first we wanted t=
o just pull in minimal data to populate the Dimension/Fact tables and any i=
nformation needed to derive columns. =20
> =B7 However the business area wants a complete snapshot of ALL TH=
E data for this given subject matter. Their reason is that sometimes our C=
UBES don't have all the information they want, and they want to be able to =
query the data mart tables against the staging area tables using SQL or Acc=
ess. Since our data mart load times is first Saturday of the month - this =
argues in favor of this. If the load process was every day or once a week -=
it might be better to just query across the data mart and the reporting (r=
eplicated production) database - the the data mart tables would be older th=
an the information they were linking against on the replicated report DB us=
ing the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach? =20
> 2. According to a Microsoft diagram, the load goes from Source to S=
taging to OLAP DB - which is really just another OLTP DB but the tables are=
modeled as Dimension/Fact tables, right? =20
> 3. Our existing data mart which everyone mostly uses CUBES to acces=
s, has a big manual step. After all the ETL happens and the Dim/Fact table=
s have been updated - we are having to open up SSAS and process the cubes, =
the first business day after the 1st Saturday, to get them refreshed - can'=
t this be automated within SSIS?
> 4. There have been some major production changes to our existing da=
ta source for our existing production data mart. All the ETL was done on S=
QL2k with stored proc's and DTS was only used as the policeman to control t=
he flow. All the SCD processes were done and are working fine using SP's. =
We now have to make changes and are faced with having two complete differe=
nt systems to manage - one with an ETL using the old method and the 2nd usi=
ng SSIS. Some of the CUBES from the original data mart are going to be com=
bined with our new cubes we are creating in our new system. To try and mak=
e it clearer - we have for example a Client dimension and a Client fact tab=
le that first is refreshed monthly with the old ETL, and now our new subjec=
t matter is now also going to affect the Client dimension and Client fact t=
able that was created the first project. The old production changes it ap=
pears may be many weeks to adjust using the old ETL methods - so it begs th=
e question, since all the ETL is working fine with DTS as flow master - is =
it perhaps better to get it all working with SSIS calling all the SP's and =
incorporate all our new ETL within SSIS using the new features that SSIS ca=
n offer? =20
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL pac=
kage or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:
>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:
>
Estimating time for creation of clustered index
I asked my SQL Server to create me a clustered index on a 200,000ish
row table. It's taking rather longer than I expected. But, I don't
really know how long I should have expected it to take. How can I
estimate that?
Thanks!
IonDo you have a backup of the database? A pretty reliable way to see how this
will impact your production system is to restore it (on another server,
another instance, or on the same instance with a different database name),
and create the clustered index on the copy. It will be slightly affected by
factors such as different hardware and different activity, but unless the
situation is extreme, it should be within an order of magnitude.
I have not seen anything that resembles a formula for predicting how long a
clustered index will take, without doing any actual work. There are so many
variables involved, I think it will be every difficult to approach anything
even remotely trustworthy.
<ionFreeman@.gmail.com> wrote in message
news:1142528571.960565.264770@.j33g2000cwa.googlegroups.com...
>I asked my SQL Server to create me a clustered index on a 200,000ish
> row table. It's taking rather longer than I expected. But, I don't
> really know how long I should have expected it to take. How can I
> estimate that?
> Thanks!
> Ion
>
row table. It's taking rather longer than I expected. But, I don't
really know how long I should have expected it to take. How can I
estimate that?
Thanks!
IonDo you have a backup of the database? A pretty reliable way to see how this
will impact your production system is to restore it (on another server,
another instance, or on the same instance with a different database name),
and create the clustered index on the copy. It will be slightly affected by
factors such as different hardware and different activity, but unless the
situation is extreme, it should be within an order of magnitude.
I have not seen anything that resembles a formula for predicting how long a
clustered index will take, without doing any actual work. There are so many
variables involved, I think it will be every difficult to approach anything
even remotely trustworthy.
<ionFreeman@.gmail.com> wrote in message
news:1142528571.960565.264770@.j33g2000cwa.googlegroups.com...
>I asked my SQL Server to create me a clustered index on a 200,000ish
> row table. It's taking rather longer than I expected. But, I don't
> really know how long I should have expected it to take. How can I
> estimate that?
> Thanks!
> Ion
>
Tuesday, March 27, 2012
estimate size of table based on number of rows
Hi everyone,
This might be a tough one, I don't know if this is doable.
Basically I want to create a stored procedure to return estimated size of a
table.
because I don't know how many rows it will have in the future, I want to
calcuate how much disk space it takes to have one row,
then multiply by number of rows I specified. (space calculation for index is
not necessary).
EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
it'll return value in KBs after I execute proc. possible?If you run :
EXEC dbo.sp_spaceused table_name
You get the current space usage. Divide it by the current number of rows,
and multiply by the projected one.
If the table is completely empty or you'd rather calculate theoretical size,
there are formulas you can use from BOL, or better yet, a lengthy discussion
on internal structures and row sizes in Inside SQL Server 2000.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> This might be a tough one, I don't know if this is doable.
> Basically I want to create a stored procedure to return estimated size of
> a table.
> because I don't know how many rows it will have in the future, I want to
> calcuate how much disk space it takes to have one row,
> then multiply by number of rows I specified. (space calculation for index
> is not necessary).
>
> EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
>
> it'll return value in KBs after I execute proc. possible?
>
>
>
>
>
>|||Have you looked in the Books Online for sp_spaceused? It'll get you
part of the way there because it returns the rows used and the current
size of the table. Here's a quick and dirty stab at it; obviously
it'll need polishing:
This is actually a pretty useful idea; I'm planning on using this
myself.
Stu
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'Splat'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100))
--how big is the table now?
exec sp_spaceused @.Table
INSERT INTO #t
exec sp_spaceused @.Table
--strip off the ' KB' from the data column
--convert data and rows to decimal, and
--divide data by number of rows and multiply by number of anticipated
rows
SELECT rows, data, (data/rows) * @.NumberOfRows
FROM ( SELECT rows = CONVERT(decimal(32,3), rows),
data = CONVERT(decimal(32,3), LEFT(data, LEN(data)-3))
FROM #t) x
DROP TABLE #t
HTH,
Stu|||The following may help:
http://www.microsoft.com/downloads/...&displaylang=en
If you need it in a SP, try to understand the formulas used in the
spreadsheet and translate them in T-SQL (using the data from syscolumns
and other system tables).
Razvan|||I see 4 columns called reserved , index_size, unused, data
I guess I need to add 4 columns to get the total size, then divide by number
of row to find out how much disk space per row?
then disk space per row * estimate number of rows to find out estimate size?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> If you run :
> EXEC dbo.sp_spaceused table_name
> You get the current space usage. Divide it by the current number of rows,
> and multiply by the projected one.
> If the table is completely empty or you'd rather calculate theoretical
> size, there are formulas you can use from BOL, or better yet, a lengthy
> discussion on internal structures and row sizes in Inside SQL Server 2000.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>|||You need the data + index_size.
Reserved includes: data + index_size + unused.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" wrote:
> I see 4 columns called reserved , index_size, unused, data
> I guess I need to add 4 columns to get the total size, then divide by numb
er
> of row to find out how much disk space per row?
> then disk space per row * estimate number of rows to find out estimate siz
e?
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in messa
ge
> news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
>
>|||but I have a NTEXT column,
I don't think it can calculate NTEXT.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124386169.690259.289640@.z14g2000cwz.googlegroups.com...
> The following may help:
> http://www.microsoft.com/downloads/...&displaylang=en
> If you need it in a SP, try to understand the formulas used in the
> spreadsheet and translate them in T-SQL (using the data from syscolumns
> and other system tables).
> Razvan
>|||Indeed, ntext columns are not covered by the spreadsheed.
In the DataSizer.doc file, they wrote:
The tool does not include the formula to estimate the size of a table
that has Text columns. Not NULL text values consume 16 bytes in the
data row and have a minimum size of 84 bytes on the text page. Text
values are packed onto text pages with the same algorithm as data
rows so it should be possible to estimate the size of text data
storage using the HEAP table spreadsheet if you know the average size
of your text values.
Razvan|||if I use sp_spaceused 'tablename' command,
if that table has a few NText Columns,
I think it will calculate total spaces including disk spaces for NTEXT
column, correct?
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124483366.622335.202490@.g49g2000cwa.googlegroups.com...
> Indeed, ntext columns are not covered by the spreadsheed.
> In the DataSizer.doc file, they wrote:
> The tool does not include the formula to estimate the size of a table
> that has Text columns. Not NULL text values consume 16 bytes in the
> data row and have a minimum size of 84 bytes on the text page. Text
> values are packed onto text pages with the same algorithm as data
> rows so it should be possible to estimate the size of text data
> storage using the HEAP table spreadsheet if you know the average size
> of your text values.
> Razvan
>
This might be a tough one, I don't know if this is doable.
Basically I want to create a stored procedure to return estimated size of a
table.
because I don't know how many rows it will have in the future, I want to
calcuate how much disk space it takes to have one row,
then multiply by number of rows I specified. (space calculation for index is
not necessary).
EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
it'll return value in KBs after I execute proc. possible?If you run :
EXEC dbo.sp_spaceused table_name
You get the current space usage. Divide it by the current number of rows,
and multiply by the projected one.
If the table is completely empty or you'd rather calculate theoretical size,
there are formulas you can use from BOL, or better yet, a lengthy discussion
on internal structures and row sizes in Inside SQL Server 2000.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> This might be a tough one, I don't know if this is doable.
> Basically I want to create a stored procedure to return estimated size of
> a table.
> because I don't know how many rows it will have in the future, I want to
> calcuate how much disk space it takes to have one row,
> then multiply by number of rows I specified. (space calculation for index
> is not necessary).
>
> EXEC GetEstimateTableSize @.TableName='Table1', @.NumberOfRows ='3000000'
>
> it'll return value in KBs after I execute proc. possible?
>
>
>
>
>
>|||Have you looked in the Books Online for sp_spaceused? It'll get you
part of the way there because it returns the rows used and the current
size of the table. Here's a quick and dirty stab at it; obviously
it'll need polishing:
This is actually a pretty useful idea; I'm planning on using this
myself.
Stu
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'Splat'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100))
--how big is the table now?
exec sp_spaceused @.Table
INSERT INTO #t
exec sp_spaceused @.Table
--strip off the ' KB' from the data column
--convert data and rows to decimal, and
--divide data by number of rows and multiply by number of anticipated
rows
SELECT rows, data, (data/rows) * @.NumberOfRows
FROM ( SELECT rows = CONVERT(decimal(32,3), rows),
data = CONVERT(decimal(32,3), LEFT(data, LEN(data)-3))
FROM #t) x
DROP TABLE #t
HTH,
Stu|||The following may help:
http://www.microsoft.com/downloads/...&displaylang=en
If you need it in a SP, try to understand the formulas used in the
spreadsheet and translate them in T-SQL (using the data from syscolumns
and other system tables).
Razvan|||I see 4 columns called reserved , index_size, unused, data
I guess I need to add 4 columns to get the total size, then divide by number
of row to find out how much disk space per row?
then disk space per row * estimate number of rows to find out estimate size?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> If you run :
> EXEC dbo.sp_spaceused table_name
> You get the current space usage. Divide it by the current number of rows,
> and multiply by the projected one.
> If the table is completely empty or you'd rather calculate theoretical
> size, there are formulas you can use from BOL, or better yet, a lengthy
> discussion on internal structures and row sizes in Inside SQL Server 2000.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:uWKIMMBpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>|||You need the data + index_size.
Reserved includes: data + index_size + unused.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" wrote:
> I see 4 columns called reserved , index_size, unused, data
> I guess I need to add 4 columns to get the total size, then divide by numb
er
> of row to find out how much disk space per row?
> then disk space per row * estimate number of rows to find out estimate siz
e?
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in messa
ge
> news:etjHceBpFHA.2888@.TK2MSFTNGP10.phx.gbl...
>
>|||but I have a NTEXT column,
I don't think it can calculate NTEXT.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124386169.690259.289640@.z14g2000cwz.googlegroups.com...
> The following may help:
> http://www.microsoft.com/downloads/...&displaylang=en
> If you need it in a SP, try to understand the formulas used in the
> spreadsheet and translate them in T-SQL (using the data from syscolumns
> and other system tables).
> Razvan
>|||Indeed, ntext columns are not covered by the spreadsheed.
In the DataSizer.doc file, they wrote:
The tool does not include the formula to estimate the size of a table
that has Text columns. Not NULL text values consume 16 bytes in the
data row and have a minimum size of 84 bytes on the text page. Text
values are packed onto text pages with the same algorithm as data
rows so it should be possible to estimate the size of text data
storage using the HEAP table spreadsheet if you know the average size
of your text values.
Razvan|||if I use sp_spaceused 'tablename' command,
if that table has a few NText Columns,
I think it will calculate total spaces including disk spaces for NTEXT
column, correct?
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1124483366.622335.202490@.g49g2000cwa.googlegroups.com...
> Indeed, ntext columns are not covered by the spreadsheed.
> In the DataSizer.doc file, they wrote:
> The tool does not include the formula to estimate the size of a table
> that has Text columns. Not NULL text values consume 16 bytes in the
> data row and have a minimum size of 84 bytes on the text page. Text
> values are packed onto text pages with the same algorithm as data
> rows so it should be possible to estimate the size of text data
> storage using the HEAP table spreadsheet if you know the average size
> of your text values.
> Razvan
>
Thursday, March 22, 2012
escape character for '\' in SQL server 2000
Hello Experts,
We have a SQL server which we have with the name 'SELDSQL533\PRD3', on another server i am trying to create a synonym for a particular database and table inside it. but i get a below error for '\'
Is there any escape character to be used to overcome this problem
CREATE SYNONYM Project
FOR SELDSQL533\PRD3.DMS_copy.project
--
Error: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
-
/chandresh
use the following query...
CREATE SYNONYM Project
FOR [SELDSQL533\PRD3].DMS_copy.dbo.project
|||Use square brackets [ ] around any name that contains 'unacceptable' characters.
CREATE SYNONYM Project
FOR [SELDSQL533\PRD3].DMS_copy.project
erwin and data modeling
I have just started using erwin on a new database project that would be part
of our existing data warehouse.
Currently, I use sql scripts to create table, stored procs etc. and then I
update the erwin data model. This is not a good idea because I would rather
drive my data model from central location such as erwin tool. I tried to
drive data structure from the tool but its not working
What are some of the best practices to use erwin with regards to data
modeling? Are there any sites, white papers that talk about how to best use
erwin? Online help for erwin is not much useful.
I could not find any articles on google on erwin best practices etc.
Please let me know.
TIA...Didn;t you evaluate these kinds of things / support options prior to
selecting Erwin as the tool of choice? That would be a solid best practise
for a start.
RR
"sqlster" <nospam@.nospam.com> wrote in message
news:8F357659-2CFE-40C5-9779-3D5B769EBE74@.microsoft.com...
> I have just started using erwin on a new database project that would be
part
> of our existing data warehouse.
> Currently, I use sql scripts to create table, stored procs etc. and then I
> update the erwin data model. This is not a good idea because I would
rather
> drive my data model from central location such as erwin tool. I tried to
> drive data structure from the tool but its not working
> What are some of the best practices to use erwin with regards to data
> modeling? Are there any sites, white papers that talk about how to best
use
> erwin? Online help for erwin is not much useful.
> I could not find any articles on google on erwin best practices etc.
> Please let me know.
> TIA...|||head on over to http://www.infoadvisors.com/
they have active newsgroups for many things erwin, and competeing products t
oo
good luck
"sqlster" <nospam@.nospam.com> wrote in message news:8F357659-2CFE-40C5-9779-3D5B769EBE74@.mi
crosoft.com...
>I have just started using erwin on a new database project that would be par
t
> of our existing data warehouse.
> Currently, I use sql scripts to create table, stored procs etc. and then I
> update the erwin data model. This is not a good idea because I would rathe
r
> drive my data model from central location such as erwin tool. I tried to
> drive data structure from the tool but its not working
> What are some of the best practices to use erwin with regards to data
> modeling? Are there any sites, white papers that talk about how to best us
e
> erwin? Online help for erwin is not much useful.
> I could not find any articles on google on erwin best practices etc.
> Please let me know.
> TIA...sql
of our existing data warehouse.
Currently, I use sql scripts to create table, stored procs etc. and then I
update the erwin data model. This is not a good idea because I would rather
drive my data model from central location such as erwin tool. I tried to
drive data structure from the tool but its not working
What are some of the best practices to use erwin with regards to data
modeling? Are there any sites, white papers that talk about how to best use
erwin? Online help for erwin is not much useful.
I could not find any articles on google on erwin best practices etc.
Please let me know.
TIA...Didn;t you evaluate these kinds of things / support options prior to
selecting Erwin as the tool of choice? That would be a solid best practise
for a start.
RR
"sqlster" <nospam@.nospam.com> wrote in message
news:8F357659-2CFE-40C5-9779-3D5B769EBE74@.microsoft.com...
> I have just started using erwin on a new database project that would be
part
> of our existing data warehouse.
> Currently, I use sql scripts to create table, stored procs etc. and then I
> update the erwin data model. This is not a good idea because I would
rather
> drive my data model from central location such as erwin tool. I tried to
> drive data structure from the tool but its not working
> What are some of the best practices to use erwin with regards to data
> modeling? Are there any sites, white papers that talk about how to best
use
> erwin? Online help for erwin is not much useful.
> I could not find any articles on google on erwin best practices etc.
> Please let me know.
> TIA...|||head on over to http://www.infoadvisors.com/
they have active newsgroups for many things erwin, and competeing products t
oo
good luck
"sqlster" <nospam@.nospam.com> wrote in message news:8F357659-2CFE-40C5-9779-3D5B769EBE74@.mi
crosoft.com...
>I have just started using erwin on a new database project that would be par
t
> of our existing data warehouse.
> Currently, I use sql scripts to create table, stored procs etc. and then I
> update the erwin data model. This is not a good idea because I would rathe
r
> drive my data model from central location such as erwin tool. I tried to
> drive data structure from the tool but its not working
> What are some of the best practices to use erwin with regards to data
> modeling? Are there any sites, white papers that talk about how to best us
e
> erwin? Online help for erwin is not much useful.
> I could not find any articles on google on erwin best practices etc.
> Please let me know.
> TIA...sql
ERWIN
I'm working with a developer that used ERWIN to create the database I have
to interface with. At first glance I noticed that there are a ton of
unecessary triggers, many of which look like they won't even be hit because
they're trying to detect and raise error messages for FK violations (when
there are already FK contraints).
I don't like what I'm seeing so far. Can anyone give me the lowdown about
using this tool with SS2K?
PaulHehe, I've just spent the last two months working with some scripts that
were generated from ERWin.
In a nutshell, the tool is great for reverse engineering a database and
creating pretty DB Diagrams for documentation. Other than this, I don't
like the program myself as I prefer to see the entire table - not just the
columns as FK's, but also datatypes all constraints, indexes and any default
data which is inserted at table creation time.
I'm sure that there are people who love the tool, and to them I say good for
you. It's a personal choice really.
The triggers that your talking about sound like the developer added these
not realising the performance hit. Sounds like you'll have alot of work to
do. Good luck
Regards
Colin Dawson
www.cjdawson.com
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
> because they're trying to detect and raise error messages for FK
> violations (when there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||Erwin is a great tool and I use it religiously. However, it has it's
quirks. You have to be careful to set settings right to make sure that
those triggers aren't created.
I rarely use their native code and constraint generation, so I cannot give
you too much advice, but it is very easy to use and very extensible (which
is why I love it.)
I just created these two tables (ignore the types) and ran it with the
default generation. Everything is good (enough) until the triggers, so I
would just get rid of the triggers.
CREATE TABLE E_1 (
e1Id char(18) NOT NULL,
value char(18) NULL
)
go
ALTER TABLE E_1
ADD PRIMARY KEY (e1Id ASC)
go
CREATE TABLE E_2 (
e1Id char(18) NOT NULL,
value char(18) NULL,
e2Id char(18) NOT NULL
)
go
ALTER TABLE E_2
ADD PRIMARY KEY (e2Id ASC)
go
ALTER TABLE E_2
ADD FOREIGN KEY (e1Id)
REFERENCES E_1 (e1Id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
create trigger tD_E_1 on E_1 for DELETE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* DELETE trigger on E_1 */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if exists (
select * from deleted,E_2
where
/* %JoinFKPK(E_2,deleted," = "," and") */
E_2.e1Id = deleted.e1Id
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE E_1 because E_2 exists.'
goto error
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tU_E_1 on E_1 for UPDATE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* UPDATE trigger on E_1 */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.inse1Id char(18),
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if
/* %ParentPK(" or",update) */
update(e1Id)
begin
if exists (
select * from deleted,E_2
where
/* %JoinFKPK(E_2,deleted," = "," and") */
E_2.e1Id = deleted.e1Id
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE E_1 because E_2 exists.'
goto error
end
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tD_E_2 on E_2 for DELETE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* DELETE trigger on E_2 */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON CHILD DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if exists (select * from deleted,E_1
where
/* %JoinFKPK(deleted,E_1," = "," and") */
deleted.e1Id = E_1.e1Id and
not exists (
select * from E_2
where
/* %JoinFKPK(E_2,E_1," = "," and") */
E_2.e1Id = E_1.e1Id
)
)
begin
select @.errno = 30010,
@.errmsg = 'Cannot DELETE last E_2 because E_1 exists.'
goto error
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tU_E_2 on E_2 for UPDATE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* UPDATE trigger on E_2 */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.inse2Id char(18),
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON CHILD UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if
/* %ChildFK(" or",update) */
update(e1Id)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,E_1
where
/* %JoinFKPK(inserted,E_1) */
inserted.e1Id = E_1.e1Id
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.e1Id is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE E_2 because E_1 does not exist.'
goto error
end
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
> because they're trying to detect and raise error messages for FK
> violations (when there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||I think this is better...
CREATE TABLE E_1(
e1Id char(18) NOT NULL PRIMARY KEY,
value char(18) NULL
)
go
CREATE TABLE E_2 (
e1Id char(18) NOT NULL ADD PRIMARY KEY,
value char(18) NULL,
e2Id char(18) NOT NULL,
CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
ON UPDATE NO ACTION
)
go
Or better still
CREATE TABLE E_1(
e1Id char(18) NOT NULL,
value char(18) NULL
CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
)
go
CREATE TABLE E_2(
e1Id char(18) NOT NULL,
value char(18) NULL,
e2Id char(18) NOT NULL,
CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE
NO ACTION ON UPDATE NO ACTION
)
go
Here's what's been added - all constraints are now named, the entire table
is now seen and known (I've not included filegroups in the above example
though) The entire table is listed in one foul swoop, everything is shown
together at the same time, with the exception of listing the tables which
depend on this table but that's another issue. These are the things that I
think are lacking from products like ERWin. Don't mistunderstand me, I
think they great tools for showing a table graphically, but for creating
databases... I think there's alot to be desired. Also looking at the
graphical tool, it's not easy to see what check constraints, defaults and
indexes are available - the front end just doesn't show them. ERWin is good
for the job is was designed for though - to show the relationship between
tables.
Regards
Colin Dawson
www.cjdawson.com|||Hi
I'm also new to SQL and am using Xcase Ver 7x Anybody know any advantages,
di
vantages etc with it.
So far it seems to be ok creating, modifying tables, constraints etc
"Programming proves that Natural Selection is true, since every time I
idiot-proof a program a better idiot comes along.
Robert W. Drummond
John Linville
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>I think this is better...
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL PRIMARY KEY,
> value char(18) NULL
> )
> go
> CREATE TABLE E_2 (
> e1Id char(18) NOT NULL ADD PRIMARY KEY,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
> ON UPDATE NO ACTION
> )
> go
> Or better still
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL,
> value char(18) NULL
> CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
> )
> go
> CREATE TABLE E_2(
> e1Id char(18) NOT NULL,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
> CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON
> DELETE NO ACTION ON UPDATE NO ACTION
> )
> go
> Here's what's been added - all constraints are now named, the entire table
> is now seen and known (I've not included filegroups in the above example
> though) The entire table is listed in one foul swoop, everything is shown
> together at the same time, with the exception of listing the tables which
> depend on this table but that's another issue. These are the things that
> I think are lacking from products like ERWin. Don't mistunderstand me, I
> think they great tools for showing a table graphically, but for creating
> databases... I think there's alot to be desired. Also looking at the
> graphical tool, it's not easy to see what check constraints, defaults and
> indexes are available - the front end just doesn't show them. ERWin is
> good for the job is was designed for though - to show the relationship
> between tables.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Yes, clearly this is better, and why I have built lots of macro code to
implement this in ERwin. Alters are much easier to automatically generate,
especially since you can do them again later (after dropping them of
course.) The company I worked for a long time ago (and I work for them
again today :) had a consultant who also worked with the original company
that owned/wrote the original ERwin product (when it was pretty much all
that there was.) Hence we have grown an insane number of macros that deal
with everything, even turning on and off triggers, custom triggers,
constraints, indexes, everything. It was not easy, but my code is really
really useful, especially during rapid development because I can modify all
of the objects in just minutes.
I occasionally use the base ERwin functionality but only when I am in a
hurry to do something small. I used to try to lobby for changes, but
after CA purchased it I just have never found the right way, and I have all
of this code that works. The only real problem is that it is such a bother
to use I cannot even get coworkers to use it, much less sharing it with the
world. Perhaps I will do something like that and post it on my blog one
day.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>I think this is better...
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL PRIMARY KEY,
> value char(18) NULL
> )
> go
> CREATE TABLE E_2 (
> e1Id char(18) NOT NULL ADD PRIMARY KEY,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
> ON UPDATE NO ACTION
> )
> go
> Or better still
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL,
> value char(18) NULL
> CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
> )
> go
> CREATE TABLE E_2(
> e1Id char(18) NOT NULL,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
> CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON
> DELETE NO ACTION ON UPDATE NO ACTION
> )
> go
> Here's what's been added - all constraints are now named, the entire table
> is now seen and known (I've not included filegroups in the above example
> though) The entire table is listed in one foul swoop, everything is shown
> together at the same time, with the exception of listing the tables which
> depend on this table but that's another issue. These are the things that
> I think are lacking from products like ERWin. Don't mistunderstand me, I
> think they great tools for showing a table graphically, but for creating
> databases... I think there's alot to be desired. Also looking at the
> graphical tool, it's not easy to see what check constraints, defaults and
> indexes are available - the front end just doesn't show them. ERWin is
> good for the job is was designed for though - to show the relationship
> between tables.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Sounds to me like we're singing from the same hymm sheet. Just different
parts of it. Currently I'm a script jockey, as you saw from my previous
post I like to get things neat done one etc. as DBA for the company that I
work for, I'm trying to tighten up the database structures to make sure that
all foreign keys check constraints proper indexing etc, is in place. As I
don't have an unlimited amount of time to get everything into place and
working 100%, I took the decision early on that ERWin was as you say "it is
such a bother to use". For documentation ERWin is brilliant for creating
documentation, the diagrams are much better looking that the diagrams from
SQL Server, so the company tend to use these. Also lesser experiences
developers can use the tool for creating a basic table structure,
brainstorming and stuff like that to get something off the ground. Once
they're happy with the design, I have to perseude them to generate a script
then go through it with a fine too comb to be sure that they've not missed
anything. After they've finished, it's my turn and I spend ages giving the
developer the third degree and adding about a million extra contraints and
mocking their indexing ideas. However once corrected the database is
extremely well designed. I just wish that ERWin could read in a script and
learn the style that was used to create that script, then when using ERWin
to generate a script it generates a script as close a possible to the
original.
I've waffled for too long on this now.
Regards
Colin Dawson
www.cjdawson.com
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uAtpGSspFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Yes, clearly this is better, and why I have built lots of macro code to
> implement this in ERwin. Alters are much easier to automatically
> generate, especially since you can do them again later (after dropping
> them of course.) The company I worked for a long time ago (and I work
> for them again today :) had a consultant who also worked with the original
> company that owned/wrote the original ERwin product (when it was pretty
> much all that there was.) Hence we have grown an insane number of macros
> that deal with everything, even turning on and off triggers, custom
> triggers, constraints, indexes, everything. It was not easy, but my code
> is really really useful, especially during rapid development because I can
> modify all of the objects in just minutes.
> I occasionally use the base ERwin functionality but only when I am in a
> hurry to do something small. I used to try to lobby for changes, but
> after CA purchased it I just have never found the right way, and I have
> all of this code that works. The only real problem is that it is such a
> bother to use I cannot even get coworkers to use it, much less sharing it
> with the world. Perhaps I will do something like that and post it on my
> blog one day.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>|||I used ERWIN for a short time myself and I did like it up to a point.
Unfortunately, it doesn't support SQL Server 2000's declarative referential
integrity (DRI) and creates triggers to cascade updates and deletes to
related tables.
For this reason alone I stopped using ERWIN. I found that the triggers
remained after I altered table/column names or relationships, which caused
updates/deletes to fail until I realised what was happening.
I'm sure it's great for SQL 7, but it would have to be configured properly
to be worthwhile in SQL 2000. IMO.
Thanks. Dan.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
because
> they're trying to detect and raise error messages for FK violations (when
> there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||I think so to. The basics we are both saying is that ERWin output sucks.
Cannot disagree with that, especially since I wrote my own script
generators, albeit in the ERwin tool :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:bdgOe.93743$G8.59922@.text.news.blueyonder.co.uk...
> Sounds to me like we're singing from the same hymm sheet. Just different
> parts of it. Currently I'm a script jockey, as you saw from my previous
> post I like to get things neat done one etc. as DBA for the company that I
> work for, I'm trying to tighten up the database structures to make sure
> that all foreign keys check constraints proper indexing etc, is in place.
> As I don't have an unlimited amount of time to get everything into place
> and working 100%, I took the decision early on that ERWin was as you say
> "it is such a bother to use". For documentation ERWin is brilliant for
> creating documentation, the diagrams are much better looking that the
> diagrams from SQL Server, so the company tend to use these. Also lesser
> experiences developers can use the tool for creating a basic table
> structure, brainstorming and stuff like that to get something off the
> ground. Once they're happy with the design, I have to perseude them to
> generate a script then go through it with a fine too comb to be sure that
> they've not missed anything. After they've finished, it's my turn and I
> spend ages giving the developer the third degree and adding about a
> million extra contraints and mocking their indexing ideas. However once
> corrected the database is extremely well designed. I just wish that
> ERWin could read in a script and learn the style that was used to create
> that script, then when using ERWin to generate a script it generates a
> script as close a possible to the original.
> I've waffled for too long on this now.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uAtpGSspFHA.3004@.TK2MSFTNGP15.phx.gbl...
>sql
to interface with. At first glance I noticed that there are a ton of
unecessary triggers, many of which look like they won't even be hit because
they're trying to detect and raise error messages for FK violations (when
there are already FK contraints).
I don't like what I'm seeing so far. Can anyone give me the lowdown about
using this tool with SS2K?
PaulHehe, I've just spent the last two months working with some scripts that
were generated from ERWin.
In a nutshell, the tool is great for reverse engineering a database and
creating pretty DB Diagrams for documentation. Other than this, I don't
like the program myself as I prefer to see the entire table - not just the
columns as FK's, but also datatypes all constraints, indexes and any default
data which is inserted at table creation time.
I'm sure that there are people who love the tool, and to them I say good for
you. It's a personal choice really.
The triggers that your talking about sound like the developer added these
not realising the performance hit. Sounds like you'll have alot of work to
do. Good luck
Regards
Colin Dawson
www.cjdawson.com
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
> because they're trying to detect and raise error messages for FK
> violations (when there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||Erwin is a great tool and I use it religiously. However, it has it's
quirks. You have to be careful to set settings right to make sure that
those triggers aren't created.
I rarely use their native code and constraint generation, so I cannot give
you too much advice, but it is very easy to use and very extensible (which
is why I love it.)
I just created these two tables (ignore the types) and ran it with the
default generation. Everything is good (enough) until the triggers, so I
would just get rid of the triggers.
CREATE TABLE E_1 (
e1Id char(18) NOT NULL,
value char(18) NULL
)
go
ALTER TABLE E_1
ADD PRIMARY KEY (e1Id ASC)
go
CREATE TABLE E_2 (
e1Id char(18) NOT NULL,
value char(18) NULL,
e2Id char(18) NOT NULL
)
go
ALTER TABLE E_2
ADD PRIMARY KEY (e2Id ASC)
go
ALTER TABLE E_2
ADD FOREIGN KEY (e1Id)
REFERENCES E_1 (e1Id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
create trigger tD_E_1 on E_1 for DELETE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* DELETE trigger on E_1 */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON PARENT DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if exists (
select * from deleted,E_2
where
/* %JoinFKPK(E_2,deleted," = "," and") */
E_2.e1Id = deleted.e1Id
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE E_1 because E_2 exists.'
goto error
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tU_E_1 on E_1 for UPDATE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* UPDATE trigger on E_1 */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.inse1Id char(18),
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON PARENT UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if
/* %ParentPK(" or",update) */
update(e1Id)
begin
if exists (
select * from deleted,E_2
where
/* %JoinFKPK(E_2,deleted," = "," and") */
E_2.e1Id = deleted.e1Id
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE E_1 because E_2 exists.'
goto error
end
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tD_E_2 on E_2 for DELETE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* DELETE trigger on E_2 */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON CHILD DELETE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if exists (select * from deleted,E_1
where
/* %JoinFKPK(deleted,E_1," = "," and") */
deleted.e1Id = E_1.e1Id and
not exists (
select * from E_2
where
/* %JoinFKPK(E_2,E_1," = "," and") */
E_2.e1Id = E_1.e1Id
)
)
begin
select @.errno = 30010,
@.errmsg = 'Cannot DELETE last E_2 because E_1 exists.'
goto error
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
go
create trigger tU_E_2 on E_2 for UPDATE as
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* UPDATE trigger on E_2 */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.inse2Id char(18),
@.errno int,
@.errmsg varchar(255)
select @.numrows = @.@.rowcount
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
/* E_1 R/1 E_2 ON CHILD UPDATE NO ACTION */
/* ERWIN_RELATION:PARENT_OWNER="", PARENT_TABLE="E_1"
CHILD_OWNER="", CHILD_TABLE="E_2"
P2C_VERB_PHRASE="R/1", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_1", FK_COLUMNS="e1Id" */
if
/* %ChildFK(" or",update) */
update(e1Id)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,E_1
where
/* %JoinFKPK(inserted,E_1) */
inserted.e1Id = E_1.e1Id
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.e1Id is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE E_2 because E_1 does not exist.'
goto error
end
end
/* ERwin Builtin Sun Aug 21 15:19:26 2005 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
> because they're trying to detect and raise error messages for FK
> violations (when there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||I think this is better...
CREATE TABLE E_1(
e1Id char(18) NOT NULL PRIMARY KEY,
value char(18) NULL
)
go
CREATE TABLE E_2 (
e1Id char(18) NOT NULL ADD PRIMARY KEY,
value char(18) NULL,
e2Id char(18) NOT NULL,
CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
ON UPDATE NO ACTION
)
go
Or better still
CREATE TABLE E_1(
e1Id char(18) NOT NULL,
value char(18) NULL
CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
)
go
CREATE TABLE E_2(
e1Id char(18) NOT NULL,
value char(18) NULL,
e2Id char(18) NOT NULL,
CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE
NO ACTION ON UPDATE NO ACTION
)
go
Here's what's been added - all constraints are now named, the entire table
is now seen and known (I've not included filegroups in the above example
though) The entire table is listed in one foul swoop, everything is shown
together at the same time, with the exception of listing the tables which
depend on this table but that's another issue. These are the things that I
think are lacking from products like ERWin. Don't mistunderstand me, I
think they great tools for showing a table graphically, but for creating
databases... I think there's alot to be desired. Also looking at the
graphical tool, it's not easy to see what check constraints, defaults and
indexes are available - the front end just doesn't show them. ERWin is good
for the job is was designed for though - to show the relationship between
tables.
Regards
Colin Dawson
www.cjdawson.com|||Hi
I'm also new to SQL and am using Xcase Ver 7x Anybody know any advantages,
di
vantages etc with it.So far it seems to be ok creating, modifying tables, constraints etc
"Programming proves that Natural Selection is true, since every time I
idiot-proof a program a better idiot comes along.
Robert W. Drummond
John Linville
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>I think this is better...
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL PRIMARY KEY,
> value char(18) NULL
> )
> go
> CREATE TABLE E_2 (
> e1Id char(18) NOT NULL ADD PRIMARY KEY,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
> ON UPDATE NO ACTION
> )
> go
> Or better still
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL,
> value char(18) NULL
> CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
> )
> go
> CREATE TABLE E_2(
> e1Id char(18) NOT NULL,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
> CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON
> DELETE NO ACTION ON UPDATE NO ACTION
> )
> go
> Here's what's been added - all constraints are now named, the entire table
> is now seen and known (I've not included filegroups in the above example
> though) The entire table is listed in one foul swoop, everything is shown
> together at the same time, with the exception of listing the tables which
> depend on this table but that's another issue. These are the things that
> I think are lacking from products like ERWin. Don't mistunderstand me, I
> think they great tools for showing a table graphically, but for creating
> databases... I think there's alot to be desired. Also looking at the
> graphical tool, it's not easy to see what check constraints, defaults and
> indexes are available - the front end just doesn't show them. ERWin is
> good for the job is was designed for though - to show the relationship
> between tables.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Yes, clearly this is better, and why I have built lots of macro code to
implement this in ERwin. Alters are much easier to automatically generate,
especially since you can do them again later (after dropping them of
course.) The company I worked for a long time ago (and I work for them
again today :) had a consultant who also worked with the original company
that owned/wrote the original ERwin product (when it was pretty much all
that there was.) Hence we have grown an insane number of macros that deal
with everything, even turning on and off triggers, custom triggers,
constraints, indexes, everything. It was not easy, but my code is really
really useful, especially during rapid development because I can modify all
of the objects in just minutes.
I occasionally use the base ERwin functionality but only when I am in a
hurry to do something small. I used to try to lobby for changes, but
after CA purchased it I just have never found the right way, and I have all
of this code that works. The only real problem is that it is such a bother
to use I cannot even get coworkers to use it, much less sharing it with the
world. Perhaps I will do something like that and post it on my blog one
day.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>I think this is better...
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL PRIMARY KEY,
> value char(18) NULL
> )
> go
> CREATE TABLE E_2 (
> e1Id char(18) NOT NULL ADD PRIMARY KEY,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON DELETE NO ACTION
> ON UPDATE NO ACTION
> )
> go
> Or better still
> CREATE TABLE E_1(
> e1Id char(18) NOT NULL,
> value char(18) NULL
> CONSTRAINT PK_E_1_e1Id PRIMARY KEY (e1Id ASC)
> )
> go
> CREATE TABLE E_2(
> e1Id char(18) NOT NULL,
> value char(18) NULL,
> e2Id char(18) NOT NULL,
> CONSTRAINT PK_E_2_e2Id PRIMARY KEY (e2Id ASC),
> CONSTRAINT FK_E_2_e1Id FOREIGN KEY (e1Id) REFERENCES E_1 (e1Id) ON
> DELETE NO ACTION ON UPDATE NO ACTION
> )
> go
> Here's what's been added - all constraints are now named, the entire table
> is now seen and known (I've not included filegroups in the above example
> though) The entire table is listed in one foul swoop, everything is shown
> together at the same time, with the exception of listing the tables which
> depend on this table but that's another issue. These are the things that
> I think are lacking from products like ERWin. Don't mistunderstand me, I
> think they great tools for showing a table graphically, but for creating
> databases... I think there's alot to be desired. Also looking at the
> graphical tool, it's not easy to see what check constraints, defaults and
> indexes are available - the front end just doesn't show them. ERWin is
> good for the job is was designed for though - to show the relationship
> between tables.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Sounds to me like we're singing from the same hymm sheet. Just different
parts of it. Currently I'm a script jockey, as you saw from my previous
post I like to get things neat done one etc. as DBA for the company that I
work for, I'm trying to tighten up the database structures to make sure that
all foreign keys check constraints proper indexing etc, is in place. As I
don't have an unlimited amount of time to get everything into place and
working 100%, I took the decision early on that ERWin was as you say "it is
such a bother to use". For documentation ERWin is brilliant for creating
documentation, the diagrams are much better looking that the diagrams from
SQL Server, so the company tend to use these. Also lesser experiences
developers can use the tool for creating a basic table structure,
brainstorming and stuff like that to get something off the ground. Once
they're happy with the design, I have to perseude them to generate a script
then go through it with a fine too comb to be sure that they've not missed
anything. After they've finished, it's my turn and I spend ages giving the
developer the third degree and adding about a million extra contraints and
mocking their indexing ideas. However once corrected the database is
extremely well designed. I just wish that ERWin could read in a script and
learn the style that was used to create that script, then when using ERWin
to generate a script it generates a script as close a possible to the
original.
I've waffled for too long on this now.
Regards
Colin Dawson
www.cjdawson.com
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uAtpGSspFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Yes, clearly this is better, and why I have built lots of macro code to
> implement this in ERwin. Alters are much easier to automatically
> generate, especially since you can do them again later (after dropping
> them of course.) The company I worked for a long time ago (and I work
> for them again today :) had a consultant who also worked with the original
> company that owned/wrote the original ERwin product (when it was pretty
> much all that there was.) Hence we have grown an insane number of macros
> that deal with everything, even turning on and off triggers, custom
> triggers, constraints, indexes, everything. It was not easy, but my code
> is really really useful, especially during rapid development because I can
> modify all of the objects in just minutes.
> I occasionally use the base ERwin functionality but only when I am in a
> hurry to do something small. I used to try to lobby for changes, but
> after CA purchased it I just have never found the right way, and I have
> all of this code that works. The only real problem is that it is such a
> bother to use I cannot even get coworkers to use it, much less sharing it
> with the world. Perhaps I will do something like that and post it on my
> blog one day.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:zp6Oe.93559$G8.66384@.text.news.blueyonder.co.uk...
>|||I used ERWIN for a short time myself and I did like it up to a point.
Unfortunately, it doesn't support SQL Server 2000's declarative referential
integrity (DRI) and creates triggers to cascade updates and deletes to
related tables.
For this reason alone I stopped using ERWIN. I found that the triggers
remained after I altered table/column names or relationships, which caused
updates/deletes to fail until I realised what was happening.
I'm sure it's great for SQL 7, but it would have to be configured properly
to be worthwhile in SQL 2000. IMO.
Thanks. Dan.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23%23eGBBmpFHA.2888@.TK2MSFTNGP10.phx.gbl...
> I'm working with a developer that used ERWIN to create the database I have
> to interface with. At first glance I noticed that there are a ton of
> unecessary triggers, many of which look like they won't even be hit
because
> they're trying to detect and raise error messages for FK violations (when
> there are already FK contraints).
> I don't like what I'm seeing so far. Can anyone give me the lowdown about
> using this tool with SS2K?
> Paul
>|||I think so to. The basics we are both saying is that ERWin output sucks.
Cannot disagree with that, especially since I wrote my own script
generators, albeit in the ERwin tool :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:bdgOe.93743$G8.59922@.text.news.blueyonder.co.uk...
> Sounds to me like we're singing from the same hymm sheet. Just different
> parts of it. Currently I'm a script jockey, as you saw from my previous
> post I like to get things neat done one etc. as DBA for the company that I
> work for, I'm trying to tighten up the database structures to make sure
> that all foreign keys check constraints proper indexing etc, is in place.
> As I don't have an unlimited amount of time to get everything into place
> and working 100%, I took the decision early on that ERWin was as you say
> "it is such a bother to use". For documentation ERWin is brilliant for
> creating documentation, the diagrams are much better looking that the
> diagrams from SQL Server, so the company tend to use these. Also lesser
> experiences developers can use the tool for creating a basic table
> structure, brainstorming and stuff like that to get something off the
> ground. Once they're happy with the design, I have to perseude them to
> generate a script then go through it with a fine too comb to be sure that
> they've not missed anything. After they've finished, it's my turn and I
> spend ages giving the developer the third degree and adding about a
> million extra contraints and mocking their indexing ideas. However once
> corrected the database is extremely well designed. I just wish that
> ERWin could read in a script and learn the style that was used to create
> that script, then when using ERWin to generate a script it generates a
> script as close a possible to the original.
> I've waffled for too long on this now.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uAtpGSspFHA.3004@.TK2MSFTNGP15.phx.gbl...
>sql
Wednesday, March 21, 2012
Errors while upsizing Access database to SQLS2k
I am trying to upsize an Access database to SQL Server 2000. I select
"Create new database" option.
I select "LOCAL" as the database. and check [x] use trusted connection.
SQL server is on this local machine. I supply a name for the new
database and click [Next>] and I get the following error.
================================================== ===============
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.
================================================== ===============
Why am I getting these errors?
SQL Server is installed to accept mixed mode (or Windows authentication
mode)
SQL Server is running.
VS.Net Professional is loaded on this system so an ODBC driver should
be available.
What steps, other than I am taking should I be doing?
Are you doing this on the machine that MSDE is running on? If you are trying
to do this remotely then you will not be able to connect to the database
server, since you supplied DISABLENETWORKPROTOCOLS=1 as a parameter to your
installation. Also since you installed a named instance you will need to
supply the full name to the instance, which in your case will be <your
machine name>\HipMSDE. (local) will not work as the database server name, as
it will try to connect to a server with only the same name as the machine
and not the named instance.
Jim
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1107749903.811964.185300@.g14g2000cwa.googlegr oups.com...
>I am trying to upsize an Access database to SQL Server 2000. I select
> "Create new database" option.
> I select "LOCAL" as the database. and check [x] use trusted connection.
> SQL server is on this local machine. I supply a name for the new
> database and click [Next>] and I get the following error.
> ================================================== ===============
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access denied.
> ================================================== ===============
> Why am I getting these errors?
> SQL Server is installed to accept mixed mode (or Windows authentication
> mode)
> SQL Server is running.
> VS.Net Professional is loaded on this system so an ODBC driver should
> be available.
> What steps, other than I am taking should I be doing?
>
|||Jim,
Thank you for your reply.
When I changed the name to the name of my computer "SEDNA", it worked!
I thought "(local)" was the proper name to use because the upsizing
wizard offered it in the list. I thought it "discovered" that name, and
after all the name in Enterprize manager is "(local)" not "SEDNA". Now
that you let me in on this sectret, I know. Thank you for tell me the
other scret, that I can upsize to MSDE using "SEDNA\HipMSDE". There is
know whay I could have guessed this!
This all frustrates me very much! How am I supposed to know to use
"SEDNA" rather than "(local)" when "(local)" is offered to me in the
list? and Enterprize manager does not use "SEDNA"? How am I supposed to
know the sintax "SEDNA\HipMSDE" when that is NOT offered in the list?
How am I supposed to know the secret code "SEDNA\HipMSDE"? Nothing
anywhere tells me this sintax!!?!
> it will try to connect to a server with only the same name as the
machine
> and not the named instance
Where the #%$* am I supposed to discover this! I've been reading these
instructions for two days!!!?! I have not seen anything even hinting to
this.
Why does the system mislead me and accept "(local)" under othere
contexts such as VB.NET connection strings. It was the VB.NET
connection string context that lead me to guess "SEDNA" because that
works in a connection string even though the name of the instance is
NOT!!!! "SEDNA", its the name of the machine.
Where do I learn all these mysteries?
Doug
Jim Young wrote:
> Are you doing this on the machine that MSDE is running on? If you are
trying
> to do this remotely then you will not be able to connect to the
database
> server, since you supplied DISABLENETWORKPROTOCOLS=1 as a parameter
to your
> installation. Also since you installed a named instance you will need
to
> supply the full name to the instance, which in your case will be
<your
> machine name>\HipMSDE. (local) will not work as the database server
name, as
> it will try to connect to a server with only the same name as the
machine[vbcol=seagreen]
> and not the named instance.
> Jim
> "dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
> news:1107749903.811964.185300@.g14g2000cwa.googlegr oups.com...
select[vbcol=seagreen]
connection.[vbcol=seagreen]
exist[vbcol=seagreen]
authentication[vbcol=seagreen]
should[vbcol=seagreen]
|||hi,
dbuchanan wrote:
> This all frustrates me very much! How am I supposed to know to use
> "SEDNA" rather than "(local)" when "(local)" is offered to me in the
> list? and Enterprize manager does not use "SEDNA"? How am I supposed
> to know the sintax "SEDNA\HipMSDE" when that is NOT offered in the
> list? How am I supposed to know the secret code "SEDNA\HipMSDE"?
> Nothing anywhere tells me this sintax!!?!
>.....
http://msdn.microsoft.com/library/de...setup_2xmb.asp
you can download BOL (BookOnLine) from
http://www.microsoft.com/sql/techinf...2000/books.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
"Create new database" option.
I select "LOCAL" as the database. and check [x] use trusted connection.
SQL server is on this local machine. I supply a name for the new
database and click [Next>] and I get the following error.
================================================== ===============
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.
================================================== ===============
Why am I getting these errors?
SQL Server is installed to accept mixed mode (or Windows authentication
mode)
SQL Server is running.
VS.Net Professional is loaded on this system so an ODBC driver should
be available.
What steps, other than I am taking should I be doing?
Are you doing this on the machine that MSDE is running on? If you are trying
to do this remotely then you will not be able to connect to the database
server, since you supplied DISABLENETWORKPROTOCOLS=1 as a parameter to your
installation. Also since you installed a named instance you will need to
supply the full name to the instance, which in your case will be <your
machine name>\HipMSDE. (local) will not work as the database server name, as
it will try to connect to a server with only the same name as the machine
and not the named instance.
Jim
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1107749903.811964.185300@.g14g2000cwa.googlegr oups.com...
>I am trying to upsize an Access database to SQL Server 2000. I select
> "Create new database" option.
> I select "LOCAL" as the database. and check [x] use trusted connection.
> SQL server is on this local machine. I supply a name for the new
> database and click [Next>] and I get the following error.
> ================================================== ===============
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access denied.
> ================================================== ===============
> Why am I getting these errors?
> SQL Server is installed to accept mixed mode (or Windows authentication
> mode)
> SQL Server is running.
> VS.Net Professional is loaded on this system so an ODBC driver should
> be available.
> What steps, other than I am taking should I be doing?
>
|||Jim,
Thank you for your reply.
When I changed the name to the name of my computer "SEDNA", it worked!
I thought "(local)" was the proper name to use because the upsizing
wizard offered it in the list. I thought it "discovered" that name, and
after all the name in Enterprize manager is "(local)" not "SEDNA". Now
that you let me in on this sectret, I know. Thank you for tell me the
other scret, that I can upsize to MSDE using "SEDNA\HipMSDE". There is
know whay I could have guessed this!
This all frustrates me very much! How am I supposed to know to use
"SEDNA" rather than "(local)" when "(local)" is offered to me in the
list? and Enterprize manager does not use "SEDNA"? How am I supposed to
know the sintax "SEDNA\HipMSDE" when that is NOT offered in the list?
How am I supposed to know the secret code "SEDNA\HipMSDE"? Nothing
anywhere tells me this sintax!!?!
> it will try to connect to a server with only the same name as the
machine
> and not the named instance
Where the #%$* am I supposed to discover this! I've been reading these
instructions for two days!!!?! I have not seen anything even hinting to
this.
Why does the system mislead me and accept "(local)" under othere
contexts such as VB.NET connection strings. It was the VB.NET
connection string context that lead me to guess "SEDNA" because that
works in a connection string even though the name of the instance is
NOT!!!! "SEDNA", its the name of the machine.
Where do I learn all these mysteries?
Doug
Jim Young wrote:
> Are you doing this on the machine that MSDE is running on? If you are
trying
> to do this remotely then you will not be able to connect to the
database
> server, since you supplied DISABLENETWORKPROTOCOLS=1 as a parameter
to your
> installation. Also since you installed a named instance you will need
to
> supply the full name to the instance, which in your case will be
<your
> machine name>\HipMSDE. (local) will not work as the database server
name, as
> it will try to connect to a server with only the same name as the
machine[vbcol=seagreen]
> and not the named instance.
> Jim
> "dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
> news:1107749903.811964.185300@.g14g2000cwa.googlegr oups.com...
select[vbcol=seagreen]
connection.[vbcol=seagreen]
exist[vbcol=seagreen]
authentication[vbcol=seagreen]
should[vbcol=seagreen]
|||hi,
dbuchanan wrote:
> This all frustrates me very much! How am I supposed to know to use
> "SEDNA" rather than "(local)" when "(local)" is offered to me in the
> list? and Enterprize manager does not use "SEDNA"? How am I supposed
> to know the sintax "SEDNA\HipMSDE" when that is NOT offered in the
> list? How am I supposed to know the secret code "SEDNA\HipMSDE"?
> Nothing anywhere tells me this sintax!!?!
>.....
http://msdn.microsoft.com/library/de...setup_2xmb.asp
you can download BOL (BookOnLine) from
http://www.microsoft.com/sql/techinf...2000/books.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Sunday, March 11, 2012
errors in storedprocedures
Is there a function that will indicate if there is an error in your stored procedure? Like for example, I want to create a stored procedure and if there is an error I want it to print out an error. How can I do this?It' called the query analyzer and there are 2 options the parse button and the execute button.|||Thanks, I am aware of the query panel. Are you suggestion that there isn't anything else? I know you can use the SQL server agent or DTS to do some error handling on your job but I wanted to know if there was a function you could add similar to the onerror function in visual basic.|||Check @.@.error in BOL.|||Sure
http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx
http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx
Friday, March 9, 2012
Errors creating push subscriptions...
Hi All,
Had to recreate our SQLServer instance after a rebulid of
the server. Unfortuantely I was not able to
recreate/reload a backup so had to create from scratch.
In trying to setup replication I have the following error
message trying to create a push subscriptions:
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
'FHMYDATABSE: Error 14053: Cannot load the DLL
replincrementlsn extended procedure, or one of the DLLs it
references. Reason: 126(The specified module could not be
found.).
The subscription could not be updated at this time.
The subscription could not be created.'
Any ideas? I tried locating the extended proc mention but
no luck. Any help very much appreciated, as I've had this
problem for about 2 weeks now and the powers that bey want
a swift fix! Thanks...
Davej
Dave,
perhaps your service pack failed to fully install. Have a look at sqlsp.log
file from the c:\windows directory this might shed some light on any failed
actions during the sp installation.
HTH,
Paul Ibison
|||Thanks Paul for the note. I re-ran the sp update and it
must of cured something because the first error has gone
but I get the following!
FHMYDATABASE: Error 515: Cannot insert the value NULL
into column 'step_name', table 'msdb.dbo.sysjobsteps';
column does not allow nulls. INSERT fails.
why? any ideas?
Cheers...
>--Original Message--
>Dave,
>perhaps your service pack failed to fully install. Have a
look at sqlsp.log
>file from the c:\windows directory this might shed some
light on any failed
>actions during the sp installation.
>HTH,
>Paul Ibison
>
>.
>
|||Dave,do you have any remaining subscriptions/jobs from the previous
implementation of replication perhaps? You might try running
sp_remopvedbreplication on the databases involved prior to setting it up
again. If there are any orphaned records in the table
MSreplication_subscriptions on the subscriber database then these can be
deleted. After that, refresh the jobs folder and then implement as per
normal.
HTH,
Paul Ibison
|||Hi Paul,
Did what you suggested. Went right back to basics removing
replication from the database. Then went trough the
procedure using stored procs insetadt of EM. Came to
creating a push sub and got the following, bit more
detailed errors:
Server: Msg 515, Level 16, State 2, Procedure
sp_add_jobstep_internal, Line 129
Cannot insert the value NULL into column 'step_name',
table 'msdb.dbo.sysjobsteps'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
Server: Msg 14266, Level 16, State 1, Procedure
sp_verify_jobstep, Line 46
The specified '@.step_id' is invalid (valid values are:
1..1).
Server: Msg 14070, Level 16, State 1, Procedure
sp_changesubstatus, Line 993
Could not update the distribution database subscription
table. The subscription status could not be changed.
Server: Msg 14057, Level 16, State 1, Procedure
sp_addsubscription, Line 971
The subscription could not be created.
the script is:
use qboard
go
/* adds a publication to the database*/
exec sp_addpublication @.publication = 'Q Published Data
(from Script)'
,@.sync_method = 'native'
,@.repl_freq = 'continuous'
,@.status = 'active'
,@.allow_push = 'true'
go
/* adds a subscription to the publication just defined */
exec sp_addsubscription @.publication = 'Q Published Data
(from Script)'
,@.subscriber = 'FHQDB'
,@.subscription_type = 'push'
go
Is there someting I'm missing?
Thanks for any insights...
Davej
>--Original Message--
>Dave,do you have any remaining subscriptions/jobs from
the previous
>implementation of replication perhaps? You might try
running
>sp_remopvedbreplication on the databases involved prior
to setting it up
>again. If there are any orphaned records in the table
>MSreplication_subscriptions on the subscriber database
then these can be
>deleted. After that, refresh the jobs folder and then
implement as per
>normal.
>HTH,
>Paul Ibison
>
>.
>
|||Davej,
there seems to be some parts of you script missing. Here is a script for
transactional replication of CustomerDemographics:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'Northwind', @.optname = N'publish',
@.value = N'true'
GO
use [Northwind]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'Northwindcds', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of Northwind database from Publisher
PLL-DT-16.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'PLL-DT-16-Northwind-4'
exec sp_addpublication_snapshot @.publication =
N'Northwindcds',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 222700,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'PLL-DT-16-Northwind-Northwindcds-10'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'PLL-DT-16\xxx
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'domain\vvv'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'Northwindcds', @.article =
N'CustomerDemographics', @.source_owner = N'dbo', @.source_object =
N'CustomerDemographics', @.destination_table = N'CustomerDemographics', @.type
= N'logbased', @.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3, @.status =
16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
sp_MSupd_CustomerDemographics', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'Northwindcds', @.article = N'all',
@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
HTH,
Paul Ibison
|||Paul,
with these stored procs, do you need to specify all the
prameters or can some be defaulted? I'll use your example
as a template...
Thanx
davej
>--Original Message--
>Davej,
>there seems to be some parts of you script missing. Here
is a script for
>transactional replication of CustomerDemographics:
>-- Enabling the replication database
>use master
>GO
>exec sp_replicationdboption @.dbname = N'Northwind',
@.optname = N'publish',
>@.value = N'true'
>GO
>use [Northwind]
>GO
>-- Adding the transactional publication
>exec sp_addpublication @.publication = N'Northwindcds',
@.restricted =
>N'false', @.sync_method = N'native', @.repl_freq =
N'continuous', @.description
>= N'Transactional publication of Northwind database from
Publisher
>PLL-DT-16.', @.status = N'active', @.allow_push = N'true',
@.allow_pull =
>N'true', @.allow_anonymous = N'false',
@.enabled_for_internet = N'false',
>@.independent_agent = N'false', @.immediate_sync =
N'false', @.allow_sync_tran
>= N'false', @.autogen_sync_procs = N'false', @.retention =
336,
>@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder
= N'true',
>@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login
= N'anonymous',
>@.allow_dts = N'false', @.allow_subscription_copy =
N'false',
>@.add_to_active_directory = N'false', @.logreader_job_name =
>N'PLL-DT-16-Northwind-4'
>exec sp_addpublication_snapshot @.publication =
>N'Northwindcds',@.frequency_type = 4, @.frequency_interval
= 1,
>@.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 1,
>@.frequency_subday = 1, @.frequency_subday_interval = 0,
@.active_start_date =
>0, @.active_end_date = 0, @.active_start_time_of_day =
222700,
>@.active_end_time_of_day = 0, @.snapshot_job_name =
>N'PLL-DT-16-Northwind-Northwindcds-10'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'BUILTIN\Administrators'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'distributor_admin'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'PLL-DT-16\xxx
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'domain\vvv'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'sa'
>GO
>-- Adding the transactional articles
>exec sp_addarticle @.publication = N'Northwindcds',
@.article =
>N'CustomerDemographics', @.source_owner = N'dbo',
@.source_object =
>N'CustomerDemographics', @.destination_table =
N'CustomerDemographics', @.type
>= N'logbased', @.creation_script = null, @.description =
null,
>@.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status =
>16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
>sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
>sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
>sp_MSupd_CustomerDemographics', @.filter = null,
@.sync_object = null,
>@.auto_identity_range = N'false'
>GO
>-- Adding the transactional subscription
>exec sp_addsubscription @.publication = N'Northwindcds',
@.article = N'all',
>@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep',
@.sync_type =
>N'automatic', @.update_mode = N'read only', @.offloadagent
= 0,
>@.dts_package_location = N'distributor'
>GO
>
>HTH,
>Paul Ibison
>
>.
>
|||Dave,
the vast majority can be missed out - too many to mention, but details in
BOL (see sp_addpublication, sp_addarticle). You could get EM to generate the
script from another server if you restore your database and create the
publication there, and want to use scripts.
Regards,
Paul Ibison
Had to recreate our SQLServer instance after a rebulid of
the server. Unfortuantely I was not able to
recreate/reload a backup so had to create from scratch.
In trying to setup replication I have the following error
message trying to create a push subscriptions:
SQL Server Enterprise Manager encountered errors creating
push subscriptions for the following Subscribers:
'FHMYDATABSE: Error 14053: Cannot load the DLL
replincrementlsn extended procedure, or one of the DLLs it
references. Reason: 126(The specified module could not be
found.).
The subscription could not be updated at this time.
The subscription could not be created.'
Any ideas? I tried locating the extended proc mention but
no luck. Any help very much appreciated, as I've had this
problem for about 2 weeks now and the powers that bey want
a swift fix! Thanks...
Davej
Dave,
perhaps your service pack failed to fully install. Have a look at sqlsp.log
file from the c:\windows directory this might shed some light on any failed
actions during the sp installation.
HTH,
Paul Ibison
|||Thanks Paul for the note. I re-ran the sp update and it
must of cured something because the first error has gone
but I get the following!
FHMYDATABASE: Error 515: Cannot insert the value NULL
into column 'step_name', table 'msdb.dbo.sysjobsteps';
column does not allow nulls. INSERT fails.
why? any ideas?
Cheers...
>--Original Message--
>Dave,
>perhaps your service pack failed to fully install. Have a
look at sqlsp.log
>file from the c:\windows directory this might shed some
light on any failed
>actions during the sp installation.
>HTH,
>Paul Ibison
>
>.
>
|||Dave,do you have any remaining subscriptions/jobs from the previous
implementation of replication perhaps? You might try running
sp_remopvedbreplication on the databases involved prior to setting it up
again. If there are any orphaned records in the table
MSreplication_subscriptions on the subscriber database then these can be
deleted. After that, refresh the jobs folder and then implement as per
normal.
HTH,
Paul Ibison
|||Hi Paul,
Did what you suggested. Went right back to basics removing
replication from the database. Then went trough the
procedure using stored procs insetadt of EM. Came to
creating a push sub and got the following, bit more
detailed errors:
Server: Msg 515, Level 16, State 2, Procedure
sp_add_jobstep_internal, Line 129
Cannot insert the value NULL into column 'step_name',
table 'msdb.dbo.sysjobsteps'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
Server: Msg 14266, Level 16, State 1, Procedure
sp_verify_jobstep, Line 46
The specified '@.step_id' is invalid (valid values are:
1..1).
Server: Msg 14070, Level 16, State 1, Procedure
sp_changesubstatus, Line 993
Could not update the distribution database subscription
table. The subscription status could not be changed.
Server: Msg 14057, Level 16, State 1, Procedure
sp_addsubscription, Line 971
The subscription could not be created.
the script is:
use qboard
go
/* adds a publication to the database*/
exec sp_addpublication @.publication = 'Q Published Data
(from Script)'
,@.sync_method = 'native'
,@.repl_freq = 'continuous'
,@.status = 'active'
,@.allow_push = 'true'
go
/* adds a subscription to the publication just defined */
exec sp_addsubscription @.publication = 'Q Published Data
(from Script)'
,@.subscriber = 'FHQDB'
,@.subscription_type = 'push'
go
Is there someting I'm missing?
Thanks for any insights...
Davej
>--Original Message--
>Dave,do you have any remaining subscriptions/jobs from
the previous
>implementation of replication perhaps? You might try
running
>sp_remopvedbreplication on the databases involved prior
to setting it up
>again. If there are any orphaned records in the table
>MSreplication_subscriptions on the subscriber database
then these can be
>deleted. After that, refresh the jobs folder and then
implement as per
>normal.
>HTH,
>Paul Ibison
>
>.
>
|||Davej,
there seems to be some parts of you script missing. Here is a script for
transactional replication of CustomerDemographics:
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'Northwind', @.optname = N'publish',
@.value = N'true'
GO
use [Northwind]
GO
-- Adding the transactional publication
exec sp_addpublication @.publication = N'Northwindcds', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= N'Transactional publication of Northwind database from Publisher
PLL-DT-16.', @.status = N'active', @.allow_push = N'true', @.allow_pull =
N'true', @.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name =
N'PLL-DT-16-Northwind-4'
exec sp_addpublication_snapshot @.publication =
N'Northwindcds',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 222700,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'PLL-DT-16-Northwind-Northwindcds-10'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'PLL-DT-16\xxx
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'domain\vvv'
GO
exec sp_grant_publication_access @.publication = N'Northwindcds', @.login =
N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'Northwindcds', @.article =
N'CustomerDemographics', @.source_owner = N'dbo', @.source_object =
N'CustomerDemographics', @.destination_table = N'CustomerDemographics', @.type
= N'logbased', @.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3, @.status =
16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
sp_MSupd_CustomerDemographics', @.filter = null, @.sync_object = null,
@.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription @.publication = N'Northwindcds', @.article = N'all',
@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep', @.sync_type =
N'automatic', @.update_mode = N'read only', @.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
HTH,
Paul Ibison
|||Paul,
with these stored procs, do you need to specify all the
prameters or can some be defaulted? I'll use your example
as a template...
Thanx
davej
>--Original Message--
>Davej,
>there seems to be some parts of you script missing. Here
is a script for
>transactional replication of CustomerDemographics:
>-- Enabling the replication database
>use master
>GO
>exec sp_replicationdboption @.dbname = N'Northwind',
@.optname = N'publish',
>@.value = N'true'
>GO
>use [Northwind]
>GO
>-- Adding the transactional publication
>exec sp_addpublication @.publication = N'Northwindcds',
@.restricted =
>N'false', @.sync_method = N'native', @.repl_freq =
N'continuous', @.description
>= N'Transactional publication of Northwind database from
Publisher
>PLL-DT-16.', @.status = N'active', @.allow_push = N'true',
@.allow_pull =
>N'true', @.allow_anonymous = N'false',
@.enabled_for_internet = N'false',
>@.independent_agent = N'false', @.immediate_sync =
N'false', @.allow_sync_tran
>= N'false', @.autogen_sync_procs = N'false', @.retention =
336,
>@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder
= N'true',
>@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login
= N'anonymous',
>@.allow_dts = N'false', @.allow_subscription_copy =
N'false',
>@.add_to_active_directory = N'false', @.logreader_job_name =
>N'PLL-DT-16-Northwind-4'
>exec sp_addpublication_snapshot @.publication =
>N'Northwindcds',@.frequency_type = 4, @.frequency_interval
= 1,
>@.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 1,
>@.frequency_subday = 1, @.frequency_subday_interval = 0,
@.active_start_date =
>0, @.active_end_date = 0, @.active_start_time_of_day =
222700,
>@.active_end_time_of_day = 0, @.snapshot_job_name =
>N'PLL-DT-16-Northwind-Northwindcds-10'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'BUILTIN\Administrators'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'distributor_admin'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'PLL-DT-16\xxx
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'domain\vvv'
>GO
>exec sp_grant_publication_access @.publication =
N'Northwindcds', @.login =
>N'sa'
>GO
>-- Adding the transactional articles
>exec sp_addarticle @.publication = N'Northwindcds',
@.article =
>N'CustomerDemographics', @.source_owner = N'dbo',
@.source_object =
>N'CustomerDemographics', @.destination_table =
N'CustomerDemographics', @.type
>= N'logbased', @.creation_script = null, @.description =
null,
>@.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status =
>16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
>sp_MSins_CustomerDemographics', @.del_cmd = N'CALL
>sp_MSdel_CustomerDemographics', @.upd_cmd = N'MCALL
>sp_MSupd_CustomerDemographics', @.filter = null,
@.sync_object = null,
>@.auto_identity_range = N'false'
>GO
>-- Adding the transactional subscription
>exec sp_addsubscription @.publication = N'Northwindcds',
@.article = N'all',
>@.subscriber = N'PLL-DT-16', @.destination_db = N'testrep',
@.sync_type =
>N'automatic', @.update_mode = N'read only', @.offloadagent
= 0,
>@.dts_package_location = N'distributor'
>GO
>
>HTH,
>Paul Ibison
>
>.
>
|||Dave,
the vast majority can be missed out - too many to mention, but details in
BOL (see sp_addpublication, sp_addarticle). You could get EM to generate the
script from another server if you restore your database and create the
publication there, and want to use scripts.
Regards,
Paul Ibison
Wednesday, March 7, 2012
Errorlog
I have a large errorlog file that seems to always be in use - can I
delete it or create a new one ?See sp_cycle_errorlog in the BOL
<af.at.work@.gmail.com> wrote in message
news:1154349669.831471.11590@.h48g2000cwc.googlegroups.com...
>I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>|||af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>
Lookup 'sp_cycle_errorlog' in Books Online
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sp_cycle_errorlog did the trick...
af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?|||Hello,
Incase if you are not restarting the SQL Server service or restaring the
server then probably monthly you can schedule a SQL Agent job to run the
command sp_cycle_errorlog to create a new log file. This will help you to
open the log file faster when required.
Thanks
Har
SQL Server MVP
<af.at.work@.gmail.com> wrote in message
news:1154350467.021510.127940@.i3g2000cwc.googlegroups.com...
> sp_cycle_errorlog did the trick...
>
> af.at.work@.gmail.com wrote:
>
delete it or create a new one ?See sp_cycle_errorlog in the BOL
<af.at.work@.gmail.com> wrote in message
news:1154349669.831471.11590@.h48g2000cwc.googlegroups.com...
>I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>|||af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>
Lookup 'sp_cycle_errorlog' in Books Online
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sp_cycle_errorlog did the trick...
af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?|||Hello,
Incase if you are not restarting the SQL Server service or restaring the
server then probably monthly you can schedule a SQL Agent job to run the
command sp_cycle_errorlog to create a new log file. This will help you to
open the log file faster when required.
Thanks
Har
SQL Server MVP
<af.at.work@.gmail.com> wrote in message
news:1154350467.021510.127940@.i3g2000cwc.googlegroups.com...
> sp_cycle_errorlog did the trick...
>
> af.at.work@.gmail.com wrote:
>
Errorlog
I have a large errorlog file that seems to always be in use - can I
delete it or create a new one ?See sp_cycle_errorlog in the BOL
<af.at.work@.gmail.com> wrote in message
news:1154349669.831471.11590@.h48g2000cwc.googlegroups.com...
>I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>|||af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>
Lookup 'sp_cycle_errorlog' in Books Online
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sp_cycle_errorlog did the trick...
af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?|||Hello,
Incase if you are not restarting the SQL Server service or restaring the
server then probably monthly you can schedule a SQL Agent job to run the
command sp_cycle_errorlog to create a new log file. This will help you to
open the log file faster when required.
Thanks
Har
SQL Server MVP
<af.at.work@.gmail.com> wrote in message
news:1154350467.021510.127940@.i3g2000cwc.googlegroups.com...
> sp_cycle_errorlog did the trick...
>
> af.at.work@.gmail.com wrote:
>> I have a large errorlog file that seems to always be in use - can I
>> delete it or create a new one ?
>
delete it or create a new one ?See sp_cycle_errorlog in the BOL
<af.at.work@.gmail.com> wrote in message
news:1154349669.831471.11590@.h48g2000cwc.googlegroups.com...
>I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>|||af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?
>
Lookup 'sp_cycle_errorlog' in Books Online
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||sp_cycle_errorlog did the trick...
af.at.work@.gmail.com wrote:
> I have a large errorlog file that seems to always be in use - can I
> delete it or create a new one ?|||Hello,
Incase if you are not restarting the SQL Server service or restaring the
server then probably monthly you can schedule a SQL Agent job to run the
command sp_cycle_errorlog to create a new log file. This will help you to
open the log file faster when required.
Thanks
Har
SQL Server MVP
<af.at.work@.gmail.com> wrote in message
news:1154350467.021510.127940@.i3g2000cwc.googlegroups.com...
> sp_cycle_errorlog did the trick...
>
> af.at.work@.gmail.com wrote:
>> I have a large errorlog file that seems to always be in use - can I
>> delete it or create a new one ?
>
Sunday, February 26, 2012
Error:Type Expected
I did create one simple program which is used to retrieve data from SQL Server Database into the DataGrid. Unfortunately,compilation stops at the position that 'Dim conn as SQLConnection'.The error is just same as the subject 'Type Expected'.
By the way, the default frameworkSDK from VS.NET version 1.0 lies different place from .NET1.1 I did install recently.If so, I wonder which FrameworkSDK will be used if I'm intending to compile ASP.NET Web Application.
Looking forward to your timely kind reply!At the top of your page with the code, are you importing the System.Data namespace?
Imports System.Data
Josh
Friday, February 24, 2012
Error:1418 during Mirroring
I am trying to do mirroring using SQL Server Ent. 2005 SP1 on two machines. I was able to create end points on both the machines at the port no.s 5091 and 5092 respectively. I have all the security setting settings for both the users. I am getting error no. 1418 while starting mirroring sessions.
Can anybody tell me how to troubleshoot this error?
Can anybody tell me how to troubleshoot this error?
Google Error 1418, there are helpful pointers
e.g. check your SQL is listening on TCP, and the correct port
maybe SQL Browser?
you pointed to the correct ports?
Our mirroring works off the box, because I just used the wizard and used the default ports
|||There are some troubleshooting hints in the paper: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
|||Try editing the C:\WINDOWS\system32\drivers\etc\hosts file on each server to include
IP addressDNSname for each server.
Error: Too many backup devices specified
I am new to SQL Server 2005.
I am trying to restore a database on my computer using SQL Server 2005.
In SQL 2000, I could create the database and then restore a backup over it.
I tried doing this in SQL 2005 and I get the Error:
Too many backup devices specified for backup or restore; only 64 allowed.
Error: 3205.
In the 'Restore Database' window, I select the database and I select, 'From
Device' and point to the backup file to restore. From the 'Specify Backup'
window, I select the backup file, I get the above error.
What am I doing wrong?
ThanksGloria,
It seems that most of the people who run into this are trying to restore a
2005 database to a 2000 server.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
The description of your problem does not sound like you are doing this (or
at least not intentionally) but you might check it out.
Do you backup to multiple files or do you use single file backups? SQL
Server 2000 had a problem restoring multi-file backups, but that was fixed
in 2000 and should be working fine in SQL Server 2005.
RLF
"Gloria" <Gloria@.discussions.microsoft.com> wrote in message
news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
>I am new to SQL Server 2005.
> I am trying to restore a database on my computer using SQL Server 2005.
> In SQL 2000, I could create the database and then restore a backup over
> it.
> I tried doing this in SQL 2005 and I get the Error:
> Too many backup devices specified for backup or restore; only 64 allowed.
> Error: 3205.
> In the 'Restore Database' window, I select the database and I select,
> 'From
> Device' and point to the backup file to restore. From the 'Specify
> Backup'
> window, I select the backup file, I get the above error.
> What am I doing wrong?
> Thanks|||We use single file backups.
The file is from SQL 2005 and I am trying to restore it in SQL 2005 BUT, I
do have SQL 2000 on my computer also and the other databases that I see in
the database list (Object Explorer are SQL 2000 ).
I will check out the links you sent and will continue troubleshooting.
Thanks,
"Russell Fields" wrote:
> Gloria,
> It seems that most of the people who run into this are trying to restore a
> 2005 database to a 2000 server.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
> The description of your problem does not sound like you are doing this (or
> at least not intentionally) but you might check it out.
> Do you backup to multiple files or do you use single file backups? SQL
> Server 2000 had a problem restoring multi-file backups, but that was fixed
> in 2000 and should be working fine in SQL Server 2005.
> RLF
> "Gloria" <Gloria@.discussions.microsoft.com> wrote in message
> news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
> >I am new to SQL Server 2005.
> > I am trying to restore a database on my computer using SQL Server 2005.
> > In SQL 2000, I could create the database and then restore a backup over
> > it.
> >
> > I tried doing this in SQL 2005 and I get the Error:
> > Too many backup devices specified for backup or restore; only 64 allowed.
> > Error: 3205.
> >
> > In the 'Restore Database' window, I select the database and I select,
> > 'From
> > Device' and point to the backup file to restore. From the 'Specify
> > Backup'
> > window, I select the backup file, I get the above error.
> >
> > What am I doing wrong?
> >
> > Thanks
>
>|||I noticed when I opened SQL Server 2005 that my server was listed as a SQL
Server 2000 type. It looks like this:
Server Name (SQL Server 8.0......)
The '8.0' I believe means that it is SQL Server 2000 and not 2005.
When it is 2005, it is SQL Server 9.0.3042
When you start SQL 2005, the dialog box lists the server names. In my case,
my server group is on my machine so I added '\SQLEXPRESS' to the name of my
machine and it identified it as Server 9.0.3042.
"William" wrote:
> Hi, I would like to know if you could find the solution to this problem because it's happening to me too.
> I'm traying to restore a backup from sql 2005 to a database in sql 2005 too. The message is the same that yours.
> I appreciate your answer.
> Thanks!!!
>
I am trying to restore a database on my computer using SQL Server 2005.
In SQL 2000, I could create the database and then restore a backup over it.
I tried doing this in SQL 2005 and I get the Error:
Too many backup devices specified for backup or restore; only 64 allowed.
Error: 3205.
In the 'Restore Database' window, I select the database and I select, 'From
Device' and point to the backup file to restore. From the 'Specify Backup'
window, I select the backup file, I get the above error.
What am I doing wrong?
ThanksGloria,
It seems that most of the people who run into this are trying to restore a
2005 database to a 2000 server.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
The description of your problem does not sound like you are doing this (or
at least not intentionally) but you might check it out.
Do you backup to multiple files or do you use single file backups? SQL
Server 2000 had a problem restoring multi-file backups, but that was fixed
in 2000 and should be working fine in SQL Server 2005.
RLF
"Gloria" <Gloria@.discussions.microsoft.com> wrote in message
news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
>I am new to SQL Server 2005.
> I am trying to restore a database on my computer using SQL Server 2005.
> In SQL 2000, I could create the database and then restore a backup over
> it.
> I tried doing this in SQL 2005 and I get the Error:
> Too many backup devices specified for backup or restore; only 64 allowed.
> Error: 3205.
> In the 'Restore Database' window, I select the database and I select,
> 'From
> Device' and point to the backup file to restore. From the 'Specify
> Backup'
> window, I select the backup file, I get the above error.
> What am I doing wrong?
> Thanks|||We use single file backups.
The file is from SQL 2005 and I am trying to restore it in SQL 2005 BUT, I
do have SQL 2000 on my computer also and the other databases that I see in
the database list (Object Explorer are SQL 2000 ).
I will check out the links you sent and will continue troubleshooting.
Thanks,
"Russell Fields" wrote:
> Gloria,
> It seems that most of the people who run into this are trying to restore a
> 2005 database to a 2000 server.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
> The description of your problem does not sound like you are doing this (or
> at least not intentionally) but you might check it out.
> Do you backup to multiple files or do you use single file backups? SQL
> Server 2000 had a problem restoring multi-file backups, but that was fixed
> in 2000 and should be working fine in SQL Server 2005.
> RLF
> "Gloria" <Gloria@.discussions.microsoft.com> wrote in message
> news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
> >I am new to SQL Server 2005.
> > I am trying to restore a database on my computer using SQL Server 2005.
> > In SQL 2000, I could create the database and then restore a backup over
> > it.
> >
> > I tried doing this in SQL 2005 and I get the Error:
> > Too many backup devices specified for backup or restore; only 64 allowed.
> > Error: 3205.
> >
> > In the 'Restore Database' window, I select the database and I select,
> > 'From
> > Device' and point to the backup file to restore. From the 'Specify
> > Backup'
> > window, I select the backup file, I get the above error.
> >
> > What am I doing wrong?
> >
> > Thanks
>
>|||I noticed when I opened SQL Server 2005 that my server was listed as a SQL
Server 2000 type. It looks like this:
Server Name (SQL Server 8.0......)
The '8.0' I believe means that it is SQL Server 2000 and not 2005.
When it is 2005, it is SQL Server 9.0.3042
When you start SQL 2005, the dialog box lists the server names. In my case,
my server group is on my machine so I added '\SQLEXPRESS' to the name of my
machine and it identified it as Server 9.0.3042.
"William" wrote:
> Hi, I would like to know if you could find the solution to this problem because it's happening to me too.
> I'm traying to restore a backup from sql 2005 to a database in sql 2005 too. The message is the same that yours.
> I appreciate your answer.
> Thanks!!!
>
Error: Too many backup devices specified
I am new to SQL Server 2005.
I am trying to restore a database on my computer using SQL Server 2005.
In SQL 2000, I could create the database and then restore a backup over it.
I tried doing this in SQL 2005 and I get the Error:
Too many backup devices specified for backup or restore; only 64 allowed.
Error: 3205.
In the 'Restore Database' window, I select the database and I select, 'From
Device' and point to the backup file to restore. From the 'Specify Backup'
window, I select the backup file, I get the above error.
What am I doing wrong?
Thanks
Gloria,
It seems that most of the people who run into this are trying to restore a
2005 database to a 2000 server.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
The description of your problem does not sound like you are doing this (or
at least not intentionally) but you might check it out.
Do you backup to multiple files or do you use single file backups? SQL
Server 2000 had a problem restoring multi-file backups, but that was fixed
in 2000 and should be working fine in SQL Server 2005.
RLF
"Gloria" <Gloria@.discussions.microsoft.com> wrote in message
news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
>I am new to SQL Server 2005.
> I am trying to restore a database on my computer using SQL Server 2005.
> In SQL 2000, I could create the database and then restore a backup over
> it.
> I tried doing this in SQL 2005 and I get the Error:
> Too many backup devices specified for backup or restore; only 64 allowed.
> Error: 3205.
> In the 'Restore Database' window, I select the database and I select,
> 'From
> Device' and point to the backup file to restore. From the 'Specify
> Backup'
> window, I select the backup file, I get the above error.
> What am I doing wrong?
> Thanks
|||We use single file backups.
The file is from SQL 2005 and I am trying to restore it in SQL 2005 BUT, I
do have SQL 2000 on my computer also and the other databases that I see in
the database list (Object Explorer are SQL 2000 ).
I will check out the links you sent and will continue troubleshooting.
Thanks,
"Russell Fields" wrote:
> Gloria,
> It seems that most of the people who run into this are trying to restore a
> 2005 database to a 2000 server.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
> The description of your problem does not sound like you are doing this (or
> at least not intentionally) but you might check it out.
> Do you backup to multiple files or do you use single file backups? SQL
> Server 2000 had a problem restoring multi-file backups, but that was fixed
> in 2000 and should be working fine in SQL Server 2005.
> RLF
> "Gloria" <Gloria@.discussions.microsoft.com> wrote in message
> news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
>
>
I am trying to restore a database on my computer using SQL Server 2005.
In SQL 2000, I could create the database and then restore a backup over it.
I tried doing this in SQL 2005 and I get the Error:
Too many backup devices specified for backup or restore; only 64 allowed.
Error: 3205.
In the 'Restore Database' window, I select the database and I select, 'From
Device' and point to the backup file to restore. From the 'Specify Backup'
window, I select the backup file, I get the above error.
What am I doing wrong?
Thanks
Gloria,
It seems that most of the people who run into this are trying to restore a
2005 database to a 2000 server.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
The description of your problem does not sound like you are doing this (or
at least not intentionally) but you might check it out.
Do you backup to multiple files or do you use single file backups? SQL
Server 2000 had a problem restoring multi-file backups, but that was fixed
in 2000 and should be working fine in SQL Server 2005.
RLF
"Gloria" <Gloria@.discussions.microsoft.com> wrote in message
news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
>I am new to SQL Server 2005.
> I am trying to restore a database on my computer using SQL Server 2005.
> In SQL 2000, I could create the database and then restore a backup over
> it.
> I tried doing this in SQL 2005 and I get the Error:
> Too many backup devices specified for backup or restore; only 64 allowed.
> Error: 3205.
> In the 'Restore Database' window, I select the database and I select,
> 'From
> Device' and point to the backup file to restore. From the 'Specify
> Backup'
> window, I select the backup file, I get the above error.
> What am I doing wrong?
> Thanks
|||We use single file backups.
The file is from SQL 2005 and I am trying to restore it in SQL 2005 BUT, I
do have SQL 2000 on my computer also and the other databases that I see in
the database list (Object Explorer are SQL 2000 ).
I will check out the links you sent and will continue troubleshooting.
Thanks,
"Russell Fields" wrote:
> Gloria,
> It seems that most of the people who run into this are trying to restore a
> 2005 database to a 2000 server.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2423812&SiteID=1
> The description of your problem does not sound like you are doing this (or
> at least not intentionally) but you might check it out.
> Do you backup to multiple files or do you use single file backups? SQL
> Server 2000 had a problem restoring multi-file backups, but that was fixed
> in 2000 and should be working fine in SQL Server 2005.
> RLF
> "Gloria" <Gloria@.discussions.microsoft.com> wrote in message
> news:E51D26A7-88C0-4580-9722-40D84E17B6E9@.microsoft.com...
>
>
Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipelin
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"
-Jamie
|||
In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.
|||And the registry...?
-Jamie
|||
I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:
\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read
\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read
|||run regedit
press CTRL-F
search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID
-Jamie
|||
Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.
Please get back to me a.s.a.p.
David
|||Hi all, I have the same problem and no sulution for it......hmmm|||I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.
|||Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.
|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipelin
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
Not exactly sure why this is happening but as a first stab can you do a search on your machine and in your registry for "DTSPipeline.DLL". it should be in "%PROGRAMFILES%\Microsoft SQL Server\90\DTS\Binn\DTSPipeline.dll"
-Jamie
|||In the properties of the file DTSPipeline.dll for the Administrator account that I am using I have all the permissions checked, except for Special Permissions. For the SQLServer2005DTSUser$MyComputerName the permissions checked are 1.Read & Execute and 2.Read.
|||And the registry...?
-Jamie
|||I am not completely sure of where I am supposed to look for in the registry. Possible values you were asking me about:
\HKEY_CLASSES_ROOT\DTS.Pipeline -> Permissions: Administrator- 1.Full Control 2.Read
\HKEY_CLASSES_ROOT\DTS.Pipeline.1 -> Permissions: Administrator- 1.Full Control 2.Read
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS -> Permissions: Administrator- 1.Full Control 2.Read SQLServer2005UserType - 1.Read
|||run regedit
press CTRL-F
search for DTSPipeline.dll. You should have an entry in HKEY_CLASSES_ROOT\CLSID
-Jamie
|||Jaime, can you please let me know if the advise you were given was useful, or if you have solved the problems yourself - I'm experiencing exactly the same issues and I've checked all items that have been suggested and everything appears to be as it should be, but I'm still getting the error messages.
Please get back to me a.s.a.p.
David
|||Hi all, I have the same problem and no sulution for it......hmmm|||I too have seen this problem. I was using a trial version of Visual Studio with a trial version of SQL Server Enterprise Edition. When I converted to Visual Studio Professional Edition and SQL Server Developer Edition, I found this problem. However, the problem disappeared after I installed the SQL Server 2005 SP1. Try it. It might work for you too.
|||Sorry for not answering before. At that time I wasn't able to figure out what was the source of the problem, so I had to reinstall the OS again.
|||I had this error until I installed Sql Server Sp1.|||I am using the trial version of SSIS and faced the same problem. Just installed SP1 for SQL Server and the problem dissappeared.
Subscribe to:
Posts (Atom)