Is Job Running? SQL Server Agent Jobs

CREATE PROC isJobRunning (@jobName VARCHAR (256))
AS
BEGIN
/*****************************************************************************
Joe Kelly
2011-06-27 17:14:43.770

FROM: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/da57aedb-096c-40fb-936f-0f727fe3f605/

Determines whether a job is already running or not.

Returns: 0 - no, 1 - yes

EXEC isJobRunning N'WFX Agency Data Import'

*****************************************************************************/

SET NOCOUNT ON

DECLARE @xp_results TABLE
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

DECLARE @job_owner sysname SET @job_owner = SUSER_SNAME()
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner

DECLARE @IsJobRunning BIT

SELECT @IsJobRunning = x.running
FROM @xp_results x
INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id
WHERE sj.name = N'WFX Agency Data Import'
--Insert your job's name between the single quotes

PRINT @IsJobRunning

END

Reading ASP.NET Application Settings From Web.config Using Classic AS

http://www.asp101.com/articles/john/readsettings/default.asp

"...Introduction

Whether you're trying to share settings between your ASP.NET and legacy classic ASP apps or are simply looking for a way to make your eventual migration to ASP.NET easier, this piece of code might be just the ticket.

The code came about because I recently found myself tasked with the annoying job of migrating a database server from an old machine to a newer piece of hardware. The database server was acting as the backend data store for a web site that's been around for quite a few years and was running a mix of classic ASP and ASP.NET applications.

Thankfully both the classic ASP and ASP.NET applications were relatively careful about keeping their connection strings centralized, but I was still needed to change the setting in both the ASP and ASP.NET applications. The switch ended up going quite smoothly, but as I was looking around to make sure I'd found everything, I started to realize just how many settings and how much information was duplicated between the legacy and new applications. It seemed like there had to be a better way.

Since pretty much all new development is being done on .NET, the obvious solution was to find an easy way for classic ASP applications to be able to read their settings from the same source as the ASP.NET applications: the web.config file.


The Code

As we're all aware, the data in the web.config file is stored in an XML-based format. Now I'll admit that my XML skills probably aren't up to par with those of you who use it on a regular basis, but I did manage to hack together a couple short functions to facilitate reading connection strings and application settings from the site's root web.config file.


readsettings.asp

<%@ Language="VBScript" %>
<%
Option Explicit

Function GetAppSetting(strAppSettingKey)
Dim xmlWebConfig
Dim nodeAppSettings
Dim nodeChildNode
Dim strAppSettingValue

Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))

If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeAppSettings = xmlWebConfig.selectSingleNode("//configuration/appSettings")
For Each nodeChildNode In nodeAppSettings.childNodes
If nodeChildNode.getAttribute("key") = strAppSettingKey Then
strAppSettingValue = nodeChildNode.getAttribute("value")
Exit For
End If
Next
Set nodeAppSettings = Nothing
End If
Set xmlWebConfig = Nothing

GetAppSetting = strAppSettingValue
End Function

Function GetConnectionString(strConnStringName)
Dim xmlWebConfig
Dim nodeConnStrings
Dim nodeChildNode
Dim strConnStringValue

Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))

If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeConnStrings = xmlWebConfig.selectSingleNode("//configuration/connectionStrings")
For Each nodeChildNode In nodeConnStrings.childNodes
If nodeChildNode.getAttribute("name") = strConnStringName Then
strConnStringValue = nodeChildNode.getAttribute("connectionString")
Exit For
End If
Next
Set nodeConnStrings = Nothing
End If
Set xmlWebConfig = Nothing

GetConnectionString = strConnStringValue
End Function
%>
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
< html xmlns="http://www.w3.org/1999/xhtml" >
< head>
< title>Reading ASP.NET Application Settings From Web.config Using Classic ASP< /title>
< /head>
< body>

< p>
The following values are all read from the web.config
file located in the root of the web site.
< /p>

< p>
Welcome Message: <%= GetAppSetting("WelcomeMessage") %>
< /p>

< p>
Sample Connection String: <%= GetConnectionString("SampleConnString") %>
< /p>

< p>
Thank You Message: <%= GetAppSetting("ThankYouMessage") %>
< /p>

< /body>
< /html>

As you can see they're quite easy to use both functions are quite similar. The only real difference between the two are related to the location of the settings in the XML tree and the syntax differences between the application settings and connection strings sections.

Speaking of the settings, if you use the functions as written, you'll be retrieving settings by using the connection string's name or the key associated with an application setting. These values are case sensitive, so if you're having trouble, make sure you've got everything spelled exactly the same... case and all.

Although you'll most likely be using your own existing web.config file, I'm including a simple one here for illustration.
web.config

< ?xml version="1.0"?>
< !--
Comments and whitespace shouldn't cause any problems.
-->
< configuration>
< appSettings>
< add key="WelcomeMessage" value="Welcome to our site." />
< add key="ThankYouMessage" value="Thanks for visiting... please come back soon." />
< /appSettings>
< connectionStrings>
< add name="SampleConnString"
providerName="System.Data.OleDbClient"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Inetpub\wwwroot\App_Data\test.mdb;"
/>
< /connectionStrings>
< system.web>
< compilation debug="false" />
< /system.web>
< /configuration>

That's all there is to it. A quick and easy way for you to enable your classic ASP applications to read application settings and connection strings from ASP.NET's web.config file..."

ASP Worker Process - Memory

Response Buffer Limit Exceeded (page fails to render, no obvious error)

... The report is so large that it surpassed the allocated amount of memory for the ASP worker process (aspbufferinglimit). Quadrupling the amount to 16 MB (from the recommended 4MB) allows it to now function correctly...

--------------------------
To change size:

http://support.microsoft.com/kb/925764

set drive:

cd /d %systemdrive%\inetpub\adminscripts

get current amount

cscript.exe adsutil.vbs GET w3svc/aspbufferinglimit


set new amount
cscript.exe adsutil.vbs SET w3svc/aspbufferinglimit < bytes >


--------------------------

String length exceeds maximum length of 32767 characters for 'FileSystem' APIs
Visual Studio 2008
Other Versions

• Visual Studio 2005
A string's length exceeds the maximum length of 32767 characters.
To correct this error
• Shorten the string.
See Also
________________________________________
Other Resources
Strings in Visual Basic

How to Get/Crack a Lost Excel Macro PW? - Solved!

With the help of this post
http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project
and a bit of hacking ...

This seems to be the easiest way (and it should scare y'all just how easy this was, just took some time)

a.) XLSM, XLSB (convert to XLSM, take the performance hit)
b.) VBA is pw protected, not the workbook or worksheet (if so, you're hosed)
c.) Back up file (you'll likely screw this up the first time or two and if you do you'll lose all the VBA modules)
d.) Change extension to .zip, extract to subfolder
e.) in .\xl\vbaProject.bin, with a hex/binary editor (not a file editor) munge the key [dpb] (i.e. becomes [dpx]), save
f.) On parent level, select xml file and the three subfolders, right click, "Send to", "Compressed Zip Folder"
g.) Rename zip file to xlsm extension, open file
h.) When prompted for invalid key, select "Yes" - continue
i.) Open VBA editor
j.) When you go to look at a module, you'll get an error
k.) Go to Tools / Properties / Protection, reset PW, save

You should now be able to view the module source code.

Linked Servers - Log-in Failed for Anonymous

Third hop fails - log in on the actual box you want to deploy to rather than with QA.

Format SQL Date from 2011-06-14 to 06/14/2011

DECLARE @foo DATETIME = GETDATE ()

SELECT CONVERT(VARCHAR(10), @foo, 101)

List Issue: must declare a body ...

"must declare a body because it is not marked abstract or extern."

http://stackoverflow.com/questions/95683/net-property-generating-must-declare-a-body-because-it-is-not-marked-abstract-o

Compiler is being instructed to use 2.0, not 3.5

--------------------------------
add to web.config (separate section - remove space after opening angle bracket):


< system.codedom>
< compilers>
< compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharpCodeProvider,System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" warningLevel="4">
< providerOption name="CompilerVersion" value="v3.5" />
< providerOption name="WarnAsError" value="false" />
< /compiler>
< /compilers>
< /system.codedom>

Classic ASP Debugging with VS 2005

http://blogs.msdn.com/b/greggm/archive/2006/03/15/552108.aspx

Visual Studio 2005

" ... However, if you insist on using Visual Studio 2005 for your classic ASP debugging needs, I have some good news -- while harder to setup, with a little bit of help from this blog, you can still have a nice experience debugging your class ASP code with Visual Studio 2005. There are four features that we cut from Visual Studio 2005 that affect ASP debugging:

1. Project system support for configuring IIS
2. ASP Auto-attach, which the debugger used to find the ASP worker process
3. Remote script debugging
4. Debugging managed code and script code at the same time

Let's go through how we can work around these cuts.


#1. The project system provided support to automatically configure your machine for ASP debugging. However, since it is only a one-time deal, you can always manually configure your machine. From technet:

1.


In IIS Manager, double-click the local computer, right-click the Web Sites folder or an individual Web site folder, and then click Properties.

Note

Configuration settings made at the Web Sites level are inherited by all of the Web sites on the server. You can override inheritance by configuring the individual site or site element.

2.



Click the Home Directory tab, and then click Configuration.

3.



Click the Debugging tab, and then select the Enable ASP server-side script debugging check box.

4.



Click Send detailed ASP error messages to client if you want to send the client very detailed debugging information, or click Send the following text error message to client and type the text you want to send to the client.

5.



Click OK.

If you intend to debug client-side script as well, you might also want to set the ASPCLIENTDEBUG cookie. See MSDN.



#2. Since the debugger doesn’t have support for ASP Auto-Attach, you can’t just press F5. But what you can do is to hit your page in IE, and use the below macro to automatically start debugging the ASP code. You can assign a macro to a key, so within 2 minutes, you can have Ctrl-Shift-F5 (or whatever key you want), setup to automatically attach to the worker process and get a pretty similar experience to what you have always had.

Sub ClassicASPAttach()

Try

Dim os As System.Version = System.Environment.OSVersion.Version

Dim IISProcess As String = "w3wp.exe"



If os.Major = 5 And os.Minor < 2 Then

IISProcess = "dllhost.exe"

End If



Dim processFound As Boolean = False



Dim process As EnvDTE80.Process2

For Each process In DTE.Debugger.LocalProcesses



'Determine if the process could the IIS worker process

Dim processName As String = process.Name.ToLowerInvariant()

Dim processBaseName As String = System.IO.Path.GetFileName(processName)

If Not processBaseName = IISProcess Then

If Not processBaseName = "inetinfo.exe" Then

Continue For

End If

End If



'Determine if the process contains asp.dll

Dim aspLoaded As Boolean = False

Dim diagProcess As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessById(process.ProcessID)

Dim diagModule As System.Diagnostics.ProcessModule

For Each diagModule In diagProcess.Modules

Dim moduleName As String = System.IO.Path.GetFileName(diagModule.FileName).ToLowerInvariant()

If moduleName = "asp.dll" Then

aspLoaded = True

Exit For

End If

Next



'If the process contains asp.dll, attach to it

If aspLoaded Then

process.Attach2("Script")

processFound = True

End If

Next



If Not processFound Then

MsgBox("Could not find this IIS process. Hit a web page containing classic ASP script so that the process will start.")

End If



Catch ex As System.Exception

MsgBox(ex.Message)

End Try

End Sub



#3. Sadly, there just isn’t any way to do remote script debugging. If you need remote script debugging, my only suggestion would be to use Remote Desktop and run Visual Studio on your server.


#4. Again, there just isn’t any way to debug both script code and managed code at the same time. My only suggestion here would be to switch back and forth between managed debugging and script debugging. ... "