In article <MPG.225efff6806313cd9896a5@news.dbase.com>,
trannnospam@nospammpx.xom.au says...
> Hi All,
> I'm sure this is just a string thing ...
>
> I'm trying to generate a temporary table with a dynamic name but I can't
> get it to work.
>
>
> cTempHead=" blabla "
> form.vtrackdb.executeSQL('"CREATE TABLE'+cTempHead+'(COMPANY varchar
> (60))"')
>
>
> The error I get is
> Server Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
> find stored procedure 'CREATE TABLE temphead (COMPANY varchar(60))'.
>
> Help Please
>
> TIA
> Trevor
Trevor,
If I recall (I haven't done this), others have said you need to put dbo.
in from of the table name when using MS SQL server:
create table dbo.yourTable <etc.>
This message says a lot about it:
-------------------------------------------------------------
Subject: Re: Stored Proc MS SQL and dbase
From: "Robert Bravery" <me@u.com>
Date: Mon, 26 Jun 2006 20:57:05 +0200
Newsgroups: dbase.sql-servers
Message-ID: <oBIlCNVmGHA.1976@news-server>
HI,
To answer you question,
The master database is a parculiar beast. It along with others are
needed
for the proper running of MSSQL. They contain, all critical information
such as server specific configuration information, user login accounts,
running processes, system error messages, system stored procedures etc.
It
also records the existence of other databases and the location of the
primary files that contain the initialization information for the user
databases.
Most of the objects in the master database are maked as system, and
therefor
would be visible to the current server. Think of the global or _app
variable
in dbase.
Your table in your database are not system object, and neither should
be.
Think of having many databases each with an employees table, now if you
were
to run a select statement, even from a master SP, which employees table
are
you looking for. The same thing applies for dbase tables, variable etc.
its
all a matter of scope. In dbase, if you did not name you PB dbase
creates
names for you, pushbutton1. Now imagine you have 100 forms, all with a
pushbutton1 in it, possibly two or more, if you have PB on containers.
Now
in a form you call the pushbutton1.onclick, which one are we refering
to,
you do this by supply the fully qualifies name, and by the fact that
dbase
makes this object private to the containing object, namely the form.
So you would need to supply the fully qualified object name, similar to
dbase: as in form.pushbutton1.text
So the name would be database.user.objectname, or in a cluster
environment
or data wharehouse, you would have server.database.user.object.
Then in you Sp you would need to do something like
Select * from NothWind.dbo.employees
This would work from anywhere in the master database, or the parent
database
of the SP.
But if you were in another database, you would also need to call that
SP(object) by its fully qualified name, as in:
exec mynewdatabase.dbo.northwindselectallemployees.
This of course assumes you have all the needed permisions.
So in my case I created a testsp in the master database, it was not a
system
object.
In my database, namely RASRMIS, from dbase, I executed the SP as exec
master.dbo.testsp. In that SP I simply had a select statement as select
*
from rasrmis.dbo.company where id=@id
This then worked as I supplied the parameter.
Hope this answers the questions.
Robert
-------------------------------------------------------------
--
Geoff Wass [dBVIPS]
Montréal, Québec, Canada
.|.|.| dBASE info at http://geocities.com/geoff_wass |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.
|