Set it up in development.

Make the changes.

Try your best to take advantage of the new rights.

You'll need to provide a bit more info.

Script it all out for us (Check, create, insert, query clean up) and show examples of the output you're wanting.

Check out the link...

Thanks Erland!!

sp_ServerOption is exactly what I need.

EXEC sp_serveroption '<LinkedServer>', 'remote proc transaction promotion', 'false'

Double post after error.

Koen's query is much easier, but here's my alternate query anyway.

WITH cteRoles

AS

(

SELECTROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,

Name,

Role

FROMSample

)

SELECTName

FROMcteRoles

WHERERowNr = 2

Here's another method.

DECLARE @StartVarchar(5) = '07:00',

@EndVarchar(5) = '05:59'

SELECTCASE

WHEN REPLACE(@Start, ':', '') < REPLACE(@End, ':','')

THEN DATEDIFF(HH, CAST('1900-01-01 '+ @Start AS SmallDateTime), CAST('1900-01-01 ' + @End AS SmallDateTime)) + 1...

Actually after rereading my code, it does NOT examine TEXT data and give a more appropriate data type.

Only suggest similar data types for text. I.e. Text to ==>> char, nchar,...

Erland's code works fine for me as well.

SQL 2012 Express Edition.

I do have an extremely heavy work in progress that may be of some help.

Haven't looked at it in a while as it was giving me headaches.

The idea is to...

Thanks Luis!!

Thanks for your comments guys.

Unfortunately PiMané's suggestion doesn't work as it is still getting parse and failing due to the unknown column name.

IF @@VERSION LIKE '%2005%'

BEGIN

EXEC('SELECTCASE

WHEN RIGHT(LEFT(@@VERSION, 25), 4)...

Can handle the first 2 IDs but the last one is tricky.

I don't know where to split the flight(s) there from the flight(s) back.

Anyway, this is the function I've come...

Only got ID 5 working so far. This is just a pointer in the right a direction whilst I have a crack at the other ones.

DECLARE@StringVarchar(50) = 'MEL/SYD*SYD/MEL',

@TempVarchar(50),

@CurPortVarchar(3),

@ResultVarchar(50) = ''

SET...

Are you able to put a try catch block around your SP and use SCOPE_IDENTITY()

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k%28SCOPE_IDENTITY_TSQL%29;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28DevLang-TSQL%29&rd=true

An XML data type

http://msdn.microsoft.com/en-us/library/ms187339%28v=sql.120%29.aspx