SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DAC - What to Execute when Connected?


DAC - What to Execute when Connected?

Author
Message
mtillman-921105
mtillman-921105
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 3852
Our abbreviations are beginning to overlap often. Since I like audo, I see "DAC" and I think "Digital to Analogue Converter."

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
dparmender
dparmender
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 23
Thanks for the updated code. this code will be a great asset for all dba's.
Leendert van Staalduinen
Leendert van Staalduinen
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 397
We learn every day, but my SSMS complains about the line:
"CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st".
I do not know what is going wrong.
Leendert.
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1346 Visits: 1312
Are you executing the code on a SQL 2005? Does not work on SQL 2000.

Here is the link for more information on CROSS APPLY

http://msdn.microsoft.com/en-us/library/ms177634.aspx

Rudy



Leendert van Staalduinen
Leendert van Staalduinen
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 397
Rudy, thanks.

I am using SQL Server 2005, Leendert.
gagandeshpande
gagandeshpande
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
Need some help...

The following gives an error.

SELECT '--Shows avail free DB space ' as ' '
exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )
AS int)/128.0 AS ''Available Space In MB'' FROM .SYSFILES'

I also got this when I tried to execute - EXEC sp_msForEachDB 'PRINT ''?'''

Could not find stored procedure 'sp_msForEachDB'.

We are on SQL Server Enterprise 2005

Any ideas ?

Regards

Gagan
Jerry Hung
Jerry Hung
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1814 Visits: 1208
It worked fine for me on SQL 2008

You have to remove the period and lower case 'sysfiles' (SYSFILES won't work for me)

exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )
AS int)/128.0 AS ''Available Space In MB'' FROM sysfiles' --select * from sysfiles



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3670 Visits: 1807
Rudy,

If this is intended for SQL 2005/2008 why are you using legacy views like sysdatabases and sysfiles in the code. You also are not using the schema qualifiers in the code.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
gagandeshpande
gagandeshpande
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
I tried this

SELECT '--Shows avail free DB space ' as ' '
exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )
AS int)/128.0 AS ''Available Space In MB'' FROM sysfiles'

Gave me the following output

(1 row(s) affected)
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'sp_MSForEachDB'.

Results was only
--Shows avail free DB space

Is the installation missing the procedure ?
jbwillia
jbwillia
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 75
That was definitley a nice article and the code works. I only had to do minor changes like:

exec sp_MSForEachDB 'Use [?] SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )
AS float)/128.0 AS ''Available Space In MB'' FROM sysfiles'

They way you had it wriiten does not support case sensitive collation.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search