Incorrect syntax near '{'

[great_learner]

Hi guys,

I am trying to create a table in SQL Server 2005 through simple query which is as follows:

create table Appointment
{
Doc_ID numeric(4,0) references Patient(Pat_ID),
Pat_ID numeric(4,0) references Doctor(Doc_ID),
_Day day,
Date datetime,
Consultant_Name varchar(40),
primary key(Doc_ID,Pat_ID)
};

but i am getting the error:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '{'

I know there is a little syntax mistake, but i am unable to figure out. I didnot find solution on google. Please help me, I am newbie.. Thanks in advance

[jbisono]

Change " { " for " ( "

[great_learner]

I tried but now it gives another error:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'

my query:

create table Appointment
(
Doc_ID numeric(4,0) references Doctor(Doc_ID),
Pat_ID numeric(4,0) references Patient(Pat_ID),
_Day day,
Date datetime,
Consultant_Name varchar(40),
foreign key(Doc_ID,Pat_ID)
);

[jbisono]

Change foreign key to primary key.

[great_learner]

Thanks alot for your help. Code worked now.

create table Appointment
(
Doc_ID numeric(4,0),
Pat_ID numeric(4,0),
_Day varchar(12),
Date datetime,
Consultant_Name varchar(40),
primary key(Doc_ID,Pat_ID)
);

My last Question is that now how can i access this table? I cant find in list of my database tables. Do you know where tables are created through queries? Can you help?

[great_learner]

Thanks alot for your help. Code worked now.

create table Appointment
(
Doc_ID numeric(4,0),
Pat_ID numeric(4,0),
_Day varchar(12),
Date datetime,
Consultant_Name varchar(40),
primary key(Doc_ID,Pat_ID)
);

My last Question is that now how can i access this table? I cant find in list of my database tables. Do you know where tables are created through queries? Can you help?

[jbisono]

When you create a new query, there is a dropdownlist right next to the Execute button to the left, which you have to select in what database you want that query to run, you have to be very carefully with this because you can create procedure against the wrong database. anyway if you didn't change that I think by default the master database is selected, so it should be in there.

Right click the Databases folder in hit refresh.

I notice that you took out the reference for Doctor Table and Patient, maybe that did not work because you were running that statement against the wrong database, try to put the references back and run that query again the right database, if you have the Doctor and Patient table created it should work fine.

[great_learner]

Hi sorry for late reply... i retried the query, it says there exists an appointment table already but i cant locate it even after refreshing database. i cant find it also in any folder. Where can it be?

[BitBlt]

Here's a handy little script I use when I "lose" tables. It will basically iterate through every database in the SQL instance and list all the tables/views. Feel free to tweak it to limit or increase the scope (as in, database_id > 4 or so to exclude system databases like master, model, msdb and tempdb).

declare @mySQLStmt varchar(256)
-- Use this to create an empty temp table to hold your results
select * 
into #myTable 
from master.INFORMATION_SCHEMA.TABLES 
where 0 = 1

-- prepare a cursor to iterate with
declare cursor1 cursor for
select 'insert into #myTable select * from ' + name + '.INFORMATION_SCHEMA.TABLES' 
from master.sys.databases
-- uncomment to exclude system databases
-- where database_id > 4

open cursor1
fetch next from cursor1 into @mySQLStmt
while @@FETCH_STATUS = 0
begin
	-- execute the generated SQL statements, then get the next statement
	exec (@mySQLStmt)
	fetch next from cursor1 into @mySQLStmt
end

-- be sure to clean up and release your resources!
close cursor1
deallocate cursor1

-- select to your heart's content from this table.  
select * from #myTable
where TABLE_NAME = 'MyLostTable'

-- be sure to clean up!
drop table #myTable

This was tested with SQL2005 and SQL2008. Good luck finding your lost table!

[jbisono]

Nice BitBlt!!!!!

[BitBlt]

Thanks...hope you find it useful.