Solved

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

Posted on 2011-03-09
8
339 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Name space syntax error 12 39
Runtime Error 2 28
xpath sql query 2008 8 42
ORA-00923: FROM keyword not found where expected 3 23
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now