Multi Database Join
I'm attempting to create a multi database join in a document between a Text file (contains only one record) and a SQL table (contains over 1 million records). Once the join is created, if I check out the browse section I see ALL the records in the SQL table instead of only seeing the one record that should be joined based upon the multi database join setup.
I've contacted Tech support and they agree with me that I should only be seeing one record instead of all of them, it's been over a week and I have not heard back from them .... I am curious if anyone else has had this issue?
I'm using BT 10.0 SR4
I have also tried this with an Excel file and I get the same results.
-
Assuming that all is configured correctly I've not come across this issue before. Do you have a CAS ticket number from your tech-support request?
0 -
I do not have a CAS ticket number yet, but if I get one I'll add it here.
0 -
Which browse tab are you looking at? Make sure you're selecting 'All Databases' on the left, then 'browse' on the right inside of Database Connection setup to see the view of just the results of the join. If you've got one of the databases themselves selected than browse shows the data prior to the join.
Also, if you go to file>print than hit 'select records', what do you see there? All the data or just the record that you're expecting?
0 -
Browse from the All Databases.
When I attempt to print and use the 'select records' option, I see the same date as the browse when I am on the All Database in the database setup.
0 -
Can you attach a BarTender document and the text file for us to examine? At the least we'll be able to see what you've setup to see if you've made any obvious error.
0 -
this is kinda what I sent Tech support.
I wasn't able to attach the excel file so I saved it as a tab delimited text file. The BTW file is just an example because the real file is connected to a SQL database. This file is just using a text and Excel file, but I get the same results.
I thought I should mention that I do have a label in production where the multidatabase join is working properly, but that one starts with a record in SQL and goes to an Excel file to get info. It seems when I try to go the other way it isn't working.
0 -
I read the tab delimited file into Excel and saved it back to an xlsx file. The BarTender document was a bit messed up so I started again from scratch. I found i could join the two data files together, however, the "RECID" field in the Excel file was being seen as the DOUBLE data type and thus wasn't giving a valid join on the string field "LOSRecID" of the text file. I forced the data type in the Excel file by changing the first record to have a couple of alpha characters in it thus giving a WCHAR data type. With this done the join worked fine with tables joined in either order. Maybe you're getting this same problem on the SQL Server database??
0 -
I suppose that is very possible. However, BarTender specifies the SQL table RECID field as a BIGINT datatype (and this is not something that I or anyone in the organization has the power to change). I am only able to change the Text file LOSRecID field to Character, Int, or Float (through BarTender) ... non of which seem to work.
Do you have any suggestions on how to work around this?
The whole point of having the RECID on the text file is an entry point to our ERP system to get any piece of data I may need in the future. If I have to hard code data because the Multi join wont join a text file and a SQL database because of field types, I loose some of the benefit of having an outside label software and have to wait on development to get the text file updated with new information.
0 -
What data type is RECINT in the SQL Server database itself? Could we perhaps get a very simple version of your SQL database (where the issue reproduces) for us to examine?
0 -
The RECID field is a BIGINT data type according to bartender
The support ticket number is CAS-247307-4VTZ7Y
Here is the SQL data in a zip file from Dropbox
https://dl.dropboxusercontent.com/u/12534491/BarTender/EBCLEANOrderbookedSchedule_Script.zip
Good luck
0 -
Can you give me instructions on how to import this data into SQL Server using Management Studio. This is a "Microsoft SQL Server Query File", rather than a backup of the database, so I'm not sure how to proceed...
0 -
Me either ... I'll have to ask the database guy.
0 -
My database guy said you can use this script to create the table needed and use the excel file further up in the thread as the data to import. You really don't need to do any of this in my opinion. The test is simple ... create a SQL table with a BIGINT datatype and try to use a text file as the second data source and you will see that BarTender can't handle the join.
USE [DatabaseName]
GO
/****** Object: Table
[dbo].[EBCLEANORDERBOOKEDSCHEDULE] Script Date: 2/14/2014
8:44:36 AM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE](
[OUTQTY] [numeric](28, 12) NOT NULL,
[PRODQTY] [numeric](28, 12) NOT NULL,
[ORDERQTY] [numeric](28, 12) NOT NULL,
[ITEMID] [nvarchar](30) NOT NULL,
[LINENUM] [numeric](28, 12) NOT NULL,
[SALESID] [nvarchar](30) NOT NULL,
[PRODSTARTDATE] [datetime]
NOT NULL,[DELIVERYDATE] [datetime]
NOT NULL,[LEANORDERSCHEDULE] [nvarchar](10) NOT NULL,
[STATUS] [int] NOT NULL,
[ACTUALSTARTDATE] [datetime]
NOT NULL,[PARENTITEMID] [nvarchar](30) NOT NULL,
[OFFSET] [int] NOT NULL,
[ASSEMBLYSCHEDULE] [int]
NOT NULL,[STATIONID] [nvarchar](10) NOT NULL,
[PTO] [int] NOT NULL,
[LEANSCHEDULESEQ] [nvarchar](3) NOT NULL,
[BOMID] [nvarchar](30) NOT NULL,
[FINISHEDDATE] [datetime]
NOT NULL,[ESTIMATEDRELEASE] [datetime]
NOT NULL,[ACTIONDATE] [datetime]
NOT NULL,[RELEASEDATE] [datetime]
NOT NULL,[BLOCKED] [int] NOT NULL,
[LENGTH] [numeric](28, 12) NOT NULL,
[NBRTOSUPPLY] [numeric](28, 12) NOT NULL,
[TOTLENGTH] [numeric](28, 12) NOT NULL,
[SPLIT] [int] NOT NULL,
[SORTTYPE] [nvarchar](10) NOT NULL,
[PULLWAREHOUSE] [nvarchar](10) NOT NULL,
[STRRECID] [nvarchar](10) NOT NULL,
[PRODID] [nvarchar](30) NOT NULL,
[EBCSORTTYPE] [nvarchar](10) NOT NULL,
[SIGNOFF] [int] NOT NULL,
[WIC_DELIVERYDATE] [datetime]
NOT NULL,[FIRSTOPCOMPLETED] [int]
NOT NULL,[BATCHCELL] [nvarchar](8) NOT NULL,
[FIRSTOPCOMPLETEDDATE] [datetime]
NOT NULL,[DATAAREAID] [nvarchar](3) NOT NULL,
[RECVERSION] [int] NOT NULL,
[RECID] [bigint] NOT NULL,
[WIC_ORIGRESCHDELVDATE] [datetime]
NOT NULL,[WIC_IBS_REPORTPROBLEM] [int]
NOT NULL,[WIC_EXPEDITED] [int]
NOT NULL,[WIC_KEYACCOUNTCUSTOMER] [int]
NOT NULL,CONSTRAINT [I_20069RECID] PRIMARY KEY NONCLUSTERED
(
[DATAAREAID] ASC,
[RECID] 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 [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [OUTQTY]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [PRODQTY]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [ORDERQTY]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [ITEMID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [LINENUM]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [SALESID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [PRODSTARTDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [DELIVERYDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [LEANORDERSCHEDULE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [STATUS]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [ACTUALSTARTDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [PARENTITEMID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [OFFSET]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [ASSEMBLYSCHEDULE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [STATIONID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [PTO]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [LEANSCHEDULESEQ]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [BOMID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [FINISHEDDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [ESTIMATEDRELEASE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [ACTIONDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [RELEASEDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [BLOCKED]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [LENGTH]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [NBRTOSUPPLY]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [TOTLENGTH]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [SPLIT]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [SORTTYPE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [PULLWAREHOUSE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [STRRECID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [PRODID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [EBCSORTTYPE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [SIGNOFF]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [WIC_DELIVERYDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [FIRSTOPCOMPLETED]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('') FOR [BATCHCELL]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [FIRSTOPCOMPLETEDDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('dat') FOR [DATAAREAID]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((1)) FOR [RECVERSION]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ('1900-01-01 00:00:00.000')
FOR [WIC_ORIGRESCHDELVDATE]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [WIC_IBS_REPORTPROBLEM]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [WIC_EXPEDITED]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] ADD
DEFAULT ((0)) FOR [WIC_KEYACCOUNTCUSTOMER]GO
ALTER TABLE [dbo].[EBCLEANORDERBOOKEDSCHEDULE] WITH CHECK ADD CHECK (([RECID]<>(0)))
GO
0 -
I was able to reproduce a similar behaviour as explain by Ian C, but this time with the SQL Server database. An "Invalid Join" message will appear, this making the join invalid. I fixed it by changing the "Data Type" for the Text File database connection field to be "Integer". This way, it will match with the BIGINT datatype from the SQL Server field.
See attached the picture which shows how to do this. Does this help?
0 -
I can verify the same behavior. When you originally set it up you get a join error until you change the data type for the text file to Integer.
After you did that did you go back to All Databases and use the browse Tab? When I do this, I see ALL the records in the SQL table instead of just the one that should be joined from the Text File.
0 -
When I did that, it just gave me 1 record as the result. Have in mind however that for my tests I've been using a pretty simple SQL Server database with just 3 records...
I'm sorry, but I'm not able to reproduce this behaviour.
0
Please sign in to leave a comment.
Comments
16 comments