Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL - SELECT statement Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2006 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 10, 2010 11:42 AM
Points: 3, Visits: 50

Dear All.

I am receiving this error message "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" to  the statement below :

declare @tamanho as varchar(10)
SET @tamanho= (select substring(name,1,30) as Banco,
               CAST( ( FILEPROPERTY( name, 'SpaceUsed' ) * 8 ) / 1024 AS numeric( 10, 2) )
               FROM sysfiles)
go

Please, somebody can help...

Thanks,

Flavio Bendl

 

 

Post #254769
Posted Tuesday, January 31, 2006 1:52 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028

Well,

If you execute this code alone, how many records are returned?

select substring(name,1,30) as Banco,
               CAST( ( FILEPROPERTY( name, 'SpaceUsed' ) *  ) / 1024 AS numeric( 10, 2) )
FROM sysfiles

More than 1.

So which one do you want to use to set the variable to?

 

Post #254784
Posted Tuesday, January 31, 2006 2:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:44 AM
Points: 1,148, Visits: 81

IANAD, (I am not a developer) but see if this helps:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_11_9nw3.asp

 

 





My hovercraft is full of eels.
Post #254787
Posted Wednesday, February 1, 2006 1:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:36 PM
Points: 631, Visits: 236

Try:

declare @tamanho varchar(10)
select @tamanho=substring(name,1,10),
       --CAST((FILEPROPERTY(name, 'SpaceUsed')* 8)/1024 AS numeric(10,2))
FROM sysfiles

SELECT @tamanho

or:

declare @tamanho varchar(10)
select @tamanho=
       --substring(name,1,30) as Banco,
       CONVERT(varchar(10),CAST((FILEPROPERTY(name, 'SpaceUsed')* 8)/1024 AS numeric(10,2)))
FROM sysfiles

SELECT @tamanho

or:

declare @tamanho numeric(10,2), @banco varchar(30)
select @banco=substring(name,1,30),
       @tamanho=CAST((FILEPROPERTY(name, 'SpaceUsed')* 8)/1024 AS numeric(10,2))
FROM sysfiles

SELECT @banco, @tamanho

Andy




Post #254866
Posted Wednesday, February 1, 2006 10:39 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:24 AM
Points: 685, Visits: 104
i think the question we should be asking Flavio is 'What do you want to do?' before we suggest any solution


Everything you can imagine is real.

Post #255013
Posted Wednesday, February 1, 2006 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 10, 2010 11:42 AM
Points: 3, Visits: 50

Dear All,

 

I need to make a monthly control  on disk space on SQL 2000 Server. I have 3 servers.

I thought to create a table and to insert the SELECT result in table.

 

I don’t know very well SQL 2000 and DBA procedures.

If somebody knows some procedure to make it, please help me.

 

*****Replay to David A. Long

Your select is returning only one record (Log file) . I would like seeing all records this table, but, it´s very good.

Thank you

Post #255047
Posted Wednesday, February 1, 2006 1:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:44 AM
Points: 1,148, Visits: 81

See if this article helps. 

http://www.sqlservercentral.com/columnists/mnash/monitoringdriveanddatabasefreespace.asp





My hovercraft is full of eels.
Post #255066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse