MS-SQL Servr7-login command script inserted in Stored Procedure

[markajem]

Is there a way to place within a stored procedure that is used within a Crystal report a login in script where I can set up a username and password to flow with it.

CR v8.5

Thanks
Mark

 

[vidru]

If I understand you correctly, what you want to do won't work, as the report would need the login info prior to calling the stored procedure.

Can you explain your intent a little more?

-dave

 

[markajem]

Yes, okay.

We had a stored procedure written for us by a programmer 1 1/2 years ago. The report has been working correctly until recently. It seems that with all the security patches that we have applied to our servers somewhere in there we must have caused the stored procedure to fail because of a blank password being sent to the database.

We are presently trying to call in two seperate databases with the stored procedure. Please see the procedure below for a reference:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER    PROCEDURE DailyQtyFulfilled

AS

select 	i.itemnmbr as GP_ItemNumber,
	i.itemdesc as GP_ItemDesc,
	qtyonhnd as GP_QtyOnHand,
	isnull(f.qtyfull,0) as GP_QtyToBeFulfilled,
	isnull(f.tot_xtndprce,0) as GP_ExtPrice,
	isnull(i.currcost,0) as GP_CurrCost,
	isnull(rb_pickdetl.tot_qty_picked,0) as RB_QtyPickedNotShipped,
	isnull(rb_binlocat.tot_pcs,0) as RB_QtyOnHand

from iv00102 q (nolock)
	inner join iv00101 i (nolock)
		on q.itemnmbr = i.itemnmbr
	
-- Get the fulfilled quantity and extended price from SOP10200
	left outer join (select itemnmbr,sum(xtndprce) as tot_xtndprce, sum(qtyfulfi) as qtyfull
			from sop10200 (nolock)
			where qtyfulfi >0
			group by itemnmbr
			having sum(qtyfulfi) > 0) f
		on q.itemnmbr = f.itemnmbr

-- Get the quantity picked and not shipped from PICKDETL 
	left outer join (select product, sum(cast(qty_picked as numeric(19,5))) as tot_qty_picked
			from myserver2.wmsdata.dbo.pickdetl
--			where complete ='C'
			where qty_picked <> '0'
			group by product) rb_pickdetl
		on q.itemnmbr = cast(rb_pickdetl.product as char(31))

-- Get the quantity on hand from BINLOCAT 
	left outer join (select product, sum(cast(quantity as int) * cast(packsize as int)) as tot_pcs
			from myserver2.wmsdata.dbo.binlocat
			group by product) rb_binlocat
		on q.itemnmbr = cast(rb_binlocat.product as char(31))		

where q.locncode = 'MAPLE' 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Notice: This stored procedure is running from the myserver1, then in the middle of the procedure it calls in the other server myserver2.

The error message we are getting is:

Error 18452: Login Failed for user '(null)', Reason: Not associated with a trusted SQL server connection.

The IT director checked the web and made sure all the settings were as they were suggested.

Any clue here??

Thanks for your help