sys.object
-
Yes, im at lost. The developer and management didn't see eye to eye. I believe hi's departure was not pleasant and i doubt this developer will assist. So no handover or proper hand over was done.
Right now, im working from a copy of the data. The security environment is high, because i was thrown off the server for not having a static IP.
So it might be, it is only showing me what i can see.
-
-
and have you bothered going to the manuals and search for the tables I mentioned?
as you mentioned there is a proc that already reads the table in question, if that "read" is not done through dynamic sql then Jeff's code should find it as long as you have view definition or db owner or ddl_admin on the database - if you don't then maybe that is why you don't get any results.
but going back the the table I mentioned try the following.
select sc1.name as SchemaName
, ob1.name as ObjectName
, ob1.type_desc as ObjectType
, sm.definition as SQLText
from sys.sql_modules sm
inner join sys.objects ob1
on ob1.object_id = sm.object_id
inner join sys.schemas sc1
on sc1.schema_id = ob1.schema_id
where definition like '%REPLACE WITH TABLE NAME!!!!%'this will give you any schema based object that contains the table name in question - and with that you also have the object name, type and the sql code of that object
-
Like Jeff said, we dont know how the table was created or being populated. I ran your code and got this:
-
now go through each one of those sp's and see if any of them is populating the table - I should not have to tell you this!!
-
Does not seem like it.
-
I don't know how you created your text file - but you missed the point. you need to MANUALLY go through each sp, through the object explorer, generate their script and look inside it.
if you did a copy and past of the query I gave you you are likely missing a lot of the code for each procedure/function.
or better yet - and I should have remembered this.
your company SHOULD have all your code on a source control of some kind - that is the correct place to search for this.
if you don't then your first bit of work should be to get a copy of Visual Studio with Sql Server Data Tools and reverse engineer your databases so all your code is on your PC and can be searched easily - and then get it onto a Source Control solution like GIT or Azure DevOps.
-
I did the reverse engineering with SSIS as suggested. I only found these code with a relation to the empty table.
Could it be that the insert code can be saved outside the dabase in a json file?
-
Most definitely YES. It could be just about anywhere including living on a WebServer, etc, etc, or in another stored proc as a bit of dynamic SQL where dependencies won't be taken from.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CREATE FUNCTION [dbo].[fn_GetSummaryReport] ( @pStartDate DATE, @pEndDate DATE ) RETURNS TABLE RETURN SELECT SUM(vurc.QuantityReceived) TotalLitres, SUM(vurc.QuantityReceived * afp.PricePerL) TotalValue, SUM(vurc.EligiblePurchases) EligibleLitres, SUM(vurc.EligiblePurchases * afp.PricePerL) EligibleValue, SUM(vurc.NonEligible) IneligibleLitres, SUM(vurc.NonEligible * afp.PricePerL) IneligibleValue, COUNT(vurc.QuantityReceived) TotalNumberOfTransactions, COUNT(IIF(le.RegNumber = 'RNF',NULL,1)) TotalNumberOfCompleteTransactions, COUNT(IIF(le.RegNumber = 'RNF',1,NULL)) TotalNumberOfFaultyTransactions, SUM(IIF(le.RegNumber = 'RNF',NULL,vurc.QuantityReceived)) TotalVolumeOfCompleteTransactions, SUM(IIF(le.RegNumber = 'RNF',vurc.QuantityReceived,NULL)) TotalVolumeOfFaultyTransactions, alb.RAFLevy RAFLevy, alb.FuelLevy FuelLevy, alb.PercEligible PercEligible, (alb.RAFLevy + alb.FuelLevy) / 100 * alb.PercEligible FinalRebatePL, ( SUM(vurc.EligiblePurchases * alb.FuelLevy) + SUM(vurc.EligiblePurchases * alb.RAFLevy) ) / 100 * alb.PercEligible TotalClaimable FROM vw_UsageReportCached AS vurc LEFT JOIN vw_FuelPrice AS afp ON vurc.TransactionDateTime BETWEEN afp.PurchaseDate AND ISNULL(afp.NextPurchaseDate, GETDATE()) LEFT JOIN auditLevyBreakdown AS alb ON vurc.TransactionDateTime BETWEEN alb.StartDate AND ISNULL(alb.EndDate, GETDATE()) AND alb.IsActive = 1 LEFT JOIN lstEquipment AS le ON le.FleetId = vurc.RegNumber WHERE CAST(vurc.TransactionDateTime AS DATE) BETWEEN @pStartDate AND @pEndDate GROUP BY alb.RAFLevy, alb.FuelLevy, alb.PercEligible
@frederico_fonseca @JeffModen This is what i get sraight from the server, see attached.
Can this 'NOT IN' be the problem?
CREATE VIEW [dbo].[vw_UsageReportCached]
AS
SELECT *
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)
)
GO -
It could -- You tell us.
Execute the select in the query in the view without the WHERE clause. Does it return anything? If not, then there is no data, period.
If so, then remove the filter on EquipmentId to find out if that is the problem. If that does not return data, then you know you don't having matching RegNumber(s) in lstEquipment. If it does return data, then you know you don't have matching EquipmentIds in mapEquipmentLocation.
Can this 'NOT IN' be the problem?
CREATE VIEW [dbo].[vw_UsageReportCached]
AS
SELECT *
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)
)
GO
Even if it works, the SQL in the where clause would probably be better written w/ an inner join instead of an in -- e.g.,
(SELECT le.RegNumber
FROM lstEquipment le
INNER JOIN mapEquipmentLocation mel ON mel.EquipmentId = le.Id)and I prefer not exists to NOT IN, so I'd be inclined to use that to avoid issues with nulls (unless you need the NOT IN behavior)
NOT EXISTS (SELECT * FROM lstEquipment le
INNER JOIN mapEquipmentLocation mel ON mel.EquipmentId = le.Id
WHERE le.RegNumber = cacheUsageLogbook.RegNumber
) -
Tables [cacheUsageLogbook] and [lstEquipment] contains data. I got your code to work, accept for the last one.
/****** Object: Table [dbo].[cacheUsageLogbook] Script Date: 2022/08/10 16:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cacheUsageLogbook](
[TransactionDateTime] [DATETIME] NOT NULL,
[ReceivedFromStorageUnitNumber] [NVARCHAR](50) NULL,
[QuantityReceived] [FLOAT] NOT NULL,
[TypeOfVehicle] [NVARCHAR](50) NOT NULL,
[OpeningBalanceFuel] [FLOAT] NOT NULL,
[RegNumber] [NVARCHAR](25) NULL,
[OpeningOdo] [NVARCHAR](53) NOT NULL,
[ClosingOdo] [NVARCHAR](53) NOT NULL,
[TotalOdoUsed] [NVARCHAR](53) NOT NULL,
[TotalFuelUsed] [FLOAT] NOT NULL,
[UnusedBalance] [FLOAT] NOT NULL,
[SpecificActivityPerformed] [NVARCHAR](4000) NULL,
[NonEligible] [FLOAT] NOT NULL,
[WhenActivityPerformed] [NVARCHAR](53) NOT NULL,
[WhereActivityPerformed] [NVARCHAR](4000) NULL,
[EligiblePurchases] [FLOAT] NOT NULL
) ON [PRIMARY]
GO
------------
/****** Object: Table [dbo].[lstEquipment] Script Date: 2022/08/10 16:13:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lstEquipment](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[FleetId] [NVARCHAR](25) NOT NULL,
[RegNumber] [NVARCHAR](25) NULL,
[EquipmentDescription] [NVARCHAR](100) NULL,
[ModelId] [INT] NOT NULL,
[MakeId] [INT] NOT NULL,
[TankSize] [FLOAT] NULL,
[ConsumptionTypeId] [INT] NULL,
[VehicleTypeId] [INT] NULL,
[DecommissionDate] [DATETIME] NULL,
[CreateDate] [DATETIME] NOT NULL,
[ModifyDate] [DATETIME] NOT NULL,
[IsActive] [BIT] NOT NULL,
[AFSEquipmentId] [INT] NULL,
CONSTRAINT [PK_lstEquipment] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[lstEquipment] ADD CONSTRAINT [DF_lstEquipment_CreateDate] DEFAULT (GETDATE()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[lstEquipment] ADD CONSTRAINT [DF_lstEquipment_ModifyDate] DEFAULT (GETDATE()) FOR [ModifyDate]
GO
ALTER TABLE [dbo].[lstEquipment] ADD CONSTRAINT [DF_lstEquipment_IsActive] DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstConsumptionType] FOREIGN KEY([ConsumptionTypeId])
REFERENCES [dbo].[lstConsumptionType] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstConsumptionType]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstMake] FOREIGN KEY([MakeId])
REFERENCES [dbo].[lstMake] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstMake]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstModel] FOREIGN KEY([ModelId])
REFERENCES [dbo].[lstModel] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstModel]
GO
ALTER TABLE [dbo].[lstEquipment] WITH CHECK ADD CONSTRAINT [FK_lstEquipment_lstVehicleType] FOREIGN KEY([VehicleTypeId])
REFERENCES [dbo].[lstVehicleType] ([Id])
GO
ALTER TABLE [dbo].[lstEquipment] CHECK CONSTRAINT [FK_lstEquipment_lstVehicleType]
GO -
Maybe there's also an lstEquipment procedure? Try it with the dbo schema -- i.e.,
dbo.lstEquipment
You should always preface/qualify object references w/ schemas.
I didn't because I didn't know the schema since the code example didn't include it.
-
-
My tables/views reverse engineered done with SSIS.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply