# Failing to create a reflection in SQL server

**URL:** <https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419>\
**Category:** Uncategorized\
**Created:** [November 4, 2017, 1:13am UTC](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419 "2017-11-04T01:13:55Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![nakdouche](https://avatars.discourse-cdn.com/v4/letter/n/df705f/32.png) [@nakdouche](https://community.dremio.com/u/nakdouche)\
**Post date:** [November 4, 2017, 1:13am UTC](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419/1 "2017-11-04T01:13:56Z")

</div>

Hello,

I looked a bit in the forum but I could not see anything that will help me with this. The query that is being submitted to SQL server does not have a fully formed boolean condition in where clause.

The query is generate fine with Dremio and works fine when interacting with it via a client. But when I try to create a reflection, I get an error:

DATA\_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

sql SELECT \*  
FROM [AdventureWorks2012\_Data].[Sales].[SalesOrderDetail]  
INNER JOIN [AdventureWorks2012\_Data].[Sales].[SalesOrderHeader] ON [SalesOrderDetail].[SalesOrderID] = [SalesOrderHeader].[SalesOrderID]  
WHERE [SalesOrderHeader].[OnlineOrderFlag]  
plugin SQL Server 2012  
SqlOperatorImpl JDBC\_SUB\_SCAN  
Location 14:0:3  
SqlOperatorImpl JDBC\_SUB\_SCAN  
Location 14:0:3  
Fragment 14:0

[Error Id: c9eba148-b904-46bb-8571-2fe08191f2e6 on CFS-NA-03.cfscg.local:31010]

(com.microsoft.sqlserver.jdbc.SQLServerException) An expression of non-boolean type specified in a context where a condition is expected, near ‘OnlineOrderFlag’.  
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError():217  
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult():1635  
com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement():865  
com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute():762  
com.microsoft.sqlserver.jdbc.TDSCommand.execute():6276  
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand():1793  
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand():184  
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement():159  
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery():661  
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():165  
com.dremio.exec.store.CoercionReader.setup():109  
com.dremio.sabot.op.scan.ScanOperator$1.run():178  
com.dremio.sabot.op.scan.ScanOperator$1.run():174  
java.security.AccessController.doPrivileged():-2  
javax.security.auth.Subject.doAs():-1  
org.apache.hadoop.security.UserGroupInformation.doAs():1807  
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():174  
com.dremio.sabot.op.scan.ScanOperator.setupReader():166  
com.dremio.sabot.op.scan.ScanOperator.setup():147  
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():552  
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79  
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63  
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():522  
com.dremio.sabot.driver.StraightPipe.setup():102  
com.dremio.sabot.driver.StraightPipe.setup():102  
com.dremio.sabot.driver.StraightPipe.setup():102  
com.dremio.sabot.driver.Pipeline.setup():58  
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():331  
com.dremio.sabot.exec.fragment.FragmentExecutor.run():227  
com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83  
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577  
com.dremio.sabot.task.AsyncTaskWrapper.run():92  
com.dremio.sabot.task.slicing.SlicingThread.run():71

I should have a query that looks like some thing similar to this:

SELECT [SalesOrderHeader].[OnlineOrderFlag],\*  
FROM [AdventureWorks2012\_Data].[Sales].[SalesOrderDetail]  
INNER JOIN [AdventureWorks2012\_Data].[Sales].[SalesOrderHeader] ON [SalesOrderDetail].[SalesOrderID] = [SalesOrderHeader].[SalesOrderID]  
WHERE [SalesOrderHeader].[OnlineOrderFlag] = ‘true’

This runs fine in sql server. But I am not sure how dremio reflection get this query generated.

How does the reflection miss the where clause?

Thank you.  
Nabil

---

<div class="post-metadata">

**Author:** ![laurent](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/laurent/32/16_2.png) [@laurent](https://community.dremio.com/u/laurent)\
**Post date:** [November 6, 2017, 4:46pm UTC](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419/2 "2017-11-06T16:46:09Z")

</div>

Hi @nakdouche,

Thanks for reporting, it looks like a bug on our side when generating the query. We will look into it!  
Can you provide your table schema?

---

<div class="post-metadata">

**Author:** ![nakdouche](https://avatars.discourse-cdn.com/v4/letter/n/df705f/32.png) [@nakdouche](https://community.dremio.com/u/nakdouche)\
**Post date:** [November 6, 2017, 6:23pm UTC](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419/3 "2017-11-06T18:23:30Z")

</div>

Thank you Laurent.

Here are the schemas of the two tables involved in the query I reported above:

**Object: Table [Sales].[SalesOrderDetail] Script Date: 11/6/2017 12:17:43 PM**  
SET ANSI\_NULLS ON  
GO

SET QUOTED\_IDENTIFIER ON  
GO

CREATE TABLE [Sales].[SalesOrderDetail](  
[SalesOrderID] [int] NOT NULL,  
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,  
[CarrierTrackingNumber] nvarchar NULL,  
[OrderQty] [smallint] NOT NULL,  
[ProductID] [int] NOT NULL,  
[SpecialOfferID] [int] NOT NULL,  
[UnitPrice] [money] NOT NULL,  
[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF\_SalesOrderDetail\_UnitPriceDiscount] DEFAULT ((0.0)),  
[LineTotal] AS (isnull(([UnitPrice]_((1.0)-[UnitPriceDiscount]))_[OrderQty],(0.0))),  
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF\_SalesOrderDetail\_rowguid] DEFAULT (newid()),  
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF\_SalesOrderDetail\_ModifiedDate] DEFAULT (getdate()),  
CONSTRAINT [PK\_SalesOrderDetail\_SalesOrderID\_SalesOrderDetailID] PRIMARY KEY CLUSTERED  
(  
[SalesOrderID] ASC,  
[SalesOrderDetailID] ASC  
)WITH (PAD\_INDEX = OFF, STATISTICS\_NORECOMPUTE = OFF, IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS = ON, ALLOW\_PAGE\_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]

GO

ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderDetail\_SalesOrderHeader\_SalesOrderID] FOREIGN KEY([SalesOrderID])  
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])  
ON DELETE CASCADE  
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK\_SalesOrderDetail\_SalesOrderHeader\_SalesOrderID]  
GO

ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderDetail\_SpecialOfferProduct\_SpecialOfferIDProductID] FOREIGN KEY([SpecialOfferID], [ProductID])  
REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])  
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK\_SalesOrderDetail\_SpecialOfferProduct\_SpecialOfferIDProductID]  
GO

ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderDetail\_OrderQty] CHECK (([OrderQty]\>(0)))  
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK\_SalesOrderDetail\_OrderQty]  
GO

ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderDetail\_UnitPrice] CHECK (([UnitPrice]\>=(0.00)))  
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK\_SalesOrderDetail\_UnitPrice]  
GO

ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderDetail\_UnitPriceDiscount] CHECK (([UnitPriceDiscount]\>=(0.00)))  
GO

ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [CK\_SalesOrderDetail\_UnitPriceDiscount]  
GO

**Object: Table [Sales].[SalesOrderHeader] Script Date: 11/6/2017 12:19:56 PM**  
SET ANSI\_NULLS ON  
GO

SET QUOTED\_IDENTIFIER ON  
GO

SET ANSI\_PADDING ON  
GO

CREATE TABLE [Sales].[SalesOrderHeader](  
[SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,  
[RevisionNumber] [tinyint] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_RevisionNumber] DEFAULT ((0)),  
[OrderDate] [datetime] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_OrderDate] DEFAULT (getdate()),  
[DueDate] [datetime] NOT NULL,  
[ShipDate] [datetime] NULL,  
[Status] [tinyint] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_Status] DEFAULT ((1)),  
[OnlineOrderFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_OnlineOrderFlag] DEFAULT ((1)),  
[SalesOrderNumber] AS (isnull(N’SO’+CONVERT(nvarchar,[SalesOrderID]),N’\*\*\* ERROR \*\*\*’)),  
[PurchaseOrderNumber] [dbo].[OrderNumber] NULL,  
[AccountNumber] [dbo].[AccountNumber] NULL,  
[CustomerID] [int] NOT NULL,  
[SalesPersonID] [int] NULL,  
[TerritoryID] [int] NULL,  
[BillToAddressID] [int] NOT NULL,  
[ShipToAddressID] [int] NOT NULL,  
[ShipMethodID] [int] NOT NULL,  
[CreditCardID] [int] NULL,  
[CreditCardApprovalCode] varchar NULL,  
[CurrencyRateID] [int] NULL,  
[SubTotal] [money] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_SubTotal] DEFAULT ((0.00)),  
[TaxAmt] [money] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_TaxAmt] DEFAULT ((0.00)),  
[Freight] [money] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_Freight] DEFAULT ((0.00)),  
[TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),  
[Comment] nvarchar NULL,  
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_rowguid] DEFAULT (newid()),  
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF\_SalesOrderHeader\_ModifiedDate] DEFAULT (getdate()),  
CONSTRAINT [PK\_SalesOrderHeader\_SalesOrderID] PRIMARY KEY CLUSTERED  
(  
[SalesOrderID] ASC  
)WITH (PAD\_INDEX = OFF, STATISTICS\_NORECOMPUTE = OFF, IGNORE\_DUP\_KEY = OFF, ALLOW\_ROW\_LOCKS = ON, ALLOW\_PAGE\_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]

GO

SET ANSI\_PADDING ON  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_Address\_BillToAddressID] FOREIGN KEY([BillToAddressID])  
REFERENCES [Person].[Address] ([AddressID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_Address\_BillToAddressID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_Address\_ShipToAddressID] FOREIGN KEY([ShipToAddressID])  
REFERENCES [Person].[Address] ([AddressID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_Address\_ShipToAddressID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_CreditCard\_CreditCardID] FOREIGN KEY([CreditCardID])  
REFERENCES [Sales].[CreditCard] ([CreditCardID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_CreditCard\_CreditCardID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_CurrencyRate\_CurrencyRateID] FOREIGN KEY([CurrencyRateID])  
REFERENCES [Sales].[CurrencyRate] ([CurrencyRateID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_CurrencyRate\_CurrencyRateID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_Customer\_CustomerID] FOREIGN KEY([CustomerID])  
REFERENCES [Sales].[Customer] ([CustomerID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_Customer\_CustomerID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_SalesPerson\_SalesPersonID] FOREIGN KEY([SalesPersonID])  
REFERENCES [Sales].[SalesPerson] ([BusinessEntityID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_SalesPerson\_SalesPersonID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_SalesTerritory\_TerritoryID] FOREIGN KEY([TerritoryID])  
REFERENCES [Sales].[SalesTerritory] ([TerritoryID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_SalesTerritory\_TerritoryID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [FK\_SalesOrderHeader\_ShipMethod\_ShipMethodID] FOREIGN KEY([ShipMethodID])  
REFERENCES [Purchasing].[ShipMethod] ([ShipMethodID])  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK\_SalesOrderHeader\_ShipMethod\_ShipMethodID]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderHeader\_DueDate] CHECK (([DueDate]\>=[OrderDate]))  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK\_SalesOrderHeader\_DueDate]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderHeader\_Freight] CHECK (([Freight]\>=(0.00)))  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK\_SalesOrderHeader\_Freight]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderHeader\_ShipDate] CHECK (([ShipDate]\>=[OrderDate] OR [ShipDate] IS NULL))  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK\_SalesOrderHeader\_ShipDate]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderHeader\_Status] CHECK (([Status]\>=(0) AND [Status]\<=(8)))  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK\_SalesOrderHeader\_Status]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderHeader\_SubTotal] CHECK (([SubTotal]\>=(0.00)))  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK\_SalesOrderHeader\_SubTotal]  
GO

ALTER TABLE [Sales].[SalesOrderHeader] WITH CHECK ADD CONSTRAINT [CK\_SalesOrderHeader\_TaxAmt] CHECK (([TaxAmt]\>=(0.00)))  
GO

ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK\_SalesOrderHeader\_TaxAmt]  
GO

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [December 5, 2017, 6:52pm UTC](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419/4 "2017-12-05T18:52:48Z")

</div>

Hi @nakdouche

Sorry for the delay in response, are you still having this issue? If yes, let me try this internally and get back to you

Thanks,  
@balaji.ramaswamy

---

<div class="post-metadata">

**Author:** ![nakdouche](https://avatars.discourse-cdn.com/v4/letter/n/df705f/32.png) [@nakdouche](https://community.dremio.com/u/nakdouche)\
**Post date:** [December 10, 2017, 5:24pm UTC](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419/5 "2017-12-10T17:24:19Z")

</div>

Hi Balaji

in effort to make progress with the task I am assigned with, I went ahead and started a new query. Unfortunately I bumped into the issue that I reported here which you are helping me with as well.  
([Reflection always says 'in progress'](https://community.dremio.com/t/reflection-always-says-in-progress/461/11))

This issue ([Failing to create a reflection in SQL server](https://community.dremio.com/t/failing-to-create-a-reflection-in-sql-server/419/3)) may still be there as I have not made any more effort to resolve it but I am no longer pursuing it. So we could drop this one.

Thank  
Nabil