Solved

New to SQL server help me!!

Posted on 2004-10-21
439 Views
Last Modified: 2012-06-27
Ok here is what I want to do and what I have done already.

Want to do:

I want to be able to write .asp pages with sql statements to create and drop tables.

What I have done:

I have a windows 2000 server running, with IIS on it, and MS SQL server 2000.

The problem:

Dont know where to begin to setup a connection to a database from a .asp file and the how to create the tables. I tried figuring out how to connect to the database but it didnt go so well. help me.
0
Question by:Waynebebay
    10 Comments
     
    LVL 34

    Assisted Solution

    by:arbert
    There are a million different ways, but here's one example:

    DbConnectionStr = "ProvIDer=SQLOLEDB.1;Password=yourpassword;Persist Security Info=True;User ID=youruserid;Initial Catalog=yourdatabase;Data Source=SQLServerNameorIPAddress"


      Set c_objConn = Server.CreateObject("ADODB.Connection")
      c_objConn.Open dbConnectionStr

    sql="create table test (column1 varchar(100),column2 int)"

    c_objconn.execute(sql)


    Brett
    0
     

    Author Comment

    by:Waynebebay
    here is what i get


    Error Type:
    Microsoft OLE DB Provider for SQL Server (0x80004005)
    Login failed for user 'dbo'. Reason: Not associated with a trusted SQL Server connection.
    /justin/create.asp, line 7


    i dont think i know the user name and password. I thought it was  user = dbo   pw = sa    for the northwind database.  where can I change the user and password? and dont i have to do some stuff with ODBC and IIS before i can connect with this code?
    0
     
    LVL 34

    Expert Comment

    by:arbert
    dbo stands for database owner--it's not really a user......sa is the "system administrator" account......

    you can open enterprise manager.  Then either go to security, or expand your database and go to users.  If you want to create a new login, go to security and add the login--you can also assign the database permissions when you add the user.

    You also need to right click on your server (in enterprise manager) and choose properties.  Click on the security tab and make sure you have "SQL Server and Windows" selected for the security mode.  If you don't have this selected, you have to setup the IIS account with rights on the database....
    0
     

    Author Comment

    by:Waynebebay
    ok i did exactly what you told me to do. i added the users SERVER001\Administrator

    I tried it again with that username and password it did not work. I used the user name Administrator, and SERVER001\Administrator. with the password that I put somewhere in IIS if forgot and tried a blank password.

    does the computer have to be on a domain or be a domain controller maybe?
    0
     
    LVL 34

    Expert Comment

    by:arbert
    No, if you setup to use Windows and SQL authentication, it doesn't have to be on the same domain...and you got a login failed error above, so we know IIS is talking to SQL Server.

    paste the connection string you used (stip the password out)
    0
     

    Author Comment

    by:Waynebebay
    I used the one posted above the guy gave to me....i dont know if the password is right, i will change the windows administrator password and try again. if i dont post it is fixed then it didnt work.

    DbConnectionStr = "ProvIDer=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=Administrator;Initial Catalog=Northwind;Data Source=192.168.1.103"


      Set c_objConn = Server.CreateObject("ADODB.Connection")
      c_objConn.Open dbConnectionStr

    sql="create table test (column1 varchar(100),column2 int)"

    c_objconn.execute(sql)


    Error Type:
    Microsoft OLE DB Provider for SQL Server (0x80040E4D)
    Login failed for user 'Administrator'.
    /justin/create.asp, line 7





    0
     
    LVL 10

    Expert Comment

    by:lengreen
    Hi

    I would recommend reading a good tutorial on ASP Databases & ADO

    here is one that i feel would answer many of your questions and give you a good insight

    cheers

    Len

    http://www.4guysfromrolla.com/webtech/faq/Databases/faq1.shtml
    0
     
    LVL 10

    Accepted Solution

    by:
    JUst To Get you Connected

    Forget anything you have done so far just do this to the letter.

    Open Enterprise Manager
    Expand Your Server
    Expand Security
    Right Click Logins -> New Login
    Name: TEST
    Select SQL Server Authentication
    Password: password
    Select Sever Roles TAB Check System Administrator (just to give you access to all DBs not usually a good idea tho )
    Click OK
    Confirm Password


    your new connection string to try

    Data Source=192.168.1.103;Initial Catalog=Northwind;User ID=TEST;Password=password;Pooling=True;

    OK see how you get on


    (Bear in mind when SQL Server was installed Windows Only Authentication must not have been selected, if it was there is a registry edit to do)

    cheers

    Len
    0
     

    Author Comment

    by:Waynebebay
    thanks that worked. im splitting the points cause he helped with the create tables
    0
     
    LVL 34

    Expert Comment

    by:arbert
    "Forget anything you have done so far just do this to the letter."

    nice...
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how the fundamental information of how to create a table.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now