Issue with added Columns to table

Transact-SQL
1

Good afternoon all- first post here, thanks for having me! Question that maybe really basic but its been driving me a little crazy- So I am hoping for some help

So I have a procedure that clears out a table (CRM_Opportunty), and recreates it.
It works perfectly until I added the 5 lines between the commented out lines " --- charlie 220518"

As part of this (and I think this is where I go wrong) I added 5 columns to the table CRM_Opportunity

Capture

When I extend the table and make my code change the code runs cleanly, but nothing populates in the table- if I remove my new lines, and remove the 5 extended columns, and run it, the tables populates again.

I am sorry for the entry level question but did I miss a step after adding the columns.

I have also tried re-creating the table- same result.

Thank you all for any advice

Code is below

ALTER PROCEDURE [dbo].[CRM_OpportunitiesSp]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
   	
	Declare @OutputFile nvarchar(120), 
		@FilePath NVARCHAR(120),
		@bcpCommand NVARCHAR(1000)

	truncate table CRM_Opportunity;
	insert into CRM_Opportunity
	SELECT	o.opportunityid, 
			ui.username, 
			a.office, 
			left(a.account,200), 
			left(c.custid,20), 
			left(c.name,200),
			left(cpm.MasterGroup,30),
			op.Quantity, 
			o.estimatedclose,
			left(op.productnumber,50), 
			left(op.descrip,200), 
			op.estimatedclose as InstallDate, 
			pr.family as FamilyType, 
			replace(replace(cast(pr.description as nvarchar),char(10),''),char(13),'') as ExtendedDescription,
			op.calcprice * op.quantity as OpProductPotential, 
			op.calcprice * op.QUANTITY * o.closeprobability / 100 as OpProductWeightedPotential,
			o.total_margin as OppMargin, 
			o.total_margin_pct as OppMarginPct, --o.total_margin * o.total_margin_pct / 100 as OppWgtMargin,  
            op.margin as OppProdMargin, op.MARGINPCT * op.calcprice * op.quantity * o.closeprobability / 100 as OppProductMgnPct,
--- charlie 220518
  			o.closeprobability, 
			o.createdate, 
			o.stage, 
			o.seccodeid, 
			o.businessunit
   --- end charlie 220518

           from [99.99.99.999].SalesLogix.SysDba.OPPORTUNITY o
           left outer join [99.99.99.999].SalesLogix.SysDba.Opportunity_Product op ON op.opportunityid = o.opportunityid
           left outer join CRM_ProductMaster cpm ON cpm.CRM_PRODUCTID = op.productid
              left outer join [99.99.99.999].SalesLogix.SysDba.product pr ON cpm.CRM_PRODUCTID = pr.productid
           join [99.99.99.999].SalesLogix.SysDba.Account a ON a.accountid = o.accountid
           join [99.99.99.999].SalesLogix.SysDba.userinfo ui ON ui.userid = o.ACCOUNTMANAGERID
           LEFT outer join [99.99.99.999].SalesLogix.SysDba.AccountNumbers an ON an.accountid = o.accountid
           LEFT OUTER JOIN CUSTOMER c on c.salesofficeid = an.office and c.custid = ISNULL(an.accountnbr,a.accountid)
           WHERE o.status = 'Open'
end
2

could be a conflict with the source and destination column type and/or conflict between length of the source and destination .

Can you try running this using SSMS and see if there is an error : truncation error, data type error

1 Like
3

Thank you for the reply- thats the strange part

In SSMS with both versions of code I get a "Query Executed Successfully" message- even with the version that gives me no data in the table.

I actually tried removing one of the inserts from my code, to test, and I got an error saying

"Msg 213, Level 16, State 1, Procedure CRM_OpportunitiesSp, Line 19
Column name or number of supplied values does not match table definition."

So I know its working....

4

for test purposes try this.
remove insert into CRM_Opportunity

instead add the following

select x,y,z
into dbo.CRM_Opportunity_test
from x,y,z

and run sproc. then compare CRM_Opportunity and CRM_Opportunity_test.

the other thing it could be is that it is somehow caching the sproc? maybe drop and create? just be careful you lose permissions when you do that.

1 Like
5

Looks like it was caching, had to reboot SQL server.

Thank you so much for your time

6

there are less drastic approaches if you are interested.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS and maybe WITH RECOMPILE

Maybe others can chime in on these options.

1 Like
7

thank you

wasted so much time on it that I needed to fix

Appreciate the help