Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-09
8
343 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:alfardan
  • 5
  • 2
8 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35080600
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.
0
 

Author Comment

by:alfardan
ID: 35080721
Then what to do here?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35080819
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?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:alfardan
ID: 35081223
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:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26834295.html
0
 
LVL 2

Expert Comment

by:KentMarsh
ID: 35100485
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

0
 

Author Comment

by:alfardan
ID: 35117216
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.
0
 

Accepted Solution

by:
alfardan earned 0 total points
ID: 35146220
0
 

Author Closing Comment

by:alfardan
ID: 35178725
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
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question