Database

Re: not sure of advantages of functions over stored procedures

26-Jun-06 8:25

J4amieC wrote:
Live and learn

Absolutely - the day we stop learning is the day we die.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Colin Angus Mackay23-Jun-06 3:48

checking for existence of server and SQL instance

23-Jun-06 3:48

sishya wrote:
Can anyone tell me the advantages of functions over stored procedures and vice versa and i am not shoor if i can use system ftions like getdate() in functions
pls help me out

You cannot use functions such as GETDATE() because they are non-deterministic. A function must have a deterministic operation. i.e. Given the same inputs it will always produce the same output.

I wouldn't go around and convert stored procedures to functions just because.

I wouldn't say functions or stored procedures have any avantages over each other. They are designed for different uses.

A function can return data to calling code more easily that a stored procedure can. A stored procedure can perform data modification actions (INSERT/UPDATE/DELETE) that a function can't.

Talal Sultan23-Jun-06 0:27

Talal Sultan

23-Jun-06 0:27

I'm writing a small C# windows application that will create a new database and populate it with tables, views and stored procedures. Of course I am using SQL scripts that I'm launching from the windows application. I ask the user to enter 2 fields, the server name and the instance name of the SQL server.

My question is: is there a way to check if the entered server name and instance exist?

Since I launch the scripts by starting a process to run osql, I don't know if I get some return value to tell me that the server was not found. So basically even if the server name is wrong, the application says that the operation is completed, although nothing has been done.

Thanks,
Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Re: checking for existence of server and SQL instance

Colin Angus Mackay23-Jun-06 1:08

Re: checking for existence of server and SQL instance

23-Jun-06 1:08

Talal Sultan wrote:
My question is: is there a way to check if the entered server name and instance exist?

You could always create a SqlConnection and attempt to Open the master database on the connection. If it fails you get an exception (although it may also fail for insufficient rights, but if you're creating a new database you're going to need them anyway). If it works you can Close the connection in the knowledge that the server exists.

Talal Sultan23-Jun-06 3:15

Talal Sultan

23-Jun-06 3:15

Thanks, I'll try that out Smile | :)

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Using Centralized Database MS SQL Server2000

Varun Arora22-Jun-06 23:20

Varun Arora

22-Jun-06 23:20

Hi All,

I have setup of my vb application running on a computer with backend sqlserver2000 on same computer. I want that I can run my application on many computers which are networked with the same central database. Can any one please suggest me what parameters should I have to keep in my mind or simply how can I achieve this?

I will be very thankful.

Varun Arora,
Software Engineer,
Knowledge Calls,
Gurgaon, India

Re: Using Centralized Database MS SQL Server2000

Colin Angus Mackay23-Jun-06 1:04

23-Jun-06 1:04

Varun_soft11 wrote:
Can any one please suggest me what parameters should I have to keep in my mind or simply how can I achieve this?

Change your connection string to point to the central database rather than the local one. There isn't much else to it.

data from database

rcshek22-Jun-06 15:12

rcshek

22-Jun-06 15:12

It might be a silly question
I want to retrieve the data when some action is performed
Actually,when the page is loaded default data loads in to the data grid
when the combo box Item is selected then I want to retreive the data related to that particular condition
Do i need to create one more data adapter and data set
If not How can i do that
can any one of u guys tell me

Thanks

Re: data from database

VK-Cadec23-Jun-06 8:25

VK-Cadec

23-Jun-06 8:25

Store all the information in one dataset. When the combo box is created you can select out of this existing dataset based in conditions given - maybe selecting only one type of products codes based on selected value.

query

Tara1422-Jun-06 10:57

Tara14

22-Jun-06 10:57

Hi,
I have a table like this:

Param    Cat    Pay
----    ----    ---------
bugj    home    rent
bugj    home    repair
budj    home    something1
budj    car     something2
budj    car     somethg3
budj    tax     somethg4

My progam has to search the field catand list all the categories. i.e home,car,tax

Plese can you tell me if there is any particular sql which eliminates repititions?

Thanks

Fortitudine Vinsinues!

Re: query

Eric Dahlvang22-Jun-06 11:35

Eric Dahlvang

22-Jun-06 11:35

SELECT DISTINCT Cat FROM TableName

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: query

Tara1422-Jun-06 19:11

Tara14

22-Jun-06 19:11

Thank you.

Fortitudine Vinsinues!

Stored procedure

Nayota22-Jun-06 8:29

Nayota

22-Jun-06 8:29

Hello All,

I have a table that has 4 coloums of which one is primary key and is set to auto increment.
Is it possible to write a stored procedure that inserts value in the rest of the three coloumns and returns the value of the primary key that was generated automatically.

Re: Stored procedure

Dustin Metzgar22-Jun-06 9:23

Dustin Metzgar

22-Jun-06 9:23

I'm not a database expert, but nobody else answered your question so I'll give it a shot. It sounds like you're using Oracle or something because you said auto increment instead of index. But, in SQL Server, you can write an insert statement in your stored procedure like this:

insert into [table] 
(
	column1,
	column2,
	column3
)
values
( 
	@column1,
	@column2,
	@column3,
)
return SCOPE_IDENTITY()

That will return the value in the identity column.

In Oracle, you do it two ways. The first way is to have a sequence and get the next value of the sequence first using:

SELECT table_seq.NEXTVAL AS ID from DUAL

Then when you do your insert, use that ID in your insert.

The other way is to have a sequence and a trigger. Do your insert and then select the max value for that column out of the table and return it. The first way is probably better.

Any of you db gurus out there, please correct me if I'm wrong.

Logifusion[^]

Re: Stored procedure

Nayota22-Jun-06 10:22

Nayota

22-Jun-06 10:22

Thanks Dustin, that was fast. I am actually using SQL Server 2000 and I am sorry I forgot to mention.

Re: Stored procedure

jonathan1522-Jun-06 23:08

jonathan15

22-Jun-06 23:08

with SQL server its the same but you do
:

RETURN @@IDENTITY

Jon

Dataset update

kjosh22-Jun-06 6:10

kjosh

22-Jun-06 6:10

Hi,
What happens when we try to update a data in a dataset which is already deleted in sql server?

Thanks in advance

image

md_refay22-Jun-06 4:26

md_refay

22-Jun-06 4:26

i want to check column of type image in my database to see if image column in the selected row has image in it or it null?

ma_refay

Re: image

Colin Angus Mackay22-Jun-06 4:51

22-Jun-06 4:51

SELECT CASE WHEN ImageColumnName IS NULL THEN 0 ELSE 1 END FROM MyTable

sql server triggers

flippydeflippydebop22-Jun-06 3:59

22-Jun-06 3:59

Hello all,

using SQL server 2000 does anyone know a sql statment to get a list of trigger names that are associated with a table?

flippydeflippydebop22-Jun-06 4:19

22-Jun-06 4:19

just to clarify im looking for a way to list the defiend triggers of a particular table.. I have found examples for oracle and MySQL5.0 but nothing for SQL Server 200.

Oracle = select trigger_name from user_triggers;

MySQL = SELECT TRIGGER_NAME,ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_TABLE='TEST_TABLE';

flippydeflippydebop22-Jun-06 4:26

22-Jun-06 4:26

ahh dont worry...

ive found it.. it: exec sp_helptrigger table_name

flippydeflippydebop22-Jun-06 4:44

22-Jun-06 4:44

ahh.... actually im still having trouble.. while that works im still unable to use that select statement programatically... Does anyone know how i can do this?

flippydeflippydebop22-Jun-06 5:06