October 13, 2002 at 4:11 am
quote:
Try SELECT COUNT(*) FROM TABLEB and SELECT COUNT(*) FROM TABLEA and see if either gives you an invalid object return, that should narrow which it is."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
ISSUE 1 - ANTARES686: Your suggestion is getting close to solving the issue. It does appear to be a path issue as I stated in past responses. When I highlight the "specific" table and access the QA for that specific table, and run the Select Count(*), it does count the columns. This procedure works "positively" for either table, as long as I highlight the specific table and run the QA from that specific table. In order for me to receive the column count, I must be in that specific table. In addition, if I run the INSERT INTO statement for the specific highlighted table, only the table that I am now working-in with the QA returns no message error, and the table I am NOT working-in pertaining to the QA returns the "invalid object name." That leads me down the "path issue." If I open the QA under databases, then I receive an "invalid object name" for both tables pertaining to the INSERT INTO statement. Hopefully, I have made the following statements clear. If anything is not clear, then I will further define my interpretation.
ISSUE 2: EL.C: The bracket [] issue did not resolve the problem.
ISSUE 3: ALLEN_CUI: I do not see where deleting or renaming a table will resolve anything, unless I have a corrupted table. All test indicate the table is not corrupted. Further, I cannot delete or rename the tables at this stage, since that would create more problems in the future. I would use this only as a last resort when all other avenues have failed, and I would probably just seek other alternatives.
Roy A. Day (royaday@hotmail.com)
October 13, 2002 at 4:16 am
quote:
Try either delete or rename the target table and run select * into targettable from originaltable.
ISSUE 1 - ANTARES686: Your suggestion is getting close to solving the issue. It does appear to be a path issue as I stated in past responses. When I highlight the "specific" table and access the QA for that specific table, and run the Select Count(*), it does count the columns. This procedure works "positively" for either table, as long as I highlight the specific table and run the QA from that specific table. In order for me to receive the column count, I must be in that specific table. In addition, if I run the INSERT INTO statement for the specific highlighted table, only the table that I am now working-in with the QA returns no message error, and the table I am NOT working-in pertaining to the QA returns the "invalid object name." That leads me down the "path issue." If I open the QA under databases, then I receive an "invalid object name" for both tables pertaining to the INSERT INTO statement. Hopefully, I have made the following statements clear. If anything is not clear, then I will further define my interpretation.
ISSUE 2: EL.C: The bracket [] issue did not resolve the problem.
ISSUE 3: ALLEN_CUI: I do not see where deleting or renaming a table will resolve anything, unless I have a corrupted table. All test indicate the table is not corrupted. Further, I cannot delete or rename the tables at this stage, since that would create more problems in the future. I would use this only as a last resort when all other avenues have failed, and I would probably just seek other alternatives.
Roy A. Day (royaday@hotmail.com)
October 13, 2002 at 4:29 am
quote:
HiTry to put brackets [ and ] around your table name. If the name contains spaces or is a special word, this error may occur.
INSERT INTO [dstTable] (col1, col2, ...) SELECT col1, col2, ... FROM [dstTable]...
Best regards
el.c.
myLittleTools.net :: web-based applications
ISSUE 1 - ANTARES686: Your suggestion is getting close to solving the issue. It does appear to be a path issue as I stated in past responses. When I highlight the "specific" table and access the QA for that specific table, and run the Select Count(*), it does count the columns. This procedure works "positively" for either table, as long as I highlight the specific table and run the QA from that specific table. In order for me to receive the column count, I must be in that specific table. In addition, if I run the INSERT INTO statement for the specific highlighted table, only the table that I am now working-in with the QA returns no message error, and the table I am NOT working-in pertaining to the QA returns the "invalid object name." That leads me down the "path issue." If I open the QA under databases, then I receive an "invalid object name" for both tables pertaining to the INSERT INTO statement. Hopefully, I have made the following statements clear. If anything is not clear, then I will further define my interpretation.
ISSUE 2: EL.C: The bracket [] issue did not resolve the problem.
ISSUE 3: ALLEN_CUI: I do not see where deleting or renaming a table will resolve anything, unless I have a corrupted table. All test indicate the table is not corrupted. Further, I cannot delete or rename the tables at this stage, since that would create more problems in the future. I would use this only as a last resort when all other avenues have failed, and I would probably just seek other alternatives.
Roy A. Day (royaday@hotmail.com)
October 13, 2002 at 7:14 am
Are you sure you are in the correct database.
If you run Query analyser whilst the databases node is selected in QA then the database you will be connected to will be your default database (master is default). If you want to run it in master then you have to qualify the table with the databasename i.e MyDB.dbo.MyTable
Or change the database to your database in the combo box on the toolbar
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 13, 2002 at 8:26 am
quote:
Are you sure you are in the correct database.If you run Query analyser whilst the databases node is selected in QA then the database you will be connected to will be your default database (master is default). If you want to run it in master then you have to qualify the table with the databasename i.e MyDB.dbo.MyTable
Or change the database to your database in the combo box on the toolbar
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
ISSUE 4: simonsabin: I am running the QA in my database file and not the master database file. However, your suggestion of MyDB.dbo.MyTable excited some neurons in my cerebrum pertaining to SQL "nomenclature," as associated with the SQL "path," and I have now resolved the problem. It was a SQL "nomenclature" and SQL "path" problem.
Thank you, and all the sqlservercentrial.com individuals, including but not limited to, antares686, who sought to assist me on solving this problem.
Once again, thank you all. YOU MADE MY DAY!
WE HAVE LIFT-OFF; ALL SYSTEMS ARE GO!
Thank you ALL!
Roy A. Day (royaday@hotmail.com)
October 13, 2002 at 8:38 am
CASE CLOSED. THANK YOU.
Roy A. Day (royaday@hotmail.com)
October 14, 2002 at 9:40 am
Just for kicks, in Query Analyzer, navigate to the database where these tables reside, and execute the following script, but before doing so, replace the text between the single quotes of the two SET statements with the name of your two tables (i.e. the table you're inserting into, and the table you're inserting from):
---
DECLARE @object1 SYSNAME
DECLARE @object2 SYSNAME
SET @object1 = 'Insert name of first table'
SET @object2 = 'Insert name of second table'
SET NOCOUNT ON
SELECT USER_NAME([UID]) + ' owns ' + @object1
FROM SYSOBJECTS
WHERE [NAME] = @object1
UNION
SELECT USER_NAME([UID]) + ' owns ' + @object2
FROM SYSOBJECTS
WHERE [NAME] = @object2
SET NOCOUNT OFF
---
What are your results?
Matthew Burr
Edited by - mdburr on 10/14/2002 09:44:59 AM
Edited by - mdburr on 10/14/2002 09:45:38 AM
October 14, 2002 at 9:43 am
Nevermind then. 🙂 Glad you've resolved it.
Matthew Burr
Edited by - mdburr on 10/14/2002 09:46:42 AM
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply