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