Re: Stored procedure - Database Discussion Boards

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.

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

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

Colin Angus Mackay

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

flippydeflippydebop

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?

Re: sql server triggers

flippydeflippydebop22-Jun-06 4:19

flippydeflippydebop

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';

Re: sql server triggers

flippydeflippydebop22-Jun-06 4:26

flippydeflippydebop

22-Jun-06 4:26

ahh dont worry...

ive found it.. it: exec sp_helptrigger table_name

Re: sql server triggers

flippydeflippydebop22-Jun-06 4:44

flippydeflippydebop

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?

Re: sql server triggers

flippydeflippydebop22-Jun-06 5:06

flippydeflippydebop

22-Jun-06 5:06

ahh dont worry... ive cracked it!!!

select name as 'Trigger', object_name(parent_obj) as 'Table' from sysobjects where xtype = 'TR' and object_name(parent_obj) = 'MY_TABLE_NAME'

where 'MY_TABLE_NAME' is the name of the table

inserting 10 rows in a table using a query

248912822-Jun-06 2:19

2489128

22-Jun-06 2:19

hi, i want to find the solution for the following sql query..

there is a table T(x,y,z)

i want to insert 10 records into table T using a sql query.

sam

Re: inserting 10 rows in a table using a query

Mairaaj Khan22-Jun-06 3:12

Mairaaj Khan

22-Jun-06 3:12

Nigam Samir wrote:
i want to insert 10 records into table T using a sql query.

You can use loops for this purpose either in Stored procedures or in front end application.

_____________________________
Success is not something to wait for, its something to work for.

Re: inserting 10 rows in a table using a query

Eric Dahlvang22-Jun-06 6:46

Eric Dahlvang

22-Jun-06 6:46

insert into T select top 10 'xval','yval','zval' from sysobjects

This will put 10 records (all the same) into the "T" table. I just used sysobjects becuase it is pretty much guarenteed to have at least 10 records in it.

If you want different values in the fields or records, you could use the random function.

There might be betters ways to do this though.

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

password?!!!

md_refay22-Jun-06 0:38

md_refay

22-Jun-06 0:38

i work in hr sqlserver database
in employees table i have password column so every one have permission to open database can see all the passwords i wnat toedit the column password so who open employyes table see symblos in password column not the real password

ma_refay

Re: password?!!!

Colin Angus Mackay22-Jun-06 1:00

Colin Angus Mackay

22-Jun-06 1:00

Do you mean you want to encrypt the passwords? Do you want to change the permissions so that only valid users can access the passwords?

Changing it so that it just displays symbols is not going to happen in SQL Server - Its function is to serve data, not to define how it is displayed. That is up to the applications that access the database. Perhaps you want to change them instead.

Re: password?!!!

r.stropek22-Jun-06 2:13

r.stropek

22-Jun-06 2:13

Hi!

Check my blog at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. There I show how you can encrypt and decrypt the password column with SQL2005.

If you do not have SQL2005 but 2000 you do not have these smart encryption functions built into the DB. You would have to implement encryption yourself (e. g. using .NET's encryption functions). In fact for a password it would be enough to calculate a (salted) hash (see http://en.wikipedia.org/wiki/Salt_%28cryptography%29 for details) -> no encryption needed.

A completly unsecure solution that just displays the password in binary instead of cleartext is to change the data type of the password column to varbinary. A SELECT will then just show hex values. Again, this is NOT encryption. Everyone how is able to write SQL can write cast(password as varchar) and sees the password. However, sometimes it is enough to just changing the display format of the password to binary.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Re: password?!!!

Colin Angus Mackay22-Jun-06 4:53

Colin Angus Mackay

22-Jun-06 4:53

r.stropek wrote:
However, sometimes it is enough to just changing the display format of the password to binary.

This is not a display format - it is a storage format. The database knows nothing about how to display the data, that is the function of the application that is connected to the database.

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