Tuesday, May 25, 2021

Search String in SQLServer in All databases all tables all columns

 Search String in SQLServer in All databases all tables all columns

 

-------------------------Part 1
--------------------------Part 1
--------------------------Part 1
USE [master]
GO


create/alter PROC [dbo].[GlobalSearch]
AS
BEGIN


IF OBJECT_ID('dbo.ListOfObj', 'U') IS NOT NULL
DROP TABLE dbo.ListOfObj;
CREATE TABLE master.dbo.ListOfObj (DatabaseNAME nvarchar(370), TableName nvarchar(3630), ColumnName nvarchar(3630))

SET NOCOUNT ON
DECLARE @DBname nvarchar(256),@TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @DBname=''
SET @TableName = ''

DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name FROM sys.databases where database_id >6
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
----------------------------------------
----------------------------------------

declare @sql_v nvarchar(max)
BEGIN
SET @SQL_v = 'insert into master.dbo.ListOfObj select ''' + @DBName +''', t.name as table_name , c.name as column_name
from
' + quotename(@DBName) +'.'+'sys.tables t '+
' join ' + quotename(@DBName) +'.'+ 'sys.columns c on c.object_id = t.object_id '+
' join ' + quotename(@DBName) +'.'+ 'sys.types ty on ty.system_type_id = c.system_type_id'+
' where type_desc = ''USER_TABLE''
and ty.name in (''text'',''varchar'',''char'',''nvarchar'',''nchar'')'
--modify below line to search field datatype

EXEC sp_executesql @SQL_v
--PRINT @SQL_v

END
----------------------------------------
----------------------------------------

--print '-Blah-Blah-Blah' + @DBName
FETCH NEXT FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName

END
GO




--------------------------Part 2
--------------------------Part 2
--------------------------Part 2


create/alter PROC [dbo].[GlobalSearch_FinalCall]
(
@SearchString nvarchar(100)
)
AS
BEGIN

IF OBJECT_ID('dbo.FinalResultOutput', 'U') IS NOT NULL
DROP TABLE dbo.FinalResultOutput;
CREATE TABLE master.dbo.FinalResultOutput (DatabaseNAME nvarchar(370),
TableName nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))



exec dbo.GlobalSearch


declare @SearchString2 nvarchar(100);
SET @SearchString2 = QUOTENAME('%' + @SearchString + '%','''')


DECLARE @DatabaseName nvarchar(2000), @TableName nvarchar(2000),
@ColumnName nvarchar(2000)
DECLARE @ListOfObj CURSOR
SET @ListOfObj = CURSOR FOR
SELECT DatabaseName, TableName, ColumnName FROM master.dbo.ListOfObj
OPEN @ListOfObj
FETCH NEXT
FROM @ListOfObj INTO @DatabaseName, @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
----------------------------------------
----------------------------------------

declare @s nvarchar(max)
BEGIN
set @s = (
'SELECT count(*) from ' + @DatabaseName +'..' +
@TableName + ' where ' + @ColumnName + ' like ''''%'+
@SearchString+'%''' )


INSERT INTO FinalResultOutput
EXEC
(
'SELECT distinct ''' + @DatabaseName +''',''' + @TableName + ''','''
+ @ColumnName + ''',''' +@s +'''''
FROM [' + @DatabaseName+'].dbo.'+@TableName +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchString2
)



END
----------------------------------------
----------------------------------------
FETCH NEXT FROM @ListOfObj INTO @DatabaseName, @TableName, @ColumnName
END
CLOSE @ListOfObj
DEALLOCATE @ListOfObj

END
GO

exec [GlobalSearch_FinalCall] 'Test'
select * from FinalResultOutput

Monday, May 17, 2021

SQL Server Installation Error

 TITLE: Microsoft SQL Server 2012  Setup
------------------------------

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498220 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0x681D636F%25401428%25401

------------------------------
BUTTONS:

OK
------------------------------

Problem - Dot Net framework 3.5 is not installed on your machine, or higher version is installed

Solution - Install Dot Net Framework 3.5


I tried the solution provided by Microsoft, but it doesn't helped me

.NET Framework 3.5 installation errors: 0x800F0906, 0x800F081F, 0x800F0907, 0x800F0922
"https://docs.microsoft.com/en-US/troubleshoot/windows-client/application-management/dotnet-framework-35-installation-error"

Enable some setting as given in above link
Then Open Control Panel
> Programs ans Features
> Turn Windows Feature On or Off
> Then click " .NetFramework 3.5( include .NET 2.0 and 3.0)"

These steps gives me below error and i follow some other steps

Then i tried to install DotnetFramework 3.5 manually

it throws error

Error Code 0x800F0954 (Windows 10)

Correct steps to install .NET framework manually (see below)

1. Right-click Start, and click Run
2. Type regedit.exe and click OK
3. Go to the following registry key:     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU
4. In the right-pane, if the value named UseWUServer exists, set its data to 0
5. Exit the Registry Editor
6. Restart Windows.


After install .netframework3.5 repeat above steps and revert the setting to original

RESTART machine and you will be able to install the SQL Server

Thursday, May 13, 2021

Oracle | Find Week Start Date and Week End date

 If you want to find out the week start date and week end date from any date, it is possible in Oracle with different way, since their is no direct function available in oracle

SELECT TRUNC(sysdate, 'iw') - 1 AS week_start_date
	,(TRUNC(sysdate, 'iw') + 7 - 1 / 86400) - 1 AS week_end_date
	,CASE 
		WHEN TO_CHAR(sysdate, 'DY') = 'SUN'
			THEN (TRUNC(sysdate + 1, 'iw') + 7 - 1 / 86400) - 1
		ELSE (TRUNC(sysdate, 'iw') + 7 - 1 / 86400) - 1
		END
--If aby custom Week start date or end date, In my case i handled for Sunday as week start date
FROM dual;
 

Salesforce Connection Issue with pentaho Kettle

 If you are trying to connect salesforce with pentaho in order to fetch/insert/update data and you might can face the error 

Error:

Failed : API security token required

 

Problem :

You might be entering wrong token along with your password, or your you havn't generated token

Solution :

Generate Token at saleforce side, or change the security token if you forgot the previous token

web stats