Dynamic SQL error!! (Network Steve Forum)

Dynamic SQL error!!

Why am I getting thins error?

Msg 102, Level 15, State 1, Line 76
Incorrect syntax near '@RemoteServer'.

SET @strSql = 'SELECT DISTINCT TOP ' + @numRecordsToProcess + ' ' + '
i.[MasterKey],
i.[OriginalKey],
i.[ImageName],
i.[ImageBatchType],
i.[NoOfPages],
i.[ImageSize],
i.[Station],
i.[UserId],
i.[ServerPath],
i.[Path],
i.[Status],
i.[VirtualPath],
i.[LocalImageRootPath],
i.[GroupKey],
dt.[TypeKey] AS [DocumentType]
FROM [ImageMaster] i(NOLOCK)
INNER JOIN [ImagePage] ip(NOLOCK) ON i.[MasterKey] = ip.[MasterDocument]
INNER JOIN DocumentTypes dt(NOLOCK) ON ip.PageType = dt.TypeKey
WHERE i.[CreateDateTime] >= DATEADD(Year,-2,GETDATE())
AND NOT EXISTS
(SELECT [MasterKey]
FROM ['@RemoteServer'].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK)
WHERE i.[MasterKey] = lg.[MasterKey] AND lg.[Processed] = ''Y'')'

September 4th, 2015 2:16pm

Next to last line should be

FROM [' + @RemoteServer + '].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK)

Tom

Proposed as answer by Naomi NModerator 12 hours 37 minutes ago

Free Windows Admin Tool Kit Click here and download it now

September 4th, 2015 2:19pm

Try the below: You need to provide + @Variablename +

SET @strSql =   'SELECT DISTINCT TOP ' + @numRecordsToProcess + ' ' + '
i.[MasterKey], 
i.[OriginalKey], 
i.[ImageName], 
i.[ImageBatchType], 
i.[NoOfPages],  
i.[ImageSize], 
i.[Station], 
i.[UserId], 
i.[ServerPath], 
i.[Path], 
i.[Status], 
i.[VirtualPath], 
i.[LocalImageRootPath], 
i.[GroupKey], 
dt.[TypeKey] AS [DocumentType]
                        FROM [ImageMaster] i(NOLOCK) 
                            INNER JOIN [ImagePage] ip(NOLOCK) ON i.[MasterKey] = ip.[MasterDocument] 
                            INNER JOIN DocumentTypes dt(NOLOCK) ON ip.PageType = dt.TypeKey 
                        WHERE i.[CreateDateTime] >= DATEADD(Year,-2,GETDATE()) 
                            AND NOT EXISTS 
(SELECT [MasterKey] 
                                FROM ['+@RemoteServer+'].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK)
                                WHERE i.[MasterKey] = lg.[MasterKey] AND lg.[Processed] = ''Y'')'

September 4th, 2015 2:20pm

Try the below: You need to provide + @Variablename +

SET @strSql =   'SELECT DISTINCT TOP ' + @numRecordsToProcess + ' ' + '
i.[MasterKey], 
i.[OriginalKey], 
i.[ImageName], 
i.[ImageBatchType], 
i.[NoOfPages],  
i.[ImageSize], 
i.[Station], 
i.[UserId], 
i.[ServerPath], 
i.[Path], 
i.[Status], 
i.[VirtualPath], 
i.[LocalImageRootPath], 
i.[GroupKey], 
dt.[TypeKey] AS [DocumentType]
                        FROM [ImageMaster] i(NOLOCK) 
                            INNER JOIN [ImagePage] ip(NOLOCK) ON i.[MasterKey] = ip.[MasterDocument] 
                            INNER JOIN DocumentTypes dt(NOLOCK) ON ip.PageType = dt.TypeKey 
                        WHERE i.[CreateDateTime] >= DATEADD(Year,-2,GETDATE()) 
                            AND NOT EXISTS 
(SELECT [MasterKey] 
                                FROM ['+@RemoteServer+'].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK)
                                WHERE i.[MasterKey] = lg.[MasterKey] AND lg.[Processed] = ''Y'')'

Free Windows Admin Tool Kit Click here and download it now

September 4th, 2015 2:24pm

This worked, but now I am getting this error:

Msg 245, Level 16, State 1, Line 54
Conversion failed when converting the varchar value 'SELECT DISTINCT ' to data type int.

September 4th, 2015 2:26pm

@numRecordsToProcess INT,
----
SET @strSql = N'SELECT DISTINCT TOP ' + @numRecordsToProcess + ' ' + '
....

You must convert @numRecordsToProcess as int value first to varchar

SET @strSql = N'SELECT DISTINCT TOP ' + convert(varchar(9), @numRecordsToProcess) + ' ' + '

Free Windows Admin Tool Kit Click here and download it now

September 4th, 2015 2:34pm

Still you forgot to add +....

+@RemoteServer+

Try the below:

DECLARE @numRecordsToProcess INT, @RemoteServer VARCHAR(50) --AS --BEGIN SET NOCOUNT ON; DECLARE @StrSql VARCHAR(6000) SET @numRecordsToProcess = 0 IF @numRecordsToProcess != 0 BEGIN SET @strSql = N'SELECT DISTINCT TOP ' + convert(varchar(9), @numRecordsToProcess) + ' ' + ' i.[MasterKey], i.[OriginalKey], i.[ImageName], i.[ImageBatchType], i.[NoOfPages], i.[ImageSize], i.[Station], i.[UserId], i.[ServerPath], i.[Path], i.[Status], i.[VirtualPath], i.[LocalImageRootPath], i.[GroupKey], dt.[TypeKey] AS [DocumentType] FROM [ImageMaster] i(NOLOCK) INNER JOIN [ImagePage] ip(NOLOCK) ON i.[MasterKey] = ip.[MasterDocument] INNER JOIN DocumentTypes dt(NOLOCK) ON ip.PageType = dt.TypeKey WHERE i.[CreateDateTime] >= DATEADD(Year,-2,GETDATE()) AND NOT EXISTS (SELECT [MasterKey] FROM ['+@RemoteServer+'].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK) WHERE i.[MasterKey] = lg.[MasterKey] AND lg.[Processed] = ''Y'')' END ELSE BEGIN SET @strSql = 'SELECT DISTINCT ' + convert(varchar(9), @numRecordsToProcess) + ' ' + ' i.[MasterKey], i.[OriginalKey], i.[ImageName], i.[ImageBatchType], i.[NoOfPages], i.[ImageSize], i.[Station], i.[UserId], i.[ServerPath], i.[Path], i.[Status], i.[VirtualPath], i.[LocalImageRootPath], i.[GroupKey], dt.[TypeKey] AS [DocumentType] FROM [ImageMaster] i(NOLOCK) INNER JOIN [ImagePage] ip(NOLOCK) ON i.[MasterKey] = ip.[MasterDocument] INNER JOIN DocumentTypes dt(NOLOCK) ON ip.PageType = dt.TypeKey WHERE i.[CreateDateTime] >= DATEADD(Year,-2,GETDATE()) AND NOT EXISTS (SELECT [MasterKey] FROM ['+@RemoteServer+'].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK) WHERE i.[MasterKey] = lg.[MasterKey] AND lg.[Processed] = ''Y'')' END PRINT @StrSql

September 4th, 2015 2:46pm

In the else part we need to remove @NumRecordsToProcess otherwise it will be invalid query.

Free Windows Admin Tool Kit Click here and download it now

September 4th, 2015 3:25pm

Next to last line should be

FROM [' + @RemoteServer + '].[Interact].[dbo].[tblImageTransfer3xLogging] lg (NOLOCK)

Tom

Proposed as answer by Naomi NModerator Friday, September 04, 2015 6:32 PM

September 4th, 2015 6:15pm

This topic is archived. No further replies will be accepted.
Other recent topics