Create a Trigger to run a Stored Procedure

I want to create a Trigger that will run once a day at a set time.

This trigger would actually run a Stored Procedure named say "spAppData".

This SP will in turn run and delete the entire tables data, and re-insert data into a table we'll call APPS.

Would anyone know how to help me get started on creating a Trigger with the info I've provided?

Thank you,

NBK

To schedule an action, you want a SQL Agent Job to execute your stored procedure at the determined time.

If you are using SQL Express (which does NOT include SQL Agent), you can use a combination of a Windows Scheduler task, SQLCmd.exe, and a Stored Procedure

|||

Thank you Arnie. That's just the direction I needed.

Wallace

|||can you please give more details on this.
i have same requrement.
thanks in advance

Create a time series

Given the following table information:

HOSTNAME DATETIME
WEBNYC001 2005-06-15 10:30AM
WEBNYC001 2005-06-15 10:31AM
WEBNYC001 2005-06-15 10:31AM
WEBNYC001 2005-06-15 10:34AM
WEBNYC001 2005-06-15 10:35AM
WEBNYC001 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:30AM
WEBNYC002 2005-06-15 10:30AM
WEBNYC002 2005-06-15 10:33AM
WEBNYC002 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:35AM

How can I easily return the following results:
HOSTNAME DATETIME COUNT
WEBNYC001 2005-06-15 10:30AM 1
WEBNYC001 2005-06-15 10:31AM 2
WEBNYC001 2005-06-15 10:32AM 0
WEBNYC001 2005-06-15 10:33AM 0
WEBNYC001 2005-06-15 10:34AM 1
WEBNYC001 2005-06-15 10:35AM 2
WEBNYC002 2005-06-15 10:30AM 2
WEBNYC002 2005-06-15 10:31AM 0
WEBNYC002 2005-06-15 10:32AM 0
WEBNYC002 2005-06-15 10:33AM 1
WEBNYC002 2005-06-15 10:34AM 0
WEBNYC002 2005-06-15 10:35AM 3

Thanks!"joshsackett" <joshsackett@.gmail.com> wrote in message
news:1118852141.669360.188390@.g44g2000cwa.googlegr oups.com...
> Given the following table information:
> HOSTNAME DATETIME
> WEBNYC001 2005-06-15 10:30AM
> WEBNYC001 2005-06-15 10:31AM
> WEBNYC001 2005-06-15 10:31AM
> WEBNYC001 2005-06-15 10:34AM
> WEBNYC001 2005-06-15 10:35AM
> WEBNYC001 2005-06-15 10:35AM
> WEBNYC002 2005-06-15 10:30AM
> WEBNYC002 2005-06-15 10:30AM
> WEBNYC002 2005-06-15 10:33AM
> WEBNYC002 2005-06-15 10:35AM
> WEBNYC002 2005-06-15 10:35AM
> WEBNYC002 2005-06-15 10:35AM
> How can I easily return the following results:
> HOSTNAME DATETIME COUNT
> WEBNYC001 2005-06-15 10:30AM 1
> WEBNYC001 2005-06-15 10:31AM 2
> WEBNYC001 2005-06-15 10:32AM 0
> WEBNYC001 2005-06-15 10:33AM 0
> WEBNYC001 2005-06-15 10:34AM 1
> WEBNYC001 2005-06-15 10:35AM 2
> WEBNYC002 2005-06-15 10:30AM 2
> WEBNYC002 2005-06-15 10:31AM 0
> WEBNYC002 2005-06-15 10:32AM 0
> WEBNYC002 2005-06-15 10:33AM 1
> WEBNYC002 2005-06-15 10:34AM 0
> WEBNYC002 2005-06-15 10:35AM 3
> Thanks!

Here's one possible solution. In general, queries involving ranges of times,
dates or numbers are often easier if you have an auxiliary table to join on.
If you don't want to implement such a table, you could create a table-valued
function which returns all required values between two given datetimes
instead - that would avoid having a potentially very large table in the
database.

Simon

create table dbo.Data (
host char(9) not null,
dt datetime not null
/* need a primary key here */
)
go
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:31AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:31AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:34AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:33AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
go

create table dbo.Times (
tm datetime not null primary key
)
go

insert into dbo.Times select '2005-06-15 10:30AM'
insert into dbo.Times select '2005-06-15 10:31AM'
insert into dbo.Times select '2005-06-15 10:32AM'
insert into dbo.Times select '2005-06-15 10:33AM'
insert into dbo.Times select '2005-06-15 10:34AM'
insert into dbo.Times select '2005-06-15 10:35AM'
go

select
h.host,
t.tm,
coalesce(a.cnt, 0)
from
dbo.Times t
cross join (select distinct host from dbo.Data) h
left outer join
(
select
host,
dt,
count(*) as 'cnt'
from
dbo.Data
group by
host,
dt
) a
on t.tm = a.dt
and h.host = a.host
order by
h.host,
t.tm
go

drop table dbo.Data
drop table dbo.Times
go|||That kicked ass.

Exactly what I needed... thanks!sql

Create a time dimension

Hi !
I have a question : my dataBase contains a table which have an attribute startTime.
The startTime give the day and the hour of an event.
I want to 'display' the event for a period time (one day or one week, or between to date...).
But I don't know how to do... Because if I add a dimension with this startTime, and in my report I put parameter with two date, I can only choose in the date exit in the attribut StartTime, but as I have a lot, an with a small range, I can't display all days...

I would like that the user choose a date as 27/03/07 and the report display all event in this day...
but I don't know what to do, because StartTime is as 27/03/07 10:50:42...

Can anybody help me ? Thank you !!!

In a named calculation(data source view in SSAS2005) or in a view/table(AS2000) you can try this: http://blog.mike-obrien.net/PermaLink,guid,f3363145-8753-4604-8314-855012a00400.aspx

This works with TSQL

HTH

Thomas Ivarsson

|||Hi Thomas,
Thank you for your answer...
But I have a problem not with named calculation but with the dimension, I didn't understand how to do !
As I explain last time, I have a table with a dateTime value, so I have a list of a lot of event with all this dateTime value, I want to organize them by day, So i want a time dimension that give just the day (as your answer) and which it is link with my dateTime, for example I have 5 event the 25/03/2007, 14 event the 26/03/2007...
so I don't what to do...
May I have to create a time dimension from server and my TimeDate are linked to this table ?

|||

Hello! Maybe I am wrong but I understand your problem as that you have time fragments on your date columns in the fact table and that you would like to change that?

The link I have sent you have examples that transform dates with 2007-03-29:13:30:06:01 to 2007-03-29:00:00:00 . If you build a time dimension with a datetime-column and you enter 2007-03-29 you will get 2007-03-29:00:00:00 in that column and a match between the time dimension and the fact table on dates.

Regards

Thomas Ivarsson

Create a table with a field with only Time datatype

Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?

See this enigma's post link (http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk)
Its a good one ...|||i dont understand why you are not thinking in terms of a datetime/smalldatetime field by ignoring the date part and whats wrong in it. however, if you are rigid you can store it either as char field or as numeric field (float/decimal). remember you do not actually save anything by doing so.

storing as char field is straight forward. if you want to store as number (remember that each date+time has a numeric representation) can take hints from the following queries

--1 minute as number
select cast(cast('20060101 11:01 AM' as smalldatetime) as float)- cast(cast('20060101 11:00 AM' as smalldatetime) as float)
--it returns approx 0.0006944

--use that to get a date time 20060101 11:00 AM back
select cast( cast(cast('20060101' as smalldatetime) as float) + (0.0006944*60*11) as smalldatetime)

quite unnecessary...;-)sql

Create a login table question

Hey all- I have been reading this forum for quite some time now but I have never posted- this place is a great tool for young developers.

Anyway, I have a specific question.

I have a table (members)- with PK member_id- that holds member names and personal info etc.

My goal is for each member_id in the members table, insert that member_id into my fk column in a logins table. then, when each member_id is inserted, i want to insert the first initial of the member_name and first 4 characters of the last_name, along with the member_id into the logins table in the login_name column.

i also want to insert a unique 6 digit string into the logins table (login_password). I'm not great with script, so any help would be appreciated.

Thanks,

sudeep.Suppose you have this situation:

CREATE TABLE members
(member_id NUMBER(2) CONSTRAINT pk_mem PRIMARY KEY,
member_name VARCHAR2(10),
last_name VARCHAR2(10)
);

CREATE TABLE logins
(member_id NUMBER(2) CONSTRAINT fk_log_mem
REFERENCES members (member_id)
INITIALLY DEFERRED DEFERRABLE,
login_name VARCHAR2(20),
login_password VARCHAR2(6)
);

CREATE SEQUENCE seq_mem_login START WITH 100000;
/* The sequence is used to generate unique login passwords in this example */

CREATE OR REPLACE TRIGGER trg_member
AFTER INSERT
ON members
FOR EACH ROW
BEGIN
INSERT INTO logins
(member_id,
login_name,
login_password
)
VALUES (:NEW.member_id,
SUBSTR (:NEW.member_name, 1, 1)
|| SUBSTR (:NEW.last_name, 1, 4)
|| TO_CHAR (:NEW.member_id),
seq_mem_login.NEXTVAL
);
END;
/

insert into members values (1, 'john', 'smith');

select * from members;
MEMBER_ID MEMBER_NAM LAST_NAME
---- ---- ----
5 john smith

select * from logins;
MEMBER_ID LOGIN_NAME LOGIN_
---- ------- --
5 jsmit5 100000|||thanks man- i'll let you know how it goes in a few.|||whats wrong with this code? anyone?

it gives me the following error:

Server: Msg 128, Level 15, State 1, Procedure trigPassword_CreateMember, Line 9
The name 'member_id' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

--Create the trigger
CREATE TRIGGER trigPassword_CreateMember ON Accounts
AFTER Insert
AS
BEGIN
BEGIN TRAN
INSERT INTO Logins(member_id, login_name, login_password)
VALUES (inserted.member_id, SUBSTRING(inserted.first_name,1,1) + SUBSTRING(last_name,1,1) + CONVERT(char(5),inserted.member_id),
inserted.account_id)
COMMIT TRAN
END
GO

Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Create a daily, weekly, monthly and quarterly average for all measures

hi,

What's the best way to create an average for all time grains across all measures?

At the moment, I've got a calculated measure for each measure, which itself uses two calculated measures:

Code Snippet

DayCount =
count(descendants([DATE TIME].[Calendar], ,LEAVES))

MonthCount =
count(descendants([DATE TIME].[Calendar], [DATE TIME].[Calendar].[Month]))

...

Code Snippet

SumSales =
sum([DATE TIME].[Calendar], [Sales])

SumUnits =
sum([DATE TIME].[Calendar], [Sales Units])

...

Code Snippet

AvgDailySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[DayCount])

AvgMonthlySales =
iif([Measures].[DayCount] = 0, null, [Measures].[SumSalesUnits] / [Measures].[MonthCount])

...

Thanks in advance,

If you search this forum on "AVERAGE" and "TIME" you'll find a bunch of solutions addressing this issue.

B.

|||

Hi mmmman,

For the days counting and using it for avarages caculations you should create additional measure group that use the time dimension table as fact table with a measure that counts days.

If you play futher, for examle to base your calculations on count of business days, set in fact table 0 for weekend days and 1 for work days then made a sum measure on it.

The proposed approach has following advatages: is multiselect aware, hasn't performance drawbacks.

More about day counting you cat take from Mosha's blog

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/27/counting_days_mdx.aspx

where another approaches are also described.

Crazy error when too many rows (records) are requested

Hi,

I am hoping someone here can help me out with this crazy error. I have an rdlc that works fine most of the time. When the user selects certain large date ranges and requests a huge amount of data we get this error:

HttpException (0x80072745): Unable to make the session state request to the session state server. Please ensure that the ASP.NET State
service is started and that the client and server ports are the same. If the server is on a remote machine, please ensure that it
accepts remote requests by checking the value of
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\aspnet_state\Parameters\AllowRemoteConnection. If the server is on the local
machine, and if the before mentioned registry value does not exist or is set to 0, then the state server connection string must use
either 'localhost' or '127.0.0.1' as the server name.]
System.Web.SessionState.OutOfProcSessionStateStore.MakeRequest(StateProtocolVerb verb, String id, StateProtocolExclusive
exclusiveAccess, Int32 extraFlags, Int32 timeout, Int32 lockCookie, Byte[] buf, Int32 cb, Int32 networkTimeout,
SessionNDMakeRequestResults& results) +1565
System.Web.SessionState.OutOfProcSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData
item, Object lockId, Boolean newItem) +192
System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +355
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Caveat; State Service is running.So, this solution is NOT germanehttp://forums.asp.net/t/1030680.aspx
Do to business requirements I cannot restrict the date ranges a user might enter

Any ideas, suggestions, work-arounds?? Thank you in advance!

I have finally figured out a "solution". Basically, I had to change how session was being managed. It was being manged by StateServer and I switched it to "InProc" and that seemed to do the trick.

Believe me I tried pretty muchEVERYTHING to get this to work and switching the session management was a last ditch, stab-in-the-dark try. And it worked. I think maybe there is some sort of bug with StateServer and rdlc files?

I was able to reproduce the error and the solution locally using a basic .aspx page with an rdlc control. I made a basic object for the data. Dumped a million rows onto the rdlc while running StateServer and got the error posted above. Then I just switched the session management to "InProc" and got the report to display the million rows. It took a bit of spinning but it eventually displayed. I hope this helps somebody out. Take care.

Crash of Enterprise Manager.

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
Any hint?
Thanks
Massimo
Hi,
sounds like you have some problems with Microsoft Management Console (MMC).
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135000434.876224.255900@.g14g2000cwa.googlegr oups.com...
> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> Any hint?
> Thanks
> Massimo
>
|||What have I to do?
|||Hi,
Try reinstalling MMC from
%CD%:\x86\other\instmmc.exe
%CD% is a letter of SQL Server CD-ROM.
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135065562.406941.165770@.g44g2000cwa.googlegr oups.com...
> What have I to do?
>
|||I already done that.
If I log in (to the machine) as a different user, Enterprise manager
runs well. Only with my account it crashes.
Have you more hints for me?
Thanks for your help.
|||Hi,
It couldbe a problem with profile for this user.
Are other MMC consoles running OK?
Maybe you can try deleting the profile and creating one from the scratch for
this user.
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135098033.010474.95190@.g49g2000cwa.googlegro ups.com...
>I already done that.
> If I log in (to the machine) as a different user, Enterprise manager
> runs well. Only with my account it crashes.
> Have you more hints for me?
> Thanks for your help.
>
|||My profile come from active directory domain. I have no permission to
delete it and create it again.
Other mmc are ok, in effect I use "manage computer" (I hope the
translation is correct, I get it with a right click on "my computer")
to connect to the local instace of sql server.
I am preatty sure that the problem is in some settings but I do noty
know where are those settiongs.
Do you know it?
Thanks

Crash of Enterprise Manager.

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
Any hint?
Thanks
MassimoHi,
sounds like you have some problems with Microsoft Management Console (MMC).
--
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135000434.876224.255900@.g14g2000cwa.googlegroups.com...
> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> Any hint?
> Thanks
> Massimo
>|||What have I to do?|||Hi,
Try reinstalling MMC from
%CD%:\x86\other\instmmc.exe
%CD% is a letter of SQL Server CD-ROM.
--
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135065562.406941.165770@.g44g2000cwa.googlegroups.com...
> What have I to do?
>|||I already done that.
If I log in (to the machine) as a different user, Enterprise manager
runs well. Only with my account it crashes.
Have you more hints for me?
Thanks for your help.|||Hi,
It couldbe a problem with profile for this user.
Are other MMC consoles running OK?
Maybe you can try deleting the profile and creating one from the scratch for
this user.
--
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135098033.010474.95190@.g49g2000cwa.googlegroups.com...
>I already done that.
> If I log in (to the machine) as a different user, Enterprise manager
> runs well. Only with my account it crashes.
> Have you more hints for me?
> Thanks for your help.
>|||My profile come from active directory domain. I have no permission to
delete it and create it again.
Other mmc are ok, in effect I use "manage computer" (I hope the
translation is correct, I get it with a right click on "my computer")
to connect to the local instace of sql server.
I am preatty sure that the problem is in some settings but I do noty
know where are those settiongs.
Do you know it?
Thanks

Crash of Enterprise Manager.

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
Any hint?
Thanks
MassimoHi,
sounds like you have some problems with Microsoft Management Console (MMC).
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135000434.876224.255900@.g14g2000cwa.googlegroups.com...
> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> Any hint?
> Thanks
> Massimo
>|||What have I to do?|||Hi,
Try reinstalling MMC from
%CD%:\x86\other\instmmc.exe
%CD% is a letter of SQL Server CD-ROM.
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135065562.406941.165770@.g44g2000cwa.googlegroups.com...
> What have I to do?
>|||I already done that.
If I log in (to the machine) as a different user, Enterprise manager
runs well. Only with my account it crashes.
Have you more hints for me?
Thanks for your help.|||Hi,
It couldbe a problem with profile for this user.
Are other MMC consoles running OK?
Maybe you can try deleting the profile and creating one from the scratch for
this user.
Danijel Novak
"mappopo" <massimo.petrillo@.gmail.com> wrote in message
news:1135098033.010474.95190@.g49g2000cwa.googlegroups.com...
>I already done that.
> If I log in (to the machine) as a different user, Enterprise manager
> runs well. Only with my account it crashes.
> Have you more hints for me?
> Thanks for your help.
>|||My profile come from active directory domain. I have no permission to
delete it and create it again.
Other mmc are ok, in effect I use "manage computer" (I hope the
translation is correct, I get it with a right click on "my computer")
to connect to the local instace of sql server.
I am preatty sure that the problem is in some settings but I do noty
know where are those settiongs.
Do you know it?
Thanks

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
MassimoHi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:

> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>|||I think so. Where are those informations?|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:

> I think so. Where are those informations?
>

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
Massimo
Hi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:

> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>
|||I think so. Where are those informations?
|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:

> I think so. Where are those informations?
>

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
Massimo
Hi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:

> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>
|||I think so. Where are those informations?
|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:

> I think so. Where are those informations?
>

Crash of Enterprise Manager, strange solution

Hi,
Every time I try to use enterprise manager it suddenlly crashes.
The server is on, I can access it in other ways but without using
enterprise manager.
I removed sqlserver from the system, and installed it again.
I also installed the service pack 4.
I made many attempt to solve the problem.
At last I renamed the file "C:\Program files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
et voila EM runs well again.
Does anyone know why?
Thanks
MassimoHi
I presume copying the MMC back to the original file name MMC still fails? In
which case MMC there must be some configuration information linked to the
file name.
John
"mappopo" wrote:
> Hi,
> Every time I try to use enterprise manager it suddenlly crashes.
> The server is on, I can access it in other ways but without using
> enterprise manager.
> I removed sqlserver from the system, and installed it again.
> I also installed the service pack 4.
> I made many attempt to solve the problem.
> At last I renamed the file "C:\Program files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC", double click
> et voila EM runs well again.
> Does anyone know why?
> Thanks
> Massimo
>|||I think so. Where are those informations?|||Hi
I would have expected it to be in the registry, but searching it didn't seem
to turn up anything!
John
"mappopo" wrote:
> I think so. Where are those informations?
>

CR8.5 and VB6 - I dont want it to create the form!

Every time I add a new Crystal Report to my VB6 project it used to ask if I wanted to add a form with the viewer control on it automatically (it also asked something else, but I can't remember what). I got sick of hitting "No" then "OK" so I checked the "don't ask me this question again" style box.

Now, every time I add a report it also adds a form to my project with the viewer control. I don't want it to do this, as it's annoying removing the useless form and it is screwing with my source control. And it does it even though when i checked the "don't ask me... " i told it not to create the form.

So I've checked all the settings I can find and I can't find anything to stop it happening. Does anyone have a quick fix to this before I go trawling through the registry to find a solution?update - i trawled the registry, found some likely candidates, but none of them worked. It's real frustrating, so any help will be appreciated, but I can live with it (I've lived with worse)

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions'
thx for help
--
PITPit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is often
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such a
> heave load on CPU?
> any suggestions'
> thx for help

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions?
thx for help
PIT
Pit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is often
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such a
> heave load on CPU?
> any suggestions?
> thx for help

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions'
thx for help
--
PITPit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is ofte
n
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such
a
> heave load on CPU?
> any suggestions'
> thx for help

CPU usage

I have been using SQL Server 2000 for some time and am attempting to upgrade
to SQL Server 2005 (testing the trial version).
I backed up my SQL Server 2000 database(s) and then restored them on SQL
Server 2005 which worked well.
Everything seemed to be working fine until I updated SQL Server 2005 with
service pack 2.
I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
is now using 25% CPU continuously (or 100% of one of the processor
instances). The databases I converted are working as expected, however, the
SQL Server process is continuously burning CPU when nothing is happening
(this is over several days now).
Has anybody else see this problem?
Ross,
I have seen this happen in the past, for many different reasons.
I assume from your comment that you know that it is sqlservr.exe that is
using the 25%. Can you run sp_who2 to identify some process that continues
to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
the oldest transaction in each database. (However, I assume that there is
not a blocking transaction or you would be having other problems.)
If you identify a problem spid, try to get the hostname for that spid.
Sometimes a client computer loses connectivity, but the SQL Server does not
know that, so it keeps trying (forever) to send the result set to the
client. If that is the case, logging the hostname computer off of the
domain, then logging back into the domain, often alerts SQL Server to the
problem so that it will abandon that result set.
If appropriate, you can try to KILL the spid. (But the spid may not always
be killable.)
Other software can also get into a confused state and eat up CPU. For
example, a DTS package, backup software, etc. can also get into an confused
state and never stop running. Use Windows Task Manager to check whether
another task is actually the guilty party. If it is one of those, then try
killing that Windows process.
If you cannot do anything else, you should schedule a restart of your SQL
Sever. That should (naturally) clear the problem out. But you will need to
remain alert to its returning.
But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>I have been using SQL Server 2000 for some time and am attempting to
>upgrade
> to SQL Server 2005 (testing the trial version).
> I backed up my SQL Server 2000 database(s) and then restored them on SQL
> Server 2005 which worked well.
> Everything seemed to be working fine until I updated SQL Server 2005 with
> service pack 2.
> I am running this on a Dual-Core AMD Opteron Processor 2216HE. SQL Server
> is now using 25% CPU continuously (or 100% of one of the processor
> instances). The databases I converted are working as expected, however,
> the
> SQL Server process is continuously burning CPU when nothing is happening
> (this is over several days now).
> Has anybody else see this problem?
>
|||Thanks for your answer.
It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
actually restarted SQL Server (and the machine itself) and subsequently the
SQL Server process goes right back up to 25%.
As you noted regarding the blocking transaction, I'm not experiencing any
problems with the database, just the CPU usage.
The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468 (as
it happens, the machine was restarted at 11:19PM EST last night).
Full row information from sp_who2:
SPID 3
Status BACKGROUND
Login sa
HostName .
BlkBy .
DBName NULL
Command LAZY WRITER
CPU TIME 71195468
DiskIO 0
LastBatch 03/12 23:19:34
ProgramName (blank)
SPID 3
REQUESTID 0
Any ideas?
Thanks again for your help...
Ross
"Russell Fields" wrote:

> Ross,
> I have seen this happen in the past, for many different reasons.
> I assume from your comment that you know that it is sqlservr.exe that is
> using the 25%. Can you run sp_who2 to identify some process that continues
> to eat up CPU time? If so, use dbcc inputbuffer, fn_get_sql, or look at
> sys.dm_exec_sql_text to see what is running. Also use DBCC OPENTRAN to find
> the oldest transaction in each database. (However, I assume that there is
> not a blocking transaction or you would be having other problems.)
> If you identify a problem spid, try to get the hostname for that spid.
> Sometimes a client computer loses connectivity, but the SQL Server does not
> know that, so it keeps trying (forever) to send the result set to the
> client. If that is the case, logging the hostname computer off of the
> domain, then logging back into the domain, often alerts SQL Server to the
> problem so that it will abandon that result set.
> If appropriate, you can try to KILL the spid. (But the spid may not always
> be killable.)
> Other software can also get into a confused state and eat up CPU. For
> example, a DTS package, backup software, etc. can also get into an confused
> state and never stop running. Use Windows Task Manager to check whether
> another task is actually the guilty party. If it is one of those, then try
> killing that Windows process.
> If you cannot do anything else, you should schedule a restart of your SQL
> Sever. That should (naturally) clear the problem out. But you will need to
> remain alert to its returning.
> But, FWIW, I have not seen much of this on SQL Server 2005 SP2.
> RLF
>
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:3BB4D4DE-BFA5-431D-971B-4CB63C4E9381@.microsoft.com...
>
>
|||Ross,
Check out: http://support.microsoft.com/kb/931821
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer.
> It is definitely SQL Server that is eating 25% CPU (sqlservr.exe). I have
> actually restarted SQL Server (and the machine itself) and subsequently
> the
> SQL Server process goes right back up to 25%.
> As you noted regarding the blocking transaction, I'm not experiencing any
> problems with the database, just the CPU usage.
> The sp_who2 shows a CPUTime for a "LAZY WRITER" process id 3 of 71195468
> (as
> it happens, the machine was restarted at 11:19PM EST last night).
> Full row information from sp_who2:
> SPID 3
> Status BACKGROUND
> Login sa
> HostName .
> BlkBy .
> DBName NULL
> Command LAZY WRITER
> CPU TIME 71195468
> DiskIO 0
> LastBatch 03/12 23:19:34
> ProgramName (blank)
> SPID 3
> REQUESTID 0
> Any ideas?
> Thanks again for your help...
> Ross
>
> "Russell Fields" wrote:
|||Thank you very much.
I've requested the hotfix and will see what happens.
"Russell Fields" wrote:

> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Unfortunately, that didn't go very well.
I installed the hotfix which appeared to solve the CPU problem (sqlservr.exe
back at 0% when no activity). I was able to run the management software and
all looked fine, but when I attempted to run my web application, sql server
choked. A simple query returning a datareader (ASP.NET) caused SQLServer to
get a memory exception.
Needless to say, I removed the Hotfix and am now back where I started. I
guess I'll have to wait until Microsoft releases the hotfix in an update.
Ross
"Russell Fields" wrote:

> Ross,
> Check out: http://support.microsoft.com/kb/931821
> RLF
> "Ross" <Ross@.discussions.microsoft.com> wrote in message
> news:51734426-C053-4F76-9801-137FFC3DCACB@.microsoft.com...
>
>
|||Ross,
Too bad. Memory exceptions are bugs, so you could report it to Microsoft if
you want.
RLF
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:8498810D-A651-4F6C-9970-A6FD26FC8835@.microsoft.com...[vbcol=seagreen]
> Unfortunately, that didn't go very well.
> I installed the hotfix which appeared to solve the CPU problem
> (sqlservr.exe
> back at 0% when no activity). I was able to run the management software
> and
> all looked fine, but when I attempted to run my web application, sql
> server
> choked. A simple query returning a datareader (ASP.NET) caused SQLServer
> to
> get a memory exception.
> Needless to say, I removed the Hotfix and am now back where I started. I
> guess I'll have to wait until Microsoft releases the hotfix in an update.
> Ross
> "Russell Fields" wrote: