ADP - variables to pass to Stored Proc (1 Viewer)

[Adrianna]

Okay,
I've searched and found a lot of unasnwered questions. I've been using DAO and not ADO forever, so bare with me here (also been away from VBA for a while). I've created an adp file (access 2003) linked to a SQL 2005 database. I would like to loop through tTableNames and pass the field tableName into two sequential stored procs until all tableName fields in the tTableName table have been processed. Seems super straight forward, but I've never done it before. I don't believe that I need to establish a "connection" because the adp already has me connected to the data. So, I would assume that I should just be able to pass the variable as the input parameter (@tableNm) into the stored proc?

Thanks!

 

[pdx_man]

Well, since you're in the SQL Server Forum, I will give you the SQL Server answer. This should be run totally on SQL Server, not with VBA. Since you are using a SP, I can assume that the tables you are processing are also SQL Server tables, and your tTableNames table is also a SQL Table containing the names of the SQL Tables to be processed.

Create Procedure dbo.proc_GoodName

DECLARE tables_cursor CURSOR
    FOR
    SELECT TheField FROM tTableNames WHERE type = 'U'

OPEN tables_cursor
DECLARE @tablename sysname

FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)

BEGIN

    EXEC YourProc @tablename)
    FETCH NEXT FROM tables_cursor INTO @tablename

END
DEALLOCATE tables_cursor

 

[Adrianna]

Unfortunately, I'm using MS Access as my intermediary, as the two SQL databases are no connected. One is SQL 2000- supporting our live customer base, the other is our SQL 2005 server which we are preparing for our new .NET web interface, but took the opportunity to completely alter and improve the data structure. So, my scripts are created to migrate data....everything runs fine except the process of clearing out tables for a fresh push.

Hmmmm....actually, I could create a temp table, but I still need to loop through the tTables to pass the tablename to the sp_DelContents and sp_ReSeed SP's.
I'm going to try what you posted. My brain is just fried.

 

[pdx_man]

Hmmm ... sounds like a job for DTS or SSIS, depending on whether you want to push/pull the data from/to SS2000 to/from SS2005 (DTS) or SSIS to do the reverse.