Coderock

--------------------------------
link hover image

SQL: get previous from max value

Frontpage calendar image November 29, 2015

select max(card_no),vehicle_number FROM WBG.WBG_01_01 where card_no < (Select max(card_no) from WBG.WBG_01_01 group by vehicle_number) group by vehicle_number Source: stackoverflow

link hover image

SQL : Remove Special Characters except Space.

Frontpage calendar image November 14, 2015

DECLARE @str VARCHAR(400) –Add your specialcharacters here DECLARE @specialchars VARCHAR(50) = ‘%[~,@,#,$,%,&,*,(,),.,!^?:]%’ SET @str = ‘KRA!NTHI@#KUMAR, KU%^?MAR GO~()$U.:D’ WHILE PATINDEX( @specialchars, @str ) > 0 —Remove special characters using Replace function SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @specialchars, @str ), 1 ),”),’-‘,’ ‘) SELECT @str   Source: www.sqlservercentral.com

link hover image

How to export all data from table to an insertable sql format?

Frontpage calendar image November 10, 2015

Quick and Easy way: Right click database Point to tasks Select generate scripts Click next Choose tables Click next Click advanced Scroll to data to script – Called types of data to script in SMSS 2014 Select data only Click next and generate your script Source: stackoverflow

link hover image

The data type of substitution parameter 1 does not match the expected type of the format specification

Frontpage calendar image July 13, 2015

RAISERROR(‘Test Error Number: %d.’,16,1,@MyNumber) When the trigger is executed with the value 1 passed in the @MyNumber parameter, the result is: Msg 2786, Level 16, State 1, trigger trg_delete, Line 19 The data type of substitution parameter 1 does not match the expected type of the format specification. If the datatype for @MyNumber is changed to […]

link hover image

Error:”Index was outside the bounds of the array”

Frontpage calendar image May 24, 2015

I had a problem with “SQL Server Freetext and Contains functions at Godaddy webhosting”. Solution: I upgraded my SqlServer management studio from 2008 to 2012 Or Download the service packs of SqlServer Management Studio and update probably resolve you solution. This solved the prblem. Source: stackoverflow

link hover image

How to use SQL Server Management Studio

Frontpage calendar image November 4, 2014

SQL Server comes with a couple of neat features that make it very easy to capture an execution plan, simply make sure that the “Include Actual Execution Plan” meny item (found under the “Query” menu) is ticked and run your query as normal. Actual Execution Plan – (CTRL + M) – is created after execution […]

link hover image

SQL Error: The version of SQL Server in use does not support datatype ‘datetime2’

Frontpage calendar image September 19, 2014

The DBA doesn’t use datetime2 because it’s new in SQL Server 2008 and the site runs on 2005. It turns out that Entity Framework 4 somehow got the idea to use SQL Server 2008. The fix was to edit the .edmx file in an XML editor and set the ProviderManifestToken=”2005″ instead of 2008. Here’s how […]

link hover image

SQL Creating Full Text Catalog and Full Text Search

Frontpage calendar image June 29, 2014

Sql error message: Msg 7601 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘yourView’ because it is not full-text indexed. I’ll show you how to creating Full Text Catalog and Full Text Search on a database.Full Text Index helps to perform complex queries against character data. These queries can include word or […]

link hover image

SQL: Display from date only time in format HH:MM

Frontpage calendar image December 18, 2013

You have a field as datetime in sql. It is storing the datetime in the format as DD/mm/yyyy HH:MM:SS. now you want to retrieve the time value in the format HH:MM and show it on the design view. SELECT CONVERT(VARCHAR(2),DATEPART(hour, getdate())) +CONVERT(VARCHAR(2), ‘:’) +right(‘0’ + CONVERT(VARCHAR(2),DATEPART(minute, getdate())), 2 )

link hover image

How to UPDATE a statement with JOIN in SQL Server?

Frontpage calendar image November 27, 2013

If you need to update table in SQL Server with data from its ‘parent’ table: table1 (PARENT) ——— id (int) table2Id (int) field (int) table2 ——— id (int) field (int) Script ———————————- update tt set tt.field = t.field from table2 tt inner join table1 t on tt.id = t.table2Id