MS SQL: Search keyword in the whole database

16 Dec

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

Delete old files in Windows and Oracle

24 Jul

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

3 Jun

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

12 Mar

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

10 Feb

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

3 Feb

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