IF OBJECT_ID('tempdb..##badge_updt') IS NOT NULL DROP TABLE ##badge_updt
Thursday, July 30, 2015
Dropping Global Temp Tables
-- A better question is why are you using global temp tables in the first place???
IF OBJECT_ID('tempdb..##badge_updt') IS NOT NULL DROP TABLE ##badge_updt
IF OBJECT_ID('tempdb..##badge_updt') IS NOT NULL DROP TABLE ##badge_updt
Wednesday, July 29, 2015
TFS Hidden Workspace Solution
-
Q:
When creating a new build in Team
Foundation Server, I get the following error when attempting to run the new
build:
The path
C:\Build\ProductReleases\FullBuildv5.4.2x\Sources is already mapped to
workspace BuildServer_23.
I am unable to see a workspace by that
name in the workspaces dialog.
|
|
|
|
Monday, July 27, 2015
PowerShell - Find All Files of Type by Date
$limit = (Get-Date).AddDays(-15)
#$path = "C:\Some\Path"
$path = "C:\Temp"
# Delete files older than the $limit.
# Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Extension -eq ".txt" }
$limit = (Get-Date).AddDays(-1)
$path = "C:\Temp"
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Extension -eq ".BAK" }
#$path = "C:\Some\Path"
$path = "C:\Temp"
# Delete files older than the $limit.
# Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Extension -eq ".txt" }
$limit = (Get-Date).AddDays(-1)
$path = "C:\Temp"
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Extension -eq ".BAK" }
What Are the Sizes of My Databases?
with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
Where Are my Backups?
SELECT physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name like '%%'
ORDER BY backup_finish_date DESC
backup_start_date,
backup_finish_date,
backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name like '%%'
ORDER BY backup_finish_date DESC
Wednesday, July 22, 2015
Fun With Powershell - Copy All Unique Files and Grab the Top X Lines
#change the prompt
function prompt { "Posh >" ; return " " }
#copy locally all unique file names
#Make the script file to Get just the top few lines
Get-ChildItem -Path C:\temp\ExperiantRegistration\ -Filter "*.txt" | ForEach-Object {"Get-Content " + """" + $_.FullName + """ -totalcount 3" > getHeaders.ps1}
function prompt { "Posh >" ; return " " }
#copy locally all unique file names
Get-ChildItem -Path \\server\ARC\Exp\AM\ -Recurse -Filter "*.txt" | ForEach-Object {"Copy " + $_.FullName + " ""\\Dest\temp\ExperiantRegistration\" + $_.Name + " " + $_.LastWriteTime + ".txt""" -replace ":", "" -replace "/", "" >> copyLocal.bat}
#Make the script file to Get just the top few lines
Get-ChildItem -Path C:\temp\ExperiantRegistration\ -Filter "*.txt" | ForEach-Object {"Get-Content " + """" + $_.FullName + """ -totalcount 3" > getHeaders.ps1}
Tuesday, July 14, 2015
Find Tables with Column Named
SELECT
c.name
, t.name
FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE c.name like '%sub%topi%' -- OBJ%' -- NOTE%'
ORDER BY
c.name
, t.name
c.name
, t.name
FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE c.name like '%sub%topi%' -- OBJ%' -- NOTE%'
ORDER BY
c.name
, t.name
Friday, July 10, 2015
SQL Server - Is DB Still Restoring?
SELECT DATABASEPROPERTYEX ('DBName', 'Status')
SELECT session_id SPID
, command
, a.text Query
, start_time
, percent_complete
, dateadd(second,estimated_completion_time/1000, getdate()) estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
SELECT session_id SPID
, command
, a.text Query
, start_time
, percent_complete
, dateadd(second,estimated_completion_time/1000, getdate()) estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
Wednesday, June 17, 2015
Find Table in Multiple Databases
http://stackoverflow.com/questions/610609/find-a-table-across-multiple-databases-sql-server-2005
sp_MSforeachdb 'USE ?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[table_name_here]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
PRINT ''Found in db ?''
END'
Or ...
USE master
GO
SELECT 'USE [' + name + '] ' + CHAR (13) + CHAR (10)
+ 'GO ' + CHAR (13) + CHAR (10)
+ 'SET NOCOUNT ON; SELECT DB_NAME(); SELECT name FROM sysobjects WHERE type = ''U'' AND name = ''some_table'' '
+ CHAR (13)
FROM sysdatabases
Monday, June 15, 2015
Find tables with foreign keys.
Source: http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
SELECT t.name TableWithForeignKey
, fk.constraint_column_id FK_PartNo
, c.name ForeignKeyColumn
FROM sys.foreign_key_columns fk
JOIN sys.tables t
ON fk.parent_object_id = t.[object_id]
JOIN sys.columns c
ON fk.parent_object_id = c.[object_id]
AND fk.parent_column_id = c.column_id
JOIN sys.tables t1
ON fk.referenced_object_id = t1.[object_id]
ORDER BY
TableWithForeignKey
, FK_PartNo
Source: http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
SELECT t.name TableWithForeignKey
, fk.constraint_column_id FK_PartNo
, c.name ForeignKeyColumn
FROM sys.foreign_key_columns fk
JOIN sys.tables t
ON fk.parent_object_id = t.[object_id]
JOIN sys.columns c
ON fk.parent_object_id = c.[object_id]
AND fk.parent_column_id = c.column_id
JOIN sys.tables t1
ON fk.referenced_object_id = t1.[object_id]
ORDER BY
TableWithForeignKey
, FK_PartNo
Tuesday, April 21, 2015
Tuesday, November 19, 2013
SSIS/SQLAgent - Enable User to See and Start Jobs
To see, the user must be in the SQLAgentReaderRole or SQLAgentUserRole, to start them the user must be in the SQLAgentOperaterRole. As these only exist in MSDB then the user must also be a member there.
Friday, November 8, 2013
Get and record all browser and session values
public static void recordUserVariables()
{
string sessionId = System.Web.HttpContext.Current.Session.SessionID,
sameSessionId = UtilFns.Common.GetSessionVariable("userSessionId");
bool recordSession = false;
/// The sess vars are apparently not alwas set when the user first gets to the portal
string temp_ss_Username_aspx = UtilFns.Common.GetSessionVariable("Username_aspx");
string repeatSessionRecording = UtilFns.Common.GetSessionVariable("repeatSessionRecording");
/// The sess vars are apparently not alwas set when the user first gets to the portal
/// so set a flag to repeat this when they are present
if (String.IsNullOrEmpty(temp_ss_Username_aspx))
{
UtilFns.Common.SetSessionVariable("repeatSessionRecording", "1");
}
if (repeatSessionRecording == "1")
{
recordSession = true;
UtilFns.Common.SetSessionVariable("repeatSessionRecording", "0");
}
if (String.IsNullOrEmpty(sameSessionId))
{
recordSession = true;
UtilFns.Common.SetSessionVariable("userSessionId", sessionId);
}
else if (sameSessionId != sessionId)
{
recordSession = true;
}
if (recordSession)
{
/// For recording user values, called from teh home page
string ss_Username_aspx = "",
ss_Email_aspx = "",
ss_IsAdmin_aspx = "",
ss_ActualUsername_aspx = "",
ss_ActualIsAdmin_aspx = "",
ss_FullName2_aspx = "",
ss_HRAdmin = "",
ss_eRF_Agency = "",
ss_BudEntryAlways = "",
sv_APPL_MD_PATH = "",
sv_APPL_PHYSICAL_PATH = "",
sv_AUTH_TYPE = "",
sv_AUTH_USER = "",
sv_INSTANCE_META_PATH = "",
sv_LOCAL_ADDR = "",
sv_LOGON_USER = "",
sv_REMOTE_ADDR = "",
sv_REMOTE_HOST = "",
sv_REMOTE_USER = "",
sv_REQUEST_METHOD = "",
sv_SCRIPT_NAME = "",
sv_SERVER_NAME = "",
sv_SERVER_PORT = "",
sv_SERVER_PROTOCOL = "",
sv_SERVER_SOFTWARE = "",
sv_URL = "",
sv_HTTP_CONNECTION = "",
sv_HTTP_ACCEPT = "",
sv_HTTP_ACCEPT_ENCODING = "",
sv_HTTP_ACCEPT_LANGUAGE = "",
sv_HTTP_COOKIE = "",
sv_HTTP_HOST = "",
sv_HTTP_USER_AGENT = "",
brwsr_computer_name = "",
brwsr_MachineName = "",
brwsr_Type = "",
brwsr_Browser = "",
brwsr_Version = "",
brwsr_MajorVersion = "",
brwsr_MinorVersion = "",
brwsr_Platform = "",
brwsr_Beta = "",
brwsr_Crawler = "",
brwsr_AOL = "",
brwsr_Win16 = "",
brwsr_Win32 = "",
brwsr_Frames = "",
brwsr_Tables = "",
brwsr_Cookies = "",
brwsr_VBScript = "",
brwsr_EcmaScriptVersion = "",
brwsr_JavaApplets = "",
brwsr_ActiveXControls = "",
brwsr_JavaScriptVersion = "";
try
{
ss_Username_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Username_aspx")))?
UtilFns.Common.GetSessionVariable("Username_aspx"): "";
ss_Email_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Email_aspx")))?
UtilFns.Common.GetSessionVariable("Email_aspx"): "";
ss_IsAdmin_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("IsAdmin_aspx")))?
UtilFns.Common.GetSessionVariable("IsAdmin_aspx"): "";
ss_ActualUsername_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Username_aspx")))?
UtilFns.Common.GetSessionVariable("Username_aspx"): "";
ss_ActualIsAdmin_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("ActualIsAdmin_aspx")))?
UtilFns.Common.GetSessionVariable("ActualIsAdmin_aspx"): "";
ss_FullName2_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("FullName2_aspx")))?
UtilFns.Common.GetSessionVariable("FullName2_aspx"): "";
ss_HRAdmin = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("HRAdmin")))?
UtilFns.Common.GetSessionVariable("HRAdmin"): "";
ss_eRF_Agency = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("eRF_Agency")))?
UtilFns.Common.GetSessionVariable("eRF_Agency"): "";
ss_BudEntryAlways = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("BudEntryAlways")))?
UtilFns.Common.GetSessionVariable("BudEntryAlways"): "";
sv_APPL_MD_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("APPL_MD_PATH")))?
UtilFns.Common.GetServerVariable("APPL_MD_PATH"): "";
sv_APPL_PHYSICAL_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("APPL_PHYSICAL_PATH")))?
UtilFns.Common.GetServerVariable("APPL_PHYSICAL_PATH"): "";
sv_AUTH_TYPE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("AUTH_TYPE")))?
UtilFns.Common.GetServerVariable("AUTH_TYPE"): "";
sv_AUTH_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("AUTH_USER")))?
UtilFns.Common.GetServerVariable("AUTH_USER"): "";
sv_INSTANCE_META_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("INSTANCE_META_PATH")))?
UtilFns.Common.GetServerVariable("INSTANCE_META_PATH"): "";
sv_LOCAL_ADDR = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("LOCAL_ADDR")))?
UtilFns.Common.GetServerVariable("LOCAL_ADDR"): "";
sv_LOGON_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("LOGON_USER")))?
UtilFns.Common.GetServerVariable("LOGON_USER"): "";
sv_REMOTE_ADDR = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_ADDR")))?
UtilFns.Common.GetServerVariable("REMOTE_ADDR"): "";
sv_REMOTE_HOST = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_HOST")))?
UtilFns.Common.GetServerVariable("REMOTE_HOST"): "";
sv_REMOTE_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_USER")))?
UtilFns.Common.GetServerVariable("REMOTE_USER"): "";
sv_REQUEST_METHOD = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REQUEST_METHOD")))?
UtilFns.Common.GetServerVariable("REQUEST_METHOD"): "";
sv_SCRIPT_NAME = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SCRIPT_NAME")))?
UtilFns.Common.GetServerVariable("SCRIPT_NAME"): "";
sv_SERVER_NAME = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_NAME")))?
UtilFns.Common.GetServerVariable("SERVER_NAME"): "";
sv_SERVER_PORT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_PORT")))?
UtilFns.Common.GetServerVariable("SERVER_PORT"): "";
sv_SERVER_PROTOCOL = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_PROTOCOL")))?
UtilFns.Common.GetServerVariable("SERVER_PROTOCOL"): "";
sv_SERVER_SOFTWARE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_SOFTWARE")))?
UtilFns.Common.GetServerVariable("SERVER_SOFTWARE"): "";
sv_URL = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("URL")))?
UtilFns.Common.GetServerVariable("URL"): "";
sv_HTTP_CONNECTION = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_CONNECTION")))?
UtilFns.Common.GetServerVariable("HTTP_CONNECTION"): "";
sv_HTTP_ACCEPT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT")))?
UtilFns.Common.GetServerVariable("HTTP_ACCEPT"): "";
sv_HTTP_ACCEPT_ENCODING = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT_ENCODING")))?
UtilFns.Common.GetServerVariable("HTTP_ACCEPT_ENCODING"): "";
sv_HTTP_ACCEPT_LANGUAGE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT_LANGUAGE")))?
UtilFns.Common.GetServerVariable("HTTP_ACCEPT_LANGUAGE"): "";
sv_HTTP_COOKIE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_COOKIE")))?
UtilFns.Common.GetServerVariable("HTTP_COOKIE"): "";
sv_HTTP_HOST = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_HOST")))?
UtilFns.Common.GetServerVariable("HTTP_HOST"): "";
sv_HTTP_USER_AGENT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_USER_AGENT")))?
UtilFns.Common.GetServerVariable("HTTP_USER_AGENT"): "";
string[] computer_name = System.Net.Dns.GetHostEntry
(HttpContext.Current.Request.ServerVariables["remote_addr"]).HostName.Split(new Char[] { '.' });
brwsr_computer_name = (!String.IsNullOrEmpty(computer_name[0].ToString()))?
computer_name[0].ToString(): "";
brwsr_MachineName = (!String.IsNullOrEmpty(System.Environment.MachineName))?
System.Environment.MachineName: "";
System.Web.HttpBrowserCapabilities browser = HttpContext.Current.Request.Browser;
brwsr_Type = (!String.IsNullOrEmpty(browser.Type.ToString()))?
browser.Type.ToString(): "";
brwsr_Browser = (!String.IsNullOrEmpty(browser.Browser.ToString()))?
browser.Browser.ToString(): "";
brwsr_Version = (!String.IsNullOrEmpty(browser.Version.ToString()))?
browser.Version.ToString(): "";
brwsr_MajorVersion = (!String.IsNullOrEmpty(browser.MajorVersion.ToString()))?
browser.MajorVersion.ToString(): "";
brwsr_MinorVersion = (!String.IsNullOrEmpty(browser.MinorVersion.ToString()))?
browser.MinorVersion.ToString(): "";
brwsr_Platform = (!String.IsNullOrEmpty(browser.Platform.ToString()))?
browser.Platform.ToString(): "";
brwsr_Beta = (!String.IsNullOrEmpty(browser.Beta.ToString()))?
browser.Beta.ToString(): "";
brwsr_Crawler = (!String.IsNullOrEmpty(browser.Crawler.ToString()))?
browser.Crawler.ToString(): "";
brwsr_AOL = (!String.IsNullOrEmpty(browser.AOL.ToString()))?
browser.AOL.ToString(): "";
brwsr_Win16 = (!String.IsNullOrEmpty(browser.Win16.ToString()))?
browser.Win16.ToString(): "";
brwsr_Win32 = (!String.IsNullOrEmpty(browser.Win32.ToString()))?
browser.Win32.ToString(): "";
brwsr_Frames = (!String.IsNullOrEmpty(browser.Frames.ToString()))?
browser.Frames.ToString(): "";
brwsr_Tables = (!String.IsNullOrEmpty(browser.Tables.ToString()))?
browser.Tables.ToString(): "";
brwsr_Cookies = (!String.IsNullOrEmpty(browser.Cookies.ToString()))?
browser.Cookies.ToString(): "";
brwsr_VBScript = (!String.IsNullOrEmpty(browser.VBScript.ToString()))?
browser.VBScript.ToString(): "";
brwsr_EcmaScriptVersion = (!String.IsNullOrEmpty(browser.EcmaScriptVersion.ToString()))?
browser.EcmaScriptVersion.ToString(): "";
brwsr_JavaApplets = (!String.IsNullOrEmpty(browser.JavaApplets.ToString()))?
browser.JavaApplets.ToString(): "";
brwsr_ActiveXControls = (!String.IsNullOrEmpty(browser.ActiveXControls.ToString()))?
browser.ActiveXControls.ToString(): "";
brwsr_JavaScriptVersion = (!String.IsNullOrEmpty(browser["JavaScriptVersion"].ToString()))?
browser["JavaScriptVersion"].ToString(): "";
string sSQL = @"foo.insBarBrowserValues "
+ " @sessionId = '" + sessionId + "'"
+ ", @ss_Username_aspx = '" + ss_Username_aspx + "'"
+ ", @ss_Email_aspx = '" + ss_Email_aspx + "'"
+ ", @ss_IsAdmin_aspx = '" + ss_IsAdmin_aspx + "'"
+ ", @ss_ActualUsername_aspx = '" + ss_ActualUsername_aspx + "'"
+ ", @ss_ActualIsAdmin_aspx = '" + ss_ActualIsAdmin_aspx + "'"
+ ", @ss_FullName2_aspx = '" + ss_FullName2_aspx + "'"
+ ", @ss_HRAdmin = '" + ss_HRAdmin + "'"
+ ", @ss_eRF_Agency = '" + ss_eRF_Agency + "'"
+ ", @ss_BudEntryAlways = '" + ss_BudEntryAlways + "'"
+ ", @sv_APPL_MD_PATH = '" + sv_APPL_MD_PATH + "'"
+ ", @sv_APPL_PHYSICAL_PATH = '" + sv_APPL_PHYSICAL_PATH + "'"
+ ", @sv_AUTH_TYPE = '" + sv_AUTH_TYPE + "'"
+ ", @sv_AUTH_USER = '" + sv_AUTH_USER + "'"
+ ", @sv_INSTANCE_META_PATH = '" + sv_INSTANCE_META_PATH + "'"
+ ", @sv_LOCAL_ADDR = '" + sv_LOCAL_ADDR + "'"
+ ", @sv_LOGON_USER = '" + sv_LOGON_USER + "'"
+ ", @sv_REMOTE_ADDR = '" + sv_REMOTE_ADDR + "'"
+ ", @sv_REMOTE_HOST = '" + sv_REMOTE_HOST + "'"
+ ", @sv_REMOTE_USER = '" + sv_REMOTE_USER + "'"
+ ", @sv_REQUEST_METHOD = '" + sv_REQUEST_METHOD + "'"
+ ", @sv_SCRIPT_NAME = '" + sv_SCRIPT_NAME + "'"
+ ", @sv_SERVER_NAME = '" + sv_SERVER_NAME + "'"
+ ", @sv_SERVER_PORT = '" + sv_SERVER_PORT + "'"
+ ", @sv_SERVER_PROTOCOL = '" + sv_SERVER_PROTOCOL + "'"
+ ", @sv_SERVER_SOFTWARE = '" + sv_SERVER_SOFTWARE + "'"
+ ", @sv_URL = '" + sv_URL + "'"
+ ", @sv_HTTP_CONNECTION = '" + sv_HTTP_CONNECTION + "'"
+ ", @sv_HTTP_ACCEPT = '" + sv_HTTP_ACCEPT + "'"
+ ", @sv_HTTP_ACCEPT_ENCODING = '" + sv_HTTP_ACCEPT_ENCODING + "'"
+ ", @sv_HTTP_ACCEPT_LANGUAGE = '" + sv_HTTP_ACCEPT_LANGUAGE + "'"
+ ", @sv_HTTP_COOKIE = '" + sv_HTTP_COOKIE + "'"
+ ", @sv_HTTP_HOST = '" + sv_HTTP_HOST + "'"
+ ", @sv_HTTP_USER_AGENT = '" + sv_HTTP_USER_AGENT + "'"
+ ", @brwsr_computer_name = '" + brwsr_computer_name + "'"
+ ", @brwsr_MachineName = '" + brwsr_MachineName + "'"
+ ", @brwsr_Type = '" + brwsr_Type + "'"
+ ", @brwsr_Browser = '" + brwsr_Browser + "'"
+ ", @brwsr_Version = '" + brwsr_Version + "'"
+ ", @brwsr_MajorVersion = '" + brwsr_MajorVersion + "'"
+ ", @brwsr_MinorVersion = '" + brwsr_MinorVersion + "'"
+ ", @brwsr_Platform = '" + brwsr_Platform + "'"
+ ", @brwsr_Beta = '" + brwsr_Beta + "'"
+ ", @brwsr_Crawler = '" + brwsr_Crawler + "'"
+ ", @brwsr_AOL = '" + brwsr_AOL + "'"
+ ", @brwsr_Win16 = '" + brwsr_Win16 + "'"
+ ", @brwsr_Win32 = '" + brwsr_Win32 + "'"
+ ", @brwsr_Frames = '" + brwsr_Frames + "'"
+ ", @brwsr_Tables = '" + brwsr_Tables + "'"
+ ", @brwsr_Cookies = '" + brwsr_Cookies + "'"
+ ", @brwsr_VBScript = '" + brwsr_VBScript + "'"
+ ", @brwsr_EcmaScriptVersion = '" + brwsr_EcmaScriptVersion + "'"
+ ", @brwsr_JavaApplets = '" + brwsr_JavaApplets + "'"
+ ", @brwsr_ActiveXControls = '" + brwsr_ActiveXControls + "'"
+ ", @brwsr_JavaScriptVersion = '" + brwsr_JavaScriptVersion + "'" ;
DataUtilities.utilSQL.DBExecNonQueryText(sSQL, "foo");
}
catch (Exception ex)
{
UtilFns.Common.LogAll("Error Recording User Values", ex.ToString(), UtilFns.Common.GetCurrentPageName(),
"Session ID: " + sessionId, true);
}
finally
{
;
}
}
}
--DROP TABLE foo.userBarValues
--USE foo
--GO
--CREATE TABLE foo.userBarValues (
-- ident BIGINT IDENTITY (1, 1)
--, sessionId VARCHAR(128) NOT NULL
--, ss_Username_aspx VARCHAR(128)
--, ss_Email_aspx VARCHAR(128)
--, ss_IsAdmin_aspx VARCHAR(2)
--, ss_ActualUsername_aspx VARCHAR(128)
--, ss_ActualIsAdmin_aspx VARCHAR(2)
--, ss_FullName2_aspx VARCHAR(128)
--, ss_HRAdmin VARCHAR(2)
--, ss_eRF_Agency VARCHAR(2)
--, ss_BudEntryAlways VARCHAR(2)
--, sv_APPL_MD_PATH VARCHAR(128)
--, sv_APPL_PHYSICAL_PATH VARCHAR(128)
--, sv_AUTH_TYPE VARCHAR(128)
--, sv_AUTH_USER VARCHAR(128)
--, sv_INSTANCE_META_PATH VARCHAR(128)
--, sv_LOCAL_ADDR VARCHAR(32)
--, sv_LOGON_USER VARCHAR(128)
--, sv_REMOTE_ADDR VARCHAR(32)
--, sv_REMOTE_HOST VARCHAR(32)
--, sv_REMOTE_USER VARCHAR(128)
--, sv_REQUEST_METHOD VARCHAR(16)
--, sv_SCRIPT_NAME VARCHAR(128)
--, sv_SERVER_NAME VARCHAR(128)
--, sv_SERVER_PORT VARCHAR(8)
--, sv_SERVER_PROTOCOL VARCHAR(16)
--, sv_SERVER_SOFTWARE VARCHAR(128)
--, sv_URL VARCHAR(128)
--, sv_HTTP_CONNECTION VARCHAR(128)
--, sv_HTTP_ACCEPT VARCHAR(1024)
--, sv_HTTP_ACCEPT_ENCODING VARCHAR(128)
--, sv_HTTP_ACCEPT_LANGUAGE VARCHAR(128)
--, sv_HTTP_COOKIE VARCHAR(1024)
--, sv_HTTP_HOST VARCHAR(128)
--, sv_HTTP_USER_AGENT VARCHAR(1024)
--, brwsr_computer_name VARCHAR(128)
--, brwsr_MachineName VARCHAR(128)
--, brwsr_Type VARCHAR(128)
--, brwsr_Browser VARCHAR(128)
--, brwsr_Version VARCHAR(16)
--, brwsr_MajorVersion VARCHAR(16)
--, brwsr_MinorVersion VARCHAR(16)
--, brwsr_Platform VARCHAR(128)
--, brwsr_Beta VARCHAR(16)
--, brwsr_Crawler VARCHAR(16)
--, brwsr_AOL VARCHAR(16)
--, brwsr_Win16 VARCHAR(16)
--, brwsr_Win32 VARCHAR(16)
--, brwsr_Frames VARCHAR(16)
--, brwsr_Tables VARCHAR(16)
--, brwsr_Cookies VARCHAR(16)
--, brwsr_VBScript VARCHAR(16)
--, brwsr_EcmaScriptVersion VARCHAR(16)
--, brwsr_JavaApplets VARCHAR(16)
--, brwsr_ActiveXControls VARCHAR(16)
--, brwsr_JavaScriptVersion VARCHAR(16)
--, crDate DATETIME DEFAULT GETDATE()
--)
USE PerformanceFoo
GO
CREATE PROC Foo.insBarBrowserValues (
@sessionId VARCHAR(128)
, @ss_Username_aspx VARCHAR(128)
, @ss_Email_aspx VARCHAR(128)
, @ss_IsAdmin_aspx VARCHAR(2)
, @ss_ActualUsername_aspx VARCHAR(128)
, @ss_ActualIsAdmin_aspx VARCHAR(2)
, @ss_FullName2_aspx VARCHAR(128)
, @ss_HRAdmin VARCHAR(2)
, @ss_eRF_Agency VARCHAR(2)
, @ss_BudEntryAlways VARCHAR(2)
, @sv_APPL_MD_PATH VARCHAR(128)
, @sv_APPL_PHYSICAL_PATH VARCHAR(128)
, @sv_AUTH_TYPE VARCHAR(128)
, @sv_AUTH_USER VARCHAR(128)
, @sv_INSTANCE_META_PATH VARCHAR(128)
, @sv_LOCAL_ADDR VARCHAR(32)
, @sv_LOGON_USER VARCHAR(128)
, @sv_REMOTE_ADDR VARCHAR(32)
, @sv_REMOTE_HOST VARCHAR(32)
, @sv_REMOTE_USER VARCHAR(128)
, @sv_REQUEST_METHOD VARCHAR(16)
, @sv_SCRIPT_NAME VARCHAR(128)
, @sv_SERVER_NAME VARCHAR(128)
, @sv_SERVER_PORT VARCHAR(8)
, @sv_SERVER_PROTOCOL VARCHAR(16)
, @sv_SERVER_SOFTWARE VARCHAR(128)
, @sv_URL VARCHAR(128)
, @sv_HTTP_CONNECTION VARCHAR(128)
, @sv_HTTP_ACCEPT VARCHAR(1024)
, @sv_HTTP_ACCEPT_ENCODING VARCHAR(128)
, @sv_HTTP_ACCEPT_LANGUAGE VARCHAR(128)
, @sv_HTTP_COOKIE VARCHAR(1024)
, @sv_HTTP_HOST VARCHAR(128)
, @sv_HTTP_USER_AGENT VARCHAR(1024)
, @brwsr_computer_name VARCHAR(128)
, @brwsr_MachineName VARCHAR(128)
, @brwsr_Type VARCHAR(128)
, @brwsr_Browser VARCHAR(128)
, @brwsr_Version VARCHAR(16)
, @brwsr_MajorVersion VARCHAR(16)
, @brwsr_MinorVersion VARCHAR(16)
, @brwsr_Platform VARCHAR(128)
, @brwsr_Beta VARCHAR(16)
, @brwsr_Crawler VARCHAR(16)
, @brwsr_AOL VARCHAR(16)
, @brwsr_Win16 VARCHAR(16)
, @brwsr_Win32 VARCHAR(16)
, @brwsr_Frames VARCHAR(16)
, @brwsr_Tables VARCHAR(16)
, @brwsr_Cookies VARCHAR(16)
, @brwsr_VBScript VARCHAR(16)
, @brwsr_EcmaScriptVersion VARCHAR(16)
, @brwsr_JavaApplets VARCHAR(16)
, @brwsr_ActiveXControls VARCHAR(16)
, @brwsr_JavaScriptVersion VARCHAR(16)
)
AS
BEGIN
/*************************************************************************
Joe Kelly
2013-11-08 15:50:13.990
For collecting data about the browser a user is employing, called from
the default page in the portal
*************************************************************************/
SET NOCOUNT ON
DECLARE @errorMsg VARCHAR (MAX) = ''
BEGIN TRY
INSERT Foo.userBarValues (
sessionId
, ss_Username_aspx
, ss_Email_aspx
, ss_IsAdmin_aspx
, ss_ActualUsername_aspx
, ss_ActualIsAdmin_aspx
, ss_FullName2_aspx
, ss_HRAdmin
, ss_eRF_Agency
, ss_BudEntryAlways
, sv_APPL_MD_PATH
, sv_APPL_PHYSICAL_PATH
, sv_AUTH_TYPE
, sv_AUTH_USER
, sv_INSTANCE_META_PATH
, sv_LOCAL_ADDR
, sv_LOGON_USER
, sv_REMOTE_ADDR
, sv_REMOTE_HOST
, sv_REMOTE_USER
, sv_REQUEST_METHOD
, sv_SCRIPT_NAME
, sv_SERVER_NAME
, sv_SERVER_PORT
, sv_SERVER_PROTOCOL
, sv_SERVER_SOFTWARE
, sv_URL
, sv_HTTP_CONNECTION
, sv_HTTP_ACCEPT
, sv_HTTP_ACCEPT_ENCODING
, sv_HTTP_ACCEPT_LANGUAGE
, sv_HTTP_COOKIE
, sv_HTTP_HOST
, sv_HTTP_USER_AGENT
, brwsr_computer_name
, brwsr_MachineName
, brwsr_Type
, brwsr_Browser
, brwsr_Version
, brwsr_MajorVersion
, brwsr_MinorVersion
, brwsr_Platform
, brwsr_Beta
, brwsr_Crawler
, brwsr_AOL
, brwsr_Win16
, brwsr_Win32
, brwsr_Frames
, brwsr_Tables
, brwsr_Cookies
, brwsr_VBScript
, brwsr_EcmaScriptVersion
, brwsr_JavaApplets
, brwsr_ActiveXControls
, brwsr_JavaScriptVersion
)
SELECT
@sessionId
, @ss_Username_aspx
, @ss_Email_aspx
, @ss_IsAdmin_aspx
, @ss_ActualUsername_aspx
, @ss_ActualIsAdmin_aspx
, @ss_FullName2_aspx
, @ss_HRAdmin
, @ss_eRF_Agency
, @ss_BudEntryAlways
, @sv_APPL_MD_PATH
, @sv_APPL_PHYSICAL_PATH
, @sv_AUTH_TYPE
, @sv_AUTH_USER
, @sv_INSTANCE_META_PATH
, @sv_LOCAL_ADDR
, @sv_LOGON_USER
, @sv_REMOTE_ADDR
, @sv_REMOTE_HOST
, @sv_REMOTE_USER
, @sv_REQUEST_METHOD
, @sv_SCRIPT_NAME
, @sv_SERVER_NAME
, @sv_SERVER_PORT
, @sv_SERVER_PROTOCOL
, @sv_SERVER_SOFTWARE
, @sv_URL
, @sv_HTTP_CONNECTION
, @sv_HTTP_ACCEPT
, @sv_HTTP_ACCEPT_ENCODING
, @sv_HTTP_ACCEPT_LANGUAGE
, @sv_HTTP_COOKIE
, @sv_HTTP_HOST
, @sv_HTTP_USER_AGENT
, @brwsr_computer_name
, @brwsr_MachineName
, @brwsr_Type
, @brwsr_Browser
, @brwsr_Version
, @brwsr_MajorVersion
, @brwsr_MinorVersion
, @brwsr_Platform
, @brwsr_Beta
, @brwsr_Crawler
, @brwsr_AOL
, @brwsr_Win16
, @brwsr_Win32
, @brwsr_Frames
, @brwsr_Tables
, @brwsr_Cookies
, @brwsr_VBScript
, @brwsr_EcmaScriptVersion
, @brwsr_JavaApplets
, @brwsr_ActiveXControls
, @brwsr_JavaScriptVersion
END TRY
BEGIN CATCH
--
SELECT @errorMsg = foo.fnGetErrorTryCatch(0)
SELECT @sessionId = 'Error logging values for session: ' + @sessionId
EXEC dbo.ins_sys_error_log
'foo.insBarBrowserValues'
, @sessionId
, 0
, @errorMsg
, 1
END CATCH
END
{
string sessionId = System.Web.HttpContext.Current.Session.SessionID,
sameSessionId = UtilFns.Common.GetSessionVariable("userSessionId");
bool recordSession = false;
/// The sess vars are apparently not alwas set when the user first gets to the portal
string temp_ss_Username_aspx = UtilFns.Common.GetSessionVariable("Username_aspx");
string repeatSessionRecording = UtilFns.Common.GetSessionVariable("repeatSessionRecording");
/// The sess vars are apparently not alwas set when the user first gets to the portal
/// so set a flag to repeat this when they are present
if (String.IsNullOrEmpty(temp_ss_Username_aspx))
{
UtilFns.Common.SetSessionVariable("repeatSessionRecording", "1");
}
if (repeatSessionRecording == "1")
{
recordSession = true;
UtilFns.Common.SetSessionVariable("repeatSessionRecording", "0");
}
if (String.IsNullOrEmpty(sameSessionId))
{
recordSession = true;
UtilFns.Common.SetSessionVariable("userSessionId", sessionId);
}
else if (sameSessionId != sessionId)
{
recordSession = true;
}
if (recordSession)
{
/// For recording user values, called from teh home page
string ss_Username_aspx = "",
ss_Email_aspx = "",
ss_IsAdmin_aspx = "",
ss_ActualUsername_aspx = "",
ss_ActualIsAdmin_aspx = "",
ss_FullName2_aspx = "",
ss_HRAdmin = "",
ss_eRF_Agency = "",
ss_BudEntryAlways = "",
sv_APPL_MD_PATH = "",
sv_APPL_PHYSICAL_PATH = "",
sv_AUTH_TYPE = "",
sv_AUTH_USER = "",
sv_INSTANCE_META_PATH = "",
sv_LOCAL_ADDR = "",
sv_LOGON_USER = "",
sv_REMOTE_ADDR = "",
sv_REMOTE_HOST = "",
sv_REMOTE_USER = "",
sv_REQUEST_METHOD = "",
sv_SCRIPT_NAME = "",
sv_SERVER_NAME = "",
sv_SERVER_PORT = "",
sv_SERVER_PROTOCOL = "",
sv_SERVER_SOFTWARE = "",
sv_URL = "",
sv_HTTP_CONNECTION = "",
sv_HTTP_ACCEPT = "",
sv_HTTP_ACCEPT_ENCODING = "",
sv_HTTP_ACCEPT_LANGUAGE = "",
sv_HTTP_COOKIE = "",
sv_HTTP_HOST = "",
sv_HTTP_USER_AGENT = "",
brwsr_computer_name = "",
brwsr_MachineName = "",
brwsr_Type = "",
brwsr_Browser = "",
brwsr_Version = "",
brwsr_MajorVersion = "",
brwsr_MinorVersion = "",
brwsr_Platform = "",
brwsr_Beta = "",
brwsr_Crawler = "",
brwsr_AOL = "",
brwsr_Win16 = "",
brwsr_Win32 = "",
brwsr_Frames = "",
brwsr_Tables = "",
brwsr_Cookies = "",
brwsr_VBScript = "",
brwsr_EcmaScriptVersion = "",
brwsr_JavaApplets = "",
brwsr_ActiveXControls = "",
brwsr_JavaScriptVersion = "";
try
{
ss_Username_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Username_aspx")))?
UtilFns.Common.GetSessionVariable("Username_aspx"): "";
ss_Email_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Email_aspx")))?
UtilFns.Common.GetSessionVariable("Email_aspx"): "";
ss_IsAdmin_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("IsAdmin_aspx")))?
UtilFns.Common.GetSessionVariable("IsAdmin_aspx"): "";
ss_ActualUsername_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("Username_aspx")))?
UtilFns.Common.GetSessionVariable("Username_aspx"): "";
ss_ActualIsAdmin_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("ActualIsAdmin_aspx")))?
UtilFns.Common.GetSessionVariable("ActualIsAdmin_aspx"): "";
ss_FullName2_aspx = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("FullName2_aspx")))?
UtilFns.Common.GetSessionVariable("FullName2_aspx"): "";
ss_HRAdmin = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("HRAdmin")))?
UtilFns.Common.GetSessionVariable("HRAdmin"): "";
ss_eRF_Agency = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("eRF_Agency")))?
UtilFns.Common.GetSessionVariable("eRF_Agency"): "";
ss_BudEntryAlways = (!String.IsNullOrEmpty(UtilFns.Common.GetSessionVariable("BudEntryAlways")))?
UtilFns.Common.GetSessionVariable("BudEntryAlways"): "";
sv_APPL_MD_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("APPL_MD_PATH")))?
UtilFns.Common.GetServerVariable("APPL_MD_PATH"): "";
sv_APPL_PHYSICAL_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("APPL_PHYSICAL_PATH")))?
UtilFns.Common.GetServerVariable("APPL_PHYSICAL_PATH"): "";
sv_AUTH_TYPE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("AUTH_TYPE")))?
UtilFns.Common.GetServerVariable("AUTH_TYPE"): "";
sv_AUTH_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("AUTH_USER")))?
UtilFns.Common.GetServerVariable("AUTH_USER"): "";
sv_INSTANCE_META_PATH = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("INSTANCE_META_PATH")))?
UtilFns.Common.GetServerVariable("INSTANCE_META_PATH"): "";
sv_LOCAL_ADDR = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("LOCAL_ADDR")))?
UtilFns.Common.GetServerVariable("LOCAL_ADDR"): "";
sv_LOGON_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("LOGON_USER")))?
UtilFns.Common.GetServerVariable("LOGON_USER"): "";
sv_REMOTE_ADDR = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_ADDR")))?
UtilFns.Common.GetServerVariable("REMOTE_ADDR"): "";
sv_REMOTE_HOST = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_HOST")))?
UtilFns.Common.GetServerVariable("REMOTE_HOST"): "";
sv_REMOTE_USER = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REMOTE_USER")))?
UtilFns.Common.GetServerVariable("REMOTE_USER"): "";
sv_REQUEST_METHOD = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("REQUEST_METHOD")))?
UtilFns.Common.GetServerVariable("REQUEST_METHOD"): "";
sv_SCRIPT_NAME = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SCRIPT_NAME")))?
UtilFns.Common.GetServerVariable("SCRIPT_NAME"): "";
sv_SERVER_NAME = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_NAME")))?
UtilFns.Common.GetServerVariable("SERVER_NAME"): "";
sv_SERVER_PORT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_PORT")))?
UtilFns.Common.GetServerVariable("SERVER_PORT"): "";
sv_SERVER_PROTOCOL = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_PROTOCOL")))?
UtilFns.Common.GetServerVariable("SERVER_PROTOCOL"): "";
sv_SERVER_SOFTWARE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("SERVER_SOFTWARE")))?
UtilFns.Common.GetServerVariable("SERVER_SOFTWARE"): "";
sv_URL = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("URL")))?
UtilFns.Common.GetServerVariable("URL"): "";
sv_HTTP_CONNECTION = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_CONNECTION")))?
UtilFns.Common.GetServerVariable("HTTP_CONNECTION"): "";
sv_HTTP_ACCEPT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT")))?
UtilFns.Common.GetServerVariable("HTTP_ACCEPT"): "";
sv_HTTP_ACCEPT_ENCODING = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT_ENCODING")))?
UtilFns.Common.GetServerVariable("HTTP_ACCEPT_ENCODING"): "";
sv_HTTP_ACCEPT_LANGUAGE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_ACCEPT_LANGUAGE")))?
UtilFns.Common.GetServerVariable("HTTP_ACCEPT_LANGUAGE"): "";
sv_HTTP_COOKIE = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_COOKIE")))?
UtilFns.Common.GetServerVariable("HTTP_COOKIE"): "";
sv_HTTP_HOST = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_HOST")))?
UtilFns.Common.GetServerVariable("HTTP_HOST"): "";
sv_HTTP_USER_AGENT = (!String.IsNullOrEmpty(UtilFns.Common.GetServerVariable("HTTP_USER_AGENT")))?
UtilFns.Common.GetServerVariable("HTTP_USER_AGENT"): "";
string[] computer_name = System.Net.Dns.GetHostEntry
(HttpContext.Current.Request.ServerVariables["remote_addr"]).HostName.Split(new Char[] { '.' });
brwsr_computer_name = (!String.IsNullOrEmpty(computer_name[0].ToString()))?
computer_name[0].ToString(): "";
brwsr_MachineName = (!String.IsNullOrEmpty(System.Environment.MachineName))?
System.Environment.MachineName: "";
System.Web.HttpBrowserCapabilities browser = HttpContext.Current.Request.Browser;
brwsr_Type = (!String.IsNullOrEmpty(browser.Type.ToString()))?
browser.Type.ToString(): "";
brwsr_Browser = (!String.IsNullOrEmpty(browser.Browser.ToString()))?
browser.Browser.ToString(): "";
brwsr_Version = (!String.IsNullOrEmpty(browser.Version.ToString()))?
browser.Version.ToString(): "";
brwsr_MajorVersion = (!String.IsNullOrEmpty(browser.MajorVersion.ToString()))?
browser.MajorVersion.ToString(): "";
brwsr_MinorVersion = (!String.IsNullOrEmpty(browser.MinorVersion.ToString()))?
browser.MinorVersion.ToString(): "";
brwsr_Platform = (!String.IsNullOrEmpty(browser.Platform.ToString()))?
browser.Platform.ToString(): "";
brwsr_Beta = (!String.IsNullOrEmpty(browser.Beta.ToString()))?
browser.Beta.ToString(): "";
brwsr_Crawler = (!String.IsNullOrEmpty(browser.Crawler.ToString()))?
browser.Crawler.ToString(): "";
brwsr_AOL = (!String.IsNullOrEmpty(browser.AOL.ToString()))?
browser.AOL.ToString(): "";
brwsr_Win16 = (!String.IsNullOrEmpty(browser.Win16.ToString()))?
browser.Win16.ToString(): "";
brwsr_Win32 = (!String.IsNullOrEmpty(browser.Win32.ToString()))?
browser.Win32.ToString(): "";
brwsr_Frames = (!String.IsNullOrEmpty(browser.Frames.ToString()))?
browser.Frames.ToString(): "";
brwsr_Tables = (!String.IsNullOrEmpty(browser.Tables.ToString()))?
browser.Tables.ToString(): "";
brwsr_Cookies = (!String.IsNullOrEmpty(browser.Cookies.ToString()))?
browser.Cookies.ToString(): "";
brwsr_VBScript = (!String.IsNullOrEmpty(browser.VBScript.ToString()))?
browser.VBScript.ToString(): "";
brwsr_EcmaScriptVersion = (!String.IsNullOrEmpty(browser.EcmaScriptVersion.ToString()))?
browser.EcmaScriptVersion.ToString(): "";
brwsr_JavaApplets = (!String.IsNullOrEmpty(browser.JavaApplets.ToString()))?
browser.JavaApplets.ToString(): "";
brwsr_ActiveXControls = (!String.IsNullOrEmpty(browser.ActiveXControls.ToString()))?
browser.ActiveXControls.ToString(): "";
brwsr_JavaScriptVersion = (!String.IsNullOrEmpty(browser["JavaScriptVersion"].ToString()))?
browser["JavaScriptVersion"].ToString(): "";
string sSQL = @"foo.insBarBrowserValues "
+ " @sessionId = '" + sessionId + "'"
+ ", @ss_Username_aspx = '" + ss_Username_aspx + "'"
+ ", @ss_Email_aspx = '" + ss_Email_aspx + "'"
+ ", @ss_IsAdmin_aspx = '" + ss_IsAdmin_aspx + "'"
+ ", @ss_ActualUsername_aspx = '" + ss_ActualUsername_aspx + "'"
+ ", @ss_ActualIsAdmin_aspx = '" + ss_ActualIsAdmin_aspx + "'"
+ ", @ss_FullName2_aspx = '" + ss_FullName2_aspx + "'"
+ ", @ss_HRAdmin = '" + ss_HRAdmin + "'"
+ ", @ss_eRF_Agency = '" + ss_eRF_Agency + "'"
+ ", @ss_BudEntryAlways = '" + ss_BudEntryAlways + "'"
+ ", @sv_APPL_MD_PATH = '" + sv_APPL_MD_PATH + "'"
+ ", @sv_APPL_PHYSICAL_PATH = '" + sv_APPL_PHYSICAL_PATH + "'"
+ ", @sv_AUTH_TYPE = '" + sv_AUTH_TYPE + "'"
+ ", @sv_AUTH_USER = '" + sv_AUTH_USER + "'"
+ ", @sv_INSTANCE_META_PATH = '" + sv_INSTANCE_META_PATH + "'"
+ ", @sv_LOCAL_ADDR = '" + sv_LOCAL_ADDR + "'"
+ ", @sv_LOGON_USER = '" + sv_LOGON_USER + "'"
+ ", @sv_REMOTE_ADDR = '" + sv_REMOTE_ADDR + "'"
+ ", @sv_REMOTE_HOST = '" + sv_REMOTE_HOST + "'"
+ ", @sv_REMOTE_USER = '" + sv_REMOTE_USER + "'"
+ ", @sv_REQUEST_METHOD = '" + sv_REQUEST_METHOD + "'"
+ ", @sv_SCRIPT_NAME = '" + sv_SCRIPT_NAME + "'"
+ ", @sv_SERVER_NAME = '" + sv_SERVER_NAME + "'"
+ ", @sv_SERVER_PORT = '" + sv_SERVER_PORT + "'"
+ ", @sv_SERVER_PROTOCOL = '" + sv_SERVER_PROTOCOL + "'"
+ ", @sv_SERVER_SOFTWARE = '" + sv_SERVER_SOFTWARE + "'"
+ ", @sv_URL = '" + sv_URL + "'"
+ ", @sv_HTTP_CONNECTION = '" + sv_HTTP_CONNECTION + "'"
+ ", @sv_HTTP_ACCEPT = '" + sv_HTTP_ACCEPT + "'"
+ ", @sv_HTTP_ACCEPT_ENCODING = '" + sv_HTTP_ACCEPT_ENCODING + "'"
+ ", @sv_HTTP_ACCEPT_LANGUAGE = '" + sv_HTTP_ACCEPT_LANGUAGE + "'"
+ ", @sv_HTTP_COOKIE = '" + sv_HTTP_COOKIE + "'"
+ ", @sv_HTTP_HOST = '" + sv_HTTP_HOST + "'"
+ ", @sv_HTTP_USER_AGENT = '" + sv_HTTP_USER_AGENT + "'"
+ ", @brwsr_computer_name = '" + brwsr_computer_name + "'"
+ ", @brwsr_MachineName = '" + brwsr_MachineName + "'"
+ ", @brwsr_Type = '" + brwsr_Type + "'"
+ ", @brwsr_Browser = '" + brwsr_Browser + "'"
+ ", @brwsr_Version = '" + brwsr_Version + "'"
+ ", @brwsr_MajorVersion = '" + brwsr_MajorVersion + "'"
+ ", @brwsr_MinorVersion = '" + brwsr_MinorVersion + "'"
+ ", @brwsr_Platform = '" + brwsr_Platform + "'"
+ ", @brwsr_Beta = '" + brwsr_Beta + "'"
+ ", @brwsr_Crawler = '" + brwsr_Crawler + "'"
+ ", @brwsr_AOL = '" + brwsr_AOL + "'"
+ ", @brwsr_Win16 = '" + brwsr_Win16 + "'"
+ ", @brwsr_Win32 = '" + brwsr_Win32 + "'"
+ ", @brwsr_Frames = '" + brwsr_Frames + "'"
+ ", @brwsr_Tables = '" + brwsr_Tables + "'"
+ ", @brwsr_Cookies = '" + brwsr_Cookies + "'"
+ ", @brwsr_VBScript = '" + brwsr_VBScript + "'"
+ ", @brwsr_EcmaScriptVersion = '" + brwsr_EcmaScriptVersion + "'"
+ ", @brwsr_JavaApplets = '" + brwsr_JavaApplets + "'"
+ ", @brwsr_ActiveXControls = '" + brwsr_ActiveXControls + "'"
+ ", @brwsr_JavaScriptVersion = '" + brwsr_JavaScriptVersion + "'" ;
DataUtilities.utilSQL.DBExecNonQueryText(sSQL, "foo");
}
catch (Exception ex)
{
UtilFns.Common.LogAll("Error Recording User Values", ex.ToString(), UtilFns.Common.GetCurrentPageName(),
"Session ID: " + sessionId, true);
}
finally
{
;
}
}
}
--DROP TABLE foo.userBarValues
--USE foo
--GO
--CREATE TABLE foo.userBarValues (
-- ident BIGINT IDENTITY (1, 1)
--, sessionId VARCHAR(128) NOT NULL
--, ss_Username_aspx VARCHAR(128)
--, ss_Email_aspx VARCHAR(128)
--, ss_IsAdmin_aspx VARCHAR(2)
--, ss_ActualUsername_aspx VARCHAR(128)
--, ss_ActualIsAdmin_aspx VARCHAR(2)
--, ss_FullName2_aspx VARCHAR(128)
--, ss_HRAdmin VARCHAR(2)
--, ss_eRF_Agency VARCHAR(2)
--, ss_BudEntryAlways VARCHAR(2)
--, sv_APPL_MD_PATH VARCHAR(128)
--, sv_APPL_PHYSICAL_PATH VARCHAR(128)
--, sv_AUTH_TYPE VARCHAR(128)
--, sv_AUTH_USER VARCHAR(128)
--, sv_INSTANCE_META_PATH VARCHAR(128)
--, sv_LOCAL_ADDR VARCHAR(32)
--, sv_LOGON_USER VARCHAR(128)
--, sv_REMOTE_ADDR VARCHAR(32)
--, sv_REMOTE_HOST VARCHAR(32)
--, sv_REMOTE_USER VARCHAR(128)
--, sv_REQUEST_METHOD VARCHAR(16)
--, sv_SCRIPT_NAME VARCHAR(128)
--, sv_SERVER_NAME VARCHAR(128)
--, sv_SERVER_PORT VARCHAR(8)
--, sv_SERVER_PROTOCOL VARCHAR(16)
--, sv_SERVER_SOFTWARE VARCHAR(128)
--, sv_URL VARCHAR(128)
--, sv_HTTP_CONNECTION VARCHAR(128)
--, sv_HTTP_ACCEPT VARCHAR(1024)
--, sv_HTTP_ACCEPT_ENCODING VARCHAR(128)
--, sv_HTTP_ACCEPT_LANGUAGE VARCHAR(128)
--, sv_HTTP_COOKIE VARCHAR(1024)
--, sv_HTTP_HOST VARCHAR(128)
--, sv_HTTP_USER_AGENT VARCHAR(1024)
--, brwsr_computer_name VARCHAR(128)
--, brwsr_MachineName VARCHAR(128)
--, brwsr_Type VARCHAR(128)
--, brwsr_Browser VARCHAR(128)
--, brwsr_Version VARCHAR(16)
--, brwsr_MajorVersion VARCHAR(16)
--, brwsr_MinorVersion VARCHAR(16)
--, brwsr_Platform VARCHAR(128)
--, brwsr_Beta VARCHAR(16)
--, brwsr_Crawler VARCHAR(16)
--, brwsr_AOL VARCHAR(16)
--, brwsr_Win16 VARCHAR(16)
--, brwsr_Win32 VARCHAR(16)
--, brwsr_Frames VARCHAR(16)
--, brwsr_Tables VARCHAR(16)
--, brwsr_Cookies VARCHAR(16)
--, brwsr_VBScript VARCHAR(16)
--, brwsr_EcmaScriptVersion VARCHAR(16)
--, brwsr_JavaApplets VARCHAR(16)
--, brwsr_ActiveXControls VARCHAR(16)
--, brwsr_JavaScriptVersion VARCHAR(16)
--, crDate DATETIME DEFAULT GETDATE()
--)
USE PerformanceFoo
GO
CREATE PROC Foo.insBarBrowserValues (
@sessionId VARCHAR(128)
, @ss_Username_aspx VARCHAR(128)
, @ss_Email_aspx VARCHAR(128)
, @ss_IsAdmin_aspx VARCHAR(2)
, @ss_ActualUsername_aspx VARCHAR(128)
, @ss_ActualIsAdmin_aspx VARCHAR(2)
, @ss_FullName2_aspx VARCHAR(128)
, @ss_HRAdmin VARCHAR(2)
, @ss_eRF_Agency VARCHAR(2)
, @ss_BudEntryAlways VARCHAR(2)
, @sv_APPL_MD_PATH VARCHAR(128)
, @sv_APPL_PHYSICAL_PATH VARCHAR(128)
, @sv_AUTH_TYPE VARCHAR(128)
, @sv_AUTH_USER VARCHAR(128)
, @sv_INSTANCE_META_PATH VARCHAR(128)
, @sv_LOCAL_ADDR VARCHAR(32)
, @sv_LOGON_USER VARCHAR(128)
, @sv_REMOTE_ADDR VARCHAR(32)
, @sv_REMOTE_HOST VARCHAR(32)
, @sv_REMOTE_USER VARCHAR(128)
, @sv_REQUEST_METHOD VARCHAR(16)
, @sv_SCRIPT_NAME VARCHAR(128)
, @sv_SERVER_NAME VARCHAR(128)
, @sv_SERVER_PORT VARCHAR(8)
, @sv_SERVER_PROTOCOL VARCHAR(16)
, @sv_SERVER_SOFTWARE VARCHAR(128)
, @sv_URL VARCHAR(128)
, @sv_HTTP_CONNECTION VARCHAR(128)
, @sv_HTTP_ACCEPT VARCHAR(1024)
, @sv_HTTP_ACCEPT_ENCODING VARCHAR(128)
, @sv_HTTP_ACCEPT_LANGUAGE VARCHAR(128)
, @sv_HTTP_COOKIE VARCHAR(1024)
, @sv_HTTP_HOST VARCHAR(128)
, @sv_HTTP_USER_AGENT VARCHAR(1024)
, @brwsr_computer_name VARCHAR(128)
, @brwsr_MachineName VARCHAR(128)
, @brwsr_Type VARCHAR(128)
, @brwsr_Browser VARCHAR(128)
, @brwsr_Version VARCHAR(16)
, @brwsr_MajorVersion VARCHAR(16)
, @brwsr_MinorVersion VARCHAR(16)
, @brwsr_Platform VARCHAR(128)
, @brwsr_Beta VARCHAR(16)
, @brwsr_Crawler VARCHAR(16)
, @brwsr_AOL VARCHAR(16)
, @brwsr_Win16 VARCHAR(16)
, @brwsr_Win32 VARCHAR(16)
, @brwsr_Frames VARCHAR(16)
, @brwsr_Tables VARCHAR(16)
, @brwsr_Cookies VARCHAR(16)
, @brwsr_VBScript VARCHAR(16)
, @brwsr_EcmaScriptVersion VARCHAR(16)
, @brwsr_JavaApplets VARCHAR(16)
, @brwsr_ActiveXControls VARCHAR(16)
, @brwsr_JavaScriptVersion VARCHAR(16)
)
AS
BEGIN
/*************************************************************************
Joe Kelly
2013-11-08 15:50:13.990
For collecting data about the browser a user is employing, called from
the default page in the portal
*************************************************************************/
SET NOCOUNT ON
DECLARE @errorMsg VARCHAR (MAX) = ''
BEGIN TRY
INSERT Foo.userBarValues (
sessionId
, ss_Username_aspx
, ss_Email_aspx
, ss_IsAdmin_aspx
, ss_ActualUsername_aspx
, ss_ActualIsAdmin_aspx
, ss_FullName2_aspx
, ss_HRAdmin
, ss_eRF_Agency
, ss_BudEntryAlways
, sv_APPL_MD_PATH
, sv_APPL_PHYSICAL_PATH
, sv_AUTH_TYPE
, sv_AUTH_USER
, sv_INSTANCE_META_PATH
, sv_LOCAL_ADDR
, sv_LOGON_USER
, sv_REMOTE_ADDR
, sv_REMOTE_HOST
, sv_REMOTE_USER
, sv_REQUEST_METHOD
, sv_SCRIPT_NAME
, sv_SERVER_NAME
, sv_SERVER_PORT
, sv_SERVER_PROTOCOL
, sv_SERVER_SOFTWARE
, sv_URL
, sv_HTTP_CONNECTION
, sv_HTTP_ACCEPT
, sv_HTTP_ACCEPT_ENCODING
, sv_HTTP_ACCEPT_LANGUAGE
, sv_HTTP_COOKIE
, sv_HTTP_HOST
, sv_HTTP_USER_AGENT
, brwsr_computer_name
, brwsr_MachineName
, brwsr_Type
, brwsr_Browser
, brwsr_Version
, brwsr_MajorVersion
, brwsr_MinorVersion
, brwsr_Platform
, brwsr_Beta
, brwsr_Crawler
, brwsr_AOL
, brwsr_Win16
, brwsr_Win32
, brwsr_Frames
, brwsr_Tables
, brwsr_Cookies
, brwsr_VBScript
, brwsr_EcmaScriptVersion
, brwsr_JavaApplets
, brwsr_ActiveXControls
, brwsr_JavaScriptVersion
)
SELECT
@sessionId
, @ss_Username_aspx
, @ss_Email_aspx
, @ss_IsAdmin_aspx
, @ss_ActualUsername_aspx
, @ss_ActualIsAdmin_aspx
, @ss_FullName2_aspx
, @ss_HRAdmin
, @ss_eRF_Agency
, @ss_BudEntryAlways
, @sv_APPL_MD_PATH
, @sv_APPL_PHYSICAL_PATH
, @sv_AUTH_TYPE
, @sv_AUTH_USER
, @sv_INSTANCE_META_PATH
, @sv_LOCAL_ADDR
, @sv_LOGON_USER
, @sv_REMOTE_ADDR
, @sv_REMOTE_HOST
, @sv_REMOTE_USER
, @sv_REQUEST_METHOD
, @sv_SCRIPT_NAME
, @sv_SERVER_NAME
, @sv_SERVER_PORT
, @sv_SERVER_PROTOCOL
, @sv_SERVER_SOFTWARE
, @sv_URL
, @sv_HTTP_CONNECTION
, @sv_HTTP_ACCEPT
, @sv_HTTP_ACCEPT_ENCODING
, @sv_HTTP_ACCEPT_LANGUAGE
, @sv_HTTP_COOKIE
, @sv_HTTP_HOST
, @sv_HTTP_USER_AGENT
, @brwsr_computer_name
, @brwsr_MachineName
, @brwsr_Type
, @brwsr_Browser
, @brwsr_Version
, @brwsr_MajorVersion
, @brwsr_MinorVersion
, @brwsr_Platform
, @brwsr_Beta
, @brwsr_Crawler
, @brwsr_AOL
, @brwsr_Win16
, @brwsr_Win32
, @brwsr_Frames
, @brwsr_Tables
, @brwsr_Cookies
, @brwsr_VBScript
, @brwsr_EcmaScriptVersion
, @brwsr_JavaApplets
, @brwsr_ActiveXControls
, @brwsr_JavaScriptVersion
END TRY
BEGIN CATCH
--
SELECT @errorMsg = foo.fnGetErrorTryCatch(0)
SELECT @sessionId = 'Error logging values for session: ' + @sessionId
EXEC dbo.ins_sys_error_log
'foo.insBarBrowserValues'
, @sessionId
, 0
, @errorMsg
, 1
END CATCH
END
Friday, September 27, 2013
IIS6 Max Buffer Output Size
http://support.microsoft.com/kb/925764
Increase the AspBufferingLimit value in the metabase or ...
To increase the buffering limit, follow these steps:
Increase the AspBufferingLimit value in the metabase or ...
Method 4: Increase the buffer limit
You can increase the buffering limit if one of the following conditions is true:- The client is not a Web browser.
- You cannot redesign the application to take advantage of a paging technology, such as the GridView class.
To increase the buffering limit, follow these steps:
- Click Start, click Run, type cmd, and then click OK.
- Type the following command, and then press ENTER:cd /d %systemdrive%\inetpub\adminscripts
- Type the following command, and then press ENTER:cscript.exe adsutil.vbs SET w3svc/aspbufferinglimit LimitSizeNote LimitSize represents the buffering limit size in bytes. For example, the number 67108864 sets the buffering limit size to 64 MB.
- Click Start, click Run, type cmd, and then click OK.
- Type the following command, and then press ENTER:cd /d %systemdrive%\inetpub\adminscripts
- Type the following command, and then press ENTER:cscript.exe adsutil.vbs GET w3svc/aspbufferinglimit
Thursday, August 29, 2013
Tool for Scripting View Generation
/******************************************************************************
Joe Kelly
2013-08-29 13:54:01.987
ScriptGen.sql
Given two databases, source and dest, this script will generate a READ ONLY
view in dest for each table or view in source.
Note:
- Existing schema.object definitions in dest will be overwritten
- If one tries to update one of the read only views an error as follows
will be generated:
Update or insert of view or function 'abc' failed because it
contains a derived or constant field.
this is due to the intentional ambiguity introduced to the view by a
union on the same object with a predicate of where 1 = 0
- Columns are not named because on larger objects the buffer can be
exceeded resulting in syntax errors (yes, it would better to do the
concatenation in an external program)
- For easier testing change the rowcount set
To Use:
Set the Custom variables and execute in text mode or to file, apply output carefully
@sourceDB SYSNAME = 'test'
, @destDB SYSNAME = 'test2'
, @sourceSchema SYSNAME = 'dbo'
, @destSchema SYSNAME = 'someSchema'
******************************************************************************/
USE test
GO
-- SET ROWCOUNT 30
SET ROWCOUNT 0
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Custom variables
DECLARE
@sourceDB SYSNAME = 'foo'
, @destDB SYSNAME = 'test2'
, @sourceSchema SYSNAME = 'dbo'
, @destSchema SYSNAME = 'someSchema'
-- System variables
DECLARE
@rowCount BIGINT = 0
, @rowIter BIGINT = 0
, @columnCount INT = 0
, @columnIter BIGINT = 0
, @tableString NVARCHAR (MAX) = ''
, @tableStringShort NVARCHAR (MAX) = ''
, @tableName SYSNAME = ''
, @tableType NVARCHAR (16) = ''
, @commandString NVARCHAR (MAX) = ''
, @use NVARCHAR (MAX) = ''
, @drop1 NVARCHAR (MAX) = 'IF OBJECT_ID('''
, @drop2 NVARCHAR (MAX) = ''') IS NOT NULL DROP VIEW '
SELECT @use = 'USE ' + @sourceDB
-- Ensure we are in the correct DB
EXECUTE sp_executesql @stmt = @use
IF OBJECT_ID('tempdb..#columns') IS NOT NULL
DROP TABLE #columns
IF OBJECT_ID('tempdb..#tables') IS NOT NULL
DROP TABLE #tables
IF OBJECT_ID('tempdb..#output') IS NOT NULL
DROP TABLE #output
CREATE TABLE #columns (
ident BIGINT IDENTITY (1, 1)
, TABLE_CATALOG SYSNAME
, TABLE_SCHEMA SYSNAME
, TABLE_NAME SYSNAME
, TABLE_TYPE SYSNAME
, COLUMN_NAME SYSNAME
, ORDINAL_POSITION INT
)
CREATE TABLE #tables (
ident BIGINT IDENTITY (1, 1)
, TABLE_CATALOG SYSNAME
, TABLE_SCHEMA SYSNAME
, TABLE_NAME SYSNAME
, TABLE_TYPE SYSNAME
, columnCount INT
, rowStart BIGINT
)
CREATE TABLE #output (
ident BIGINT IDENTITY (1, 1)
, command NVARCHAR (MAX)
)
-- Get a list of all the columns in the tables and views
INSERT #columns (
TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, TABLE_TYPE
, COLUMN_NAME
, ORDINAL_POSITION
)
SELECT
t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, t.TABLE_TYPE
, c.COLUMN_NAME
, c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
ORDER BY
t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
-- Get a list of all the distinct tables and views
INSERT #tables (
TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, TABLE_TYPE
, columnCount
, rowStart
)
SELECT DISTINCT
c.TABLE_CATALOG
, c.TABLE_SCHEMA
, c.TABLE_NAME
, c.TABLE_TYPE
, COUNT (c.TABLE_CATALOG)
, MIN (c.ident)
FROM #columns c
GROUP BY
c.TABLE_CATALOG
, c.TABLE_SCHEMA
, c.TABLE_NAME
, c.TABLE_TYPE
SELECT @rowCount = @@ROWCOUNT
-- Set the destination db for the output
INSERT #output ( command )
SELECT 'USE ' + @destDB
+ CHAR (10) -- + CHAR (13)
+ CHAR (10) -- + CHAR (13)
+ 'GO '
+ CHAR (10) -- + CHAR (13)
-- Ensure that the schema exists in the destination db
-- Create schema has to be the first command in a batch
-- so this will be a fatal error
INSERT #output ( command )
SELECT 'IF SCHEMA_ID('''
+ @destSchema
+ ''') IS NULL '
+ 'SELECT ''Destination Schema ['
+ @destSchema
+ '] does not extist'''
+ CHAR (10) -- + CHAR (13)
+ CHAR (10) -- + CHAR (13)
+ 'GO '
+ CHAR (10) -- + CHAR (13)
--+ 'CREATE SCHEMA '
--+ '['
--+ @destSchema
--+ '] AUTHORIZATION dbo '
--+ CHAR (10) -- + CHAR (13)
--+ CHAR (10) -- + CHAR (13)
--+ 'GO '
--+ CHAR (10) -- + CHAR (13)
-- Iterate through the list of tables (order is important)
WHILE @rowIter < @rowCount
BEGIN
SELECT @rowIter += 1
, @columnIter = 0
, @commandString = ''
SELECT @columnCount = t.columnCount
, @tableName = t.TABLE_NAME
, @tableType = t.TABLE_TYPE
, @tableString = '[' + @destDB + '].[' + @destSchema + '].[' + t.TABLE_NAME + ']'
, @tableStringShort = '[' + @destSchema + '].[' + t.TABLE_NAME + ']'
FROM #tables t
WHERE t.ident = @rowIter
SELECT @commandString += 'SELECT * '
-- This can produce text strings that are just too long fo the
-- SSMS editor so just use the wildcard ...
---- Iterate through the list of columns (order is important)
--WHILE @columnIter < @columnCount -1
--BEGIN
-- SELECT @columnIter += 1
-- -- Get all but the last column
-- SELECT @commandString += '['
-- + c.COLUMN_NAME
-- + '] /*'
-- + CAST (c.ORDINAL_POSITION AS VARCHAR (8))
-- + '*/ , '
-- FROM #columns c
-- WHERE c.TABLE_NAME = @tableName
-- AND c.ORDINAL_POSITION = @columnIter
--END
--SELECT @columnIter += 1
---- Now get the last column
--SELECT @commandString += '['
--+ c.COLUMN_NAME
--+ '] /*'
--+ CAST (c.ORDINAL_POSITION AS VARCHAR (8))
--+ '*/ '
--FROM #columns c
--WHERE c.TABLE_NAME = @tableName
-- AND c.ORDINAL_POSITION = @columnIter
-- Now build up the table name and attach
SELECT @commandString += ' FROM ['
+ @sourceDB
+ '].['
+ @sourceSchema
+ '].['
+ @tableName
+ ']'
-- Drop statements
INSERT #output ( command )
SELECT @drop1
+ @tableString
+ @drop2
+ @tableStringShort
+ ';'
+ CHAR (10) -- + CHAR (13)
+ CHAR (10) -- + CHAR (13)
+ 'GO '
+ CHAR (10) -- + CHAR (13)
INSERT #output ( command )
SELECT 'CREATE VIEW '
+ @tableStringShort
+ CHAR (10) -- + CHAR (13)
+ 'AS '
+ CHAR (10) -- + CHAR (13)
+ @commandString
+ CHAR (10) -- + CHAR (13)
+ 'UNION '
+ CHAR (10) -- + CHAR (13)
+ @commandString
+ ' WHERE 1 = 0; '
+ CHAR (10) -- + CHAR (13)
+ CHAR (10) -- + CHAR (13)
+ 'GO '
+ CHAR (10) -- + CHAR (13)
END
SELECT command '-- BE REALLY CAREFUL WHERE YOU RUN THIS ... '
FROM #output
Wednesday, July 31, 2013
T-SQL - Get RowCount from a Dynamically Executed Statement
DECLARE
@Period VARCHAR (8) = ''
, @DEDFYXXRecords BIGINT = 0
, @EXECFYXX NVARCHAR (MAX) = ''
, @RowCount BIGINT = 0;
SELECT @EXECFYXX = ' INSERT Data( '
+ ' Period '
+ ', CostCenter '
+ ') '
+ 'SELECT '
+ ' FYXX.Period '
+ ', FYXX.CostCenter '
+ 'FROM DataFY'
+ LEFT (@Period, 2)
+ ' FYXX '
+ ' JOIN DataB da '
+ ' ON FYXX.Account = da.Acct '
+ ' WHERE da.GroupID = 99 '
+ ' AND FYXX.Period = '''
+ @Period
+ '''; SELECT @RowCount = @@ROWCOUNT; ';
EXEC sp_executesql @EXECFYXX, N'@RowCount BIGINT OUTPUT', @RowCount OUTPUT
SELECT @DEDFYXXRecords = @RowCount
Tuesday, July 30, 2013
Classic ASP DB Conn Cheat
For those time when you gotta go back but don't want to make it too bad...
Scenario: Classic ASP ap (perhaps mixed with .net) that has BO layer in ActiveX dll's (VB, of all things). You want perform some follow-up work after the dll method has run.
We could parse web.config for the connection string and then reformat for ADO ... or we could just use a separate connection string and store it in an include file:
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Joe Kelly
' 2013.07.30
' Classic.inc
'
' Changed to use ../Include/Classic.inc so as to have one place to manage
' connection strings for Classic ASP
'
' Page Admin_Imp_Expense.asp, part of the "Monthly Expense Detail" process
' uses this file
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim connPortal
connPortal = "Provider=SQLNCLI10;Server=TESTSQL;Database=TESTPortal;Uid=TESTUser;Pwd=TESTPw;"
%>
And then to use it ...
Dim conn, cmd, result
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
conn.ConnectionString = connPortal
conn.Open
cmd.ActiveConnection = conn
cmd.CommandText = "EXEC dbo.someProc; "
Set result = cmd.Execute
If result.EOF = False Then
While result.EOF = False
Response.Write ("")
Response.Write (result.Fields("DEDFYXXRecords"))
result.MoveNext
WEnd
End If
conn.Close
Set conn = Nothing
Set cmd = Nothing
Scenario: Classic ASP ap (perhaps mixed with .net) that has BO layer in ActiveX dll's (VB, of all things). You want perform some follow-up work after the dll method has run.
We could parse web.config for the connection string and then reformat for ADO ... or we could just use a separate connection string and store it in an include file:
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Joe Kelly
' 2013.07.30
' Classic.inc
'
' Changed to use ../Include/Classic.inc so as to have one place to manage
' connection strings for Classic ASP
'
' Page Admin_Imp_Expense.asp, part of the "Monthly Expense Detail" process
' uses this file
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim connPortal
connPortal = "Provider=SQLNCLI10;Server=TESTSQL;Database=TESTPortal;Uid=TESTUser;Pwd=TESTPw;"
%>
And then to use it ...
Dim conn, cmd, result
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")
conn.ConnectionString = connPortal
conn.Open
cmd.ActiveConnection = conn
cmd.CommandText = "EXEC dbo.someProc; "
Set result = cmd.Execute
If result.EOF = False Then
While result.EOF = False
Response.Write ("")
Response.Write (result.Fields("DEDFYXXRecords"))
result.MoveNext
WEnd
End If
conn.Close
Set conn = Nothing
Set cmd = Nothing
Tuesday, July 23, 2013
SQL Server - Currently Running Jobs
-- How to query currently running SQL Server Agent jobs
-- Source: http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/
-- Source: http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/
IF EXISTS (
SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL
, last_run_date nvarchar (20) NOT NULL
, last_run_time nvarchar (20) NOT NULL
, next_run_date nvarchar (20) NOT NULL
, next_run_time nvarchar (20) 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
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6)
, next_run_time = right ('000000' + next_run_time, 6);
SELECT j.name AS JobName
, j.enabled AS Enabled
, CASE x.running
WHEN 1
THEN 'Running'
ELSE
CASE h.run_status
WHEN 2
THEN 'Inactive'
WHEN 4
THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus
, coalesce (x.current_step, 0) AS CurrentStepNbr
, CASE
WHEN x.last_run_date > 0
THEN convert (datetime, substring (x.last_run_date, 1, 4)
+ '-' + substring (x.last_run_date, 5, 2)
+ '-' + substring (x.last_run_date, 7, 2)
+ ' ' + substring (x.last_run_time, 1, 2)
+ ':' + substring (x.last_run_time, 3, 2)
+ ':' + substring (x.last_run_time, 5, 2)
+ '.000', 121)
ELSE
NULL
END AS LastRunTime
, CASE h.run_status
WHEN 0
THEN 'Fail'
WHEN 1
THEN 'Success'
WHEN 2
THEN 'Retry'
WHEN 3
THEN 'Cancel'
WHEN 4
THEN 'In progress'
END AS LastRunOutcome
, CASE
WHEN h.run_duration > 0
THEN (h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT JOIN msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT JOIN msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where x.running = 1
Subscribe to:
Posts (Atom)