3

I have two DB's and an application that gets/sets data using an Entity Data Model in VS 2010.

I do my full stored procedure in DB(A) and then use the EXEC statement in DB(B) to return the data. The data returns fine and is correct, but whenever I run the program I get the following Exception in C#:

The 'AbandonTime' property on 'GetAverageTimes_Result' could not be set to a 'String' value. You must set this property to a non-null value of type 'Decimal'.

I have checked everwhere in the Data Model, the values are Decimal and set as nullable etc etc. Even if I change this to any other type or non-nullable value, I still get the same error. It is incredibly frustrating.

Any help will be appreciated.

Melanie
  • 584
  • 2
  • 11
  • 31

1 Answers1

0

If the serial is on the main table from EACH database, then you can just join the tables together using the fully qualified name:

CREATE VIEW vWhatever

AS

SELECT (whatever you want to select)

FROM [Database A].[schema - usually dbo].DSNA_tblMaiin AS a

JOIN [Database B].[dbo].DSNB_tblMaiin AS b ON a.Serialnumber = b.SerialNumber

JOIN [Database C].[dbo].DSNC_tblMaiin AS c ON a.SerialNumber = c.SerialNumber

WHERE (whatever you want to filter by)

GO

If the assets were in one of the tables, but not all three (and you didn't know which one), you could use a UNION within the view to accomplish it - it will essentially bring all three tables together, appending the fields that you specify (of the same datatype) until you have one large "virtual table" ex:

CREATE VIEW vWhatever

AS

SELECT col1, col2, SerialNumber, etc.

FROM [Database A].[dbo].DSNA_tblMaiin

UNION ALL

SELECT col1, col2, SerialNumber, etc.

FROM [Database B].[dbo].DSNB_tblMaiin

UNION ALL

SELECT col1, col2, SerialNumber, etc.

FROM [Database C].[dbo].DSNC_tblMaiin

GO

Dot notation is exactly what you're looking at. And if you were extending the query to another server, it would just be [ServerName].[DatabaseName].[ObjectSchema].[ObjectName] instead of [DatabaseName].[ObjectSchema].[ObjectName]

zapbuild
  • 182
  • 4