MS SQL: Search keyword in the whole database

How to search for a keyword in the whole database. You need to do the following:

1: Create a procedure

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN
	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
	SET NOCOUNT ON
	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)
		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END
	SELECT ColumnName, ColumnValue FROM #Results
END

2: Run the procedure
exec SearchAllTables 'Searchstring'
GO

Read more:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

How to download a copy of everything on Facebook

With this method you can make a copy of everything which have happened on your facebook account, and also recover “deleted” messages and etc.

1. Login into your Facebook account and go to setting after it navigate to General account Settings.
2. Now you can see the Download a copy of your Facebook data, now click on download a copy link.
3. After clicking on link this will open a new page and on this page you can see download archive button. Click on it, after clicking it want you password and asking security question by Facebook.
4. Now you get message by Facebook – “the download link will be sent to your email id which you used to create your Facebook account.
5. Wait for a minute, check your e-mail, now you can see that you received Message from Facebook with download link. Click on download link can download zip file.
6. After downloading zip file extract it and find you lost messages, pictures and videos

Read more:

http://www.computertrickstips.com/recover-deleted-facebook-messages/

Delete old files in Windows and Oracle

Oracle produces a lot of trace and log files. Take a backup of the files before renaming/removing them. For the trace files – it’s wise to keep 1-2 week of alert/trace files in the same directory. Here’s an example to create a bat-file which you can schedule to delete old files (older than 14 days:

forfiles /p D:\App\Oracle\diag\rdbms\ora01\ora01\alert\ /s /m *.* /D -14 /C “cmd /c del /F /Q @path”

Oracle will automatically create a new alert log files when you rename/delete existing file.

Read more

https://community.oracle.com/thread/3544775?start=0&tstart=0

http://dba.stackexchange.com/questions/15715/how-to-free-up-disk-space-which-logs-directories-to-clean

https://community.oracle.com/thread/2297940?tstart=0

http://ss64.com/nt/forfiles.html

http://ss64.com/nt/del.html

http://stackoverflow.com/questions/51054/batch-file-to-delete-files-older-than-n-days

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:402360068979

http://dba-masters.blogspot.no/2012/08/11g-r2-trace-files-and-alert-logs.html

http://stackoverflow.com/questions/7160342/how-to-skip-are-you-sure-y-n-when-deleting-files-in-batch-files

http://www.databasejournal.com/features/oracle/article.php/3875896/Purging-Oracle-Databases-Alert-Log-with-ADRCI—Usage-and-Warning.htm

How do I list or search all the column names in my database – ms sql

Here is an example of a query that does the job:

 

USE dbname
GO

SELECT t.name AS table_name,
SCHEMA_NAME (schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%something%’
ORDER BY schema_name, table_name;

 

 

How to check version of odbc

To check for correct version of odbc, below is how to access odbc on Windows 64 bits:

• odbc 32 bit
  • c:\windows\SysWOW64\odbcad32.exe
• odbc 64 bit
  • c:\windows\System32\odbcad32.exe

 

 

You can also check what verson of odbc are installed by going to:

• odbc administrator – drivers tab – check if 32/62 dll are installed for the different sw (e.g Oracle, sql server)

 

Read more:

https://community.oracle.com/message/10839853

http://www.connectionstrings.com/download-oracle-11g-release-2-client-odbc-drivers/

http://www.simba.com/wp-content/uploads/2010/10/HOW-TO-32-bit-vs-64-bit-ODBC-Data-Source-Administrator.pdf

 

 

 

MS SQL – method to backup all instances/databases through script

Sometimes you need to backup all of the instances and databases in a particular server. One way is to script everything and create a schedule task in Windows to run it.

 

1: Create a sql-file with:

DECLARE @name VARCHAR (100) — database name
DECLARE @path VARCHAR (256) — path for backup files
DECLARE @fileName VARCHAR (256) — filename for backup
DECLARE @fileDate VARCHAR (40) — used for file name

— specify database backup directory
SET @path = ‘E:\MSSQL\Backup\’

— specify filename format
–SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileDate =
CONVERT (VARCHAR (20), GETDATE (), 112)
+ REPLACE (CONVERT (VARCHAR (20), GETDATE (), 108), ‘:’, ”)

DECLARE
db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,
‘model’,
‘msdb’,
‘tempdb’) — exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
–PRINT @name
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

2: Create a cmd-file with (backup instances default, instanceaa, instancebb):

sqlcmd -S servernameXX -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEAA -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEBB-i”c:\temp\backup_all_databases.sql”

3: Create a schedule task to rund cmd-file e.g everyday

 

Read more

http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

 

Error while opening Excel in Sharepoint2010

If you get error

‘Unable to process the request. Wait a few miniutes and try performing this operation again”

you need to edit the file

‘C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\XML’

Remove/comment out lines containing xls/xlsx

<Mapping FileExtension=”xlsx” RedirectUrlTemplate=”/_layouts/xlviewer.aspx?id=|0″ NoGetRedirect=”TRUE” CreateRedirectUrlTemplate=”/_layouts/xlviewer.aspx?new=1″/>

 

Reset IIS with through e.g command prompt:

iisreset /restart

Read more

http://sharepoint2010tutor.blogspot.no/2011/11/unable-to-process-request-while-opening.html

http://sharepoint.stackexchange.com/questions/64795/excel-document-opening-in-browser-unsupported-features

https://msdn.microsoft.com/en-us/library/ff595319(v=office.14).aspx

https://technet.microsoft.com/en-us/library/ee837425(v=office.14).aspx

http://stackoverflow.com/questions/22084561/difference-between-iisreset-and-iis-stop-start-command

 

 

 

MS SQL – disable constraint to update/delete records

In the relational db you have many relations, and in order to delete a certain record you have to delete all of the relations that record have in all the referenced tables. One way is to temporary disable the constraints – update the records – enable the constraints. You can use the command below:

* Disable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX NOCHECK CONSTRAINT all"

* Re-enable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX WITH CHECK CHECK CONSTRAINT all"

* Disable the constraints on a table called tableName:
ALTER TABLE tableXX NOCHECK CONSTRAINT ALL

* Re-enable the constraints on a table called tableName:
ALTER TABLE tableXX WITH CHECK CHECK CONSTRAINT ALL

Read more:
http://stackoverflow.com/questions/737115/turn-off-constraints-temporarily-ms-sql
http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql/161410#161410

Mysql – Copy database to another server

I used the sqlyog to copy database – which you can download from:

https://code.google.com/p/sqlyog/

https://code.google.com/p/sqlyog/wiki/Downloads

 

Or you can use sqldump command:

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

Can read more:

http://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another

http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database

http://serverfault.com/questions/194462/any-way-to-copy-the-schema-from-one-mysql-database-to-another

http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html

http://dev.mysql.com/doc/refman/5.6/en/copying-databases.html

 

 

 

 

 

 

Log Backup Failed to Complete

When you get this alert in the MS SQL 2012 server, then you probably haven’t managed to perform a full backup and transaction log backup of the database after creating the database the first time. This is when you have “Recovery model” set to full. Run this command in the editor for that database:

BACKUP DATABASE [DATABASENN] TO DISK = N’e:\mssql\backup\DATABASENN_1.bak’
GO
BACKUP LOG [DATABASENN] TO DISK = N’e:\mssql\backup\DATABASENN_1log.bak’
GO

Read more:

http://support2.microsoft.com/kb/2646329

http://stackoverflow.com/questions/3131258/backup-failed-to-complete-the-command-backup-database

http://blog.sqlauthority.com/2010/03/27/sql-server-fix-error-4214-backup-log-cannot-be-performed-because-there-is-no-current-database-backup/