Solved

Table fields missing when dragged into SQL Qry Builder

Posted on 2001-08-23
5
193 Views
Last Modified: 2013-12-24
I'm creating a web project on a Win2000 pro workstation connecting to an IIS 5 server connected to SQL server via System DSN. I create my data connection,(OLE DB for SQL) all tests fine. I create my data command, and open SQL Query Builder. When I drag my tables into the upper pane there are no fields in the tables,(except the ALL Catagories field) and there are no relationships between the tables(foreign key etc.) which there certainly are if I view the database diagram on the SQL Server itself. If I open the same project on the same pc with a desktop version of SQL running, the Table's fields and relationships will showup when dragged into the upper pane, allowing me to choose the fields and make my query. I sense there's some issue with connecting to the SQL server, or IIS server, but I can't figure it out. I have dbo rights over the database, I've relaxed security on the IIS server, but I'm a newbie dealing with VI and IIs so I could've missed something. And maybe this is more of a IIS/2000 configuration question?
Thanks for your help.
0
Comment
Question by:jazmarc
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:dgorin
ID: 6425431
I just tried to duplicate your problem but couldn't.  I made a new Interdev connection using a system DSN that connects to a NT4/SQL7 server as sa.  Computer with Interdev is W2K Pro.  I saw all the columns in the table when I dragged a table into the sql query builder pane.

You may want to revisit the DSN configuration and verify what user it's set up for, etc.

0
 

Author Comment

by:jazmarc
ID: 6426428
Yeah I've been reading more about this trying to solve it as well, and in Teach Yourself Visual Interdev 6 in 24hrs. they describe the exact problem I'm having and attribute it to the ODBC connection or permissions "the Query Designer will not display data columns for an input source if you do not have sufficent access rights to the input source or if the ODBC driver cannot return information about an input source you are using (for example, if no such table currently exists). In such cases, only a title bar is displayed for the input source window and the * (All Columns) check box."
So now my question is...Which ODBC do I use? Do I set one up on my local workstation and connect to the SQL server directly? Do I point to a File DSN I've setup on the Web Server? And are there any permissions I need to setup within the ODBC to ensure I have adequate rights to the database? I know the user setup in ODBC IS the database owner. It would seem I'm missing some piece to the ODBC/OLEDB puzzle. Thanks for your response.
0
 
LVL 5

Accepted Solution

by:
dgorin earned 300 total points
ID: 6426805
What I do is use SQL Server authentication.  Create a System DSN on your InterDev workstation using the ODBC manager.  When you create the DSN you will have the opportunity to specify the username and password, or choose to use NT authentication.

If it's your server, try using the sa user initially (the SQL Server Administrator user).  If everything works OK, you should review the permissions granted to the database and tables you are trying to use.

Once you create a connection in Interdev that works, you will need to have a DSN of the same name on the target server when you publish your site, or you'll need to reconfigure the connection on the server only.  I use an ISP that supports Interdev, so I just have different projects for local and remote, and connection settings for my local workstation & test database and the online server & database are different.

In any case, keep in mind the database connection needs to be between the db and the server using the db.  For your workstation that means you make a DSN on your workstation to connect Interdev or your local web server to your development database, while on a remote server you need a DSN on that server which connects to whatever production database you may be using.

hth,
 
0
 

Author Comment

by:jazmarc
ID: 6427139
Thanks
Armed with that I went in today to play with it again.
I was determined to get to the bottom of this. This time I loaded Interdev directly on the Web server I'm working on, just to take my workstation out of the troubleshooting loop. Any way I tried to connect to the SQL server I'd only pull up a title bar with no columns! Being really frustrated now I went to look at the database on the SQL server. It's small, thankfully, only 4 tables, our IT dept. wants to have a database with a Web front end to keep our trouble tickets in. So I recreated the thing. I didn't set it up originally, but here's the kicker, this is the name of the database 'IT Database'(without quotes).
THE DAMN NAME HAS A SPACE IN IT!!! I could kill the guy who set this up. I recreated the DB called 'it_database'in no time, setup my Data Environment in VI and saw my titlebar with columns immediately!!! What a relief. Thanks for your help explaining which server connects where.
0
 
LVL 5

Expert Comment

by:dgorin
ID: 6427480
Glad you got it working!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

708 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

19 Experts available now in Live!

Get 1:1 Help Now