case an expression of non-boolean type specified in a context where a condition is expected

Question

 Use [DBName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Declare @MainTableNames TABLE(TableName varchar(100))

Declare @TransmissionTableName TABLE (TransTableName varchar(100), RelationColName varchar(100))

Declare @NextTransmissionIDs TABLE(TransmissionID bigint)

Declare @HoldMainName varchar(100)

Declare @SourceServer varchar(max)



Declare @DestinationServer varchar(max)     

Declare @vsql varchar(max)

Declare @flag int

Set @SourceServer='[XYZ].[DBName].[dbo]'
Set @DestinationServer='[ABC].[DBName].[dbo]'




INSERT INTO @MainTableNames(TableName)
Values ('A'),('B'),('C'),('D');

IF OBJECT_ID('tempdb..#NextIDs','local') IS NOT NULL
    DROP TABLE #NextIDs       
CREATE TABLE #NextIDs (
    UniqueID varchar(100))


Declare maintablecursor Cursor for        
SELECT TableName from @MainTableNames

Declare @MainTabName varchar(100)

Declare @TransTabName varchar(100)
Declare @TransRelationCol bigint

if not exists(select 1 from sys.servers where name='ABC')
begin 
EXEC sp_addlinkedserver @server = 'ABC'
print 'Server Added in the list '
end

open maintablecursor 
FETCH NEXT from maintablecursor into @MainTabName

Declare @StartDate datetime      
Declare @EndDate datetime         
SET @StartDate = '2017-07-05 00:00:00.000'         
SET @EndDate =   '2017-07-05 23:59:59.000'           

Select @HoldMainName =  @MainTabName

/*Set @vsql=' SELECT 1 FROM '+@SourceServer+'.'+ @MainTabName+' WHERE [dbo].'+@MainTabName+'.[receiptdt] >='+@StartDate+' and [dbo].'+@MainTabName+'.[receiptdt] <= '+@EndDate+' and transmissiontid is not null'
EXEC(@vsql)*/ **used but getting error near receiptdt and while loop**

--while EXISTS (EXEC(@vsql))
--BEGIN

set @vsql=' INSERT INTO #NextIDS(UniqueID) SELECT TOP 1 ID FROM '+@SourceServer+'.'+@MainTabName + 
' WHERE convert(datetime, [dbo].'+@MainTabName+'.[receiptdt], 102) <= cast('+ @EndDate+' AS DATETIME) and ID is not null;'
EXEC(@vsql)     


Set @vsql =' Insert into '+ @DestinationServer+'.'+@MainTabName+ 
            ' SELECT *  FROM ' +@SourceServer+'.'+@MainTabName+' AS a
        INNER JOIN #NextIDs AS b ON a.transmissiontid = b.UniqueID'     
EXEC(@vsql) 
print @MainTabName + ' at time of insertion'

FETCH NEXT from maintablecursor into @MainTabName

WHILE @@FETCH_STATUS = 0
BEGIN
SEt @vsql=' Insert into '+ @DestinationServer+'.'+@MainTabName+ 
            ' SELECT *  FROM ' +@SourceServer+'.'+@MainTabName+' AS a
        INNER JOIN #NextIDs AS b ON a.transmissiontid = b.UniqueID'     
EXEC(@vsql)     
print @MainTabName + ' at time of insertion'
        
SET @vsql= ' DELETE '+ @SourceServer+'.'+@MainTabName+ 
    ' FROM ' + @SourceServer+'.'+@MainTabName+ ' AS a
    INNER JOIN #NextIDs AS b ON a.transmissiontid = b.UniqueID' 
EXEC(@vsql)
print @MainTabName + ' at time of deletion'

FETCH NEXT from maintablecursor into @MainTabName
END --Cursor END 

CLOSE maintablecursor;

DEALLOCATE maintablecursor;

Set @vsql=  ' DELETE '+ @SourceServer+'.'+@HoldMainName+ 
    ' FROM '+ @SourceServer+'.'+@HoldMainName+ ' AS a
    INNER JOIN #NextIDs AS b ON a.transmissiontid = b.UniqueID'

print(@HoldMainName)
-- END --while loop end

This is the whole code that i m using but i m getting error near the receipt date (receiptdt in source and destination table is in datetime format) type conversion error AND also getting the error near the While loop(while(EXEC(@vsql))) case an expression of non-boolena type

I m defining my start and end date in date time format but still getting the type conversion error

Use prepared statements when you want to run queries with variable input. — Progman, Aug 16 '20 at 10:27
Print your SQL query to see what the error is. You are missing a lot of spaces, your query will result in something like `SELECT 1 FROMserver.tableWHERE ....`. — Progman, Aug 16 '20 at 10:29
Hello, how can i print my sql queries to know the error bcz due to the error the query is not in execution mode — Vaibhav Aggarwal, Aug 16 '20 at 10:53
I don't want to use the prepared statement can u provide me any other alternative — Vaibhav Aggarwal, Aug 16 '20 at 10:58
Run a simple `SELECT` statement, see https://stackoverflow.com/questions/18838611/how-to-display-the-value-of-a-variable-at-the-commandline-in-mysql — Progman, Aug 16 '20 at 11:09
Pls pay attention to the tags! You tagged the question as mysql, but the query is for ms sql server! — Shadow, Aug 16 '20 at 11:52
@Progman after adjusting the spaces still i m getting the same error and the error near receipt date for more description plz go to my own answer in which all the code is given thoroughly — Vaibhav Aggarwal, Aug 17 '20 at 10:42

score 0 · Accepted Answer · answered Aug 16 '20 at 12:00

Please pay careful attention to spacing. Key words must be separated by spaces. The PRINT statement below the variable assignment will allow you to QA the code prior to executing.

Set @vsql='SELECT 1 FROM '+@SouceServer+'.'+ @MainTabName+
          ' WHERE [dbo].'+@MainTabName+'.[receiptdt] >='+@StartDate+
                 ' and [dbo].'+@MainTabName+'.[receiptdt] <= '+@EndDate+
                 ' and transmissiontid is not null'
print (@vsql);

case an expression of non-boolean type specified in a context where a condition is expected

1 Answers1