How do I Limit SQL Transaction Logs? - Database Discussion Boards

Jörgen Sigvardsson3-Jun-02 2:42

3-Jun-02 2:42

I'm trying to implement simple OLEDB consumer classes for some stored procedures. It turned out that SPs must be attributed in VS.NET.

If I have understood attributes correctly, they are static properties right? So if I have an OLEDB SP like this:

<br />
[<br />
db_source(L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=xxxdb;Data Source=SOMEHOST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SOMEWORKSTATION;Use Encryption for Data=False;Tag with column collation when possible=False"),<br />
	db_command(L"{ ? = CALL dbo.SomeProcedure(?,?,?) }")<br />
]<br />
class CLoadSheetItem<br />
{<br />
public:<br />
 // ...<br />
};<br />

How can I change the Data Source property during runtime? I want this software to be flexible enough so that I can install it and do a simple setup during installation. Is it even possible to change the data source attribute at runtime?

Sonorked as well: 100.13197 jorgen
FreeBSD is sexy.

Re: Attributed OLEDB question

Jörgen Sigvardsson3-Jun-02 2:51

Jörgen Sigvardsson

3-Jun-02 2:51

Nevermind that question.. Smile | :)

Sonorked as well: 100.13197 jorgen
FreeBSD is sexy.

MySQL Question

Venet2-Jun-02 5:29

Venet

2-Jun-02 5:29

All right CPians.

This is not a programming question and I'm sure it is very basic, so I
thought I'll ask it in here.

My friend has database that he is working on. At the moment he has machine that runs MySQL server (running on Windows) and he connects from his workstation.

Now I need to have the same database running on Linux.

So my question is where the data is stored. Is it stroed on the server or in his workstation. As you can see I have no experience in MySQL so any help would be appreciated.

Venet.

Aquila non captat muscas.

Problem with ADO function GetCollect

31-May-02 23:05

When i use GetCollect on WindowsXP machine, it's OK, but on Windows2000 it isn't. I have tryed with: Fields->GetItem(&varIndex)->GetValue(), but the result was the same. Have anybody some idea?

return Recordset

Max Santos31-May-02 8:43

Max Santos

31-May-02 8:43

How can i return a recorset from a Stored procedure ?

rs = Cmd1->Execute(NULL, NULL,adCmdStoredProc);

why is this allways returning a closed recorset?

Casa.Sapo.pt

Re: return Recordset

Mazdak31-May-02 9:58

Mazdak

31-May-02 9:58

I don't know the answer but there are some articles in CP about stored procedure.Have you looked at them?

http://www.codeproject.com/database

Mazy

"The more I search, the more my need
For you,
The more I bless, the more I bleed
For you."The Outlaw Torn-Metallica

Re: return Recordset

Rashid Thadha3-Jun-02 12:38

Rashid Thadha

3-Jun-02 12:38

That third parameter on your Execute call looks dodgy, I think it should be something like adNoOptionSpecified (it might be slightly spelt incorrectly, check MSDN)

How do I Limit SQL Transaction Logs?

Larry Antram30-May-02 16:34

Larry Antram

30-May-02 16:34

I'm running SQL Server 2000. I have Transaction Logs configured to "Automatically Grow File" with "Unrestricted File Growth"... and I've noticed that some are starting to get quite huge.

My questions are this:

- Is there an easy way to keep the transaction log no larger than a certain size, without human intervention?

- If I tell SQL Server 2000 to limit the transaction log growth to X MB, will it stop when it hits X MB (and SQL server stop working?) or will it simply keep the last X MB of transaction log information?

- I don't think I actually ever use transactions. I mostly just use the basic SQL commands (Insert, Delete, etc.). What is the purpose of "transaction logs" if I'm not using "transactions" when I'm modifying data? Are they needed? Can they be turned off?

Thanks for any insight into these questions. It is greatly appreciated.

Regards, Larry Antram
Stardust Software

"I know not with what weapons World War III will be fought, but World War IV will be fought with sticks and stones."
-- Albert Einstein

Re: How do I Limit SQL Transaction Logs?

Ed Gadziemski1-Jun-02 12:22

Ed Gadziemski

1-Jun-02 12:22

I'm going from memory here, so hope this is right:

I think it will crash if you limit the log to X MB and fill it up. Instead, do a "dump trans" (something like that, you'll need to look up the exact syntax) regularly, perhaps as part of a nightly script. That will stop the logs from growing so big.

Transactions are automatically generated for some operations such as insert and delete. You can also explicitly bound a transaction with begin trans....end trans.

Re: How do I Limit SQL Transaction Logs?

3-Jun-02 21:23

Hi Larry,

you can't stop growing this file. You have to backup the log-file using the option "Remove inactive entries from transaction log". Use "Database Maintenance Plans" to do it without human intervention. If your file is too large you can shrink it after your log-backup.

Regards,
Sebastian

Re: How do I Limit SQL Transaction Logs?

Andrew Connell2-Jul-02 3:31

Andrew Connell

2-Jul-02 3:31

Do complete DB backups or Tran Log backups are regular intervals. My general rule is to run a complete backup on Sunday at 3a and then run differential backups at 3a daily after that... each week overwriting the previous week (history is maintained through tapes).

-AC MCDBA

Andrew Connell
IM on MSN
andrew@aconnell.com

Mixed mode installation MSDE 2000

29-May-02 2:56

I want to install a SQL server 2000 desktop engine with mixed mode authentication on a Windows 2000 professional machine. By default the DB engine is installed in windows authentication mode. How do change that to mixed mode (windows autentication + sql server authentication). I have the SQL server 2000 developper edition CD.

Rustan

Re: Mixed mode installation MSDE 2000

Sebastian Weber5-Jun-02 0:08

Sebastian Weber

5-Jun-02 0:08

i'm not shure - but have a look at this link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q285097

Sebastian

Re: Mixed mode installation MSDE 2000

Bernhard Hofmann15-May-03 4:46

Bernhard Hofmann

15-May-03 4:46

IT WORKED for me!

Don't worry, nobody lives forever.

CRecordset - SQL - Count()

28-May-02 23:38

I am using a database for storing a set of data, I want to do a query on this data, and select a random returned column. What is the best way to do this?

At present I am doing a query returning a single column 'count(*)' then repeating the same query this time returning the data I want, I then call SetAbsolutePosition() on a random row, to get the data I want.

This seems not to be too bad, but it is with quite a simple query at the moment, this will get a lot more complicated in the future.

I can't return the data I want and the count(*) column as this should only returns 1 row (and fails anyway).

One alternative would be to create a temporary table and then count the rows in this, but I don't know what the overhead of this would be.

Any other ideas? Is their a 'select a random row function' I am being blind to.

Kris

Re: CRecordset - SQL - Count()

Matt Gullett28-May-02 23:53

Matt Gullett

28-May-02 23:53

I think I understand.

Why can't you do your select count(*) to find the # of rows and then do a SELECT TOP [somerandomnumber] to get what you want. Then all you have to do is MoveLast.

Re: CRecordset - SQL - Count()

29-May-02 2:55

Yes, but even so I still need to do two queries (one to get the count, the other to get my data).

I will try using SELECT TOP and see if it improves the speed.

Thanks

SQL problem

Alex Deem28-May-02 19:42

Alex Deem

28-May-02 19:42

Hi,

I have a table consisting of an ID, a Date and a StatusCode. Basically it holds a log of when the status of things ('Defects') identified by the ID changed. (The ID is NOT unique in this table).

I am trying to develop an SQL statement that will return the ID and Date for records that have most recently been changed to a certain status. I am using MS Access.

The Query below will return the date that each Defect's status was last changed:

SELECT DefectNumber, Max(DateTime)
FROM MAIN_Status
GROUP BY DefectNumber

I want to be able to discard records from the results of this that are not currently "issued". NOTE: Using a WHERE StatusCode = "ISS" clause does not do what is required - it will give me the most recent date that each Defect has been issued. After a defect has been issued it can then be changed to completed and thus is no longer relevant.

I hope this makes sense...

Any suggestions at all would be great.
Thanks,

Alex

Re: SQL problem

Matt Gullett28-May-02 23:51

Matt Gullett

28-May-02 23:51

If I understand what you are asking, I think what you need to do is have a WHERE clause with an IN statement

SELECT DefectNumber, Max(DateTime)
FROM MAIN_Status
WHERE ID not IN (SELECT ID FROM MAIN_Status where sts = 'completed)
GROUP BY DefectNumber

Re: SQL problem

Alex Deem29-May-02 0:07

Alex Deem

29-May-02 0:07

Would this not just retrieve the Date for when each defect was last issued?
What I require is a list of defects for which the last action was to issue them, and i require the Date at which this took place.

I imagine this is hard to understand without a more verbose description of the problem. Perhaps explaining an analogous situation could help:

A table of employee details as follows:

DepartmentCode Name Salary
1 Alex 40000
1 Kevin 30000
1 Mark 20000
2 Bruce 50000
2 John 60000
3 Michael 70000

Say I want to find the employees in each Department with the highest salary.

I could do:

SELECT DepartmentCode, MAX(Salary)
FROM employees
GROUP BY DepartmentCode

to get a table with DepartmentCode and Salary, but how can i also get the Name of the highest paid person?

The Name is analogous to my StatusCode, if i can get the StatusCode in the results i can use perhaps a HAVING clause to eliminate the ones that are not "ISS".

I hope this makes things clearer

Thanks for your help Smile | :)

~Alex

Files in SQL Server?

HenrikO28-May-02 8:27

HenrikO

28-May-02 8:27

Hello
I would like to be able to put files in my Database. But it appears to me( as a rookie) that the size of this files must be very limited. Is this true or am I only looking in the worong places. What type of data type should I use in SQL Server? Image? Binary? When I read the help on these types it seems to me that they only can be very small.
I would like to put files in there that can be up to 1-2 MB. Is this possible?

regards
Henrik

Re: Files in SQL Server?

Matt Gullett28-May-02 8:34

Matt Gullett

28-May-02 8:34

Typically, you would use binary column types. You can use TEXT type fields IF you encode your file (Base64 is common) before putting it in the DB.

I have put 10-20 meg files in a database, but it really wasn't a good design. I can't comment on your requirements, but for me it has always been better to have a predefined path on a server where files are placed and then just put the path to the file in the database.

Re: Files in SQL Server?

HenrikO28-May-02 10:12

HenrikO

28-May-02 10:12

Re: Files in SQL Server?

Matt Gullett28-May-02 10:15

Matt Gullett

28-May-02 10:15

Typically I just use on column and insert the file name like

\\NTSRVR01\DBData\SomeDir\SomeFile.someext

Re: Files in SQL Server?

HenrikO28-May-02 10:32

HenrikO

28-May-02 10:32

Thank you very much!!!

It's nice with friendly people when you feel stupid Smile | :)

Last Visit: 31-Dec-99 18:00 Last Update: 24-Sep-24 0:17

Refresh

ᐊ Prev 1...2425 2426 2427 242824292430 2431 2432 2433 2434 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.