Link to home
Start Free TrialLog in
Avatar of thescourge
thescourge

asked on

SQL Data Adapter, Stored Procedure, Invalid Object Error

OK,

I have seen a bunch of posts that look similar and some that were, but no answers that definitively solve this for me.

Simple situation

Trying to configure a SQLDataAdapter to work with an MSDE/SQL Server Connection, following steps in Configure Wizard until it finally says:

Generated SELECT Statement.
Invalid object name '#trans'.

I am setting the Select statement to run an existing stored procedure in the wizard, called BuildSummaryView that looks like this:

CREATE PROCEDURE BuildSummaryView AS execute rac @transform='Sum(convert(float,myValue))',@grpcol='myName',@pvtcol='myDescription',@from='vwPData'


This query is designed to exectute RAC which is some kind of thing I bought that basically is a bunch of stored procedures (encrypted) that build cross-tab queries, given the above inputs. the view vwPData looks like this:

CREATE PROCEDURE BuildSummaryView AS execute rac @transform='Sum(convert(float,myValue))',@grpcol='myName',@pvtcol='myDescription',@from='vwPData'

The bottom line is this:

When i execute the above stored procedure from query analyzer it works! I do get an answer, the answer looks something like this:

myName          Funct                       Totals  Customer contacts  Outstanding SDR's  Remaining deliverables  Test cases created WBS elements assigned WBS elements completed WBS elements completed on time WBS elements planned to be completed
--------------- --------------------------- ------ ------------------ ------------------ ----------------------- ------------------- --------------------- ---------------------- ------------------------------ ------------------------------------
XXXXX         Sum(convert(float,myValue)) 2514   4                                     2500                                        4                     2                      1                              3
yyyy         Sum(convert(float,myValue)) 57     12                 9                                                              9                     9                      9                              9
fgfgfgfg     Sum(convert(float,myValue)) 21     2                                                             3                   5                     4                      2                              5
Totals          Sum(convert(float,myValue)) 2592   18                 9                  2500                    3                   18                    15                     12                             17



Except of course it is all nicely formatted etc.

So I know that RAC works from within that environment and that the stored procedure is working, so i figure the error is some kind of permission thing? but I think i have turned on every available permission in the database for this connection, login, database, user, etc, etc, but i still keep getting invalid object "#trans"

Of course in addition i have know idea what table trans is or what is creating it, but i am assuming that it is a temporary table created by RAC which i cannot see because the T-SQL is encrypted.

All i want to do is to take the above output from BuildSummaryView and bind it to a datagrid for displaying the data! but i can't configure the SQLDataAdapter.....


AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHH!!!!

i appreciate your help.

muchisimos gracias mis amigos y amigas.

--ts

p.s. will be gone for lunch today from 12--1 central time so i will respond quickly after that time. thanks again.
Avatar of thescourge
thescourge

ASKER

HMMM... I know one of you dudes knows the answer to this. Share please!!
whats going on? Did I drop a stinky or something? how come no one is answering this question!! ARGHH!!
Avatar of amit_g
Post the code that executes this SP including the connection string. You might want to replace any login info from connection string by XXXX.
Public Class TopRoll
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlConTMT = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        '
        'SqlConTMT
        '
        Me.SqlConTMT.ConnectionString = "workstation id=XXXXX;packet size=4096;user id=XXXXXXXXXXXXX;data source=XXXXXXXX;per" & _
        "sist security info=False;initial catalog=YYYYYYY"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "[BuildSummaryView]"
        Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
        Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

    End Sub
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents SqlConTMT As System.Data.SqlClient.SqlConnection
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

End Class
There is no Connection.Open statment. If this complete code?

In the connection string are you using the same user and password that you use from SQL Query Analyser? If not, do that.
ASKER CERTIFIED SOLUTION
Avatar of rehand
rehand

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for your input rehand. it was most helpful. here is the second part of your points. you earned it!

for those of you wondering why rehand got points for just saying hi see this link:

https://www.experts-exchange.com/questions/20726878/SQL-SERVER-NET-WORTH-1000-POINTS.html

thanks again

ts