Create table creates invisible table

Create table creates invisible table

Post by Edward » Sat, 12 Jan 2002 01:39:38



I am trying to create a table on a database using the following
script:

if exists (select * from sysobjects where id =
object_id(N'[dbo].[tblResponsesBackup]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblResponsesBackup]
GO

CREATE TABLE [dbo].[tblResponsesBackup] (
        [fldResponseID]  [uniqueidentifier] NULL ,
        [fldCSID] [uniqueidentifier] NULL ,
        [fldDate] [smalldatetime] NULL ,
        [fldResponse] [ntext] NULL ,
        [fldAttachedFile] [nvarchar] (255) NULL ,
        [fldFileAttached] [smallint] NULL ,
        [fldDocID] [int] NULL ,
        [fldUserID] [uniqueidentifier] NULL ,
        [fldUser] [nvarchar] (3) NULL ,
        [fldLastUpdate] [smalldatetime] NULL ,
        [fldUpdateBy] [uniqueidentifier] NULL
)
GO

If I run it against an ordinary database it works fine and the table
is visible.  If I run it against the database where I *actually* want
it, it appears to run fine but the table is not visible.  If I try to
create the table manually, the system informs me "Table
tblResponsesBackup already exists".  Not in my database window it
doesn't.

The difference is that the database where I want to create the table
has some tables involved in merge replication.

How to make the table visible?  Any ideas, anyone?

TIA

Edward

 
 
 

Create table creates invisible table

Post by Dejan Sark » Sat, 12 Jan 2002 21:06:08


Have you tried to access it with a two-part name - like
SELECT * FROM dbo.tblResponsesBackup ?

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
Please join me at PASS North America in Denver - the first and only
user conference dedicated to SQL Server.
http://www.sqlpass.org/events/denverjan/index.cfm


Quote:> I am trying to create a table on a database using the following
> script:

> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[tblResponsesBackup]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblResponsesBackup]
> GO

> CREATE TABLE [dbo].[tblResponsesBackup] (
> [fldResponseID]  [uniqueidentifier] NULL ,
> [fldCSID] [uniqueidentifier] NULL ,
> [fldDate] [smalldatetime] NULL ,
> [fldResponse] [ntext] NULL ,
> [fldAttachedFile] [nvarchar] (255) NULL ,
> [fldFileAttached] [smallint] NULL ,
> [fldDocID] [int] NULL ,
> [fldUserID] [uniqueidentifier] NULL ,
> [fldUser] [nvarchar] (3) NULL ,
> [fldLastUpdate] [smalldatetime] NULL ,
> [fldUpdateBy] [uniqueidentifier] NULL
> )
> GO

> If I run it against an ordinary database it works fine and the table
> is visible.  If I run it against the database where I *actually* want
> it, it appears to run fine but the table is not visible.  If I try to
> create the table manually, the system informs me "Table
> tblResponsesBackup already exists".  Not in my database window it
> doesn't.

> The difference is that the database where I want to create the table
> has some tables involved in merge replication.

> How to make the table visible?  Any ideas, anyone?

> TIA

> Edward


 
 
 

Create table creates invisible table

Post by Steve Kas » Sat, 12 Jan 2002 21:28:34


Edward,

  Any chance tblResponsesBackup exists, but is some kind of object
other than a table?

create view tblResponsesBackup as
select * from pubs..sales
go

if exists (select * from sysobjects where id =
object_id(N'[dbo].[tblResponsesBackup]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblResponsesBackup]
GO

CREATE TABLE [dbo].[tblResponsesBackup] (
        [fldResponseID]  [uniqueidentifier] NULL ,
        [fldCSID] [uniqueidentifier] NULL ,
        [fldDate] [smalldatetime] NULL ,
        [fldResponse] [ntext] NULL ,
        [fldAttachedFile] [nvarchar] (255) NULL ,
        [fldFileAttached] [smallint] NULL ,
        [fldDocID] [int] NULL ,
        [fldUserID] [uniqueidentifier] NULL ,
        [fldUser] [nvarchar] (3) NULL ,
        [fldLastUpdate] [smalldatetime] NULL ,
        [fldUpdateBy] [uniqueidentifier] NULL
)
GO

select * from sysobjects where
id=object_id(N'[dbo].[tblResponsesBackup]')

drop view tblResponsesBackup

Steve Kass
Drew University


> I am trying to create a table on a database using the following
> script:

> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[tblResponsesBackup]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblResponsesBackup]
> GO

> CREATE TABLE [dbo].[tblResponsesBackup] (
>         [fldResponseID]  [uniqueidentifier] NULL ,
>         [fldCSID] [uniqueidentifier] NULL ,
>         [fldDate] [smalldatetime] NULL ,
>         [fldResponse] [ntext] NULL ,
>         [fldAttachedFile] [nvarchar] (255) NULL ,
>         [fldFileAttached] [smallint] NULL ,
>         [fldDocID] [int] NULL ,
>         [fldUserID] [uniqueidentifier] NULL ,
>         [fldUser] [nvarchar] (3) NULL ,
>         [fldLastUpdate] [smalldatetime] NULL ,
>         [fldUpdateBy] [uniqueidentifier] NULL
> )
> GO

> If I run it against an ordinary database it works fine and the table
> is visible.  If I run it against the database where I *actually* want
> it, it appears to run fine but the table is not visible.  If I try to
> create the table manually, the system informs me "Table
> tblResponsesBackup already exists".  Not in my database window it
> doesn't.

> The difference is that the database where I want to create the table
> has some tables involved in merge replication.

> How to make the table visible?  Any ideas, anyone?

> TIA

> Edward

 
 
 

1. create invisible table

Hi, I use informix-SE in DEC 5000/240.
 I have a table "manf002" about personal data.
 I can use isql to querry the data,
   but I can not find it in the database directory.
 My question is How to create a invisible table?
 Another question, I have so many tables,
    so my vendor make some tables for me like ( sal , man ...)
    for me to place different kind tables.
 How to create table in the database can put the tabel in sub-directory.
    example:   main.dbs --- man - man001
                                  man002 ( table)
                        --- sal - sal001
                                  sal002
                             |
                           ( sub-directory)
 Thanks advance.


2. DTS Text File Import

3. create temp table: -261 Cannot create file for table - Not owner

4. sudden inability to import images - FMP 4 / Win98

5. DTS: Using table from Source and table from Destination to create new table in Destination

6. Database Indexes: Open Question

7. How to execute a Create table or alter table table everytime before a data load

8. DBase III+ question on separating a field

9. CREATE TABLE or Import results in new table being flagged as SYSTEM table

10. Creating Identity Colum when Creating table through Code in VB

11. When creating table how do you create an variable array

12. creating a cursor using a dynamically created table name

13. drop default constraint created at creating table time**