asked on

Really weird!!! Same exact code executes in Query tool, but not in .NET application

This is really weird

I have an SQL block of code that has the following code:

--Code Block 1
DECLARE @MY_VAR VARCHAR(50)
IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL
BEGIN
DROP TABLE #TEMP_TABLE
END
SELECT C.* INTO #TEMP_TABLE FROM OPENQUERY(MY_SERVER_NAME,'EXEC SOME_PROCEDURE_THAT_PRODUCES_A_TABLE ''ARGUMENT_VALUE1'', ''ARGUMENT_VALUE2''')
SELECT @MY_VAR=SOME_COL_NAME_IN_TEMP_TABLE FROM #TEMP_TABLE
SELECT 'FOUND RECORD: ' + @MY_VAR
DROP TABLE #TEMP_TABLE
GO

Open in new window

The weird thing now is --> If I use this block of code with different ['ARGUMENT_VALUE1','ARGUMENT_VALUE2'] that are passed to the procedure, see below:

--Code Block 2
DECLARE @MY_VAR VARCHAR(50)
IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL
BEGIN
DROP TABLE #TEMP_TABLE
END
SELECT C.* INTO #TEMP_TABLE FROM OPENQUERY(MY_SERVER_NAME,'EXEC SOME_PROCEDURE_THAT_PRODUCES_A_TABLE ''ARGUMENT_VALUE1'', ''ARGUMENT_VALUE2''')
SELECT @MY_VAR=SOME_COL_NAME_IN_TEMP_TABLE FROM TEMP_TABLE
SELECT 'FOUND RECORD: ' + @MY_VAR
DROP TABLE #TEMP_TABLE
GO

DECLARE @MY_VAR VARCHAR(50)
IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL
BEGIN
DROP TABLE #TEMP_TABLE
END
SELECT C.* INTO #TEMP_TABLE FROM OPENQUERY(MY_SERVER_NAME,'EXEC SOME_PROCEDURE_THAT_PRODUCES_A_TABLE ''ARGUMENT_VALUE3'', ''ARGUMENT_VALUE4''')
SELECT @MY_VAR=SOME_COL_NAME_IN_TEMP_TABLE FROM TEMP_TABLE
SELECT 'FOUND RECORD: ' + @MY_VAR
DROP TABLE #TEMP_TABLE
GO

DECLARE @MY_VAR VARCHAR(50)
IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL
BEGIN
DROP TABLE #TEMP_TABLE
END
SELECT C.* INTO #TEMP_TABLE FROM OPENQUERY(MY_SERVER_NAME,'EXEC SOME_PROCEDURE_THAT_PRODUCES_A_TABLE ''ARGUMENT_VALUE5'', ''ARGUMENT_VALUE6''')
SELECT @MY_VAR=SOME_COL_NAME_IN_TEMP_TABLE FROM TEMP_TABLE
SELECT 'FOUND RECORD: ' + @MY_VAR
DROP TABLE #TEMP_TABLE
GO

Open in new window

Then I pase this block of code in MS SQL Management Studio query then execute it I will get three tables displayed for me with the desired results as expected.

However, if I do build this [Code Block 2] in my .NET application and execute it to get my dataset filled with the expected three tables, I receive the following error message:

Incorrect syntax near 'GO'.
There is already an object named '#TEMP_TABLE' in the database.
The variable name '@MY_VAR' has already been declared.

What's wrong here? Why the same code executes in SQL query tool but not through .NET application?

Carl Tawn

GO is a Management Studio only keyword. It isn't a standard SQL command so isn't recognised by the SQL Provider used when trying to execute a query from code.

alfardan

ASKER

Then what to do here?

Carl Tawn

For starters you need to take out the GO keywords. Although a query like you are trying to run you might find a bit of a struggle with inline SQL. Is there any change that you could convert it to a stored procedure and run that instead?

alfardan

ASKER

Yes there is a problem in having it as a procedure, the problem is that OPENQUERY() function does not accept params passed to it.

Check my previous question:

https://www.experts-exchange.com/questions/26834295/Issue-with-using-OpenQuery-that-contains-variables.html

KentMarsh

Here is a code example that uses SQL Strings that are executed within a transaction.

See if this approach works for you.

public string DoSql(int departmentID)
        {
            string result = string.Empty;

            string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);

            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();

            try
            {
                string sql = string.Empty;
                SqlCommand cmd;

                ExecuteSQL(conn, "DECLARE @MY_VAR VARCHAR(50) ");
                ExecuteSQL(conn, "IF OBJECT_ID('tempdb..#TEMP_TABLE') IS NOT NULL BEGIN DROP TABLE #TEMP_TABLE END ");
                ExecuteSQL(conn, "SELECT C.* INTO #TEMP_TABLE FROM OPENQUERY(MY_SERVER_NAME,'EXEC SOME_PROCEDURE_THAT_PRODUCES_A_TABLE ''ARGUMENT_VALUE1'', ''ARGUMENT_VALUE2''') ");
                ExecuteSQL(conn, "SELECT @MY_VAR=SOME_COL_NAME_IN_TEMP_TABLE FROM TEMP_TABLE ");

                cmd = new SqlCommand("SELECT 'FOUND RECORD: ' + @MY_VAR", conn);
                result = cmd.ExecuteScalar().ToString();
                ExecuteSQL(conn, "DROP TABLE #TEMP_TABLE ");
                
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();                
                throw new ApplicationException("Select Error", sqlEx);
            }
            finally
            {
                tran.Commit();
                conn.Close();
            }

            return result;
        }

        private int ExecuteSQL(SqlConnection conn, string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            return cmd.ExecuteNonQuery();
        }

Open in new window

alfardan

ASKER

This is only to execute the SQL statement once, the issue I'm facing is if I keep the connection open and use the same sql statement to be executed again but with having different [ARGUMENT_VALUE1] and [ARGUMENT_VALUE2] passed to the openquery function. Chedck my Code Block 2 in my origiinal question.

ASKER CERTIFIED SOLUTION

alfardan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alfardan

ASKER

No expert could provide the solution, so I had to search the net and found the noe that I posted ni my last response to this question, which did perfectly work for me as a solution to use SQL syntax itself instead of .NET