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

Invalid identifier/column names after Upsize Access data to SQL Se

I have been asked to maintain an Access 2000 database which is a code front
end linked by ODBC to a SQL Server 2005 back end. (Originally the data was
in an Access database but was later moved to SQL Server 2005 by the Upsizing
tools.)
Some tables have column names which do not conform to the rules given in SQL
Books Online - there are field names with embedded spaces, first character
not a-z or A-Z, name contains '/' and two names which are T-SQL reserved
words. When the tables are viewed via Management Studio, these 'odd' names
are enclosed in square brackets.
The production system is working in this state, but when I try to set up a
test environment on a stand-alone PC, I cannot access these tables. If I try
to link them I get 'ODBC -- Call failed', and if I try to import them I get
error messages about various column names. I can only link to, or import
other tables which do not have these 'odd' field names. If I rename the
columns in SQL server then I can import or link them with no problem.
(My PC has the latest service packs for SQL Server 2005 and Office 2003, and
MDAC 2.8 SP1.)
Somehow the production system works with this data but my test PC doesn't.
What do I need to do on my test PC to use this data with the original 'odd'
column names?
How are you linking the tables? You can write DAO/VBA code to pass the
correctly-delimited table names.
Frankly, you'd be better off just changing the names to conform to
SQLS identifier rules. Having non-compliant names is just going to
cause more headaches down the road. Take the hit now before you've
deployed the app.
-mary
On Fri, 22 Jun 2007 07:54:04 -0700, didub
<didub@.discussions.microsoft.com> wrote:

>I have been asked to maintain an Access 2000 database which is a code front
>end linked by ODBC to a SQL Server 2005 back end. (Originally the data was
>in an Access database but was later moved to SQL Server 2005 by the Upsizing
>tools.)
>Some tables have column names which do not conform to the rules given in SQL
>Books Online - there are field names with embedded spaces, first character
>not a-z or A-Z, name contains '/' and two names which are T-SQL reserved
>words. When the tables are viewed via Management Studio, these 'odd' names
>are enclosed in square brackets.
>The production system is working in this state, but when I try to set up a
>test environment on a stand-alone PC, I cannot access these tables. If I try
>to link them I get 'ODBC -- Call failed', and if I try to import them I get
>error messages about various column names. I can only link to, or import
>other tables which do not have these 'odd' field names. If I rename the
>columns in SQL server then I can import or link them with no problem.
>(My PC has the latest service packs for SQL Server 2005 and Office 2003, and
>MDAC 2.8 SP1.)
>Somehow the production system works with this data but my test PC doesn't.
>What do I need to do on my test PC to use this data with the original 'odd'
>column names?
|||I agree that the column names should be changed to satisfy SQL naming rules,
and I plan to do that, but that is not my first priority.
This app. was created and deployed several years ago by someone who is no
longer with the company. On the live server and desktops it is working with
these invalid names.
My first objective was to install a copy of the system on my own PC so I can
take it apart and plan some major changes and bug fixes. So ideally I would
just like to get my test system working as the live system does.
My guess is that there are some ODBC related settings that I need to change.
Or perhaps there is something I need to do on the SQL Server database. I
have searched Knowledgebase and the forums but I haven't found anything
helpful yet.
"Mary Chipman [MSFT]" wrote:

> How are you linking the tables? You can write DAO/VBA code to pass the
> correctly-delimited table names.
> Frankly, you'd be better off just changing the names to conform to
> SQLS identifier rules. Having non-compliant names is just going to
> cause more headaches down the road. Take the hit now before you've
> deployed the app.
> -mary
> On Fri, 22 Jun 2007 07:54:04 -0700, didub
> <didub@.discussions.microsoft.com> wrote:
>
|||As I recommended earlier, write code so that you can specify the
delimiters. If you're using UI tools then you have no way of knowing
what the unspoken assumptions are - i.e., what is being passed to ODBC
and then the server. Another option is to put a Profiler trace on it
and see what the server is receiving from the client. It's hard to
tell from a distance what's actually going on under the covers with an
app, especially if someone else wrote it.
-mary
On Fri, 22 Jun 2007 15:09:01 -0700, didub
<didub@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I agree that the column names should be changed to satisfy SQL naming rules,
>and I plan to do that, but that is not my first priority.
>This app. was created and deployed several years ago by someone who is no
>longer with the company. On the live server and desktops it is working with
>these invalid names.
>My first objective was to install a copy of the system on my own PC so I can
>take it apart and plan some major changes and bug fixes. So ideally I would
>just like to get my test system working as the live system does.
>My guess is that there are some ODBC related settings that I need to change.
>Or perhaps there is something I need to do on the SQL Server database. I
>have searched Knowledgebase and the forums but I haven't found anything
>helpful yet.
>
>"Mary Chipman [MSFT]" wrote:

Invalid identifier/column names after Upsize Access data to SQL Se

I have been asked to maintain an Access 2000 database which is a code front
end linked by ODBC to a SQL Server 2005 back end. (Originally the data was
in an Access database but was later moved to SQL Server 2005 by the Upsizing
tools.)
Some tables have column names which do not conform to the rules given in SQL
Books Online - there are field names with embedded spaces, first character
not a-z or A-Z, name contains '/' and two names which are T-SQL reserved
words. When the tables are viewed via Management Studio, these 'odd' names
are enclosed in square brackets.
The production system is working in this state, but when I try to set up a
test environment on a stand-alone PC, I cannot access these tables. If I try
to link them I get 'ODBC -- Call failed', and if I try to import them I get
error messages about various column names. I can only link to, or import
other tables which do not have these 'odd' field names. If I rename the
columns in SQL server then I can import or link them with no problem.
(My PC has the latest service packs for SQL Server 2005 and Office 2003, and
MDAC 2.8 SP1.)
Somehow the production system works with this data but my test PC doesn't.
What do I need to do on my test PC to use this data with the original 'odd'
column names?How are you linking the tables? You can write DAO/VBA code to pass the
correctly-delimited table names.
Frankly, you'd be better off just changing the names to conform to
SQLS identifier rules. Having non-compliant names is just going to
cause more headaches down the road. Take the hit now before you've
deployed the app.
-mary
On Fri, 22 Jun 2007 07:54:04 -0700, didub
<didub@.discussions.microsoft.com> wrote:

>I have been asked to maintain an Access 2000 database which is a code front
>end linked by ODBC to a SQL Server 2005 back end. (Originally the data was
>in an Access database but was later moved to SQL Server 2005 by the Upsizin
g
>tools.)
>Some tables have column names which do not conform to the rules given in SQ
L
>Books Online - there are field names with embedded spaces, first character
>not a-z or A-Z, name contains '/' and two names which are T-SQL reserved
>words. When the tables are viewed via Management Studio, these 'odd' names
>are enclosed in square brackets.
>The production system is working in this state, but when I try to set up a
>test environment on a stand-alone PC, I cannot access these tables. If I tr
y
>to link them I get 'ODBC -- Call failed', and if I try to import them I get
>error messages about various column names. I can only link to, or import
>other tables which do not have these 'odd' field names. If I rename the
>columns in SQL server then I can import or link them with no problem.
>(My PC has the latest service packs for SQL Server 2005 and Office 2003, an
d
>MDAC 2.8 SP1.)
>Somehow the production system works with this data but my test PC doesn't.
>What do I need to do on my test PC to use this data with the original 'odd'
>column names?sql

Invalid Column Names?

Here is my code:
DECLARE curCAED1 CURSOR FAST_FORWARD FOR
SELECT * FROM okc_contr_license
WHERE CONTR_TYPE = 'ME'
ORDER BY CONTRACTOR_NUMBER
OPEN curCAED1
FETCH NEXT FROM curCAED1 INTO
@.pContrType, @.pContractorNumber,
@.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
@.pFirstName, @.pMiddleInitial, @.pLastName
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
SET CAE_FName = @.pFirstName
WHERE CONTR_TYPE = @.pContrType AND CONTRACTOR_NUMBER = @.pContractorNumber
FETCH NEXT FROM curCAED1 INTO
@.pContrType, @.pContractorNumber,
@.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
@.pFirstName, @.pMiddleInitial, @.pLastName
END
CLOSE curCAED1
DEALLOCATE curCAED1
Here is my error:
Server: Msg 207, Level 16, State 3, Line 134
Invalid column name 'CONTR_TYPE'.
Server: Msg 207, Level 16, State 1, Line 134
Invalid column name 'CONTRACTOR_NUMBER'.
What is going on? Shouldn't this work?
Thanks y'all!
DarrellI can't be too sure without the DDL, but I'll ask the question of why would
you do this update in a cursor? Also, bad idea to do SELECT * in a cursor.
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eQyhwdlWGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my code:
> DECLARE curCAED1 CURSOR FAST_FORWARD FOR
> SELECT * FROM okc_contr_license
> WHERE CONTR_TYPE = 'ME'
> ORDER BY CONTRACTOR_NUMBER
> OPEN curCAED1
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
> SET CAE_FName = @.pFirstName
> WHERE CONTR_TYPE = @.pContrType AND CONTRACTOR_NUMBER =
> @.pContractorNumber
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> END
> CLOSE curCAED1
> DEALLOCATE curCAED1
> Here is my error:
> Server: Msg 207, Level 16, State 3, Line 134
> Invalid column name 'CONTR_TYPE'.
> Server: Msg 207, Level 16, State 1, Line 134
> Invalid column name 'CONTRACTOR_NUMBER'.
> What is going on? Shouldn't this work?
> Thanks y'all!
> Darrell|||Those columns don't exist, as you've read the results of the select
query into (presumably) @.pContrType and @.pContractorNumber.
What is the WHERE clause trying to achieve?
*** Sent via Developersdex http://www.examnotes.net ***|||First. Found the problem. The fields don't exist in the table being updated
(as
noted by Anthony Brown). Doh! My mistake.
Second. There is a one-to-many relationship. One record in the table being
updated to many records in the cursor table.
Third. Normally I would agree with your SELECT * principal. However, there a
re
only 8 columns in the table I am declaring the cursor on. So, it is under co
ntrol.
Thanks for your help and insight.
David D Webb said the following on 04/07/2006 11:09 AM:
> I can't be too sure without the DDL, but I'll ask the question of why woul
d
> you do this update in a cursor? Also, bad idea to do SELECT * in a cursor
.
>
> "Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
> news:eQyhwdlWGHA.1220@.TK2MSFTNGP02.phx.gbl...
>|||> Second. There is a one-to-many relationship. One record in the table being
> updated to many records in the cursor table.
You are effectively making the relationship 1 to 1. The only update that
"counts" will the be last one for a given contractor_number. Unfortunately,
you have made it a somewhat random 1 to 1 relationship since the cursor is
not ordered by anything other than contractor_number. You could just have
easily used an aggregate (min, max - ly, there is no
chose_any_one_i_don't_care() aggregate function), performing the update much
more quickly and efficiently with a single statement.|||HAve you tried to run the update with some values to see if it works by
itself?
E.g.
begin tran
UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
SET CAE_FName = [some value]
WHERE CONTR_TYPE = [some value]
AND
CONTRACTOR_NUMBER = [some value]
rollback
It looks like the table PermitsSystemNew.dbo.CAED_CONVERSION does not have
those columns...
Let em kow how it goes...
"Darrell" wrote:

> Here is my code:
> DECLARE curCAED1 CURSOR FAST_FORWARD FOR
> SELECT * FROM okc_contr_license
> WHERE CONTR_TYPE = 'ME'
> ORDER BY CONTRACTOR_NUMBER
> OPEN curCAED1
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE PermitsSystemNew.dbo.CAED_CONVERSION --Error generated here--
> SET CAE_FName = @.pFirstName
> WHERE CONTR_TYPE = @.pContrType AND CONTRACTOR_NUMBER = @.pContractorNumb
er
> FETCH NEXT FROM curCAED1 INTO
> @.pContrType, @.pContractorNumber,
> @.pCityLicType, @.pCityLicNumber, @.pCityLicRenDate,
> @.pFirstName, @.pMiddleInitial, @.pLastName
> END
> CLOSE curCAED1
> DEALLOCATE curCAED1
> Here is my error:
> Server: Msg 207, Level 16, State 3, Line 134
> Invalid column name 'CONTR_TYPE'.
> Server: Msg 207, Level 16, State 1, Line 134
> Invalid column name 'CONTRACTOR_NUMBER'.
> What is going on? Shouldn't this work?
> Thanks y'all!
> Darrell
>