Solved

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

Posted on 2011-03-09
8
345 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
[X]
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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

707 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