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.

  • 4f1

  • 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:

     

    Attachments:
    You must be logged in to view attached files.
  • 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.

    Attachments:
    You must be logged in to view attached files.
  • 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?

    json_sql

    • This reply was modified 6 days ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • 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)
    Intro to Tally Tables and Functions

  •         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

     

    • This reply was modified 2 days, 6 hours ago by  yrstruly.
    • This reply was modified 2 days, 6 hours ago by  yrstruly.
    • This reply was modified 2 days, 6 hours ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • 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.server3

     

     

    server4

    /****** 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.

  • f1

    Attachments:
    You must be logged in to view attached files.
  • My tables/views reverse engineered done with SSIS.

    • This reply was modified 1 day, 23 hours ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply