I receive an error on "rs.Open SQLStr, cn, adOpenStatic: A cursor with the name 'table_cursor' already exists.
SQLStr is a string relative to the SSMS query.
If there is a different approach obtaining a database's tables and record count of each table I'm open to suggestions.
The VBA code goes something like this:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=MSOLEDBSQL;Data Source=" & Server_Name_Target & ";Initial Catalog=" & DatabaseName & ";User ID=" & User_ID & ";Password=" & Password_Target & ";"
cn.Execute SQLStr
rs.Open SQLStr, cn, adOpenStatic
...more code
Code: Select all
-- This is the SSMS query script
DECLARE @DatabaseName NVARCHAR(128) = 'YourDatabaseName' -- Replace 'YourDatabaseName' with your actual database name
DECLARE @DynamicSQL NVARCHAR(MAX) -- Use a different variable to store the dynamic SQL script
-- Dynamically build SQL script including the USE statement
SET @DynamicSQL = 'USE ' + QUOTENAME(@DatabaseName) + '; -- Switch to the specified database
CREATE TABLE #TableCounts
(
TableName NVARCHAR(128),
RecordCount INT
)
DECLARE @TableName NVARCHAR(128)
-- Cursor to iterate through table names
DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Dynamic SQL to count records in each table
DECLARE @InnerSQL NVARCHAR(MAX)
SET @InnerSQL = ''INSERT INTO #TableCounts (TableName, RecordCount) SELECT '''''' + @TableName + '''''', COUNT(*) FROM '' + @TableName
EXEC sp_executesql @InnerSQL
FETCH NEXT FROM table_cursor INTO @TableName
END
CLOSE table_cursor
DEALLOCATE table_cursor
-- Select the record counts from #TableCounts
SELECT * FROM #TableCounts'
-- Execute the dynamically built SQL script
EXEC sp_executesql @DynamicSQL