How can I use System.DirectoryServices in SQL 2005

I'm working on migrating webservices from VS 2003 to SQL Server 2005 by using VS 2005 DB project to create some Stored Procedure. When I want to add a component reference of "System.DirectoryServices " in to my project, I could not find it in the list. Is there a way to add this component in to my DB project or how can I access the Active Directory from the SQL 2005.

Thank you in advance,

Worachart C.

Worachart wrote:

I'm working on migrating webservices from VS 2003 to SQL Server 2005 by using VS 2005 DB project to create some Stored Procedure. When I want to add a component reference of "System.DirectoryServices " in to my project, I could not find it in the list. Is there a way to add this component in to my DB project or how can I access the Active Directory from the SQL 2005.

The reason you can not reference the dll from a Sql project in VS, is because the dll is not in the "approved list". In other words, certain system assemblies are considered to not be used from inside SQL Server, i.e. they can cause problems etc, or they have not been fully tested from inside SQL Server. Your directory services assembly is one of those.
However, you can register the assembly manually in SQL Server by running CREATE ASSEMBLY from Sql Server Management Studio, and after having done that, the assembly will be available to reference from your Sql Server project. Notice that you probably have to register the assembly as UNSAFE, which should make you stop and think what you are doing - so you do not call methods that can harm SQL Server.
Niels|||Thank you Niels to make me understand a lot better. I will learn more about whether I should use that class in my project or should I try with some other way.

POP

How can I use stored procedure's recordset output into simple SELECT statement?

Hi All
I have one stored procedure called MY_SP that returns a recordset. I would
like to use this recordset output into my SELECT statement like this:
SELECT * FROM (EXEC MY_SP '1', '2') RS
The above blurs up with syntax error at EXEC. Is there a way to achieve
this? (Note that MY_SP takes few parameters)
Thanks in advance.
NayanI don't understand why don't you just call EXEC MY_SP '1', '2'?
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have one stored procedure called MY_SP that returns a recordset. I
> would like to use this recordset output into my SELECT statement like
> this:
> SELECT * FROM (EXEC MY_SP '1', '2') RS
> The above blurs up with syntax error at EXEC. Is there a way to achieve
> this? (Note that MY_SP takes few parameters)
> Thanks in advance.
> Nayan
>|||thanks for asking
The example I have included is for the purpose of getting my problem across
to the audience. I agree with you that if I need to do something as simple
as quoted, I would rather do it your way. I'm actually trying to further
create a complex query using SELECT that will require data from this SP.
I hope I have made my question little more clearer.
TIA
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>I don't understand why don't you just call EXEC MY_SP '1', '2'?
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
>|||Use Temp table to store the resultset from MY_SP is one option.
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
> thanks for asking
> The example I have included is for the purpose of getting my problem
> across to the audience. I agree with you that if I need to do something
> as simple as quoted, I would rather do it your way. I'm actually trying
> to further create a complex query using SELECT that will require data from
> this SP.
> I hope I have made my question little more clearer.
> TIA
> Nayan
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thanks Perayu for your quick response.
The temp table option will work fine but what if I dont have permission to
modify the said SP? In that case, what would be my options?
thanks again for your ideas.
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:OfZwUV6RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Use Temp table to store the resultset from MY_SP is one option.
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
>|||On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:

>Thanks Perayu for your quick response.
>The temp table option will work fine but what if I dont have permission to
>modify the said SP? In that case, what would be my options?
Hi Nayan,
You don't have to modify the SP. You can insert the results of the SP in
a tem table, then use that in your later queries:
CREATE TABLE #Reults
(Col1 some_datetype NOT NULL,
..)
INSERT INTO #Results (Col1, ...)
EXEC EXEC MY_SP '1', '2'
Hugo Kornelis, SQL Server MVP|||In light of your other posts, rewrite the stored procedure as a table
returning function and then you can easily use it in the FROM part of a
SELECT statement.|||Hi Hugo.
Thanks for replying.
I already did this earlier but wanted know if there is a simpler way that
can allow me to pipe-in the resultset from an SP without first creating the
table.
thanks again.
Nayan
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:r7le129b97pr04pcafqo0boti13qba0945@.
4ax.com...
> On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:
>
> Hi Nayan,
> You don't have to modify the SP. You can insert the results of the SP in
> a tem table, then use that in your later queries:
> CREATE TABLE #Reults
> (Col1 some_datetype NOT NULL,
> ...)
> INSERT INTO #Results (Col1, ...)
> EXEC EXEC MY_SP '1', '2'
> --
> Hugo Kornelis, SQL Server MVP|||Hi JeffB
I understand your solution. What I'm looking at is how can I directly call
the stored procedure in the FROM part of the SELECT statement. I hope the
answer to this is not "NO" - cant use a stored procedure in the FROM part!
Thanks for the reply
Nayan
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142374607.563351.82890@.i40g2000cwc.googlegroups.com...
> In light of your other posts, rewrite the stored procedure as a table
> returning function and then you can easily use it in the FROM part of a
> SELECT statement.
>|||I don't believe that you can. This is what a function that returns a
table is for.

How can I use SqlDataSource to read a row?

The new databound controls are great, but somehow doing the simpler things has gotten harder.The code is in a function that is passed pollid. I want to use SqlDataSource to get the smallest value of answerid where pollid = the value passed for pollid. For example:

"Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"

I can set up the SqlDataSource for a data reader to fill a GridView control, but that isn't what I want to do. In fact, I can't find anything on SqlDataSource that doesn't also involve the GridView control.

I have so far:

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myReader As SqlDataReader

myConnection = New SqlConnection()
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("UserPolls").ConnectionString

myCommand = New SqlCommand()
myCommand.CommandText = "Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"
myCommand.CommandType = CommandType.Text
myCommand.Connection = myConnection
myCommand.Connection.Open()

Now assuming I have a single row of data, how do I read the value of Answerid? If I have this wrong, what should I have?

Diane

select top 1 AnswerId from..... order by AnswerId.

|||

Already did that - it's in the example I posted. What I don't know is how to read the data from the record. In classic ASP, if I had a recordset called rs, I would do:

Dim newId as integer

newId = rd("AnswerId")

How do I do this with a SqlDataSource?

Diane

|||

I found an article that helped. A less than perfect but working solution is:

Dim userpoll As New UserPolls
SqlDataSource1.SelectCommand = "Select Top 1 [PollId], [AnswerId] from [PollAnswers] where pollid=" & PollId & " order by AnswerId"
Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
For Each dr As DataRow In dv.Table.Rows
AnswerId = dr("AnswerID")
Next

The For..Next is not needed, but since I'm only returning one record, it won't hurt.

|||

Just a quick addition to what yo already have: you should be using Parameterized Queries. Do a search for it in these forums and you will find how/why to use parameterized queries. In brief, to avoid SQL Injection attacks.

|||

A search turned up three results - your post here, another post by you and one more that also recommends using parameterized quieries. Can you direct me to a tutorial or article?

Diane

|||

Sure: Here's a few:

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

http://davidhayden.com/blog/dave/archive/2005/10/24/2528.aspx

If you are wondering how/where I got them ... Goooooooglee.....

How can I use SQL with Access

How can I use SQL as a server for my Access Database app?
Chumpie999You could LINK SQL tables into Access then use them as regular Access tables. You can use ADO to connect to SQL and write a frontend application.

How can I use SQL reporting Services to get a dynamic dataset from another web service as my rep

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

I think this technical article may help you.

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

Simone

|||Thank you very much, this is almost exactly what I was looking for, not 100% but closer then I have been for weeks now. I just need to try and figure out now if I can send a parameter to a web service now to get the result set I need. Again, thanks...

How can I use SQL Report in ASP.NET?

Hi,

Can I use SQL Reports in ASP.NET? Please help me how it is working. Can I use more than one tables in query?

Arun.

You can use reportviewer from VS in your ASP.NET. You may have it for the express version through SP1. You also can use multiple tables in your query through JOINS for example. If you mean for the ReportViewer datasource, you can use multple datasources for the reportviwer. I hope I answer your question in a generic way. If you have specific questions, I will try to share what I know.

How can I use Sql Express instead of Sql Server 2005 for asp.net providers

Hi all,

Since I activated the aspnet_regsql.exe tool, it has been a bad day - here's why:

Originally I installed VS2005 Pro (without Sql Express) and then Sql Server 2005 Dev. I then started to learn asp.net from the ".NET FRAMEWORK 2.0 Web-Based client Development" Training kit from Micorsoft. I have compiled and run every exercise up till Chapter 9 lesson 2 exercise 1, without the need to start the Sql Server 2005. In exercise 2 I should learn about asp.net profilers and the exercise showed me to use the aspnet_regsql.exe tool if I was using Sql Server 2005 - and so I did, and exercise 2 worked fine after a couple of adjustment in the connection string, because it was configured to use the SQLEXPRESS server by default.

However - in Chapter 9 lesson 3, I should not use asp.net providers, so I shut down the Sql Server 2005, because I don't have that much memory to spend. Now the trouble comes - every website I have made afterwards require that the Sql Server 2005 is running or else I get some errors.

How can I get back to the way it was before I activated the aspnet_regsql.exe, so I don't have to have the Sql Server 2005 running every time I want to make a website using aspx?

Thanks in advance

From your description, it sounds like you need to change the connection strings to use the SQL Express instance instead of the SQL Developer instance.|||

My connection string in both the machine.config and web.config, located in <Win dir>\Microsoft.NET\Framework\v2.0.50727\CONFIG, looks like this:

<connectionStrings>

<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;

Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;UserInstance=true"

providerName="System.Data.SqlClient" />

</connectionStrings>

And I do not use an web.config at the website level.

I have also tried to reverse the action done by aspnet_regsql, by selecting the "Remove application services information from an existing database" option when starting aspnet_regsql.exe. Then detaching the aspnetdb database in Sql Server 2005 and deleted the aspnetdb files (mdb and log files) from Sql Server 2005 - all with out luck.