Database

SQL and the growing log file

27-Aug-04 1:33

DECLARE @id int;
INSERT INTO aTable(name, age)
VALUES('Joe Bloggs', 21);
SELECT @id = @@IDENTITY;
INSERT INTO anotherTable(id, phone)
VALUES(@id, '212-555-2468');

If you are doing this from within an ASP.NET application then you can do something like

CREATE PROCEDURE InsertATable
(
    @name varchar(32),
    @age int
)
AS
INSERT INTO aTable(name, age)
VALUES('Joe Bloggs', 21);
SELECT @@IDENTITY as Id;
GO

SqlCommand cmd = new SqlCommand("InsertATable", connection);
cmd.Parameters.Add("@name", "Joe Bloggs");
cmd.Parameters.Add("@age", 21);
cmd.CommandType = CommandType.StoredProcedure;
int id = (int)cmd.ExecuteScalar();
// I now have the id of the newly created record in my .NET application.

Does this help?

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

totig25-Aug-04 20:02

totig

25-Aug-04 20:02

I have a log file that has grown a lot. I need to get it smaller. I have been reading SQL books online, and while it explains the problem, I cannot work out what the solution is to it. The database is being backed up each night, not the log. I would like to know if there is a way that the log can drop all the logs until the last database backup.

mikasa26-Aug-04 3:40

mikasa

26-Aug-04 3:40

If you're not concerned with the Log, change it to "Simple" mode on the Server. I am pretty sure though that if you are backing up the Logs, it will shrink the Log, but I can't be sure off the top of my head.

Mekong River26-Aug-04 3:41

Mekong River

26-Aug-04 3:41

You should check the property of SQL server database. In the property dialog box click on the third tab "Transaction Log" property. This page contain the information about setting how the log file growth it size. If you have a problem with the size of the log file growth very fast try to choose an option "Restrict File Growth" option and set it value.

Hope it is work!!!

APO-CEDC
Save Children Norway-Cambodia Office

totig26-Aug-04 3:47

totig

26-Aug-04 3:47

I found that changing the db to simple, and then running the shrink db command, the log file has dropped and wont grow again. Limiting the log in this instance would not have worked, as the db was expecting the log file to be backed up as well. Had the log file been set to backup, the log files size would have decreased.

Nested Transaction between ADO.NET and T-SQL

Rocky Moore31-Aug-04 20:18

Rocky Moore

31-Aug-04 20:18

Glad you brought this up as I reminded me to make a tip out of the backup procedure I use on my server.

http://www.hintsandtips.com/ShowTip/130/hat.aspx[^]

I use the "backup log with truncate_only" and then use the "DBCC SHRINKFILE".

If you restrict the length of the log, it will fill your NT Event log with error messages when the log reaches the limit.

Rocky <><
www.HintsAndTips.com - Now with GMail Queue
www.MyQuickPoll.com - 2004 Election poll is #33
www.GotTheAnswerToSpam.com

"We plan for the future, we learn from the past, we live life in the present!"

Sanjeev Kumar25-Aug-04 14:29

25-Aug-04 14:29

I am writing an application that calls numbers of stored procedure wrapped in an ADO.NET transaction. Some of the stored procedure implements T-SQL transactions.
Is it safe to call Stored Procedure like this or there is potential of data corruption.

Have anyone seen any document or article that explain how this works.

Sample code

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction theTran = conn.BeginTransaction();
try
{
// create command object
// set Command Parameters
// Set Command Parameters values
// EXECUTE Stored Procedure 1

// create command object
// set Command Parameters
// Set Command Parameters values
// EXECUTE Stored Procedure 2

....

theTran.Commit();
}
catch
{
theTran.Rollback();
}
conn.Close();
}

Stored Procedure

Create Procedure InsABC
@NAME varchar(50),
@Address varchar(100)
as
BEGIN
BEGIN TRANSACTION
....
-- Do INSERT ...
....

-- In case of error rollback

...
COMMIT TRANSACTION

END

S Sansanwal25-Aug-04 16:33

S Sansanwal

25-Aug-04 16:33

Why don't you create a single StoreProcedure that is called by client.
Inside this SP, call all other procedures.

Sanjay Sansanwal
www.sansanwal.com

Sanjeev Kumar25-Aug-04 16:46

25-Aug-04 16:46

No, I can't combine all the SP, some times same SP is called multiple times depending on Data. Its all dynamic based on data which comes from client application

Colin Angus Mackay25-Aug-04 22:34

25-Aug-04 22:34

This isn't a problem. You can nest transactions inside each other. Just make sure that they all get committed or rolledback properly otherwise you might get errors saying that the transaction count before starting some SP is 1 and afterwards is 2 (because you forgot to commit or rollback the transaction before the SP exited)

Sanjeev Kumar26-Aug-04 7:07

26-Aug-04 7:07

Thanks Colin,
I have written an application just as proof of concept and it works fine. But, my boss think its not safe to do like this and he refering to Online Book Which say :-
----------------------
Specifying Transaction Boundaries
You can identify when SQL Server transactions start and end with Transact-SQL statements or API functions and methods.

Transact-SQL statements

Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command prompt utility.

API functions and methods

Database APIs such as ODBC, OLE DB, and ADO contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server application.

Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not start a transaction using the ODBC API functions, and then use the Transact-SQL COMMIT statement to complete the transaction. This would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.--------------

As per this document I am not suppose to mix transaction between ADO.NET and T-SQL. And I am not mixing transactions instead, I am nesting it.
Is there is any published article or document online which can help me explaning to my BOSS?

Thanks
Sanjeev

Colin Angus Mackay26-Aug-04 8:45

26-Aug-04 8:45

What the book is saying, and you've already realised, is that if you start a transaction in ADO.NET you must complete it in ADO.NET. If you start a transaction in T-SQL you must complete it in T-SQL. You can of course nest transactions inside each other.

For example (in this pseudo code)

Start transaction 1
  Start transaction 2
    -- Do stuff
  End transaction 2
End transaction 1

The above is perfectly fine. Transaction 1 and 2 can both be in T-SQL, or both be in ADO.NET or Transaction 1 can be in ADO.NET with Transaction 2 being in T-SQL.

Think of it like loops, you can start a for loop and call into another method that perhaps has another for loop inside it. The for loop in the inner method must complete before the for loop in the outer method can complete. Does this make sense?

As for a published article... I am not sure about that. The text you've quoted seems perfectly reasonable to me. How competant is your boss? (Hmmm... maybe that is a bad question) How technical is your boss? Could he understand it in a way similar to the for loop analogy? If he is less technical, how about a Russian doll analogy? (The Russian dolls that are hollow and one fits inside the other, they split at the waist so you can enclose one doll around another. Pretend that the base is a start transaction, the head is the end transaction, and each size of doll is a different technology. You must obviously match the correct base with the correct head otherwise they won't fit together properly.)

Does this help?

Sanjeev Kumar26-Aug-04 13:59

26-Aug-04 13:59

Thanks for the reply
I Hope my boss will understand this.

Colin Angus Mackay27-Aug-04 6:07

Parse sql query without executing

27-Aug-04 6:07

Good Luck! Big Grin | :-D

tocheng00525-Aug-04 11:42

tocheng005

25-Aug-04 11:42

Hi All,
Is there any way that I can parse and validate a SQL query (like the Parse Query function in Query Analyzer of SQL Server) before actually executing it in .NET code? Thanks a lot!

Tony Cheng

Re: Parse sql query without executing

S Sansanwal25-Aug-04 16:31

S Sansanwal

25-Aug-04 16:31

Execute SET PARSEONLY ON
Test your syntax and then
Execute SET PARSEONLY OFF

Sanjay Sansanwal
www.sansanwal.com

Re: Parse sql query without executing

tocheng00526-Aug-04 12:07

tocheng005

26-Aug-04 12:07

Thanks a lot, Sanjay.

Tony

DataSet with GUIDs

mikasa25-Aug-04 11:21

mikasa

25-Aug-04 11:21

I have a DataTable with a PKey defined as a GUID. The problem is trying to "lookup" values in the DataTable.Select Method. Since this is a GUID column, I cannot do the normal things like I could in SQL Server (i.e. DataTable.Select("ID = '" & sID & "'")). In SQL, the GUID DataType is treated like a String.

I generated an XSD Schema of my table, and I got this info for the PKey:

xs:element name="ID" msdata:DataType="System.Guid, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xs:string"

How can I get my DataTable to treat my PKey like a String at run time without loading aa Schema first? Or, how can I lookup GUID values in a DataTable??? OMG | :OMG:

Re: DataSet with GUIDs

mikasa27-Aug-04 3:05

mikasa

27-Aug-04 3:05

Ok, for some unknown reason, this now works when I know before it didn't WTF | :WTF:

Dealing with special characters in SQL queries

david@mindplay.com25-Aug-04 10:36

david@mindplay.com

25-Aug-04 10:36

Alright, I'm about ready to yank what's left of my hair out. Hopefully someone out there has the answer to my problem. Here it is…

I want to allow users to type any damn fool thing they want into a text field and then store that string in a database. I don’t like the idea of disallowing certain characters. I’d like to figure a way to store any string in the database regardless of what silly characters the user inputs.

I’ve done quite a bit of looking around on the internet for the answer to this problem. Surprisingly no one seems to have a good answer. I thought this would be easy and that I would find a viable answer right away. I have to admit that I am a little disappointed with the development community as a whole right now. For shame!

Anyway, I figured out how to deal with apostrophes and “single-qoutes”. ‘ this character whatever you call it anyway. If you simply double-up an apostrophe in a SQL query, for example: the string “Mr. O’Connell” would be changed to “Mr. O’’Connell”. This seems to work fine. Here’s the problem that’s killing me, what if the string was “Mr. O”Connell”, now what do you do. Obviously, it would be stupid to use a quotation mark it this way, but I want to allow users to indulge their stupidity. How in the heck do you put that in a SQL query without invoking a database error? Also, what about other special characters like: \ @ # $ % and so on. What if the string was something like this “Mr. “\@#$’%”. There has got to be an answer.

Not that it matters a whole lot, but the application is written in VB.Net and I am querying against a MS Access database. Access is just for now, when the application is finished users will have the option of several different providers such as: Access, SQL Server or My SQL.

Please Help

Re: Dealing with special characters in SQL queries

Colin Angus Mackay25-Aug-04 11:13