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. ... "

Fun with DBMail and Service Accounts

If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.

Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).

a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.

b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB

Above was the solution to our issues.

Cheers – J

Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx

Other hints:

+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?

+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;

Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp

What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';

Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'

What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'

SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems

Fun with SQL Server DatabaseMail and Service Accounts

If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.

Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).

a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.

b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB

Above was the solution to our issues.

Cheers – J

Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx

Other hints:

+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?

+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;

Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp

What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';

Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'

What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'

SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems

jQuery ASP.Net Find Value of Input Hidden

http://www.foliotek.com/devblog/extending-jquery-to-select-asp-controls/


jQuery.expr[':'].asp = function(elem, i, match) {
return (elem.id && elem.id.match(match[3] + "$"));
};

alert($(":asp(fy)").val());

fy = $(":asp(h1)").val();

.Net JS - Register Client Script

/// http://msdn.microsoft.com/en-us/library/kx145dw2%28v=VS.90%29.aspx
String csname = "SpreadJS";
String csurl = "Spread_New_or_All.js";
Type cstype = this.GetType();
ClientScriptManager cs = Page.ClientScript;
if (!cs.IsClientScriptIncludeRegistered(cstype, csname))
{
cs.RegisterClientScriptInclude(cstype, csname, ResolveClientUrl(csurl));
}

Really - I'm Not a DBA :: Custom DB Permissions: user - schema - db - login Part1

/*
Creates user, assigns full permissions to specified schema an RO to all other schemas in the DB
*/


USE MASTER

GO

ALTER PROC dbo.dba_Assign_DB_Schema_Permissions (@db VARCHAR (256), @schema VARCHAR (256), @user VARCHAR (256), @isADName INT = 1)
AS
BEGIN

/*****************************************************************************
Joe Kelly
2011-04-07 11:23:12.193

For assigning users very specific permissions. Assumes user is in AD.

If a SQL log-in does not exist it is created. Will not effect existing
sql login.

If the log-in is not a member of the specified DB they are added as guest
and specified DB is set as default. Will not effect (current) DB settings
for an existing user.

Creates access and CRUD for specifiec login for specified DB as guest

Creates SELECT permissions to all other schemas in the specified database

Creates all permissions but Ownership and Take Control for the specified
schema in the specified DB.

Can be parameterized (prime number modulo) for attachment to a UI (checkboxes).

May be paired with a specific dbo.dba_Deny_DB_Schema_Permissions though should
be unnecessary if this is parameterized (i.e. grant select & exec on
another's schema but not alter)

Joe Kelly
2012-02-29 11:02:14.367

Modified for AD and non AD accounts

-- AD
EXEC Master.dbo.dba_Assign_DB_Schema_Permissions 'TARGETDB', 'BUD', 'UserA', 1
-- Non AD
EXEC Master.dbo.dba_Assign_DB_Schema_Permissions 'TARGETDB', 'BUD', 'UserA', 0



------------------------------------------------------------------------------
-- DATABASE level permissions

CONNECT
This grants or denies the ability to enter the database. When a new user is
created, it is granted by default.

CREATE DEFAULT
This grants or denies the ability to create a default. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

CREATE FUNCTION
This grants or denies the ability to create a function. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

CREATE PROCEDURE
This grants or denies the ability to create a stored procedure. This
permission is granted implicitly to the db_ddladmin and db_owner fixed
database roles. In SQL Server 2005 or higher compatibility mode, the user
will still need ALTER SCHEMA rights to create one in a particular schema.

CREATE TABLE
This grants or denies the ability to create a table. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

CREATE VIEW
This grants or denies the ability to create a view. This permission is
granted implicitly to the db_ddladmin and db_owner fixed database roles.
In SQL Server 2005 or higher compatibility mode, the user will still need
ALTER SCHEMA rights to create one in a particular schema.

VIEW DEFINITION
This grants or denies the ability to view the underlying T-SQL or metadata
on objects within the database. The db_securityadmin database fixed server
role has this permission implicitly.

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

-- SCHEMA level permissions
ALTER
This grants or denies the ability to alter the existing schema.

EXECUTE
This grants or denies the ability to issue the EXECUTE command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

INSERT
This grants or denies the ability to issue the INSERT command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

DELETE
This grants or denies the ability to issue the DELETE command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

UPDATE
This grants or denies the ability to issue the UPDATE command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

SELECT
This grants or denies the ability to issue the SELECT command against all
applicable objects within the schema. /* Best practices say not to use
this at the database level, but rather at the schema level. */

REFERENCES
This grants or denies the ability to create relationships between objects
such as foreign keys on tables referencing other tables or the use of
SCHEMABINDING by views and functions. The permission is granted
implicitly to the db_ddladmin fixed database role.

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

SET NOCOUNT ON

DECLARE @sql NVARCHAR (2048) = ''
, @domain NVARCHAR (128) = 'DOMAIN\'
, @output VARCHAR (8000) = ''
, @error BIGINT = 0
, @count INT = 0
, @iter BIGINT = 0
, @schemaName VARCHAR (256) = ''
, @rowcount INT = 0

DECLARE @schemas TABLE (iter INT IDENTITY (1, 1), SCHEMANAME NVARCHAR(256))
DECLARE @fullUserName NVARCHAR (128) = '', @NTUserName NVARCHAR (128) = ''

IF(@isADName != 0) SELECT @fullUserName = @domain + @user
ELSE SELECT @fullUserName = @user

SELECT @NTUserName = '[' + @fullUserName + ']'

-------------------------------------------------------------------------------
-- See if the specified DB exists on this server

IF NOT EXISTS (
SELECT name FROM master.dbo.sysdatabases WHERE name = @db)
BEGIN
SET @output += 'Specified database, ' + @db
+ ', does not exist on this server'
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END

-------------------------------------------------------------------------------
-- See if the specified schema exists in this DB

SET @sql = 'SELECT SCHEMA_NAME '
+ ' FROM ' + @db + '.INFORMATION_SCHEMA.SCHEMATA '
+ ' WHERE CATALOG_NAME = ''' + @db + ''''
+ ' AND SCHEMA_NAME = ''' + @schema + ''''
-- SELECT @sql
EXEC sp_executesql @sql
SET @rowcount = @@ROWCOUNT

IF (@rowcount != 1)
BEGIN
SET @output += 'Specified schema, ' + @schema
+ ', does not exist in databae: ' + @db -- + DB_NAME()
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END

-------------------------------------------------------------------------------
-- See if we need to create the login

IF NOT EXISTS (SELECT name FROM master.dbo.syslogins WHERE name = @fullUserName)
BEGIN
SET @sql = 'CREATE LOGIN ' + @NTUserName + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + @db
-- SELECT @sql
EXEC sp_executesql @sql
SET @error = @@ERROR

IF (@error != 0)
BEGIN
SET @output += 'Failure creating log-in: ' + @fullUserName
+ CHAR(10) + CHAR(13)
+ 'Username does NOT have to be specified with the domain, UCSFMC is assumed. '
+ CHAR(10) + CHAR(13)
+ 'Correct username format for input: SmithJ'
+ CHAR(10) + CHAR(13)
+ ' Is the user in active directory?'
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'Log-in created: ' + @fullUserName
+ CHAR(10) + CHAR(13)

END

-------------------------------------------------------------------------------
-- See if the user exists in this DB

SET @sql = 'SELECT name '
+ ' FROM ' + @db + '.dbo.sysusers '
+ ' WHERE name = ''' + @fullUserName + ''''
-- SELECT @sql
EXEC sp_executesql @sql
SET @rowcount = @@ROWCOUNT

IF (@rowcount = 0)
BEGIN
SET @sql = 'USE ' + @db + ' CREATE USER ' + @NTUserName + ' FOR LOGIN ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql
SET @error = @@ERROR

IF (@error != 0)
BEGIN
SET @output += 'Failure creating user ' + @fullUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @fullUserName + ' created in database ' + @db
+ CHAR(10) + CHAR(13)
END

-------------------------------------------------------------------------------
-- Assign database permissions

---------------------------------------
-- Assign CONNECT

SET @sql = 'USE ' + @db + ' GRANT CONNECT TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CONNECT to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CONNECT'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE DEFAULT

SET @sql = 'USE ' + @db + ' GRANT CREATE DEFAULT TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE DEFAULT to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE DEFAULT'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE FUNCTION

SET @sql = 'USE ' + @db + ' GRANT CREATE FUNCTION TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE FUNCTION to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE FUNCTION'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE PROCEDURE

SET @sql = 'USE ' + @db + ' GRANT CREATE PROCEDURE TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE PROCEDURE to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE PROCEDURE'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE TABLE

SET @sql = 'USE ' + @db + ' GRANT CREATE TABLE TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE TABLE to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE TABLE'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign CREATE VIEW

SET @sql = 'USE ' + @db + ' GRANT CREATE VIEW TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning CREATE VIEW to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned CREATE VIEW'
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign VIEW DEFINITION

SET @sql = 'USE ' + @db + ' GRANT VIEW DEFINITION TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning VIEW DEFINITION to ' + @NTUserName + ' in database ' + @db
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned VIEW DEFINITION'
+ CHAR(10) + CHAR(13)

-- End DB permissions
---------------------------------------

-------------------------------------------------------------------------------
-- Assign schema permissions

---------------------------------------
-- Assign ALTER

SET @sql = 'USE ' + @db + ' GRANT ALTER ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning ALTER to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned ALTER on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign EXECUTE

SET @sql = 'USE ' + @db + ' GRANT EXECUTE ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning EXECUTE to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned EXECUTE on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign INSERT

SET @sql = 'USE ' + @db + ' GRANT INSERT ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning INSERT to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned INSERT on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign DELETE

SET @sql = 'USE ' + @db + ' GRANT DELETE ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning DELETE to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned DELETE on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign UPDATE

SET @sql = 'USE ' + @db + ' GRANT UPDATE ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning UPDATE to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned UPDATE on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign SELECT

SET @sql = 'USE ' + @db + ' GRANT SELECT ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning SELECT to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned SELECT on schema ' + @schema
+ CHAR(10) + CHAR(13)

---------------------------------------
-- Assign REFERENCES

SET @sql = 'USE ' + @db + ' GRANT REFERENCES ON SCHEMA :: ' + @schema + ' TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning REFERENCES to ' + @NTUserName + ' in database ' + @db + ' on schema ' + @schema
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' in database ' + @db + ' assigned REFERENCES on schema ' + @schema
+ CHAR(10) + CHAR(13)

-- End schema permissions
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- Assign SELECT on the other schemas in this DB

SET @sql = 'SELECT DISTINCT SCHEMA_NAME '
+ 'FROM ' + @db + '.INFORMATION_SCHEMA.SCHEMATA '
+ 'WHERE SCHEMA_NAME != ''' + @schema + ''''
+ 'AND SCHEMA_NAME NOT LIKE ''db_%'''

INSERT @schemas (SCHEMANAME) EXEC sp_executesql @sql
SELECT @count = @@ROWCOUNT
INSERT @schemas (SCHEMANAME) SELECT 'dbo'
SELECT @count += @@ROWCOUNT

WHILE (@iter < @count)
BEGIN

SELECT @schemaName = SCHEMANAME FROM @schemas WHERE iter = @iter + 1

SET @sql = 'USE ' + @db + ' GRANT SELECT ON SCHEMA :: [' + @schemaName + '] TO ' + @NTUserName
-- SELECT @sql
EXEC sp_executesql @sql

IF (@error != 0)
BEGIN
SET @output += 'Failure assigning SELECT to ' + @NTUserName + ' on schema ' + @schemaName
+ CHAR(10) + CHAR(13)
GOTO RaiseError
END
SET @output += 'User: ' + @NTUserName + ' assigned SELECT on schema ' + @schemaName
+ CHAR(10) + CHAR(13)

SET @iter += 1

END

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

SELECT @output + CHAR(10) + CHAR(13) + 'Routing passed'

RETURN

RaiseError:

SELECT @output + CHAR(10) + CHAR(13) + 'Routing failed'

END

User Security

Handling user security is an exercise in the practice of obfuscation by design.

schema permissions

select *
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where class_desc = 'schema'

SQL Server Installation information

SELECT SERVERPROPERTY('productversion')
, SERVERPROPERTY ('productlevel')
, SERVERPROPERTY ('edition')
, SERVERPROPERTY ('InstanceName')
, SERVERPROPERTY ('LCID')
, SERVERPROPERTY ('MachineName')
, SERVERPROPERTY ('LicenseType')
, SERVERPROPERTY ('NumLicenses')

Specialized User / Role / Schema setups

Specialized User / Role / Schema setups

GRANT VIEW DEFINITION ON SCHEMA :: Usr TO [ABC\SmithJ]
GRANT CREATE TABLE TO [ABC\SmithJ]

OR ...

UserA + UserB + UserC >> Role1 >> Schema1 (ownership no difference)

for Role1 assign ddl_admin

Remove role rights on other schemas

Can still create objects in other schemas but not modify them

New SQL Mail

DECLARE @body1 VARCHAR (100)
SET @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='foo@foo.org',
@subject = 'My Mail Test msdb.dbo.sp_send_dbmail',
@body = @body1,
@body_format = 'HTML' ;

Leap Year

http://en.wikipedia.org/wiki/Leap_years

if year modulo 400 is 0
then is_leap_year
else if year modulo 100 is 0
then not_leap_year
else if year modulo 4 is 0
then is_leap_year
else
not_leap_year

VIEW Definition

USE myDB
GO
GRANT VIEW Definition TO [domain\fuddelmer]

Restore DB

RESTORE DATABASE [dbName] FROM DISK = N'D:\temp\dbName_backup_2011_02_23_020006_4755253.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

Disabling F1

Explore.exe reset
C:\WINDOWS\PCHealth\HelpCtr\Binaries\HelpCtr.exe


VS 9.0 reset:
C:\Program Files\Common Files\Microsoft Shared\VS Help Data\9.0

Copy Table Contents

USE Budget
GO

/*

DROP TABLE #columnsPerTable

*/

SET NOCOUNT ON

DECLARE @iter INT = 0, @iterCol INT = 0, @tableCount INT = 0, @columnCount INT = 0

DECLARE @insert VARCHAR(1024) = ' INSERT '
, @delete VARCHAR(1024) = ' DELETE '
, @select VARCHAR(1024) = ' SELECT '
, @from VARCHAR(1024) = ' FROM '
, @where VARCHAR(1024) = ' WHERE FiscalYear = @fromYear'
, @tableName VARCHAR(1024) = ''
, @columnName VARCHAR(1024) = ''
, @columnString VARCHAR(8000) = ''
, @comma CHAR(2)= ', '

-- CREATE TABLE #tables (
DECLARE @tables TABLE (
ident INT IDENTITY (1, 1)
, TABLE_SCHEMA VARCHAR (255)
, TABLE_NAME VARCHAR (255)
)

-- CREATE TABLE #columns (
DECLARE @columns TABLE (
TABLE_SCHEMA VARCHAR (255)
, TABLE_NAME VARCHAR (255)
, COLUMN_NAME VARCHAR (255)
)

CREATE TABLE #columnsPerTable (
-- DECLARE #columnsPerTable TABLE (
ident INT IDENTITY (1, 1)
, TABLE_NAME VARCHAR (255)
, COLUMN_NAME VARCHAR (255)
)

INSERT @tables (
TABLE_SCHEMA
, TABLE_NAME
)
SELECT DISTINCT
t.TABLE_SCHEMA
, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = t.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_CATALOG = 'Budget'
AND t.TABLE_SCHEMA = 'dbo'
AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT LIKE '%bak%'
AND c.COLUMN_NAME LIKE '%fisc%'

-- # of tables
SELECT @tableCount = @@ROWCOUNT

INSERT @columns (
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
)
SELECT t.TABLE_SCHEMA
, t.TABLE_NAME
, c.COLUMN_NAME
FROM @tables t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME

WHILE (@iter < @tableCount)
BEGIN

SELECT @tableName = TABLE_NAME
FROM @tables
WHERE ident = @iter + 1

INSERT #columnsPerTable (
TABLE_NAME
, COLUMN_NAME
)
SELECT TABLE_NAME
, COLUMN_NAME
FROM @columns
WHERE TABLE_NAME = @tableName

SELECT @columnCount = @@ROWCOUNT

WHILE (@iterCol < @columnCount)
BEGIN

SELECT @columnString += '[' + COLUMN_NAME + ']' + @comma
FROM #columnsPerTable
WHERE ident = @iterCol + 1
AND COLUMN_NAME IS NOT NULL

SELECT @iterCol += 1

END

SELECT @columnString = LEFT (RTRIM(LTRIM(@columnString)), LEN(RTRIM(LTRIM(@columnString))) - 1)

SELECT @delete
+ ' '
+ @tableName
+ @where

SELECT @insert
+ @tableName
+ ' ( '
+ @columnString
+ ' ) '

SELECT @select
+ @columnString
+ @from
+ @tableName
+ @where

SELECT @columnCount = 0, @iterCol = 0, @columnString = ''
SELECT @iter += 1
DELETE #columnsPerTable
DBCC CHECKIDENT (#columnsPerTable, RESEED, 0)

END

/*

DROP TABLE #columnsPerTable

DECLARE @insert VARCHAR(1024) = ' INSERT '
, @select VARCHAR(1024) = ' SELECT '
, @where VARCHAR(1024) = ' WHERE '
, @tableName VARCHAR(1024)
, @columnName VARCHAR(1024)
, @columnString VARCHAR(Max)
, @comma CHAR(2)= ', '

*/

Find Tables with Columns ...

SELECT DISTINCT t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, c.COLUMN_NAME
, ' -- '
, t.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = t.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_CATALOG = 'Budget'
AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME NOT LIKE '%bak%'
AND c.COLUMN_NAME LIKE '%fisc%'

Table Sizes

-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

UPDATE #t SET reserved = REPLACE(REPLACE(reserved, 'kb', '') , ' ', '')
UPDATE #t SET data = REPLACE(REPLACE(data, 'kb', '') , ' ', '')
UPDATE #t SET index_size = REPLACE(REPLACE(index_size, 'kb', '') , ' ', '')
UPDATE #t SET unused = REPLACE(REPLACE(unused, 'kb', '') , ' ', '')


SELECT *
FROM #t
order by cast (data as int ) desc


SELECT *
FROM #t
order by cast (reserved as int ) desc

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t

-- DROP TABLE #t

SQL SERVER CREATE READONLY USERS

/*********************************************************
Joe Kelly
2011-01-05 13:19:07.680

Add user to all non-system, writable, databases as READONLY

Input: db server username

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

SET NOCOUNT ON

DECLARE @userName VARCHAR (128) = 'UCSFMC\sosas'

DECLARE @dbList TABLE (ident INT IDENTITY (0, 1), dbName VARCHAR (128))
DECLARE @execString VARCHAR (128) = ''
, @aVeryLongString VARCHAR (MAX) = ''
, @lineFeed CHAR (4) = CHAR(10) + CHAR(13)
, @maxCount INT = 0
, @iter INT = 0

IF NOT EXISTS
(
SELECT name
FROM master.dbo.syslogins
WHERE name = @userName
)
BEGIN
SELECT 'Please create server login for: ' + @userName
RETURN
END

INSERT @dbList (dbName)
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('Master', 'Resource', 'TempDB', 'Model', 'MSDB', 'Distribution', 'ReportServer', 'ReportServerTempDB')
AND name NOT LIKE 'ASPState%'

SELECT @maxCount = @@ROWCOUNT

WHILE (@iter < @maxCount)
BEGIN
SELECT @aVeryLongString = 'USE ['
+ dbName
+ '] '
+ @lineFeed
+ 'GO '
+ @lineFeed
FROM @dbList
WHERE ident = @iter

SELECT @aVeryLongString += 'IF NOT EXISTS ( SELECT name FROM sysusers WHERE name = '''
+ @userName
+ ''') BEGIN EXEC sp_adduser '''
+ @userName
+ '''; END '
+ @lineFeed

SELECT @aVeryLongString += 'EXEC sp_addrolemember db_datareader, '''
+ @userName
+ '''; '
+ @lineFeed

SELECT @aVeryLongString += 'EXEC sp_addrolemember db_denydatawriter, '''
+ @userName
+ '''; '
+ @lineFeed

SELECT @aVeryLongString

SELECT @iter += 1
END

Generate Scripts for Finding Fragmentation

SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),i.id) + ',' + -- table id
CONVERT(varchar(20),i.indid) + ') -- ' + -- index id
object_name(i.id) + '.' + -- table name
i.name -- index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = 'U'
and i.indid < 2
and i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid

DB Sizes & Space Used ... sp_spaceused

Per DB: sp_helpdb

Per object: EXEC sp_spaceused 'sys_users'

For all objects in a DB: EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

sp_MSforeachtable is and undocumented system proc that lets you do it easily.

I suggest dumping the output to a flat file and cleaning in excel ([Query] [Results to] [Results to file]).

Old School Transform: 1-X Pivot Table

ALTER PROC hGrid_MevDetails_Parent (
@costCenter CHAR (7)
, @FY CHAR (2)
)
AS
BEGIN

-- Joe Kelly
-- 2010-11-23 11:39:21.473
--
-- Parent proc that drives the hierarchal data grid in the
-- portal for labor something using Tmp_PreBuild_MEV_Details

-- EXEC hGrid_MevDetails_Parent '1601001', '10'

SET NOCOUNT ON

DECLARE @preXForm TABLE (
gDescription VARCHAR (50)
, gID INT
, Period INT -- CHAR (4)
, pMonth INT -- CHAR (2)
, sumBudget FLOAT
)

INSERT @preXForm (
gID
, Period
, pMonth
, sumBudget
)
SELECT tpmd.groupID, CAST(tpmd.Period AS INT), CAST(RIGHT(tpmd.Period, 2) AS INT), SUM(tpmd.Budget)
FROM Tmp_PreBuild_MEV_Details tpmd
WHERE CostCenter = '1707000' -- @costCenter CHAR (7)
AND LEFT(tpmd.Period, 2) = '10' -- @FY CHAR (2)
GROUP BY tpmd.GroupID, tpmd.Period

SELECT p.gID GroupID
, dg.Description
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 1
AND p.gID = gID
) AS p1
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 2
AND p.gID = gID
) AS p2
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 3
AND p.gID = gID
) AS p3
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 4
AND p.gID = gID
) AS p4
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 5
AND p.gID = gID
) AS p5
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 6
AND p.gID = gID
) AS p6
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 7
AND p.gID = gID
) AS p7
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 8
AND p.gID = gID
) AS p8
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 9
AND p.gID = gID
) AS p9
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 10
AND p.gID = gID
) AS p10
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 11
AND p.gID = gID
) AS p11
, (
SELECT ISNULL(sumBudget, 0)
FROM @preXForm
WHERE pMonth = 12
AND p.gID = gID
) AS p12
FROM @preXForm p
LEFT OUTER JOIN dat_groups dg
ON p.gID = dg.id
GROUP BY p.gID
, dg.Description
, dg.SortOrder
ORDER BY ISNULL(dg.SortOrder, 999)

END

JS Timer

var icount = 10;
var t;
function ticker() {
countDown();
t = setTimeout("ticker()", 1000);
if (icount <= 0) {
clearTimeout(t);
window.location = "http://performance";
}
}

function countDown() {
icount--;
document.getElementById('idCounter').innerText = icount;
}

Page Transitions

< meta ht tp-equiv="Page-Exit" content="pro gid:DXIm ageTransform.Microsoft.Fade(Overlap=1.00,duration=0.3)" / >

.............

but, of course, w/o the extra spaces ...

JS file from a master page

Credit to http://geekswithblogs.net/rachit/archive/2007/01/14/103608.aspx

Finally a method that works ...

In the master page Page_Load event

protected void Page_Load(object sender, EventArgs e)
{
HtmlGenericControl myJs = new HtmlGenericControl();
myJs.TagName = "script";
myJs.Attributes.Add("type", "text/javascript");
myJs.Attributes.Add("language", "javascript"); //don't need it usually but for cross browser.
myJs.Attributes.Add("src", ResolveUrl("../Script/fileIO.js"));
this.Page.Header.Controls.Add(myJs);

}

Now you can reference its functions in the master page markup and in the content page markup.

Objects by Schema

SELECT SCHEMA_NAME(schema_id), *
FROM sys.objects
WHERE SCHEMA_ID = 20

SELECT DISTINCT ' SELECT SCHEMA_NAME(', schema_id , ')'
FROM sys.objects

SELECT SCHEMA_NAME(20)

Simple SysColumns

SELECT so.name
, so.crdate
, sc.*
FROM sysobjects so
JOIN syscolumns sc
ON so.id = sc.id
WHERE so.type = 'U'
AND sc.name LIKE '%dos%'
ORDER BY so.crdate DESC

Find Column

SELECT ' SELECT '''
+ so.name
+ '.'
+ sc.name
+ ': '', ['
+ sc.name
+ '] FROM ['
+ s.name
+ '].['
+ so.name
-- , so.crdate
+ '] WHERE ['
+ sc.name
+ '] LIKE ''%748801'''
FROM sys.objects so
JOIN syscolumns sc
ON so.object_id = sc.id
JOIN sys.schemas s
ON so.schema_id = s.schema_id
WHERE so.type = 'u'
AND (
sc.name LIKE '%CC%'
OR sc.name LIKE '%cost%center%'
)
AND sc.name NOT LIKE '%account%'
ORDER BY so.create_date DESC

And generate the search text ...

DECLARE @qArg VARCHAR (64) = '%748801%'

SELECT ' SELECT * FROM ['
+ s.name
+'].['

+ so.name
+ '] '
+ ' WHERE ['
+ sc.name
+ '] LIKE '''
+ @qArg
+ ''''
FROM sys.objects so
JOIN syscolumns sc
ON so.object_id = sc.id
JOIN sys.schemas s
ON so.schema_id = s.schema_id
WHERE so.type = 'u'
AND (
sc.name LIKE '%CC%'
OR sc.name LIKE '%cost%center%'
)
AND sc.name NOT LIKE '%account%'
ORDER BY so.create_date DESC

Find in Syscomments

SELECT so.name
, so.crdate
, sc.text
FROM sysobjects so
JOIN syscomments sc
ON so.id = sc.id
WHERE so.type = 'p'
AND sc.text LIKE '%insert%select%*%'
ORDER BY so.crdate DESC

Read AppSettings and-or XML File

public static string GetCustomConfigValue(string filePath, string keyName)
{
string retVal = "";
XmlDocument doc = new XmlDocument();
try
{
doc.Load(HttpContext.Current.Server.MapPath(filePath));
XmlNode root = doc.DocumentElement;
retVal = root.SelectSingleNode(keyName).ChildNodes[0].Value;
}
catch (Exception ex)
{
logAll("DSS Web 2 Beta", ex.ToString(), utilFns.Common.GetCurrentPageName(), "XML error in utilFns.cs: " + filePath + " : " + keyName, true);
}
return retVal;
}

public static string GetAppConfigValue(string KeyName)
{
return ConfigurationSettings.AppSettings[KeyName];
}

Import / Using Directive for no code behind

<%@ Import Namespace="System.Configuration" %>

Random FN and Data Generator

CREATE TABLE jk_RecoveryTest (
ident INT IDENTITY (1, 1)
, cInt INT DEFAULT 0
, cFloat FLOAT DEFAULT 0
, cDatetime DATETIME DEFAULT GETDATE()
, cChar CHAR (4)
, cVarChar VARCHAR (128)
, cVarCharMax VARCHAR (MAX)
, cStart DATETIME DEFAULT GETDATE()
, cEnd DATETIME DEFAULT GETDATE()
, iteration INT DEFAULT 0
, uDate DATETIME DEFAULT GETDATE()
, crDate DATETIME DEFAULT GETDATE()
)

GO

CREATE TABLE jk_RecoveryStats (
cVarChar VARCHAR (1023)
, cStart DATETIME DEFAULT GETDATE()
, cEnd DATETIME DEFAULT GETDATE()
, iteration INT DEFAULT 0
, uDate DATETIME DEFAULT GETDATE()
, crDate DATETIME DEFAULT GETDATE()
)

GO

CREATE VIEW dbo.vRandNumber
AS
SELECT RAND() RandNumber

GO

CREATE FUNCTION RandNumber()
RETURNS float
AS
BEGIN
RETURN (SELECT RandNumber
FROM dbo.vRandNumber)
END

GO

CREATE FUNCTION RandNumberRng(@Min int, @Max int)
RETURNS float
AS
BEGIN
RETURN @Min
+ ( SELECT RandNumber
FROM dbo.vRandNumber)
* (@Max-@Min)
END




TRUNCATE TABLE jk_RecoveryTest

TRUNCATE TABLE jk_RecoveryStats

-- SELECT * FROM jk_RecoveryTest

SET NOCOUNT ON

DECLARE @iter INT = 1
, @outerIter INT = 1
, @stop INT = 10000--0
, @outerStop INT = 10--00 0
, @tDate DATETIME = GETDATE()
, @tString VARCHAR (128) = 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx'
, @tInt INT = 0

WHILE (@outerIter <= @outerStop)
BEGIN

WHILE (@iter <= @stop)
BEGIN

INSERT jk_RecoveryTest (
cInt
, cFloat
, cDatetime
, cChar
, cVarChar
, cVarCharMax
, cStart
, cEnd
, iteration
)
SELECT
CAST(dbo.RandNumberRng (0, 9) AS INT)
, dbo.RandNumberRng (0, 9)
, DATEADD(dd, dbo.RandNumberRng (0, 28), @tDate)
, LEFT (@tString, dbo.RandNumberRng (0, 4))
, LEFT (@tString, dbo.RandNumberRng (0, 128))
, LEFT (@tString, dbo.RandNumberRng (0, 128))
, @tDate
, @tDate
, @iter

SET @iter += 1
END

--SELECT 'Inserts'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000
--SET @tDate = GETDATE()

SET @iter = 1

WHILE (@iter <= @stop)
BEGIN

SELECT @tInt = AVG(cFloat)
-- SELECT AVG(cFloat)
FROM jk_RecoveryTest
WHERE cInt % @iter = 2

SET @iter += 1

END

--SELECT 'Seeks'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000

--INSERT jk_RecoveryStats (
-- cVarChar
-- , cStart
-- , cEnd
-- , iteration
-- )
--SELECT
-- 'FULL - Seeks'
-- , @tDate
-- , GETDATE()
-- , @outerIter

--SET @tDate = GETDATE()

SET @iter = 1

WHILE (@iter <= @stop)
BEGIN

UPDATE jk_RecoveryTest
SET cInt = dbo.RandNumberRng (0, @iter)
WHERE ident = @iter

SET @iter += 1

END

--SELECT 'Updates'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000

--INSERT jk_RecoveryStats (
-- cVarChar
-- , cStart
-- , cEnd
-- , iteration
-- )
--SELECT
-- 'FULL - Updates'
-- , @tDate
-- , GETDATE()
-- , @outerIter

--SET @tDate = GETDATE()

SET @iter = 1

WHILE (@iter <= @stop)
BEGIN

DELETE
FROM jk_RecoveryTest
WHERE ident = @iter

SET @iter += 1

END

--SELECT 'Deletes'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000

INSERT jk_RecoveryStats (
cVarChar
, cStart
, cEnd
, cDiff
, iteration
)
SELECT
-- 'FULL - Deletes'
'FULL'
, @tDate
, GETDATE()
, DATEDIFF (ms, @tDate, GETDATE())
, @outerIter

SET @tDate = GETDATE()

SET @iter = 1

SET @outerIter += 1

END


SELECT cVarChar, AVG(cDiff)
FROM jk_RecoveryStats
GROUP BY cVarChar