December 23, 2008 at 8:44 am
Colleagues;
I have an interesting, multi-faceted challenge. Six months ago (as an IT contractor) I came into an MS Navision 5.0 environment and was asked to become the SQLRS\SP expert. I had no previous experience with either (as well as Navision) but a challenge never stopped me in the past (plus work always beats unemployment) so I jumped in. My current company has no development staff so internal resources are unavailable. Moreover, cash for external resources has dried up since the markets crashed. We had contractors for a few weeks who set up an initial batch of SP’s and this is what I have used as a springboard for forward development.
The current issue is this:
We have three companies with three different sets of Navision tables, so to accomplish some level of reusability between the three companies I have been using nvarchar variables to drive the SP dynamic queries. Moreover, I pass parms into the SP’s from RS to generate the outputs. This current project requires that I check a navision shipment to determine if a number of qualifications are met. The 1st step generates a row count, which is then evaluated to determine if another step should be executed. All this processing is enclosed in an nvarchar variable which is then executed via an exec clause. The text generated in the nvarchar looks perfect and runs perfectly when executed in a query window in SQL Server. (BTW, we are using SQL Server 2005 SP2.) However, no output is generated when the SP is run. I assume the issue has to do with the nvarchar and the sql exec command.
Block 1 shows the contents of the nvarchar var from debug. Block two shows the SP code …
Any help or direction you can give is appreciated. If I’m way off track RE best practice, I would love to see alternatives. If you have examples of code I could use, feel free to share it.
Best regards, thanks in advance and happy holidays, Rich Sara
BLOCK 1:
~~~~~~~~
CREATE TABLE #Top10_Cust_Items
(
No_ varchar(10),
CName varchar(50),
NER_ItemNo varchar(30),
Item_Desc varchar(50),
Cust_ItemNo varchar(25)
)
declare @sql nvarchar
declare @Count Int set @Count = 0 set @Count = (SELECT Count(*) From [NER Data Products, Inc_$Posted Package] Where [Posted Source ID] = 'SS0000057' And [Posted Source ID] In (Select Distinct PP.[Posted Source ID] FROM [NER Data Products, Inc_$Sales Shipment Line] AS SL INNER JOIN [NER Data Products, Inc_$Posted Package] AS PP ON SL.[Document No_] = PP.[Posted Source ID] INNER JOIN
[NER Data Products, Inc_$Customer] CM ON SL.[Sell-to Customer No_] = CM.No_ Where SL.Type = '2' And PP.[Blind Shipment] = '0' and PP.[Double Blind Shipment] = '0' and PP.No_ Like 'PKG%' and (upper(CM.[Name]) Not like '%NER%' And upper(CM.[Name]) Not like '%IMAGE1%'))) If @Count > 0 Begin INSERT INTO #Top10_Cust_Items SELECT TOP(10) CM.No_, CM.Name, SIL.No_,IM.Description, SIL.[Cross-Reference No_] FROM [NER Data Products, Inc_$Sales Invoice Line] AS SIL INNER JOIN [NER Data Products, Inc_$Item] AS IM ON SIL.No_ = IM.No_ INNER JOIN [NER Data Products, Inc_$Customer] ON SIL.[Sell-to Customer No_] = CM.No_ WHERE (SIL.[Sell-to Customer No_] = '4048') GROUP BY CM.No_, CM.Name, SIL.No_, IM.Description, SIL.[Cross-Reference No_] Order BY sum(Quantity) desc Select * From #Top10_Cust_Items End Exec (@sql)
BLOCK 2:
~~~~~~~~
USE [PRODUCTION]
GO
/****** Object: StoredProcedure [dbo].[wsp_Fast_Facts_Customer_Sales_Report~Triggered] Script Date: 12/23/2008 08:12:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------
-- Modifications Log:
----- Coder:
----- Date:
----- ModID:
----- Narrative for changes made:
----- 1:
---------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[wsp_Fast_Facts_Customer_Sales_Report~Triggered]
(
@CompanyCode varchar(10) ,
@Cust_No varchar(10),
@Shipment_ID varchar(15),
@Mode varchar(1)
)
AS
Set NoCount ON
/*** For Global Usage */
declare @DBName varchar(100)
Select @DBName=[DBName] From [$Company] Where CompanyCode=@CompanyCode
declare @sql nvarchar(max)
CREATE TABLE #Top10_Cust_Items
(
No_ varchar(10),
CName varchar(50),
NER_ItemNo varchar(30),
Item_Desc varchar(50),
Cust_ItemNo varchar(25)
)
--------------------------------------------------------------------------------------------------------------------
-- Mode 1, Run individual Report from Invoice Trigger -------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
If @Mode = '1'
Begin
End
---------------------------------------------------------------------------------------------------------------------
-- Mode 2, Run Report from Shipments Trigger ------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
Else If @Mode = '2'
Begin
-- IF returned count is at least 1 the current shipment passes qualification testing ...
set @sql = ''
set @sql = @sql + ' declare @Count Int '
set @sql = @sql + ' set @Count = 0 '
set @sql = @sql + ' set @Count = (SELECT Count(*) From [' + @DBName + '$Posted Package] Where [Posted Source ID] = ''' + @Shipment_ID + ''' '
set @sql = @sql + ' And [Posted Source ID] In (Select Distinct PP.[Posted Source ID] FROM [' + @DBName + '$Sales Shipment Line] AS SL INNER JOIN '
set @sql = @sql + ' [' + @DBName + '$Posted Package] AS PP ON SL.[Document No_] = PP.[Posted Source ID] INNER JOIN '
set @sql = @sql + ' [' + @DBName + '$Customer] CM ON SL.[Sell-to Customer No_] = CM.No_ '
set @sql = @sql + ' Where SL.Type = ''2'' And PP.[Blind Shipment] = ''0'' and PP.[Double Blind Shipment] = ''0'' and PP.No_ Like ''PKG%'' '
set @sql = @sql + ' and (upper(CM.[Name]) Not like ''%NER%'' And upper(CM.[Name]) Not like ''%IMAGE1%''))) '
set @sql = @sql + ' If @Count > 0 '
-- Insert top 10 items
set @sql = @sql + ' INSERT INTO #Top10_Cust_Items '
set @sql = @sql + ' SELECT TOP(10) CM.No_, CM.Name, SIL.No_, IM.Description, SIL.[Cross-Reference No_] '
set @sql = @sql + ' FROM [' + @DBName + '$Sales Invoice Line] AS SIL INNER JOIN '
set @sql = @sql + ' [' + @DBName + '$Item] AS IM ON SIL.No_ = IM.No_ INNER JOIN '
set @sql = @sql + ' [' + @DBName + '$Customer] CM ON SIL.[Sell-to Customer No_] = CM.No_ '
set @sql = @sql + ' WHERE (SIL.[Sell-to Customer No_] = ''' + @Cust_No + ''') '
set @sql = @sql + ' GROUP BY CM.No_, CM.Name, SIL.No_, IM.Description, SIL.[Cross-Reference No_] '
set @sql = @sql + ' Order BY sum(Quantity) desc '
set @sql = @sql + ' Select * From #Top10_Cust_Items '
set @sql = @sql + 'exec (@sql) '
Drop table #Top10_Cust_Items
Return
End
---------------------------------------------------------------------------------------------------------------------
-- Mode ?, Error ----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
Else
Begin
Select 'Mode is unrecognized'
Return
End
December 23, 2008 at 8:57 am
In Block 1, I notice you didn't specify a length for @sql. Run this and see what happens as a result.
-----------------------------------------------------------------------------------
declare @sql nvarchar
set @sql = 'Deck the halls with boughs of holly fa la la la laaa la la la laa.'
select @sql
-----------------------------------------------------------------------------------
In Block 2, you declare @sql as varchar(max) and then build a string to be executed, but you include the exec(@SQL) within the string itself. How is the @sql string supposed to execute itself? Whenever you build strings to be executed, first try putting a PRINT @sql statement at the end. Cut and paste the results from the PRINT into another window to execute. See what results you get. Without being able to run your code, I think what you should have at the end is as follows:
instead of
-------------------------------------------------------
set @sql = @sql + ' Select * From #Top10_Cust_Items '
set @sql = @sql + 'exec (@sql) '
Drop table #Top10_Cust_Items
try this
-------------------------------------------------------
set @sql = @sql + ' Select * From #Top10_Cust_Items '
set @sql = @sql + 'Drop table #Top10_Cust_Items'
exec (@sql)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 23, 2008 at 9:27 am
All any of us know is what you post 😉 I did notice the declaration in block 2, and was in the process of revising my earlier comments. Look over my first post again and let me know if it helps.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 23, 2008 at 9:41 am
Bob,
You, my friend, ROCK! You were right on the money. I think I confused myself as this particular proc is my biggest and most complex yet. So, hey, one more question -
Am I way off base RE a best practices approach here? Is there another way to handle variable table names and variable processing in SP's feeding SQLRS reports?
Any guidance at all is appreciated!
Thanks again, RDS
December 23, 2008 at 9:47 am
You're welcome, Rich.
Dynamic SQL is the only option I know of to handle variable table names, including identical table names in separate DBs. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply