Re: I need the quiery for the following please help me out - Database Discussion Boards

Re: TEXT datatype

andyharman24-Jan-07 0:28

andyharman

24-Jan-07 0:28

I don't have access to SQL-Server 2005 from this machine, but you should be able to create tables like:

create table MyTable(
  Id int identity(1,1) not null,
  MyBigColumn varchar(max) null
  )

I believe that Microsoft are recommending use of "varchar(max)" instead of the "text" data-type.

Regards
Andy

Re: TEXT datatype

quiteSmart24-Jan-07 0:36

quiteSmart

24-Jan-07 0:36

andyharman wrote:
MyBigColumn varchar(max) null

I read on MSDN that i can do this, but when trying it; it doesn't work

i don't have any clue why that is happening

Re: TEXT datatype

andyharman24-Jan-07 2:17

andyharman

24-Jan-07 2:17

What error message do you get?

Re: TEXT datatype

quiteSmart24-Jan-07 2:24

quiteSmart

24-Jan-07 2:24

andyharman wrote:
What error message do you get?

Syntax Error newr 'max'

Re: TEXT datatype

Colin Angus Mackay23-Jan-07 23:25

Colin Angus Mackay

23-Jan-07 23:25

quiteSmart wrote:
i have read an article that says that i can use the VARCHAR to arround 2 billion characters.

If you are using SQL Server 2005 (I forgot about that - I'm not yet using SQL Server 2005)

Upcoming events:
* Glasgow Geek Dinner (5th March)
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Also

Ennis Ray Lynch, Jr.24-Jan-07 3:49

Ennis Ray Lynch, Jr.

24-Jan-07 3:49

BLOB data is stored separately from the record in the underlying data structure by the RDBMS

File Not Found

I need the quiery for the following please help me out

MD1223-Jan-07 16:27

MD12

23-Jan-07 16:27

Respected sir,

I have some of the questions related to sql queries
1.what is the sql server query for having the 3rd max salary record from the following table

EmpId Empname EmpSal
1 Joseph 1000
2 rahul 299
3 Rahim 4000
4 John 350
5 kate 2000

2.what is the sql server query for arraning the records with latest date for the following table

EmpId Empname EmpSal Date
1 Joseph 1000 21may2005
2 rahul 299 12 june2004
3 Rahim 4000 3may2006
4 John 350 5april2001
5 kate 2000 8june1998

3.how to sort the particular coloumn with out affecting to other coloums like

Empnam empSal
Joseph 1000
rahul 299
Rahim 4000
John 350
kate 2000

the above table has to be translated as given below in which only the salary is sorted in descending order with out disturbing empnames

Empname Empsal
joseph 4000
rahul 2000
rahim 1000
john 350
kate 299

4.In stored procedures how to jump to required line,
for example we have 1 stored procedure in which it contains 20 lines of code,but during accessing if we want to execute 8th line directly jumping from 2line of code.how to make it possible

please suggest me
Thanking you in anticipation

Frown | :(

Re: I need the quiery for the following please help me out

Colin Angus Mackay23-Jan-07 22:29

Colin Angus Mackay

23-Jan-07 22:29

MD12 wrote:
what is the sql server query for having the 3rd max salary record from the following table

SELECT TOP 1 FROM (SELECT TOP 3 FROM MyTable ORDER BY Salary ASC) ORDER BY Salary DESC

MD12 wrote:
what is the sql server query for arraning the records with latest date for the following table

It depends what you mean by "arraging the records with the latest date"

SELECT * FROM MyTable ORDER BY SomeDateColumn

MD12 wrote:
3.how to sort the particular coloumn with out affecting to other coloums like

I have never come across a situation where that was useful.

MD12 wrote:
4.In stored procedures how to jump to required line

Use GOTO - or better would be to create a proper conditional structure with an IF block.

Upcoming events:
* Glasgow Geek Dinner (5th March)
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Flattening a property table

mr_lasseter23-Jan-07 8:10

mr_lasseter

23-Jan-07 8:10

I have a the following table structure

t_entity
entity_id
connect_date
disconnect_date

t_entity_info
entity_id
property_name
value

Let's say I have the following data in the tables
t_entity
1 | 1/1/2005 | 12/1/2005
2 | 1/1/2006 | 12/1/2006

t_entity_info
1 | application | ds3
1 | subapplicaiton | wan
2 | application | voice
2 | subapplicaiton | phone

I want the following results with one query (using Oracle)

1 | 1/1/2005 | 12/1/2005 | ds3 | wan
2 | 1/1/2006 | 12/1/2006 | voice | phone

Anyone got any ideas? Thanks in advance.

Mike Lasseter

SQL server 2000 OR 2005

sanaziuse23-Jan-07 6:43

sanaziuse

23-Jan-07 6:43

Hi all .
I wanna know ... :
1. What is the difference between Sqlserver2000 and SqlServer2005 ??
2. Which one is more useful ?
3. Which one works better and is more compatible with Asp.net2003 ?

Thank you very Much. Smile | :)

Re: SQL server 2000 OR 2005

Aaron VanWieren23-Jan-07 7:26

Aaron VanWieren

23-Jan-07 7:26

"4"
Sorry, could not help myself.

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

The Enlightenment Project (paraphrased comment)

Re: SQL server 2000 OR 2005

Chris Meech23-Jan-07 7:31

Chris Meech

23-Jan-07 7:31

My calculator gives 5, but I have my doubts.

Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]

Re: SQL server 2000 OR 2005

Aaron VanWieren23-Jan-07 7:48

Aaron VanWieren

23-Jan-07 7:48

Calculated starting from zero. OOps. That only works if you are worried about a context out of range error! Big Grin | :-D

Any suggestions for this guy. I prefer 2005 because of SMO and SQL Server Express is free. Plus, nifty serialization and inside out CLR.

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

The Enlightenment Project (paraphrased comment)

Re: SQL server 2000 OR 2005

andyharman23-Jan-07 22:53

andyharman

23-Jan-07 22:53

1. See "http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx[^]".
2. I would go for SQL Server 2005 because it will be supported longer. I would mainly only consider SQL Server 2000 if you already had licences.
3. Both should work the same from .NET 2003. NET 2005 would definately favour SQL Server 2005.

Regards
Andy

How to populate a C++ listbox with data from MS Access

hzhengyang23-Jan-07 5:29

hzhengyang

23-Jan-07 5:29

Hi,

I have a table Main with 3 fields: ID, Name, and Info.
I created a listbox, but how do i populate it using with data from this query.... "select distinct Name from Main"
Below is the portion of my code.

***************
OleDbDataAdapter* da;
OleDbConnection* odc;
DataSet* ds;

odc = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\Main.mdb");
da = new OleDbDataAdapter("SELECT DISTINCT Name FROM Main",odc);
ds = new DataSet();
da->Fill(ds,"Main");

//stuck... don't know how to proceed from here.

please help..thanks!!!!

Regarding Triggers

Syed Muhammad Kamran23-Jan-07 3:48

Syed Muhammad Kamran

23-Jan-07 3:48

I am developing an sms messaging app (Let say app A) with backend in SQL2K. This app depends on a schema of an already deployed application (Let say app X). I have also created a schema of my own application which may reside on any other machine.

Whenever app X done a transaction on some of the tables I am initiating a trigger from there. After doing some operations on the inserted record in the trigger I am inserting that record to my own schema generated for App A by the link server to my Schema.

I have made a windows service, whenever a new rocord is inserted in my tables through schema I'll compose and send an SMS message to my client.

My problem is since triggers runs in an implicit transaction whenever the inserting triggers fails the transaction which was inserted by App X is also got failed. Which I don't want. How to cope with this... I don't know about error handling. How to implement fail save recovery in this scenario.

SMK

Loops in ERD

mytotaleclipse23-Jan-07 3:31

mytotaleclipse

23-Jan-07 3:31

Hi All,

I want to know whether loops in ERDs are wrong or show weak design or not.
Example: if table A is connected to table B & table B is connected to C & C is connected to A..
Is this wrong??
Actually the ERD is larger than that & the alternative is to make multiple joins when retreiving data so I want to know which is better.
Thanks a lot

Re: Loops in ERD

Colin Angus Mackay23-Jan-07 3:41

Colin Angus Mackay

23-Jan-07 3:41

mytotaleclipse wrote:
want to know whether loops in ERDs are wrong or show weak design or not

No, they are not wrong.

Upcoming events:
* Glasgow Geek Dinner (5th March)
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Re: Loops in ERD

Colin Angus Mackay23-Jan-07 3:43

Colin Angus Mackay

23-Jan-07 3:43

The tightest "loop" (relationship) you can have is when a table joins onto itself - which is perfectly valid.

e.g. An employee has a manager, a manager is an employee also. So the managerID column of the employee table refers to the ID of another employee.

Upcoming events:
* Glasgow Geek Dinner (5th March)
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Search All Columns of a Table for a Keyword

Jay_se23-Jan-07 2:14

Jay_se

23-Jan-07 2:14

Hi,

How to search all columns of a table in a database for a keyword?
I am using ASP.NET 2.0 and Sql Server 2000. Give me the logic behind this operation?

Thanks,

Regards,

Regards,

Jay Smile | :)

Re: Search All Columns of a Table for a Keyword

Aaron VanWieren23-Jan-07 3:57

Aaron VanWieren

23-Jan-07 3:57

Not sure exactly what you are trying to do, but if you need to look for a particular value and if you are using datatables you could loop through your column names and in each loop run a datatable.select command, storing the values in an array.

More information would be helpful.

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

The Enlightenment Project (paraphrased comment)

GISDevCafe.com

Alter table query for Sql Server

Saira Tanwir22-Jan-07 23:07

Saira Tanwir

22-Jan-07 23:07

hi guys

can anyone plz tell me the syntax for the following queries

1.Alter Table name
2.Alter Column name
3.Alter Column dataType
4.Alter Primary/Foreign/Unique constraints of a Column

plz tell queries that work in Sql Server.

these are the queries that i am using for the first two tasks and they don't work
1.
string sql="ALTER TABLE "+comboBox2.Text+" TO "+new_tn;
2.
string sql="ALTER TABLE "+comboBox2.Text+" RENAME COLUMN "+new_tn;

regards

Re: Alter table query for Sql Server

andyharman23-Jan-07 0:33

andyharman

23-Jan-07 0:33

The syntax for SQL Server 2000 is:
1. EXEC sp_rename 'old_table_name', 'new_table_name'
2. EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN'
3. ALTER TABLE table_name ALTER COLUMN column_name new_data_type
4. Lookup "ALTER TABLE" in "SQL Server Books Online".

Regards
Andy

Re: Alter table query for Sql Server

Saira Tanwir23-Jan-07 0:44

Saira Tanwir

23-Jan-07 0:44

thank you

regards Rose | [Rose]

Saira

Developing complex SQL Queries

Praveen_S22-Jan-07 19:09

Praveen_S

22-Jan-07 19:09

I want examples of some complex sql queries.

such as there could be some 2 - 3 tables as Purchase , Order and Order Details etc..

and there would be some 50 questions asked with answers i.e sql queries

pls suggested me some links that would contains above mentioned example.

Thanks in advance

Praveen

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