Help with SQL 6.5

I know this is an old version but the customer cannot upgrade at this time due to a Mac software issue. I am a newbie so forgive me if I ramble. Here is my question.

Windows Server 2003
SQL running version 6.5
Log size =1998 MB
Log Avail=600 MB
No maint. plan set up on this, and when I try to create one it warns me about running a maint. plan on a DB that is larger than 400 MB. When I try and trucate logs in EM seems like it runs but the size stays the same. The customer restarts the SQL service and users are then able to log in.

THe database used to run on a NT 4.0 box up till 6 months ago when it was moved to the 2003 box. It ran fine up till last week. The customer tells me that people have been getting errors logging in. In the event viewer the following error reports.

Event Type: Error
Event Source: MSSQLServer
Event Category: (2)
Event ID: 17060
Date: 8/4/2006
Time: 7:51:51 AM
User: N/A
Computer: AUX-SERVER
Description:
The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error : 701, Severity: 17, State: 2, There is insufficient system memory to run this query..
Data:
0000: bd 02 00 00 11 00 00 00 ......
0008: 00 00 00 00 07 00 00 00 ......
0010: 6d 61 73 74 65 72 00 master.
Any ideas? Thanks!!What are the errors that the users are getting?

And the all important question, what changed, and who changed it?|||There is insufficient system memory to run this query..I am not sure what changed...|||Is this happening every day, or does it take a few days to "build up"? Also, when it does happen, does everyone get the error message, or do a few people manage to get in, while others are locked out?|||Ok, there are a few different ways to solve this problem.

The underlying problem has to do with how SQL 6.5 allocates memory. There are issues with the way SQL 6.5 works in Windows 2000 and later releases.

The easy solution is to buy a copy of Microsoft Virtual Server, install that onto the box you're using to run SQL 6.5, then create a virtual machine and install Windows NT 4.0 in that virtual machine. At this point, you've got the problem contained and can manage it easily and effectively.

A much more difficult solution (but requiring no additional software or licenses) is to simply work to configure the SQL 6.5 instance so that it uses a fixed amount of memory, then adjust the XP settings in the registry so that they don't strangle themselves when they hit those limits. This isn't usually hard, but it is rather complex and it requires someone that really knows SQL 6.5 and its memory usage... It is not a job for someone without a lot of experience.

There are a number of other possible solutions, but they all have associated risks. You'll have to decide which one suits your needs best if you decide to head down any of these paths.

-PatP|||To answer Mcrowley...it happens every cpl days...all are not able to log in...|||Oh yeah, one relatively simple way to solve this problem if you can afford daily reboots is to reboot the machine every day. This works around the memory allocation problem by not allowing the machine to reach the threshold where it can't effectively allocate memory anymore.

If you can afford the daily reboots, then the simple answer is to just schedule a script to restart (http://www.microsoft.com/technet/scriptcenter/scripts/desktop/state/dmstvb07.mspx) the server.

-PatP|||the machine has been running for a few months, configured the same way, with no problems...why now did it start acting up? Took that long to build up? Total server memory is 1 gig. SQL Server is set up with 32768 (2K blocks) of memory. Like I said I am now well versed in SQL then alone version 6.5!|||The underlying problem depends on the number of occurances of certain behaviors. In other words the problem occurs after the ill-behaved code executes a certain number of times... That number depends on the hardware configuration, device drivers, services, etc.

You've probably just reached the point where the threshold is now low enough to become a "pain point" while it hadn't been one before. This could be because of hardware changes, patches, or even network changes that forced loading additional software/drivers that were configured but not used in the past.

-PatP|||Thanks Pat...would setting up a maintenance plan help for this database? When I try to set one up it warns against setting one up on database's larger than 400mb.|||Setting up a maintenance plan might or might not help with database performance, but it won't do diddly for helping with memory problems. If my analysis of what's causing the machine (SQL Server anyway) to become non-responsive is correct, then a maintenance plan won't make any difference.

SQL 6.5 is a much simpler creature than its successors. The maintenance plans were not too effective, and it was EASY to code a script that did a much better job, especially for databases over about 300 Mb or so. You could ensure basic database health with just two commands DBCC CHECKDB, and DBCC CHECKALLOC, but you still needed to keep an eye on the database on a regular basis to "keep the wheels on the bus"

-PatP

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
Regards,
Diego F.
What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:

> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>
|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
Regards,
Diego F.
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> escribi en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...[vbcol=seagreen]
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
|||Yes, it was exactly that :-)
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribi en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
--
Regards,
Diego F.What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
--
Regards,
Diego F.
"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> escribió en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
>> Hi. I'm writing my firsts stored procedures.
>> I'm trying to get a row from one table and insert the results in other
>> table. By now I have this:
>> create proc registerTable
>> @.email varchar(30)
>> as
>> select name,email
>> from users
>> where email=@.email
>> go
>> insert into employees
>> exec registerTable 'users'
>> go
>> But I need to include the insert in the procedure. How can I do that?
>> --
>> Regards,
>> Diego F.
>>
>>|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>|||Yes, it was exactly that :-)
--
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribió en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>> Hi. I'm writing my firsts stored procedures.
>> I'm trying to get a row from one table and insert the results in other
>> table. By now I have this:
>> create proc registerTable
>> @.email varchar(30)
>> as
>> select name,email
>> from users
>> where email=@.email
>> go
>> insert into employees
>> exec registerTable 'users'
>> go
>> But I need to include the insert in the procedure. How can I do that?
>> --
>> Regards,
>> Diego F.
>>
>>
>

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
Regards,
Diego F.What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:

> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
Regards,
Diego F.
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> escribi en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...[vbcol=seagreen]
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
>|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>|||Yes, it was exactly that :-)
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribi en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>sql

help with query from a sql newbie

hope someone can help

i have a table a temp table that gets created on a daily basis andcan have between 10 -100 rows in it which looks like this

idstarttimeduration

110:00:00600

210:10:00300

311:33:0015

etc

duration is in seconds

what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.

Andy

is the value of column id in running ? What is the version of SQL Server are you using ?|||

apwhelan wrote:

what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.

That is a bit confusing.

What is 'next'?

The row chronologically following?

What are you attempting to 'discover'?

It seems like you may be after the amount of time between events -but that isn't too clear. So in your sample data, there is no 'missing' time between rows 1 and 2, but quite a bit of 'missing' time between rows 2 and 3.

Is that what you wish to display?

|||

I agree this is a little confusing but will the following get you somewhere close to where you want to be?

select t1.id as endoftask, t1.id2 as startoftask, datediff(s,endtime, t2.starttime) as idletime

from (SELECT *, dateadd(s, duration, starttime) as endtime, id+1 as id2

FROM table1) AS t1

inner join table1 t2

on t1.id2 = t2.id

This assumes that your id column is sequential with no missing numbers. If this doesn't work, i guess you could go down the cursor route.

Hope this helps!

|||

Another potential alternative might be something like:

Code Snippet

declare @.temp table
( id integer,
startTime datetime,
duration integer
)

insert into @.temp
select 1, '10:00:00', 600 union all
select 2, '10:10:00', 300 union all
select 3, '11:33:00', 15
--select * from @.temp

;with tempSeq as
( select id,
startTime,
duration,
dateadd(ss, duration, startTime) as endTime,
row_number() over
(order by startTime, id) as Seq
from @.temp
), tempSeq2 as
( select id,
startTime,
duration,
dateadd(ss, duration, startTime) as endTime,
1 + row_number() over
(order by startTime, id) as Seq2
from @.temp
)
select a.id,
a.Seq,
convert(varchar(10), a.startTime, 108) as a_startTime,
a.duration,
convert(varchar(10), a.endTime, 108) as a_endTime,
convert(varchar(10),
case when Seq2 is null then 0
else a.endTime - b.StartTime
end, 108)
as timeDifference
from tempSeq a
left join tempSeq2 b
on a.Seq = b.Seq2

/*
id Seq a_startTime duration a_endTime timeDifference
-- -- -- - --
1 1 10:00:00 600 10:10:00 00:00:00
2 2 10:10:00 300 10:15:00 00:15:00
3 3 11:33:00 15 11:33:15 01:23:15
*/

|||All of these work, but the bigger question is why you need to store that data. The best thing to do is to store the start and end of a thing, and then you can use those to calculate any kind of interval you need, including averages, sums and the like. Remember, avoid storing what you can calculate, unless it becomes a performance issue to do so.

Help with NOT EXISTS query

Newbie here...looking for help with a NOT EXISTS query or suggestions for a
better method.
I have 2 linked tables: CUSTOMERS and ADDRESSES
common fields are CUS_NO and ADR_CD
I need to find records where an address code (ADR_CD) entered into CUSTOMERS
does not have that same ADR_CD existing in the ADRESSES table.

Example:
CUS_NO = 12345
ADR_CD = Ohio01

If the combination of cus_no 12345 and Ohio01 does not exist in the
ADDRESSES table, I need to find them.

Thanks in advance.RDRaider wrote:
> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT <select list>
FROM Customers c
WHERE NOT EXISTS (SELECT *
FROM Addresses a
WHERE c.Cus_No = a.Cust_No
AND c.ADR_CD = a.ADR_CD)

Zach|||On Mon, 13 Dec 2004 20:17:45 GMT, RDRaider wrote:

> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ADDRESSES
WHERE Customers.cus_no = ADDRESSES.cus_no
AND Customers.adr_cd = Addresses.adr_cd )

Alternative method:

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
LEFT JOIN Addresses
ON Customers.cus_no = Addresses.cus_no
AND Customers.adr_cd = Addresses.adr_cd
WHERE Addresses.cus_no IS NULL|||Thanks for the quick reply. It works! I don't know why I couldn't get the
same results, need to hit the books I guess.

"nib" <individual_news@.nibsworld.com> wrote in message
news:326chiF3i7ns1U1@.individual.net...
> RDRaider wrote:
>> Newbie here...looking for help with a NOT EXISTS query or suggestions for
>> a better method.
>> I have 2 linked tables: CUSTOMERS and ADDRESSES
>> common fields are CUS_NO and ADR_CD
>> I need to find records where an address code (ADR_CD) entered into
>> CUSTOMERS does not have that same ADR_CD existing in the ADRESSES table.
>>
>> Example:
>> CUS_NO = 12345
>> ADR_CD = Ohio01
>>
>> If the combination of cus_no 12345 and Ohio01 does not exist in the
>> ADDRESSES table, I need to find them.
>>
>> Thanks in advance.
> SELECT <select list>
> FROM Customers c
> WHERE NOT EXISTS (SELECT *
> FROM Addresses a
> WHERE c.Cus_No = a.Cust_No
> AND c.ADR_CD = a.ADR_CD)
> Zach