TSQL Help to run OpenQuery using vairables

Question

Post reply

TSQL Help to run OpenQuery using vairables

Mac1986

SSCertifiable

Points: 6216
More actions
December 30, 2013 at 1:49 am

#301850

I’m trying to get fromLSN from the remote server getting Error
Conversion failed when converting date and/or time from character string.
DECLARE @AcquisitionServerAlias sysname = 'AcquisitionServer3' ,@AcquisitionDBName sysname = 'Mydb',@begintime datetime = '2013-12-29 19:52:27.760'
,@FromSQL nvarchar(300),@FromParam nvarchar(50),@ReturnFromLSNValue binary(10),@fromLSN binary(10)
SET @FromSQL = 'SELECT @FromLSNOUT = fromLSN FROM OPENQUERY ('+@AcquisitionServerAlias+', ''SELECT '+@AcquisitionDBName+'.sys.fn_cdc_map_time_to_lsn('+@begintime+')as fromLSN'')'
SET @FromParam = N'@FromLSNOUT binary(10) OUTPUT'
EXECUTE sp_executesql @FromSQL, @FromParam , @FromLSNOUT = @fromLSN OUTPUT
SET @ReturnFromLSNValue = (SELECT @fromLSN)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DECLARE

@AcquisitionServerAlias sysname = 'AcquisitionServer3' ,

@AcquisitionDBName sysname = 'Mydb',

@begintime datetime = '2013-12-29 19:52:27.760',

@FromSQL nvarchar(300),

@FromParam nvarchar(50),

@ReturnFromLSNValue binary(10),

@fromLSN binary(10)

SET @FromSQL = 'SELECT @FromLSNOUT = fromLSN FROM OPENQUERY ('+@AcquisitionServerAlias+', ''SELECT '+@AcquisitionDBName+'.sys.fn_cdc_map_time_to_lsn('+@begintime+')as fromLSN'')'

PRINT @FromSQL

'Msg 241, Level 16, State 1, Line 10

Conversion failed when converting date and/or time from character string.'

You're attempting to concatenate a string and a datetime (which takes precedence), and the string cannot convert to a date. Either define @begintime as string, or convert @begintime in the string concatenation: +CONVERT(stringdatatype,@begintime,pattern)+.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mac1986 SSCertifiable Points: 6216 More actions · Answer 2

thanks a ton.

That solved my problem

SET @FromSQL = 'SELECT @FromLSNOUT = fromLSN FROM OPENQUERY ('+@AcquisitionServerAlias+',

''SELECT '+@AcquisitionDBName+'.sys.fn_cdc_map_time_to_lsn(''''smallest greater than or equal'''','''''+convert(varchar,@begintime,121)+''''')as fromLSN'')'

SET @FromParam = N'@FromLSNOUT binary(10) OUTPUT'

EXECUTE sp_executesql @FromSQL, @FromParam , @FromLSNOUT = @fromLSN OUTPUT

SET @ReturnFromLSNValue = (SELECT @fromLSN)