CODEWITHSUNDEEP
sqlsql-servervb.net
C.Corvax
C.Corvax

Reputation: 43

SQL statement runs in SQL Server- Not in Visual Studio

I'm running Visual Studio 2008 and have been told that I have all rights to create #temp tables. I have done this before, but for some reason my sql in the new application always returns 0 rows.

I have the SQL connection string stored in my Application events as 

Friend sqlConnProducts as String

I create a sql string and pass it to the SQLExecute Function

Private Function SQLExecute(ByVal sSQL As String) As DataTable
    Dim rtnTable As New DataTable()
    Dim sConn As New SqlConnection(My.Application.sqlConRecvInsp)
    Dim sCmd As New SqlCommand(sSQL, sConn)
    Dim taAdapter As New SqlDataAdapter(sCmd)

    Try
        sConn.Open()
        sCmd.CommandTimeout = 0
        taAdapter.Fill(rtnTable)
        sConn.Close()
    Catch ex As Exception
        MessageBox.Show("SQL Failed at: " & ex.Message & vbNewLine & ex.StackTrace)
    End Try

    sConn.Dispose()
    sCmd.Dispose()

    Return rtnTable
End Function

I even have a debug.print(sql) in the sql creation phase. When I run the output from the debug, I get about 7k records. Any suggestions?

Upvotes: 0

Views: 155

Answers (2)

Will Marcouiller
Will Marcouiller

Reputation: 24142

Here are some links that might light your path to your solution:

  1. SQL Team - Temporary Tables;
  2. Eliminate the Use of Temporary Tables For HUGE Performance Gains;
  3. Differences between SQL Server temporary tables and table variables;
  4. Should I use a #temp table or a @table variable?

Finally, I doubt a temporary table i suitable for your needs here.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Take a look at the following regarding SQL Server temp tables - it may provide some guidance as to what's going on.

http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

Share and enjoy.

Upvotes: 1

Related Questions