[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Capture two variables

Hi i have the follwoing that returns
two columns
Im trying to capture these as two variables rather than output


declare @l_db_name       varchar(50)
SET @l_db_name = 'TESTTC'
set @retType = N'@cnt varchar(20) OUTPUT'
set @retType = N'@cnt varchar(20) OUTPUT'
set @cmd  = N'select ceiling((size * 8192)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then
            @CNT = convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
                   + 'else @CNT =  convert (varchar, growth) + '' Percent''' + char(10)+char(13)
                  + 'end' + char(10)+char(13)
                         + 'from ' + @l_db_name + '.dbo.sysfiles'

set @retType = N'@cnt varchar(20) OUTPUT'

exec sp_executesql @cmd, @retType OUTPUT
--, @retVal OUTPUT
0
TRACEYMARY
Asked:
TRACEYMARY
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

declare @l_db_name       varchar(50)
SET @l_db_name = 'TESTTC'
set @retType = N'@cnt varchar(20) OUTPUT'

set @cmd  = N'select ceiling((size * 8192)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
          convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
                 + 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
               + 'end' + char(10)+char(13)
                         + 'from ' + @l_db_name + '.dbo.sysfiles'
set @retType = N'@cnt varchar(20) OUTPUT'

exec sp_executesql @cmd, @retType OUTPUT
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

declare @l_db_name       varchar(50)
declare @retType nvarchar(200)
declare @cnt varchar(20)
declare @ceil decimal (20,4)

SET @l_db_name = 'TESTTC'
set @retType = N'@cnt varchar(20) OUTPUT, @ceil decimal (20,4) '

set @cmd  = N'select @ceil = ceiling((size * 8192)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
          convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
                 + 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
               + 'end' + char(10)+char(13)
                         + 'from ' + @l_db_name + '.dbo.sysfiles'

exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT
0
 
LowfatspreadCommented:
you can get the "two" columns like this...

declare @SQL varchar(8000)
declare @l_db_name sysname
set @l_db_name='MINE'

create table #temp (size integer, growth varchar(30),fileid int)

Set @SQL = 'insert into #temp (size,growth,fileid) select ceiling((size * 8192)/(1024.0 * 1024.0)), '
          +'case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB'''
          +'      else convert (varchar, growth) + '' Percent'''
          +' end,fileid from '+@l_db_name+'.dbo.sysfiles '

Exec(@sql)

declare @sz int
declare @growth varchar(30)

select @sz=size,@growth=growth
 from #temp

print @sz
print @growth

--drop table #temp



however is that what you want?

the table probably has at least 2 rows in it (one per file in your database)

what are you trying to do with the variables afterwards...
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
TRACEYMARYAuthor Commented:
You little stars.....
I was also going to give up and write to tables then come back again get results then do calculations...that would have taken me all day..

Im ...still on my database usage analysis...this is the final steps...phew ...3 weeks later lol.

Cheers
0
 
TRACEYMARYAuthor Commented:
Hey i tried to just capture the results in the end to a table.

--EXEC SYSTEMADM.sp_DBInfo


CREATE TABLE [DBINFO] (
      [ROWID] [int] IDENTITY (1, 1) NOT NULL ,
      [SERVERNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DBNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DATA_NAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LOG_NAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DATA_FILENAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LOG_FILENAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DATA_GROWTH] [int] NULL ,
      [LOG_GROWTH] [int] NULL ,
      [CREATED_DATE] [datetime] NULL CONSTRAINT [DF_DSTATS_INFO_CREATED_DATE] DEFAULT (getdate()),
      [DATA_SIZE_PAGES] [int] NULL ,
      [LOG_SIZE_PAGES] [int] NULL ,
      [DATA_GROWTH_STATUS] [int] NULL ,
      [LOG_GROWTH_STATUS] [int] NULL
) ON [PRIMARY]
GO




ALTER procedure SYSTEMADM.sp_DBInfo
as
SELECT CONVERT(varchar(40), NEWID()) 'rowid', *
INTO #temp
FROM master.dbo.sysdatabases

DECLARE @rowid varchar(40)
DECLARE @DBNAME nvarchar(50)
DECLARE @DATA_GROWTH int
DECLARE @LOG_GROWTH int
DECLARE @DATA_NAME nvarchar(100)
DECLARE @LOG_NAME nvarchar(100)
DECLARE @LOG_FILENAME nvarchar(100)
DECLARE @DATA_FILENAME nvarchar(100)
DECLARE @SERVERNAME nvarchar(100)
DECLARE @LOG_SIZE_PAGES int
DECLARE @LOG_GROWTH_STATUS int
DECLARE @DATA_SIZE_PAGES int
DECLARE @DATA_GROWTH_STATUS int



SET @SERVERNAME = 'BSGLABDB'
-- Get first row
SELECT @rowid = MIN(rowid)
FROM #temp

WHILE @rowid IS NOT NULL
BEGIN
      SET @DBNAME = ''
      SET @DATA_GROWTH = 0

   -- Do something with the row data
   SELECT @DBNAME = name
   FROM #temp
   WHERE rowid = @rowid

        


--get mdf information
      SELECT @DATA_GROWTH = C.GROWTH ,
             @DATA_NAME = C.NAME,
             @DATA_FILENAME = C.FILENAME,
             @DATA_SIZE_PAGES = B.SIZE,
             @DATA_GROWTH_STATUS = B.STATUS
            FROM MASTER.DBO.SYSDATABASES  A
      INNER JOIN SYSFILES B
      ON B.FILEID = 1
      INNER JOIN MASTER.dbo.sysaltfiles C
      ON C.DBID = A.DBID AND C.FILEID = 1
      WHERE A.NAME = @DBNAME

--get ldf information

--Insert into DB INFO
      SELECT @LOG_GROWTH = C.GROWTH,
             @LOG_NAME = C.NAME,
             @LOG_FILENAME = C.FILENAME,
             @LOG_SIZE_PAGES = B.SIZE,
             @LOG_GROWTH_STATUS = B.STATUS
             FROM MASTER.DBO.SYSDATABASES  A
      INNER JOIN SYSFILES B
      ON B.FILEID = 1
      INNER JOIN MASTER.dbo.sysaltfiles C
      ON C.DBID = A.DBID AND C.FILEID = 2
      WHERE A.NAME = @DBNAME


      IF NOT EXISTS
            (SELECT * FROM SYSTEMADM.DBINFO
            WHERE SYSTEMADM.DBINFO.DBNAME = @DBNAME AND
                  SYSTEMADM.DBINFO.SERVERNAME = @SERVERNAME)
            BEGIN
                  
                  INSERT INTO SYSTEMADM.DBINFO(SERVERNAME, DBNAME, DATA_NAME, LOG_NAME, DATA_FILENAME, LOG_FILENAME, DATA_GROWTH, LOG_GROWTH, DATA_SIZE_PAGES, LOG_SIZE_PAGES, DATA_GROWTH_STATUS, LOG_GROWTH_STATUS)
                        VALUES
                        (@SERVERNAME, @DBNAME, @DATA_NAME, @LOG_NAME, @DATA_FILENAME, @LOG_FILENAME, @DATA_GROWTH, @LOG_GROWTH, @DATA_SIZE_PAGES, @LOG_SIZE_PAGES, @DATA_GROWTH_STATUS, @LOG_GROWTH_STATUS)
            END
      
      IF EXISTS       (SELECT * FROM SYSTEMADM.DBINFO
            WHERE SYSTEMADM.DBINFO.DBNAME = @DBNAME AND
                  SYSTEMADM.DBINFO.SERVERNAME = @SERVERNAME)
            
            BEGIN
                  UPDATE SYSTEMADM.DBINFO
                        SET DATA_NAME = @DATA_NAME,
                              LOG_NAME = @LOG_NAME,
                            DATA_FILENAME = @DATA_FILENAME,
                            LOG_FILENAME = @LOG_FILENAME,
                            DATA_GROWTH = @DATA_GROWTH,
                            LOG_GROWTH = @LOG_GROWTH,
                            DATA_SIZE_PAGES = @DATA_SIZE_PAGES,
                            LOG_SIZE_PAGES = @LOG_SIZE_PAGES,
                            DATA_GROWTH_STATUS = @DATA_GROWTH_STATUS,
                            LOG_GROWTH_STATUS = @LOG_GROWTH_STATUS
      
                  WHERE SYSTEMADM.DBINFO.DBNAME = 'DEVUFCP' AND
                  SYSTEMADM.DBINFO.SERVERNAME = @SERVERNAME
            END

      
            




   SELECT @rowid = MIN(rowid)
   FROM #temp
   WHERE rowid > @rowid
END

drop table #temp







--SELECT * FROM SYSTEMADM.DBINFO



Everything works great except the status and the size are all the same through out the databases..?

GO
0
 
TRACEYMARYAuthor Commented:
oh i got it

 SELECT @DATA_GROWTH = C.GROWTH ,
            @DATA_NAME = C.NAME,
            @DATA_FILENAME = C.FILENAME,
            @DATA_SIZE_PAGES = C.SIZE,
            @DATA_GROWTH_STATUS = C.STATUS
          FROM MASTER.DBO.SYSDATABASES  A
--     INNER JOIN SYSFILES B
--     ON B.FILEID = 1
     INNER JOIN MASTER.dbo.sysaltfiles C
     ON C.DBID = A.DBID AND C.FILEID = 1
     WHERE A.NAME = @DBNAME

Can't read the sysfiles it be for the database your running it in ...MYDATABASE and not equal to the database
@DBNAME


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now