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?
 
lengreenConnect With a Mentor Commented:
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
 
arbertConnect With a Mentor Commented:
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.