Must declare table variable '@P1select' while executing a OpenRowset query through WinForms

Question

I have created a WindowsForms tool to execute any select script placed inside a path when I click on the Execute button. Now I got a big query created with OpenRowSet concept and it also has many temp table created also.

But when I execute directly in the SSMS I would get the required rows. When I tried through the tool, it is giving the error:

The Batch Could not be analyzed because of compile errors, Must declare the table variable @P1select"

try
{
    DirectoryInfo d = new DirectoryInfo(path);
    FileInfo[] sqlfiles = d.GetFiles("*.sql");
    SqlCommand cm;
    using (SqlConnection cnn = new SqlConnection(ConnectionString))
        if (!(sqlfiles.Length == 0))
        {
            try
            {
                cnn.Open();
                foreach (FileInfo file in sqlfiles)
                {
                  
                    string script = file.OpenText().ReadToEnd();
                  
                    using (cm = cnn.CreateCommand())
                        cm.CommandText = script;
                  
                    cm.CommandTimeout = 3600;
                  
                    SqlDataReader reader = cm.ExecuteReader();
                  
                    var dt = new DataTable();
                    dt.Load(reader);
                    DataGV.AutoGenerateColumns = true;
                    DataGV.DataSource = dt;
                    DataGV.Refresh();
                    DataGV.ReadOnly = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:" + ex.Message, "Warning");
            }

Is there anything I want to update in the code to handle this?

OpenRowSetQuery:

    ''SELECT C.ClientName,C.createdon as ClientCreatedOn,C.Clientid  ,E.EnName,E.EngIDnum
         , E.CreatedOn as EngagementCreatedOn, 
              case when [AuditProjectDefinitionID] is null then case when E.[EngIDnum] is null then ''''Client'''' else ''''Engagement'''' end
                 else ''''Project'''' end ClientEngProject,  
         CN.CountryName
      ,[ProjectNumber]
      ,AudPrj.[AuditProjectName],AudPrj.AuditProjectDefinitionID,
      AudPrj.WorkflowEngIDnum
      
 ,case when mngreq =''''d0840bb3-2a51-4986-ba72-9cbf87bad77f'''' and  audprjactivity =''''A48DCB00-8092-4072-B271-A6B09D517A3A''''   then ''''Audit Project Linked to Enterprise or Other Risk''''
            when  mngreq =''''d0840bb3-2a51-4986-ba72-9cbf87bad77f'''' and audprjactivity =''''F7647E40-B4A2-45D5-9367-21CE413CBB97''''   then ''''Audit Project not Linked to  Enterprise or Other Risk''''
              when mngreq =''''d0840bb3-2a51-4986-ba72-9cbf87bad77f'''' and audprjactivity =''''E4041928-51F2-4765-8318-3498685860A1'''' then ''''Other IA Activity''''
               when mngreq =''''d52b9266-2080-4ac4-a5a6-8b77f59feec6'''' then ''''mngreqProjects''''
                       else  ''''Projects Not added'''' end as audprjactivity 
                                                                                                                                  
   ,case when ProjectType =''''1CC8E2AF-0A5F-4436-928A-4DE0176E2550''''   then ''''Compliance''''
            when ProjectType =''''0F645210-4E03-40FB-A177-93733798F75E''''   then ''''Risk Based''''
                      when ProjectType is null then ''''''''
            else ''''Follow up'''' end as ProjectType 

                                        
                                FROM ' + @DatabaseName1 + '.'+@EngSchemaName1+'.[Client] C
    left join  ' + @DatabaseName1 + '.'+@EngSchemaName1+'.Engagement E on C.ClientID = E.ClientID
    
  left join  ' + @DatabaseName1 + '.'+@EngSchemaName1+'.[AuditProjectDefinition] AudPrj on AudPrj.ClientId=E.ClientID and 
   AudPrj.EngIDnum=E.EngIDnum 
  and 
   ISNULL(IsDeleted,0)<>1
  left join ' + @DatabaseName1 + '.'+@EngSchemaName1+'.Country CN on C.CountryID=CN.CountryID  

    ORDER BY C.ClientID, E.EngIDnum, AudPrj.ProjectNumber 

    '')'

    INSERT INTO #ReportDet exec(@sql1) 


    if OBJECT_ID('tempdb.dbo.#EngagementSDP','U') IS NOT NULL DROP TABLE #EngagementSDP
CREATE TABLE #EngagementSDP(EngIDnum Nvarchar(max), WorkflowEngIDnum Nvarchar(max),subpracticeid int)
 
SET @sql4 = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @SQLServerName1 + ';Trusted_Connection=yes;'', 
    ''SELECT EngIDnum,WorkflowEngIDnum,subpracticeid
    FROM '+ @DatabaseName1 + '.'+@EngSchemaName1+'.EngagementSDP'')'```

Did you take a look at this https://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp ? — Coskun Ozogul, Sep 10 '20 at 07:31
@Kyle Wang : Sorry the whole SQL query has 355lines and cant copy it here — AbhisCodeStore, Sep 10 '20 at 07:48
@AbhisCodeStore, if you can't post the entire script, include at least the `OPENROWSET` query. — Dan Guzman, Sep 10 '20 at 10:04
I don't see `@p1select` declared or used in the query. Is somewhere else in the script or in one of the variables building the dynamic SQL statements? — Dan Guzman, Sep 10 '20 at 12:54
@DanGuzman There isn't any such variables used in this entire query — AbhisCodeStore, Sep 10 '20 at 13:03

Must declare table variable '@P1select' while executing a OpenRowset query through WinForms

0 Answers0