Linked Servers and FMTONLY

[mreigler]

We have a pretty basic interface set up with numerous vendors. We set up a linked server based on their company name. They create a stored procedure on their server that has the correct name and parameters. There are about 20 potential vendors that could be set up, but at any given location there are usually only 5 to 10. We use a single stored proc on our server to call the correct linked server based on the linked server (vendor) name. Our single procedrue looks something like this:

CREATE PROCEDURE [dbo].[usp_ExecuteRemoteProcedure]
	@ServerName VARCHAR(25),
	@StringIn VARCHAR(100),
	@BoolIn BIT,
	@Date1 DATETIME OUTPUT,
	@Date2 DATETIME OUTPUT,
	@Amount MONEY OUTPUT, 
	@Int1 INTEGER OUTPUT,
	@ErrorCode INTEGER OUTPUT,
	@ErrorDesc VARCHAR(100) OUTPUT

AS

	IF UPPER(@ServerName) = 'VENDOR1' BEGIN
				EXEC VENDOR1...usp_RemoteProcedure
					@StringIn,
			   		@BoolIn,
					@Date1 OUTPUT,
					@Date2 OUTPUT,
					@Amount OUTPUT ,
					@Int1 OUTPUT ,
					@ErrorCode OUTPUT,
					@ErrorDesc OUTPUT
				END
			RETURN
	END
	IF UPPER(@ServerName) = 'VENDOR2' BEGIN
				EXEC VENDOR2...usp_RemoteProcedure
					@StringIn,
			   		@BoolIn,
					@Date1 OUTPUT,
					@Date2 OUTPUT,
					@Amount OUTPUT ,
					@Int1 OUTPUT ,
					@ErrorCode OUTPUT,
					@ErrorDesc OUTPUT
				END
			RETURN
	END		
	IF UPPER(@ServerName) = 'VENDOR3' BEGIN
				EXEC VENDOR3...usp_RemoteProcedure
					@StringIn,
			   		@BoolIn,
					@Date1 OUTPUT,
					@Date2 OUTPUT,
					@Amount OUTPUT ,
					@Int1 OUTPUT ,
					@ErrorCode OUTPUT,
					@ErrorDesc OUTPUT
				END
			RETURN
	END			

GO

My problem is that I ran a trace and see the following which is taking 13 seconds to run. This is a noticable delay since the users typically see a resonse within a second.

SET FMTONLY ON  
EXEC usp_ExecuteRemoteProcedure' ',' ',0,'01-01-1992 01:01:01:000','01-01-1992 01:01:01:000',0,0,0,' '   
SET FMTONLY OFF

I'm wondering where this is coming from since I never call the procedure with empty parameters like this. Also if I break my one stored procedure into 20 different procedures that each call 1 linked server this seems to go away.






Mike Reigler
Melange Computer Services, Inc

 

[nice95gle]

If I can remember the FMTONLY is caused when you send a empty parameter to a SQL Statement.


so this will cause FMTONLY to be turned on

 EXEC VENDOR1...usp_RemoteProcedure
                    @StringIn,
                       @BoolIn,
                    @Date1 OUTPUT,
                    @Date2 OUTPUT,
                    @Amount OUTPUT ,
                    @Int1 OUTPUT ,
                    @ErrorCode OUTPUT,
                    @ErrorDesc OUTPUT

this will not

SET @Date1= getdate()
SET @Date2= getdate()
SET @Amount = 500
SET Int1 = 500
SET ErrorCode = 16
SET ErrorDesc ='This is a test'

 EXEC VENDOR1...usp_RemoteProcedure
                    @StringIn,
                       @BoolIn,
                    @Date1 OUTPUT,
                    @Date2 OUTPUT,
                    @Amount OUTPUT ,
                    @Int1 OUTPUT ,
                    @ErrorCode OUTPUT,
                    @ErrorDesc OUTPUT

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

I forgot @StringIn and @BoolIn but you get the idea.


Well Done is better than well said
- Ben Franklin

 

[mreigler]

@StringIn and @BoolIn are never empty (at least when called by me). All the output parameters are initialized to null. I've been doing some additional searches and found references to FTMONLY being used by ADO, which I'm using to call the stored procedure on my server. But the only place I see this is with this one stored procedure and I use ADO to execute all my other stored procs. So why don't I see it in other places?

I also forgot to add that this is on an SQL 2000 Server.

Mike Reigler
Melange Computer Services, Inc

 

[nice95gle]

>> All the output parameters are initialized to null

Do keep in mind that NULL by definition means UNKNOWN. So setting the para's to NULL is the same as passing nothing.
I don't think it has anything to do with ADO. Because of the Null para's SQL server can't evaluate the statement so it will just check for the correct formatting of your code. Just as a test hardcode the values in DEV and see if FMTONLY ON still happens.

Well Done is better than well said
- Ben Franklin