Feeds:
Posts
Comments

Archive for the ‘SSMS’ Category

BEGIN TRY
	---------------- EOF ------------------
	DROP TABLE AnyTable
	-----------------------------------------
END TRY

BEGIN CATCH
	PRINT 'Error Handled: ' + ERROR_MESSAGE()
END CATCH

IF OBJECT_ID('tempdb..##AnyGlobalTable') IS NOT NULL
	DROP TABLE ##AnyGlobalTable

Advertisements

Read Full Post »

/* My explation

Think of partition as the number of groups you want the ranking for.
Therefore it will assign a row number starting from 1 to the columns of which the same values appear in the columns specified in the partition section

So if you just have one column there it will count each and every row
If you have two columns it will start the count of rows from 1 for each time the columns values are different.
In other workds: if you have two columns it will count the ranks for two groups.

Let me explain:
Say you have a table with two fields.
In there you store names of fruits in one column and the colour in another:

Mango orange
Mango green
Mango yello

Melon orange
Melon greeen
Melon yellow

If you just use any one of the column to partintion it will give your the rank from 1 -6 (total number of rows = 6).
If you put both the names of the columnns then you have rank for both the groups from (1-3)


declare @a table
(
n varchar(50)
, c varchar(50)
)

INSERT INTO @a
SELECT 'Mango', 'Orange'
UNION ALL
SELECT 'Mango', 'Green'
UNION ALL
SELECT 'Mango', 'Yellow'
UNION ALL
SELECT 'Melon', 'Orange'
UNION ALL
SELECT 'Melon', 'Green'
UNION ALL
SELECT 'Melon', 'Yellow'
SELECT * FROM @a
order by 1, 2

select *
,row_number() OVER (PARTITION BY n ORDER BY n) NameOccurance
,row_number() OVER (PARTITION BY c ORDER BY c) ColourOccurance
,row_number() OVER (PARTITION BY n, c ORDER BY n) BothOccurance
from @a a
order by 1, 3

-- thik of partition as group
-- in plain english for every mango get the colour ranking ordered by the name mango

Read Full Post »

You cannot display randome select statements in functtions unless you are returning one.

You cannoe use temp tables as well.

To come across these prblems use “select into” using a real talbe.

Also be aware that even if a real table is used it will not allow an inser into statement

—————————————————–

in SQL Server 2008 a parameter of table type can be passed as read-only where no update ,delete or insert operations can be used.

http://www.codeproject.com/Articles/37174/Table-Value-Parameter-in-SQL-Server-2008

Read Full Post »

There is no boolena data type is TSQL but instead BIT can be used

An integer data type that can take a value of 1, 0, or NULL.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

— ============
The string values TRUE and FALSE can be converted to bit values:
TRUE is converted to 1 and FALSE is converted to 0.
— ============

Read Full Post »

IP > Server Objects > New Linked Server>

General>>
Linked Server (Any name you want to use)
Other data source (select this one)
Product name (put the IP here)
Data source (put the same IP here)

Security>>
Be made using this security context (select this one)
save the user name and password

Press OK

Read Full Post »

Error: Msg 64, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)

Problem: When I query a server to run large scripts on a different machine it would give the above error (losses the connection  from the connection pool).

Resolution: Copy the script and run it on the same machine.

Read Full Post »