Click here to monitor SSC
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
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 1304
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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
SSC Eights!
SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2044 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