May 24, 2011 at 2:11 pm
I'm working with two DBs (DB1, DB2) within SQL Server Management Studio (SQL 2008 R2)
In DB1 I have a table, db1.ERate (contains employee payroll info). The fields I use are id (FK, varchar(10), not null) and rate (decimal(14,6), null)
In DB2 I have a view that has a table called empMain that I am linking DB1.ERate to. In table empMain of DB2 the linking field is called employeeid (nvarchar(50), not null).
Both fields are populated with the same data, but of different data types (varchar and nvarchar) If I add a field in db1.ERate of datatype varchar and populate it with data, the link between the table and the other table works fine and the data is displayed. If the link between the two tables is mismatched between varchar and nvarchar the query doesn't display any results.
So, I created a simple view that only has the DB1.ERate.id and DB1.ERate.rate in it and I CAST(id AS nvarchar(50)) to force it to type nvarchar. I rerun the main query (view) but change out the DB1.ERate table with the view I just created (that has the CAST()) and still no data is displayed.
So the question is, how do I make the data in DB1.ERate.id of type 'whatever' so that it correctly links to the data in DB2.empMain.employeeid of datatype nvarchar? I can't modify any table structure because the database is sync'd across the internet to a payroll vendor and if any table structure is different on "our end", payroll doesn't work.
Suggestions?
Thanks!
AWS
May 24, 2011 at 2:14 pm
Can you please post the table structure with sample data.
Also the code that is linking the 2 tables
---------------------------------------------------------------------------------------
It begins by taking the first step.
May 24, 2011 at 2:23 pm
May 24, 2011 at 2:30 pm
Here is db1.ERate table:
***********************************
CREATE TABLE [dbo].[ERate](
[co] [varchar](10) NOT NULL,
[id] [varchar](10) NOT NULL,
[rateCode] [varchar](10) NOT NULL,
[salary] [decimal](14, 2) NULL,
[rate] [decimal](14, 6) NULL,
[ratePer] [varchar](10) NULL,
... ETC. Rest of fields not necessary for example
CONSTRAINT [PK_ERate] PRIMARY KEY NONCLUSTERED
(
[guidfield] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
***********************************
Here is DB2.empMain table:
CREATE TABLE [dbo].[empMain](
[employee_id] [int] IDENTITY(1,1) NOT NULL,
[company_id] [int] NOT NULL,
[employeeid] [nvarchar](50) NOT NULL,
[unused2] [nvarchar](50) NOT NULL,
... ETC. Other fields removed
CONSTRAINT [PK_empMain] PRIMARY KEY NONCLUSTERED
(
[employee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
***********************************
Here is the view I am using that is replacing DB1.ERate:
SELECT rate, CAST(id AS nvarchar(50)) AS employeeid
FROM dbo.ERate
***********************************
Here is the main view that I am building my data source from that is not yielding any results (this is being run from within DB2):
SELECT dbo.Job.JobNumber AS JobId, dbo.empMain.firstname, dbo.empMain.lastname, dbo.timeCard.total_hr * DB1.dbo.sscERate.rate AS Hrs,
DATEADD(DAY, 7 - DATEPART(WEEKDAY, dbo.timeCard.timecard_dt), dbo.timeCard.timecard_dt) AS WeekEnding
FROM dbo.empMain INNER JOIN
dbo.timeCard ON dbo.empMain.employee_id = dbo.timeCard.employee_id INNER JOIN
dbo.Job ON dbo.timeCard.job_id = dbo.Job.job_id INNER JOIN
DB1.dbo.sscERate ON dbo.empMain.employeeid = DB1.dbo.sscERate.employeeid
ORDER BY WeekEnding DESC, JobId
***********************************
When I added a field to DB1.ERate and made it nvarchar(50), everything worked fine. Little did I know payroll was unable to process because the sync of data between here and the payroll vendor was failing. So I had to remove the new field and come up with an alternative that did not include a table structure alteration.
Thanks for your assistance.
May 24, 2011 at 2:39 pm
Why even create a view, do a join between the tables and do a Cast(fieldname, int). See example below
SELECT dbo.Job.JobNumber AS JobId, dbo.empMain.firstname, dbo.empMain.lastname, dbo.timeCard.total_hr * DB1.dbo.sscERate.rate AS Hrs,
DATEADD(DAY, 7 - DATEPART(WEEKDAY, dbo.timeCard.timecard_dt), dbo.timeCard.timecard_dt) AS WeekEnding
FROM dbo.empMain
INNER JOIN dbo.timeCard
ON dbo.empMain.employee_id = dbo.timeCard.employee_id
INNER JOIN dbo.Job
ON dbo.timeCard.job_id = dbo.Job.job_id
INNER JOIN DB1.dbo.ERate
ON dbo.empMain.employeeid = Cast(DB1.dbo.ERate.id, int)
ORDER BY WeekEnding DESC, JobId
---------------------------------------------------------------------------------------
It begins by taking the first step.
May 24, 2011 at 2:50 pm
Thanks, that does it. There was a "TEST" user in the empMain table that when I did that before I would get a conversion error converting "TEST" to int data type. I changed the ID from "TEST" to some number then applied your linking suggestion and now we're good.
Thanks for the quick info!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply