http://beyondrelational.com/justlearned/posts/792/sql-server-to-get-comma-separated-value-for-every-column-name-in-tables-with-t-sql.aspx
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+', ' ,'') + Name
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID)='dat_groups'
SELECT @listStr
Monday, November 14, 2011
Wednesday, November 9, 2011
T-SQL Count Spaces in String
http://stanbiron.com/2010/07/29/SimpleWayToCountCharactersAndWordsUsingTSQL.aspx
-- Count the number of words
DECLARE @String VARCHAR(100)
SELECT @String = 'SQL Server 2005 Stan test code'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1
-- Count the number of words
DECLARE @String VARCHAR(100)
SELECT @String = 'SQL Server 2005 Stan test code'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1
Wednesday, November 2, 2011
Longest Running Queries
http://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx#bm1151148
SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 average_seconds
, qs.total_elapsed_time / 1000000.0 total_seconds
, qs.execution_count
, SUBSTRING (qt.text,qs.statement_start_offset/2
, ( CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
)/2
) individual_query
, o.name object_name
, DB_NAME(qt.dbid) database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
LEFT OUTER JOIN sys.objects o
ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC
SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 average_seconds
, qs.total_elapsed_time / 1000000.0 total_seconds
, qs.execution_count
, SUBSTRING (qt.text,qs.statement_start_offset/2
, ( CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
)/2
) individual_query
, o.name object_name
, DB_NAME(qt.dbid) database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
LEFT OUTER JOIN sys.objects o
ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC
SSMS REGEX - Remove Carriage Returns
http://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/
\n @\n
\n
\n @\n
\n
SQL Regex
http://beyondrelational.com/blogs/naomi/archive/2011/10/19/remove-bad-characters-from-a-string.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Nov02
01.CREATE FUNCTION dbo.RemoveBadChars
02. (@String VARCHAR(8000),
03. @AllowedPattern VARCHAR(100))
04.RETURNS VARCHAR(8000)
05.AS
06. BEGIN
07. DECLARE @nBadChars INT,
08. @Rest VARCHAR(100)
09.
10. SET @String = '!@#$% 123 ??abc##rd,,,'
11.
12. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
13.
14. WHILE @nBadChars > 0
15. BEGIN
16. SET @Rest = Substring(@String,@nBadChars + 1,Len(@String))
17.
18. SET @String = Substring(@String,1,@nBadChars - 1)
19.
20. SET @Rest = Substring(@Rest,Patindex('%[' + @AllowedPattern + ']%',@Rest),
21. Len(@Rest))
22.
23. SET @String = @String + @Rest
24.
25. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
26. END
27.
28. RETURN @String
29. END
30.
31.GO
1.SELECT dbo.RemoveBadChars('1234@@@@@@@@@????????djwkejwk^&*-+=a',' 0-9a-z')
01.CREATE FUNCTION dbo.RemoveBadChars
02. (@String VARCHAR(8000),
03. @AllowedPattern VARCHAR(100))
04.RETURNS VARCHAR(8000)
05.AS
06. BEGIN
07. DECLARE @nBadChars INT,
08. @Rest VARCHAR(100)
09.
10. SET @String = '!@#$% 123 ??abc##rd,,,'
11.
12. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
13.
14. WHILE @nBadChars > 0
15. BEGIN
16. SET @Rest = Substring(@String,@nBadChars + 1,Len(@String))
17.
18. SET @String = Substring(@String,1,@nBadChars - 1)
19.
20. SET @Rest = Substring(@Rest,Patindex('%[' + @AllowedPattern + ']%',@Rest),
21. Len(@Rest))
22.
23. SET @String = @String + @Rest
24.
25. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
26. END
27.
28. RETURN @String
29. END
30.
31.GO
1.SELECT dbo.RemoveBadChars('1234@@@@@@@@@????????djwkejwk^&*-+=a',' 0-9a-z')
Friday, October 28, 2011
Wednesday, October 19, 2011
SQL Server Good Guidance on Missing Indices
http://beyondrelational.com/blogs/martinschmidt/archive/2011/09/27/missing-indexes.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Oct18
One of the most common things I encounter when asked to help with performance problems, is wrong or inadequate indexing. Creating the optimal indexes for a system, is no where near a trivial excercise. You need to consider the read/write ratio as well as how you write your queries. This is not the scope of this blog post.
SQL Server has a number of dynamic management views/functions, which is very useful for different purposes. At least if you know they are there, and how to use them. For missing index information, these views are important:
view source
print?
1.SELECT * FROM sys.dm_db_missing_index_group_stats
2.SELECT * FROM sys.dm_db_missing_index_details
3.SELECT * FROM sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats tracks information about indexes which could have been useful to the SQL Server. It holds information about how many times the SQL Server could have used that index, and how great an impact it would have had on the query. This DMV does not give you the details about the table and columns that the index should have been created on.
This is however available in the sys.dm_db_missing_index_details DMV, where you get information about the database_id, object_id and the columns in the index.
The last DMV is only used to bind the two DMV’s together.
The DMV’s contains data on server level, so it will show details about missing index in all your databases.
Let me show how a basic query on these DMV’s could look like:
view source
print?
1.SELECT *
2.FROM
3. sys.dm_db_missing_index_groups g
4. INNER JOIN sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle
5. INNER JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
On my local SQL Server, this gives me an empty resultset:
So, let me create some test data:
view source
print?
01.CREATE DATABASE IndexDemo
02.GO
03.USE IndexDemo
04.GO
05.CREATE TABLE DemoData (
06. Id INT IDENTITY PRIMARY KEY,
07. Val1 INT,
08. Val2 INT,
09. Val3 CHAR(4000)
10.)
11.GO
12.INSERT INTO DemoData (Val1, Val2, Val3)
13.VALUES (1,1, '')
14.GO
15.
16.INSERT INTO DemoData (Val1, Val2, Val3)
17.SELECT Val1, Val2, Val3
18.FROM DemoData
19.GO 15
I have created a table with a clustered index on the Id column, and filled the table with 32K rows. Now I will create a query to return the Id and Val1 column, given a specific value for Val2:
view source
print?
1.SELECT Id, Val1
2.FROM DemoData
3.WHERE Val2 = 123
So, now let me try to run the missing index query again, but this time only looking at a limited number of columns:
view source
print?
01.SELECT
02. user_seeks,
03. last_user_seek,
04. avg_total_user_cost,
05. avg_user_impact,
06. database_id,
07. object_id,
08. equality_columns,
09. inequality_columns,
10. included_columns
11.FROM
12. sys.dm_db_missing_index_groups g
13. INNER JOIN sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle
14. INNER JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
Now one row is returned. Let’s go through the returned columns, and see what the values mean:
user_seeks
This tells you how many times the SQL Server could have used this missing index to lookup data. The higher this number is, the more reason to build the index. How high this is, depends on your system. On high volume OLTP setups, numbers of less the 10.000 is not worth worrying about, and on smaller systems a value of 20-100 might be high enough to consider the index.
last_user_seek
This tells you when the missing index was last needed. If this value is equal to last saturday at night, it _could_ mean that you have a nightly job running on saturday, which might need this index. avg_total_user_cost
This value does not have a unit, but it’s a value that tells you something about the estimated cost of the queries that needed the index. The higher the value is, the more resource intensive is the query that needs the index. If the value is very low (perhaps 0.01) then the query that needed the index was not very resource intensive. Think of a query that executes in 2ms compared to a query that takes 2000ms. You will probably gain more by adding an index to satisfy the 2000ms query than the 2ms one.
avg_user_impact
This is the expected improvement of the query if you build the query. The closer the value is to 100, the more the query will benefit from building the index.
database_id
This is the database where you need to build the index.
object_id
This is the object_id of the table that could need an index.
equality_columns
These columns are the ones that you should build the index on. Equality columns means that the predicate used the column like this: “Val2 = x”, which was exactly how I wrote my demo query.
inequality_columns
You should also add these columns to your index. Inequality columns are used for range predicates, like: “Val2 > x”. My missing index query returned NULL in this column, because my demo query had no such predicate.
included_columns
If columns are used in the SELECT part of your query, and not in the predicate, you can add these columns as included columns in the index. This way all needed data is available in the index, and no key lookup is needed on the clustered index. But beware! The more columns you add to your index, the more write IO and space you need. My rule of thumb says, that the total number of columns (equality_columns + inequality_columns + included_columns) should not exceed 5. If the total number of columns exceeds 5, that does not mean that you should not build the index – it just means that you need to think carefully about what you are doing!
So, normally I run this query, and returns the rows ordered by user_seeks desc. Then I look at the top 10-20 rows, and see if any of them have avg_user_impact in the 90-100 range. Then I look for the number of columns in the index, and then I decide whether or not the index should be build. This is NOT a query that gives you the answer to everything, but it can definately help you spot the indexes that you should add to your system.
Finally you can actually modify the query to give you the full CREATE INDEX statement, so you just need to copy that to a query window and execute it. The full query I usually use, looks like this:
view source
print?
01.DECLARE @DBName VARCHAR(100)
02.DECLARE @TableName VARCHAR(100)
03.--If this line is commented in, the missing index list will only contain
04.--missing indexes for the given database
05.--SET @DBName = 'MyDatabase'
06.--SET @TableName = 'MyTable'
07.
08.;WITH CTE
09.AS
10.(
11.SELECT
12. DB_NAME(d.database_id) AS DatabaseName,
13. user_seeks,
14. user_scans,
15. avg_total_user_cost,
16. avg_user_impact,
17. d.equality_columns,
18. d.inequality_columns,
19. d.included_columns,
20. 'USE ' + DB_NAME(d.database_id) + '; CREATE NONCLUSTERED INDEX IX_' +
21. replace(replace(replace(replace(isnull(equality_columns, '') +
22. isnull(inequality_columns, ''), ',', '_'), '[', ''),']', ''), ' ', '') +
23. CASE WHEN included_columns IS NOT NULL
24. THEN '_INC_' + replace(replace(replace(replace(included_columns, ',', '_'), '[', ''),']', ''), ' ', '')
25. ELSE '' END + ' ON ' + statement + ' (' +
26. CASE
27. WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL
28. THEN equality_columns + ', ' + inequality_columns
29. WHEN equality_columns IS NOT NULL AND inequality_columns IS NULL
30. THEN equality_columns
31. WHEN equality_columns IS NULL AND inequality_columns IS NOT NULL
32. THEN inequality_columns
33. END + ')' +
34. CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' +
35. replace(replace(replace(included_columns, '[', ''),']', ''), ' ', '') + ')'
36. ELSE '' END +
37. CASE WHEN @@Version LIKE '%Enterprise%' THEN ' WITH (ONLINE = ON)'
38. ELSE '' END AS CreateIndex
39.FROM
40. sys.dm_db_missing_index_groups g
41. INNER JOIN sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
42. INNER JOIN sys.dm_db_missing_index_details d on g.index_handle = d.index_handle
43.WHERE
44. (DB_NAME(d.database_id) = @DBName
45. OR @DBName IS NULL)
46.)
47.SELECT * FROM CTE
48.WHERE CreateIndex LIKE '%'+@TableName+'%' OR @TableName IS NULL
49.ORDER BY user_seeks DESC
Feel free to modify to match your naming standards.
Monday, October 17, 2011
Friday, October 14, 2011
$ajax Web Service Not Working
Really big gotcha on a site I recently inherited.
Scenario: Ajax call worked fine in dev, did not work upon deployment. RDC to test server and run same page from there, all works fine. So, works when invoked locally but not by remote client. Same code ... same(?) security context...
Symptom: (overly helpful) Server response is “Request format is unrecognized for URL unexpectedly ending in … ”
Solution: http://aspadvice.com/blogs/ssmith/archive/2007/09/04/FIX-Request-format-is-unrecognized-for-URL-unexpectedly-ending-in.aspx
Add this node under
< webServices>
< protocols>
< add name="HttpGet"/>
< add name="HttpPost"/>
< /protocols>
< /webServices>
Reminder: Trap and log errors in your $ajax calls
gacutil
C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin C:\WINNT\Microsoft.NET\Framework\v1.0.3705
Thursday, October 13, 2011
SQL Server Unused Indexes
http://beyondrelational.com/justlearned/posts/727/get-list-of-ununsed-index-and-remove-it.aspx
USE PerformancePortal
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID)
, INDEXNAME = I.NAME
, I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN
(
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid
)
ORDER BY OBJECTNAME
, I.INDEX_ID
, INDEXNAME ASC
GO
Wednesday, October 12, 2011
T-SQL Try - Catch Error Messaging
ALTER FUNCTION portal.fnGetErrorTryCatch ()
RETURNS VARCHAR (MAX)
AS
BEGIN
/*************************************************************************
Joe Kelly
2011-10-12 17:18:44.707
Standard error response for when using try/catch blocks that stays
in scope
Syntax:
BEGIN TRY
SELECT 2/0
END TRY
BEGIN CATCH
SELECT portal.fnGetErrorTryCatch ()
END CATCH
*************************************************************************/
DECLARE @error VARCHAR (MAX)
SELECT @error = 'ERROR_LINE: ' + CAST (ISNULL (ERROR_LINE(), 0) AS VARCHAR (MAX))
+ ' | ERROR_MESSAGE: ' + ISNULL (ERROR_MESSAGE(), 0)
+ ' | ERROR_NUMBER: ' + CAST (ISNULL (ERROR_NUMBER(), 0) AS VARCHAR (MAX))
+ ' | ERROR_PROCEDURE: ' + ISNULL (ERROR_PROCEDURE(), 0)
+ ' | ERROR_SEVERITY: ' + CAST (ISNULL (ERROR_SEVERITY(), 0) AS VARCHAR (MAX))
+ ' | ERROR_STATE: ' + CAST (ISNULL (ERROR_STATE(), 0) AS VARCHAR (MAX)) ;
RETURN @error
END
SSMS Regex Replace
http://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/
/n @/n
/n
/n
Friday, September 30, 2011
SQL Server UPDATE STATISTICS
-- Whole DB
EXEC sp_updatestats
-- Single table
UPDATE STATISTICS table_or_indexed_view_name
EXEC sp_updatestats
-- Single table
UPDATE STATISTICS table_or_indexed_view_name
Thursday, September 29, 2011
t-sql SQL Server Table Sizes
http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm
-- EXEC [dbo].[dba_SpaceUsed] 'Budget', 'S'
CREATE PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name
-- If omitted, the current database is reported.
, @SortBy char(1) = 'S' -- N for name, S for Size
-- T for table name
/* Returns a table with the space used in all tables of the
* database. It's reported with the schema information unlike
* the system procedure sp_spaceuse.
*
* sp_spaceused is used to perform the calculations to ensure
* that the numbers match what SQL Server would report.
*
* Compatible with sQL Server 2000 and 2005
*
* Example:
exec dbo.dba_SpaceUsed null, 'T'
*
* © Copyright 2007 Andrew Novick http://www.NovickSoftware.com
* This software is provided as is without warrentee of any kind.
* You may use this procedure in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
******************************************************************/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN
SET @SourceDB = DB_NAME () -- The current DB
END
--------------------------------------------------------
-- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname
, TabName sysname )
SELECT @sql = 'insert #tables ([schema], [TabName])
select TABLE_SCHEMA, TABLE_NAME
from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)
---------------------------------------------------------------
-- #TabSpaceTxt Holds the results of sp_spaceused.
-- It Doesn't have Schema Info!
CREATE TABLE #TabSpaceTxt (
TabName sysname
, [Rows] varchar (11)
, Reserved varchar (18)
, Data varchar (18)
, Index_Size varchar ( 18 )
, Unused varchar ( 18 )
)
---------------------------------------------------------------
-- The result table, with numeric results and Schema name.
CREATE TABLE #TabSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
DECLARE @Tab sysname -- table name
, @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR
SELECT [SCHEMA], TabNAME
FROM #tables
OPEN TableCursor;
FETCH TableCursor into @Sch, @Tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB
+ ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '
+ '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
Delete from #TabSpaceTxt; -- Stores 1 result at a time
EXEC (@sql);
INSERT INTO #TabSpace
SELECT @Sch
, [TabName]
, convert(bigint, rows)
, convert(numeric(18,3), convert(numeric(18,3),
left(reserved, len(reserved)-3)) / 1024.0)
ReservedMB
, convert(numeric(18,3), convert(numeric(18,3),
left(data, len(data)-3)) / 1024.0) DataMB
, convert(numeric(18,3), convert(numeric(18,3),
left(index_size, len(index_size)-3)) / 1024.0)
Index_SizeMB
, convert(numeric(18,3), convert(numeric(18,3),
left(unused, len([Unused])-3)) / 1024.0)
[UnusedMB]
FROM #TabSpaceTxt;
FETCH TableCursor into @Sch, @Tab;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
-----------------------------------------------------
-- Caller specifies sort, Default is size
IF @SortBy = 'N' -- Use Schema then Table Name
SELECT * FROM #TabSpace
ORDER BY [Schema] asc, [TabName] asc
ELSE IF @SortBy = 'T' -- Table name, then schema
SELECT * FROM #TabSpace
ORDER BY [TabName] asc, [Schema] asc
ELSE -- S, NULL, or whatever get's the default
SELECT * FROM #TabSpace
ORDER BY ReservedMB desc
;
DROP TABLE #Tables
DROP TABLE #TabSpaceTxt
DROP TABLE #TabSpace
-- EXEC [dbo].[dba_SpaceUsed] 'Budget', 'S'
CREATE PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name
-- If omitted, the current database is reported.
, @SortBy char(1) = 'S' -- N for name, S for Size
-- T for table name
/* Returns a table with the space used in all tables of the
* database. It's reported with the schema information unlike
* the system procedure sp_spaceuse.
*
* sp_spaceused is used to perform the calculations to ensure
* that the numbers match what SQL Server would report.
*
* Compatible with sQL Server 2000 and 2005
*
* Example:
exec dbo.dba_SpaceUsed null, 'T'
*
* © Copyright 2007 Andrew Novick http://www.NovickSoftware.com
* This software is provided as is without warrentee of any kind.
* You may use this procedure in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
******************************************************************/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN
SET @SourceDB = DB_NAME () -- The current DB
END
--------------------------------------------------------
-- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname
, TabName sysname )
SELECT @sql = 'insert #tables ([schema], [TabName])
select TABLE_SCHEMA, TABLE_NAME
from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)
---------------------------------------------------------------
-- #TabSpaceTxt Holds the results of sp_spaceused.
-- It Doesn't have Schema Info!
CREATE TABLE #TabSpaceTxt (
TabName sysname
, [Rows] varchar (11)
, Reserved varchar (18)
, Data varchar (18)
, Index_Size varchar ( 18 )
, Unused varchar ( 18 )
)
---------------------------------------------------------------
-- The result table, with numeric results and Schema name.
CREATE TABLE #TabSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
DECLARE @Tab sysname -- table name
, @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR
SELECT [SCHEMA], TabNAME
FROM #tables
OPEN TableCursor;
FETCH TableCursor into @Sch, @Tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB
+ ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '
+ '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
Delete from #TabSpaceTxt; -- Stores 1 result at a time
EXEC (@sql);
INSERT INTO #TabSpace
SELECT @Sch
, [TabName]
, convert(bigint, rows)
, convert(numeric(18,3), convert(numeric(18,3),
left(reserved, len(reserved)-3)) / 1024.0)
ReservedMB
, convert(numeric(18,3), convert(numeric(18,3),
left(data, len(data)-3)) / 1024.0) DataMB
, convert(numeric(18,3), convert(numeric(18,3),
left(index_size, len(index_size)-3)) / 1024.0)
Index_SizeMB
, convert(numeric(18,3), convert(numeric(18,3),
left(unused, len([Unused])-3)) / 1024.0)
[UnusedMB]
FROM #TabSpaceTxt;
FETCH TableCursor into @Sch, @Tab;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
-----------------------------------------------------
-- Caller specifies sort, Default is size
IF @SortBy = 'N' -- Use Schema then Table Name
SELECT * FROM #TabSpace
ORDER BY [Schema] asc, [TabName] asc
ELSE IF @SortBy = 'T' -- Table name, then schema
SELECT * FROM #TabSpace
ORDER BY [TabName] asc, [Schema] asc
ELSE -- S, NULL, or whatever get's the default
SELECT * FROM #TabSpace
ORDER BY ReservedMB desc
;
DROP TABLE #Tables
DROP TABLE #TabSpaceTxt
DROP TABLE #TabSpace
Tuesday, September 27, 2011
SQL Server Upgrade to 2k8 R2
(since we do this kind of thing sooo regularly … )
Preparation:
Update server (OS & SQL to latest SP – not ‘necessary but appears to be a common practice*)
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Record the account under which each service runs
Update engine
(should not have to) Restore user Db’s
(should not have to) Restore users and permissions, jobs et. al. ‘should’ still be there
Test …
* Adam – 2k8 R2 from 2k5 requires 2k5 SP2 or greater
Errors & causes: “The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.
“cannot create db … “
Check permission on target ldf & mdf dirs for sql server user
“process cannot access *.dll”
Use “Process Explorer” to find who has a lock on the dll and kill the PID. MOM is one of the likely culprits
From: Kelly, Joe
Sent: Tuesday, September 27, 2011 12:35 PM
To: Joe, Adam; Kelly, Joe
Subject: FYI - Error Attaching Db's
Per our con, this is the error that I was receiving when trying to reattach the db’s:
“The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.
Lesson learned:
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Update engine
Restore user Db’s
Restore users and permissions, jobs et. al. ‘should’ still be there
Preparation:
Update server (OS & SQL to latest SP – not ‘necessary but appears to be a common practice*)
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Record the account under which each service runs
Update engine
(should not have to) Restore user Db’s
(should not have to) Restore users and permissions, jobs et. al. ‘should’ still be there
Test …
* Adam – 2k8 R2 from 2k5 requires 2k5 SP2 or greater
Errors & causes: “The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.
“cannot create db … “
Check permission on target ldf & mdf dirs for sql server user
“process cannot access *.dll”
Use “Process Explorer” to find who has a lock on the dll and kill the PID. MOM is one of the likely culprits
From: Kelly, Joe
Sent: Tuesday, September 27, 2011 12:35 PM
To: Joe, Adam; Kelly, Joe
Subject: FYI - Error Attaching Db's
Per our con, this is the error that I was receiving when trying to reattach the db’s:
“The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.
Lesson learned:
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Update engine
Restore user Db’s
Restore users and permissions, jobs et. al. ‘should’ still be there
Monday, September 12, 2011
C# Sleep Routine
int i = 0;
string retVal = "";
string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_PlugX';";
//string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_Plug';";
while (i == 0)
{
retVal = DataUtilities.utilSQL.DBExecScalarString(sSQL2, "TSI");
tOut.Text += retVal;
if (retVal == "0")
{
break;
}
else
{
System.Threading.Thread.Sleep(1000);
}
}
string retVal = "";
string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_PlugX';";
//string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_Plug';";
while (i == 0)
{
retVal = DataUtilities.utilSQL.DBExecScalarString(sSQL2, "TSI");
tOut.Text += retVal;
if (retVal == "0")
{
break;
}
else
{
System.Threading.Thread.Sleep(1000);
}
}
Friday, September 9, 2011
Thursday, September 8, 2011
GRANT EXEC - Start SQL Agent Job
GRANT EXEC ON [sp_start_job] TO [someUser]
http://msdn.microsoft.com/en-us/library/ms186757.aspx
Permissions
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.
http://msdn.microsoft.com/en-us/library/ms186757.aspx
Permissions
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.
Wednesday, August 24, 2011
How to Get/Crack a Lost Excel Macro PW? - 2003 and below
http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project
Yes there is, as long as you are using a .xls format spreadsheet (the default for Excel up to 2003). For Excel 2007 onwards, the default is .xlsx, which is a fairly secure format, and this method will not work.
As Treb says, it's a simple comparison, so one method is simply to swap out the password entry in the file using a hex editor (see What is a good Windows hex editor?). Step by step example:
1. Create a new simple excel file.
2. In the VBA part, set a simple password (say - 1234).
3. Save the file and exit. Then check the file size - see Stewbob's gotcha
4. Open the file you just created with a hex editor.
5.
Copy the lines starting with the following keys:
CMG=....
DPB=...
GC=...
6.
FIRST BACKUP the excel file you don't know the VBA password for, then open it with your hex editor, and paste the above copied lines from the dummy file.
7. save the excel file and exit.
8. Now, open the excel file you need to see the VBA code in. The password for the VBA code will simply be 1234 (as in the example I'm showing here).
Yes there is, as long as you are using a .xls format spreadsheet (the default for Excel up to 2003). For Excel 2007 onwards, the default is .xlsx, which is a fairly secure format, and this method will not work.
As Treb says, it's a simple comparison, so one method is simply to swap out the password entry in the file using a hex editor (see What is a good Windows hex editor?). Step by step example:
1. Create a new simple excel file.
2. In the VBA part, set a simple password (say - 1234).
3. Save the file and exit. Then check the file size - see Stewbob's gotcha
4. Open the file you just created with a hex editor.
5.
Copy the lines starting with the following keys:
CMG=....
DPB=...
GC=...
6.
FIRST BACKUP the excel file you don't know the VBA password for, then open it with your hex editor, and paste the above copied lines from the dummy file.
7. save the excel file and exit.
8. Now, open the excel file you need to see the VBA code in. The password for the VBA code will simply be 1234 (as in the example I'm showing here).
Proper Case t-sql
ALTER FUNCTION ProperCase(@Text VARCHAR (8000))
RETURNS VARCHAR(8000)
AS
BEGIN
/***************************************************
2011-08-24 10:44:23.503
Source: http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx
***************************************************/
DECLARE @Reset BIT
DECLARE @Ret VARCHAR(8000)
DECLARE @i INT
DECLARE @c CHAR (1)
SELECT @Reset = 1, @i=1, @Ret = '';
WHILE (@i <= LEN (@Text))
SELECT @c=
SUBSTRING (@Text,@i,1)
, @Ret = @Ret
+ CASE
WHEN @Reset=1
THEN UPPER(@c)
ELSE LOWER(@c)
END
, @Reset = CASE
WHEN @c LIKE '[a-zA-Z]'
THEN 0
ELSE 1
END
, @i = @i +1
RETURN @Ret
END
RETURNS VARCHAR(8000)
AS
BEGIN
/***************************************************
2011-08-24 10:44:23.503
Source: http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx
***************************************************/
DECLARE @Reset BIT
DECLARE @Ret VARCHAR(8000)
DECLARE @i INT
DECLARE @c CHAR (1)
SELECT @Reset = 1, @i=1, @Ret = '';
WHILE (@i <= LEN (@Text))
SELECT @c=
SUBSTRING (@Text,@i,1)
, @Ret = @Ret
+ CASE
WHEN @Reset=1
THEN UPPER(@c)
ELSE LOWER(@c)
END
, @Reset = CASE
WHEN @c LIKE '[a-zA-Z]'
THEN 0
ELSE 1
END
, @i = @i +1
RETURN @Ret
END
Friday, August 19, 2011
Undocumented strored procedure sp_MSgetversion will gives version information of sql server
EXEC master..sp_MSgetversion
http://beyondrelational.com/justlearned/posts/496/about-the-undocumented-strored-procedure-spmsgetversion.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Aug19
http://beyondrelational.com/justlearned/posts/496/about-the-undocumented-strored-procedure-spmsgetversion.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Aug19
Wednesday, August 17, 2011
FYI - Fn for Tuning Excel Dates into T-SQL Dates (ISO 8601 standard format)
=TEXT(B5,"yyyy-mm-dd")
i.e.
07/14/11 becomes 2011-07-14
07/22/11 becomes 2011-07-22
07/28/11 becomes 2011-07-28
08/05/11 becomes 2011-08-05
08/11/11 becomes 2011-08-11
08/19/11 becomes 2011-08-19
08/25/11 becomes 2011-08-25
09/02/11 becomes 2011-09-02
Credit: http://michiel.wordpress.com/2010/03/26/convert-excel-date-value-to-sql-date/
Monday, August 15, 2011
DB Mail - profile name is not valid
Use this parameterized syntax:
EXEC msdb.dbo.sp_send_dbmail @recipients = @To
, @profile_name = 'Decision Support Services'
, @subject = @subject
, @body = @body
, @body_format = 'HTML'
Wednesday, August 3, 2011
Read Only DB User with DDL per Schema
USE [DB]
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [domain\user]
GO
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [domain\user]
GO
Tuesday, August 2, 2011
Friday, July 8, 2011
Cannot resolve collation conflict for equal to operation.
COLLATE DATABASE_DEFAULT
as in ...
AND jk.UOM_Act COLLATE DATABASE_DEFAULT = r.UOM_Act COLLATE DATABASE_DEFAULT
as in ...
AND jk.UOM_Act COLLATE DATABASE_DEFAULT = r.UOM_Act COLLATE DATABASE_DEFAULT
Tuesday, June 28, 2011
Is Job Running? SQL Server Agent Jobs
CREATE PROC isJobRunning (@jobName VARCHAR (256))
AS
BEGIN
/*****************************************************************************
Joe Kelly
2011-06-27 17:14:43.770
FROM: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/da57aedb-096c-40fb-936f-0f727fe3f605/
Determines whether a job is already running or not.
Returns: 0 - no, 1 - yes
EXEC isJobRunning N'WFX Agency Data Import'
*****************************************************************************/
SET NOCOUNT ON
DECLARE @xp_results TABLE
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname SET @job_owner = SUSER_SNAME()
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner
DECLARE @IsJobRunning BIT
SELECT @IsJobRunning = x.running
FROM @xp_results x
INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id
WHERE sj.name = N'WFX Agency Data Import'
--Insert your job's name between the single quotes
PRINT @IsJobRunning
END
AS
BEGIN
/*****************************************************************************
Joe Kelly
2011-06-27 17:14:43.770
FROM: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/da57aedb-096c-40fb-936f-0f727fe3f605/
Determines whether a job is already running or not.
Returns: 0 - no, 1 - yes
EXEC isJobRunning N'WFX Agency Data Import'
*****************************************************************************/
SET NOCOUNT ON
DECLARE @xp_results TABLE
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname SET @job_owner = SUSER_SNAME()
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner
DECLARE @IsJobRunning BIT
SELECT @IsJobRunning = x.running
FROM @xp_results x
INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id
WHERE sj.name = N'WFX Agency Data Import'
--Insert your job's name between the single quotes
PRINT @IsJobRunning
END
Monday, June 27, 2011
Reading ASP.NET Application Settings From Web.config Using Classic AS
http://www.asp101.com/articles/john/readsettings/default.asp
"...Introduction
Whether you're trying to share settings between your ASP.NET and legacy classic ASP apps or are simply looking for a way to make your eventual migration to ASP.NET easier, this piece of code might be just the ticket.
The code came about because I recently found myself tasked with the annoying job of migrating a database server from an old machine to a newer piece of hardware. The database server was acting as the backend data store for a web site that's been around for quite a few years and was running a mix of classic ASP and ASP.NET applications.
Thankfully both the classic ASP and ASP.NET applications were relatively careful about keeping their connection strings centralized, but I was still needed to change the setting in both the ASP and ASP.NET applications. The switch ended up going quite smoothly, but as I was looking around to make sure I'd found everything, I started to realize just how many settings and how much information was duplicated between the legacy and new applications. It seemed like there had to be a better way.
Since pretty much all new development is being done on .NET, the obvious solution was to find an easy way for classic ASP applications to be able to read their settings from the same source as the ASP.NET applications: the web.config file.
The Code
As we're all aware, the data in the web.config file is stored in an XML-based format. Now I'll admit that my XML skills probably aren't up to par with those of you who use it on a regular basis, but I did manage to hack together a couple short functions to facilitate reading connection strings and application settings from the site's root web.config file.
readsettings.asp
<%@ Language="VBScript" %>
<%
Option Explicit
Function GetAppSetting(strAppSettingKey)
Dim xmlWebConfig
Dim nodeAppSettings
Dim nodeChildNode
Dim strAppSettingValue
Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))
If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeAppSettings = xmlWebConfig.selectSingleNode("//configuration/appSettings")
For Each nodeChildNode In nodeAppSettings.childNodes
If nodeChildNode.getAttribute("key") = strAppSettingKey Then
strAppSettingValue = nodeChildNode.getAttribute("value")
Exit For
End If
Next
Set nodeAppSettings = Nothing
End If
Set xmlWebConfig = Nothing
GetAppSetting = strAppSettingValue
End Function
Function GetConnectionString(strConnStringName)
Dim xmlWebConfig
Dim nodeConnStrings
Dim nodeChildNode
Dim strConnStringValue
Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))
If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeConnStrings = xmlWebConfig.selectSingleNode("//configuration/connectionStrings")
For Each nodeChildNode In nodeConnStrings.childNodes
If nodeChildNode.getAttribute("name") = strConnStringName Then
strConnStringValue = nodeChildNode.getAttribute("connectionString")
Exit For
End If
Next
Set nodeConnStrings = Nothing
End If
Set xmlWebConfig = Nothing
GetConnectionString = strConnStringValue
End Function
%>
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
< html xmlns="http://www.w3.org/1999/xhtml" >
< head>
< title>Reading ASP.NET Application Settings From Web.config Using Classic ASP< /title>
< /head>
< body>
< p>
The following values are all read from the
file located in the root of the web site.
< /p>
< p>
Welcome Message: <%= GetAppSetting("WelcomeMessage") %>
< /p>
< p>
Sample Connection String: <%= GetConnectionString("SampleConnString") %>
< /p>
< p>
Thank You Message: <%= GetAppSetting("ThankYouMessage") %>
< /p>
< /body>
< /html>
As you can see they're quite easy to use both functions are quite similar. The only real difference between the two are related to the location of the settings in the XML tree and the syntax differences between the application settings and connection strings sections.
Speaking of the settings, if you use the functions as written, you'll be retrieving settings by using the connection string's name or the key associated with an application setting. These values are case sensitive, so if you're having trouble, make sure you've got everything spelled exactly the same... case and all.
Although you'll most likely be using your own existing web.config file, I'm including a simple one here for illustration.
web.config
< ?xml version="1.0"?>
< !--
Comments and whitespace shouldn't cause any problems.
-->
< configuration>
< appSettings>
< add key="WelcomeMessage" value="Welcome to our site." />
< add key="ThankYouMessage" value="Thanks for visiting... please come back soon." />
< /appSettings>
< connectionStrings>
< add name="SampleConnString"
providerName="System.Data.OleDbClient"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Inetpub\wwwroot\App_Data\test.mdb;"
/>
< /connectionStrings>
< system.web>
< compilation debug="false" />
< /system.web>
< /configuration>
That's all there is to it. A quick and easy way for you to enable your classic ASP applications to read application settings and connection strings from ASP.NET's web.config file..."
"...Introduction
Whether you're trying to share settings between your ASP.NET and legacy classic ASP apps or are simply looking for a way to make your eventual migration to ASP.NET easier, this piece of code might be just the ticket.
The code came about because I recently found myself tasked with the annoying job of migrating a database server from an old machine to a newer piece of hardware. The database server was acting as the backend data store for a web site that's been around for quite a few years and was running a mix of classic ASP and ASP.NET applications.
Thankfully both the classic ASP and ASP.NET applications were relatively careful about keeping their connection strings centralized, but I was still needed to change the setting in both the ASP and ASP.NET applications. The switch ended up going quite smoothly, but as I was looking around to make sure I'd found everything, I started to realize just how many settings and how much information was duplicated between the legacy and new applications. It seemed like there had to be a better way.
Since pretty much all new development is being done on .NET, the obvious solution was to find an easy way for classic ASP applications to be able to read their settings from the same source as the ASP.NET applications: the web.config file.
The Code
As we're all aware, the data in the web.config file is stored in an XML-based format. Now I'll admit that my XML skills probably aren't up to par with those of you who use it on a regular basis, but I did manage to hack together a couple short functions to facilitate reading connection strings and application settings from the site's root web.config file.
readsettings.asp
<%@ Language="VBScript" %>
<%
Option Explicit
Function GetAppSetting(strAppSettingKey)
Dim xmlWebConfig
Dim nodeAppSettings
Dim nodeChildNode
Dim strAppSettingValue
Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))
If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeAppSettings = xmlWebConfig.selectSingleNode("//configuration/appSettings")
For Each nodeChildNode In nodeAppSettings.childNodes
If nodeChildNode.getAttribute("key") = strAppSettingKey Then
strAppSettingValue = nodeChildNode.getAttribute("value")
Exit For
End If
Next
Set nodeAppSettings = Nothing
End If
Set xmlWebConfig = Nothing
GetAppSetting = strAppSettingValue
End Function
Function GetConnectionString(strConnStringName)
Dim xmlWebConfig
Dim nodeConnStrings
Dim nodeChildNode
Dim strConnStringValue
Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))
If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeConnStrings = xmlWebConfig.selectSingleNode("//configuration/connectionStrings")
For Each nodeChildNode In nodeConnStrings.childNodes
If nodeChildNode.getAttribute("name") = strConnStringName Then
strConnStringValue = nodeChildNode.getAttribute("connectionString")
Exit For
End If
Next
Set nodeConnStrings = Nothing
End If
Set xmlWebConfig = Nothing
GetConnectionString = strConnStringValue
End Function
%>
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
< html xmlns="http://www.w3.org/1999/xhtml" >
< head>
< title>Reading ASP.NET Application Settings From Web.config Using Classic ASP< /title>
< /head>
< body>
< p>
The following values are all read from the
web.configfile located in the root of the web site.
< /p>
< p>
Welcome Message: <%= GetAppSetting("WelcomeMessage") %>
< /p>
< p>
Sample Connection String: <%= GetConnectionString("SampleConnString") %>
< /p>
< p>
Thank You Message: <%= GetAppSetting("ThankYouMessage") %>
< /p>
< /body>
< /html>
As you can see they're quite easy to use both functions are quite similar. The only real difference between the two are related to the location of the settings in the XML tree and the syntax differences between the application settings and connection strings sections.
Speaking of the settings, if you use the functions as written, you'll be retrieving settings by using the connection string's name or the key associated with an application setting. These values are case sensitive, so if you're having trouble, make sure you've got everything spelled exactly the same... case and all.
Although you'll most likely be using your own existing web.config file, I'm including a simple one here for illustration.
web.config
< ?xml version="1.0"?>
< !--
Comments and whitespace shouldn't cause any problems.
-->
< configuration>
< appSettings>
< add key="WelcomeMessage" value="Welcome to our site." />
< add key="ThankYouMessage" value="Thanks for visiting... please come back soon." />
< /appSettings>
< connectionStrings>
< add name="SampleConnString"
providerName="System.Data.OleDbClient"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Inetpub\wwwroot\App_Data\test.mdb;"
/>
< /connectionStrings>
< system.web>
< compilation debug="false" />
< /system.web>
< /configuration>
That's all there is to it. A quick and easy way for you to enable your classic ASP applications to read application settings and connection strings from ASP.NET's web.config file..."
Thursday, June 23, 2011
ASP Worker Process - Memory
Response Buffer Limit Exceeded (page fails to render, no obvious error)
... The report is so large that it surpassed the allocated amount of memory for the ASP worker process (aspbufferinglimit). Quadrupling the amount to 16 MB (from the recommended 4MB) allows it to now function correctly...
--------------------------
To change size:
http://support.microsoft.com/kb/925764
set drive:
cd /d %systemdrive%\inetpub\adminscripts
get current amount
cscript.exe adsutil.vbs GET w3svc/aspbufferinglimit
set new amount
cscript.exe adsutil.vbs SET w3svc/aspbufferinglimit < bytes >
--------------------------
String length exceeds maximum length of 32767 characters for 'FileSystem' APIs
Visual Studio 2008
Other Versions
• Visual Studio 2005
A string's length exceeds the maximum length of 32767 characters.
To correct this error
• Shorten the string.
See Also
________________________________________
Other Resources
Strings in Visual Basic
... The report is so large that it surpassed the allocated amount of memory for the ASP worker process (aspbufferinglimit). Quadrupling the amount to 16 MB (from the recommended 4MB) allows it to now function correctly...
--------------------------
To change size:
http://support.microsoft.com/kb/925764
set drive:
cd /d %systemdrive%\inetpub\adminscripts
get current amount
cscript.exe adsutil.vbs GET w3svc/aspbufferinglimit
set new amount
cscript.exe adsutil.vbs SET w3svc/aspbufferinglimit < bytes >
--------------------------
String length exceeds maximum length of 32767 characters for 'FileSystem' APIs
Visual Studio 2008
Other Versions
• Visual Studio 2005
A string's length exceeds the maximum length of 32767 characters.
To correct this error
• Shorten the string.
See Also
________________________________________
Other Resources
Strings in Visual Basic
Tuesday, June 21, 2011
How to Get/Crack a Lost Excel Macro PW? - Solved!
With the help of this post
http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project
and a bit of hacking ...
This seems to be the easiest way (and it should scare y'all just how easy this was, just took some time)
a.) XLSM, XLSB (convert to XLSM, take the performance hit)
b.) VBA is pw protected, not the workbook or worksheet (if so, you're hosed)
c.) Back up file (you'll likely screw this up the first time or two and if you do you'll lose all the VBA modules)
d.) Change extension to .zip, extract to subfolder
e.) in .\xl\vbaProject.bin, with a hex/binary editor (not a file editor) munge the key [dpb] (i.e. becomes [dpx]), save
f.) On parent level, select xml file and the three subfolders, right click, "Send to", "Compressed Zip Folder"
g.) Rename zip file to xlsm extension, open file
h.) When prompted for invalid key, select "Yes" - continue
i.) Open VBA editor
j.) When you go to look at a module, you'll get an error
k.) Go to Tools / Properties / Protection, reset PW, save
You should now be able to view the module source code.
http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project
and a bit of hacking ...
This seems to be the easiest way (and it should scare y'all just how easy this was, just took some time)
a.) XLSM, XLSB (convert to XLSM, take the performance hit)
b.) VBA is pw protected, not the workbook or worksheet (if so, you're hosed)
c.) Back up file (you'll likely screw this up the first time or two and if you do you'll lose all the VBA modules)
d.) Change extension to .zip, extract to subfolder
e.) in .\xl\vbaProject.bin, with a hex/binary editor (not a file editor) munge the key [dpb] (i.e. becomes [dpx]), save
f.) On parent level, select xml file and the three subfolders, right click, "Send to", "Compressed Zip Folder"
g.) Rename zip file to xlsm extension, open file
h.) When prompted for invalid key, select "Yes" - continue
i.) Open VBA editor
j.) When you go to look at a module, you'll get an error
k.) Go to Tools / Properties / Protection, reset PW, save
You should now be able to view the module source code.
Wednesday, June 15, 2011
Linked Servers - Log-in Failed for Anonymous
Third hop fails - log in on the actual box you want to deploy to rather than with QA.
Tuesday, June 14, 2011
Format SQL Date from 2011-06-14 to 06/14/2011
DECLARE @foo DATETIME = GETDATE ()
SELECT CONVERT(VARCHAR(10), @foo, 101)
SELECT CONVERT(VARCHAR(10), @foo, 101)
Thursday, June 9, 2011
List Issue: must declare a body ...
"must declare a body because it is not marked abstract or extern."
http://stackoverflow.com/questions/95683/net-property-generating-must-declare-a-body-because-it-is-not-marked-abstract-o
Compiler is being instructed to use 2.0, not 3.5
--------------------------------
add to web.config (separate section - remove space after opening angle bracket):
< system.codedom>
< compilers>
< compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharpCodeProvider,System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" warningLevel="4">
< providerOption name="CompilerVersion" value="v3.5" />
< providerOption name="WarnAsError" value="false" />
< /compiler>
< /compilers>
< /system.codedom>
http://stackoverflow.com/questions/95683/net-property-generating-must-declare-a-body-because-it-is-not-marked-abstract-o
Compiler is being instructed to use 2.0, not 3.5
--------------------------------
add to web.config (separate section - remove space after opening angle bracket):
< system.codedom>
< compilers>
< compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharpCodeProvider,System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" warningLevel="4">
< providerOption name="CompilerVersion" value="v3.5" />
< providerOption name="WarnAsError" value="false" />
< /compiler>
< /compilers>
< /system.codedom>
Monday, June 6, 2011
Classic ASP Debugging with VS 2005
http://blogs.msdn.com/b/greggm/archive/2006/03/15/552108.aspx
Visual Studio 2005
" ... However, if you insist on using Visual Studio 2005 for your classic ASP debugging needs, I have some good news -- while harder to setup, with a little bit of help from this blog, you can still have a nice experience debugging your class ASP code with Visual Studio 2005. There are four features that we cut from Visual Studio 2005 that affect ASP debugging:
1. Project system support for configuring IIS
2. ASP Auto-attach, which the debugger used to find the ASP worker process
3. Remote script debugging
4. Debugging managed code and script code at the same time
Let's go through how we can work around these cuts.
#1. The project system provided support to automatically configure your machine for ASP debugging. However, since it is only a one-time deal, you can always manually configure your machine. From technet:
1.
In IIS Manager, double-click the local computer, right-click the Web Sites folder or an individual Web site folder, and then click Properties.
Note
Configuration settings made at the Web Sites level are inherited by all of the Web sites on the server. You can override inheritance by configuring the individual site or site element.
2.
Click the Home Directory tab, and then click Configuration.
3.
Click the Debugging tab, and then select the Enable ASP server-side script debugging check box.
4.
Click Send detailed ASP error messages to client if you want to send the client very detailed debugging information, or click Send the following text error message to client and type the text you want to send to the client.
5.
Click OK.
If you intend to debug client-side script as well, you might also want to set the ASPCLIENTDEBUG cookie. See MSDN.
#2. Since the debugger doesn’t have support for ASP Auto-Attach, you can’t just press F5. But what you can do is to hit your page in IE, and use the below macro to automatically start debugging the ASP code. You can assign a macro to a key, so within 2 minutes, you can have Ctrl-Shift-F5 (or whatever key you want), setup to automatically attach to the worker process and get a pretty similar experience to what you have always had.
Sub ClassicASPAttach()
Try
Dim os As System.Version = System.Environment.OSVersion.Version
Dim IISProcess As String = "w3wp.exe"
If os.Major = 5 And os.Minor < 2 Then
IISProcess = "dllhost.exe"
End If
Dim processFound As Boolean = False
Dim process As EnvDTE80.Process2
For Each process In DTE.Debugger.LocalProcesses
'Determine if the process could the IIS worker process
Dim processName As String = process.Name.ToLowerInvariant()
Dim processBaseName As String = System.IO.Path.GetFileName(processName)
If Not processBaseName = IISProcess Then
If Not processBaseName = "inetinfo.exe" Then
Continue For
End If
End If
'Determine if the process contains asp.dll
Dim aspLoaded As Boolean = False
Dim diagProcess As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessById(process.ProcessID)
Dim diagModule As System.Diagnostics.ProcessModule
For Each diagModule In diagProcess.Modules
Dim moduleName As String = System.IO.Path.GetFileName(diagModule.FileName).ToLowerInvariant()
If moduleName = "asp.dll" Then
aspLoaded = True
Exit For
End If
Next
'If the process contains asp.dll, attach to it
If aspLoaded Then
process.Attach2("Script")
processFound = True
End If
Next
If Not processFound Then
MsgBox("Could not find this IIS process. Hit a web page containing classic ASP script so that the process will start.")
End If
Catch ex As System.Exception
MsgBox(ex.Message)
End Try
End Sub
#3. Sadly, there just isn’t any way to do remote script debugging. If you need remote script debugging, my only suggestion would be to use Remote Desktop and run Visual Studio on your server.
#4. Again, there just isn’t any way to debug both script code and managed code at the same time. My only suggestion here would be to switch back and forth between managed debugging and script debugging. ... "
Visual Studio 2005
" ... However, if you insist on using Visual Studio 2005 for your classic ASP debugging needs, I have some good news -- while harder to setup, with a little bit of help from this blog, you can still have a nice experience debugging your class ASP code with Visual Studio 2005. There are four features that we cut from Visual Studio 2005 that affect ASP debugging:
1. Project system support for configuring IIS
2. ASP Auto-attach, which the debugger used to find the ASP worker process
3. Remote script debugging
4. Debugging managed code and script code at the same time
Let's go through how we can work around these cuts.
#1. The project system provided support to automatically configure your machine for ASP debugging. However, since it is only a one-time deal, you can always manually configure your machine. From technet:
1.
In IIS Manager, double-click the local computer, right-click the Web Sites folder or an individual Web site folder, and then click Properties.
Note
Configuration settings made at the Web Sites level are inherited by all of the Web sites on the server. You can override inheritance by configuring the individual site or site element.
2.
Click the Home Directory tab, and then click Configuration.
3.
Click the Debugging tab, and then select the Enable ASP server-side script debugging check box.
4.
Click Send detailed ASP error messages to client if you want to send the client very detailed debugging information, or click Send the following text error message to client and type the text you want to send to the client.
5.
Click OK.
If you intend to debug client-side script as well, you might also want to set the ASPCLIENTDEBUG cookie. See MSDN.
#2. Since the debugger doesn’t have support for ASP Auto-Attach, you can’t just press F5. But what you can do is to hit your page in IE, and use the below macro to automatically start debugging the ASP code. You can assign a macro to a key, so within 2 minutes, you can have Ctrl-Shift-F5 (or whatever key you want), setup to automatically attach to the worker process and get a pretty similar experience to what you have always had.
Sub ClassicASPAttach()
Try
Dim os As System.Version = System.Environment.OSVersion.Version
Dim IISProcess As String = "w3wp.exe"
If os.Major = 5 And os.Minor < 2 Then
IISProcess = "dllhost.exe"
End If
Dim processFound As Boolean = False
Dim process As EnvDTE80.Process2
For Each process In DTE.Debugger.LocalProcesses
'Determine if the process could the IIS worker process
Dim processName As String = process.Name.ToLowerInvariant()
Dim processBaseName As String = System.IO.Path.GetFileName(processName)
If Not processBaseName = IISProcess Then
If Not processBaseName = "inetinfo.exe" Then
Continue For
End If
End If
'Determine if the process contains asp.dll
Dim aspLoaded As Boolean = False
Dim diagProcess As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessById(process.ProcessID)
Dim diagModule As System.Diagnostics.ProcessModule
For Each diagModule In diagProcess.Modules
Dim moduleName As String = System.IO.Path.GetFileName(diagModule.FileName).ToLowerInvariant()
If moduleName = "asp.dll" Then
aspLoaded = True
Exit For
End If
Next
'If the process contains asp.dll, attach to it
If aspLoaded Then
process.Attach2("Script")
processFound = True
End If
Next
If Not processFound Then
MsgBox("Could not find this IIS process. Hit a web page containing classic ASP script so that the process will start.")
End If
Catch ex As System.Exception
MsgBox(ex.Message)
End Try
End Sub
#3. Sadly, there just isn’t any way to do remote script debugging. If you need remote script debugging, my only suggestion would be to use Remote Desktop and run Visual Studio on your server.
#4. Again, there just isn’t any way to debug both script code and managed code at the same time. My only suggestion here would be to switch back and forth between managed debugging and script debugging. ... "
Thursday, April 28, 2011
Fun with DBMail and Service Accounts
If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.
Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).
a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.
b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB
Above was the solution to our issues.
Cheers – J
Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx
Other hints:
+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?
+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;
Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp
What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';
Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'
What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'
SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems
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
Fun with SQL Server DatabaseMail and Service Accounts
If you're having difficulties getting DBMail to work and everything 'looks good' try the following - these were my resolution steps after several fun hours.
Note: our SQL Server was setup such that each of the services (SQL, Agent, OLAP, … ran with a different service account).
a.) Give the account (Windows) that runs SQL Server (and the agent, to be safe) [READ] & [EXE] on DatabaseMail[XX].exe, DatabaseMailEngine[XX].exe, DatabaseMailProtocols[XX].exe (where [XX] may represent the major revision of the SQL Server release). Files are in [Install Path]\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. These files must be present for DBMail to work.
b.) Give the account (SQL Login) that runs SQL Server (and the agent, to be safe) [EXEC] on MSDB
Above was the solution to our issues.
Cheers – J
Useful: http://technet.microsoft.com/en-us/library/ms190630.aspx
Other hints:
+ If there is anti-virus software running on ALPHABETSOUP is port 25 open and is/are
([DATABASEMAIL90.EXE]/[DATABASEMAIL10.EXE]/[DATABASEMAIL.EXE]) enabled to execute? Is 25 the correct SMTP port?
+ Broker enabled? (calls the mail exe)
SELECT is_broker_enabled FROM msdb.sys.databases WHERE name = 'msdb' ;
Stop & start mail:
-- sysmail_stop_sp
-- sysmail_start_sp
What’s in the queue?
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'mail';
Syntax:
EXEC sp_send_dbmail @profile_name='Profile Name',
@recipients='acct1@domain.org; acct2@domain.org; acct3@domain.org '
@subject='ALPHABETSOUP',
@body='Tested.'
What are the other mail tables?
SELECT 'SELECT * FROM ', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'sysmail%' and Table_Type = 'base table'
SELECT * FROM sysmail_log ORDER BY log_id DESC
SELECT sent_status, * FROM sysmail_allitems
SELECT * FROM sysmail_sentitems
SELECT sent_status, * FROM sysmail_unsentitems
SELECT * FROM sysmail_faileditems
SELECT * FROM sysmail_mailattachments
SELECT * FROM sysmail_event_log
SELECT * FROM sysmail_profile
SELECT * FROM sysmail_principalprofile
SELECT * FROM sysmail_account
SELECT * FROM sysmail_profileaccount
SELECT * FROM sysmail_servertype
SELECT * FROM sysmail_server
SELECT * FROM sysmail_configuration
SELECT * FROM sysmail_mailitems
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
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
Subscribe to:
Posts (Atom)