Error executing procedure that gives Space Aloc/Used/Free...

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  March 13, 2009 at 5:43 am

  #207479

  Hello,

  I've created one sp (with your help) to give me the, Total Space Used, Total Space Alocated and Total Free Space in one SQL Server 2K5 Instance.

  This procedure is working very well in all other instances that i've created. The only difference from this instance to all other is that this named instance has a lot of file groups and the space alocated is beside 2TB

  I send attached the file with the code of the sp (sp_GetSpaceAUX.txt) and the file were the error and the expected result appears after i execute the procedure sp_GetSpace (ErrorAUX.txt)

  Hope that you can help me

  Thanks and regards,

  JMSM 😉

• beezell

  Hall of Fame

  Points: 3112

  More actions

  March 13, 2009 at 6:11 am

  #959022

  Towards the end of your stored procedure there is a statement:

  "select @@servername as 'InstName', sum(a.size) as 'Space Aloc (mb)', sum(a.used) as 'Space Used (mb)', sum(a.size)-sum(a.used) as 'Free Space (mb)'

  from AuxSpaceTable"

  somehow the table being refered to as "a" was taken out. Replace it with this statement and it should work:

  "select @@servername as 'InstName', sum(a.size) as 'Space Aloc (mb)', sum(a.used) as 'Space Used (mb)', sum(a.size)-sum(a.used) as 'Free Space (mb)'

  from AuxSpaceTable a"

  Cheers,

  Brian

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  March 13, 2009 at 6:11 am

  #959023

  At first glance I see this, but it doesnt match your error message

  select @@servername as 'InstName', sum(a.size) as 'Space Aloc (mb)', sum(a.used) as 'Space Used (mb)', sum(a.size)-sum(a.used) as 'Free Space (mb)'

  from AuxSpaceTable

  if you use a.size, you should alias your AuxSpaceTable with a. How does it bind the column to AuxSpaceTable in your instances that this executes sucessfully in?

• beezell

  Hall of Fame

  Points: 3112

  More actions

  March 13, 2009 at 6:16 am

  #959025

  I executed the code (in SQL 2008) and the message I got refered to it as a.size, etc instead of the previous error. It maybe a version dependent thing? I didn't try it in 2000 or 2005 to see if it might be different. After adding the "a" it worked fine. Please post back to this if you are still having issues.

  Cheers,

  Brian

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  March 13, 2009 at 6:21 am

  #959029

  Hi there, the error was mine.

  The sp that i send you is not the correct one ,i've attached the one that is good and where the query is fine. (sp_GetSpaceAUX-Correct.txt)

  select @@servername as 'InstName', sum(size) as 'Space Aloc (mb)', sum(used) as 'Space Used (mb)', sum(size)-sum(used) as 'Free Space (mb)'

  from AuxSpaceTable

  Sorry :crying:

  Thanks and regards,

  JMSM 😉

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  March 13, 2009 at 6:37 am

  #959035

  Change

  insert into AuxSpaceTable select name,size,used,logsize,logspaceused from #dbs1;

  To

  insert into AuxSpaceTable select name,Size,Used,LogSize,LogSpaceUsed from #dbs1;

  You hit a case sensitive server.

  For your reference, you can verify with what your server collation is set to from here

  http://msdn.microsoft.com/en-us/library/ms144250(SQL.90).aspx

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  March 13, 2009 at 7:00 am

  #959058

  Thanks a lot Jamie.

  You were completely right.

  I've tell a lot of times to Gila Monster that hope one day can help her as she helps me and now i must tell you the same thing.

  Thanks a lot every body.

  Regards 😉