I have a somewhat confusing error that has suddenly appears after we migrated our database from SQL Server 2005 to SQL Server 2008 (although I can't think how it could be related to this).
I have a report that uses a total of eleven parameters, the first 3 of which are hidden from the user.
Parameter 4 is a popup box which uses the value of parameter 2 to populate itself, the code for this routine is as follows:
CREATE PROCEDURE sp_DBR_puLocations @mydbr_param2 int, @inLogin varchar(30)
AS
BEGIN
declare @SQL varchar(max)
declare @DB varchar(50)
SET @DB = (select vUDB from mydbr..bmslookup where vUName = @inLogin)
SET @SQL = @DB + 'qselStorageLocationsByWarehouse ' + cast(@mydbr_param2 as varchar)
CREATE TABLE #tmp( LocationID int NOT NULL, Location varchar(50) NOT NULL, LocationAbbreviation varchar(max) NOT NULL ) insert into #tmp execute (@SQL) select LocationID, Location from #tmp drop table #tmp
END
Parameter 5 is another popup box, and this one is populated dependent on the users selection for parameter 4, the code for this is as follows:
CREATE PROCEDURE sp_DBR_puBins @mydbr_param4 int, @inLogin varchar(30)
AS
BEGIN
declare @SQL varchar(200)
declare @DB varchar(50)
SET @DB = (select vUDB from mydbr..bmslookup where vUName = @inLogin)
SET @SQL = 'SELECT BinID, Bin FROM ' + @DB + 'tblBin WHERE Active = 1 AND StorageLocationID = ' + cast(@mydbr_param4 as varchar)
execute (@SQL)
END
This code however fails. Instead of the user being presented with a popup box there is instead just a statement saying No parameters available.
As stated initially, this worked perfectly when we were on SQL Server 2005, but since the move to SQL Server 2008 it no longer does, and I can't see any reason why it wouldn't.
Thanks in advance.
