Google Advanced Operators Cheat Sheet

Basic Examples
This Search		Finds Pages Containing…
biking Italy		the words biking and Italy
recycle steel OR iron		information on recycling steel or recycling iron
“I have a dream“		the exact phrase I have a dream
salsa –dance		the word salsa but NOT the word dance
Louis +I France		information about Louis the First (I), weeding out other kings of France
castle ~glossary		glossaries about castles, as well as dictonaries, lists of terms, terminology, etc.
fortune-telling		all forms of the term, whether spelled as a single word, a phrase, or hyphenated
define:imbroglio		definitions of the word imbroglio from the Web
“Nancy * Blachman“		the words Nancy and Blachman separated by exactly one word

<!–

–>

Calculator
Operators		Meaning		Type Into Search Box (& Results)
+ – * /		basic arithmetic		12 + 34 – 56 * 7 / 8
% of		percentage of		45% of 39
^ or **		raise to a power		2^5 or 2**5
the nth root of		nth root		4th root of 16
old units in new units		convert units		300 Euros in USD, 130 lbs in kg, or 31 in hex

Restrict Search
Operators		Meaning		Type Into Search Box (& Results)
city1 city2		Book flights.		sfo bos (Book flights from San Francisco (SFO) to Boston (BOS).)
site:		Search only one website or domain.		Halloween site:www.census.gov (Search for information on Halloween gathered by the US Census Bureau.)
[#]..[#]		Search within a range of numbers.		Dave Barry pirate 2002..2006 (Search for Dave Barry articles mentioning pirates written in these years.)
filetype: (or ext:)		Find documents of the specified type.		Form 1098-T IRS filetype:pdf (Find the US tax form 1098-T in PDF format.)
link:		Find linked pages, i.e., show pages that point to the URL.		link:warriorlibrarian.com (Find pages that link to Warrior Librarian’s website.)

Specialized Information Queries
Operators		Meaning		Type Into Search Box (& Results)
book (or books)		Search full-text of books.		book Ender’s Game (Show book-related information. Note: No colon needed after book.)
define, what is, what are		Show a definition for a word or phrase.		define monopsony, what is podcast (Show a definition for the words monopsony and podcast. Note: No colon after define, what is, or what are.)
define:		Provide definitions for words, phrases, and acronyms from the Web.		define:kerning (Find definitions for kerning from the Web.)
phonebook:		Show all phonebook listings.		phonebook: Disney CA (Search for Disney’s phone numbers in California - CA.)
rphonebook:		Show residential phonebook listings.		rphonebook: bill jones NY (Search for the phone number of every Bill Jones in New York State - NY.)
movie:		Find reviews and showtimes.		movie: traffic (Search for information about this movie, including reviews, showtimes, etc.)
stocks:		Given ticker symbols, show stock information		stocks: goog (Find Google’s current stock price.)
weather		Given a location (US zip code or city), show the weather		weather Seattle WA, weather 81612 (Show the current weather and forecast. Note: No colon after weather.)

Alternative Query Types
Operators		Meaning		Type Into Search Box (& Results)
cache:		Display Google’s cached version of a web page.		cache:www.irs.gov (Show Google’s cached version of the US Internal Revenue Service home page.)
info: (or id:)		Find info about a page.		info:www.theonion.com (Find information about The Onion website.)
related:		List web pages that are similar or related to the URL.		related:www.healthfinder.gov (Find websites related to the Healthfinder website.)

Restrict Search to Sites where Query Words Appear
Operators		Meaning		Type Into Search Box (& Results)
allinanchor:		All query words must appear in anchor text of links to the page.		allinanchor:useful parenting sites (Search for pages that are called useful parenting sites by others.)
inanchor:		Terms must appear in anchor text of links to the page.		restaurants Portland inanchor:kid-friendly (Search for pages on Portland restaurants for which links to the page say they are “kid friendly.”)
allintext:		All query words must appear in the text of the page.		allintext:ingredients cilantro chicken lime (Search for recipes with these three ingredients.)
intext:		The terms must appear in the text of the page.		Dan Shugar intext:Powerlight (Find pages mentioning Dan Shugar where his company, Powerlight, is included in the text of the page, i.e., less likely to be from the corporate website.)
allintitle:		All query words must appear in the title of the page.		allintitle: Google Advanced Operators (Search for pages with titles containing “Google,” “Advanced,”, and “Operators”.)
intitle:		The terms must appear in the title of the page.		movies comedy intitle:top ten (Search for pages with the words movie and comedy that include top ten in the title of the page.)
allinurl:		All query words must appear in the URL.		allinurl:pez faq (Search for pages containing the words pez & faq in the URL.)
inurl:		The terms must appear in the URL of the page.		pharmaceutical inurl:investor (Search for pages in which the URL contains the word investor.)

Restrict Search to Google Groups
Operators		Meaning		Type Into Search Box (& Results)
author:		Find Groups messages from the specified author.		flying author:Hamish author:Reid (Search for Hamish Reid’s articles on flying.)
group:		Find Groups messages from the specified newsgroup.		ivan doig group:rec.arts.books (Search for postings about Ivan Doig in the group rec.arts.books.)
insubject:		Find Groups messages containing crazy quilts in the subject.		insubject:”crazy quilts” (Find articles containing crazy quilts in the subject line.)

Restrict Search to Google News
Operators		Meaning		Type Into Search Box (& Results)
location:		Find News articles from sources located in the specified location.		queen location:uk (Find British news articles on the Queen.)
source:		Find News articles from specified sources.		peace source:ha_aretz (Show articles on peace from the Israeli newspaper Ha’aretz.)

Restrict Search to Froogle
Operators		Meaning		Type Into Search Box (& Results)
store:		Find Froogle products from the specified store.		jeans store:gap (Find Gap jeans.)

Top 10 Obscure Google Search Tricks

 

10. Get the local time anywhere

What time is it in Bangkok right now? Ask Google. Enter simply what time is it to get the local time in big cities around the world, or add the locale at the end of your query, like what time is it hong kong to get the local time there.

9. Track flight status

Enter the airline and flight number into the Google search box and get back the arrival and departure times right inside Google’s search results.

8. Convert currency, metrics, bytes, and more

Google’s powerful built-in converter calculator can help you out whether you’re cooking dinner, traveling abroad, or building a PC. Find out how many teaspoons are in a quarter cup (quarter cup in teaspoons) or how many seconds there are in a year (seconds in a year) or how many euros there are to five dollars (5 USD in Euro). For the geekier set, bits in kilobytes (155473 bytes in kilobytes) and numbers in hex or binary (19 in binary) are also pretty useful.

7. Compare items with “better than” and find similar items with “reminds me of”

Reader Adam taps the wisdom of the crowds by searching for like items using key phrases. He writes in:

Simply search for, in quotes: “better than _keyword_”Some example results:

Results 1 – 100 of about 550 English pages for ” better than WinAmp”.

Results 1 – 57 of 57 English pages for ” better than mIRC”.

Results 1 – 100 of about 17,500 English pages for ” better than Digg”. (Wow. Poor Digg.)

The results will almost always lead you to discovering alternatives to whatever it is you’re searching for. Using the same concept, you can use this trick to discover new music or movies. For example, ” reminds me of _someband_” or “sounds like _someband_” will pull up artists people have thought sounded similar to the one you typed in. This is also a great way to find good, no-name musicians you’d probably never know of otherwise.

Examples:

Results 1 – 88 of 88 English pages for ” reminds me of Metallica”.

Results 1 – 36 of 36 English pages for ” similar to Garden State”.

Results 1 – 66 of 66 English pages for ” sounds like The Shins”.

Just get creative and you’ll, without a doubt, find cool new stuff you probably never knew existed.

6. Use Google as a free proxy

What, your company blocks that hip new web site just because it drops the F bomb occasionally? Use Google’s cache to take a peek even when the originating site’s being blocked, with cache:example.com.

5. Remove affiliate links from product searches

When you’re sick of seeing duplicate product search results from the likes of eBay, Bizrate, Pricerunner, and Shopping.com, clear ‘em out by stacking up the -site:ebay.com -site:bizrate.com -site:shopping.com operator. Alternately, check out Give Me Back My Google (original post), a service that does all that known reseller cleaning up for you when you search for products. Compare this GMBMG search for a Cruzer 1GB flash drive to the regular Google results.

4. Find related terms and documents

Ok, this one’s direct from any straight-up advanced search operator cheat sheet, but it’s still one of the lesser-used tricks in the book. Adding a tilde (~) to a search term will return related terms. For example, Googling ~nutrition returns results with the words nutrition, food, and health in them.

3. Find music and comic books

Using a combination of advanced search operators that specify music files available in an Apache directory listing, you can turn Google into your personal Napster. Go ahead, try this search for Nirvana tracks: -inurl:(htm|html|php) intitle:"index of" +"last modified" +"parent directory" +description +size +(wma|mp3) "Nirvana". (Sub out Nirvana for the band you’re interested in; use this one in conjunction with number 7 to find new music, too.) The same type of search recipe can find comic books as well.

2. ID people, objects, and foreign language words and phrases with Google Image Search

Google Image search results show you instead of tell you about a word. Don’t know what jicama looks like? Not sure if the person named “Priti” who you’re emailing with is a woman or a man? Spanish rusty and you forgot what “corazon” is? Pop your term into Google Image Search (or type image jicama into the regular search box) to see what your term’s about.

 

1. Make Google recognize faces

If you’re doing an image search for Paris Hilton and don’t want any of the French city, a special URL parameter in Google’s Image search will do the trick. Add &imgtype=face to the end of your image search to just get images of faces, without any inanimate objects. Try it out with a search for rose (which returns many photos of flowers) versus rose with the face parameter.

What’s your favorite ninja Google search technique? Tell us about it in the comments.

SQL Server – Find Field Value in Database

The following is a SQL Script that can be run in a database to return all tables and columns where a particular value is present.  This can be used for strings or values with a small modification.

This type of thing is great when moving applications/products between servers.  This is certainly a good script to include in your master table to be used over and over.

DECLARE @value VARCHAR(64) 
DECLARE @sql VARCHAR(1024) 
DECLARE @table VARCHAR(64) 
DECLARE @column VARCHAR(64)

SET @value = ‘valuehere’

CREATE TABLE #t ( 
    tablename VARCHAR(64), 
    columnname VARCHAR(64) 
)

DECLARE TABLES CURSOR 
FOR

    SELECT o.name, c.name 
    FROM syscolumns c 
    INNER JOIN sysobjects o ON c.id = o.id 
    WHERE o.type = ‘U’ AND c.xtype IN (167, 175, 231, 239) 
    ORDER BY o.name, c.name

OPEN TABLES

FETCH NEXT FROM TABLES 
INTO @table, @column

WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sql = ‘IF EXISTS(SELECT NULL FROM [' + @table + '] ‘ 
    –SET @sql = @sql + ‘WHERE RTRIM(LTRIM([' + @column + '])) = ”’ + @value + ”’) ‘ 
    SET @sql = @sql + ‘WHERE RTRIM(LTRIM([' + @column + '])) LIKE ”%’ + @value + ‘%”) ‘ 
    SET @sql = @sql + ‘INSERT INTO #t VALUES (”’ + @table + ”’, ”’ 
    SET @sql = @sql + @column + ”’)’

    EXEC(@sql)

    FETCH NEXT FROM TABLES 
    INTO @table, @column 
END

CLOSE TABLES 
DEALLOCATE TABLES

SELECT * 
FROM #t

DROP TABLE #t

Working with files and the FileSystemObject

By Darren Green
Version 7.0/2000
Level Intermediat

The ability to work with files gives us great flexibility in how we use DTS. It allows us to add some intelligence into a package, and cope with unexpected situations automatically. The key to working with files is the Scripting Run Time as this gives us the FileSystemObject, which can be fully exploited through ActiveX Script Tasks, Workflow scripts, and even transformations, inside your DTS packages. We start by demonstrating some simple file manipulation procedures, and then move on to how we can fully integrate them into a DTS package and make our solutions more flexible.

Full documentation from Microsoft on the FileSystemObject object and related objects can be found here

Copy File

This shows a simple file copy operation, using hardcoded source and destination filenames.

' Copy File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"
	sDestinationFile = "C:\DestinationFile.txt"

	oFSO.CopyFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Move or Rename File

This shows a simple file move operation, using hardcoded source and destination filenames. There is no explicit rename method in the scripting object, but a move is just the same.

' Move File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"
	sDestinationFile = "C:\Folder\DestinationFile.txt"

	oFSO.MoveFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Delete File

This shows a slightly more advanced delete operation. When using the DeleteFile method, if the file does not exist, an error is raised (File not found). We use the FileExists method to check for the file, and only call the DeleteFile when required. For another example of using FileExists see the article How can I check if a file exists?

' Delete File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"

	' Check if file exists to prevent error
	If oFSO.FileExists(sSourceFile) Then
		oFSO.DeleteFile sSourceFile
	End If

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Using Global Variables

To try and make our packages more flexible we should avoid using hard coded filenames as much as possible. One good method of doing this is to store all parameters required in global variables. In this way, no matter how many times we use a filename or file path inside our package, we only have to make one change. This sample is a move operation again, and uses two global variables. The source filename is held in one global variable, and the destination path or folder is held in a second. We are only supplying the destination folder in this example, so the existing filename will be preserved. It is important that the path is qualified with a backslash, so we use a helper function QualifyPath to ensure this is the case.

' Global Variable Move
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value
	sDestinationFile = QualifyPath(DTSGlobalVariables("DestinationPath").Value)

	MsgBox sSourceFile
	MsgBox sDestinationFile
	oFSO.MoveFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Function QualifyPath(ByVal sPath)
	If Right(sPath, 1) = "\" Then
		QualifyPath = sPath
	Else
		QualifyPath = sPath & "\"
	End If
End Function

File Date & Time

A simple validation check we may wish to do would be to check the data and time of a file to ensure it has been created within a defined time period. In this example we check that the file has been created today, otherwise we return a failure result from the task. In some case you may not want to fail the task, which in turn leads to a failed package result, in which case the code should be placed inside a Workflow script instead. For an example of using Workflow scripts to avoid failure conditions, see the article How can I check if a file exists?

' File Date & Time
Option Explicit

Function Main()

	Dim oFSO
	Dim oFile
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value

	Set oFile = oFSO.GetFile(sSourceFile)

	If oFile.DateCreated < Date Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End If

	' Clean Up
	Set oFile = Nothing
	Set oFSO = Nothing
End Function

File Size

Another simple validation check similar to the date check shown above uses the Size property. In this example we will return an error if the file is empty or 0 bytes in size.

' File Size
Option Explicit

Function Main()

	Dim oFSO
	Dim oFile
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value

	Set oFile = oFSO.GetFile(sSourceFile)

	If oFile.Size > 0 Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End If

	' Clean Up
	Set oFile = Nothing
	Set oFSO = Nothing
End Function

Using Connections

The majority of your file manipulations will be related to files used by connections within your DTS package. A common requirement is to be import a file of a changing name, for which many people would first think of using the move or rename method shown above, but why not just change the connection instead? You can see how to do this in the article How can I change the filename for a text file connection?

The obvious integration of connections into your file manipulations is to read or set a filename on a connection, and use that within the rest of your script. Connections have a DataSource property, and for Text File connections and Jet (Excel, Access) connections, this property is the filename. For DBase and FoxPro connections then DataSource is the folder, the filename or table will be selected in the task that uses the connection.

In this simple example we will read the filename from a connection and then rename the file, such as you may want to do after importing a file to indicate that the file has been dealt with. To get reference to the connection object we use the name, this is what you see as the label in the designer and it is also shown in the “Existing connection” drop-down in the connection properties dialog.

' Rename File from Connection
Option Explicit

Function Main()

	Dim oPkg
	Dim oConn
	Dim oFSO
	Dim oFile
	Dim sFilename

	' Get reference to the current Package object
	Set oPkg = DTSGlobalVariables.Parent

	' Get reference to the named connection
	Set oConn = oPkg.Connections("Text File (Source)")

	' Get the filename from the connection
	sFilename = oConn.DataSource

	' Rename the file
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	oFSO.MoveFile sFilename, sFilename & ".bak"

	' Clean Up
	Set oConn = Nothing
	Set oPkg = Nothing
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

How can I check if a file exists?

By Darren Green
Version 2000
Level Intermediate

Checking for the existence of a file before trying to import it can be very useful in ensuring your systems run smoothly. You can use the Scripting FileSystemObject to check for the presence of a file using ActiveX Script. If the file exists you can obviously import it or initiate an alternative process, such as a sending a warning to someone or just ignore this error all together.

The simplest method is to use an ActiveX Script Task that returns a success (DTSTaskExecResult_Success) or failure (DTSTaskExecResult_Failure) result as appropriate. The following example reads the filename (DataSource) property from a named connection, and returns success or failure based upon the existence of the file:

' Pkg 211 (File Exists - 1)
Option Explicit

Function Main()
        Dim oFSO, sFileName

        ' Get the name of the file from the Connection "Text File (Source)"
        sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Main = DTSTaskExecResult_Success
        Else
                Main = DTSTaskExecResult_Failure
        End If

        Set oFSO = Nothing
End Function

The problem with using an ActiveX Script Task is that because the task fails, so does the package. This is often inappropriate, for example you may have package scheduled every 15 minutes that imports a file. This file is only produced when certain conditions have been met on an external system, and the absence of the file is by no means cause for concern. This is where you can use the power of an ActiveX Workflow Script. Workflow scripts use the DTSStepScriptResult constants, one of which is DTSStepScriptResult_DontExecuteTask. As you’d expect from the name, the task associated with this step does not execute, but importantly it does not raise any errors either.

To use an ActiveX workflow script, select the task that must be prevented from executing when the file is missing. Right-click Workflow Properties, or Workflow followed by Properties as appropriate. On the Options tab select “Use ActiveX Script”, and then click Properties. You will now need to code the checking mechanism and return the appropriate result. DTSStepScriptResult_ExecuteTask allows the task to execute and DTSStepScriptResult_DontExecuteTask blocks the workflow path and prevents the task from executing.

The example below takes it’s filename name from a global variable, purely to demonstrate and alternative mechanism of supplying this information. Reading it from a connection as above would work equally well. The actual check using the FileExists method is the same:

' Pkg 211 (File Exists - 2)
Option Explicit

Function Main()
        Dim oFSO, sFileName

        ' Get the name of the file from the global variable "ImportFileName"
        sFilename = DTSGlobalVariables("ImportFileName").Value

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Main = DTSStepScriptResult_ExecuteTask
        Else
                Main = DTSStepScriptResult_DontExecuteTask
        End If

        Set oFSO = Nothing
End Function

Global Variables and SQL statements in DTS

By Darren Green
Version 7.0+/2000
Level Advanced

For those fortunate enough to have SQL 2000, using global variables with an Execute SQL Task, or as part of the source statement in the DataPump task is now a standard feature. For those still using SQL Server 7.0 this tedious process, but here are some examples of how it can be done. Even if you are using SQL Server 2000, you may still have a need for this due to certain syntax limitations imposed when using parameters.

In SQL Server 7.0 the only way to integrate a global variable into your SQL is to actually code the value as a literal in the SQL statement. The simplest solution is to just build a new statement with the value embedded in it at run-time. You can do this from an ActiveX Script Task as illustrated below.

This example amends the SELECT statement for a DataPump task, to filter based on the value of the global variable HireDate:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
	Dim oPkg, oDataPump, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "SELECT * FROM dbo.employee WHERE hire_date > '" & _
		DTSGlobalVariables("HireDate").Value & "'"

	' Get reference to the DataPump Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

	' Assign SQL Statement to Source of DataPump
	oDataPump.SourceSQLStatement = sSQLStatement

	' Clean Up
	Set oDataPump = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

A similar method can be used to alter the Execute SQL Task:

' 205 (Change SQLStatement)
Option Explicit

Function Main()
	Dim oPkg, oExecSQL, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "INSERT dbo.NewEmployees" & vbCrLf & _
		"SELECT * FROM dbo.employee" & vbCrLf & _
		"WHERE hire_date > '" & DTSGlobalVariables("HireDate").Value & "'"

	' Get reference to the Exec SQL Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask

	' Assign SQL Statement to Exec SQL Task
	oExecSQL.SQLStatement = sSQLStatement

	' Clean Up
	Set oExecSQL = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

 

To save time and reduce potential errors there is a custom task available from the Archive page which will prefix the SQL with T-SQL declares for all global variables in a package.

Global Variables and Stored Procedure Parameters

By Darren Green
Version 2000

Input Parameters

One of the benefits of the SQL Server 2000 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL. This means you can use the global variable as an input parameter for your stored procedure.

Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call your stored procedure, using a question mark as the placeholder for your parameter:

EXEC dbo.spInputTest ?

 

Once you have entered the SQL statement you can click Parameters and you will be presented with the Input Parameters tab. The mapping list view will have one row for each placeholder found in the SQL. There parameters have no meaningful names, they are listed in order, so Parameter 1 will be the first meaningful found, Parameter 2 will be the second placeholder found on so on. For each parameter found you can select a global variable to map to that parameter. At run time the provider (ODBC or OLE-DB) parameter support will be invoked and the mapped global variable value will be substituted for the placeholder.

It is important to note that the parameter handling uses the provider parameter support since not all providers support parameters, and others provide only limited or unreliable support. Obviously the Microsoft SQL Server providers have full support.

Another important point about parameters is that just like when using local variables in SQL they cannot be used to parameterise object names such as procedure names, table names or column names. In a normal SQL statement you can workaround this by using dynamic SQL, but in DTS you have the option of dynamically building the SQL statement. For more on this strategy see Global Variables and SQL statements in DTS

Ouput Parameters

You can also capture stored procedure output parameters, although this cannot be done through the same placeholder mechanism. This simple example demonstrates how you can capture stored procedure return values and output parameters into global variables using the Output Parameter functionality of the Execute SQL Task in SQL Server 2000.

The Execute SQL Task supports two types or output parameter types, row values and rowsets. There is no placeholder functionality as used for input parameters. Confusion often arises when people try and retrieve values from the input parameter placeholder. In this case we have to use a SELECT statement to return our values as normal query rowset and map them in this way.

To build a simple demonstration package, first create the stored procedure below:

CREATE PROCEDURE dbo.spReturnAndOutputTest
    @OutTest int OUTPUT
AS
  SET @OutTest = 9
  RETURN 1

 

Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call our stored procedure and return the values as a query result or rowset:

DECLARE @ReturnValue int, @OutTest int
EXEC @ReturnValue=dbo.spReturnAndOutputTest @OutTest OUTPUT
SELECT @ReturnValue AS ReturnValue, @OutTest AS OutTest

 

Once you have entered the SQL statement you can click Parameters and select the Output Parameters tab. Select the Output Parameter Type of Row Value. The columns of our SELECT, the values we want to return, are shown in the left hand column of the Parameter Mapping grid, using the names specified as column aliases in the SELECT statement. They can now be mapped to global variables of your choice.

Input and Output Parameters

To use both input and ouput parameters for the same stored procedure call, a combination of the two methods described above might look like this:

DECLARE @OutputTest int
EXEC dbo.spInputAndOutputTest ?,  @OutputTest OUTPUT
SELECT @OutputTest AS OutputTest

 

Unfortunately when you click on the Parameters button you will get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation

The error is raised by the design-time syntax validation routine. Fortunately the run-time routine is more forgiving than the design-time routine. The way to workaround this is to substitute your stored procedure call with a simple SQL statement that produces the same meta-data, which will allow you to map the parameters as required. Below is our sample stored procedure:

CREATE PROCEDURE dbo.spInputAndOutputTest
    @InputTest int,
    @OutputTest int OUTPUT
AS
  SET @OutputTest = @InputTest * 2

 

The following simple SQL statement, whilst functionaly different, produces the same meta-data as our real SQL statement:

SELECT id AS OutputTest FROM sysobjects where id = ?

 

Enter the meta-data matching SQL and click Parameters. We can then map the input parameter as in the first example. Next select Output Parameters and go onto select a Row Value output parameter as in the second example. Once complete close Execute SQL Task.

The parameter mapping has now been done, but to complete the operation you need replace the matching SQL with the real SQL. To do this open the Disconnected Edit feature (found on the Package menu). Expand the Tasks node, and select the correct Execute SQL Task, e.g. DTSTask_DTSExecuteSQLTask_1. Now double click the SQLStatement property for our selected task, and overwrite the matching SQL with the real SQL as shown at the top of this example. Execute the package.

In summary, you can use some dummy SQL to setup the parameter mapping at design-time, but then change the SQL afterwards. This does mean that if you ever need to change the parameter mappings you must repeat the substitution process to setup the new mappings.

Using SET NOCOUNT ON

If your stored procedure performs one or more DML operations that cause informational resultsets to be generated, then these will confuse the task. An informational resultset will be familiar to anyone that uses Query Analyser, as you will have seen them as messages in results, for example (1 row(s) affected). These messages are indistinguishable from the SELECT resultset you are really interested in, and since the task reads the first resultset it encounters, an informational resultset can mask your real data. To suppress this behaviour add the SET NOCOUNT ON statement to the top or your stored procedure or script.

SINGARAYA KONDA

Sri Lakshmi Narasimha Swamy - It is famous temple of God Sri Lakshmi Narasimha Swamy which is located at Patha Singaraya Konda near 30 kms to Ongole (Prakasam District, Andhra Pradesh),which dates back to the 15th century. The grand Brahmostavaalu celebrations held in june every year, attracts a large number of devotees from all over.

To view temple visually please click the below link.
http://www.flickr.com/photos/34087317@N08/sets/72157612140937017/

SQL Interview Questions

1. What are two methods of retrieving SQL?
2. What cursor type do you use to retrieve multiple recordsets?
3. What is the difference between a “where” clause and a “having” clause? - “Where” is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
4. What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause. 
5. What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
6. What are the tradeoffs with having indexes? – 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
7. What is a “join”? - ‘join’ used to connect two or more tables logically with or without common field.
8. What is “normalization”? “Denormalization”? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
9. What is a “constraint”? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE – enforces uniqueness of a particular table column. DEFAULT – specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY – validates that every value in a column exists in a column of another table. CHECK – checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
10. What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: – B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. – Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) – Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
11. What is a “primary key”? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from 
    database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about 
    your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.
12. What is a “functional dependency”? How does it relate to database table design? – Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.
13. What is a “trigger”? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.
14. Why can a “group by” or “order by” clause be expensive to process? – Processing of “group by” or “order by” clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
15. What is “index covering” of a query? – Index covering means that “Data can be found only using indexes, without touching the tables”
16. What types of join algorithms can you have?
17. What is a SQL view? – An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

 

1. Which of the following has the highest order of precedence?
   • Functions and Parenthesis
   • Multiplication, Division and Exponents
   • Addition and Subtraction
   • Logical Operations
2. When designing a database table, how do you avoid missing column values for non-primary key columns?
   • Use UNIQUE constraints
   • Use PRIMARY KEY constraints
   • Use DEFAULT and NOT NULL constraints
   • Use FOREIGN KEY constraints
   • Use SET constraints
3. Which of the following is the syntax for creating an Index?
   • CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)
   • CREATE [UNIQUE] INDEX OF tbl_name (index_columns)
   • CREATE [UNIQUE] INDEX ON tbl_name (index_columns)
   • CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
4. Which of the following is not a valid character datatype in SQL Server?
   • BLOB
   • CHAR
   • VARCHAR
   • TEXT
   • VARTEXT
5. Which of the following statements about SQL Server comments is false?
   • /* … */ are used for multiline comments
   • // is used for single line comments
   • – is used for single line comments
   • Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
   • ‘ is used for single line comments
6. Consider the following transaction code:
   
   Begin Transaction
Update names_table set employee_name = "Ramesh" where employee_name = "Mahesh"
Save Transaction SAVE_POINT
Update salaries set salary=salary + 900 where employee_job = "Engineer"
Rollback transaction
Commit transaction
   What will be the result produced by this transaction?   
   • “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be

updated

1.  
   • Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
   • “Ramesh” will be updated to “Mahesh” and salary of engineers will also be

updated.

1. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
   • DEFAULT and NOT NULL constraints
   • FOREIGN KEY constraints
   • PRIMARY KEY and UNIQUE constraints
   • IDENTITY columns
   • CHECK constraints
2. Which of the following are not date parts?
   • quarter
   • dayofweek
   • dayofyear
   • weekday
3. The IF UPDATE (column_name) parameter in a trigger definition will return
   TRUE in case of an INSERT statement being executed on the triggered table:   
   • Yes
   • No
   • It returns TRUE only if an UPDATE query is executed
   • Both b and c
4. Which one of the following must be specified in every DELETE statement?
   • Table Name
   • Database name
   • LIMIT clause
   • WHERE clause
   • Column Names
5. Which one of the following correctly selects rows from the table myTable that have null in column column1?
   • SELECT * FROM myTable WHERE column1 is null
   • SELECT * FROM myTable WHERE column1 = null
   • SELECT * FROM myTable WHERE column1 EQUALS null
   • SELECT * FROM myTable WHERE column1 NOT null
   • SELECT * FROM myTable WHERE column1 CONTAINS null
6. Is this statement true or false:
   A cursor is a pointer that identifies a specific working row within a set   
   • True
   • False
7. Which of the following commands is used to change the structure of table?
   • CHANGE TABLE
   • MODIFY TABLE
   • ALTER TABLE
   • UPDATE TABLE
8. Consider the following statements and pick the correct answer:
   
   1. ceiling() - returns the smallest integer greater than or equal to the specified value
2. floor() - returns the largest integer less than or equal to the specified value   
   • 1 is true and 2 is false
   • 1 is false and 2 is true
   • Both 1 and 2 are true
   • Both 1 and 2 are false
9. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?
   • SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
   • SELECT * FROM Persons SORT REVERSE ‘FirstName’

c . SELECT * FROM Persons ORDER BY ‘FirstName’

1.  
   • SELECT * FROM Persons ORDER BY FirstName DESC

SELECT * FROM Persons ORDER BY DESC FirstName

1. What is the maximum value that can be stored for a datetime field?
   • Dec 31, 9999
   • Jun 6, 2079
   • Jan 1, 2753
   • Jan 1, 2100
2. Consider the following queries:
   1. select * from employee where department LIKE "[^F-M]%”;
2. select * from employee where department = “[^F-M]%”;
   Select the correct option:   
   • Query 2 will return an error
   • Both the queries will return the same set of records
   • Query 2 is perfectly correct
   • Query 2 would return one record less than Query 1
3. How can you view the structure of a table named “myTable” in SQL Server?
   • desc myTable
   • desc table myTable
   • sp_columns myTable
   • None of the above
   • Using either option a or c
4. What does referential integrity (also called relational integrity) prevent?
   • Loss of data from employee sabotage
   • Loss of data from any one corrupted table
   • Recursive joins
   • One-to-many or many-to-many relationships between columns in a table
   • Data redundancy
5. Which of the following is not a global variable?
   • @@colcount
   • @@error
   • @@rowcount
   • @@version
   • All are valid global variables
6. Consider the following two tables:1. customers( customer_id, customer_name)
   2. branch ( branch_id, branch_name )
   What will be the output if the following query is executed:
   Select * branch_name from customers,branch   
   • It will return the fields customer_id, customer_name, branch_name
   • It will return the fields customer_id, customer_name, branch_id, branch_name
   • It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
   • It will return an empty set since the two tables do not have any common field name
   • It will return an error since * is used alone for one table only

    

    

    

7. Which of the following is not a control statement?
   • if…else
   • if exists
   • do…while
   • while
   • begin…end
8. Which of the following is not a valid Numeric datatypes in SQL Server?
   • INT
   • SMALLINT
   • TINYINT
   • BIGINT
   • MONEY
9. Which of the following datatypes is not supported by SQL-Server?
   • Character
   • Binary
   • Logical
   • Date
   • Numeric
   • All are supported
10. What will the output be if you try to perform arithmetic on NULL values?
    • 0
    • NULL
    • It will generate an error message
    • Can’t be determined
11. Which of the following options is not correct about the DATEDIFF() function?
    • It returns the difference between parts of two specified dates
    • It takes three arguments
    • It returns a signed integer value equal to second date part minus first date part
    • It returns a signed integer value equal to first date part minus second date part
12. Sample Code
    
    CREATE TABLE table1(
column1 varchar(50),
column2 varchar(50),
column3 varchar(50),
column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?   
    • ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;
    • MODIFY TABLE table1 ADD column2a AFTER column2;
    • INSERT INTO table1 column2a AS varchar(50) AFTER column2;
    • ALTER TABLE table1 INSERT column2a varchar(50) AFTER column2;
    • CHANGE TABLE table1 INSERT column2a BEFORE column3;
13. State which of the following are true
    • Views are a logical way of looking at the logical data located in the tables
    • Views are a logical way of looking at the physical data located in the tables
    • Tables are physical constructs used for storage and manipulation of data in databases
    • Tables are logical constructs used for storage and manipulation of data in databases
14. Which of the following is not a valid binary datatype in SQL Server?
    • BINARY
    • VARBINARY
    • BIT
    • IMAGE
    • TESTAMP
15. Which of the following is false with regards to sp_help?
    • When a procedure name is passed to sp_help, it shows the parameters
    • When a table name is passed to sp_help, it shows the structure of the table
    • When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
    • All of the above are true
    • Which of the following are false for batches (batch commands)?
      • Statements in a batch are parsed, compiled and executed as a group
      • None of the statements in the batch is executed if there are any syntax errors in the batch
      • None of the statements in the batch is executed if there are any parsing errors in the batch
      • None of the statements in the batch is executed if there are any fatal errors in the batch
    • Select the correct option:
      • Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
      • Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer

VB 6 Interview Questions

1. How do you center a form?
2. Can I send keystrokes to a DOS application?
3. Convert an RGB value to a long, or a long to RGB.
4. Implement smooth scrolling for either text, graphics or controls across a form.
5. Implement some quick and easy encryption (can be something primitive).
6. 4 different types of sorts: advantages and disadvantages.
7. Compute CRC32 checksum, write a quick piece of code that accepts the packet of data and returns the CRC.
8. How do you use the Mouse OFF event?
9. How do I call Windows Help files from a VB program?
10. How do I create a textbox that lets you insert tabs?
11. How do I make text box that displays asterisks when the user types in data such as password?
12. How do I create multi-column combo box?
13. How do I make a menu popup from a CommandButton?
14. How to create menus at run time in VB?
15. Write a generic error handling routine.
16. How to copy text to the Windows clipboard and from it.
17. How can I call a Command button without clicking it?
18. Write a simple app with Encrypt and Decrypt buttons and Textbox where the user can enter text for encryption and decryption.
19. 3 main differences between flexgrid control and dbgrid control
20. ActiveX and Types of ActiveX Components in VB
21. Advantage of ActiveX Dll over ActiveX Exe .
22. Advantages of disconnected recordsets . Benefit of wrapping database calls into MTS transactions 6. Benefits of using 23 MTS
23. Can database schema be changed with DAO, RDO or ADO?
24. Can you create a tabletype of recordset in Jet-connected ODBC database engine?
25. Constructors and destructors
26. Controls which do not have events
27. Default property of datacontrol
28. Define the scope of Public, Private, Friend procedures?
29. Describe Database Connection pooling relative to MTS
30. Describe: In of Process vs. Out of Process component. Which is faster?
31. Difference between a function and a subroutine, Dynaset and Snapshot, early and late binding, image and picture controls, linked object and embedded Object,listbox and combo box,Listindex and Tab index,modal and moduless window, Object and Class, query unload and unload in form, declaration and instantiation of an object?
32. Draw and explain Sequence Model of DAO
33. How can objects on different threads communicate with one another?
34. How can you force new objects to be created on new threads?
35. How does a DCOM component know where to instantiate itself?
36. How do I register a component?
37. How do I set a shortcut key for label?
38. What kind of components can be used as DCOM servers?
39. Name of the control used to call a Windows application
40. Name the four different cursor and locking types in ADO and describe them briefly
41. Need of zorder method, no of controls in form, Property used to add a menus at runtime, Property used to count number of items in a combobox,resize a label control according to your caption.
42. Return value of callback function, The need of tabindex property
43. Thread pool and management of threads within a thread pool
44. To set the command button for ESC, Which property needs to be changed?
45. Type Library and what is it’s purpose?
46. Types of system controls, container objects, combo box
47. Under the ADO Command Object, what collection is responsible for input to stored procedures?
48. What are the ADO objects? Explain them.
49. What are the different compatibility types when we create a COM component?
50. What do ByVal and ByRef mean and which is the default?
51. What does Option Explicit refer to? 36. What does the Implements statement do?
52. What is OLE and DDE? Explain.
53. What is the difference between Msgbox Statement and MsgboxQ function?
54. What keyword is associated with raising system level events in VB?
55. What methods are called from the ObjectContext object to inform MTS that the transaction was successful or unsuccessful?
56. What types of data access have you used.
57. What was introduced to Visual Basic to allow the use of Callback Functions?
58. Which controls can not be placed in MDI?
59. Which controls have refresh method, clear method
60. Which Property is used to compress a image in image control?
61. Which property of menu cannot be set at run time?
62. Which property of textbox cannot be changed at runtime?
63. What is the maximum size of a textbox?
64. Which tool is used to configure the port range and protocols for DCOM communications?

 

 

---

 
Questions to ask VB Programmer

 

 

1. 3 main differences between flexgrid control and dbgrid control
2. ActiveX and Types of ActiveX Components in VB
3. Advantage of ActiveX Dll over Active Exe
4. Advantages of disconnected recordsets
5. Benefit of wrapping database calls into MTS transactions
6. Benefits of using MTS
7. Can database schema be changed with DAO, RDO or ADO?
8. Can you create a tabletype of recordset in Jet – connected ODBC database engine?
9. Constructors and distructors
10. Controls which do not have events
11. Default property of datacontrol
12. Define the scope of Public, Private, Friend procedures?
13. Describe Database Connection pooling relative to MTS
14. Describe: In of Process vs. Out of Process component. Which is faster?
15. Difference between a function and a subroutine, Dynaset and Snapshot,early and late binding, image and picture controls,Linked Object and Embedded Object,listbox and combo box,Listindex and Tabindex,modal and moduless window, Object and Class,Query unload and unload in form, Declaration and Instantiation an object?
16. Draw and explain Sequence Modal of DAO
17. How can objects on different threads communicate with one another?
18. How can you force new objects to be created on new threads?
19. How does a DCOM component know where to instantiate itself?
20. How to register a component?
21. How to set a shortcut key for label?
22. Kind of components can be used as DCOM servers
23. Name of the control used to call a windows application
24. Name the four different cursor and locking types in ADO and describe them briefly
25. Need of zorder method, no of controls in form, Property used to add a menus at runtime, Property used to count number of items in a combobox,resize a label control according to your caption.
26. Return value of callback function, The need of tabindex property
27. Thread pool and management of threads within a thread pool
28. To set the command button for ESC, Which property needs to be changed?
29. Type Library and what is it’s purpose?
30. Types of system controls, container objects, combo box
31. Under the ADO Command Object, what collection is responsible for input to stored procedures?
32. VB and Object Oriented Programming
33. What are the ADO objects? Explain them.
34. What are the different compatibility types when we create a COM component?
35. What do ByVal and ByRef mean and which is the default?
36. What does Option Explicit refer to?
37. What does the Implements statement do?
38. What is OLE and DDE? Explain.
39. What is the difference between Msgbox Statement and MsgboxQ function?
40. What keyword is associated with raising system level events in VB?
41. What methods are called from the ObjectContext object to inform MTS that the transaction was successful or unsuccessful?
42. What types of data access have you used.
43. What was introduced to Visual Basic to allow the use of Callback Functions?
44. Which controls can not be placed in MDI?
45. Which controls have refresh method, clear method
46. Which Property is used to compress a image in image control?
47. Which property of menu cannot be set at run time?
48. Which property of textbox cannot be changed at runtime and What’s the maximum size of a textbox?
49. Which tool is used to configure the port range and protocols for DCOM communications?