September 18, 2013 at 6:04 am
We have this stored procedure create that runs when i add any of the parameters to the section of the code to prompt me to enter values at the time of execution but ideally i want to run this for all sets of data and when i do that, i receive an error message of "Command text was not set for the command object"
If under the Add parameters section, I add the following list of parameters
@PCN_Key AS INT,
@CostSetKey as INT,
@Part_Type_MP AS VARCHAR(100)
and then when I go to execute the sproc with the values of 156371 for the PCN_Key, 1609 for the CostSetKey and Bottle for the Part_Type_MP, the table gets populated. Since there are more then 1 PCN_Key and Part_Type_MP, i would like the SPROC to pull in all data. So when I remove the parameters section from the code and execute it, no parameters appear on the Execute Procedure box and when I click OK, the message then appears.
USE [ConstarOLAP_PROPHIX_FactDb]
GO
/****** Object: StoredProcedure [dbo].[ProphixStdCostExport] Script Date: 09/16/2013 16:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
-- Author: <Peter Pearce, Baker Tilly>
-- Create date: <September 9, 2013>
-- Description: <Executes Remote Plex Sproc for Exporting Standard Cost>
--
ALTER PROCEDURE [dbo].[ProphixStdCostExport]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@PCN_Key AS INT = '',
@CostSetKey AS INT = '1609',
@Part_Type_MP AS VARCHAR(100),
@Part_No AS VARCHAR(100) = '',
@Cost_Date AS VARCHAR(20),
@Building_No_MP AS VARCHAR(1000)='',
@qq AS CHAR(1) = '''',
@sproc AS VARCHAR(1000)
SET @Cost_Date = CONVERT(VARCHAR,DATEADD(d,0,DATEDIFF(d,0,GETDATE() -1)), 120)
SET @sproc = 'sproc166848_58075_1335485('
@qq @Part_No @qq ','
CAST(@PCN_KEY AS VARCHAR(20)) ','
@qq CONVERT(VARCHAR, @Cost_Date,120) @qq ','
@qq @Part_Type_MP @qq ','
@qq @Building_No_MP @qq ','
CAST(@CostSetKey AS VARCHAR(20)) ')'
DELETE FROM [tblStdCost]
INSERT INTO [tblStdCost]
(
Part,
Plant,
Building,
Date,
Account,
Currency,
Version,
Measure
)
EXEC(@sproc) AT [PLEXREPORTSERVER]
END
September 18, 2013 at 6:35 am
There are some oddities in the code - but this could be a cutnpaste issue
you are setting some INTs to string values
@PCN_Key AS INT = '',
@CostSetKey AS INT = '1609',
try removing the quotes
Also I think you need to look at the procedure sproc166848_58075_1335485 on the [PLEXREPORTSERVER] , which looks like it acts upon the [tblStdCost] table.
It may give you a clue. if not post the contents of that other sproc here.
September 18, 2013 at 6:42 am
The sproc166848_58075_1335485 does not act upon the tblStdCost as i am just trying to get the results on this sproc to populate this table at the end of the processing. If i populate these 3 parameters with the below values and execute the sproc, this data is populated into the table.
@PCN_Key AS INT = '156371',
@CostSetKey AS INT = '1609',
@Part_Type_MP AS VARCHAR(100)='Bottle',
September 18, 2013 at 8:20 am
If I run this
DECLARE
@PCN_Key AS INT = '',
@CostSetKey AS INT = '1609',
@Part_Type_MP AS VARCHAR(100),
@Part_No AS VARCHAR(100) = '',
@Cost_Date AS VARCHAR(20),
@Building_No_MP AS VARCHAR(1000)='',
@qq AS CHAR(1) = '''',
@sproc AS VARCHAR(1000)
SET @Cost_Date = CONVERT(VARCHAR,DATEADD(d,0,DATEDIFF(d,0,GETDATE() -1)), 120)
SET @sproc = 'sproc166848_58075_1335485('
@qq @Part_No @qq ','
CAST(@PCN_KEY AS VARCHAR(20)) ','
@qq CONVERT(VARCHAR, @Cost_Date,120) @qq ','
@qq @Part_Type_MP @qq ','
@qq @Building_No_MP @qq ','
CAST(@CostSetKey AS VARCHAR(20)) ')'
It gives syntax error:
September 18, 2013 at 8:24 am
I receive the command completed successfully message. I can even execute the sproc with no issues but when i query the tblStdCost table, there is no data in there.
September 18, 2013 at 9:34 am
Why are you executing this stored proc via dynamic sql? You are adding complications when it is not needed. You are most likely not getting the desired results because you are passing all sorts of very strange values.
Your variables are a mess, lets look at them one at a time.
@PCN_Key AS INT = ''
The value here is now 0. It never gets set anywhere so why bother with a variable? Why do you declare it as an int and then cast it to varchar(20) the only time you use it?
@CostSetKey AS INT = '1609',
Here you have an int but are forcing an implicit conversion. Why even use a variable if it is hardcoded?
@Part_Type_MP AS VARCHAR(100),
The value is NULL. Again, why a variable when it is not set anywhere?
@Part_No AS VARCHAR(100) = '',
Do you really want to pass an empty string to your proc as a part number?
@Cost_Date AS VARCHAR(20),
Why a varchar??? It is a datetime value, you should use the proper datatypes.
@Building_No_MP AS VARCHAR(1000)='',
Yet another empty string being passed.
@qq AS CHAR(1) = '''',
I can see why you would do this but dynamic sql doesn't really make sense in this context.
Have you tried to look at the dynamic string you are executing? Aside from the fact that it is absolutely chock full of syntax errors it will never actually do anything.
Let's start by "fixing" the code so it will compile.
DECLARE
@PCN_Key AS INT = '',
@CostSetKey AS INT = '1609',
@Part_Type_MP AS VARCHAR(100),
@Part_No AS VARCHAR(100) = '',
@Cost_Date AS VARCHAR(20),
@Building_No_MP AS VARCHAR(1000)='',
@qq AS CHAR(1) = '''',
@sproc AS VARCHAR(1000)
SET @Cost_Date = CONVERT(VARCHAR,DATEADD(d,0,DATEDIFF(d,0,GETDATE() -1)), 120)
SET @sproc = 'sproc166848_58075_1335485('+
@qq+@Part_No +@qq+ ','+
CAST(@PCN_KEY AS VARCHAR(20)) +','+
@qq +CONVERT(VARCHAR, @Cost_Date,120)+ @qq +','+
@qq +@Part_Type_MP+ @qq+ ','+
@qq +@Building_No_MP+ @qq+ ','+
CAST(@CostSetKey AS VARCHAR(20)) +')'
Now it will at least compile and not throw syntax errors. However, you are just building up a string and there is at least one NULL so the entire result is NULL.
In essence all you are doing is using dynamic sql to execute NULL.
I would drop the idea of using dynamic sql for this. It is adding confusion to your process. Then you need to figure out what the values for these parameters need to be or where to find the values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2013 at 10:58 am
I am not that familiar with SPROCs and am learning on the fly here. I tried to create another callout sproc that would identify the values for the parameters and have that be executed first which would then execute the main sproc.
USE [ConstarOLAP_PROPHIX_FactDb]
GO
/****** Object: StoredProcedure [dbo].[DailyStdCost] Script Date: 09/18/2013 12:56:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Tom Stagliano, Constar
-- Create date: September 10, 2013
-- Description:Excute SPROC to pull Plex Std Cost
-- =============================================
ALTER PROCEDURE [dbo].[DailyStdCost]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DELETE FROM dbo.tblStdCost
INSERT INTO dbo.tblStdCost
EXECUTE dbo.ProphixStdCostExport 156371, 1609, 'Barrier Additive'
INSERT INTO dbo.tblStdCost
EXECUTE dbo.ProphixStdCostExport 156371, 1609, 'Barrier Contaminated Regrind'
September 19, 2013 at 3:43 am
You want help because your code does not function the way you expect it.
We can help but we have to understand your code.
All we can do is , if we find faults with your code, then point them out to you.
The error you are getting is related to OLEDB, which is used when executing at a linked server. This stongly points to the fault being in the statement
EXEC(@sproc) AT [PLEXREPORTSERVER]
Lets take your code and add comments as I understand it. see comment lines in the code
/* INPUTS
@PCN_Key AS INT,
@CostSetKey as INT,
@Part_Type_MP AS VARCHAR(100)
156371 for the PCN_Key
1609 for the CostSetKey
Bottle for the Part_Type_MP,
*/
DECLARE
-- @PCN_Key is INT but is assigned an empty string. Advise you change to 0
@PCN_Key AS INT = '',
-- @CostSetKey is INT but assigned a string value. Advise you change to 1609 (without quotes)
@CostSetKey AS INT = '1609',
-- @Part_Type_MP is not set so will have default value NULL
@Part_Type_MP AS VARCHAR(100),
@Part_No AS VARCHAR(100) = '',
-- @Cost_Date is not set so will have default value NULL
@Cost_Date AS VARCHAR(20),
@Building_No_MP AS VARCHAR(1000)='',
@qq AS CHAR(1) = '''',
-- Default value will be NULL
@sproc AS VARCHAR(1000)
-- Then the Stored proc architecture will inject the values into your proc as follows.
SET @PCN_Key = 156371
SET @CostSetKey = 1609
SET @Part_Type_MP ='Bottle'
SET @Cost_Date = CONVERT(VARCHAR,DATEADD(d,0,DATEDIFF(d,0,GETDATE() -1)), 120)
-- I would normally add the concatenation operator (+) to join together parts of a string, to make it clear exactly what is going on:
-- as this SET @sproc statement does not run on my version of SQLEXPRESS (syntax error)
SET @sproc = 'sproc166848_58075_1335485('
@qq @Part_No @qq ','
CAST(@PCN_KEY AS VARCHAR(20)) ','
@qq CONVERT(VARCHAR, @Cost_Date,120) @qq ','
@qq @Part_Type_MP @qq ','
@qq @Building_No_MP @qq ','
CAST(@CostSetKey AS VARCHAR(20)) ')'
-- so your code becomes
SET @sproc = 'sproc166848_58075_1335485(' +
@qq + @Part_No + @qq + ',' +
CAST(@PCN_KEY AS VARCHAR(20)) + ',' +
@qq + CONVERT(VARCHAR, @Cost_Date,120) + @qq + ',' +
@qq + @Part_Type_MP + @qq + ',' +
@qq + @Building_No_MP + @qq + ',' +
CAST(@CostSetKey AS VARCHAR(20)) + ')'
-- At this point just print out what you got in @sproc to make sure its what you expected.
PRINT @sproc
-- I get this, (but only after I added all the + operators to the SET @Sproc statement)
-- sproc166848_58075_1335485('',156371,'2013-09-18 00:00:00','Bottle','',1609)
-- if you get NULL, then one of the component parts making up @Sproc is NULL
-- if not try running the @sproc without inserting into the table,
-- just run it with your current @sproc and see if there are any results
EXEC(@sproc) AT [PLEXREPORTSERVER]
DELETE FROM [tblStdCost]
INSERT INTO [tblStdCost]
(
Part,
Plant,
Building,
Date,
Account,
Currency,
Version,
Measure
)
-- the output of the proc call at PLEXREPORTSERVER is inserted into tblStdCost
EXEC(@sproc) AT [PLEXREPORTSERVER]
As with any debugging take it one step at a time 🙂
September 19, 2013 at 6:28 am
Tom,
Thanks for the guidance. This works for the single PCN_Key but we have 7 other PCN keys that i would the sproc to pull together at once instead of assigning the pcn_key value. There are also other Part_Type values that we need to pull information in for.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply