asked
Parameterized stored sproc in OLEDB Source /Error:Cannot find tables
I have a simple test stored proc(Sproc1) in dynamic sql which will loop though the databases. USE [Stage] GO CREATE PROCEDURE [dbo].[School] @DatabaseName varchar(50) = 'ABC1' AS DECLARE @sql varchar (100) SELECT @sql ='SELECT s.Name,s.grade FROM '+@DatabaseName +'.dbo.school s' print @sql exec (@sql) List of Databases: 1. Stage---->The Stored Sproc is present. 2. ABC1----->Database1(the School table is present) 3. ABC2----->Database2(the School table is present) 4. ABC3----->Database3(the School table is present) In SSIS Package: Execute SQL Task-->Gets the database names from a table ResultSet:Full result set Connection Type:
ADO.NET Connection:Stage(ADO) Source:DirectInput SQL Statement: Select DatabaseName from Stage.Target In Result Set window Result Name :0 Variable Name:RegionDatabaseRS Foreach Loop container collection--Enumerator : Foreach ADO Enumerator ADO object source variable: RegionDatabaseRS--->Rows in the first table variable Mappings variable :User::DatabaseName DataFlow : OLEDB Source connection(Stage) SQL Command : EXEC [dbo].[School] @DatabaseName = ? Parameters:@DatabaseName Variables: User::DatabaseName It is throwing me error when I try to execute the sproc from the Stage.(with the Stage Connection Manager) ERROR: Invalid object name .dbo.School when i try to create the same sproc in ABC1 database and change the connection string to ABC1 it works fine and loops through ABC1,ABC2,ABC3 database. But i need to make that work from the stage database. Can anyone please help me out with this issue,passing the databasename in a parameterized stored sproc.
One better way could be ALTER PROCEDURE [dbo].[School] @DatabaseName varchar(50) = 'ABC1' , @LoopCounter INT --THIS IS TO SHOW AT WHICH ITERATION THE PROCEDURE COLLAPSED. YOU SHOULD SUPPLY IT FROM YOUR SSIS PACKAGE AS DECLARE @sql varchar (100) IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = @DatabaseName AND HAS_DBACCESS(@DatabaseName)) BEGIN SELECT @sql ='SELECT s.Name,s.grade FROM '+QUOTENAME(@DatabaseName) +'.dbo.school s' print @sql exec (@sql) END ELSE RAISERROR ('The supplied database name %s is incorrect OR You do not have the access to the database. The loop counter is %d',16,1,@DatabaseName, @LoopCounter) WITH LOG --Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
are you sure the paramaters are looping correctly? One way to make sure is to put a script task in the loop, and pass the databasename variable in, and the do a simple msgbox in the script to display the variable value.
As the error suggests the database name supplied is an empty string. So as Daniel has pointed out, you can use msgbox to sort out what is wrong or make sure that a valid database name is provided.
Ok then change your procedure a bit to something like this to troubleshoot (In test environment) ALTER PROCEDURE [dbo].[School] @DatabaseName varchar(50) = 'ABC1' , @LoopCounter INT --THIS IS TO SHOW AT WHICH ITERATION THE PROCEDURE COLLAPSED. YOU SHOULD SUPPLY IT FROM YOUR SSIS PACKAGE AS DECLARE @sql varchar (100) IF LTRIM(RTRIM(@DatabaseName)) = '' OR @DatabaseName IS NULL RAISERROR ('The supplied database name is incorrect. The loop counter is %d',16,1,@LoopCounter) WITH NOWAIT SELECT @sql ='SELECT s.Name,s.grade FROM '+QUOTENAME(@DatabaseName) +'.dbo.school s' print @sql exec (@sql)
USE [Stage] GO ALTER PROCEDURE [dbo].[School] @DatabaseName varchar(50) = '' AS BEGIN SET NOCOUNT ON IF @DatabaseName = '' BEGIN SELECT s.Name,s.grade FROM school s RETURN(0) END DECLARE @sql varchar (100) SELECT @sql ='SELECT s.Name,s.grade FROM '+@DatabaseName +'.dbo.school s' EXEC (@sql) END **Is there a better way to validate the SSIS package?**
