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(fl oat,myValu e))',@grpc ol='myName ',@pvtcol= 'myDescrip tion',@fro m='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(fl oat,myValu e))',@grpc ol='myName ',@pvtcol= 'myDescrip tion',@fro m='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.....
AAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AAAAAAAAAA AHHHHHHHHH HHHHHHHHHH !!!!
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.
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(fl
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(fl
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)
yyyy Sum(convert(float,myValue)
fgfgfgfg Sum(convert(float,myValue)
Totals Sum(convert(float,myValue)
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.....
AAAAAAAAAAAAAAAAAAAAAAAAAA
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.
ASKER
whats going on? Did I drop a stinky or something? how come no one is answering this question!! ARGHH!!
Post the code that executes this SP including the connection string. You might want to replace any login info from connection string by XXXX.
ASKER
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.Debugg erStepThro ugh()> Private Sub InitializeComponent()
Me.SqlConTMT = New System.Data.SqlClient.SqlC onnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlD ataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlC ommand
'
'SqlConTMT
'
Me.SqlConTMT.ConnectionStr ing = "workstation id=XXXXX;packet size=4096;user id=XXXXXXXXXXXXX;data source=XXXXXXXX;per" & _
"sist security info=False;initial catalog=YYYYYYY"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectC ommand = Me.SqlSelectCommand1
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.Comma ndText = "[BuildSummaryView]"
Me.SqlSelectCommand1.Comma ndType = System.Data.CommandType.St oredProced ure
Me.SqlSelectCommand1.Param eters.Add( New System.Data.SqlClient.SqlP arameter(" @RETURN_VA LUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirec tion.Retur nValue, 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.SqlC onnection
Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlD ataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlC ommand
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclara tion 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
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.Debugg
Me.SqlConTMT = New System.Data.SqlClient.SqlC
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlD
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlC
'
'SqlConTMT
'
Me.SqlConTMT.ConnectionStr
"sist security info=False;initial catalog=YYYYYYY"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectC
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.Comma
Me.SqlSelectCommand1.Comma
Me.SqlSelectCommand1.Param
End Sub
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.
Protected WithEvents SqlConTMT As System.Data.SqlClient.SqlC
Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlD
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlC
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclara
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER