How to assign the control paramater to sqldatasource in code behind page.

I ran into an error "object data not set refererece " ( most dreaded error message) at the point where the control parameter was assigned to the sqldatasource in the aspx page.

<asp:SqlDataSource ID="SqlDataSourceAppliedDateScreenDateCheck" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString1%>"
SelectCommand="PROC1"
SelectCommandType="StoredProcedure" DataSourceMode="DataReader"><SelectParameters><asp:ControlParameter ControlID="DropDownListCode" Name="NewCode"
PropertyName="SelectedValue" Type="Int32" /></SelectParameters></asp:SqlDataSource>
The DropdowListCode was not accessible because it is inside a FormView.I changed the above as follows to make it work.

Page1.aspx <asp:SqlDataSource ID="SqlDataSourceAppliedDateScreenDateCheck" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1%>"
SelectCommand="PROC1"
SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
</asp:SqlDataSource>

// removed the control parameter to add in the code behind page
Page1.aspx.vb

Protected Sub SqlDataSourceAppliedDateScreenDateCheck_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSourceAppliedDateScreenDateCheck.Selecting
'Pass the control parameter in the code behind

Dim drpNewCode As DropDownList = DirectCast(FormView1.FindControl("DropDownListCode"), DropDownList)
Dim sqlCtrlParam As SqlParameter = New SqlParameter("@NewCode", drpNewCode.SelectedValue.ToString())
e.Command.Parameters.Add(sqlCtrlParam)
End Sub

Change the order of the column in a table

I created a column and had to drop and add a column named ID with the identity value. Since I re-added the column ID after the creation of the table, this column was the last. I wanted to move the column in the first since it was an identity column for that table.
This is how I was able to change the order of the column ID

SSMS -> Tools----Options ---Designers --- Unheck Prevent saving changes that require table - recreation 

Then change the column order in the table designer.

Using default trace to capture the event in the server

We had a situation whem the database was running fine since yesterday but itwas noticable slow today. So, first thing I suspected that a developer might have altered a stored procedure that ight have aused a delay ( bad query or a missing join ????)

In order to find out I took the following step.

--Find if the default race is enabled

SELECT * FROM sys.configurations WHERE configuration_id = 1568

--if it is not enabled we can enable it by running the following
sp_configure 'show advanced options' ,1;GO RECONFIGURE ;
GO
sp_configure 'default trace enabled', 1;
GO

RECONFIGURE;GO  --find the location of the default trace in the server
select * from :: fn_trace_getinfo(default)Go  --property 1 - value 2 means the trace is set for rollover when the file  has reached the maximum size.
--property 2 shows the location of the default trce file in the server
--property 3 shows the maximum size in MB of the file
--property 4 shows if there is a stop time. NULL means the trace is not set to stop
--property  5 shows if the trace is enabled, value 1 means yes it is.

Now to find all the event since yesterday

select e.name , t.starttime , t.DatabaseName , objectname , LoginName , t.HostName , t.ApplicationName , t.spid , t.eventclass
from fn_trace_gettable( 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_469.trc' , 5 ) as t
join sys.trace_events e
on e.trace_event_id = t.EventClass
where ObjectName is not null
and t.EventClass = 164 -- object altered
 and starttime > '08/30/2012'
order by t.StartTime

This pointed me to the right direction as I started to analyze the store procedures that was rolled out.

Database Mail Executable Minimum Lifetime

We are facing some issue with Sql Server 2008 R2 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time and we get dead locks alert

I adjusted the database mail executable minimum lifetime ( in seconds ) from 600 to 10 and this problem seems to have dissapear.

Databasemail object on SSMS --------> Configure databasemail ----------------->View or change system parameters--------->Database mail executable minimum lifetime change it to 10 seconds





.

BC30554: masterpage is ambiguous

I did not get this error on my dev machine. I did not get this compilation error in the staging site. I got this error in the produciton site.

Resolution: There was another file called masterpage2.aspx that was using the same namespace masterpage. It looked like a test file or for some reason a blank masterpage file was renamed this. Removed the masterpage2.aspx file , did a build and it worked fine.

Error handling in transactions in sql 2008

BEGIN TRANSACTION TRAN1

BEGIN TRY
--put all the valid statements here--


   CREATE TABLE #test(intREsult int);

   INSERT INTO #test Values (4/0);
   INSERT INTO #test Values (1/2);


  COMMIT TRAN TRAN1
END TRY


BEGIN CATCH
--the error handling code including the rollback

    DECLARE @errorMEssage nvarchar(1000);
   SELECT @errorMEssage = ERROR_MESSAGE();
   ROLLBACK TRAN TRAN1;
   RAISERROR(@errorMEssage,16,1);

END CATCH

Deleting duplicate rows

A very cool technique to find and delete duplicate rows and just keep one unique row uinsg CTE ( common table expression) in SQL 2008

Initially the table had data

updateId  ptno
1                95689
2                95689
3                95689
4               91458
5               91000

Result desired
1               95689
4               91458
5               91000

(3 unique rows)

SELECT UpdateID,Ptno INTO #TempPtno From  dataTable1
WHERE uploaded = 0
AND deleted=0
AND flag=0 ;

/* Delete Duplicate records */
WITH CTE ( UpdateId,Ptno, DuplicateCount)
AS
(
SELECT UpdateId,Ptno,
ROW_NUMBER() OVER(PARTITION BY Ptno ORDER BY ptno) AS DuplicateCount
FROM #TempPtno
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



**If there is a semicolon missing at the end of the statement before WITH CTE the following ing error will be thrown
Msg 319, Level 15, State 1, Procedure  procName , Line number
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Accessing control in a listview from code behind

In this case we are accessing the hyperlink control from the code behind to format the NavigateURL property of the control

 Protected Sub lvTACRlist_ItemDataBound(ByVal sender As Object, ByVal e As  System.Web.UI.WebControls.ListViewItemEventArgs) Handles lvTACRlist.ItemDataBound

        Dim h1 As HyperLink = CType(e.Item.FindControl("hyperLink1"), HyperLink)
        h1.NavigateUrl = h1.NavigateUrl + "&H=" + ddhoslist.SelectedValue.ToString()

 End Sub

Accessing control in a gridview from code behind

Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender

For Each gvRow In GridView1.Rows
 If gvRow.RowType = DataControlRowType.DataRow Then
   Dim h1 As HyperLink = CType(gvRow.Cells(0).Controls(0), HyperLink)
   h1.NavigateUrl = h1.NavigateUrl & "&H=" & Request.QueryString("H")
 End If
Next

End Sub

Accessing masterpage objects from child page

I had a project where I had to pass in the hospital code to the link button that was at the top of the each page . The top section of the each page was a masterpage.

I added a property called SetHospitalCode on the masterpage as follows

Public m_HosCode As String
    Public Property SetHospitalCode() As String
        Get
            Return m_HosCode
        End Get

        Set(ByVal value As String)
            m_HosCode = value
        End Set

    End Property

--------
Protected Sub DashBoard_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DashBoard.Click
      
   If m_HosCode Is Nothing Then
            Response.Redirect("DashBoard.aspx")
    Else
           Response.Redirect("DashBoard.aspx?H=" + m_HosCode.ToString())
   End If
End Sub

I set the property from the each child page as follows
Dim getMaster As MasterPage
 getMaster.SetHospitalCode =  hospitalcode 

where hospitalcode is the new value of the hospital from each page.
DashBoard is the ID of the linkbutton

 

Data Conversions

taken from http://msdn.microsoft.com/en-us/library/ms187928.aspx

Error:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Here is the sp that caused the error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO
/** ***** Version *****
* $Revision:  $
* $Date: $
* $Author: $
* $Archive: $
*
* Sample use: exec PROC_ARCHIVE_EOM
* Comments : sp to perform the monthly archive of the transactions table.
* */
CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN

BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
 year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))

IF @@error <> 0 GOTO  Error_Handler


COMMIT TRAN TRAN1

END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
ROLLBACK TRAN TRAN1




Added @@tranCount check to avoid this error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO

CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from data2link.statusUpdates.dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN
BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))


IF @@error <> 0 GOTO Error_Handler


IF @@TRANCOUNT > 0

 COMMIT TRAN TRAN1


END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
IF @@TRANCOUNT > 0
ROLLBACK TRAN TRAN1

To find which processID the current SQLServer is running on

• Fire up the SQL Server configuration Manager
• Double Click on the SQL Server Services
• On the right hand side panel find the SQL SERVER
• Right Click on it , go to properties and the Process ID is listed on the Services tab.

error:process could not execute 'sp_replcmds' error on Publisher with Remote Distributor

Today the replication failed on our Publisher. We have a transactional replication running from Publisher to the  Subscriber and they  are in two differnt machines, both using  SQL 2008 R2. The exact error we got was

1. Replication failed: process could not execute 'sp_replcmds' error on Publisher with Remote Distributor
2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'databasename'

Tried  the following step with success.

 --STEP 1

exec sp_changedbowner [domain\user]

This made the [domain\user]  account the owner and mapped it to dbo 

--STEP 2

In SSMS find the replication and expand it
Expand the local publication folder
Select the publication and right click on it and seclet "View Log Reader Agent Status"
Stop the Agent and Restart the agent


Now all ther replications were back in sync.

I ran the follwoing query to see if they are back up running

select * from distribution..MSrepl_transactions order by entry_time

Cleaning up of msdb database

Cleaning up msdb database is one of the neglected yet most important task of a sql administrator. We have some SSIS packages, full back up once a day and transactional backup in every 15 minutes. We use databaseMail extensively therefore cleaning up msdb database is a good practice.

The msdb database was 241 MB and after cleaning up ( running the following scripts) I am able to bring it down to 153 MB. I am keeping a log of the database growth in a table in every 4 hours. I do not think that the size of the msdb has to be this big. So I am leaving it the way it is and then looking at table where the file growth is captured  befor I make the decision if I need to rsize the db to a smaller size.

The scripts that I ran

--removing anything before a month

DECLARE
SET @date = DATEADD(day, -30, CURRENT_TIMESTAMP)
 --delete backup history

--delete old mail items
--especially, if you are sending attachements
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date
--delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date
--delete the SQL Server agent job history log
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date
EXEC msdb.dbo.sp_delete_backuphistory @date @date datetime

script: List the permissions of a user

I had a  task where I had to list all the permission of a specific user on the database . The following script came to the rescue


SELECT dppriper USER_NAME(dppriper.grantee_principal_id) AS [UserName], .type_desc AS principal_type_desc, .class_desc, OBJECT_NAME (dppriper.major_id) AS object_name, .permission_name, .state_desc AS permission_state_desc
FROM sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE
--dppriper.permission_name = 'EXECUTE'
USER_NAME(dppriper.grantee_principal_id) = 'myuser'

 --**replace the myuser with the user we are looking for
--**If we want to find only the stored procedures that the user has perission on comment out the  clause in the above query.

The size of the message body in dbmail

SQL 2008 can take more than 8000 characters in the meassage body of dbmail . While declaring the message body variable just set the size of the varchar to max.
For .e.g.

declare @msg_body varchar(max),           
 @v_subject


This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)

Check the backup process

Script to see how the backup is progressing.

SELECT percent_complete , (estimated_completion_time/1000)/60 Estimated_completion_time_Mins , (total_elapsed_time/1000)/60 Total_Elapsed_Time_Mins ,DB_NAME(Database_id) DBName ,* FROM
sys.dm_exec_requests WHERE session_id = (the spid for running the back up - you can find by running sp_who2 active)

MSDTC error while using Transactionscope

In one of my project while writing a client application in VB.NET I had to make a call to two different databases on different servers and in case of error encountered both the transaction had to roll back. I used transactionscope to achieve this. I could use this since the database server is SQL 2008. Transactionscope ( a very cool thing) can be used on in SQL 2005 or higher. However I was running into the following error whenever while trying to open connection2 ( second conection ).

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.



Here is a snippet of my code (in VB.NET)


While debugging through the code, it thre error as soon as it was trying to open the oConn2 ( server2)
I checked in the database server if DTC was enabled. It was. What I had missed was the MSDTC had to be configured in the client server as well. Once the MSDTC was configured on the client server Viola it worked like a charm.



Imports System.Transactions  'you will have to add reference to System.Transactions first

Using scope As New TransactionScope()
Using oconn1 As New SqlConnection(strconn1)


    ocmd1 = New SqlClient.SqlCommand()
    ProgressBar1.PerformStep()
    ocmd1.Connection = oconn1
    ocmd1.CommandText = "PROC_SKIPTRACE_RESULTS_INSERT"
    ocmd1.CommandType = CommandType.StoredProcedure
    ocmd1.CommandTimeout = 600
    Dim paramPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
    paramPTNO.Direction = ParameterDirection.Input
    ocmd1.Parameters.Add(paramPTNO)
   Try
   If oconn1.State = ConnectionState.Closed Then


       oconn1.Open()
   End If
   returnValue = ocmd1.ExecuteNonQuery()
  Catch ex As Exception
    MsgBox(ex.Message.ToString())
  Finally
    oconn1.Close()
  End Try


Using oConn2 As New SqlConnection(strconn2)
 ocmd2 = New SqlClient.SqlCommand()
 ocmd2.Connection = oConn2
 ocmd2.CommandText = "PROC_SKIPTRACE_PATIENT_TABLE_SUSPENSE_UPDATE"
 ocmd2.CommandType = CommandType.StoredProcedure
 ocmd2.CommandTimeout = 600
 Dim paramintPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
 paramintPTNO.Direction = ParameterDirection.Input
 ocmd2.Parameters.Add(paramintPTNO)


Try
 If oConn2.State = ConnectionState.Closed Then
 oConn2.Open()
 End If
 returnValue = ocmd2.ExecuteNonQuery()
Catch ex As Exception
 MsgBox(ex.Message.ToString())
Finally
 oConn2.Close()
End Try


End Using 'oConn2
End Using 'oConn1
scope.Complete()
End Using ' transactionScope

Dropping multiple tables

/*The table names are logged in the table SQLAdmin_Monitor_tbl_size_info
Using cursor to loop thorough the table and dropping them.*/



DECLARE @TableName VARCHAR(100)
DECLARE @dropCommand VARCHAR(255)

DECLARE
FOR

OPEN tableCursor
FETCH next FROM tableCursor INTO @TableName

WHILE (@@Fetch_Status >= 0)
 BEGIN
  SET @dropCommand = N'DROP TABLE ' +@TableName
  EXECUTE(@dropCommand)
  FETCH next FROM tableCursor INTO @TableName
End
CLOSE  tableCursor
DEALLOCATE  tableCursor

Update stats

I had a project where I had to find when the stats were updated last and then had to update the stats of the table which was used heavily. I found the sript on the following blog

http://troubleshootingsql.com/2010/01/22/how-to-check-database-statistics-last-updated-date-time-in-sql-server/


SELECT schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
( select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2 ) as rowcnt,
 convert(DECIMAL(18,8),
convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
FROM sysindexes i
INNER JOIN sysobjects tbls
ON i.id = tbls.id
INNER JOIN sysusers schemas
ON tbls.uid = schemas.uid
INNER JOIN information_schema.tables tl
ON tbls.name = tl.table_name
AND schemas.name = tl.table_schema
AND tl.table_type='BASE TABLE'
WHERE 0 < i.indid
AND  i.indid < 255 and table_schema <> 'sys'
 AND  i.rowmodctr <> 0 and i.status not in (8388704,8388672)
AND  (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0
ORDER BY ModifiedPercent
DESC   

Error: [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412). The step failed.

I have a stored procedure in server2 that I was trying to call from server1 using linkedserver so that it gets executed in server2.
Server 1

• Windows Server 2008(x86)
• SQL Server 2008 (x86) Standard Edition SP1

Server 2

• Windows Server 2008 (x86)
• SQL Server 2008 (x86) Standard Edition SP1

Following  is the snippet of my code on Server1

SET  XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION 
declare @RetVal int,

exec @RetVal = linksv1.db1.dbo.usp_update_Referrals 0

COMMIT TRANSACTION

So far I have taken the following steps

1. NETWORK CONNECTIVITY

• Ping server1 from server2  -- success
• Ping server2 from server1  -- success

2. DTC CONFIGURATION

• Open Start->Administrative Tools->Component Services
•  Expand the Component Services Node untill you find Local DTC right click and select properties option
•  In the Local DTC properties click in the security section and set the configuration as shown in the image

   Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically

The final step is make shure the DTC service is started and configured to start when windows start.

3. DTC SERVICE IS STARTED

• Open Start - > Open Start->Administrative Tools->Services
• Find the Distributed Transactions Coordinator Service
• Make sure the status is “Started” and the Startup Type is “Automatic”.

Follow the same step in Server2

After the DTC is configured , checked the lnked servers
Linked Server Configuration on Server 1

 OKC is the linked server pointing to my Server2
Highlight the linked Server
Right Click and go to properties
Select the Server Option in the properties window and make sure the following settings are applied
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True

All the settings were double checked ...will update in few weeks if we still get the "No transaction is active" error...

Script: View when the tables in a database were accessed last

I had to do a database clean up project, where I had to drop the usused table. I ran the following script to see when were the indexes used last and made the decision based on it. One caution though was the table that did not have indexes did not show the activity.

use [yourdb]
GO

SELECT sobj.[name],iu.last_user_seek,101,iu.last_user_update  FROM sys.dm_db_index_usage_stats iu
INNER JOIN sys.objects sobj
ON iu.object_id = so.object_id
WHERE so.type_desc = 'USER_Table'
ORDER BY  so.[name],last_user_seek
GO

error:[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

I ran into the following error when calling a stored procedure from SSIS. The stored proedure dumps the SELECT query result into a table variable and I was doing a final SELECT from the table variable.

[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

The error was eliminated after putting SET NOCOUNT ON in the begining of the stored procedure

Script: To log the space of the database

As a DBA one of my primary job is to keep track of the growth of the database so that I can forecast the space required. I have a static table where I run the following script and collect the data in about in 4 hours and analyze the data collected in the table.
I have scehduled this script as a sql job that runs in every 4 hours. Every Friday I look at the historic data and see by how large our databases grew.


DECLARE
DECLARE
DECLARE @rowcnt INT @iterator INT @dbname VARCHAR(200)DECLARE @exec_sql VARCHAR(500)SET @rowcnt = 0SET

 CREATE TABLE #db_file_info ([Database_Name] SYSNAME NOT NULL,[File_ID] SMALLINT NOT NULL,[File_Type] VARCHAR(10) NOT NULL,[File_Name] SYSNAME NOT NULL,[File_Path] VARCHAR(500) NOT NULL,[File_Size_in_MB] INT NOT NULL,[Space_Used_in_MB] INT NOT NULL,[Space_Left_in_MB] INT NOT NULL,[Time_collected] smalldatetime )
CREATE TABLE #db (dbid INT,name VARCHAR(200))INSERT INTO #dbSELECT dbid,nameFROM MASTER.dbo.sysdatabasesSET @rowcnt = @@ROWCOUNTWHILE@iterator <= @rowcntBEGINSELECT @dbname = '['+ name + ']'



Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,
[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)),
getdate()
from
dbo.sysfiles;
'FROM #dbWHERE dbid = @iteratorSET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
EXEC( @exec_sql)
SET @iterator = @iterator + 1END
INSERT INTO SQLAdmin_Monitor_db_file_infoSELECT *FROM #db_file_infoDROP TABLE #dbDROP



/*SQLAdmin_Monitor_db_file_info is the static table where I collect the results returned by the above script. */TABLE #db_file_info @iterator = 1