RSS
 

Archive for the ‘English’ Category

SQL Server “SELECT * FROM” Keyboard Shortcut

20 Oct

Think that how many times you write “SELECT * FROM” a day.

You can select data from a table with one key Ctrl+5

Let’s create an SP under master DB. If this SP is under master DB, we can use shortcut property in all DBs.

USE master
 
CREATE proc sp_select
@TABLE_NAME VARCHAR(100),
@COUNT VARCHAR(10) = NULL
AS
BEGIN
 IF EXISTS (SELECT NULL FROM sys.objects WHERE TYPE IN ('U','V') AND name=@TABLE_NAME)
 BEGIN
  IF @COUNT IS NULL
   SET @COUNT=1000
  EXEC('select top ' + @COUNT + ' * from ' + @TABLE_NAME + '(nolock)')
 END
 ELSE
  print 'Table not found.'
END
GO

bbb

SP is created, now we have to assign shortcut to this Stored Procedure; Tools > Options > Environment > Keyboard

We assigned “Ctrl+5” to sp_select as seen in the picture above.

Now, just write table or view name, select the text and hit Ctrl+5.

MY_TABLE_NAME --Selects TOP 1000

or

MY_TABLE_NAME, 50 --Selects TOP 50
 
No Comments

Posted in English, SQL Server

 

Informatica Executive Vice President Girish Pancha and Me

14 Oct

Yesterday I met Mr. Pancha at Corporate Data Management Event in Istanbul which is sponsored by Informatica, Komtaş and Ereteam.

 
No Comments

Posted in English, Informatica

 

Informatica: Target table does not allow INSERT/UPDATE/DELETE

12 Oct

Rows are rejected and in the log detail, error message is;

WRT_8119 Target table [TARGET_TABLE_NAME] does not allow INSERT Row # in bad file
or
WRT_8119 Target table [TARGET_TABLE_NAME] does not allow UPDATE Row # in bad file
or
WRT_8119 Target table [TARGET_TABLE_NAME] does not allow DELETE Row # in bad file

I recommend to change “Treat source rows as” attribute to “Data driven”

This error occurs because of the incoordinate between “Treat source rows as” and target table’s relational writer properties

When Insert is selected, but Update is selected at “Treat source rows as” property, it will get the error;

WRT_8119 Target table [TARGET_TABLE_NAME] does not allow UPDATE Row # in bad file

 
No Comments

Posted in English, Informatica

 

Useful Informatica blogs

06 Oct

http://community.informatica.com/
http://informaticatutorials-naveen.blogspot.com/
http://etl-tools.info/informatica/tutorial.html

To be added… Please send any useful URLs about Informatica.

 
No Comments

Posted in English, Informatica

 

How to list Google’s search results 100 per page

30 Sep

Sometimes you may want to see all Google’s search results 10 pages in one page, instead of to click Next button 9 times!

 

 

Let’s search “Mehmet Susakli” on Google

This is normal search, 10 records per page;

http://www.google.com/search?q=Mehmet+Susakli&hl=en&source=hp&gs_sm=e

To list 100 results in one page just add “&num=100” to the link above. So new URL is;

http://www.google.com/search?q=Mehmet+Susakli&hl=en&source=hp&gs_sm=e&num=100

If you try a number bigger than 100, it will list 100 records. Google is not such stupid to list 1 billion results in one page :)

 
No Comments

Posted in English, General

 

View partial source of HTML pages with webdevaccess

12 Sep

This 115 KB .exe file is ver very useful for web developers, especially who works on pages with ajax or comlex javascript code.

You can view HTML code which is not exist on page load, changed with javascript after some clicks or something else. This tool shows you the HTML code exactly on this time.

It works with IE8, but not tested IE9.

Microsoft published this Explorer add-on for IE5 so that file not exists on MSDN. You can download it here.

 
No Comments

Posted in English, General

 

Select Tables’ Size (MB) of a Database Ordered by Descending

12 Sep

This little script lists all tables’ size of a database. Notice that TotalSize results are in MB.

SELECT OBJECT_NAME(ps.object_id) AS ObjectName,
SUM(ps.reserved_page_count) * 8 /1024 AS TotalSize FROM 
sys.dm_db_partition_stats ps JOIN sys.TABLES t
ON ps.object_id = t.object_id
--You can find out some specific tables:
--AND OBJECT_NAME(ps.object_id) LIKE '%TABLE_NAME%'
GROUP BY ps.object_id
ORDER BY 2 DESC
 
No Comments

Posted in English, SQL Server

 

String search in views’ script

06 Sep

Search all views’ script with a given text in a specific schema.

USE DB_TO_SEARCH
 
SELECT SS.name AS [SCHEMA_NAME],
	SO.NAME AS [VIEW_NAME],
	SC.TEXT AS [VIEW_SCRIPT] FROM SYSCOMMENTS SC (NOLOCK)
INNER JOIN SYSOBJECTS SO (NOLOCK) ON SO.ID = SC.ID
INNER JOIN sys.views SV (NOLOCK) ON SO.ID = SV.object_id
INNER JOIN sys.schemas SS (NOLOCK) ON SV.schema_id = SS.schema_id
WHERE SC.TEXT LIKE '%TEXT_2_SEARCH%'
	AND SS.name='SCHEMA_2_SEARCH' --Remove this line to search all schemas
 
No Comments

Posted in English, SQL Server

 

List Dependent Objects of an Object

23 Aug

You can view dependent objects of an object by right clicking as shown below;

You can also list the objects with the following easy script;

SELECT referencing_class AS Class,
	re.referencing_class_desc AS ClassDesc,
	o.type_desc AS ObjectType,
	re.referencing_schema_name AS SchemaName,
	re.referencing_entity_name AS ObjectName,
	re.referencing_id AS ObjectID
FROM sys.dm_sql_referencing_entities ('dbo.DW_HESAP', 'Object') re
LEFT JOIN sys.objects o ON o.object_id = re.referencing_id
WHERE o.type_desc = 'VIEW' --SQL_STORED_PROCEDURE, CHECK_CONSTRAINT, etc
 
No Comments

Posted in English, SQL Server

 

Cross Apply: select detail data in one cell

16 Aug

Very genious MS SQL function; CROSS APPLY.

As you will see below, this function merges all detail data into one cell with single script. Alternative way is using Cursor.

Let’s do it with an example;

First create and fill the main and detail tables, teachers and students

CREATE TABLE #TEACHERS (
 [ID] INT NOT NULL,
 [NAME] VARCHAR(100),
 [ACTIVE] BIT
)
 
CREATE TABLE #STUDENTS (
 [ID] INT NOT NULL,
 [NAME] VARCHAR(100),
 [TEACHER_ID] INT,
 [ACTIVE] BIT
)
 
INSERT INTO #TEACHERS SELECT 1, 'AHMET OZTURK', 1
INSERT INTO #TEACHERS SELECT 2, 'VELI SENTURK', 1
INSERT INTO #TEACHERS SELECT 3, 'MEHMET KOCATURK', 1
INSERT INTO #TEACHERS SELECT 4, 'ALI KAYATURK', 1
INSERT INTO #TEACHERS SELECT 5, 'SERKAN BOSTURK', 1
 
INSERT INTO #STUDENTS SELECT 1, 'SEMA', 1, 1
INSERT INTO #STUDENTS SELECT 2, 'AYLIN', 1, 1
INSERT INTO #STUDENTS SELECT 3, 'MUSTAFA', 1, 1
INSERT INTO #STUDENTS SELECT 4, 'BURCU', 1, 1
INSERT INTO #STUDENTS SELECT 5, 'FATIH', 2, 1
INSERT INTO #STUDENTS SELECT 6, 'TURGAY', 2, 1
INSERT INTO #STUDENTS SELECT 7, 'KADIR', 2, 1
INSERT INTO #STUDENTS SELECT 8, 'MEHTAP', 3, 1
INSERT INTO #STUDENTS SELECT 9, 'IBRAHIM', 3, 1
INSERT INTO #STUDENTS SELECT 10, 'SONER', 3, 1
INSERT INTO #STUDENTS SELECT 11, 'UGUR', 3, 1
INSERT INTO #STUDENTS SELECT 12, 'CEMIL', 3, 1
INSERT INTO #STUDENTS SELECT 13, 'MURAT', 3, 1
INSERT INTO #STUDENTS SELECT 14, 'SAVAS', 4, 1
INSERT INTO #STUDENTS SELECT 15, 'HAKAN', 4, 1
INSERT INTO #STUDENTS SELECT 16, 'DAVUT', 4, 1



 We created the tables and inserted data. Now we can run the magic script :)

SELECT MAIN_T.NAME AS TEACHER_NAME,
ISNULL(LEFT(DETAIL_T.NAME,LEN(DETAIL_T.NAME)-1),'NO STUDENTS!') AS STUDENTS
FROM #TEACHERS MAIN_T
CROSS APPLY (SELECT NAME + ', ' AS [text()]
             FROM #STUDENTS
             WHERE TEACHER_ID=MAIN_T.ID AND ACTIVE=1 ORDER BY NAME
             FOR XML PATH('')) DETAIL_T (NAME)
WHERE MAIN_T.ACTIVE=1
ORDER BY 1

And the result is:

 
No Comments

Posted in English, SQL Server

 
« Older Entries