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
Archive for the ‘SSMS’ Category
Posted in Computer, Database, Errors&Resolutions, Explanations, How To, SSMS, TSQL, Uncategorized, tagged Catch, Droping, error, Global, handle Try, Handling, objects, sql, table, TSQL, Usama on September 7, 2012| Leave a Comment »
/* 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:
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
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.
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.
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.