New to SQL server help me!!

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.
WaynebebayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
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
WaynebebayAuthor Commented:
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
arbertCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

WaynebebayAuthor Commented:
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
arbertCommented:
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
WaynebebayAuthor Commented:
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
lengreenCommented:
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
lengreenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WaynebebayAuthor Commented:
thanks that worked. im splitting the points cause he helped with the create tables
0
arbertCommented:
"Forget anything you have done so far just do this to the letter."

nice...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.