Re: is it recommended to have SQL2005 Express along with MSSQL server 2005 - Database Discussion Boards

Care Career19-Apr-07 1:50

19-Apr-07 1:50

Hi Stored Procedurs r used to perform single task using mutiple tables.

I know only definition ,Can anyone plz tell me how to create dtored procedue with proper syntax with any example.

Thanks

Care Career

Re: What is Stored Procedure?

kubben19-Apr-07 1:58

kubben

19-Apr-07 1:58

http://msdn2.microsoft.com/en-us/library/ms187926.aspx[^]

Hope that helps.
Ben

Re: What is Stored Procedure?

Mike Dimmick19-Apr-07 2:28

Mike Dimmick

19-Apr-07 2:28

Most simply put, a stored procedure is any sequence of program code that executes against a database, and is stored in that database. A single SELECT statement is still a valid stored procedure, in many implementations.

I believe the SQL standard says that a stored procedure is introduced with the keywords CREATE PROCEDURE, but that's about it. Different databases have their own extended dialects of SQL to support looping and branching, and some may support stored procedures in other languages entirely.

SQL Server 2000 supports stored procedures in Transact-SQL, and SQL Server 2005 adds .NET-based 'stored procedures' written in C# and VB.NET. Oracle supports PL-SQL, which is their extended SQL syntax taking cues from the Ada programming language.

SQL Server supports sending multi-statement commands in a single batch operation. The entire batch is parsed and an execution plan formed and potentially cached before the whole thing is executed. You don't need a stored procedure for this. You can still use named parameters in a plain-text query batch, rather than calling a stored procedure. However, in SQL Server, an execution plan for a stored procedure will live longer in the cache than one for a so-called ad-hoc query.

Users can be granted access to execute a stored procedure but not to directly modify or query the tables used by that procedure. In a system I've implemented, the user account that the application logs in as can only execute the stored procedures - it cannot manipulate any of the tables or views. This means that even if it were to be hacked or the login compromised, it still can't do anything that the application couldn't do anyway.

Stability. What an interesting concept. -- Chris Maunder

Sql Database Shrink Wizard

S Anand19-Apr-07 1:46

S Anand

19-Apr-07 1:46

Dear Folks,

I am new to this forum, S Anand, From Chennai INDIA
There was a very nice tool that was available "Sql Transaction Log Shrink Wizard", is there a similar tool for shrinking the database too?

Regards, Anand

Re: Sql Database Shrink Wizard

Mike Dimmick19-Apr-07 3:01

Mike Dimmick

19-Apr-07 3:01

What's wrong with Enterprise Manager? Right-click the database in the tree, select All Tasks, Shrink Database. For SQL Server 2005 Management Studio, right-click the database, select Tasks, Shrink, Database.

On a real production system you would normally dedicate disks (or RAID arrays) to a specific database's data files or transaction log, and therefore set the files to occupy about 95% or so of the space on the drive/array, and turn off automatic growth.

If you don't intend to make transaction log backups, set the database's Recovery Model to Simple. Otherwise, transaction log space is only reused once the log has been backed up - if you're not backing it up, it will continue to grow. The Full recovery model allows you to restore to the point of failure if the database files or disks containing them are unrecoverable, but the transaction logs are still available, and you have a full database backup.

Stability. What an interesting concept. -- Chris Maunder

Re: Sql Database Shrink Wizard

S Anand21-Apr-07 2:01

S Anand

21-Apr-07 2:01

HI Mike,

I dont find anything worng with Enterpriese Manager, however the logs that is getting truncated using the tool is of greater advanantge. The DBCC Shrink command dosent shrink to a greater size and that is why my request was. Thanks for your response i got it.

Anand

sangramkp19-Apr-07 1:44

sangramkp

19-Apr-07 1:44

Often I face problems while connecting Sql Server2005

Re: is it recommended to have SQL2005 Express along with MSSQL server 2005

kubben19-Apr-07 1:59

kubben

19-Apr-07 1:59

No one sql server is enough on your PC. If you installed something like visual studio on your PC it will auto install sql express. Then if you installed sql server 2005 after you would have both on your box. I would remove sql express if you have sql server 2005 installed.

Ben

Re: is it recommended to have SQL2005 Express along with MSSQL server 2005

Aaron VanWieren19-Apr-07 8:06

Aaron VanWieren

19-Apr-07 8:06

What problems are you having? I have been running both for a while now and have run into few problems. They are both treated as separate servers.

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

SQL Query

Care Career19-Apr-07 1:41

Care Career

19-Apr-07 1:41

Hi
I have one table Employee having fields id,Name,Salary (id is primary Key)
I have another table Phone having fields id,phone.(id is forign key)

1)using these two tables I want to get all the details of Employee who does not have phone Nos.

2)& I want details of all employees who have more than 2 phone nos.

Plz send these queries as soon as possible.

Thanks

Care Career

Re: SQL Query

kubben19-Apr-07 2:02

kubben

19-Apr-07 2:02

1) Select e.* from employee e
left join Phone p on e.id = p.id
where p.id is null

2)
select e.id,p.phone from employee e
join phone p on e.id = p.id
group by e.id, p.phone
having count(*) > 1

You should probably buy a good sql book.

Ben

Insert Date using oledbcommand

cocoonwls19-Apr-07 0:18

cocoonwls

19-Apr-07 0:18

Hi,

In my app, I would like to insert data into MS Access. In my databse, have 3 columns-(datetime,datetime,text).Below is my code

strInsert = "INSERT INTO Alarms(Date,Time,description)VALUES ('"+dateTimePicker1.Value.Date+"', '"+dateTimePicker1.Value.Date+"' , 'err orlog')";
:
:
conx.open();
OleDbCommand cmd1 = new OleDbCommand(strInsert, conx);
cmd1.ExecuteNonQuery();
conx.close();

I am getting this error message - "OleDbException was unhandled". I am not idea what was wrong.Can anyone guide me to solve this error.

thanks in advance

cocoonwls

Re: Insert Date using oledbcommand

Kschuler19-Apr-07 10:27

Kschuler

19-Apr-07 10:27

First you may want to figure out which line is cauing the error. If it is the conx.open() line then you have a problem with the connection. If it is the ExecuteNonQuery line then it is probably teh SQL. Is there more to teh error message? I've never inserted a datetime object before, but you may want to lookup the format it needs to be in and whether or not you need the single quotes around it.

Hope this helps.

--------------------------------------------------
Play Nation States and join The Code Project region with the password: byalmightybob

Re: Insert Date using oledbcommand

cocoonwls19-Apr-07 15:52

cocoonwls

19-Apr-07 15:52

Hi,
Thanks for your reply.I have found the mistake.The Date value should put "#" symbol infront and after the variable, as below:

strInsert = "INSERT INTO Alarms (DateIn, TimeIn, Description) VALUES (#" +dateTimePicker1.Value+ "#,#" +dateTimePicker1.Value+ "#,'e')"

regards
cocoonwls

Select count and select

N a v a n e e t h18-Apr-07 22:37

N a v a n e e t h

18-Apr-07 22:37

Select count(*) From TableName
Select count(column) From TableName
Select * From TableName

In these queries which will work more fast ? What could be the reason ?

printf("Navaneeth!!")

www.w3hearts.com

Re: Select count and select

Pete O'Hanlon18-Apr-07 22:50

Pete O'Hanlon

18-Apr-07 22:50

DooDooo wrote:
In these queries which will work more fast ?

Try them out to find out. Once you've run them, have a think about what each one is actually doing and how many rows they are returning. Hint - try the second count on a nullable column.

Deja View - the feeling that you've seen this post before.

Re: Select count and select

Colin Angus Mackay18-Apr-07 22:56

Colin Angus Mackay

18-Apr-07 22:56

DooDooo wrote:
In these queries which will work more fast ? What could be the reason ?

They all do different things. It is an invalid comparison.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Select count and select

N a v a n e e t h19-Apr-07 1:22

N a v a n e e t h

19-Apr-07 1:22

Ya I know all three queries are doing different things. What I mean to say was my friend told that select count(*) will take much time that executing select * from table. Because first it will select all data from table and finds the count. I just wanted to know whether this is rite or not ?

printf("Navaneeth!!")

www.w3hearts.com

Re: Select count and select

Colin Angus Mackay19-Apr-07 1:46

Colin Angus Mackay

19-Apr-07 1:46

DooDooo wrote:
What I mean to say was my friend told that select count(*) will take much time that executing select * from table. Because first it will select all data from table and finds the count. I just wanted to know whether this is rite or not ?

You also have to take in to consideration network bandwidth used. SELECT COUNT(*) FROM MyTable returns a single value. SELECT * FROM MyTable returns the entire contents of the table.

Also, I'd guess the COUNT(*) would be able to take into account an index so it wouldn't actually have to read every single row, just one of the indexes (which would be a lot less data)

Re: Select count and select

N a v a n e e t h19-Apr-07 2:58

N a v a n e e t h

19-Apr-07 2:58

Thank you very much

printf("Navaneeth!!")

www.w3hearts.com

data moving

tradakad18-Apr-07 22:16

tradakad

18-Apr-07 22:16

Hi, All.

example: I have two databases. mySQL and MS SQL. I have to load some data from MySQL into MS SQL.
I can't use SSIS or something like that because my version of MS SQL Server hasn't it. so I must write my pwn programm foor this task.

I write two classes - first fro working with MySQL and second for MS SQL.

----------
MySQL
----------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;

public class MySqlClass
{
public MySqlClass()
{
//
// TODO: Add constructor logic here
//
}

static public MySqlConnection myConnection = null;

public string GetConnection(string con_str)
{
string connection_string = string.Empty;

if (con_str.Length == 0)
connection_string = System.Configuration.ConfigurationManager.ConnectionStrings[2].ToString();
else
connection_string = con_str;

try
{
myConnection = new MySqlConnection(connection_string);
myConnection.Open();
}
catch (MySqlException err)
{
return "Erron in connection :" + err.Message;
}

finally
{
if (myConnection != null)
myConnection.Close();
}

return null;
}

public DataSet MySqlOpen(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();

MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql_cmd, myConnection);
DataSet myDataSet = new DataSet();

myDataAdapter.Fill(myDataSet);

myConnection.Close();

return myDataSet;

}

public void MySqlRun(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();
MySqlCommand cmd = new MySqlCommand(sql_cmd, myConnection);
cmd.ExecuteNonQuery();
myConnection.Close();

}

public void MySqlRunTransaction(string[] queries)
{
MySqlTransaction trans = null;

if (myConnection != null)
myConnection.Close();

try
{
myConnection.Open();

trans = myConnection.BeginTransaction();

MySqlCommand cmd = new MySqlCommand();

cmd.Connection = myConnection;
cmd.Transaction = trans;

foreach (string query in queries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (MySqlException SqlErr)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("An error occured while transaction", SqlErr);
//return;
}
finally
{
if (myConnection != null)
{
myConnection.Close();
}
}
}

}

----------

----------
MS SQL
----------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class SqlClass
{
public SqlClass()
{
//
// TODO: Add constructor logic here
//
}

static public SqlConnection myConnection = null;

public string GetConnection(string con_str)
{

string connection_string = string.Empty;

if (con_str.Length == 0)
connection_string = System.Configuration.ConfigurationManager.ConnectionStrings[3].ToString();
else
connection_string = con_str;

try
{
myConnection = new SqlConnection(connection_string);
myConnection.Open();

}
catch (SqlException err)
{
return "Erron in connection :" + err.Message;
}

finally
{
if (myConnection != null)
myConnection.Close();
}

return null;
}

public DataSet SqlOpen(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();

SqlDataAdapter myDataAdapter = new SqlDataAdapter(sql_cmd, myConnection);
DataSet myDataSet = new DataSet();

myDataAdapter.Fill(myDataSet);

myConnection.Close();

return myDataSet;

}

public void SqlRun(string sql_cmd)
{
if (myConnection != null)
myConnection.Close();

myConnection.Open();
SqlCommand cmd = new SqlCommand(sql_cmd, myConnection);
cmd.ExecuteNonQuery();
myConnection.Close();

}

public void SqlRunTransaction(string[] queries)
{
SqlTransaction trans = null;

if (myConnection != null)
myConnection.Close();

try
{
myConnection.Open();

trans = myConnection.BeginTransaction();

SqlCommand cmd = new SqlCommand();

cmd.Connection = myConnection;
cmd.Transaction = trans;

foreach (string query in queries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (SqlException SqlErr)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("An error occured while transaction", SqlErr);
//return;
}
finally
{
if (myConnection != null)
{
myConnection.Close();
}
}
}

}

----------

so in my main unit i declared them

public MySqlClass mys = new MySqlClass();
public SqlClass ms = new SqlClass();

and when I wanna get result from MySQL i do -

DataSet myDataSet = new DataSet();

try
{
myDataSet = mys.MySqlOpen(txtMySQLQuery.Text);
}
catch (MySqlException err)
{
Response.Write(err.ToString());
}

so I have a dataset with data (now myDataSet contains result of my query isn't ?)

my question - HOW can I load a data from this dataset into another database and table? i think I should use something like dataadapter or dataset but i'm not sure. and can I use ms.SqlRun for this (i declared it before) or I should use some another way?

thanx a lot!!!

--
"Success is simple. Do what's right, the right way, at the right time."

best regards,
tradakad

Re: data moving

Colin Angus Mackay18-Apr-07 22:25

Colin Angus Mackay

18-Apr-07 22:25

tradakad wrote:
I can't use SSIS or something like that because my version of MS SQL Server hasn't it.

SSIS is just a rebranded version of DTS. Use DTS.

Re: data moving

tradakad18-Apr-07 22:47

tradakad

18-Apr-07 22:47

I haven't got it too Frown | :(

can i FREE download it? i mean for my MS SQL Server Express Edition version?

thanx!

--
"Success is simple. Do what's right, the right way, at the right time."

best regards,
tradakad

Re: data moving

Mike Dimmick19-Apr-07 3:17

Mike Dimmick

19-Apr-07 3:17

I'm fairly sure that's not true, otherwise why would there be a separate download for the legacy DTS runtime? SSIS is effectively the next version of DTS (in development and Beta it was still called DTS and the process is called MsDtsServer.exe), but is not fully compatible with 2000's DTS.

Yes, if targetting SQL Server 2000 and optionally 2005, use DTS. Otherwise use SSIS.

SQL Server 2005 Express Edition doesn't have SSIS. That's only included in Standard and Enterprise Editions (and of course Developer Edition which is Enterprise with a restricted licence, the capability to run on client Windows operating systems, and autoshrink turned on). DTS was also not present in MSDE 2000.

Stability. What an interesting concept. -- Chris Maunder

Re: data moving

Colin Angus Mackay19-Apr-07 3:25

Colin Angus Mackay

19-Apr-07 3:25

Mike Dimmick wrote:
I'm fairly sure that's not true, otherwise why would there be a separate download for the legacy DTS runtime?

It is a rebranded DTS. As you pointed out they rebranded it for SQL Server 2005 half way through the development cycle - hence there are references to DTS in the code.

Mike Dimmick wrote:
SQL Server 2005 Express Edition doesn't have SSIS. That's only included in Standard and Enterprise Editions (and of course Developer Edition which is Enterprise with a restricted licence, the capability to run on client Windows operating systems, and autoshrink turned on). DTS was also not present in MSDE 2000.

True. I made an assumption that it was one of the full server or developer products that was being used.

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