SQL: Search Stored Procedure's Content

Having a bunch of stored procedure in a database could be a headache. Much more, is having a thousand lines of code in each stored procedure.

The line of SQL query statement below helps us to search all stored procedures, look into each line of code and return a stored procedure name that finds the value you’re looking for.

[SearchValue] is the value (in string format) you want to search. This can be any value such as file name, table name, another stored procedure name, action & the likes.

select name

from sys.procedures

where object_definition(object_id) like '%[SearchValue]%'

L.

Connect to the database in Classic ASP, vbscript

Connection String syntaxt for SQL Server:

Driver={SQL Native Client}; Server=SERVER_NAME;DBQ=DATABASE_NAME;UID=USERNAME; PWD=PASSWORD;

Sample:

Driver={SQL Native Client}; Server=testserver;DBQ=master;UID=aspnet; PWD=1234;
Take note that the PWD (password) is case sensitive.

Sample vbscript to connect to the database using the connectiong string above:

set cnUser = server.CreateObject("ADODB.Connection")

cnTrans.ConnectionString=GlobalConnectionString
cnTrans.Open

set rsUser = cnTrans.execute("Select username, firstname, lastname from Users")

iterate to the result set:

while not rsUser.eof

    response.write(rsUser("username")) ' get/show fieldname username

    rsUser.movenext                    ' move to the next record

wend

L.

Modify IIS Metabase

If you need to modify the IIS Metabase, you can do so by editing the Metabase.xml file directly using Microsoft Visual Studio or notepad. But before you can do so, you must enable the edit-while-running feature in IIS Manager. After your changes, don’t forget to disable the edit-while-running feature. To make sure that your changes were implemented, you might need to restart the server.

To modify the IIS Metabase, follow the following steps:

Enable the edit-while running feature of the metabase using IIS Manager:

1. In IIS Manager, right-click the local computer, and then click Properties.
2. Check the Enable Direct Metabase Edit check box
3. Click OK.

Modify the IIS metabase:

1. Open the Metabase.xml file in a text editor. The default path to this file is systemroot\system32\inetserv\metabase.xml
2. Modify the metabase properties that you wish to change in the Metabase.xml file.
3. Save the changes to the file, and close the text editor.

Disable the Edit-while running feature of the metabase using IIS Manager:

1. In IIS Manager, right-click the local computer, and then click Properties.
2. Uncheck the Enable Direct Metabase Edit check box
3. Click OK.

L.

LINQ: Get Random Record

There is no built-in function that returns a random record from the database using LINQ to SQL. One way is to create a user-defined function in a partial class of the data context.

public partial class DataContextNameHere : System.Data.Linq.DataContext
{

       //some code here

        [Function(Name = "NEWID", IsComposable = true)]
        public Guid Random()
        {
            throw new NotImplementedException();
        }

}

To use this method in your LINQ Statement, here is an example for a small number of records:

DataContext DbContext = new DataContext();

var result = (from r in DbContext.TableName

                orderby DbContext.Random()

                select r).FirstOrDefault();

 

 

For a large number of records, multiple queries is advisable to use, where it get the number of records and pick one at random.

DataContext DbContext = new DataContext();

var list = from r in DbContext.TableName

             select r;

int count = list.Count(); // get count here
int index = new Random().Next(count);

var result = list.Skip(index).FirstOrDefault(); // pick on here

L.