Invalid object name "MyTable" error for a StoredProcedure OleDbCommand

What doesInvalid object name "MyTable" mean?

I checked the table and column names. They are both correct.

Will the error mean something else, e.g. wrong data type, or no data?

TIA,
Jeffrey

Dim strConnAsString = ConfigurationManager.ConnectionStrings("MyConnectString").ConnectionString
Dim oConnAsNew OleDbConnection(strConn)
Dim oDBCommandAsNew OleDbCommand("MyStoredProceduret", oConn)
oDBCommand.CommandType = CommandType.StoredProcedure
oDBCommand.Connection.Open()
Dim rtnValueAsString = oDBCommand.ExecuteScalar() This is the error source file

This is the SP
CREATE PROCEDURE MyStoredProcedure AS
SELECT SettingsReqSchdTimeout FROM Settings
GO

Your query may be querying the wrong table or schema.

Try this

Dim oDBCommandAsNew OleDbCommand("[DatabaseName].[DatabaseSchemaName].MyStoredProceduret", oConn)

replace the value within []

|||

Do a search in your project for the wordMyTable, you must have a referencesomewhere in your code.

|||

You have made a spelling mistake, that could be the error, the below stored proc name has a T on the end, but your SQL doesn't.

Dim oDBCommandAsNew OleDbCommand("MyStoredProceduret", oConn)

CREATE PROCEDUREMyStoredProcedure AS
SELECT SettingsReqSchdTimeout FROM Settings
GO

|||

Thanks. What is [DatabaseSchemaName]? the dbo name?

I tried the "DatabaseName.MyStoredProcedure". The error msg is: wrong stored procedure name.

Any more suggestions? Thanks?

|||

was the name of your stored procedure just a typo in your post? it has at on the end of it. This will cause problems... won't it?

Dim oDBCommandAsNew OleDbCommand("MyStoredProceduret", oConn)

|||

'MyStoredProcedure" is not the real sp name. I just typed it to indicate a sp name.

I have checked that the real sp name is correct. Thanks.

|||

I think the problem is the database owner name. How do I cnage the database name

back to dbo? Just can not remember it.

TIA,
Jeffrey

|||

check out books online for sp_changeobjectowner

|||

I have verified that the error was due to the owner of the table is not dbo,
by creating another table with the owner as dbo.

However, I still don't know how to use sp_changeobjectowner to change
the table owner to dbo. I even tried sp_changetableowner, but didn't work.

TIA,
Jeffrey

Into cursor problem

My code in SP
DECLARE @.sql nvarchar(4000)
SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from #myTable '
print @.sql
EXEC @.sql
I got the error Invalid object name '#temptable' , Does anyone know the
reason ? thanks a lotUnless #temptable is declared within the container SP, it only exists within
the scope of the dynamic sql and will be destroyed when it completes:
DECLARE @.sql nvarchar(4000)
CREATE TABLE #temptable ...
SELECT @.sql = 'INSERT #tempTable SELECT ... from #myTable'
print @.sql
EXEC @.sql
Mr Tea
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23HgFUx7GFHA.3352@.TK2MSFTNGP10.phx.gbl...
> My code in SP
> DECLARE @.sql nvarchar(4000)
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from #myTable
> '
> print @.sql
> EXEC @.sql
> I got the error Invalid object name '#temptable' , Does anyone know the
> reason ? thanks a lot
>|||Try this
SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
#myTable '
print @.sql
EXEC (''+@.sql +'')
Madhivanan|||> Try this
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
> #myTable '
> print @.sql
> EXEC (''+@.sql +'')
?
Does that will anyhow solve the problem?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109396430.717724.313510@.z14g2000cwz.googlegroups.com...
> Try this
> SELECT @.sql = 'SELECT accttype,cocode,coname INTO #tempTable from
> #myTable '
> print @.sql
> EXEC (''+@.sql +'')
> Madhivanan
>|||Thomas
I tested that in the query analyser. The text displayed is (9 row(s)
affected). But actually the table was not created. Can you tell me
where the prblem is?
Madhivanan|||Madhivanan,

> I tested that in the query analyser
Tested what? The code you posted?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109401722.488986.74000@.o13g2000cwo.googlegroups.com...
> Thomas
> I tested that in the query analyser. The text displayed is (9 row(s)
> affected). But actually the table was not created. Can you tell me
> where the prblem is?
> Madhivanan
>|||Yes thomas
Madhivanan|||The code you posted will not work on its own.
(It was even missing the declare statement )
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109402883.603761.286740@.f14g2000cwb.googlegroups.com...
> Yes thomas
> Madhivanan
>|||Thomas,
Run this and see the result
create table #temp (no int, name varchar(100))
insert into #temp values(1,'Name1')
insert into #temp values(2,'Name2')
insert into #temp values(3,'Name3')
insert into #temp values(4,'Name4')
insert into #temp values(5,'Name5')
Declare @.sql varchar(100)
SELECT @.sql = 'SELECT no,name INTO #tempTable from #temp'
print @.sql
EXEC (''+@.sql +'')
drop table #temp
Actually it is running without any error. But the table #tempTable is
not created.
Can you find out the reason?
Madhivanan|||Madhivanan,
The #tempTable is not visible because EXEC() has its own scope.
The following are the limitations of EXEC
a.. Within the SQL batch you cannot access local variables or parameters
of the calling stored procedure.
b.. Any USE statement will not affect the calling stored procedure.
c.. Temp tables created in the SQL batch will not be available to the
calling procedure since they are dropped when the block exits - just like
when you exit a stored procedure. The batch can however access tables
created in the calling procedure.
d.. If you issue a SET command in the dynamic SQL batch, the effect of the
SET command lasts for the duration of the dynamic SQL batch only.
e.. The query plan for the batch is not part of the plan for the calling
procedure. Cachewise the query is just as good as a bare SQL statement sent
from the client.
f.. If the batch results in an condition that leads to abortion of the
batch, for instance rollback in a trigger, not only the batch of dynamic SQL
is terminated, but also the calling procedure (and its caller and so on).
Its taken from this excellent article by Erland Sommarskog.
http://www.sommarskog.se/dynamic_sql.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109413962.850048.149990@.z14g2000cwz.googlegroups.com...
> Thomas,
> Run this and see the result
>
> create table #temp (no int, name varchar(100))
> insert into #temp values(1,'Name1')
> insert into #temp values(2,'Name2')
> insert into #temp values(3,'Name3')
> insert into #temp values(4,'Name4')
> insert into #temp values(5,'Name5')
> Declare @.sql varchar(100)
> SELECT @.sql = 'SELECT no,name INTO #tempTable from #temp'
> print @.sql
> EXEC (''+@.sql +'')
> drop table #temp
> Actually it is running without any error. But the table #tempTable is
> not created.
> Can you find out the reason?
> Madhivanan
>

Interpreting Statistics IO

This is my orignal statistics io return:
Table 'MyTable'. Scan count 1, logical reads 4338, physical reads 0, read-
ahead reads 0.
This is my revised statistics io return:
Table 'MyTable'. Scan count 117, logical reads 536, physical reads 0, read-
ahead reads 0.
Do I view this as an improvement since the logical reads reduced? Profiler
shows a large reduction in duration and reads when comparing the original to
the revised. The Scan count grew substantially, yet the logical reads droppe
d
substantially. Based upon Profiler my assumption is that the logical reads
reduction is the best indicator. True?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> Based upon Profiler my assumption is that the logical reads
> reduction is the best indicator. True?
Yes. Logical reads is as close as you can get to seeing how much SQL Server
had to actualy hit the hard drive. The only reason "Physical Reads" are zero
is because the data your query needed is already in the SQL Server data
cache.
You could run
DBCC DROPCLEANBUFFERS
to empty the cache, forcing SQL Server to actually hit the hard drive, and
you'd see how slow your query really could take.
But hard drive access is everything.|||Compare to a query that i wish i could make faster:
Table 'Worktable'. Scan count 155, logical reads 16039, physical reads 0,
read-ahead reads 0.
Table 'Patrons'. Scan count 4, logical reads 12, physical reads 0,
read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 114968, physical reads
0, read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 194430, physical reads
0, read-ahead reads 0.
Table 'Windows'. Scan count 1566, logical reads 3132, physical reads 0,
read-ahead reads 0.
Table 'Sessions'. Scan count 1566, logical reads 9396, physical reads 0,
read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 39312, physical
reads 0, read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 26412, physical
reads 0, read-ahead reads 0.
Table 'LCTs'. Scan count 4, logical reads 40, physical reads 0, read-ahead
reads 0.
Table 'LCTs'. Scan count 4, logical reads 372, physical reads 0, read-ahead
reads 92.
Table 'Worktable'. Scan count 135, logical reads 14779, physical reads 0,
read-ahead reads 0.|||Thanks Ian.
For further clarification on the relation to Reads shown in Profiler and the
logical and physical reads listed for the same procedure in Statistics IO, i
t
appears the Reads shown in Profiler are greater than the sum of the logical
and physical reads produced for the same procedure in Statistics IO. Is
Profiler showing reads against System tables whereas Statistics IO does not?
Ian Boyd wrote:
>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>had to actualy hit the hard drive. The only reason "Physical Reads" are zer
o
>is because the data your query needed is already in the SQL Server data
>cache.
>You could run
>DBCC DROPCLEANBUFFERS
>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>you'd see how slow your query really could take.
>But hard drive access is everything.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||> Is
> Profiler showing reads against System tables whereas Statistics IO does not?[/vbco
l]
Yes. Also, STATISTICS IO doesn't show I/O performed by scalar UDFs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:616e5ea2a1085@.uwe...[vbcol
=seagreen]
> Thanks Ian.
> For further clarification on the relation to Reads shown in Profiler and t
he
> logical and physical reads listed for the same procedure in Statistics IO,
it
> appears the Reads shown in Profiler are greater than the sum of the logica
l
> and physical reads produced for the same procedure in Statistics IO. Is
> Profiler showing reads against System tables whereas Statistics IO does no
t?
> Ian Boyd wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1

Interpreting Statistics IO

This is my orignal statistics io return:
Table 'MyTable'. Scan count 1, logical reads 4338, physical reads 0, read-
ahead reads 0.
This is my revised statistics io return:
Table 'MyTable'. Scan count 117, logical reads 536, physical reads 0, read-
ahead reads 0.
Do I view this as an improvement since the logical reads reduced? Profiler
shows a large reduction in duration and reads when comparing the original to
the revised. The Scan count grew substantially, yet the logical reads dropped
substantially. Based upon Profiler my assumption is that the logical reads
reduction is the best indicator. True?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> Based upon Profiler my assumption is that the logical reads
> reduction is the best indicator. True?
Yes. Logical reads is as close as you can get to seeing how much SQL Server
had to actualy hit the hard drive. The only reason "Physical Reads" are zero
is because the data your query needed is already in the SQL Server data
cache.
You could run
DBCC DROPCLEANBUFFERS
to empty the cache, forcing SQL Server to actually hit the hard drive, and
you'd see how slow your query really could take.
But hard drive access is everything.|||Compare to a query that i wish i could make faster:
Table 'Worktable'. Scan count 155, logical reads 16039, physical reads 0,
read-ahead reads 0.
Table 'Patrons'. Scan count 4, logical reads 12, physical reads 0,
read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 114968, physical reads
0, read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 194430, physical reads
0, read-ahead reads 0.
Table 'Windows'. Scan count 1566, logical reads 3132, physical reads 0,
read-ahead reads 0.
Table 'Sessions'. Scan count 1566, logical reads 9396, physical reads 0,
read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 39312, physical
reads 0, read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 26412, physical
reads 0, read-ahead reads 0.
Table 'LCTs'. Scan count 4, logical reads 40, physical reads 0, read-ahead
reads 0.
Table 'LCTs'. Scan count 4, logical reads 372, physical reads 0, read-ahead
reads 92.
Table 'Worktable'. Scan count 135, logical reads 14779, physical reads 0,
read-ahead reads 0.|||Thanks Ian.
For further clarification on the relation to Reads shown in Profiler and the
logical and physical reads listed for the same procedure in Statistics IO, it
appears the Reads shown in Profiler are greater than the sum of the logical
and physical reads produced for the same procedure in Statistics IO. Is
Profiler showing reads against System tables whereas Statistics IO does not?
Ian Boyd wrote:
>> Based upon Profiler my assumption is that the logical reads
>> reduction is the best indicator. True?
>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>had to actualy hit the hard drive. The only reason "Physical Reads" are zero
>is because the data your query needed is already in the SQL Server data
>cache.
>You could run
>DBCC DROPCLEANBUFFERS
>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>you'd see how slow your query really could take.
>But hard drive access is everything.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||> Is
> Profiler showing reads against System tables whereas Statistics IO does not?
Yes. Also, STATISTICS IO doesn't show I/O performed by scalar UDFs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:616e5ea2a1085@.uwe...
> Thanks Ian.
> For further clarification on the relation to Reads shown in Profiler and the
> logical and physical reads listed for the same procedure in Statistics IO, it
> appears the Reads shown in Profiler are greater than the sum of the logical
> and physical reads produced for the same procedure in Statistics IO. Is
> Profiler showing reads against System tables whereas Statistics IO does not?
> Ian Boyd wrote:
>> Based upon Profiler my assumption is that the logical reads
>> reduction is the best indicator. True?
>>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>>had to actualy hit the hard drive. The only reason "Physical Reads" are zero
>>is because the data your query needed is already in the SQL Server data
>>cache.
>>You could run
>>DBCC DROPCLEANBUFFERS
>>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>>you'd see how slow your query really could take.
>>But hard drive access is everything.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1