.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

DriveInfo.GetDrive() to return info in table format using SQL CLR

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

Hello SQL CLR experts,

We're using SQL Server 2008 R2 (French) and Visual Studio 2008.  I'm trying to get a "simple" SQL CLR to work, which calls DriveInfo.GetDrive() and returns drive name, space available and total size into a SQL Table from the UDF GetDriveInfo() defined below. I need help getting the code to work.

I realize that there is already an xp_fixeddrives extended stored procedure that returns only the available space.  I want to return the Total Size of each drive, the volume name, etc.

I based my code on the article http://www.sqlservercentral.com/articles/SQLCLR/65656/ but I want to use DriveInfo.GetDrive() instead of DirectoryInfo.GetFileSystemInfos().  I was successfully able to get the DirectoryInfo assembly from the article to work, but I am having trouble getting my DriveInfo.GetDrive() adaptation to work.

In Visual Studio, I created a new C# SQL Server project and am using the following code:

using System;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

View Complete Post

More Related Resource Links

Table format of RadioButtonList


Hi All,

I have radiobuttonlist with 3 listItem, i need to give the table format to radiobuttonlist means it looks like table with row, how can i give, Any idea?

My code:-

<asp:RadioButtonList ID="RbAccount" runat="server" cssClass="Request">
<asp:ListItem Value="update" Selected="True">Change existing account </asp:ListItem>
<asp:ListItem Value="deprovision">Remove an account </asp:ListItem>
<asp:ListItem Value="new" Enabled="false">Create a new account </asp:ListItem>

If i am using this css style, it is giving me border all all the tables i have on page, i just want only this radiobuttonlist, Any idea ?

<style type="text/css">
    border:1px solid black;

Adding table to a page, but the format lost when I use clear border



I added a table to a page, by default, the table layout is the light border. So, I add my content and the vertical align seems to set to the top. But when I changed it to clear border, the content of the table will set vertical align to center. Is there a way to change this back to top align but with the clear border. I don't think I can do this by what is available in the ribbon. Please advice.

Showing available time slot in table format


I'm currently developing a system for booking of discussion room in a college. This system will allow the staff to help the students to make booking in advanced as well as for instant walk in usage.

I've got a table to keep track of bookings, another for walk in and one for time slot.

For the time slot table, it includes start time slot and end time slot, which is 30 minutes interval for each.

What i'm currently facing is that the system could not display the correct available time slot. I think it's the formula problem which I still can't get it solved.


If the booking time is 8.30am - 9.00am, I have no problem showing the 1st slot as N/A.

But if the time is 8.45am - 9.15am, the system only update the 1st slot as N/A while the 2nd slot still remain as AVAILABLE which is wrong. Because since the time ends at 9.15, I would like the slot 9.00am - 9.30am to be N/A as well.

This would be my code:

for (int x = 0; x < countTimeSlot; x++)
     if (int.Parse(arrEndTimeSlot[x].ToString()) > intBookedStartTime && int.Parse(

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Writing Webservice return values to table within SQL CLR

Visual Studio 2010/Sql Server 2005/ .net 3.5/ C# I have compiled and created assemblies and all works fine when all I do is .pipe.send.   I have changed it now to capture the values in local parameters so I can write to a sql table on the same server.  I have the connection to the database and the table created.  The code compiles fine but when I run it from sql I get the following error.  The code follows the error.   Msg 6522, Level 16, State 1, Procedure CventSP, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "CventSP": System.Data.SqlClient.SqlException: Incorrect syntax near '@eventtitle'. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StoredP

how to return records in squence of inner join table?

Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

Databinding to table that has a custom format

My problem is that I have a different format table for each section in my form. And I would like to bind data to these tables. A repeater I don't think is going to work. It would be great if I can do it like this:<table id="exampleOfWhatIWant" runat="server"> <tr> <td><%# Eval("data") %></td> <td><%# Eval("data") + 1 %></td> </tr> <tr> <td><%# Eval("data") + 2 %></td> </tr> <tr> <td><%# Eval("data") + 3 %></td> <td><%# Eval("data") + 4 %></td> <td><%# Eval("data") + 5 %></td> </tr> </table> Where the "+ 1" is the next record from that datasource --which is bound in code behind. Is there anyway, I can accomplish this without resorting to dynamically created tables (ugh).

return table from function (function has WITH common_table_expression )


I am trying to create following function which should return table but when I try to create it I get error message as "Select statements included within a function cannot return data to a client.". I am looking for workaround for this issue.



function dbo.GetCategoryIdTillBase(@Category_Id as char

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

Select Sql does not return the data eventhough the data does in particular table.


Hi Dude,

I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column .  So i have wrote the sql statment like in below.

SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718'

But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718').

When i execute the above select statement, there is no result for it. Please let me know anyone face the same problem? Please give a solution for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.

Thanks in Advance


Getting the values of drop down repeating table in info path

HI how can i get all the values of a drop down repeating table, using XPathNavigator please reply me!

if a value is not in a mssql table, return something also



I have kind of simple  question, but can't find anything usefull on the internet.

In my web application i have a sqldatasource control:

 <asp:SqlDataSource ID="sourceTrainingDate" runat="server"
    ConnectionString="<%$ ConnectionStrings:login_ConnectionString %>"
    ProviderName="<%$ ConnectionStrings:login_ConnectionString.ProviderName %>"
    SelectCommand="SELECT convert(varchar,convert(varchar,year(createtime))+'-'+convert(varchar,month(createtime))+'-'+convert(varchar,day(createtime))) as TrainingTime FROM [EmployeeTraining] where ([employee_id] = @employee_id)"
            <asp:Parameter Name="employee_id" Type="Int32" />


 protected void sourceTrainingDate_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

Error while merging: "Could not retrieve column info from table tablename" and Could not find stored


Hi i am getting following error message while merging. Can any one please help?

We are using sql server 2005
I have this problem in publisher.Gone throught his article http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/b0bbc330-91df-45d5-8dbf-e2df461a8005 but no luck.

The merge process could not retrieve column information for table 'dbo.tablename'. Verify that you have sufficient privileges on the database and retry the operation. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201016)
Get help: http://help/MSSQL_REPL-2147201016

Could not find stored procedure 'dbo.MSmerge_sel_sp_6481B06512B54C91FCC171C542E7'. (Source: MSSQLServer, Error number: 2812)
Get help: http://help/2812


How to return objects in XML format in WCF service



I have a very simple WCF web service, and define a simple class, "Employee". there is a method, GetActiveEmployees(), I'd like the method return XML format employees, so client call the method and then get XML, not object.

Is there any simple way to do it?


class Employee{

public FirstName{ get; set;}

public LastName{get;set;}



Event Handlers need to return the actual table, column and value of column and name of task which tr



Within SQL 2005 within the Event Handler tab I created a an Execute SQL Task with the following SQL statement; -

INSERT INTO dbo.CustomSSISLog (SourceName, SourceDescription, ErrorDescription,PackageName,ErrorCode,TaskName) VALUES (?,?,?,?,?,?)

In conjunction with using the Parameter Mapping tab to map parameters to system variables the above work's quite well. However the TaskName contains the name of the task which inserts the error record, I want it to contain the name of the Task (SSIS component) which triggered the error.

Also I need the error record to contain the actual table, column and value in the column which triggered the error, any ideas? There does not seem to be readily available system variables to do this.

Kind Regards,


If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds

How to change data format in result coming from SQL table


On one of my webpage i am displaying datalist in which i am using this label:

<asp:Label ID="nameLabel12a" runat="server" Text='<%# Eval("CreatedOn") %>' />

This label is displaying created on date along with time (10/18/2010 2:45:07 PM), but i would like to display it something like this:

7sec ago

20min ago

30min ago

23 hours ago

10/3/2010 (if the time is more than 24 hours then it should display the date)

how to accomplish this ? please guide me...

Return value when empty table


I have the following code that i call from the page behind code.

when no records are in the table i get an error message


conversion from type DBNULL to type Decimal is not valid

I would prefare if the procedure did not throw this error message and returned a value to notify me that no records found when zero records returned

Public Shared Function invoicetotal(ByVal CustEmail As String) As Decimal
        ' Dim selectcommand As New SqlCommand
        Dim CONNECTION As SqlConnection = MaintainDB.GetConnectionString
        Dim sel As String _
        = "SELECT sum(total) from invoices WHERE CustEmail = @CustEmail  or  @CustEmail =   " & -1
        Dim CMD As SqlCommand = New SqlCommand(sel, CONNECTION)
        If CustEmail = "" Then
            CMD.Parameters.AddWithValue("@CustEmail", -1)


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend