Tuesday, July 20, 2010

Search text values in All Tables and the SPs in same database

Search text values in All Tables and the SPs in same database


This stored procedure/SQL Statement developed/tested in SQL Server 2008 and it has all backward compatability.


· Search text value in All the Stored Procedure in same database


Following statement will search text value in all the SP created in the same database.


SELECT * FROM SYSCOMMENTS WHERE TEXT LIKE'%<search Value>%'


· Search text values in All tables in same database


This SP will search text string in all the database tables in all the columns.


Ex: If you want to search one text like 'Country name: USA' then you run following SP and execute like


Exec SearchInAllTables 'Country name: USA'


You will get all the table name and related column name where this text exists.


USE [Database name]

GO

CREATE PROCEDURE SearchInAllTables

(

@searchString NVARCHAR(1000)

)

AS

BEGIN


CREATE TABLE #OutputResults (ColumnNa NVARCHAR(500), ColumnVal NVARCHAR(4000))

SET NOCOUNT ON


DECLARE @tableNa NVARCHAR(300), @columnNa NVARCHAR(200), @searchString2 NVARCHAR(1000)

SET @tableNa = ''

SET @searchString2 = QUOTENAME('%' + @searchString + '%','''')

WHILE @tableNa IS NOT NULL
BEGIN

SET @columnNa = ''

SET @tableNa =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @tableNa

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@tableNa IS NOT NULL) AND (@columnNa IS NOT NULL)

BEGIN

SET @columnNa =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@tableNa, 2)

AND TABLE_NAME = PARSENAME(@tableNa, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @columnNa

)


IF @columnNa IS NOT NULL

BEGIN

INSERT INTO #OutputResults

EXEC

(

'SELECT ''' + @tableNa + '.' + @columnNa + ''', LEFT(' + @columnNa + ', 3630)

FROM ' + @tableNa + ' (NOLOCK) ' +

' WHERE ' + @columnNa + ' LIKE ' + @searchString2

)

END

END

END

SELECT ColumnNa, ColumnVal FROM #OutputResults

END

GO



Tuesday, July 13, 2010

Error: Unrecognized attribute 'targetFramework'. (Window 7)

Error: Unrecognized attribute 'targetFramework'. (OS- Window 7)


I am using visual studio 2010, IIS 6.1 and OS is window 7, when I create web service using visual studio 2010 and run I am getting this error, after couple of research I found that this error causing because of web service created for .NET Framework 4.0 but IIS is pointing it to .NET Framework 2.0.


You can do the following steps to point your application into .NET framework 4.0


1) Open IIS (type inetmgr on Run window)


2) Go to "Application Pool"


3) Select application folder and double click


4) On the "Edit Application Pool" popup change .NET Framework 2 to 4.


5) Restart IIS

Thursday, July 08, 2010

“Manage Content and Structure” is not visible on Share Point “Site Actions”

"Manage Content and Structure" is not visible on Share Point "Site Actions"


1) Go to "Site Action"


2) Click on "Site Settings"


3) On the "Site Actions" section go to


4) Click on "Manage site features" and


5) Activate "SharePoint Server Publishing"



Now go to the "Site Action" again you will see one more option "Manage Content and Structure"

Note: If you get Runtime error while clicking on "Manage Content and Structure" then do the following steps

1) Click on "Site Actions"

2) Go to "Site Settings"

3) Go to the "Site Collection Administration"

4) Click on "Site Collection features"

5) Activate "SharePoint Server Publishing Infrastructure"

Now again click on "Manage Content and Structure" you will not get any runtime error.