Accessing DB from Webpages or Visual Studio

Ok,

I am a webdesigner who at the moment does not know SQL (although, I plan on remedying that) so I am developing a page with a DB designer - he is doing the DB work, I am doing the look/feel, but he asked me the following questions, of which I cannot seem to find an answer and the guys who tend our server are useless - so I am hoping someone here can help me. In general what he needs to know is:

"what is the external address (either in domain format or IP) and the equivalent internal address so that we can access the msSQL running on your server. The internal address is needed for the webpages to talk to it, while the external address is needed for development of the pages in visual studio and the database tools." Also, he later sent me an email asking that when I get this info (from someone) that it would be usefull to get a sample string/query to get access to the DB. I am running SQL 2000 and have enterprise manager. Where can I find this information? or how do I figure this out?

Thank you for all the help -- Please let me know if you need any more info.

Hey everybody,

I am a webdesigner who currently does not know SQL (although I am working on Remedying that) So as for my question, it comes from the gentleman who I am working with on a new site. What he needs to know is:

"what is the external address (either in domain format or IP) and the equivalent internal address so that we can access the msSQL running on your server. The internal address is needed for the webpages to talk to it, while the external address is needed for development of the pages in visual studio and the database tools."

We are running SQL 2000 with Enterprise manager. On an IIS server. He would also like to get a sample string {? = Query?} for a page accessing the DB.

Any help about where to find this information would be greatly appreciated. If you need to know anything else. Please let me know...

|||http://www.connectionstrings.com/?carrier=sqlserver2005|||There is no internal and external address. I am not quite sure what you mean by that. There is also no querystring in IIS to access the db, you will have to code that for yourself. You can use any coding language and a connectionstring from www.connectionstrings.com. Hars to tell what he wants to know with this information so far.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The 'internal' address will be the ServerName. The 'external' address will be the port and IP address -that assumes that your firewall allows that port to be open. (I suggest using something other than the default port of 1433 -choose a high number port to use.)

As Jens suggested, the web application should use the correct connectionstring. Hopefully, you will NOT allow something as reckless and dangerous as using a =?Query to pass connection information or sensitive data.

Accessing Data directly from a SqlDataSource?

First let me give you a little back ground on me. I'm very new to the ASP, ASP.NET, Visual Studio, Sql Server, Frameworks...thing. I am coming over from a PHP/MySql background of over 5 years. The change over to VBScript and VB has been to tough and I have a basic liking for the Visual Studio 2005 and the ease of putting things together. However, I have come across something that to me seems like it should be relatively simple, but haven't been able to find the documentation or samples to describe what I'm looking to do.

Rough Need:

1. Start with a form will a couple labels and a singe textbox to get the lookup date from an user.

2. Query one table/view based on the users choice of date and select only one field of returned data.(Doing this by itself is not a problem and I can display my results in a Gridview, but this is where it starts getting tricky and the gridviews won't work for me.)

3. If there is something returned, I need to start a HTML table layout or possibly some form of a Gridview(I don't see how I would use the Gridview) and start a loop, adding the first returned row from this query into the first cell.

4. Now, based on that same User Date and the returned row value from the previous query, I need to query another Table/view and return another single field, which might return 1 or multiple rows, which I need to start a loop to display unique items in cells under the first on above.

5. Based on the original User Date and each returned row from the first query, I need to query two other Table/views and get some additional information. 4-5 fields will be returned and be displayed on one row in the number of necessary columns.

6. This would finish the first row from the first query, so I would need to loop back up to see if there were any further results and continue looping until the get to the last of the results from the first query.

7. Finally, I would just need to close up the Table/gridview.

The basic results I'm looking for would be similar to the following:

Results for 10/11/2005

Field name 1Field name 2Field name 3Field name 4Field name 5

First Result from query 1
1 of 4 results from Query 2, but only 1 unique result

Info from Query 3

Info from Query 3

Info from Query 3

Info from Query 4Info from Query 4Info from Query 3Info from Query 3.

Info from Query 3

Info from Query 4
Info from Query 4Info from Query 3

Info from Query 3

Info from Query 3

Info from Query 4
Info from Query 4Info from Query 3Info from Query 3

Info from Query 3

Info from Query 4Info from Query 4

Second Result from query 1
First result from Query 2 after looping though the first query
Second result from Query 2 after looping though the first query
Info from Query 3

Info from Query 3

Info from Query 3

Info from Query 4Info from Query 4Info from Query 3Info from Query 3.

Info from Query 3

Info from Query 4
Info from Query 4Info from Query 3

Info from Query 3

Info from Query 3

Info from Query 4
Info from Query 4Info from Query 3Info from Query 3

Info from Query 3

Info from Query 4Info from Query 4

It seems me from what I have read and slowly figuring out, is that I should be able to directly access the DataSet returned by four SqlDataSources, one for each of the above querys and then just write my own VB to handle the necessary looping, table format and such. I can easily add the for SqlDataSources to the page and add a Gridview for each one and get 4 separate chunks of info, but can't see a way with GridViews to intermingle the info like displayed above.

So, if it can be done with Gridviews, then I would love to see how that is done. But, if someone could explain to me how I access the DataSets directly that I get from the 4 SqlDataSources, then that would be ok too. I have figured enough out with VB, that I can write the code to do my looping requirements, if I can just access the information I get back. Thanks for reading all the way through this long post.

Jack

First let me apologize for the mis-spellings and the two links in my sample layout. I did some copy-n-pasting, then changed the wording, but forgot to remove the link behind. I didn't see any way on this forum to be able to Edit my own post to make the necessary corrections.

Second, is this as big of a problem as it seems, since know one made any replies to it? From what little research I've done, it looks like this should be possible with nested Repeaters, but I just can't seem to figure out how to put it all together so that the second SqlDataSource can use each returned value from the results of the first SqlDataSource to get the next set of records and then again with the third and fourth SqlDataSources.

If someone could just give a very short example of a form with a single texbox and submit button, that on postback, you could manually loop through the results returned by a single field onto the page, then I think that would give me some direction here. This is one of those cases that I don't think I want to use a Control to display the returned information, I just need to know how to manually access that returned info. Thanks again.

Jack

Accessing Custom Assemblies

We are building a report via Business Intelligent Studio. We have deployed the custom assembly dll to two folders:

1. Microsoft Sql Server\MMSQL.3\Reporting Services\Report Server\bin

2. Microsoft Visual Studio 8\Common\IDE\private assemblies.

This dll is an instance dll.

When we open the report properties/reference tab, we can find and link to the dll. Which one of the above locations is the correct one? (We have #2).

We created the class and instance entry.

Next, we added a textbox to the report layout. When we try to use the =Code keyword we get an unrecognized identifier error.

Can anyone shed any light on why this is happening. We've spent considerable time trying to find any info on this with no luck.

Any help is appreciated

Information about using custom assemblies with reports can be found here:

http://msdn2.microsoft.com/en-us/library/ms153561.aspx

For me, to use the assembly in BIDS the path is \Visual Studio 8\Common7\IDE\PrivateAssemblies

Note "Common7" and no spaces in "PrivateAssemblies".

Accessing AdventureWorks DB from ASPNET

Hi folks,

I've been wondering whats the "appropriate" way to configure AdventureWorks DB such that the WHOLE database can be access from Visual Studio and ASPNET webpages.

The problem I keep beating my head against is the schema problem which prevents me from accessing tables with different schema.

What I've done/can do so far is:

1. Installed AdventureWorks DB
2. Can run SQL cmds from Winforms apps and these are working as expected.
3. I've added MACHINENAME\ASPNET to the database. The default schema is "dbo".
4. The DB is running on the same server as the web server.
When I try to execute something trivial such as:

SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=true;");

try

{

connection.Open();

SqlCommand command = new SqlCommand("SELECT * FROM [Production.Product]", connection);

SqlDataReader reader = command.ExecuteReader();
...
To get the above to work I need to set the ASPNET users schema to Production.

I will admit I am a novice when it comes to SQL Server but I'm learning fast.

Can anyone help?

Short of trawling through a ton of code what was Microsofts intended usage of schema and how would one access tables located in multiple schemas from ASPNET.

Thanks,

Philip.

See the response at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=166975&SiteID=1

Accessing (writing into) Terdata by ado.net and SSIS

Hello everybody,

my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had′nt seen so far?.

In the software documentation we found a tip "ADO.NET" as Data Destination is′nt imlemented for now - please wait until ....

I′m asking me if someone has had the same problem before and could help me out with a little tip.

thx in advance!

Christof

Moved to the SSIS forum. It doesn't sound like you're having a problem with ADO.NET itself, but rather how to get SSIS to do what you want, so I think this is the best place to start.

Thanks,
Sarah

Accessing (writing into) Terdata by ado.net and SSIS

Hello everybody,

my name is Christof and I#m currently trying to write into our Terdata Warehouse by SSIS. In the Management Studio we can perfectly access Terdata - reading from it - but we actually see no way writing back Data into that system. Is there any hidden feature, any configuration menu that we had′nt seen so far?.

In the software documentation we found a tip "ADO.NET" as Data Destination is′nt imlemented for now - please wait until ....

I′m asking me if someone has had the same problem before and could help me out with a little tip.

thx in advance!

Christof

Good luck with non-SQL Server destinations.

Read through here for tips: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106559&SiteID=1

Access to integration services

Hi Guys,

I have a quick question, Can i access the SSIS services of a Server from a remote server using Sql Server management studio.

I'm trying to access a third DB server and i can access it using Sql server management studio with sa and password but the integration services aren't there and when i try to explicitly connect to it, it is not connecting

Any ideas?

P.S: Can any of you recommend a very good book as I've done some work with it but it seems i might be the one doing more work with BI from now on as the organisation is not going to appoint anyone else in this position.

Thank you

Gemma

Do you have the SSIS service running on the server? Do you get any error messages?|||

Gemma wrote:

P.S: Can any of you recommend a very good book as I've done some work with it but it seems i might be the one doing more work with BI from now on as the organisation is not going to appoint anyone else in this position.

This is a good book. Especially for doing ETL with SSIS.

http://www.amazon.com/Expert-Server-Integration-Services-Programmer/dp/0470134119/
|||

If you want something that is more of a reference, the Kirk Haselden Microsoft SQL Server 2005 Integration Services is really good. The Wrox Programmer to Programmer SQL Server 2005 Integration Services is also decent. (They cover nearly the same topics, you will only need one or the other)

I'd agree that Expert SSIS is a good book (through about half of it now). The Expert SSIS is more of a walk through while explaining some of the theories and providing tips and best practices. It often tells the reader to refer to the SQL Server 2005 Integration Services book for more detailed information on the various tasks / transforms.

If you are looking for a reference on Datawarehousing / B.I. in relation to SQL 2005 SSIS / Analysis Services / Reporting etc, The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite is pretty good. If you are just looking for Datawarehousing / B.I. theory then you should probably read The Data Warehouse Toolkit Second Edition by Ralph Kimball.

I also have the Delivering Business Intelligence with Microsoft SQL Server 2005 (Larson) and Practical Business Intelligence with SQL Server 2005 (Hancock) but have not had a chance to read through them as of yet....

|||

Gemma wrote:

Hi Guys,

I have a quick question, Can i access the SSIS services of a Server from a remote server using Sql Server management studio.

I'm trying to access a third DB server and i can access it using Sql server management studio with sa and password but the integration services aren't there and when i try to explicitly connect to it, it is not connecting

Any ideas?

We were having this problem until earlier this week (we are in the beginning phases of using SSIS ourselves). We were able to resolve the access denied issue (if that is what you are having) by following the steps in the following article: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

|||

Thanks to all of you, who contributed. I have made a note of your suggested books.

Can any of you also tell me a very good t-sql book as I'm really really bad in t-sql?

Thanks again

Gemma

|||

You'll want two: Inside SQL Server 2005: T-SQL Programming and T-SQL Querying. You will need to read the Querying before the programming as the programming book builds off of ideas presented in the first book.

(Although I only bought them a little less than a month ago and haven't read them yet, when I did research those were the two that came with the highest recommandations)

|||

Thank you

Ta

Gemma

Access SMO objects in CLR proc

I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?

Thanks

Bert

Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.

What would you want to do using SMO that you can't do using Transact-SQL?

|||

You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.

If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.

Access SMO objects in CLR proc

I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?

Thanks

Bert

Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.

What would you want to do using SMO that you can't do using Transact-SQL?

|||

You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.

If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.

Access or SQL Server

Hi,
I am writing a Visual Studio.NET client server app that will reside on one
PC, the GUI, Business Logic and Database. I am trying to decide which
database to use, either SQL Server/ Express or use Access with JET engine.
The database on this PC will be
also be accessed by remote PC's to generate reports.
I'm not sure the exact Pros and Cons of both databases.
I'd appreciate suggesstions as to the pros and cons of each database to help
decide which database to use.
Thanks In Advance,
MaccaHi,
http://www.microsoft.com/sql/soluti...are-access.mspx
HTH, Jens Suessmeyer,|||Macca wrote:

> Hi,
> I am writing a Visual Studio.NET client server app that will reside on one
> PC, the GUI, Business Logic and Database. I am trying to decide which
> database to use, either SQL Server/ Express or use Access with JET engine.
> The database on this PC will be
> also be accessed by remote PC's to generate reports.
> I'm not sure the exact Pros and Cons of both databases.
> I'd appreciate suggesstions as to the pros and cons of each database to he
lp
> decide which database to use.
> Thanks In Advance,
> Macca
Please do not multi-post!
I replied in microsoft.public.sqlserver.server
David Portas
SQL Server MVP
--|||> I am writing a Visual Studio.NET client server app that will reside on one
> PC, the GUI, Business Logic and Database. I am trying to decide which
> database to use, either SQL Server/ Express or use Access with JET engine.
Access has several limitations that I document here:
http://www.aspfaq.com/2195|||Thanks,
Macca
"Jens" wrote:

> Hi,
> http://www.microsoft.com/sql/soluti...are-access.mspx
> HTH, Jens Suessmeyer,
>|||Thanks,
I want to use SQL Server. This will help,
Macca
"Aaron Bertrand [SQL Server MVP]" wrote:

> Access has several limitations that I document here:
> http://www.aspfaq.com/2195
>
>

Access MSDE From VS 2003 Server Explorer

Not sure if this is a MADE or Visual Studio issue, but here goes:
I have MADE and SQL Server GUI tools installed (GUI from Developer Edition)
to support database app development with Visual Studio .NET 2003 (VS 2003).
I have the sample databases installed (pubs, North, etc.) via an executable
file ConfigSamples.exe which includes TO-SQL scripts to create the sample
databases then populate the tables with data.
Because ConfigSamples.exe is not "a known archive type" I cannot get inside
to see the TO-SQL scripts to discover how these databases are made to not
demand user authentication in VS 2003 Server Explorer.
From VS 2003, I can see my MADE instance and the databases, and access them
via Server Explorer or from .NET apps I develop/evaluate. So far, so good.
Now the issue:
I need to create new databases in conjunction with various projects, and
have done so successfully using the SQL Server GUI tools, and from TO-SQL
scripts. When I create the new database, Enterprise Manager and Query
Analyzer allow my access to do whatever I want with the database.
Not so in VS 2003 Server Explorer! the new database does show up in the list
of databases under the MADE instance, but when I try to expand the new
database node, up pops a dialog asking for a Username and Password, or
offering me the choice of using Win NT Authentication. No answer I provide
to this dialog gives me access to the database. Choosing NT Authentication
gets me another dialog with *no message text* and only an OK button to
click, and trying any ID/password combination gets me a message indicating
it's not acceptable.
I should say that I'm running using full administrative privileges.
So the question is: how can I create a database like the sample databases
that can be accessed in Server Explorer? Is there a way to turn off the
security check?
Disclaimer: I'm not a SQL Server jock (obviously) and am using the software
and databases to teach college classes and evaluate student projects, so try
to keep it fairly simple?
Thanks in advance for any insights.
Arrrrgh!!
Somehow all instances of "MSDE" in my post were changed to "MADE", could
have sworn I clicked "Ignore" in the spell checker. Sorry!

Access denied to vbs file

Hi All,
Am trying to create a job in management studio 2005, that uses an sql
server agent proxy, but when I try to open the Active X proxy (a vbs script)
on my my c:\drive, it gives me error message: Access to the path 'C:\Program
files\Microsoft Learning\2780\Mod7\Practices\CreateOrder
.vbs' is denied.
(mscorlib). I tried to shared the drive in the folder security, but no joy.
foscoHi All,
Silly me, the script was supposed to save to a path pointing to D:\ and I
check inside the script to discover this, so the job was able to execute as
instructed, changed it to C:\ drive and it worked, thanks so much.
Fosco
"fosco" <folabii@.hotmail.com> wrote in message
news:edmrXaZPIHA.1756@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> Am trying to create a job in management studio 2005, that uses an sql
> server agent proxy, but when I try to open the Active X proxy (a vbs
> script) on my my c:\drive, it gives me error message: Access to the path
> 'C:\Program files\Microsoft Learning\2780\Mod7\Practices\CreateOrder
.vbs'
> is denied. (mscorlib). I tried to shared the drive in the folder security,
> but no joy.
>
> fosco
>

Access denied to vbs file

Hi All,
Am trying to create a job in management studio 2005, that uses an sql
server agent proxy, but when I try to open the Active X proxy (a vbs script)
on my my c:\drive, it gives me error message: Access to the path 'C:\Program
files\Microsoft Learning\2780\Mod7\Practices\CreateOrder.vbs' is denied.
(mscorlib). I tried to shared the drive in the folder security, but no joy.
fosco
Hi All,
Silly me, the script was supposed to save to a path pointing to D:\ and I
check inside the script to discover this, so the job was able to execute as
instructed, changed it to C:\ drive and it worked, thanks so much.
Fosco
"fosco" <folabii@.hotmail.com> wrote in message
news:edmrXaZPIHA.1756@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> Am trying to create a job in management studio 2005, that uses an sql
> server agent proxy, but when I try to open the Active X proxy (a vbs
> script) on my my c:\drive, it gives me error message: Access to the path
> 'C:\Program files\Microsoft Learning\2780\Mod7\Practices\CreateOrder.vbs'
> is denied. (mscorlib). I tried to shared the drive in the folder security,
> but no joy.
>
> fosco
>

access denied for user <machine name>\ASPNET

Hi!

I've just started to learn to use Visual Studio, and has come to database access.
I ran into this errormessage:

access denied for user <machine name>\ASPNET

The book I'm using just only say that I have to grant the ASPNET user account permissions before the Web application will have access to a SQL database. ... Seems clear at first glance ... but I lack a "how to" ...!

--

I've tried to search the net for solutions ... several thousands of pages came up ... indicating that I'm far from the first who have this problem. ... So after I now have spend hours reading, without finding any useful ansver to the question. ...

So let me just tell the most popular answers. (So You don't repeat something I allready have read hundreds of times.):
This question has been asked before
Yes, true answer, but it doesn't help.

See the FAQ
If it was stated were in the FAQ to look it could have been useful.

Use something called "enterprise manager" (WIN XP pro, don't have this, though.)
I use WIN XP pro, so that answer dosn't help me either.

--

Hope some one can help.Do you have SQL Server installed on the machine in question? If so, Enterprise Manager will be there (it will run fine on XP pro). You will not have Enterprise Manager if you have MSDE, in which case you need to use osql.exe:

This article explains various ways to work with MSDE. Note that the "Grant permissions to the ASPNET account" is the section to do what you need to do, given your current connection string.|||Yes, I have this osql.exe program. But when I try to use it, it comes up with an access denied error message.

I found another solution to the problem: Moving the ASPNET user from "Restricted user" to "Administrators".

I was a bit hesitant to do this at first (At first glance it appears a bit unsafe to do so.), but when I realized that it is commonly done that way, and when I tried it myself everything did run as it was supposed to. (So ... problem solved!)|||This is areally, really bad idea. The reason MS used the restricted account by default is to make your machine secure. Moving the ASPNET user to the Administrators group is aterrible idea.

Just cause it works, does not mean it is correct.|||Let me reiterate what Doug just said.

Moving the ASPNET user to the Administrators group is a terrible idea.

Don't do it! If someone manages to compromise your server through your website (such as through a SQL injection exploit) they will have unlimited access to run/install/ruin whatever they want on the machine.

Terri