x

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.

more ▼

asked Dec 12, 2011 at 05:31 PM in Default

avatar image

palum
249 27 29 33

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.

Dec 12, 2011 at 06:29 PM Daniel Ross

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.

Dec 12, 2011 at 10:36 PM Usman Butt

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.

Dec 13, 2011 at 07:57 AM palum

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)
Dec 14, 2011 at 03:59 AM Usman Butt

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.
Dec 14, 2011 at 04:33 AM Usman Butt
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
        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?

more ▼

answered Dec 14, 2011 at 08:15 AM

avatar image

palum
249 27 29 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1219
x438

asked: Dec 12, 2011 at 05:31 PM

Seen: 1646 times

Last Updated: Dec 12, 2011 at 05:31 PM

Copyright 2017 Redgate Software. Privacy Policy