[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

There is already an object named 'MyTable' in the database.

Hello All;
(Classic ASP - SQL Server 2005)

I am making up a code that does the following
#1: Creates Database
#2: Creates Tables.

OK, the database part works great.
The Table part, gives me the Subject Title Error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
There is already an object named 'MyTable' in the database.
/Populate.asp, line 25

This is my full code that I am using.
Can someone please let me know what is needed to make this work?
Thank You
Carrzkiss

<%
 set conn = CreateObject("ADODB.Connection") 
    conn.open "Provider=SQLOLEDB;Data Source=server-name;User ID=myUN;Password=********;" 
    sql = "CREATE DATABASE MyDatabase"
    conn.execute sql, , 129 

	   if not tableExists then 
		tableCreate = "CREATE TABLE Mytable(" & _ 
            "IDColumn INT not null," & _ 
            "IntegerColumn INT," & _ 
            "VarcharColumn VARCHAR(50)," & _ 
            "MemoColumn VarChar(MAX))" 
        conn.execute tableCreate, , 129 
    end if 
    conn.close 
    set conn = nothing 
%>

Open in new window

0
Wayne Barron
Asked:
Wayne Barron
  • 4
  • 3
1 Solution
 
Wayne BarronAuthor Commented:
It is putting the table into Memory, NOT in the Database.
Can someone please, please let me know what the heck is going on?

Please, Anyone!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is that after you created the database, you have to "use" that database, otherwise the "create table" will go to the default (mysql) database.

so, either you reconnect after the "create database" with the created database name in teh connection string, or you do:

CREATE TABLE MyDatabase.Mytable( ...
0
 
Wayne BarronAuthor Commented:
Hey Angel.
Hope you are doing well.

I just added the Database name to the script as you suggested.
However, it is not creating the Table in the Database.

Is the code I have accurate for performing this procedure?

(New Code with MyDatebase.MyTable
 set conn = CreateObject("ADODB.Connection") 
    conn.open "Provider=SQLOLEDB;Data Source=server-name;User ID=myUN;Password=********;" 
    sql = "CREATE DATABASE MyDatabase"
    conn.execute sql, , 129 

	   if not tableExists then 
		tableCreate = "CREATE TABLE MyDatabase.Mytable(" & _ 
            "IDColumn INT not null," & _ 
            "IntegerColumn INT," & _ 
            "VarcharColumn VARCHAR(50)," & _ 
            "MemoColumn VarChar(MAX))" 
        conn.execute tableCreate, , 129 
    end if 
    conn.close 
    set conn = nothing 

Open in new window


Carrzkiss
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>However, it is not creating the Table in the Database.
errors?

>if not tableExists then
does this function eventually still return true due to checking the table name in the "current" (mysql) database instead of MyDatabase ?
0
 
Wayne BarronAuthor Commented:
No errors.
I have a response.write
Letting me know that it is completed making the database, and making the table.
Both Write out, so i know that the work is done.

I then go into SQL Server Management Studio, and see the Database "MyDatabase"
But "No" Tables have been created.

So, the next thing that I did, was to change up the code a little, into the following.

Conn.execute "CREATE TABLE MyDatabase.Mytable"

Now, I believe that something is actually starting to happen.
I get the following error.


Microsoft OLE DB Provider for SQL Server error '80040e14'
The specified schema name "OneDB" either does not exist or you do not have permission to use it.
/Populate.asp, line 41

I did some checking, and found out that I needed to give permission rights to my user.
However, I do not know how to do that part.

--
And to answer your question.
When the table is first created in (mysql), it it there.
As when I run the code again, it does me that it already exist.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>CREATE TABLE MyDatabase.Mytable
must then them:
CREATE TABLE MyDatabase.dbo.Mytable
0
 
Eugene ZCommented:
and also in your function tableExists check  MyDatabase.dbo.Mytable (or MyDatabase.<shema>.Mytable
0
 
Wayne BarronAuthor Commented:
Cannot believe it was something so blasted simple.
You rock Angel.
Keep up your wonderful work.

Carrzkiss
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now