OCP - Database Discussion Boards

How to get connection.schema("columns",restrictions) in order by ordinal position

ashish bhakhar20-Mar-08 0:58

ashish bhakhar

20-Mar-08 0:58

I want the columns of table in order of their ordinal position.

is it possible?

Re: How to get connection.schema("columns",restrictions) in order by ordinal position

Mark J. Miller20-Mar-08 11:06

Mark J. Miller

20-Mar-08 11:06

exec sp_columns '[table name]'

If that's too much info then you could try:

SQL 2000
SELECT o.name, c.name as columnname FROM syscolumns c
INNER JOIN sysobjects o ON o.id = c.id
WHERE o.[name] = 'reports'
ORDER BY c.colorder

SQL 2005
SELECT O.name, C.name as columnname FROM sys.columns C
INNER JOIN sys.objects O ON O.object_id = C.object_id
WHERE O.[name] = 'reports'
ORDER BY c.column_id

Mark's blog: developMENTALmadness.blogspot.com

Re: How to get connection.schema("columns",restrictions) in order by ordinal position

ashish bhakhar20-Mar-08 19:57

ashish bhakhar

20-Mar-08 19:57

Thank You Mark. It Works. Well Done.

data tables for a job site

AbhishekAgarwal1519-Mar-08 22:04

AbhishekAgarwal15

19-Mar-08 22:04

i m planning to create a job site,so plz guide me for design the database,tables in sqlserver 2005

Re: data tables for a job site

Ashfield19-Mar-08 22:43

Ashfield

19-Mar-08 22:43

If you can't design the tables are you sure you are competent enough to create a complete web site?

Try reading up on the subject rather than asking someone to do your work for you.

Bob
Ashfield Consultants Ltd

Re: data tables for a job site

Pete O'Hanlon22-Mar-08 9:31

Pete O'Hanlon

22-Mar-08 9:31

AbhishekAgarwal15 wrote:
so plz guide me for design the database,tables in sqlserver 2005

Well - I'd start by gathering requirements, working out what fields need to be in the database and then normalize it to at least third normal form. Here's a technical hint for you - consider investing in some paper and pencils for your design.

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

My blog | My articles

Re: data tables for a job site

KrisnNala25-Mar-08 9:09

KrisnNala

25-Mar-08 9:09

You need to do alot of planning, investigation and searching (on the web). If you don't know how to design the database and tables I agree with the other person find out for yourself. You'll get alot more satisfaction if you've done the work yourself. Trust me I know! Big Grin | :-D

Kris MCP

OCP

KambojR19-Mar-08 21:56

KambojR

19-Mar-08 21:56

Hi All,

I wanted to go for the OCP. Can any one please let me know How many exams I have to give? Which are the exams and the fees structure? And from where I can give this exam and how to pay the fees of the exams. I wanted to do OCP in 9i or 11i.

Thanks,
RK

Re: OCP

Ashfield20-Mar-08 3:20

Ashfield

20-Mar-08 3:20

Have you tried looking on the Oracle web site? I bet they have loads of information about OCP Sigh | :sigh:

Bob
Ashfield Consultants Ltd

how do I use sql triggers to replicate update changes across two different databases

GDMFSOB19-Mar-08 20:56

GDMFSOB

19-Mar-08 20:56

Hi, I have the following problem,

I have 2 different databases for 2 seperate systems I some of the data in db 2 is needed in db 1, I want to create an sql trigger for ms sql 2000 that will run when ever an update happens in db 2 the fields that I want in db 1 from db 2 must update with the changes made in db 2.

So my problem is in the trigger how to I get the values that where updated into the update stament I want to send to the other database

CREATE TRIGGER tr_table_UPDATE
ON dbo.vehicle_db1.Vechicle_list
FOR UPDATE
AS

Update dbo.vehicle_db2.Vechicles set Registration_No = 'PYS 789 GP', Model = 'Data from the original update' where ID = 'id from original update'

I hope this makes sense, in a way its kinda replication.
Thank in adavance.

Re: how do I use sql triggers to replicate update changes across two different databases

andyharman20-Mar-08 0:26

andyharman

20-Mar-08 0:26

If the databases are on the same server then there is no need to replicate the tables because you can reference tables using:

select * from <database>.<owner>.<table>

If the databases are on separate servers, but have reliable connectivity then consider using "linked servers" (look them up in BOL).

Otherwise consider using "replication" (look it up in BOL).

Hope that helps.
Andy

Re: how do I use sql triggers to replicate update changes across two different databases

GDMFSOB20-Mar-08 1:58

GDMFSOB

20-Mar-08 1:58

thanks man, but thats not what I need, i will have redesign the entire package if I am to go that route this is supposed to be a quick fix, I know its not ideal, but I figured it out about an hour ago thanks.

looping in SQL Server2005

salon19-Mar-08 20:32

salon

19-Mar-08 20:32

I have a table having columns like values, like this

col1 col2 col3
1000 10 2
1000 20 3
1000 30 4
2000 10 5
2000 20 6
2000 30 7
3000 10 8
3000 20 9
3000 30 10

I want to fetch records like if i enter col2 as 20 and col1 as 1500 then
with
"SELECT col3 FROM table1 WHERE col2 = @col2"

I will get 3 rows having respective records of value 20 for col2(col2 values are fixed)
but col1 values are variable i.e. can fall into different ranges like 1400,1500,2300 etc.
but col2 values are fixed

I want to return only one row for eg. col2 as 20 and col1 as 1500

then
1000 20 3
this row should get returned

How can i do this?

I don't know how to use cursors...

Re: looping in SQL Server2005

N a v a n e e t h19-Mar-08 20:44

N a v a n e e t h

19-Mar-08 20:44

salon wrote:
I don't know how to use cursors...

Any problem for searching and learning how it works ?

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

Re: looping in SQL Server2005

salon19-Mar-08 21:00

salon

19-Mar-08 21:00

No issues to learn...

I am just asking is there anyone have idea to do it in optimized way?

as I am trying

col1 have variable inputs like 1200,1300,2100 etc...
and col2 have fixed inputs

I am doing like this calculating min and max values for col1

and trying to put col2 in that range
but till I m not able to achieve...

Re: looping in SQL Server2005

Tobias Schoenig20-Mar-08 0:26

Tobias Schoenig

20-Mar-08 0:26

There's no problem in using a cursor within a while loop -> have a look at the MSDN, to review the example and you'll have your're problem solved.

how to change the date format

mon***z19-Mar-08 19:08

mon***z

19-Mar-08 19:08

i have a date format in 06/01/2008 which datatype is nvarchar(50).how i will change it into 06/jan/2008 format in sql. Plz help me.

select log_in_date from test1
out_put
06/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
06/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
07/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
08/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
09/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
10/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
11/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
12/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
13/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
14/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
15/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
16/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
17/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
18/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
19/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
20/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
21/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
22/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
23/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
24/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
25/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
26/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
27/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
28/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
29/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
30/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008
31/01/2008

Re: how to change the date format

N a v a n e e t h19-Mar-08 20:46

N a v a n e e t h

19-Mar-08 20:46

mon***z wrote:
nvarchar(50).

I am not sure about NVarchar, but if you were using DateTime type, use convert function to change the display format.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

Re: how to change the date format

Vimalsoft(Pty) Ltd19-Mar-08 21:27

Vimalsoft(Pty) Ltd

19-Mar-08 21:27

thanks man for your answer

I missed the namespace

<br />
  Messagebox.show("Invalid Data Entry");<br />
<br />

ANd the message Messagebox, does not Exists in the Current Context

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: how to change the date format

Ashfield19-Mar-08 22:57

Ashfield

19-Mar-08 22:57

Well, a couple of sample dates would have done rather than dozens, but your answer is:

Using @a to represent your column

<br />
declare @a nvarchar(50)<br />
set @a = '28/10/2008'<br />
set dateformat dmy<br />
select convert(varchar,datepart(day,@a)) + '/' + left(datename(month,@a),3) + '/' + convert(varchar,datepart(year,@a))

You need the set dateformat dmy so SQL knows its day month year

Bob
Ashfield Consultants Ltd

multiplying columns

neoghy19-Mar-08 18:46

neoghy

19-Mar-08 18:46

actually i have five columns like system,name,qty,weight,area

I have to fetch the details from database. I have to write a query where i have to multiply qty and weight and put it in weight column and at the same time i have to display the records in the descending order of total weight . How can this be done. Please help

skd

Re: multiplying columns

N a v a n e e t h19-Mar-08 20:53

N a v a n e e t h

19-Mar-08 20:53

It's a home work question, how about this

SELECT 
     [system]
    ,[name]
    ,[qty] * [weight] As TotalWeight
FROM 
	[dbo].[TestTable]
ORDER BY 
	TotalWeight DESC

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

Generic data fetch with IDataReader

RubensFarias19-Mar-08 16:38

RubensFarias

19-Mar-08 16:38

Hi there,

I'm looking for some advise for a generic data fetch pattern for IDataReader I'm working on, as follow:

// main load
using(GenericDataReader reader = new GenericDataReader(cm.ExecuteReader()))
{
while (reader.Read())
{
orders.Add(reader.Get<Order>()); // "GetList" pattern
//return new Order(reader); // "Get" pattern
}
}

// generic reader; interface implementation removed
public class GenericDataReader : IDataReader
{
// ... \\

public T Get<T>(string name)
{
int i = _dataReader.GetOrdinal(name);
if (_dataReader.IsDBNull(i))
return default(T);
else
return (T)_dataReader.GetValue(i);
}

public T Get<T>() where T : ILoadable, new()
{
T entity = new T();
entity.Load(this, false);
return entity;
}
}

// All business objects must implements this interface
public interface ILoadable
{
void Load(GenericDataReader reader, bool deep);
}

// Sample business object
class Order : ILoadable
{
int id;
Customer customer; // Note
DateTime orderDate;
DateTime requiredDate;
DateTime? shippedDate;
List<OrderDetail> orderDetails;

public Order(){ }

public Order(GenericDataReader reader)
{
Load(reader, true);
}

#region ILoadable Members

public void Load(GenericDataReader reader, bool deep)
{
id = reader.Get<int>("OrderID");
customer = reader.Get<Customer>(); // Note
orderDate = reader.Get<DateTime>("OrderDate");
requiredDate = reader.Get<DateTime>("OrderRequiredDate");
shippedDate = reader.Get<DateTime?>("OrderShippedDate");
if (deep && reader.NextResult())
{
orderDetails = new List<OrderDetail>();
while (reader.Read())
{
orderDetails.Add(reader.Get<OrderDetail>());
}
}
}

#endregion
}

What do you think? Are there any major drawback with this model I'm missing? Are there any more clever way to do that?

I really appreciate your time reading this. Thanks in advance,

Rubens

Re: Generic data fetch with IDataReader

Syed Mehroz Alam20-Mar-08 20:17

Syed Mehroz Alam

20-Mar-08 20:17

Seems a nice idea. Just when you are done, try to post a article on CodeProject.

Good Luck,
Mehroz

Re: Generic data fetch with IDataReader

RubensFarias23-Mar-08 12:54

RubensFarias

23-Mar-08 12:54

Hi Mehroz,

I'm working on some model limitations and, when I ready to go, I'll post @ CP, for sure.

Thanks for your incentive,

Rubens

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