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
Wednesday, April 27, 2011
Thursday, April 21, 2011
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();
jQuery.expr[':'].asp = function(elem, i, match) {
return (elem.id && elem.id.match(match[3] + "$"));
};
alert($(":asp(fy)").val());
fy = $(":asp(h1)").val();
Tuesday, April 19, 2011
.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));
}
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));
}
Thursday, April 7, 2011
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
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
Wednesday, March 30, 2011
Tuesday, March 29, 2011
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'
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where class_desc = 'schema'
Monday, March 21, 2011
SQL Server Installation information
SELECT SERVERPROPERTY('productversion')
, SERVERPROPERTY ('productlevel')
, SERVERPROPERTY ('edition')
, SERVERPROPERTY ('InstanceName')
, SERVERPROPERTY ('LCID')
, SERVERPROPERTY ('MachineName')
, SERVERPROPERTY ('LicenseType')
, SERVERPROPERTY ('NumLicenses')
, SERVERPROPERTY ('productlevel')
, SERVERPROPERTY ('edition')
, SERVERPROPERTY ('InstanceName')
, SERVERPROPERTY ('LCID')
, SERVERPROPERTY ('MachineName')
, SERVERPROPERTY ('LicenseType')
, SERVERPROPERTY ('NumLicenses')
Tuesday, March 15, 2011
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
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
Monday, March 14, 2011
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' ;
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' ;
Thursday, March 3, 2011
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
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
Friday, February 25, 2011
Wednesday, February 23, 2011
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
GO
Wednesday, February 9, 2011
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
C:\WINDOWS\PCHealth\HelpCtr\Binaries\HelpCtr.exe
VS 9.0 reset:
C:\Program Files\Common Files\Microsoft Shared\VS Help Data\9.0
Thursday, January 20, 2011
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)= ', '
*/
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%'
, 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%'
Wednesday, January 12, 2011
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
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
Wednesday, January 5, 2011
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
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
Tuesday, January 4, 2011
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
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
Wednesday, December 29, 2010
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]).
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]).
Tuesday, November 23, 2010
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
@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
Thursday, November 18, 2010
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;
}
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;
}
Thursday, November 4, 2010
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 ...
.............
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.
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.
Friday, October 22, 2010
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)
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
, 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
Friday, October 8, 2010
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
+ 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
Thursday, October 7, 2010
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
, 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
Friday, October 1, 2010
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];
}
{
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];
}
Thursday, September 30, 2010
Friday, September 24, 2010
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
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
Thursday, September 23, 2010
Where Are the Constraints?
SELECT
CAST (DATEPART(yyyy, so.crdate) AS VARCHAR) + ' - '
+ CAST (DATEPART(mm, so.crdate) AS VARCHAR) + ' - '
+ CAST( DATEPART(dd, so.crdate) AS VARCHAR) so_crdate
, su.name so_schema
, so.name so_name
, scc.name sc_name
, sct.text sct_constraint
FROM sysobjects so
JOIN syscolumns scc
ON so.id = scc.id
LEFT OUTER JOIN sysusers su
ON so.uid = su.uid
LEFT OUTER JOIN sysconstraints sc
ON so.id = sc.id
AND scc.colid = sc.colid
LEFT OUTER JOIN syscomments sct
ON sc.constid = sct.id
WHERE so.type = 'U'
AND so.crdate > '2010-07-09'
AND so.name NOT LIKE '%bak%'
AND so.name NOT LIKE '%junk%'
AND so.name NOT LIKE '%sav%'
AND su.name = 'dbo'
AND sct.text IS NOT NULL
ORDER BY
so.crdate
, so.name
, scc.name DESC
CAST (DATEPART(yyyy, so.crdate) AS VARCHAR) + ' - '
+ CAST (DATEPART(mm, so.crdate) AS VARCHAR) + ' - '
+ CAST( DATEPART(dd, so.crdate) AS VARCHAR) so_crdate
, su.name so_schema
, so.name so_name
, scc.name sc_name
, sct.text sct_constraint
FROM sysobjects so
JOIN syscolumns scc
ON so.id = scc.id
LEFT OUTER JOIN sysusers su
ON so.uid = su.uid
LEFT OUTER JOIN sysconstraints sc
ON so.id = sc.id
AND scc.colid = sc.colid
LEFT OUTER JOIN syscomments sct
ON sc.constid = sct.id
WHERE so.type = 'U'
AND so.crdate > '2010-07-09'
AND so.name NOT LIKE '%bak%'
AND so.name NOT LIKE '%junk%'
AND so.name NOT LIKE '%sav%'
AND su.name = 'dbo'
AND sct.text IS NOT NULL
ORDER BY
so.crdate
, so.name
, scc.name DESC
Wednesday, September 22, 2010
Add Default Value to Existing Column
ALTER TABLE zeroTest WITH NOCHECK
ADD CONSTRAINT DF_Zero DEFAULT 0 FOR c
ADD CONSTRAINT DF_Zero DEFAULT 0 FOR c
Thursday, July 29, 2010
Remove Items in a Select Box, IE compliant
function showAllDates() {
var findSel = new Object();
findSel = document.getElementById("idHAllDates")
if (findSel) {
if (findSel.value != 1){
RemoveDates();
}
}
else {
// default
RemoveDates();
}
}
function RemoveDates(){
var nowDate = new Date();
var nowDate2 = new Date(nowDate.getYear(), nowDate.getMonth(), nowDate.getDate());
var findSel = document.getElementById("idSelPEDate");
var tempString = "";
if (findSel)
{
// This does not work in IE
// for (var i = 1; i < findSel.options.length; i++) {
// leave the first option empty
for (var i = findSel.options.length - 1; i >=1; i--) {
// yy/mm/dd : input is in the form of mm/dd/yy
tempString = findSel.options[i].value;
tempString = tempString.substring(0, 2) + "/"
+ tempString.substring(3, 5) + "/"
+ "20" + tempString.substring(6, 8);
var compDate = new Date(tempString);
// alert(nowDate2 + " " + compDate + " " + tempString + " " + findSel.options[i].value);
if (compDate > nowDate2){
alert("bigger: " + compDate);
findSel.remove(i);
}
}
}
}
var findSel = new Object();
findSel = document.getElementById("idHAllDates")
if (findSel) {
if (findSel.value != 1){
RemoveDates();
}
}
else {
// default
RemoveDates();
}
}
function RemoveDates(){
var nowDate = new Date();
var nowDate2 = new Date(nowDate.getYear(), nowDate.getMonth(), nowDate.getDate());
var findSel = document.getElementById("idSelPEDate");
var tempString = "";
if (findSel)
{
// This does not work in IE
// for (var i = 1; i < findSel.options.length; i++) {
// leave the first option empty
for (var i = findSel.options.length - 1; i >=1; i--) {
// yy/mm/dd : input is in the form of mm/dd/yy
tempString = findSel.options[i].value;
tempString = tempString.substring(0, 2) + "/"
+ tempString.substring(3, 5) + "/"
+ "20" + tempString.substring(6, 8);
var compDate = new Date(tempString);
// alert(nowDate2 + " " + compDate + " " + tempString + " " + findSel.options[i].value);
if (compDate > nowDate2){
alert("bigger: " + compDate);
findSel.remove(i);
}
}
}
}
Thursday, July 15, 2010
ctrl home opens find and replace
[tools] [options] [general] - turn off the "Navigation for Word Perfect users" checkbox
Wednesday, July 14, 2010
Explorer.exe Customization
Target: %SystemRoot%\explorer.exe /n, /e, /select, D:\Temp\Joe\Code\
Start in: %HOMEDRIVE%%HOMEPATH%
Start in: %HOMEDRIVE%%HOMEPATH%
Tuesday, July 13, 2010
ASP and .Net BE DLLs Interop
Check out, optionally version #.
If changing #, change # for namespace, assembly and default namespace.
Build
Copy to [server] C:\Web_DSS_Files\DLLs>
Put in GAC
C:\Web_DSS_Files\DLLs>gacutil /if PMMaccr09.dll
Register for COM Interop
C:\Web_DSS_Files\DLLs>regasm PMMaccr09.dll /tlb:PMMaccr09.dll
Test
If changing #, change # for namespace, assembly and default namespace.
Build
Copy to [server] C:\Web_DSS_Files\DLLs>
Put in GAC
C:\Web_DSS_Files\DLLs>gacutil /if PMMaccr09.dll
Register for COM Interop
C:\Web_DSS_Files\DLLs>regasm PMMaccr09.dll /tlb:PMMaccr09.dll
Test
Thursday, July 8, 2010
Debugging Classic ASP With Visual Studio 2008 SP1 and 3.5 Framework
http://blogs.bitwizards.com/Kevin_Grohoske/post/2009/04/30/Debugging-Classic-ASP-With-Visual-Studio-2008-SP1-and-35-Framework.aspx
Thursday, April 30, 2009 06:22 | posted by: kevin
As much as I’d like never to debug complicated classic ASP code again, the fact is it’s everywhere in the enterprise today. Here is one way that I have found to speed up the process of supporting classic ASP w/ VS 2008 SP1/3.5.
At the last User Group meeting, I presented the features in the VS08 SP1 and 3.5 Framework. One topic/feature was only lightly covered in the documentation, but really jumped out at me, was that with Visual Studio 2008 SP 1 and 3.5 Framework VS 2008 can debug classic ASP code (script). I tried to find more information online, but the details were hard to find.So after a bit of research and trial and error I am sharing what I learned with you!
Here’s How:
1. Allow Server Side Debugging inside IIS Manager for the web site.
IIS Manager Settings
2. Open the Web Site in VS 2008 IDE by File - Open - Web Site and browsing to the directory that the IIS’s web is pointed to.
Open Web Site
3. Accept FrameworkUpgrade Warning (if prompted).
Framework Upgrade Warning
4. Configure Web Site Startup Properties to open correct website through IIS url (http://localhost/…)
Web Site Properties
5. Run Web Site in Debug Mode (F5) and accept Web.Config warning.
Allow .NET Debugging
6. In VS 2008 IDE Debug Menu select Attach To Process and choose the dllhost.exe process
Attach to dllhost.exe process
7. Begin Debugging By Setting Breakpoints in the IDE.
Visual Studio 2008 Debugging
That’s is !!! Ok so what changes were made to the original process?
When you exit the it VS 08 will prompt you to save a solution file.
And… A web.config for the .NET Debugger will be created in the root of the web. You should remove it when you deploy to production.
Thursday, April 30, 2009 06:22 | posted by: kevin
As much as I’d like never to debug complicated classic ASP code again, the fact is it’s everywhere in the enterprise today. Here is one way that I have found to speed up the process of supporting classic ASP w/ VS 2008 SP1/3.5.
At the last User Group meeting, I presented the features in the VS08 SP1 and 3.5 Framework. One topic/feature was only lightly covered in the documentation, but really jumped out at me, was that with Visual Studio 2008 SP 1 and 3.5 Framework VS 2008 can debug classic ASP code (script). I tried to find more information online, but the details were hard to find.So after a bit of research and trial and error I am sharing what I learned with you!
Here’s How:
1. Allow Server Side Debugging inside IIS Manager for the web site.
IIS Manager Settings
2. Open the Web Site in VS 2008 IDE by File - Open - Web Site and browsing to the directory that the IIS’s web is pointed to.
Open Web Site
3. Accept FrameworkUpgrade Warning (if prompted).
Framework Upgrade Warning
4. Configure Web Site Startup Properties to open correct website through IIS url (http://localhost/…)
Web Site Properties
5. Run Web Site in Debug Mode (F5) and accept Web.Config warning.
Allow .NET Debugging
6. In VS 2008 IDE Debug Menu select Attach To Process and choose the dllhost.exe process
Attach to dllhost.exe process
7. Begin Debugging By Setting Breakpoints in the IDE.
Visual Studio 2008 Debugging
That’s is !!! Ok so what changes were made to the original process?
When you exit the it VS 08 will prompt you to save a solution file.
And… A web.config for the .NET Debugger will be created in the root of the web. You should remove it when you deploy to production.
IIS Log and Last Stop - Restart
Log:
IIS - Site properties - Web Site - Log Properties
C:\WINDOWS\system32\LogFiles nad then the specific site file name w3svcxyz\abc.log
Last Stop - Restart
Event Viewer - System - Filter for IISCTLS
IIS - Site properties - Web Site - Log Properties
C:\WINDOWS\system32\LogFiles nad then the specific site file name w3svcxyz\abc.log
Last Stop - Restart
Event Viewer - System - Filter for IISCTLS
Wednesday, July 7, 2010
Command Line Process and IIS Tips
http://todotnet.com/post/2006/07/02/TipTrick-List-Running-ASPNET-Worker-Processes-and-KillRestart-them-from-the-command-line-a-better-way.aspx
Recycle Ap Pool and others ...
Tip/Trick: List Running ASP.NET Worker Processes and Kill/Restart them from the command-line [a better way]
by Sander Gerz July 02, 2006 19:37
Scott Guthrie posts a trick on a quick way to kill a process on your system, or kill and restart an ASP.NET or IIS worker process. I tried to post a comment on his trick, but the commenting system is not working. So I'll give my opinion here, leaving me with a bit more room to elaborate.
Scott's suggesting that you use taskkill to kill a process running the application pool. That's all nice and neat, but how do you know what process to kill? If you have multiple application pools, you might just kill the wrong one. A much better solution is to use the little known iisapp command. In fact, iisapp is a vb-script located in %winnt%\system32. Run it from the command prompt without parameters, and you get a list of application pools with their associated process ids.
C:\WINDOWS\system32>iisapp
W3WP.exe PID: 3328 AppPoolId: DefaultAppPool
W3WP.exe PID: 232 AppPoolId: AppPool ASPNET2.0
The command IIsApp /a DefaultAppPool /r will recycle the specified application pool. Not only is this a lot easier, it's less error prone, thus safer to use. What if you kill the wrong process? I.e. by mistyping or by the fact that after you listed your processes, the application pool has recycled already.
There are a few other commands that few are aware of. E.g.
issweb /query
This will give you a list of configured websites, their status, port number and hostheader. You can also use iisweb to create, pause, stop, start and delete websites. iisvdir will do something similar for virtual folders.
With iisback you can backup and restore the IIS configuration. In fact, if you do a listing of .vbs-files from within %winnt%\system32 you may find some other hidden gems.
Hope this helps... too.
Sander
Recycle Ap Pool and others ...
Tip/Trick: List Running ASP.NET Worker Processes and Kill/Restart them from the command-line [a better way]
by Sander Gerz July 02, 2006 19:37
Scott Guthrie posts a trick on a quick way to kill a process on your system, or kill and restart an ASP.NET or IIS worker process. I tried to post a comment on his trick, but the commenting system is not working. So I'll give my opinion here, leaving me with a bit more room to elaborate.
Scott's suggesting that you use taskkill to kill a process running the application pool. That's all nice and neat, but how do you know what process to kill? If you have multiple application pools, you might just kill the wrong one. A much better solution is to use the little known iisapp command. In fact, iisapp is a vb-script located in %winnt%\system32. Run it from the command prompt without parameters, and you get a list of application pools with their associated process ids.
C:\WINDOWS\system32>iisapp
W3WP.exe PID: 3328 AppPoolId: DefaultAppPool
W3WP.exe PID: 232 AppPoolId: AppPool ASPNET2.0
The command IIsApp /a DefaultAppPool /r will recycle the specified application pool. Not only is this a lot easier, it's less error prone, thus safer to use. What if you kill the wrong process? I.e. by mistyping or by the fact that after you listed your processes, the application pool has recycled already.
There are a few other commands that few are aware of. E.g.
issweb /query
This will give you a list of configured websites, their status, port number and hostheader. You can also use iisweb to create, pause, stop, start and delete websites. iisvdir will do something similar for virtual folders.
With iisback you can backup and restore the IIS configuration. In fact, if you do a listing of .vbs-files from within %winnt%\system32 you may find some other hidden gems.
Hope this helps... too.
Sander
Find Process Locking DLL
http://blogs.msdn.com/b/winclient/archive/2004/07/08/177947.aspx
tasklist /m thelocked.dll
tasklist /m thelocked.dll
Thursday, July 1, 2010
Friday, May 21, 2010
Merge - Upsert
MERGE INTO mergeD AS d
USING mergeS AS s
ON s.a = d.a
WHEN matched THEN
UPDATE SET d.achar = s.achar
WHEN NOT MATCHED THEN
INSERT (achar) VALUES (achar);
USING mergeS AS s
ON s.a = d.a
WHEN matched THEN
UPDATE SET d.achar = s.achar
WHEN NOT MATCHED THEN
INSERT (achar) VALUES (achar);
Quick Find Tables w/ Column Name Like
-- Utility: Quick Find Tables w/ Column Name Like
SELECT
'SELECT * FROM '
, t.TABLE_NAME
, 'WHERE Username LIKE ''kellyjo%'''
, ' -- '
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS 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 = 'BASE TABLE'
AND t.TABLE_CATALOG = 'foo'
AND c.COLUMN_NAME LIKE '%bar%'
SELECT
'SELECT * FROM '
, t.TABLE_NAME
, 'WHERE Username LIKE ''kellyjo%'''
, ' -- '
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS 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 = 'BASE TABLE'
AND t.TABLE_CATALOG = 'foo'
AND c.COLUMN_NAME LIKE '%bar%'
Tuesday, May 18, 2010
Pre and Post pend Text to String
Sub preNPost()
' pre and post pend text to a line
Dim StartLine, EndLine, CurrentLine
StartLine = DTE.ActiveDocument.Selection.TopLine
DTE.ActiveDocument.Selection.EndOfDocument(True)
EndLine = DTE.ActiveDocument.Selection.BottomLine
DTE.ActiveDocument.Selection.StartOfDocument()
CurrentLine = DTE.ActiveDocument.Selection.CurrentLine
While (CurrentLine < EndLine)
DTE.ActiveDocument.Selection.StartOfLine(vsStartOfLineOptions.vsStartOfLineOptionsFirstText)
DTE.ActiveDocument.Selection.Text = "+ zzzz "
DTE.ActiveDocument.Selection.EndOfLine()
DTE.ActiveDocument.Selection.Text = " yyyy"
DTE.ActiveDocument.Selection.LineDown()
CurrentLine = DTE.ActiveDocument.Selection.CurrentLine
End While
End Sub
' pre and post pend text to a line
Dim StartLine, EndLine, CurrentLine
StartLine = DTE.ActiveDocument.Selection.TopLine
DTE.ActiveDocument.Selection.EndOfDocument(True)
EndLine = DTE.ActiveDocument.Selection.BottomLine
DTE.ActiveDocument.Selection.StartOfDocument()
CurrentLine = DTE.ActiveDocument.Selection.CurrentLine
While (CurrentLine < EndLine)
DTE.ActiveDocument.Selection.StartOfLine(vsStartOfLineOptions.vsStartOfLineOptionsFirstText)
DTE.ActiveDocument.Selection.Text = "+ zzzz "
DTE.ActiveDocument.Selection.EndOfLine()
DTE.ActiveDocument.Selection.Text = " yyyy"
DTE.ActiveDocument.Selection.LineDown()
CurrentLine = DTE.ActiveDocument.Selection.CurrentLine
End While
End Sub
Friday, May 14, 2010
ASP 0131 Disallowed Parent Path
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q226/4/74.asp&NoWebContent=1
When you use relative paths in include statements with Microsoft Active Server Pages (ASP), browsing a Web page may return an error message similar to the following:
Active Server Pages, ASP 0131
Disallowed Parent Path
The Include file '../' cannot contain '..' to indicate the parent directory.
//, line
Back to the top
CAUSE
This is caused by disabling ASP's "parent paths" for a Web site or application...
This is caused by disabling ASP's "parent paths" for a Web site or application while using relative parent paths in an include statement.
Relative parent paths in include statements use the following form:
Back to the top
RESOLUTION
The best solution to the problem is to use absolute virtual paths from the root...
The best solution to the problem is to use absolute virtual paths from the root of the Web site instead of relative paths.
For example, if you use an include file named "mycode.inc" at the root of your server, the virtual path would be "/mycode.inc." If you use the same include file in a virtual directory named "/includes" on your server, the virtual path would be "/includes/mycode.inc."
The syntax example below illustrates how to implement virtual paths:
An alternative to using absolute virtual paths is to enable parent paths; however, this is not the preferred method. (See the notes in the More Information section for details.) This is accomplished for your default Web site by using the following steps:
Back to the top
Internet Information Services 7.0
1. Start Internet Services Manager.
2. Click Default Web Site, and then click Properties.
3. Double-click ASP in the Features pane.
4. Expand Behavior.
5. Click Enable Parent Paths.
6. Click True for Enable Parent Paths.
7. Click Apply.
Back to the top
Internet Information Services 6.0
1. Open the Internet Services Manager in the Microsoft Management Console (MMC).
2. Right-click on your Default Web Site and select Properties.
3. Click the Home Directory tab.
4. Click the Configuration button.
5. Click the App Options tab.
6. Click to select the Enable Parent Paths checkbox.
7. Click the OK button until you return to the MMC.
Back to the top
When you use relative paths in include statements with Microsoft Active Server Pages (ASP), browsing a Web page may return an error message similar to the following:
Active Server Pages, ASP 0131
Disallowed Parent Path
The Include file '../
/
Back to the top
CAUSE
This is caused by disabling ASP's "parent paths" for a Web site or application...
This is caused by disabling ASP's "parent paths" for a Web site or application while using relative parent paths in an include statement.
Relative parent paths in include statements use the following form:
Back to the top
RESOLUTION
The best solution to the problem is to use absolute virtual paths from the root...
The best solution to the problem is to use absolute virtual paths from the root of the Web site instead of relative paths.
For example, if you use an include file named "mycode.inc" at the root of your server, the virtual path would be "/mycode.inc." If you use the same include file in a virtual directory named "/includes" on your server, the virtual path would be "/includes/mycode.inc."
The syntax example below illustrates how to implement virtual paths:
An alternative to using absolute virtual paths is to enable parent paths; however, this is not the preferred method. (See the notes in the More Information section for details.) This is accomplished for your default Web site by using the following steps:
Back to the top
Internet Information Services 7.0
1. Start Internet Services Manager.
2. Click Default Web Site, and then click Properties.
3. Double-click ASP in the Features pane.
4. Expand Behavior.
5. Click Enable Parent Paths.
6. Click True for Enable Parent Paths.
7. Click Apply.
Back to the top
Internet Information Services 6.0
1. Open the Internet Services Manager in the Microsoft Management Console (MMC).
2. Right-click on your Default Web Site and select Properties.
3. Click the Home Directory tab.
4. Click the Configuration button.
5. Click the App Options tab.
6. Click to select the Enable Parent Paths checkbox.
7. Click the OK button until you return to the MMC.
Back to the top
Utility: Quick Find Tables w/ Column Name Like
SELECT
'SELECT * FROM '
, t.TABLE_NAME
, 'WHERE Username LIKE ''kellyjo%'''
, ' -- '
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS 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 = 'BASE TABLE'
AND t.TABLE_CATALOG = 'Budget'
AND c.COLUMN_NAME LIKE '%user%'
'SELECT * FROM '
, t.TABLE_NAME
, 'WHERE Username LIKE ''kellyjo%'''
, ' -- '
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS 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 = 'BASE TABLE'
AND t.TABLE_CATALOG = 'Budget'
AND c.COLUMN_NAME LIKE '%user%'
Wednesday, May 12, 2010
How to Find the Process ID for the IIS Ap Pool
C:\WINDOWS\system32>cscript.exe iisapp.vbs
Note that if the process is not hosting any threads (or instances of a site) at that moment then will/may not show.
Note that if the process is not hosting any threads (or instances of a site) at that moment then will/may not show.
Debug Process to Attatch to
Particularly useful when traipsing through (inherited) project-less sites with embedded C# (no code-behind)...
For remote debugging of managed code
aspnet_wp.exe - XP
w3pw.exe - Vista / 2k3+
(Note that msvsmon.exe must be running on remote machine)
And for the JS attach to the client process (say IE) (especially when you cannot use something like FireBug because corp. only wants to support IE).
For remote debugging of managed code
aspnet_wp.exe - XP
w3pw.exe - Vista / 2k3+
(Note that msvsmon.exe must be running on remote machine)
And for the JS attach to the client process (say IE) (especially when you cannot use something like FireBug because corp. only wants to support IE).
Friday, May 7, 2010
SQL Server 2008 Linked Servers
2005
http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/
2008
http://mssqltips.com/tip.asp?tip=1673
http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/
2008
http://mssqltips.com/tip.asp?tip=1673
Schema Syntax
SELECT COUNT (*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_CATALOG = 'foo'
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_CATALOG = 'foo'
Thursday, May 6, 2010
Tuesday, May 4, 2010
MSC Commands from the Command Line i.e. AD Users
Thanks to Mitch Tulloch www.mtit.com/mitch/ and his link to :
http://www.windowsnetworking.com/kbase/WindowsTips/Windows2003/AdminTips/Admin/LaunchAdminToolsfromtheCommandLine.html
AD Domains and Trusts
domain.msc
Active Directory Management
admgmt.msc
AD Sites and Serrvices
dssite.msc
AD Users and COmputers
dsa.msc
ADSI Edit
adsiedit.msc
Authorization manager
azman.msc
Certification Authority Management
certsrv.msc
Certificate Templates
certtmpl.msc
Cluster Administrator
cluadmin.exe
Computer Management
compmgmt.msc
Component Services
comexp.msc
Configure Your Server
cys.exe
Device Manager
devmgmt.msc
DHCP Managment
dhcpmgmt.msc
Disk Defragmenter
dfrg.msc
Disk Manager
diskmgmt.msc
Distributed File System
dfsgui.msc
DNS Managment
dnsmgmt.msc
Event Viewer
eventvwr.msc
Indexing Service Management
ciadv.msc
IP Address Manage
ipaddrmgmt.msc
Licensing Manager
llsmgr.exe
Local Certificates Management
certmgr.msc
Local Group Policy Editor
gpedit.msc
Local Security Settings Manager
secpol.msc
Local Users and Groups Manager
lusrmgr.msc
Network Load balancing
nlbmgr.exe
Performance Montior
perfmon.msc
PKI Viewer
pkiview.msc
Public Key Managment
pkmgmt.msc
QoS Control Management
acssnap.msc
Remote Desktops
tsmmc.msc
Remote Storage Administration
rsadmin.msc
Removable Storage
ntmsmgr.msc
Removalbe Storage Operator Requests
ntmsoprq.msc
Routing and Remote Access Manager
rrasmgmt.msc
Resultant Set of Policy
rsop.msc
Schema management
schmmgmt.msc
Services Management
services.msc
Shared Folders
fsmgmt.msc
SID Security Migration
sidwalk.msc
Telephony Management
tapimgmt.msc
Terminal Server Configuration
tscc.msc
Terminal Server Licensing
licmgr.exe
Terminal Server Manager
tsadmin.exe
UDDI Services Managment
uddi.msc
Windows Mangement Instumentation
wmimgmt.msc
WINS Server manager
winsmgmt.msc
http://www.windowsnetworking.com/kbase/WindowsTips/Windows2003/AdminTips/Admin/LaunchAdminToolsfromtheCommandLine.html
AD Domains and Trusts
domain.msc
Active Directory Management
admgmt.msc
AD Sites and Serrvices
dssite.msc
AD Users and COmputers
dsa.msc
ADSI Edit
adsiedit.msc
Authorization manager
azman.msc
Certification Authority Management
certsrv.msc
Certificate Templates
certtmpl.msc
Cluster Administrator
cluadmin.exe
Computer Management
compmgmt.msc
Component Services
comexp.msc
Configure Your Server
cys.exe
Device Manager
devmgmt.msc
DHCP Managment
dhcpmgmt.msc
Disk Defragmenter
dfrg.msc
Disk Manager
diskmgmt.msc
Distributed File System
dfsgui.msc
DNS Managment
dnsmgmt.msc
Event Viewer
eventvwr.msc
Indexing Service Management
ciadv.msc
IP Address Manage
ipaddrmgmt.msc
Licensing Manager
llsmgr.exe
Local Certificates Management
certmgr.msc
Local Group Policy Editor
gpedit.msc
Local Security Settings Manager
secpol.msc
Local Users and Groups Manager
lusrmgr.msc
Network Load balancing
nlbmgr.exe
Performance Montior
perfmon.msc
PKI Viewer
pkiview.msc
Public Key Managment
pkmgmt.msc
QoS Control Management
acssnap.msc
Remote Desktops
tsmmc.msc
Remote Storage Administration
rsadmin.msc
Removable Storage
ntmsmgr.msc
Removalbe Storage Operator Requests
ntmsoprq.msc
Routing and Remote Access Manager
rrasmgmt.msc
Resultant Set of Policy
rsop.msc
Schema management
schmmgmt.msc
Services Management
services.msc
Shared Folders
fsmgmt.msc
SID Security Migration
sidwalk.msc
Telephony Management
tapimgmt.msc
Terminal Server Configuration
tscc.msc
Terminal Server Licensing
licmgr.exe
Terminal Server Manager
tsadmin.exe
UDDI Services Managment
uddi.msc
Windows Mangement Instumentation
wmimgmt.msc
WINS Server manager
winsmgmt.msc
Monday, May 3, 2010
logMe
namespace Util
{
public class util
{
public static void logMe(string msg, string usr, string fPath)
{
try
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(fPath, true))
{
DateTime dNow = DateTime.Now;
file.WriteLine(dNow + " | " + usr + " | " + msg);
}
}
catch (Exception ex)
{
Debug.WriteLine (ex.ToString());
throw;
}
}
}
}
{
public class util
{
public static void logMe(string msg, string usr, string fPath)
{
try
{
using (System.IO.StreamWriter file = new System.IO.StreamWriter(fPath, true))
{
DateTime dNow = DateTime.Now;
file.WriteLine(dNow + " | " + usr + " | " + msg);
}
}
catch (Exception ex)
{
Debug.WriteLine (ex.ToString());
throw;
}
}
}
}
Monday, April 26, 2010
Registering DLLs, .Net w/ COM
old: regsvr32 LC_Pro08.dll
.Net C:\Web_DSS_Files\DLLs>gacutil /i PMMaccr08.dll
C:\Web_DSS_Files\DLLs>regasm PMMaccr08.dll /tlb:PMMaccr08.dll
.Net C:\Web_DSS_Files\DLLs>gacutil /i PMMaccr08.dll
C:\Web_DSS_Files\DLLs>regasm PMMaccr08.dll /tlb:PMMaccr08.dll
Friday, April 23, 2010
Tuesday, January 13, 2009
IIS & Port Errors: IIS Cannot create a file when that file already exists
IIS & Port Errors: IIS Cannot create a file when that file already exists
Same port numbers must use same protocol, even with different IP’s
For example:
10.0.0.11:333 (http) 10.0.0.11:444 (https)
10.0.0.12:444 (http) 10.0.0.12:333 (https)
Will result in the below error:
Also: "iis cannot create a file when that file already exists"
If using a seperate/alternate IP make sure that the "SecureBindings" and "ServerBindings" values have both been written to the metabase correctly.
I.E.:
SecureBindings="10.102.169.24:443:"
ServerBindings="10.102.169.24:80:"
Vs.:
ecureBindings=":443:"
ServerBindings="10.102.169.24:80:"
Same port numbers must use same protocol, even with different IP’s
For example:
10.0.0.11:333 (http) 10.0.0.11:444 (https)
10.0.0.12:444 (http) 10.0.0.12:333 (https)
Will result in the below error:
Also: "iis cannot create a file when that file already exists"
If using a seperate/alternate IP make sure that the "SecureBindings" and "ServerBindings" values have both been written to the metabase correctly.
I.E.:
SecureBindings="10.102.169.24:443:"
ServerBindings="10.102.169.24:80:"
Vs.:
ecureBindings=":443:"
ServerBindings="10.102.169.24:80:"
Subscribe to:
Posts (Atom)