|
See EXECUTE in SQL Server Online Book.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Actually, you don't EXECUTE functions. You do that to Stored Procedures.
"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!
|
|
|
|
|
According to Sql Server online book ,it does that too.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Are you sure? I've checked the books online and I cannot see anything about running functions with the EXECUTE COMMAND. I also created a sample, on the Northwind database, to try out.
CREATE FUNCTION dbo.AllProducts()
RETURNS TABLE
AS
RETURN(SELECT * FROM PRODUCTS)
Then I tried to execute it like this:
EXECUTE dbo.AllProducts
I got an error message
Server: Msg 2809, Level 18, State 1, Line 1
The request for procedure 'AllProducts' failed because 'AllProducts' is a function object.
"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!
|
|
|
|
|
Its very strange,I have not test it myself before but this is from my Online Book for EXECUTE command:
Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
To run a User Defined Function (or UDF) you need to do something like:
SELECT * FROM dbo.MyFunctionName()
"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!
|
|
|
|
|
Depends on the type of function, that is, what it returns. If it is a value you can just use:
set @MyValue = MyFunction(MyParameter)
If it returns a table, you would probably use it in a FROM or where you would normally specify a table.
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!"
|
|
|
|
|
I use ado.net do something to mdb database. I found out the mdb database's size was been increased when it run some time.I can't confirm what it is cause.
It is my class,
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace RoadLib
{
///
/// NdnDataBase の概要の説明です。
///
public class NdnDataBase
{
protected string m_strConnectionString;
private OleDbConnection m_connection = null;
private OleDbTransaction m_transaction = null;
///
/// NdnDataBase の概要説明です
///
public NdnDataBase()
{
//
// TODO: コンストラクタ ロジックをここに追加してください。
//
m_connection = null;
}
///
/// NdnDataBase の概要説明です
///
///
public NdnDataBase(string strConnectionString)
{
m_strConnectionString = strConnectionString;
m_connection = null;
}
///
/// DBコネクションを取得します
///
public OleDbConnection connection
{
get { return m_connection; }
}
///
/// DBトランザクションを取得します
///
public OleDbTransaction transaction
{
get { return m_transaction; }
}
///
/// データベースを開くために使用接続する
///
public string connectionString
{
get { return m_strConnectionString; }
set { m_strConnectionString = value;}
}
///
/// 指定のデータベースでセッションを開く
///
///
public OleDbConnection DBOpen()
{
if(m_connection == null)
{
try
{
//データベース接続パラメータを取得
if (m_strConnectionString == "")
{
return null;
}
m_connection = new OleDbConnection(m_strConnectionString);
//DBオープン
m_connection.Open();
}
catch(Exception err)
{
//NdnPublicFunction.WriteLog(m_strLogFileName, err.Message);
m_connection = null;
throw (err);
}
}
return m_connection;
}
///
/// データベースでセッションを閉じる
///
///
public void DBClose()
{
try
{
//DBクローズ
if (m_connection != null)
{
m_connection.Close();
}
}
catch
{
}
finally
{
//初期値を設定
m_transaction = null;
m_connection = null;
}
}
///
/// SQLステートメントを実行
///
///
///
///
///
public bool ExecSql(string strCommandText, OleDbConnection connection, OleDbTransaction transaction)
{
bool bReturn = false;
OleDbCommand dbCommand = new OleDbCommand(strCommandText, connection, transaction);
try
{
dbCommand.ExecuteNonQuery();
bReturn = true;
}
catch( Exception err)
{
throw (err);
}
return bReturn;
}
///
/// データベースでトランザクションを開始します
///
///
public bool BeginTrans()
{
bool bReturn = false;
try
{
if (m_connection != null)
{
m_transaction = m_connection.BeginTransaction();
bReturn = true;
}
}
catch (Exception err)
{
throw err;
}
return bReturn;
}
///
/// データベースでトランザクションをコミットします
///
///
public bool CommitTrans()
{
bool bReturn = false;
try
{
if (m_transaction != null)
{
m_transaction.Commit();
bReturn = true;
}
}
catch (Exception err)
{
throw err;
}
finally
{
m_transaction = null;
}
return bReturn;
}
///
/// データベースでトランザクションをロールバックします
///
///
public bool RollbackTrans()
{
bool bReturn = false;
try
{
if (m_transaction != null)
{
m_transaction.Rollback();
bReturn = true;
}
}
catch (Exception err)
{
throw err;
}
finally
{
m_transaction = null;
}
return bReturn;
}
}
}
I use it to operate mdb database.
for example:
NdnDataBase database = new NdnDataBase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nara.mdb;User Id=adminassword=;");
try
{
database.DBOpen();
database.BeginTrans();
database.ExecSql("Delete from ttt");
...
database.ExecSql("Insert into ttt values(1, 2)", database.connection, database.transaction);
database.CommitTrans();
...
}
catch
{
database.RollbackTrans();
}
finally
{
database.DBClose();
}
it operation is in a timer event.
At last when the mdb database's size be to some size, the database is bad.
|
|
|
|
|
I'm not reading throught all that code. You have to be more specific.
While scrolling, however, I did notice that you are sending commands to the database that modify the contents of the database. So of course the file is going to expand. Typically databases files only ever expand until you explicitly run some sort fo compression or free space removal command on them.
"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!
|
|
|
|
|
Im just reading an ms press book about ado.net
here it says
"if teh connection is ".closed" the connection is returned to the pool and ready for reuse..
if the connection is ".disposed" the object is deleted from memory and cannot be reused by the pool"
is this really true?
i thought it was just the .net object wrapping the native connection that was killed and removed when disposing.
isn the actual pooling handled by oledb internally or something?
//Roger
|
|
|
|
|
IIRC the connection pool is internal to your ASP.NET application, not the database engines (some database engines don't support native pooling anyway). So if you dispose of a connection it will be released from the pool. If you just close the connection, it just gets stored in the pool until something wants a connection with the same connection string. This is much quicker than actually going back to the server and requesting a connection from there.
"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!
|
|
|
|
|
|
Which means that your MS Press book is wrong. I never said I was right, I just said "If I Recalled Correctly"
"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!
|
|
|
|
|
Sorry, I did not read this earlier.. Just wrote up a tip about it yesterday as someone else asked me the question about a week ago. I did not remember correctly and did some research. Come to the same conclusion as the thread here. Many people have argued that the dispose actually would not allow it to be used in the connection pool. Who is right and wrong? In the use net groups there were people who worked on the code that said absolutely that there was little difference. About the only thing the dispose did that close did not was delete the connection string.
Here is more info along with preventing a leak:
http://www.hintsandtips.com/ShowTip/129/hat.aspx[^]
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!"
|
|
|
|
|
Hi. I have a table with a primary key that I'd like to be an identity, so I don't worry about giving it a value manually (just increases automatically. 1, 2, 3, ...)
The problem is that I have another table where these key is a foreign key, so in my application, I must fill the value manually to the second table, but then I need to get the value in the first table.
I mean, imagine I have a table with: (id, name, age), where 'id' is the primary key and is identity, with values 1,2,...
Then I have another table with: (id, phone), where 'id' is the foreign key.
In my application, I can fill entries in the first table without giving values to id, as it is done automatically, but in the second table, I must give manually the values. How can I do that?
Should I make a query to get the 'id'?
Regards,
Diego F.
|
|
|
|
|
You can get an the last inserted identity (on SQL Server) by calling
select @@identity
You can then insert this into your second table.
store your internet favourites online - www.my-faves.co.uk
|
|
|
|
|
Thanks. I was searching about this and I found also SCOPE_IDENTITY(). Apart from using one or the other, how can I get the value in my application.
I mean, I saw people using: "Select @value = scope_identity()", but where do I catch the @value variable?
Regards,
Diego F.
|
|
|
|
|
If you do a select @@identity, you get the results into your application exactly the sameway that you do with any other select statement.
With select @@identity, you do not need to select the value into a variable.
store your internet favourites online - www.my-faves.co.uk
|
|
|
|
|
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();
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!
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
