question

palum avatar image
palum 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.
ssistsql
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
0 Likes 0 ·
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.
0 Likes 0 ·
Still the issue is same, I have added a script task with msgbox Dim db_name As String db_name = Dts.Variables("User::DatabaseName").Value.ToString() MsgBox(db_name) Dts.TaskResult = ScriptResults.Success and it shows me the ABC1,ABC2,ABC3 databases and go green.
0 Likes 0 ·
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)
0 Likes 0 ·
Show more comments

1 Answer

·
palum avatar image
palum answered
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?**
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.