Missing Data(finding the root problem)
- yrstruly wrote:
Currently my "auditLevyBreakdown" table contains no data, it is suppose to have data in it.
Your function is grouping by columns from that table and it's empty??? Do you suppose that might be the reason why, even with outer joins, that could be an issue with getting any output from the function???
--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) -
Yes, that can be the issue. Do you think it will work when there is data in that table?
I am trying to find the stored procedure(s) that is linked to the table in question. I have tried the Script out an Entire Database in SQL Server. What do you recommend will be the quickest way in doing this?
- yrstruly wrote:
Yes, that can be the issue. Do you think it will work when there is data in that table?
You should try it and see. "One good test is worth a thousand expert opinions".
yrstruly wrote:I am trying to find the stored procedure(s) that is linked to the table in question. I have tried the Script out an Entire Database in SQL Server. What do you recommend will be the quickest way in doing this?
Funny that you should ask... I recently started working on some code to make that easier. With the understanding that the code is in a "WIP" status and that I don't have the time to support questions on it, you can try the following. Do see the reference I cite in the flower box for more information.
/******************************************************************************
Purpose:
Find objects that refer to the given object.
Does NOT find FK's, Indexes, etc.
-------------------------------------------------------------------------------
Usage Example:
--===== Simple usage
Notes: Needs conversion to a system proc in Master database.
Needs expansion for more useability (See References)
-------------------------------------------------------------------------------
References:
https://stackoverflow.com/questions/22005698/how-to-find-all-the-dependencies-of-a-table-in-sql-server
Need to revisit the above for some other ideas to make this even more useful.
-------------------------------------------------------------------------------
Revision History:
Rev 00 - 26 Jul 2022 - Jeff Moden
- Initial PoP creation and unit test.
Needs conversion to a system proc in Master database.
Needs expansion for more useability (See References)
******************************************************************************/--===== Identify the object to find references for.
DECLARE @ObjectName NVARCHAR(261) = N'schemaname.objectname'
;
--===== Find the objects referencing our object by name.
SELECT RefObjectName = CONCAT(sre.referencing_schema_name
,'.'
,sre.referencing_entity_name)
,RefObjectType = obj.type_desc
,RefObjectID = sre.referencing_id
,IsCallerDependent = is_caller_dependent
,ParentObjectID = obj.parent_object_id
,ParentObjectName = CONCAT(OBJECT_SCHEMA_NAME(obj.parent_object_id)
,'.'
,OBJECT_NAME(obj.parent_object_id))
,CreatedOn = obj.create_date
,LastModifiedOn = obj.modify_date
FROM sys.dm_sql_referencing_entities(@ObjectName, 'OBJECT') sre
JOIN sys.objects obj
ON obj.object_id = sre.referencing_id
ORDER BY RefObjectType,RefObjectName
;
GO--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) -
I dont quite know how i should modify your code to get the desired output?
-
Could this tool, SQL Dependancy Traker help me get my asnwer?
-
Yes, that tool could help you find stored procedures that reference the table, as could Jeff's query, or right-clicking on the table in SSMS & selecting "View Dependencies"
But once you identify dependencies, you still have to dig through procedures to find those that are inserting/updating the table in question... or are supposed to but is failing.
Or inserts/updates could be happening/not happening via dynamic SQL.
Or, given the number of views involved, inserts/updates may be done on a view selecting from the table rather than directly on the table.
Or you may find there is a gap in the workflow and it really isn't populated.
Or that the queries are out-of-date, and looking in the wrong place.
-
I ran Jeff's query, it returns nothing:
- Jeff Moden wrote:
Step 1 would be to query just the view to see if the date range returns anything. Then add a piece of your query for one of the joins and see what happens. Wash, rinse, repeat.
+1 🙂
Far away is close at hand in the images of elsewhere.
Anon. - yrstruly wrote:
I ran Jeff's query, it returns nothing:
DUDE!!!! CHANGE THE VALUE OF THE STRING FOR THE BLOODY @OBJECTNAME VARIABLE TO THE SCHEMA NAME AND OBJECT NAME FOR THE BLOODY TABLE YOU'RE LOOKING FOR! 😛 😛 😛
--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) -
It does not work.
-
and what part of "schema.objectname" don't you understand for you to have omitted one of the parts?
-
All of it.
-
This view brings back data:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (10) [TransactionDateTime]
,[ReceivedFromStorageUnitNumber]
,[QuantityReceived]
,[TypeOfVehicle]
,[OpeningBalanceFuel]
,[RegNumber]
,[OpeningOdo]
,[ClosingOdo]
,[TotalOdoUsed]
,[TotalFuelUsed]
,[UnusedBalance]
,[SpecificActivityPerformed]
,[NonEligible]
,[WhenActivityPerformed]
,[WhereActivityPerformed]
,[EligiblePurchases]
FROM [dbo].[vw_UsageReport]
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply