August 23, 2012 at 8:41 am
Hi SQL hero's
Help me with this query...
SELECT ServerName,dbname,
COUNT(dbname) AS Occurrences ---------> this value need to be casted to varchar
FROM XYZ
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
as I display this result into some Message string.....
I am getting error there ..... can anyone help me with it...
Sagar Sonawane
** Every DBA has his day!!:cool:
August 23, 2012 at 8:43 am
What error are you getting?
August 23, 2012 at 8:44 am
Would also help if you showed the code that is getting the error. From what I see, I don't the posted code having a problem.
August 23, 2012 at 8:55 am
May be misunderstanding as you've not given much to work with.
Do you mean you want to display the SELECT results in one string?
SELECT ServerName + ' ' + dbname + ' ' + CAST(COUNT(dbname) AS VARCHAR (100)) AS Occurrences
FROM XYZ
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
August 23, 2012 at 8:58 am
If you group by Server & DB, COUNT() is always 1 (as far as I can see).
How about this:
create table XYZ
(
ServerName varchar(10),
dbname varchar(10)
);
insert into XYZ values ( 'server1', 'db1' );
insert into XYZ values ( 'server1', 'db2' );
insert into XYZ values ( 'server1', 'db3' );
insert into XYZ values ( 'server2', 'db4' );
insert into XYZ values ( 'server2', 'db5' );
SELECT ServerName,--dbname,
--COUNT(dbname) AS Occurrences, ---------> this value need to be casted to varchar
CAST(COUNT(dbname) as varchar(10)) as Char_Occ
FROM XYZ
GROUP BY ServerName--,DBName
HAVING ( COUNT(DBName) > 1 )
You should be able to CAST the value without any problem...
August 23, 2012 at 10:08 am
Hi All,
sorry for late reply.....
Laurie it worked out.... tons of thanks!!!!
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy