RE: SQL ''Invalid Object Name'' on Insert, after Create Table
I'd fix the query first and see if it helps ;-)
INSERT INTO #getuser.owner#.djbtest ('id')
VALUES ('sdfasdfafasdfasfd')
You have single quotes around the column name
-Original Message-
From: Derek Bezuidenhout [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 10:22 AM
To: CF-Talk
Subject: Re: SQL ''Invalid Object Name'' on Insert, after Create Table
Thanks for the suggestions so far. I'll try them now.
Here's the code:
IF object_id('aaa_bwdatadump..djbtest') IS NOT NULL
BEGIN
DROP TABLE djbtest
END
CREATE TABLE dbo.djbtest (
id varchar(200)
)
SELECT * from djbtest
SELECT name, xtype, uid AS [owner id], USER_NAME(uid) AS owner
FROM sysobjects
WHERE name LIKE '%djbtest%'
INSERT INTO #getuser.owner#.djbtest ('id')
VALUES ('sdfasdfafasdfasfd')
-
And the error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name
'dbo.djbtest'.
The error occurred in D:\www\cfmx\scratch\bwconvertdata\test.cfm: line 30
28 :
29 :
30 : INSERT INTO #getuser.owner#.djbtest ('id')
31 :VALUES ('sdfasdfafasdfasfd')
32 :
SQLINSERT INTO dbo.djbtest ('id') VALUES ('sdfasdfafasdfasfd')
DATASOURCE aaa_bwdatadump
VENDORERRORCODE 208
SQLSTATE 42S02
---
If it makes any diff, it's on CFMX 7,0,2,137072 and MS-SQL2000 (8.00.2039
(SP4)), although I've got the same results on CF5 and MS-SQL SP3.
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255181
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL 'Invalid Object Name' on Insert, after Create Table
When you modify the schema, are you performing this from a cfquery block? If you are, put the schema modification in a stored procedure for purposes of security, speed and abstraction. Teddy On 10/3/06, Bobby Hartsfield <[EMAIL PROTECTED]> wrote: > > I've seen that happen on a database that was moved from one server to > another where user names/passwords were different in the Enterprise > Manager/CF Admin. > > Check those first... should be pretty easy to spot. Open the enterprise > manager and view the tables in that particular database scan down the > owner column and look to see if any stick out with a different name. The > chances are good that your new table will have a different owner than the > rest. > > -Original Message- > From: Derek Bezuidenhout [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 03, 2006 9:50 AM > To: CF-Talk > Subject: SQL 'Invalid Object Name' on Insert, after Create Table > > Hey folks, > I'm having trouble with inserting into a table I've just created. > > As a summary of what I'm doing: > - if the table exists, delete it > - create the table > - read from the table to prove it's there > - insert into the table > *splat* : "Invalid object name..." > > I can post the full code and error if you want. > > All the other forum posts around the web say things like: > - check the user permissions > - the user in the datasource has db_owner, and for good measure > db_datawriter, db_datareader. > - reference the table in the INSERT with the owner (ie: dbo.tablename) > - I've gone so far as to look up the owner in sysobjects > - if using JDBC driver then set selectMode=cursor > - done > > Thought it might be related to http://support.microsoft.com/kb/837970/ so > I'm now running this on a different new fresh SQL server, with SP4 > installed. > > The same error occurs on CF5 and CFMX7. > > Even when I try the Insert without the Create Table it fails. (ie: run > once > so table is created, then rem out the Create Table bit and run it again.) > > All suggestions welcome... > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL ''''Invalid Object Name'''' on Insert, after Create Table
It had to happen.
For some reason I had 'quotes' around the col-name in the Insert. D'oh!
Sorry to waste space...
>
> INSERT INTO #getuser.owner#.djbtest ('id')
> VALUES ('sdfasdfafasdfasfd')
>
>
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255175
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL ''Invalid Object Name'' on Insert, after Create Table
Thanks for the suggestions so far. I'll try them now.
Here's the code:
IF object_id('aaa_bwdatadump..djbtest') IS NOT NULL
BEGIN
DROP TABLE djbtest
END
CREATE TABLE dbo.djbtest (
id varchar(200)
)
SELECT * from djbtest
SELECT name, xtype, uid AS [owner id], USER_NAME(uid) AS owner
FROM sysobjects
WHERE name LIKE '%djbtest%'
INSERT INTO #getuser.owner#.djbtest ('id')
VALUES ('sdfasdfafasdfasfd')
-
And the error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name
'dbo.djbtest'.
The error occurred in D:\www\cfmx\scratch\bwconvertdata\test.cfm: line 30
28 :
29 :
30 : INSERT INTO #getuser.owner#.djbtest ('id')
31 :VALUES ('sdfasdfafasdfasfd')
32 :
SQLINSERT INTO dbo.djbtest ('id') VALUES ('sdfasdfafasdfasfd')
DATASOURCE aaa_bwdatadump
VENDORERRORCODE 208
SQLSTATE 42S02
---
If it makes any diff, it's on CFMX 7,0,2,137072 and MS-SQL2000 (8.00.2039
(SP4)), although I've got the same results on CF5 and MS-SQL SP3.
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255164
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL 'Invalid Object Name' on Insert, after Create Table
I've seen that happen on a database that was moved from one server to another where user names/passwords were different in the Enterprise Manager/CF Admin. Check those first... should be pretty easy to spot. Open the enterprise manager and view the tables in that particular database scan down the owner column and look to see if any stick out with a different name. The chances are good that your new table will have a different owner than the rest. -Original Message- From: Derek Bezuidenhout [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:50 AM To: CF-Talk Subject: SQL 'Invalid Object Name' on Insert, after Create Table Hey folks, I'm having trouble with inserting into a table I've just created. As a summary of what I'm doing: - if the table exists, delete it - create the table - read from the table to prove it's there - insert into the table *splat* : "Invalid object name..." I can post the full code and error if you want. All the other forum posts around the web say things like: - check the user permissions - the user in the datasource has db_owner, and for good measure db_datawriter, db_datareader. - reference the table in the INSERT with the owner (ie: dbo.tablename) - I've gone so far as to look up the owner in sysobjects - if using JDBC driver then set selectMode=cursor - done Thought it might be related to http://support.microsoft.com/kb/837970/ so I'm now running this on a different new fresh SQL server, with SP4 installed. The same error occurs on CF5 and CFMX7. Even when I try the Insert without the Create Table it fails. (ie: run once so table is created, then rem out the Create Table bit and run it again.) All suggestions welcome... ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255161 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL 'Invalid Object Name' on Insert, after Create Table
Don't know if it matters, but are you opening a transaction before you create the table, and trying to insert while that transaction is still open? Is this MS SQL Server? If so, is there a "Go" command after the table creation? Just random thoughts. ~Brad -Original Message- From: Derek Bezuidenhout [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 8:50 AM To: CF-Talk Subject: SQL 'Invalid Object Name' on Insert, after Create Table Hey folks, I'm having trouble with inserting into a table I've just created. As a summary of what I'm doing: - if the table exists, delete it - create the table - read from the table to prove it's there - insert into the table *splat* : "Invalid object name..." I can post the full code and error if you want. All the other forum posts around the web say things like: - check the user permissions - the user in the datasource has db_owner, and for good measure db_datawriter, db_datareader. - reference the table in the INSERT with the owner (ie: dbo.tablename) - I've gone so far as to look up the owner in sysobjects - if using JDBC driver then set selectMode=cursor - done Thought it might be related to http://support.microsoft.com/kb/837970/ so I'm now running this on a different new fresh SQL server, with SP4 installed. The same error occurs on CF5 and CFMX7. Even when I try the Insert without the Create Table it fails. (ie: run once so table is created, then rem out the Create Table bit and run it again.) All suggestions welcome... ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255159 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL 'Invalid Object Name' on Insert, after Create Table
Can you create a physical table and insert into it in the same batch? I'm not sure you can. Show us the code. Adrian -Original Message- From: Derek Bezuidenhout [mailto:[EMAIL PROTECTED] Sent: 03 October 2006 14:50 To: CF-Talk Subject: SQL 'Invalid Object Name' on Insert, after Create Table Hey folks, I'm having trouble with inserting into a table I've just created. As a summary of what I'm doing: - if the table exists, delete it - create the table - read from the table to prove it's there - insert into the table *splat* : "Invalid object name..." I can post the full code and error if you want. All the other forum posts around the web say things like: - check the user permissions - the user in the datasource has db_owner, and for good measure db_datawriter, db_datareader. - reference the table in the INSERT with the owner (ie: dbo.tablename) - I've gone so far as to look up the owner in sysobjects - if using JDBC driver then set selectMode=cursor - done Thought it might be related to http://support.microsoft.com/kb/837970/ so I'm now running this on a different new fresh SQL server, with SP4 installed. The same error occurs on CF5 and CFMX7. Even when I try the Insert without the Create Table it fails. (ie: run once so table is created, then rem out the Create Table bit and run it again.) All suggestions welcome... ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255158 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
