|
Hi All,
table
-----
NAme Age Total
---- --- ------
Ram 26 800
kumar 36 300
Sam 34 200
output should be
------
Name:Ram Age:26 Total:800 ;Name:Kumar Age:36 Total:300;Name.....
Please help me with this .tried using COALESCE
but I could not achieve..
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
You can do it like this...
DECLARE @String VARCHAR(8000)
set @String = ''
SELECT @String = @String + 'Name:' + ISNULL(Name,'NULL') + ' Age:' + Age + ' Total:' + Total + '; '
FROM TABLE
print @String
|
|
|
|
|
Why would you want the data in that format? If you happen to have 1000 records, would want all that data on one line?
|
|
|
|
|
Hi....
Try This....
DECLARE @Temp TABLE(Name VARCHAR(20),Age TINYINT, Total INT)
INSERT INTO @Temp(Name,Age,Total)
SELECT 'Ram',26,800
UNION ALL
SELECT 'kumar',36,300
UNION ALL
SELECT 'Sam',34,200
DECLARE @SQLStr VARCHAR(8000)
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + 'Name:' + Name + ' Age:' + CAST(Age AS VARCHAR) + ' Total:' + CAST(Total AS VARCHAR) + '; '
FROM @Temp
PRINT @SQLStr
|
|
|
|
|
Im problem is like this.
Im getting these exceptions when running my application.
"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)"
after this exception
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
In mssql server machine SQl server browser is also enabled and firewall is also disabled.
Please help.
Reply·Email·Vi
|
|
|
|
|
Hi,
Please enable the remote connection option..
|
|
|
|
|
|
hi,
i'm trying to create a program like notepad using visual basic.
all of text body inside my note will be save in one cell in Sql Server Management Studio R2, but the max of varchar is varchar(8000), and there's varchar(MAX) too.
nah if varchar(MAX) is bigger than 8000, may i know how many character it could save?
i'm trying to understand this because i don't want any error caused by character overload.
thankyou before. =)
|
|
|
|
|
Instead of typing the question in here you could have done a search[^] on the keywords and caroused the results, they look quite useful to me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
wow, thankyou for sharing that link.
pretty cool way to tell someone to search..
|
|
|
|
|
Actually it's rather nasty but it does get the point across. There are guidelines at the top which give you the same information in a more polite fashion. You, like the rest of us, obviously don't read the manual and just dived right in, most newbies get introduced to LMGTFY.
Anyway welcome to the site, may your questions be clear and interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
btw, varchar(max) indicates that the maximum storage size is 2^31-1 bytes
is that means 2,147,483,648 - 1 become 2,147,483,647 characters?
and then i don't have to worry about character overload anymore?
|
|
|
|
|
I should think that will be adequate for a NotePad style application
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
okay, thankyou very much bro, god bless you..
|
|
|
|
|
vkstarry wrote: the max of varchar is varchar(8000)
Yup, anything longer than that would cripple the database-index when you do a LIKE operation.
To keep it simple; the documents contents should be a varchar(max) (or nvarchar if you need to store unicode), and you should use strings that are limited in length to describe other properties (like a title).
Bastard Programmer from Hell
|
|
|
|
|
I want to add one column in sql table.
That will be in specific format. The format is like that
Year+00001
Year+00002
....and so on. If the year changes to new year, I want to start from 00001. like that.
Year+00001
Year+00002
to be more clear what I need is: like below in applicationID column.
name | date | applicationID
Willam | 2011-12-03 | 201100001
Susan | 2011-12-04 | 201100002
Alex | 2012-01-01 | 201200001
Mata | 2013-01-01 | 201300001
how I have to do. &
please.
|
|
|
|
|
I suspect this is a BAD idea, I also suspect you are using this field as a primary key. If so the DON'T it is a BAD design.
To do this you will need to query the table to find out how many inserts there are for this year, build your string and insert the record. There are a number of alternatives to this process that include triggers and stored procedures.
Here is the reason it is a bad idea. What happens if another user inserts a record during this process, BOOM one busted database.
I suggest you insert the datetime for Created then query filtered on the year and ordered by the created date. If you need an incremental number you can use ROW_NUMBER() assuming you are using sql server
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi, I am not clear your answer...
ok, how about adding auto generated id(identity) column in my table..
I will use id column as primary key.
I will not use applicationID column as primary key.
for applicationID column, I just want to store in this format.
id | name | date | applicationID
1 | Willam | 2011-12-03 | 201100001
2 | Susan | 2011-12-04 | 201100002
3 | Alex | 2012-01-01 | 201200001
4 | Mata | 2013-01-01 | 201300001
pls.
|
|
|
|
|
Better design!
Then you can update the record after it is inserted to calculated the applicationid field.
You will need to pad the ROW_NUMBER()/MAX value with leading zeros and combine that with the year datepart.
This is a better option as the ApplicationID field that is a human consumed piece of info is not used to identify the record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I saw same problem like me.
but I didn't see best answer, like sample.
|
|
|
|
|
There is also a security reason for not doing this.
If someone sees that their id is "20110005" it is easy to guess that 20110004 and 20110006 are also valid.
|
|
|
|
|
sankooo wrote: I want to add one column in sql table.
That will be in specific format.
Mycroft is right, but it's half the story. Tables hold data, not information.
sankooo wrote: If the year changes to new year, I want to start from 00001. like that.
That's a composite column, and in the ideal world we try to keep the data atomic. That means that you can loose the date-part, as that's already encoded in the previous column. You also don't need the prefix-zeroes, those are merely there for presentation. Hence, all you need in the ApplicationID-row is a sequence-number; all other information is already present and the complete applicationID as you need can be calculated from the rest of the tuple.
The other half of the story is that we don't want to duplicate information, as that would be redundant (and thus, increases the chances for errors).
I'd suggest you read up on normalization[^] procedures.
Bastard Programmer from Hell
|
|
|
|
|
I am using this code to view a news headline from mysql table but I want also to add one more thing please... I want to update a field named news_views (int) with +1 everytime someone views this article.. how can I do this please...
<?php
$news_id = $_GET['id'];
$conn = mysql_connect('mysql.jassimrahma.com', 'jassimdb', 'xxxxxxx');
$sql = "SELECT * FROM news WHERE news_id = '$news_id'";
mysql_select_db("jassimrahma", $conn) or die(mysql_error());
$result = mysql_query($sql, $conn) or die(mysql_error());
$count = mysql_num_rows($result);
if($count == 1)
{
$db_field = mysql_fetch_assoc($result);
$news_url = $db_field['news_url'];
$news_title = $db_field['news_title'];
$news_webpage = file_get_contents($news_url);
echo $news_webpage;
}
else
{
header("location:index.php");
}
?>
|
|
|
|
|
I think you will get more response from the PHP forum.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|