Solved

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

Posted on 2011-03-09
8
340 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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