DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model','distribution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9) BEGIN -- SQL 2005 or higher command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) END ELSE BEGIN -- SQL 2000 command DBCC DBREINDEX(@Table,' ',@fillfactor) END FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor
We are getting following error in our
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
If you want to find out what is eating up your connections, try this:
- Implement correctly all connections inside
usingblocks to close/dispose connections (as you said, this is already done)
- Check which user/machines are keeping opened connections. Run this query to identify the database id:
select distinct dbid, DB_NAME(dbid) FROM sys.sysprocesses where dbid > 0
Then, use this query to inspect all opened connections, replacing the dbid:
SELECT dbid, DB_NAME(dbid) as DatabaseName, COUNT(dbid) as ConnectionCount, loginame as LoginName FROM sys.sysprocesses WHERE dbid = 1 GROUP BY dbid, loginame ORDER BY count(dbid) desc
This can give you some hint about who is keeping too much connections opened.
- Implement pooling in connection string to limit connections. Use this in your application connection string:
Pooling=true; Min Pool Size=1; Max Pool Size=5
When we talk about security vulnerabilities in software it’s worth thinking about computer programmes on a fundamental level. On the simplistic level a computer programme is something which takes in an input, usually from the user in the form of text, processes that input, which changes the state of the machine, and then gives as output or result to the user. A bug is when certain inputs aren’t processed correctly and the wrong output is given. For example, if 1 plus 1 results in 3. A security bug however, can be when a certain input is processed in such a way that compromises the security of information managed by a programme and may even output it. We often see this in practice in web applications. Continue reading “SQL Injection for beginners”
SELECT Object_name(object_id) as [Procedure Name], Object_definition(object_id) as [Definition] FROM sys.procedures WHERE Object_definition(object_id) LIKE '%cursor%' Order by [Procedure Name]
First, you will need to enable xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
-- To update the currently configured value for this feature.
Next, you will need to change the path to the directories:
USE Master; GO SET NOCOUNT ON -- 1 - Variable declaration DECLARE @backupPath NVARCHAR(500) DECLARE @cmd NVARCHAR(500) DECLARE @fileList TABLE (backupFile NVARCHAR(255)) -- 2 - Initialize variables SET @backupPath = 'F:\baks\' -- 3 - get list of files SET @cmd = 'DIR /b "' + @backupPath + '"' INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd SELECT 'RESTORE DATABASE [' + LEFT(backupFile, CHARINDEX('_', backupFile, 12)-1) + '] FROM DISK = ''' + @backupPath + backupFile + ''' WITH RECOVERY, REPLACE GO' from @fileList
Copy the generated script into a new window and voila! Your script is done. Just press F5 to run it.
Don’t forget to turn off xp_cmdshell when done.
There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.
Imagine the following table:
ABC ABC1 ABC2 ABC3 and so on... ABC11 ABC12 ABC13 and so on.. ABC20 ABC21 ABC22 and so on..
So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.
When you run
select * from table order by my column asc
you get following results:
ABC ABC1 ABC11 ABC12 ABC13 ABC2 ABC20 ABC21 ABC22 ABC3 ABC31 ABC32
In this scenario, you would need the results sorted by the numeric value and not alphabetically.
ABC ABC1 ABC2 ABC3 ABC11 ABC12 ABC13 ABC20 ABC21 ABC22 ABC31 ABC32
How can this be accomplished?
We have been experiencing issues with some massive viewstates causing slow page loads and “System.FormatException: Invalid length for a Base-64 char array or string”.