January 29, 2003 at 3:36 pm
This is the case. One of the developers created a function in one of the SQL Server DB's to get a record set from a different server.
When they went to the live db, the function does not work because is pointing to the developer db. How to declare the server name to enable the function to read the record set. Does the question make sense to you all?
I am placing the function code and hope some one can give us an answer soon. They need to go live with this aplication tomorrow and this the only hold out. Thank you in advance.
The function reads:
CREATE FUNCTION dbo.GetUserId(@CaseNo as char(7), @RunJob as Numeric(18,0))
RETURNS Varchar(12)
AS
BEGIN
DECLARE @TUserId as Varchar(12)
DECLARE @TOutUserId as Varchar(12)
DECLARE @Servername as varchar (12)
SET @servername = 'XXXX'
SELECT @TUserId = BJ.[USER_ID]
FROM @Servername.dbname.DBO.BATCH_JOB BJ,@Servername.dbname.DBO.RECORD_SELECTION RS,@Servername.dbname.DBO.BATCH_TYPE BT
WHERE BJ.STATUS_CODE = 2 AND
BJ.RUN_JOB_ID = RS.RUN_JOB_ID AND
BJ.BATCH_TYPE_ID = BT.BATCH_TYPE_ID AND
BJ.RUN_JOB_ID = @RunJob AND
-- BJ.RUN_DATE = @ReportDate AND
-- RS.DATESUBMITTED = @ReportDate AND
RS.STATUS_ID = 3
SELECT @TOutUserId = CASE WHEN @TUserId = null Then 'XXX' ELSE @TUserId END
RETURN(@TOutUserId)
END
<<
Any suggestions?
January 30, 2003 at 2:13 am
If you wish to soft code the name of the server into a param, you will have to use a dynamic sql statement.
January 30, 2003 at 3:17 am
Thank you. I solve their problem by just linking the two serves. The function works fine then. Thank you again for your input
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply