October 17, 2016 at 3:22 pm
I'm trying to interpret the code of the guy who preceded me at my current position (unfortunately he's dead, so I cannot consult with him). I'm parsing out a stored procedure to track what's happening; and I'm seeing a block of code that is confusing me. The confusion stems from the variable @DataSourceJobRevenue (at least I think it's a variable). In the line that begins "SET @cmd", @DataSourceJobRevenue is being used; but I can't see where it's getting its value from. I cannot find this variable mentioned anywhere else in my database; so I'm assuming it does not have a default value.
Below is the code in question. If anyone can unlock this mystery for me, it'd be much appreciated.
Thank you
ALTER PROCEDURE [dbo].[ImportJobRevenue]
@DataSourceJobRevenue AS varchar(100)
AS
SET NOCOUNT ON
DELETE FROM JobRevenueStage
DECLARE @cmd varchar(100)
SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '
EXECUTE (@cmd)
October 17, 2016 at 4:00 pm
The value for @DataSourceJobRevenue must be passed in as a parameter value when the proc is run. For example:
EXEC dbo.ImportJobRevenue 'JobRevenueValue'
It's a required parameter, so its value must be provided.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 17, 2016 at 4:51 pm
I thought of that, but when I ran the following code:
use MainReport
select object_name(m.object_id), m.*
from sys.sql_modules m
where m.definition like N'%ImportJobRevenue%'
to find all references to the proc in question (ImportJobRevenue), but the only reference I get is the literal reference in the design code of the proc. Which tells me that the proc is not being called from any other proc in the database, if I'm understanding this correctly.
So then am I correct in thinking that I need to go find the code that is calling this procedure, wherever it is on the server (a daunting prospect), if I want to determine what parameter is being passed to the proc?
October 17, 2016 at 6:37 pm
deekadelic (10/17/2016)
I thought of that, but when I ran the following code:use MainReport
select object_name(m.object_id), m.*
from sys.sql_modules m
where m.definition like N'%ImportJobRevenue%'
to find all references to the proc in question (ImportJobRevenue), but the only reference I get is the literal reference in the design code of the proc. Which tells me that the proc is not being called from any other proc in the database, if I'm understanding this correctly.
So then am I correct in thinking that I need to go find the code that is calling this procedure, wherever it is on the server (a daunting prospect), if I want to determine what parameter is being passed to the proc?
Sounds logical.
I'd start from SQL Agent jobs.
Keep in mind - the file name might be coming from "DIR" output, so it may not be explicitly mentioned even in the job script.
_____________
Code for TallyGenerator
October 18, 2016 at 2:37 am
Sergiy (10/17/2016)
deekadelic (10/17/2016)
I thought of that, but when I ran the following code:use MainReport
select object_name(m.object_id), m.*
from sys.sql_modules m
where m.definition like N'%ImportJobRevenue%'
to find all references to the proc in question (ImportJobRevenue), but the only reference I get is the literal reference in the design code of the proc. Which tells me that the proc is not being called from any other proc in the database, if I'm understanding this correctly.
So then am I correct in thinking that I need to go find the code that is calling this procedure, wherever it is on the server (a daunting prospect), if I want to determine what parameter is being passed to the proc?
Sounds logical.
I'd start from SQL Agent jobs.
Keep in mind - the file name might be coming from "DIR" output, so it may not be explicitly mentioned even in the job script.
+1 for checking the Jobs.
Also might be worth checking other databases to see if there's a cross-database procedure call? Or even cross server? Recommend SQLSearch from Redgate (it's a free SSMS plug-in) to help find what you're looking for.
Also have a look in any SSIS components and any batch / cmd / sql files hanging around on the server(s) in the environment.
(Yes, I'm a Friend of Redgate. But this is free software... and Redgate provides this site. And I'm not quite sure of the point I'm making now...)
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 19, 2016 at 11:56 am
Unfortunately I'm using SQLExpress 2008, which apparently does not support SQL Agent.
I've noticed that part of the process that produces the value in the variable in question seems to be coming out of a compiled *.exe file. Am I correct in thinking that there is no way to get any visibility into an *.exe to see what it's actually doing, or is there some kind of back door?
October 19, 2016 at 12:09 pm
If this is being called from application code, and you do not have the source code, then that's going to be difficult.
If you simply want to know what value is being passed into the called to this proc, try this:
1. Create a separate utility database.
2. Create a table,
CREATE TABLE dbo.ImportJobRevenue_Parms
(
ImportJobRevenue_Parms_ID int IDENTITY,
DataSourceJobRevenue varchar(100),
Created_Timestamp datetime DEFAULT GETDATE()
)
3. At the top of the procedure, add this code:
INSERT INTO UtilDatabase.dbo.ImportJobRevenue_Parms(DataSourceJobRevenue)
VALUES(@DataSourceJobRevenue)
You are going to need to make sure that the proper permissions are granted on this database and table to make sure that no errors are thrown.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 19, 2016 at 12:42 pm
I had another idea that I really thought would work; but it isn't working, and I'm not sure why.
My idea was to simply add the code "Print @cmd" on the variable @cmd in the middle of the SP code; but when I run the full block of code all I get is "Command(s) completed successfully." Here is the code:
USE [17-oct-2016_MainReport]
GO
/****** Object: StoredProcedure [dbo].[ImportJobRevenue] Script Date: 10/19/2016 11:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportJobRevenue]
@DataSourceJobRevenue AS varchar(100)
AS
SET NOCOUNT ON
DELETE FROM JobRevenueStage
DECLARE @cmd varchar(100)
SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '
PRINT @cmd
EXECUTE (@cmd)
;
This is odd because the code below does print the value as expected.
use MainReport
declare @RAH as varchar(10)
set @RAH = 'TEST Value'
print @RAH
;
So why isn't the SP printing the value of the variable?
October 19, 2016 at 12:49 pm
I gave this a shot, of course changing the database reference to match my DB name; but I got this error message:
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@DataSourceJobRevenue".
But the problem is that I am trying to discover the value of @DataSourceJobRevenue; so declaring it would be pointless, wouldn't it?
October 19, 2016 at 12:50 pm
deekadelic (10/19/2016)
I had another idea that I really thought would work; but it isn't working, and I'm not sure why.My idea was to simply add the code "Print @cmd" on the variable @cmd in the middle of the SP code; but when I run the full block of code all I get is "Command(s) completed successfully." Here is the code:
USE [17-oct-2016_MainReport]
GO
/****** Object: StoredProcedure [dbo].[ImportJobRevenue] Script Date: 10/19/2016 11:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportJobRevenue]
@DataSourceJobRevenue AS varchar(100)
AS
SET NOCOUNT ON
DELETE FROM JobRevenueStage
DECLARE @cmd varchar(100)
SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '
PRINT @cmd
EXECUTE (@cmd)
;
This is odd because the code below does print the value as expected.
use MainReport
declare @RAH as varchar(10)
set @RAH = 'TEST Value'
print @RAH
;
So why isn't the SP printing the value of the variable?
Where do you expect it to be printed to?
If you are executing the procedure in a SSMS query window, you have to provide it to the procedure. So, you will see it. The print statement is redundant.
If it's being passed in from an application, the print statement will pass it back to the application calling the procedure. Unless you have done something in the application, you can't see this.
It sounds like you are in over your head.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 19, 2016 at 12:52 pm
Be aware too that if @DataSourceJobRevenue is NULL, then nothing will print, because
PRINT NULL
doesn't show any output.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 19, 2016 at 12:52 pm
deekadelic (10/19/2016)
I gave this a shot, of course changing the database reference to match my DB name; but I got this error message:Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@DataSourceJobRevenue".
But the problem is that I am trying to discover the value of @DataSourceJobRevenue; so declaring it would be pointless, wouldn't it?
Huh?
Can you post what you tried to do? Can you post all of the code from the entire procedure?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 19, 2016 at 1:04 pm
I was thinking it would be printed to the query window in which I'm running the SP, just above the message saying the command completed successfully.
I guess I have some more learning to do on this matter.
October 19, 2016 at 1:07 pm
Sure. This is the entire code block in question:
USE [17-oct-2016_MainReport]
GO
/****** Object: StoredProcedure [dbo].[ImportJobRevenue] Script Date: 10/19/2016 11:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportJobRevenue]
@DataSourceJobRevenue AS varchar(100)
AS
SET NOCOUNT ON
DELETE FROM JobRevenueStage
DECLARE @cmd varchar(100)
SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '
--PRINT @cmd
EXECUTE (@cmd)
DELETE FROM JobRevenueStage WHERE JobRevenue = 0 AND JobProfit = 0
DECLARE @postDate smalldatetime
SELECT @postDate = PostDate
FROM( SELECT TOP 1 PostDate FROM JobRevenueStage) a
------------------------------------------------------
-- Finalize JobRevenueDetail
------------------------------------------------------
DELETE JobRevenueDetail
WHERE PostDate = @postDate
INSERT INTO JobRevenueDetail
SELECT PostDate, BillCode, SalesRep, Ord, Dst, JobRevenue, FileNo, Seq, BillToNo,
ShipperNo, ConsigneeNo, Department,JobProfit
FROM JobRevenueStage
------------------------------------------------------
-- INSERT RankMonthJobRevenue, RankYTDJobRevenue
------------------------------------------------------
CREATE TABLE #rankmonthJobRevenue (AsOfDate datetime,
Rank int identity,
BillToNo varchar(50),
JobRevenue money
)
INSERT INTO #rankmonthJobRevenue (AsOfDate, BillToNo, JobRevenue)
SELECT @postDate, BillToNo, SUM(JobRevenue)
FROM JobRevenueDetail b
--JOIN Product p ON p.Class = b.Class AND p.BillCode = b.BillCode
WHERE PostDate = @postDate
GROUP BY BillToNo
ORDER BY SUM(JobRevenue) DESC
DELETE
FROM RankMonthJobRevenue
WHERE AsOfDate = @postDate
INSERT INTO RankMonthJobRevenue
SELECT * FROM #rankmonthJobRevenue
CREATE TABLE #rankytdJobRevenue (AsOfDate datetime,
Rank int identity,
BillToNo varchar(50),
JobRevenue money
)
INSERT INTO #rankytdJobRevenue (AsOfDate, BillToNo, JobRevenue)
SELECT @postDate, BillToNo, SUM(JobRevenue)
FROM JobRevenueDetail b
WHERE PostDate <= @postDate AND
YEAR(PostDate) = YEAR(@postDate)
GROUP BY BillToNo
ORDER BY SUM(JobRevenue) DESC
DELETE
FROM RankYTDJobRevenue
WHERE AsOfDate = @postDate
INSERT INTO RankYTDJobRevenue
SELECT * FROM #rankytdJobRevenue
------------------------------------------------------
-- Clean Up
------------------------------------------------------
DROP TABLE #rankmonthJobRevenue
DROP TABLE #rankytdJobRevenue
October 19, 2016 at 1:28 pm
deekadelic (10/19/2016)
Sure. This is the entire code block in question:USE [17-oct-2016_MainReport]
GO
/****** Object: StoredProcedure [dbo].[ImportJobRevenue] Script Date: 10/19/2016 11:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportJobRevenue]
@DataSourceJobRevenue AS varchar(100)
AS
SET NOCOUNT ON
INSERT INTO YourTableName(DataSourceJobRevenue)
VALUES(@DataSourceJobRevenue)
DELETE FROM JobRevenueStage
DECLARE @cmd varchar(100)
SET @cmd = 'BULK INSERT JobRevenueStage FROM ''' + @DataSourceJobRevenue + ''' '
--PRINT @cmd
EXECUTE (@cmd)
DELETE FROM JobRevenueStage WHERE JobRevenue = 0 AND JobProfit = 0
DECLARE @postDate smalldatetime
SELECT @postDate = PostDate
FROM( SELECT TOP 1 PostDate FROM JobRevenueStage) a
------------------------------------------------------
-- Finalize JobRevenueDetail
------------------------------------------------------
DELETE JobRevenueDetail
WHERE PostDate = @postDate
INSERT INTO JobRevenueDetail
SELECT PostDate, BillCode, SalesRep, Ord, Dst, JobRevenue, FileNo, Seq, BillToNo,
ShipperNo, ConsigneeNo, Department,JobProfit
FROM JobRevenueStage
------------------------------------------------------
-- INSERT RankMonthJobRevenue, RankYTDJobRevenue
------------------------------------------------------
CREATE TABLE #rankmonthJobRevenue (AsOfDate datetime,
Rank int identity,
BillToNo varchar(50),
JobRevenue money
)
INSERT INTO #rankmonthJobRevenue (AsOfDate, BillToNo, JobRevenue)
SELECT @postDate, BillToNo, SUM(JobRevenue)
FROM JobRevenueDetail b
--JOIN Product p ON p.Class = b.Class AND p.BillCode = b.BillCode
WHERE PostDate = @postDate
GROUP BY BillToNo
ORDER BY SUM(JobRevenue) DESC
DELETE
FROM RankMonthJobRevenue
WHERE AsOfDate = @postDate
INSERT INTO RankMonthJobRevenue
SELECT * FROM #rankmonthJobRevenue
CREATE TABLE #rankytdJobRevenue (AsOfDate datetime,
Rank int identity,
BillToNo varchar(50),
JobRevenue money
)
INSERT INTO #rankytdJobRevenue (AsOfDate, BillToNo, JobRevenue)
SELECT @postDate, BillToNo, SUM(JobRevenue)
FROM JobRevenueDetail b
WHERE PostDate <= @postDate AND
YEAR(PostDate) = YEAR(@postDate)
GROUP BY BillToNo
ORDER BY SUM(JobRevenue) DESC
DELETE
FROM RankYTDJobRevenue
WHERE AsOfDate = @postDate
INSERT INTO RankYTDJobRevenue
SELECT * FROM #rankytdJobRevenue
------------------------------------------------------
-- Clean Up
------------------------------------------------------
DROP TABLE #rankmonthJobRevenue
DROP TABLE #rankytdJobRevenue
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply