DAC - What to Execute when Connected?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rudy Panigas » DAC - What to Execute when Connected?

Add to briefcase

39 posts, Page 2 of 4

««1 2 3 4 »»»

DAC - What to Execute when Connected?

Expand / Collapse

Author

Message

mtillman-921105

Posted Friday, December 19, 2008 8:22 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 753, Visits: 3,786

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

Post #623014

Posted Friday, December 19, 2008 8:26 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, February 02, 2009 10:07 AM
Points: 81, Visits: 23

Thanks for the updated code. this code will be a great asset for all dba's.

Post #623019

Leendert van Staalduinen

Posted Friday, December 19, 2008 8:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 09, 2012 4:42 AM
Points: 29, Visits: 396

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.

Post #623031

Posted Friday, December 19, 2008 9:27 AM

Old Hand

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:35 AM
Points: 306, Visits: 1,020

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

Post #623112

Leendert van Staalduinen

Posted Friday, December 19, 2008 9:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 09, 2012 4:42 AM
Points: 29, Visits: 396

Rudy, thanks.

I am using SQL Server 2005, Leendert.

Post #623156

Posted Friday, December 19, 2008 11:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 2:42 PM
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

Post #623215

Posted Friday, December 19, 2008 11:41 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 1:56 PM
Points: 769, Visits: 1,167

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

Post #623224

Jonathan Kehayias

Posted Friday, December 19, 2008 11:47 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, June 03, 2013 7:03 PM
Points: 1,696, Visits: 1,746

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

Post #623232

Posted Friday, December 19, 2008 11:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 2:42 PM
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 ?

Post #623236

Posted Friday, December 19, 2008 11:53 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, February 03, 2012 6:55 AM
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.

Post #623240

« Prev Topic | Next Topic »

Add to briefcase

39 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions

Expand / Collapse