Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

New to SQL server help me!!

Posted on 2004-10-21
10
Medium Priority
?
497 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
Comment
Question by:Waynebebay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 600 total points
ID: 12377636
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
ID: 12377673
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
ID: 12377788
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Waynebebay
ID: 12377857
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
ID: 12377897
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
ID: 12377921
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
ID: 12378573
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:
lengreen earned 900 total points
ID: 12378632
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
ID: 12382296
thanks that worked. im splitting the points cause he helped with the create tables
0
 
LVL 34

Expert Comment

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

nice...
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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