syntac error Help

[EBee]

ERROR:

Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near '@TableName'.

--=======================================================
BEGIN
DECLARE @LastFullUpdate datetime
,@LastIncrementalUpdate datetime
,@LastUpdatedDt datetime
,@RecordCount bigint
,@sqlRecordcount nvarchar(200)
,@sqlCreatedDt nvarchar(200)
,@TableName varchar(50)

set @TableName = 'Carriers'

-- get record count
SET @sqlRecordcount = 'Select @RecordCount = Count(*) FROM ' + @TableName
EXEC sp_ExecuteSql @sqlRecordcount, N'@RecordCount Integer Output', @RecordCount

UPDATE UpdTables
SET CMDRecordCount = @RecordCount
WHERE TableName = @TableName

 

[amrita418]

Actually, you can use variables with linked servers. Its just that you need to make subtle changes to your code.

Try this and let us know your findings:

DECLARE 
@TableName varchar(30)
SET @TableName = 'Carriers'

SET @CMDcount = N'Select @RecordCount = Count(*) FROM Beedirect.dbo.' + @TableName

EXEC sbdatabase1.master.dbo.sp_executesql @CMDcount, N'@RecordCount Integer Output', @CMDRecordCount

Regards,
AA

 

[amrita418]

Also worth trying:

DECLARE
@query varchar(8000),
@TableName varchar(30)
set @TableName = 'Carriers'

      SELECT  @query = 'SELECT * FROM OPENQUERY(sbdatabase1,''SELECT * FROM Beedirect.dbo.''''' + @TableName + ''''''')'
      EXEC (@query)

Regards,
AA

 

[EBee]

I got this error:

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Carriers'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

--==========================================================

DECLARE
@query varchar(8000),
@TableName varchar(30)
set @TableName = 'Carriers'

SELECT @query = 'SELECT count(*) FROM OPENQUERY(sbdatabase1,''SELECT * FROM Beedirect.dbo.''''' + @TableName + ''''''')'
EXEC (@query)

 

[amrita418]

What happenned when you run the first stmt (using sp_executesql)? Maybe you should add the quotes around the variable as in example 2 that I posted.

Here is the link for using variables with openquery/linked servers.

http://sqljunkies.com/How To/BA89A311-B3AE-4803-BABA-64FCF1E1F8F7.scuk

I have not tried it myself yet, will update you once I try them but you can play around with the code in that link to find a solution to your problem.

Regards,
AA

 

[amrita418]

EBee,
As promised here is the dynamic sql stmt on linked servers that works.

DECLARE 
@TableName varchar(30),
@CMDcount nvarchar(500),
@CMDRecordCount int
SET @TableName = 'Fee'

SET @CMDcount = N'Select @RecordCount = Count(*) FROM prod_data.dbo.' + @TableName

EXEC newlnkserver.master.dbo.sp_executesql @CMDcount, N'@RecordCount Int Output', @CMDRecordCount output
Print 'Here is the count: ' + convert(varchar, @CMDRecordCount)

And the output I got:
Here is the count: 20011532

This way you do not need to hard code the table names and update stmts.

Regards,
AA

 

[EBee]

it works now. . great !!! thank you very much