equal distribution of start and finish dates

Hi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls helpQuestion. What exactly is your expected result? It's not entirely clear
from your description. I think you're saying that for December 2000 the
cost should be $2,000. For the other, it soundes like you are looking to
pro-rate the $400? If so, across what time period do you want it pro-rated
exactly?
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita
USE My_Test1
CREATE TABLE dbo.Test
(
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
WITH mytest (start_date,finish_date,cost,Diff_Days)
AS
(
SELECT start_date,finish_date,cost,
DATEDIFF(month, start_date,finish_date) AS Diff_Days
FROM dbo.Test
)
SELECT CASE WHEN Diff_Days =0 THEN cost ELSE cost*Diff_Days END
FROM mytest
"mita" <mita@.discussions.microsoft.com> wrote in message
news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help|||mita wrote:
> Hi all
> I have got 3 columns in my table- start date,finish date and cost..in the
> following format...
> start_date finish_date cost
> 12/12/2000 20/12/2000 $2000
> 01/09/2000 12/10/2000 $400
> Now if the month and year of the start and finish date is same, the cost
> remains same...
> but if the month of the two dates are different, i have to distribute the
> cost between the two months by calculating the cost for the number of days
> for both of the months..
> but i am not able to figure out how?
> i am using sql 2005 ..
> my table has got about 1 million rows...
> pls help
This will handle a two-month period, anything more than that is going to
be more complicated:
USE tempdb
GO
CREATE TABLE dbo.Test (
start_date DATETIME NOT NULL,
finish_date DATETIME NOT NULL,
cost DECIMAL(18,3)
)
INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
GO
CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
END
GO
SELECT
start_date,
finish_date,
cost,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4) END AS portion1,
CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
4)) END AS portion2
FROM dbo.Test|||HI Mike u r right..i need to prorate $400 according to the no. of days...for
ex cost for 30 days of september and 12 days for october
"Mike C#" wrote:
> Question. What exactly is your expected result? It's not entirely clear
> from your description. I think you're saying that for December 2000 the
> cost should be $2,000. For the other, it soundes like you are looking to
> pro-rate the $400? If so, across what time period do you want it pro-rated
> exactly?
> "mita" <mita@.discussions.microsoft.com> wrote in message
> news:D24E0F04-CD11-4C6E-AB9D-6B23384F5964@.microsoft.com...
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
>
>|||On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
>Hi all
>I have got 3 columns in my table- start date,finish date and cost..in the
>following format...
>start_date finish_date cost
>12/12/2000 20/12/2000 $2000
>01/09/2000 12/10/2000 $400
>Now if the month and year of the start and finish date is same, the cost
>remains same...
>but if the month of the two dates are different, i have to distribute the
>cost between the two months by calculating the cost for the number of days
>for both of the months..
>but i am not able to figure out how?
>i am using sql 2005 ..
>my table has got about 1 million rows...
>pls help
Hi mita,
First, you need to create a table that holds all months in your
reporting period (or more). Something like this:
CREATE TABLE dbo.Months
(MonthStart datetime NOT NULL PRIMARY KEY,
MonthEnd datetime NOT NULL);
go
DECLARE @.TheMonth datetime;
SET @.TheMonth = '200000101'; -- Start at january 2000
WHILE @.TheMonth <= '20191231' -- End at december 2019
BEGIN;
INSERT INTO dbo.Months (MonthStart, MonthEnd)
VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
END;
The above is a one time operation, provided you never drop the table.
Don't forget to add some extra months to the table in a year or ten!
With this table in place, your query becomes something like this:
SELECT PeriodStart, PeriodEnd,
TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
/ DATEDIFF (day, start_date, finish_date) AS cost
FROM (SELECT CASE WHEN a.start_date > b.MonthStart
THEN a.start_date
ELSE b.MonthStart
END AS PeriodStart,
CASE WHEN a.finish_date < b.MonthEnd
THEN a.finish_date
ELSE b.MonthEnd
END AS PeriodEnd,
a.start_date, a.finish_date,
a.cost AS TotalCost
FROM dbo.MyTable AS a
INNER JOIN dbo.Months AS b
ON b.MonthStart <= a.finish_date
AND b.MonthStart >= a.start_date) AS d
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP|||hi hugo
i just ran this query for creating the months table which u suggested... i
am getting an error....
"Conversion failed when converting datetime from character string." what do
i do?
"Tracy McKibben" wrote:
> mita wrote:
> > Hi all
> > I have got 3 columns in my table- start date,finish date and cost..in the
> > following format...
> >
> > start_date finish_date cost
> > 12/12/2000 20/12/2000 $2000
> > 01/09/2000 12/10/2000 $400
> >
> > Now if the month and year of the start and finish date is same, the cost
> > remains same...
> > but if the month of the two dates are different, i have to distribute the
> > cost between the two months by calculating the cost for the number of days
> > for both of the months..
> > but i am not able to figure out how?
> > i am using sql 2005 ..
> > my table has got about 1 million rows...
> > pls help
> This will handle a two-month period, anything more than that is going to
> be more complicated:
> USE tempdb
> GO
> CREATE TABLE dbo.Test (
> start_date DATETIME NOT NULL,
> finish_date DATETIME NOT NULL,
> cost DECIMAL(18,3)
> )
> INSERT INTO dbo.Test VALUES ('20001212','20001220',2000)
> INSERT INTO dbo.Test VALUES ('20000901','20001012',400)
> INSERT INTO dbo.Test VALUES ('20000901','20001212',300)
> GO
> CREATE FUNCTION dbo.DaysInMonth(@.Date DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN (DATEPART(day, CONVERT(DATETIME, RTRIM(CONVERT(CHAR(2),
> DATEPART(month, @.Date) + CASE WHEN DATEPART(month, @.Date) = 12 THEN -11
> ELSE 1 END)) + '/1/' + CONVERT(CHAR(4), DATEPART(year, @.Date))) - 1))
> END
> GO
> SELECT
> start_date,
> finish_date,
> cost,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN cost ELSE cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4) END AS portion1,
> CASE WHEN DATEPART(month, start_date) = DATEPART(month, finish_date)
> THEN 0 ELSE cost - (cost * (dbo.DaysInMonth(start_date) - DATEPART(day,
> start_date)) / CONVERT(NUMERIC, DATEDIFF(day, start_date, finish_date),
> 4)) END AS portion2
> FROM dbo.Test
>|||GO
CREATE TABLE [dbo].[DSS](
[Service Start] [datetime] NULL,
[Service End] [datetime] NULL,
[FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[No of Units] [float] NULL,
) ON [PRIMARY]
"Hugo Kornelis" wrote:
> On Sat, 17 Jun 2006 17:35:02 -0700, mita wrote:
> >Hi all
> >I have got 3 columns in my table- start date,finish date and cost..in the
> >following format...
> >
> >start_date finish_date cost
> >12/12/2000 20/12/2000 $2000
> >01/09/2000 12/10/2000 $400
> >
> >Now if the month and year of the start and finish date is same, the cost
> >remains same...
> >but if the month of the two dates are different, i have to distribute the
> >cost between the two months by calculating the cost for the number of days
> >for both of the months..
> >but i am not able to figure out how?
> >i am using sql 2005 ..
> >my table has got about 1 million rows...
> >pls help
> Hi mita,
> First, you need to create a table that holds all months in your
> reporting period (or more). Something like this:
> CREATE TABLE dbo.Months
> (MonthStart datetime NOT NULL PRIMARY KEY,
> MonthEnd datetime NOT NULL);
> go
> DECLARE @.TheMonth datetime;
> SET @.TheMonth = '200000101'; -- Start at january 2000
> WHILE @.TheMonth <= '20191231' -- End at december 2019
> BEGIN;
> INSERT INTO dbo.Months (MonthStart, MonthEnd)
> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
> END;
> The above is a one time operation, provided you never drop the table.
> Don't forget to add some extra months to the table in a year or ten!
> With this table in place, your query becomes something like this:
> SELECT PeriodStart, PeriodEnd,
> TotalCost * DATEDIFF (day, PeriodStart, PeriodEnd)
> / DATEDIFF (day, start_date, finish_date) AS cost
> FROM (SELECT CASE WHEN a.start_date > b.MonthStart
> THEN a.start_date
> ELSE b.MonthStart
> END AS PeriodStart,
> CASE WHEN a.finish_date < b.MonthEnd
> THEN a.finish_date
> ELSE b.MonthEnd
> END AS PeriodEnd,
> a.start_date, a.finish_date,
> a.cost AS TotalCost
> FROM dbo.MyTable AS a
> INNER JOIN dbo.Months AS b
> ON b.MonthStart <= a.finish_date
> AND b.MonthStart >= a.start_date) AS d
> (Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 18 Jun 2006 16:32:02 -0700, mita wrote:
>"Hugo Kornelis" wrote:
(snip)
>> First, you need to create a table that holds all months in your
>> reporting period (or more). Something like this:
>> CREATE TABLE dbo.Months
>> (MonthStart datetime NOT NULL PRIMARY KEY,
>> MonthEnd datetime NOT NULL);
>> go
>> DECLARE @.TheMonth datetime;
>> SET @.TheMonth = '200000101'; -- Start at january 2000
>> WHILE @.TheMonth <= '20191231' -- End at december 2019
>> BEGIN;
>> INSERT INTO dbo.Months (MonthStart, MonthEnd)
>> VALUES (@.TheMonth, DATEADD(day, -1, DATEADD(month, 1, @.TheMonth)));
>> SET @.TheMonth = DATEADD(month, 1, @.TheMonth);
>> END;
>hi hugo
>i just ran this query for creating the months table which u suggested... i
>am getting an error....
>"Conversion failed when converting datetime from character string." what do
>i do?
Hi Mita,
Sorry for the delayed reply.
The error is caused by a stupid typo in my code (quoted above) - I have
one zero to many in the date constant for Jan 1st, 2000. If you change
'200000101' to '20000101', the error should go away.
--
Hugo Kornelis, SQL Server MVP

Enumerating Xml elements and inserting

I have an untyped XML variable that for example holds the following data:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Using SQL DML and\or XQuery I would like to enumerate the XML elements and add an <ExternalId> element to any <Address> element if one doesn't exist with a value of NewId().

So the end result would be:

<Customer>

<FirstName>John</FirstName>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12334343A</ExternalId>

</Address>

<Office>

<Telephone>0208123456789</Telephone>

<Address>

<AddressLine1>1 The road</Addressline1>

<AddressLine2>Pinner</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-333333</ExternalId>

</Address>

</Office>

</Customer>

<Customer>

<FirstName>Adam</FirstName>

<Address>

<AddressLine1>19 Another road</Addressline1>

<AddressLine2>Hemel</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>QW34-122132WE-12312312</ExternalId>

</Address>

<Office>

<Telephone>0208123456222</Telephone>

<Address>

<AddressLine1>5 The road</Addressline1>

<AddressLine2>Hatfield</Addressline2>

<AddressLine3>London</Addressline3>

<ExternalId>2</ExternalId>

</Address>

</Office>

</Customer>

Any help would be greatly appreciated.

Regards

Why are you using a unique identifier. Are you familiar with the performance hit that comes with using a unique identifier? I admit there are reasons to want to use a unique identifier and yours may be one of them, but frequently I see unique identifiers used without an understanding of the consequences to performance. Here are some previous threads related to this issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430995&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1544519&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304764&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1493312&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525445&SiteID=1

|||

Thanks for the reply.

Perfomance is not an issue in this instance as it will be run once in a blue moon. But thanks for the heads-up on the performance issues. It doesn't have to be NewID() it can be any globally unique number.

Regards

sql

Enumerating Stored Procedure dependencies using SQL-DMO

I have the following (VB.NET) code:
For some reason, I can't get this code to return anything but a ResultSet
with 0 rows. Any ideas?
Public Function GetDependencies(ByVal db As SQLDMO.Database2) As String
Dim objSP As SQLDMO.StoredProcedure2 = db.StoredProcedures.Item(Me.Text)
Dim objQueryResults As SQLDMO.QueryResults = _
objSP.EnumDependencies(SQLDMO.SQLDMO_DEPENDENCY_TYPE.SQLDMODep_Valid)
Dim sb As New System.Text.StringBuilder(4096)
Dim writer As New System.IO.StringWriter(sb)
For i As Integer = 1 To objQueryResults.ResultSets
objQueryResults.CurrentResultSet = i
For j As Integer = 1 To objQueryResults.Rows
For k As Integer = 1 To objQueryResults.Columns
writer.Write(objQueryResults.ColumnName(k) & ": ")
writer.WriteLine(objQueryResults.GetColumnString(j, k))
Next
Next
Next
writer.Flush()
writer.Close()
Return sb.ToString()
End Function
Developer ExtraordinaireHi
Don't forget, in SQL 7.0 and 2000, dependency information is not guaranteed
to be correct due the Deferred Name resolution.
Have you looked in sysdepends if there is information there.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<developerExtraordinaire@.spamMeAndDie.com> wrote in message
news:ewd2zkeGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I have the following (VB.NET) code:
> For some reason, I can't get this code to return anything but a ResultSet
> with 0 rows. Any ideas?
> Public Function GetDependencies(ByVal db As SQLDMO.Database2) As String
> Dim objSP As SQLDMO.StoredProcedure2 =
db.StoredProcedures.Item(Me.Text)
> Dim objQueryResults As SQLDMO.QueryResults = _
> objSP.EnumDependencies(SQLDMO.SQLDMO_DEPENDENCY_TYPE.SQLDMODep_Valid)
> Dim sb As New System.Text.StringBuilder(4096)
> Dim writer As New System.IO.StringWriter(sb)
> For i As Integer = 1 To objQueryResults.ResultSets
> objQueryResults.CurrentResultSet = i
> For j As Integer = 1 To objQueryResults.Rows
> For k As Integer = 1 To objQueryResults.Columns
> writer.Write(objQueryResults.ColumnName(k) & ": ")
> writer.WriteLine(objQueryResults.GetColumnString(j, k))
> Next
> Next
> Next
> writer.Flush()
> writer.Close()
> Return sb.ToString()
> End Function
>
> Developer Extraordinaire
>

entry number thousand from 3 Tables with primary key number

Hi!
I have 3 tables with a primary key (number integer 4 ) and I have a datetime
field date_ (datetime 8) with the following value: 16.01.2003 00:00:04.
The primary key is not starting with one and some numbers are also missing.
Now I want to get the entry number thousand in the order after the date_
field with all rows from this 3 tables.
How must my select statement look like?
ThanksCan you further elaborate the following sentence:
Now I want to get the entry number thousand in the order after the date_
field with all rows from this 3 tables.
"Hubert Mayr" <huma1@.gmx.net> wrote in message
news:1068474664.222762@.news.liwest.at...
> Hi!
> I have 3 tables with a primary key (number integer 4 ) and I have a
datetime
> field date_ (datetime 8) with the following value: 16.01.2003 00:00:04.
> The primary key is not starting with one and some numbers are also
missing.
> Now I want to get the entry number thousand in the order after the date_
> field with all rows from this 3 tables.
> How must my select statement look like?
> Thanks
>

Entreprise Manager behaviour

Dear all,
I've got the following scenario:
-Got two EM instances running at the same time. The same view on the right
for both, this is, Tables.
From EM1 I create a table called T. After that I'm going to EM2 and refresh.
Fine because I am seeing the same object.
-Come back to EM1 and drop T object and create again another T object but
different, with more fields. When this time I'm going to EM2 and I do drop
action I've dropped the most new object not the old one when I was waiting a
messagebox or something like that warning me that I am deleting an object
which is different than I though.
Jezz, how can I do for to change this behaviour?
Any help or comment woud be very appreciated.
Current location: Alicante (ES)The behaviour you describe is exactly os should be expected.
When you drop a table in EM, EM actually executes a T-SQL drop table
statement.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:633618A6-5345-4F82-B27F-4C362546AA93@.microsoft.com...
> Dear all,
> I've got the following scenario:
> -Got two EM instances running at the same time. The same view on the right
> for both, this is, Tables.
> From EM1 I create a table called T. After that I'm going to EM2 and
> refresh.
> Fine because I am seeing the same object.
> -Come back to EM1 and drop T object and create again another T object but
> different, with more fields. When this time I'm going to EM2 and I do drop
> action I've dropped the most new object not the old one when I was waiting
> a
> messagebox or something like that warning me that I am deleting an object
> which is different than I though.
> Jezz, how can I do for to change this behaviour?
> Any help or comment woud be very appreciated.
> --
> Current location: Alicante (ES)|||Hi Stephany,
Yes, I perfectly know it, but I was wondering myself how to change
thanks anyway
--
Current location: Alicante (ES)
"Stephany Young" wrote:

> The behaviour you describe is exactly os should be expected.
> When you drop a table in EM, EM actually executes a T-SQL drop table
> statement.
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:633618A6-5345-4F82-B27F-4C362546AA93@.microsoft.com...
>
>|||> Jezz, how can I do for to change this behaviour?
Stop using EM at all :-))
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:633618A6-5345-4F82-B27F-4C362546AA93@.microsoft.com...
> Dear all,
> I've got the following scenario:
> -Got two EM instances running at the same time. The same view on the right
> for both, this is, Tables.
> From EM1 I create a table called T. After that I'm going to EM2 and
> refresh.
> Fine because I am seeing the same object.
> -Come back to EM1 and drop T object and create again another T object but
> different, with more fields. When this time I'm going to EM2 and I do drop
> action I've dropped the most new object not the old one when I was waiting
> a
> messagebox or something like that warning me that I am deleting an object
> which is different than I though.
> Jezz, how can I do for to change this behaviour?
> Any help or comment woud be very appreciated.
> --
> Current location: Alicante (ES)|||What would you like to change? Do you want to drop objects without actually
dropping them?
What are you trying to achieve?
ML
http://milambda.blogspot.com/|||In fact, I hope that in early period of time EM will be living and sharing
time with Sql Management Studio
--
Current location: Alicante (ES)
"Uri Dimant" wrote:

> Stop using EM at all :-))
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:633618A6-5345-4F82-B27F-4C362546AA93@.microsoft.com...
>
>|||No, I hope that EM works fine. Did it make sense such scenario? I think so.
--
Current location: Alicante (ES)
"ML" wrote:

> What would you like to change? Do you want to drop objects without actuall
y
> dropping them?
> What are you trying to achieve?
> ML
> --
> http://milambda.blogspot.com/|||Great, trade in your Yugo for a Kia. Management Studio is certainly a
better tool than Enterprise Manager in general, but it still shares a lot of
its flaws and limitations.
Hopefully, at least, you'll learn commands like DROP TABLE and BEGIN TRAN /
COMMIT TRAN, instead of pointing and clicking yourself to inevitable
disaster.

> In fact, I hope that in early period of time EM will be living and sharing
> time with Sql Management Studio|||drop and commit? I unfortunately know them
--
Current location: Alicante (ES)
"Aaron Bertrand [SQL Server MVP]" wrote:

> Great, trade in your Yugo for a Kia. Management Studio is certainly a
> better tool than Enterprise Manager in general, but it still shares a lot
of
> its flaws and limitations.
> Hopefully, at least, you'll learn commands like DROP TABLE and BEGIN TRAN
/
> COMMIT TRAN, instead of pointing and clicking yourself to inevitable
> disaster.
>
>
>
>
>
>|||> drop and commit? I unfortunately know them
Well, I should have said, learn to use them. I would much rather write DROP
TABLE and/or wrap my commands in transactions than blindly trust what some
GUI is going to do. Especially when I'm doing some backhanded thing like
just trying to see what will happen when I bounce quickly between two open
apps on the same machine...
A

Entity Deletion Strategy

Hi,
I'm wondering what the standard practise is for dealing with the
following very common scenario:
You have users who can use your application and they are identified by
email address. Sometimes you want to delete one of these users. You
want to keep some reference to them in the DB for auditing purposes.
Also, you want to be able to free up that email address so it can be
used again.
It seems to me there are two options:
Keep the user in the User table but set its "status" to "deleted". The
problem thought is that now many of the queries against the user table
will have to check status.
Delete the user from the User table, but have it stored in some other
table, a UserAudit table for example.
Is there a standard way of doing this? If so, how is it done? Thanks.Hi
As long as the email address does not have a unique index or is the primary
key you can use the status flag. Depending on how many deleted users you have
(or if you see a significant degredation of performance) then you may or may
not want to partition the table (or create a partitioned view). To remove the
need to add the status check to every where clause you can create an active
users view (and keep table name) of the table. To implement a partioned view
would be the flip side of this (create an new table and transfer the table
name to the partitoned view), once you are using the active users table/view
there would be no T-SQL code change involved with changing over to the other
model.
John
"nickgieschen@.gmail.com" wrote:
> Hi,
> I'm wondering what the standard practise is for dealing with the
> following very common scenario:
> You have users who can use your application and they are identified by
> email address. Sometimes you want to delete one of these users. You
> want to keep some reference to them in the DB for auditing purposes.
> Also, you want to be able to free up that email address so it can be
> used again.
> It seems to me there are two options:
> Keep the user in the User table but set its "status" to "deleted". The
> problem thought is that now many of the queries against the user table
> will have to check status.
> Delete the user from the User table, but have it stored in some other
> table, a UserAudit table for example.
> Is there a standard way of doing this? If so, how is it done? Thanks.
>|||John Bell wrote:
> Hi
> As long as the email address does not have a unique index or is the primary
> key you can use the status flag. Depending on how many deleted users you have
> (or if you see a significant degredation of performance) then you may or may
> not want to partition the table (or create a partitioned view). To remove the
> need to add the status check to every where clause you can create an active
> users view (and keep table name) of the table. To implement a partioned view
> would be the flip side of this (create an new table and transfer the table
> name to the partitoned view), once you are using the active users table/view
> there would be no T-SQL code change involved with changing over to the other
> model.
> John
> "nickgieschen@.gmail.com" wrote:
> > Hi,
> >
> > I'm wondering what the standard practise is for dealing with the
> > following very common scenario:
> >
> > You have users who can use your application and they are identified by
> > email address. Sometimes you want to delete one of these users. You
> > want to keep some reference to them in the DB for auditing purposes.
> > Also, you want to be able to free up that email address so it can be
> > used again.
> >
> > It seems to me there are two options:
> >
> > Keep the user in the User table but set its "status" to "deleted". The
> > problem thought is that now many of the queries against the user table
> > will have to check status.
> >
> > Delete the user from the User table, but have it stored in some other
> > table, a UserAudit table for example.
> >
> > Is there a standard way of doing this? If so, how is it done? Thanks.
> >
> >
You can create a trigger and when you will delete rows deleted rows
will be inserted to history table. So you can create a primary key or
unique key on email address.
Regards
Amish Shah
http://shahamishm.tripod.com

Enterprise SQL Projects (1000+ Stored Procedures)

Enterprise SQL Projects
--------
When Design is replaced with an Architectural Plan

The following post is intended as a starting point of some main concepts to consider when dealing with ent. sql projects. While it is not a direct question of any kind, it would interest people that are/or was involved in ent. projects and therefore have been troubled with similar problems.

Here is a quick overview of a couple main concepts when you have to deal with a Ent. Projects with 1000+ stored procedures.

DOCUMENTATION:
It is an absolute must to include 100% explanatory code on top of the sps.

FUNCTIONS:
Use functions to the maximum extent to reduce overal stored procedure complexity
a rule of thumb is to have 1 to 10, functions to sps ratio or simmilar.

TRIGGERS:
A lot to say about them that cannot be covered in this context

NAMING CONVENSION:
Your naming convension should be 100% pre-thought and designed, no mistakes allowed in this context as it will cause all stored procedures to be extremely difficult/impossible to browse.

a quick template could look as this:

sp
module name
underscore(_)
action (lower case)
noun (proper case)

For example:
spOrders_putOrderDetail
spMaintainUsers_deactivateUser
spReports_getZeroInventory

(quoted by: tmorton)

my addition to this would be something like:
sp< as a prefix is surtently an overkill when dealing with 1000+ sps and is not needed.

however a lot more complex naming strategies can be used, that will cause the project to be a lot more easy to maintain.Just a note on your last comment - I would also like to submit that I have thought for several years now that prefacing stored procedure names with "sp" and / or table names with "tbl" is completely unnecessary. Back in "the day" this might have been necessary but I don't think I've seen a compelling enough reason in years to continue this practice.

Of course, this is just my opinion.|||Russem:

Personally, it's all about readability and how the programmer feels which coding paradigm is easier for them to read. I personally love the Hungarian naming convention. For example, once you get into huge projects, i.e. > 1M lines of code, it gets more difficult to read the code, so by utilizing these prefixes, it sure helps the eyes (and brain)!|||::Just a note on your last comment - I would also like to submit that I have thought for
::several years now that prefacing stored procedure names with "sp" and / or table names
::with "tbl" is completely unnecessary. Back in "the day" this might have been necessary but
::I don't think I've seen a compelling enough reason in years to continue this practice.

Naturally, though, this "compelling reason" some people seem to see has NOT included going to the documentation.

There you would find out that the official documentation says that whatever you name a stored procedure, you do NOT start it with "sp_".

Because contrary to what people that have not read the documentation do think, this means "System Procedure" (not Stored Procedure).

And it DOES make a difference. Let me quote:

::It is strongly recommended that you do not create any stored procedures using sp_ as a
::prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:
::
::The stored procedure in the master database.
::
::The stored procedure based on any qualifiers provided (database name or owner).
::
::The stored procedure using dbo as the owner, if one is not specified.
::
::Therefore, although the user-created stored procedure prefixed with sp_ may exist in the
::current database, the master database is always checked first, even if the stored
::procedure is qualified with the database name.
::
::Important If any user-created stored procedure has the same name as a system stored
::procedure, the user-created stored procedure will never be executed.

Prefx if you want, but people following this should have the dignitiy to read the documentation.

Funnily, a lot of "sql gurus" in companies just prefix all "stored procedures" with "sp_" as this is "how ms does it, too".|||I'll prefix variables, sure. But I won't do it with stored procedures (and a standard prefix for all of them) and especially not with tables. I definitely agree with adding a prefix to variable names, though :)

Enterprise Mgr Error

Good Day
SQL 2000 - just started recieving the following when loading Enterprise
Manager directly on the server console (from a workstation is fine)
'Connection to application failed. Ensure that no program modules have
been deleted'
I tried re-installing both OS & SQL service packs to see if a DLL or
something was missing - but am still receiving the error - any ideas ?
Thanks!
Hi
Can you open MMC and other add-ins without getting this error?
Can you open SQL Server Enterprise Manager.msc from within MMC?
John
"ERoss" wrote:

> Good Day
> SQL 2000 - just started recieving the following when loading Enterprise
> Manager directly on the server console (from a workstation is fine)
> 'Connection to application failed. Ensure that no program modules have
> been deleted'
> I tried re-installing both OS & SQL service packs to see if a DLL or
> something was missing - but am still receiving the error - any ideas ?
> Thanks!
>
|||In article <818FA874-C913-4C1C-93FB-3B62B00E0770@.microsoft.com>,
jbellnewsposts@.hotmail.com says...[vbcol=seagreen]
> Hi
> Can you open MMC and other add-ins without getting this error?
> Can you open SQL Server Enterprise Manager.msc from within MMC?
> John
> "ERoss" wrote:
Good Point & thanks -
MMC.exe runs OK - I added various snapins - all worked successfully -
tried to add enterprise manager.msc - it blows withthe same error
Thanks
|||Hi
You don't say what service pack you are on!
But you could try renaming the MMC and loading the renamed file (which seems
to sometimes work!), renaming the MMC file and installing a service pack, or
copy the MMC from another machine that is on the same fix level.
John
"ERoss" wrote:

> In article <818FA874-C913-4C1C-93FB-3B62B00E0770@.microsoft.com>,
> jbellnewsposts@.hotmail.com says...
> Good Point & thanks -
> MMC.exe runs OK - I added various snapins - all worked successfully -
> tried to add enterprise manager.msc - it blows withthe same error
> Thanks
>
|||In article <CD313813-AB93-40D6-90A4-8BF3DB4682F5@.microsoft.com>,
jbellnewsposts@.hotmail.com says...
SQL is SP4 -
I had re-applied that service pack -
But I will retry after renaming the enterprise manager.msc file
Thanks!

> Hi
> You don't say what service pack you are on!
> But you could try renaming the MMC and loading the renamed file (which seems
> to sometimes work!), renaming the MMC file and installing a service pack, or
> copy the MMC from another machine that is on the same fix level.
> John
>

Enterprise Mgr Error

Good Day
SQL 2000 - just started recieving the following when loading Enterprise
Manager directly on the server console (from a workstation is fine)
'Connection to application failed. Ensure that no program modules have
been deleted'
I tried re-installing both OS & SQL service packs to see if a DLL or
something was missing - but am still receiving the error - any ideas ?
Thanks!Hi
Can you open MMC and other add-ins without getting this error?
Can you open SQL Server Enterprise Manager.msc from within MMC?
John
"ERoss" wrote:
> Good Day
> SQL 2000 - just started recieving the following when loading Enterprise
> Manager directly on the server console (from a workstation is fine)
> 'Connection to application failed. Ensure that no program modules have
> been deleted'
> I tried re-installing both OS & SQL service packs to see if a DLL or
> something was missing - but am still receiving the error - any ideas ?
> Thanks!
>|||In article <818FA874-C913-4C1C-93FB-3B62B00E0770@.microsoft.com>,
jbellnewsposts@.hotmail.com says...
> Hi
> Can you open MMC and other add-ins without getting this error?
> Can you open SQL Server Enterprise Manager.msc from within MMC?
> John
> "ERoss" wrote:
> > Good Day
> >
> > SQL 2000 - just started recieving the following when loading Enterprise
> > Manager directly on the server console (from a workstation is fine)
> >
> > 'Connection to application failed. Ensure that no program modules have
> > been deleted'
> >
> > I tried re-installing both OS & SQL service packs to see if a DLL or
> > something was missing - but am still receiving the error - any ideas ?
> >
> > Thanks!
> >
Good Point & thanks -
MMC.exe runs OK - I added various snapins - all worked successfully -
tried to add enterprise manager.msc - it blows withthe same error
Thanks|||Hi
You don't say what service pack you are on!
But you could try renaming the MMC and loading the renamed file (which seems
to sometimes work!), renaming the MMC file and installing a service pack, or
copy the MMC from another machine that is on the same fix level.
John
"ERoss" wrote:
> In article <818FA874-C913-4C1C-93FB-3B62B00E0770@.microsoft.com>,
> jbellnewsposts@.hotmail.com says...
> > Hi
> >
> > Can you open MMC and other add-ins without getting this error?
> >
> > Can you open SQL Server Enterprise Manager.msc from within MMC?
> >
> > John
> >
> > "ERoss" wrote:
> >
> > > Good Day
> > >
> > > SQL 2000 - just started recieving the following when loading Enterprise
> > > Manager directly on the server console (from a workstation is fine)
> > >
> > > 'Connection to application failed. Ensure that no program modules have
> > > been deleted'
> > >
> > > I tried re-installing both OS & SQL service packs to see if a DLL or
> > > something was missing - but am still receiving the error - any ideas ?
> > >
> > > Thanks!
> > >
> Good Point & thanks -
> MMC.exe runs OK - I added various snapins - all worked successfully -
> tried to add enterprise manager.msc - it blows withthe same error
> Thanks
>|||In article <CD313813-AB93-40D6-90A4-8BF3DB4682F5@.microsoft.com>,
jbellnewsposts@.hotmail.com says...
SQL is SP4 -
I had re-applied that service pack -
But I will retry after renaming the enterprise manager.msc file
Thanks!
> Hi
> You don't say what service pack you are on!
> But you could try renaming the MMC and loading the renamed file (which seems
> to sometimes work!), renaming the MMC file and installing a service pack, or
> copy the MMC from another machine that is on the same fix level.
> John
>

Enterprise Manager Won't Start

All of a sudden, whenever I try to start the SQL Server
Enterprise Manager, I get the following error:
MMC cannot open the file C:\Program File\...\SQL Server
Enterprise Manager.MSC.
This may be because the file does not exist, is not an
MMC console, or was created by a later version of MMC.
This may also be because you do not have sufficient
access rights to the file.
I've uninstalled and re-installed SQL Server at least
four times now. I still get the same error. What is
going on? The file exists. I can see it. Please, I
need some insight on this. Help!
Glenn,
Try reapplying the latest SQL Server 2000 service pack. If that doesn't
work try copying SQLEM.MSC from the SQL Server CD (remember to rename it
to Enterprise Manager.msc on your machine).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Glenn Cadoret wrote:
> All of a sudden, whenever I try to start the SQL Server
> Enterprise Manager, I get the following error:
> MMC cannot open the file C:\Program File\...\SQL Server
> Enterprise Manager.MSC.
> This may be because the file does not exist, is not an
> MMC console, or was created by a later version of MMC.
> This may also be because you do not have sufficient
> access rights to the file.
> I've uninstalled and re-installed SQL Server at least
> four times now. I still get the same error. What is
> going on? The file exists. I can see it. Please, I
> need some insight on this. Help!
|||Still no success. I have:
1. Verified all of the file permissions.
2. Installed SP3a.
3. Copied the SQLEM.MSC file, renamed it.
I still get the same error.
Any other possibilities?
Glenn Cadoret

>--Original Message--
>Glenn,
>Try reapplying the latest SQL Server 2000 service pack.
If that doesn't
>work try copying SQLEM.MSC from the SQL Server CD
(remember to rename it[vbcol=seagreen]
>to Enterprise Manager.msc on your machine).
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Glenn Cadoret wrote:
Server[vbcol=seagreen]
Server[vbcol=seagreen]
MMC.
>.
>
|||Glenn,
Well, it sounds like a permissions issue to me. What did you change? ;-)
Can you log on as local administrator and open EM?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Glenn Cadoret wrote:[vbcol=seagreen]
> Still no success. I have:
> 1. Verified all of the file permissions.
> 2. Installed SP3a.
> 3. Copied the SQLEM.MSC file, renamed it.
> I still get the same error.
> Any other possibilities?
> Glenn Cadoret
>
>
> If that doesn't
>
> (remember to rename it
>
> Server
>
> Server
>
> MMC.
|||I had the same problem. When I replaced SQLEM.msc from the CD I tried to
launch it before renaming it and it worked fine but after I renamed it it
would not open.
As long as I dont rename it to its original name it will launch fine? If
this is a permissions issue then it is easily worked around by just renaming
the file. Not a lot of security here on permissions if that is all it takes
to work around the permissons.
"Mark Allison" wrote:

> Glenn,
> Well, it sounds like a permissions issue to me. What did you change? ;-)
> Can you log on as local administrator and open EM?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Glenn Cadoret wrote:
>

Enterprise Manager Won't Start

All of a sudden, whenever I try to start the SQL Server
Enterprise Manager, I get the following error:
MMC cannot open the file C:\Program File\...\SQL Server
Enterprise Manager.MSC.
This may be because the file does not exist, is not an
MMC console, or was created by a later version of MMC.
This may also be because you do not have sufficient
access rights to the file.
I've uninstalled and re-installed SQL Server at least
four times now. I still get the same error. What is
going on? The file exists. I can see it. Please, I
need some insight on this. Help!Does the account SQL Server is using have rights to the
directory? that would be my first guess
>--Original Message--
>All of a sudden, whenever I try to start the SQL Server
>Enterprise Manager, I get the following error:
>MMC cannot open the file C:\Program File\...\SQL Server
>Enterprise Manager.MSC.
>This may be because the file does not exist, is not an
>MMC console, or was created by a later version of MMC.
>This may also be because you do not have sufficient
>access rights to the file.
>I've uninstalled and re-installed SQL Server at least
>four times now. I still get the same error. What is
>going on? The file exists. I can see it. Please, I
>need some insight on this. Help!
>.
>|||Glenn,
Try reapplying the latest SQL Server 2000 service pack. If that doesn't
work try copying SQLEM.MSC from the SQL Server CD (remember to rename it
to Enterprise Manager.msc on your machine).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Glenn Cadoret wrote:
> All of a sudden, whenever I try to start the SQL Server
> Enterprise Manager, I get the following error:
> MMC cannot open the file C:\Program File\...\SQL Server
> Enterprise Manager.MSC.
> This may be because the file does not exist, is not an
> MMC console, or was created by a later version of MMC.
> This may also be because you do not have sufficient
> access rights to the file.
> I've uninstalled and re-installed SQL Server at least
> four times now. I still get the same error. What is
> going on? The file exists. I can see it. Please, I
> need some insight on this. Help!|||Still no success. I have:
1. Verified all of the file permissions.
2. Installed SP3a.
3. Copied the SQLEM.MSC file, renamed it.
I still get the same error.
Any other possibilities?
Glenn Cadoret
>--Original Message--
>Glenn,
>Try reapplying the latest SQL Server 2000 service pack.
If that doesn't
>work try copying SQLEM.MSC from the SQL Server CD
(remember to rename it
>to Enterprise Manager.msc on your machine).
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Glenn Cadoret wrote:
>> All of a sudden, whenever I try to start the SQL
Server
>> Enterprise Manager, I get the following error:
>> MMC cannot open the file C:\Program File\...\SQL
Server
>> Enterprise Manager.MSC.
>> This may be because the file does not exist, is not an
>> MMC console, or was created by a later version of
MMC.
>> This may also be because you do not have sufficient
>> access rights to the file.
>> I've uninstalled and re-installed SQL Server at least
>> four times now. I still get the same error. What is
>> going on? The file exists. I can see it. Please, I
>> need some insight on this. Help!
>.
>|||Glenn,
Well, it sounds like a permissions issue to me. What did you change? ;-)
Can you log on as local administrator and open EM?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Glenn Cadoret wrote:
> Still no success. I have:
> 1. Verified all of the file permissions.
> 2. Installed SP3a.
> 3. Copied the SQLEM.MSC file, renamed it.
> I still get the same error.
> Any other possibilities?
> Glenn Cadoret
>
>>--Original Message--
>>Glenn,
>>Try reapplying the latest SQL Server 2000 service pack.
> If that doesn't
>>work try copying SQLEM.MSC from the SQL Server CD
> (remember to rename it
>>to Enterprise Manager.msc on your machine).
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602.html
>>
>>Glenn Cadoret wrote:
>>All of a sudden, whenever I try to start the SQL
> Server
>>Enterprise Manager, I get the following error:
>>MMC cannot open the file C:\Program File\...\SQL
> Server
>>Enterprise Manager.MSC.
>>This may be because the file does not exist, is not an
>>MMC console, or was created by a later version of
> MMC.
>>This may also be because you do not have sufficient
>>access rights to the file.
>>I've uninstalled and re-installed SQL Server at least
>>four times now. I still get the same error. What is
>>going on? The file exists. I can see it. Please, I
>>need some insight on this. Help!
>>.sql

Enterprise Manager Won't Start

All of a sudden, whenever I try to start the SQL Server
Enterprise Manager, I get the following error:
MMC cannot open the file C:\Program File\...\SQL Server
Enterprise Manager.MSC.
This may be because the file does not exist, is not an
MMC console, or was created by a later version of MMC.
This may also be because you do not have sufficient
access rights to the file.
I've uninstalled and re-installed SQL Server at least
four times now. I still get the same error. What is
going on? The file exists. I can see it. Please, I
need some insight on this. Help!Glenn,
Try reapplying the latest SQL Server 2000 service pack. If that doesn't
work try copying SQLEM.MSC from the SQL Server CD (remember to rename it
to Enterprise Manager.msc on your machine).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Glenn Cadoret wrote:
> All of a sudden, whenever I try to start the SQL Server
> Enterprise Manager, I get the following error:
> MMC cannot open the file C:\Program File\...\SQL Server
> Enterprise Manager.MSC.
> This may be because the file does not exist, is not an
> MMC console, or was created by a later version of MMC.
> This may also be because you do not have sufficient
> access rights to the file.
> I've uninstalled and re-installed SQL Server at least
> four times now. I still get the same error. What is
> going on? The file exists. I can see it. Please, I
> need some insight on this. Help!|||Still no success. I have:
1. Verified all of the file permissions.
2. Installed SP3a.
3. Copied the SQLEM.MSC file, renamed it.
I still get the same error.
Any other possibilities?
Glenn Cadoret

>--Original Message--
>Glenn,
>Try reapplying the latest SQL Server 2000 service pack.
If that doesn't
>work try copying SQLEM.MSC from the SQL Server CD
(remember to rename it
>to Enterprise Manager.msc on your machine).
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>Glenn Cadoret wrote:
Server[vbcol=seagreen]
Server[vbcol=seagreen]
MMC.[vbcol=seagreen]
>.
>|||Glenn,
Well, it sounds like a permissions issue to me. What did you change? ;-)
Can you log on as local administrator and open EM?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Glenn Cadoret wrote:[vbcol=seagreen]
> Still no success. I have:
> 1. Verified all of the file permissions.
> 2. Installed SP3a.
> 3. Copied the SQLEM.MSC file, renamed it.
> I still get the same error.
> Any other possibilities?
> Glenn Cadoret
>
>
> If that doesn't
>
> (remember to rename it
>
> Server
>
> Server
>
> MMC.
>|||I had the same problem. When I replaced SQLEM.msc from the CD I tried to
launch it before renaming it and it worked fine but after I renamed it it
would not open.
As long as I dont rename it to its original name it will launch fine? If
this is a permissions issue then it is easily worked around by just renaming
the file. Not a lot of security here on permissions if that is all it takes
to work around the permissons.
"Mark Allison" wrote:

> Glenn,
> Well, it sounds like a permissions issue to me. What did you change? ;-)
> Can you log on as local administrator and open EM?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Glenn Cadoret wrote:
>

Enterprise Manager Won't Run

I am unable to start my Enterprise Manager. I get the following error message:
"Microsoft Management Console has encountered a problem and needs to close".
This is a SQL 2000 system.
This happened one day for no apparent reason. I use SQL Enterprise Manager
everyday. I have uninstalled and reinstalled the product and have the same
problem.
Help is appreciated. Thanks.
Hi
Have you tried to run MMC on it's own and then open up the SQL Server
Enterprise Manager MMC?
John
"john" <john@.discussions.microsoft.com> wrote in message
news:4AA5174A-D9C1-4FA1-979C-3DAC67521A14@.microsoft.com...
>I am unable to start my Enterprise Manager. I get the following error
>message:
> "Microsoft Management Console has encountered a problem and needs to
> close".
> This is a SQL 2000 system.
> This happened one day for no apparent reason. I use SQL Enterprise Manager
> everyday. I have uninstalled and reinstalled the product and have the same
> problem.
> Help is appreciated. Thanks.
|||Yes, I can start say Computer Management which uses the MMC and it opens just
fine. I can leave it open and then try to start the Enterprise Manaer and i
get the same problem. I am not sure if they use the same version of the MMC
or not.
"John Bell" wrote:

> Hi
> Have you tried to run MMC on it's own and then open up the SQL Server
> Enterprise Manager MMC?
> John
> "john" <john@.discussions.microsoft.com> wrote in message
> news:4AA5174A-D9C1-4FA1-979C-3DAC67521A14@.microsoft.com...
>
>
|||Hi
You can therefore try and open the SQL Server Enterprise Manager.mmc from
the tools (or the tools/html) directory.
You could try copying the file from another machine and possibly renaming
it, if it continues to cause an issue.
John
"john" <john@.discussions.microsoft.com> wrote in message
news:C9132729-931C-488D-9F96-63AB177CA33D@.microsoft.com...[vbcol=seagreen]
> Yes, I can start say Computer Management which uses the MMC and it opens
> just
> fine. I can leave it open and then try to start the Enterprise Manaer and
> i
> get the same problem. I am not sure if they use the same version of the
> MMC
> or not.
>
> "John Bell" wrote:
|||I tried copying the MMC from another computer that can run enterprise Manager
and i had the same problem. Then I renamed it as you siuggested and now it
will run. That seems so strange. Any ideas why this works?
thanks for your help
john
"John Bell" wrote:

> Hi
> You can therefore try and open the SQL Server Enterprise Manager.mmc from
> the tools (or the tools/html) directory.
> You could try copying the file from another machine and possibly renaming
> it, if it continues to cause an issue.
> John
> "john" <john@.discussions.microsoft.com> wrote in message
> news:C9132729-931C-488D-9F96-63AB177CA33D@.microsoft.com...
>
>
|||Did you try to register a custom MMC ? Open Managment Console, File
Add/Remove Snappin, Add , SQL Server Enterprise Manager
Save the Console under a known destination path, try to open it from
this msc file.
Would be interesting if the problems occurs after creating this custom
console.
HTH, jens Suessmeyer.

Enterprise Manager Problem

Hi,

I have the following problem:

When I start Enterprise Manager and open a window (editing a table, or showing data in a table) it works correctly. But, when I open other windows after that (editing a table, or showing data in a table), the window opens but I get always a white page. The data and the grid are not shown. Only the data of the selected cell is shown.

I uninstalled it and installed SQL Server again, the problem still remains.

What can I do?

I use:
Windows XP Proffessional
SQL Server 2000 Developer Edition + SP3a

KaaNDid you check the event viewer on your system to see if it recorded any errors?

Which version of SQL Server are you running? Do you have all service packs installed?

You might also try unregistering the SQL Server and re-registering.

(Sorry, grasping at straws)

Terri

Enterprise Manager for DTS doesn't work

When I try to open a package in Server/Data transformation
Services/Local packages MMC opens a new window and displays
a warning with the following text: "The specified module
could not be found" The caption of the box says: "DTS
Designer error"
Anybody a solution?
Server: W2K, sp 4
Client: XPPro, sp1
SQL server 2000
Thanks in advance,
Tjalling
Can you re-apply the latest SP please, seems like one of the files is not
registered/config'd correctly.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tjalling" <t.ament@.go-tan.nl> wrote in message
news:347c01c47ebe$644f9680$a401280a@.phx.gbl...
> When I try to open a package in Server/Data transformation
> Services/Local packages MMC opens a new window and displays
> a warning with the following text: "The specified module
> could not be found" The caption of the box says: "DTS
> Designer error"
> Anybody a solution?
> Server: W2K, sp 4
> Client: XPPro, sp1
> SQL server 2000
> Thanks in advance,
> Tjalling
|||

Quote:

Originally posted by Euan Garden[MS]
Can you re-apply the latest SP please, seems like one of the files is not
registered/config'd correctly.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tjalling" <t.ament@.go-tan.nl> wrote in message
news:347c01c47ebe$644f9680$a401280a@.phx.gbl...
> When I try to open a package in Server/Data transformation
> Services/Local packages MMC opens a new window and displays
> a warning with the following text: "The specified module
> could not be found" The caption of the box says: "DTS
> Designer error"
> Anybody a solution?
> Server: W2K, sp 4
> Client: XPPro, sp1
> SQL server 2000
> Thanks in advance,
> Tjalling

I had the same error message.
I looked at the the sqlsp installation log "C:\WINDOWS\sqlsp.log" and found what seemed to be a warning about Microsoft Data Access Components (MDAC) and installed MDAC 2.8.
Log entry:
C:\Download\mdac26Sp2\sql2ksp3\x86\Other\sqlredis. exe /q:a /C:"setupre.exe MDACQFE=0 WARN=1 -s -SMS" ExitCode: 0
After installing it worked.
SQL Server 2000
XPPro, sp1
Nicole

Enterprise Manager error

After installing Enterprise Manager on a new HP laptop running XP Pro sp2, I get the following error message when to trying to "Return all rows" from a table. "Provider cannot be found. It may not be properly installed."

Everything I read on the internet says to install or reinstall Jet 4.0 sp8, but this has not solved the problem. I've also installed the latest MDAC...MDAC 2.8 SP1 on Windows XP SP2, and ran the compchecker, everthing checks out ok. Any ideas?

Thanks
T.C.

I have a new Sony VAIO laptop and am running XP Pro sp2 and get the same error when trying to open a table in any database.
I just installed SQL2000 sp4 hoping the error would go away...it didn't.
Did your problem ever get resolved?
|||

Hello,

I had searched through Google and Microsoft website for the error message. I find a few matches. You could try the solution suggested in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44561. Another link with suggestion from alleged Microsoft online support is http://www.mcse.ms/message201860.html. It could be due to MDAC issue.

enterprise manager error

when launching the enterprise manager software, I'm getting the following message "title microsoft sql-dmo, error 126 : genral error". I could not find informations onthis error in the help. does someo,ne have an idea?
Hi,
You may need to re-register the SQL DMO dlls. Verify the below link:-
http://support.microsoft.com/?id=248241
Thanks
Hari
MCDBA
"erwan" <anonymous@.discussions.microsoft.com> wrote in message
news:7D9E5861-233F-4850-A7F2-888FB017E8E3@.microsoft.com...
> when launching the enterprise manager software, I'm getting the following
message "title microsoft sql-dmo, error 126 : genral error". I could not
find informations onthis error in the help. does someo,ne have an idea?
|||thanks hari I've fixed the problem

enterprise manager error

when launching the enterprise manager software, I'm getting the following me
ssage "title microsoft sql-dmo, error 126 : genral error". I could not find
informations onthis error in the help. does someo,ne have an idea?Hi,
You may need to re-register the SQL DMO dlls. Verify the below link:-
http://support.microsoft.com/?id=248241
Thanks
Hari
MCDBA
"erwan" <anonymous@.discussions.microsoft.com> wrote in message
news:7D9E5861-233F-4850-A7F2-888FB017E8E3@.microsoft.com...
> when launching the enterprise manager software, I'm getting the following
message "title microsoft sql-dmo, error 126 : genral error". I could not
find informations onthis error in the help. does someo,ne have an idea?|||thanks hari I've fixed the problem

Enterprise Manager error

After installing Enterprise Manager on a new HP laptop running XP Pro sp2, I get the following error message when to trying to "Return all rows" from a table. "Provider cannot be found. It may not be properly installed."

Everything I read on the internet says to install or reinstall Jet 4.0 sp8, but this has not solved the problem. I've also installed the latest MDAC...MDAC 2.8 SP1 on Windows XP SP2, and ran the compchecker, everthing checks out ok. Any ideas?

Thanks
T.C.

I have a new Sony VAIO laptop and am running XP Pro sp2 and get the same error when trying to open a table in any database.
I just installed SQL2000 sp4 hoping the error would go away...it didn't.
Did your problem ever get resolved?
|||

Hello,

I had searched through Google and Microsoft website for the error message. I find a few matches. You could try the solution suggested in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44561. Another link with suggestion from alleged Microsoft online support is http://www.mcse.ms/message201860.html. It could be due to MDAC issue.

Enterprise Manager error

After installing Enterprise Manager on a new HP laptop running XP Pro sp2, I get the following error message when to trying to "Return all rows" from a table. "Provider cannot be found. It may not be properly installed."

Everything I read on the internet says to install or reinstall Jet 4.0 sp8, but this has not solved the problem. I've also installed the latest MDAC...MDAC 2.8 SP1 on Windows XP SP2, and ran the compchecker, everthing checks out ok. Any ideas?

Thanks
T.C.

I have a new Sony VAIO laptop and am running XP Pro sp2 and get the same error when trying to open a table in any database.

I just installed SQL2000 sp4 hoping the error would go away...it didn't.

Did your problem ever get resolved?|||

Hello,

I had searched through Google and Microsoft website for the error message. I find a few matches. You could try the solution suggested in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44561. Another link with suggestion from alleged Microsoft online support is http://www.mcse.ms/message201860.html. It could be due to MDAC issue.