Finding difference in two databases/tables
-
That is confusing - how did you 'copy' the view from QA to production? And how would that create a table instead of a view?
You stated you used the code from QA to recreate the view in PROD - and that worked. Again, the reason for that working is because the view was out of synch with the table and recreating the view updated the definition. You asked why that would occur - I explained why that happens.
It seems you then decided to make additional changes - and it isn't clear what changes you have made nor is it clear what issues those changes are now causing. Just creating a table from the definition of the view isn't going to help anything - because that table doesn't have any data and won't be updated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I copied the view, using Import/Export Wizard.
As you can see a view is copied, but it is created as a Table. I dleted this created table and re-created the view from the QA db, but it gives me that same blanks for certain columns.
I recreated the view using all the coumns:
To get all columns i used *
***** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [TransactionDateTime]
,[ReceivedFromStorageUnitNumber]
,[QuantityReceived]
,[TypeOfVehicle]
,[OpeningBalanceFuel]
,[RegNumber]
,[OpeningOdo]
,[ClosingOdo]
,[TotalOdoUsed]
,[UnusedBalance]
,[SpecificActivityPerformed]
,[NonEligible]
,[WhenActivityPerformed]
,[WhereActivityPerformed]
,[EligiblePurchases]
FROM [_Prod_20220810_1950_2].[dbo].[vw_UsageReportCached]
......................
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [dbo].[vw_UsageReportCached]
AS
SELECT TransactionDateTime, ReceivedFromStorageUnitNumber, QuantityReceived , TypeOfVehicle, OpeningBalanceFuel,
RegNumber, OpeningOdo, ClosingOdo, TotalOdoUsed, UnusedBalance, SpecificActivityPerformed, NonEligible, WhenActivityPerformed,WhereActivityPerformed, EligiblePurchases
FROM [dbo].[cacheUsageLogbook]
WHERE
1=1
AND RegNumber NOT IN (SELECT le.RegNumber
FROM lstEquipment AS le WHERE le.Id IN (SELECT EquipmentId FROM mapEquipmentLocation AS mel)
)
GOError i get when trying to run function:
-
The import/export process is designed to create tables - you selected the source of the data to be a view and the destination to be a table, that is why it created a table.
Try running the code for the view directly in a query window. If the results are the same (they should be) - then the problem isn't the view, rather the problem is the data in the source table for the view.
Like I said - I was only answering the question on why recreating the view works. And again, that is because you are using * in the view definition. When the view is created the definition of the table(s) at that time are used - and if the underlying table(s) change then the view isn't updated with those changes and you get unreliable results or even errors when using the view.
I don't know what else has been provided - I scanned through the other posts and see a reference to a table-valued function. I have no idea what relation that has to this issue, but this issue is just about the view and why that view would be causing issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Please check my Logic?
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply