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]

change these two lines to:

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

Regards,
AA

 

[mrdenny]

It looks good. Which line it line 19? You only posted 18 lines worth of code. Double click on the error in Query Analyzer and it will highlight the line with the error.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[MastermindSQL]

Change your variable declaration for

@TableName varchar(50)

to following:

DECLARE @TableName SysName

Your code should be like:
===================================================
BEGIN
DECLARE
@LastFullUpdate datetime
,@LastIncrementalUpdate datetime
,@LastUpdatedDt datetime
,@RecordCount bigint
,@sqlRecordcount nvarchar(200)
,@sqlCreatedDt nvarchar(200)
,@TableName sysname /*Changed DataType*/

SET @TableName = 'Carriers'

-- get record count
SET @sqlRecordcount = 'Select @RecordCount = Count(*) FROM ' + @TableName

EXEC sp_ExecuteSql @sqlRecordcount, N'@RecordCount Integer Output', @RecordCount
===================================================

 

[EBee]

STILL getting error with this code

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

 

[amrita418]

We wouldn't know what line 16 is. can you post your complete code and what line you think is giving you an error?

Regards,
AA

 

[EBee]

BEGIN
DECLARE
@LastFullUpdate datetime
,@LastIncrementalUpdate datetime
,@LastUpdatedDt datetime
,@RecordCount bigint
,@sqlRecordcount nvarchar(200)
,@sqlCreatedDt nvarchar(200)
,@TableName sysname /*Changed DataType*/

SET @TableName = 'Carriers'

-- get record count
SET @sqlRecordcount = 'Select @RecordCount = Count(*) FROM ' + @TableName

EXEC sp_ExecuteSql @sqlRecordcount, N'@RecordCount Int Out', @RecordCount -- LINE 16 last line

 

[amrita418]

Can you try running it replacing last two lines with the code that I posted previously?

 

[SQLBill]

Run the script in Query Analyzer, when you get the error double click on the error and it will highlight the line where it believes the error exists. Let us know which line that is.

-SQLBill

Posting advice: FAQ481-4875

 

[amrita418]

Here's the code with the change that I wanted you to make, it works fine for me:

begin
DECLARE 
         @LastFullUpdate datetime
        ,@LastIncrementalUpdate datetime 
        ,@LastUpdatedDt datetime
        ,@RecordCount bigint  
        ,@sqlRecordcount nvarchar(200)
        ,@sqlCreatedDt nvarchar(200)
        ,@TableName sysname   /*Changed DataType*/
  
       SET @TableName = 'TableA'

     -- get record count
       SET @sqlRecordcount = N'Select @RecordCount = Count(*) FROM ' + @TableName
       EXEC sp_ExecuteSql @sqlRecordcount, N'@RecordCount Integer Output', @RecordCount output
       print 'here is the output ' + convert(varchar, @recordcount)
end

Regards,
AA

 

[EBee]

You are the Man. . . THANK you all . .this helps me a lot. I don't have to write a very long code to get my record count to a linked database. .

Thank you much

 

[SQLBill]

I'm not sure you can do this:
N'@RecordCount Int Out'

I believe you have to DECLARE the datatype of the parameter and then use it in the EXEC. I also think OUT needs to be OUTPUT. For example:

DECLARE @RecordCount INT

EXEC sp_ExecuteSQL @sqlRecordcount, @RecordCount Output, @RecordCount

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Ignore my last post, I apparently was typing it while you got your solution.

-SQLBill

Posting advice: FAQ481-4875

 

[EBee]

I need a count on a linked database. . I just noticed that we have been working on assuming that it is native to the current database. However the code below does not work because OPENQUERY does not allow passing variable. . . any thoughts

--It has to be something like this:

SELECT @BeeDirectCountCarriers = Count(*)
FROM OPENQUERY(sbdatabase1,'select * from'+ @TableName)

 

[EBee]

ERROR:

Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '+'.
==========================================================

BEGIN
DECLARE
@RecordCount1 bigint
,@RecordCount2 bigint
,@sqlRecordcount nvarchar(200)
,@sqlCreatedDt nvarchar(200)
,@TableName sysname(50)
,@BeeDirectCount nvarchar(50)
,@CMDCount nvarchar(50)
,@CMDRecordCount nvarchar(50)
,@BeeRecordCount nvarchar(50)

set @TableName = 'Carriers'

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


SELECT @BeeDirectCount = N'Select @RecordCount2 = Count(*)
'FROM OPENQUERY(sbdatabase1,N'select * from'+ @TableName) -- line 22
EXEC sp_ExecuteSql @BeeDirectCount, N'@RecordCount Integer Output', @BeeRecordCount output
end

 

[amrita418]

AFAIK, openquery does not take variables so try using linked server instead.

Regards,
AA

 

[EBee]

this is ugly. . but this is what I have. . working.

Create Procedure UpdateRecordCountOnUpdTables
AS

DECLARE
@BeeDirectCountTransactions int
, @BeeDirectCountRoutes int
, @BeeDirectCountCarriers int
, @BeeDirectCountSubscriptions int
, @BeeDirectCountAddresses int
, @BeeDirectCountComplaint int
, @BeeDirectCountDistributionPointPublications int
, @BeeDirectCountOccupants int
, @BeeDirectCountRouteDraw int
, @BeeDirectCountRouteDrawAdjustments int
, @BeeDirectCountSingleCopyDraw int
, @BeeDirectCountSingleCopyDrawAdjustments int
, @CMDCountTransactions int
, @CMDCountRoutes int
, @CMDCountCarriers int
, @CMDCountSubscriptions int
, @CMDCountAddresses int
, @CMDCountComplaint int
, @CMDCountDistributionPointPublications int
, @CMDCountOccupants int
, @CMDCountRouteDraw int
, @CMDCountRouteDrawAdjustments int
, @CMDCountSingleCopyDraw int
, @CMDCountSingleCopyDrawAdjustments int

--Carriers Table Record Count
SELECT @BeeDirectCountCarriers = Count(*) from sbdatabase1.Beedirect.dbo.Carriers
SELECT @CMDCountCarriers = Count(*) from carriers

UPDATE UpdTables
SET BeeDirectRecordCount = @BeeDirectCountCarriers
WHERE TableName = 'Carriers'
UPDATE UpdTables
SET CMDRecordCount = @CMDCountCarriers
WHERE TableName = 'Carriers'

-- Transactions Table Record Count
SELECT @BeeDirectCountTransactions = Count(*) from sbdatabase1.Beedirect.dbo.Transactions
SELECT @CMDCountTransactions = Count(*) from Transactions

UPDATE UpdTables
SET BeeDirectRecordCount = @BeeDirectCountTransactions
WHERE TableName = 'Transactions'
UPDATE UpdTables
SET CMDRecordCount = @CMDCountTransactions
WHERE TableName = 'Transactions'

-- Routes Table Record Count
SELECT @BeeDirectCountRoutes= Count(*) from sbdatabase1.Beedirect.dbo.Routes
SELECT @CMDCountRoutes = Count(*) from Routes

UPDATE UpdTables
SET BeeDirectRecordCount = @BeeDirectCountRoutes
WHERE TableName = 'Routes'
UPDATE UpdTables
SET CMDRecordCount = @CMDCountRoutes
WHERE TableName = 'Routes'

-- Subscriptions Table Record Count
SELECT @BeeDirectCountSubscriptions= Count(*) from sbdatabase1.Beedirect.dbo.Subscriptions
SELECT @CMDCountSubscriptions = Count(*) from Routes

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountSubscriptions
,CMDRecordCount = @CMDCountsubscriptions
WHERE TableName = 'Subscriptions'

-- Addresses Table Record Count
SELECT @BeeDirectCountAddresses = Count(*) from sbdatabase1.Beedirect.dbo.Addresses
SELECT @CMDCountAddresses = Count(*) from Addresses

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountAddresses
,CMDRecordCount = @CMDCountAddresses
WHERE TableName = 'Addresses'

-- Complaint Table Record Count
--SELECT @BeeDirectCountComplaint = Count(*) from sbdatabase1.Beedirect.dbo.Complaint
SELECT @CMDCountComplaint = Count(*) from Complaint

UPDATE UpdTables
SET
--BeeDirectRecordCount = @BeeDirectCountComplaint
CMDRecordCount = @CMDCountComplaint
WHERE TableName = 'Complaint'

-- DistribPointPublication Table Record Count
--SELECT @BeeDirectCountDistributionPointPublication = Count(*) from sbdatabase1.Beedirect.dbo.DistributionPointPublication
SELECT @CMDCountDistributionPointPublications = Count(*) from DistribPointPublications

UPDATE UpdTables
SET
--BeeDirectRecordCount = @BeeDirectCountDistributionPointPublication
CMDRecordCount = @CMDCountDistributionPointPublications
WHERE TableName = 'DistribPointPublications'

-- Occupants Table Record Count
SELECT @BeeDirectCountOccupants= Count(*) from sbdatabase1.Beedirect.dbccupants
SELECT @CMDCountOccupants = Count(*) from Occupant

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountOccupants
,CMDRecordCount = @CMDCountOccupants
WHERE TableName = 'Occupants'

-- RouteDraw Table Record Count
SELECT @BeeDirectCountRouteDraw = Count(*) from sbdatabase1.Beedirect.dbo.RouteDraw
SELECT @CMDCountRouteDraw = Count(*) from RouteDraw

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountRouteDraw
,CMDRecordCount = @CMDCountRouteDraw
WHERE TableName = 'RouteDraw'

-- RouteDrawAdjustment Table Record Count
SELECT @BeeDirectCountRouteDrawAdjustments = Count(*) from sbdatabase1.Beedirect.dbo.RouteDrawAdjustments
SELECT @CMDCountRouteDrawAdjustments = Count(*) from RouteDrawAdjustments

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountRouteDrawAdjustments
,CMDRecordCount = @CMDCountRouteDrawAdjustments
WHERE TableName = 'RouteDrawAdjustments'

-- SingleCopyDraw Table Record Count
SELECT @BeeDirectCountSingleCopyDraw = Count(*) from sbdatabase1.Beedirect.dbo.SingleCopyDraw
SELECT @CMDCountSingleCopyDraw = Count(*) from SingleCopyDraw

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountSingleCopyDraw
,CMDRecordCount = @CMDCountSingleCopyDraw
WHERE TableName = 'SingleCopyDraw'

-- SingleCopyDrawAdjustments Table Record Count
SELECT @BeeDirectCountSingleCopyDrawAdjustments = Count(*) from sbdatabase1.Beedirect.dbo.SingleCopyDrawAdjustments
SELECT @CMDCountSingleCopyDrawAdjustments = Count(*) from SingleCopyDrawAdjustments

UPDATE UpdTables
SET
BeeDirectRecordCount = @BeeDirectCountSingleCopyDrawAdjustments
,CMDRecordCount = @CMDCountSingleCopyDrawAdjustments
WHERE TableName = 'SingleCopyDrawAdjustments'
GO

 

[amrita418]

what does this give you?

set @TableName = 'Carriers'
SET @CMDcount = N'Select @RecordCount1 = Count(*) FROM sbdatabase1.Beedirect.dbo.' + @TableName
   EXEC sp_ExecuteSql @CMDcount, N'@RecordCount Integer Output', @CMDRecordCount

Regards,
AA

 

[amrita418]

What I am trying to say is, you should not hard code all the entries like that. What happens if 20 more tables are added? Do you keep adding to this script.

Ideally you should read one table name at a time from information_schema.tables using a cursor. Get the values for the local and remote server and update UpdTables with the respective values, read the next table and so on.

I think you need to replace @RecordCount1 to @RecordCount in my previous post.

Regards,
AA

 

[EBee]

The problem I have is that, the variable @TableName is not allowed in a linked server. the localserver database works just fine, Howerver, The link sever will not accept a variable. I has to be a straight SQL string.