0

Command prompt from your current folder

Sometimes you need a command prompt to do stuff.  That works fine if folder is need isn’t buried too deep in windows.  For example, C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config.

Who wants to type all that out?  Well here’s a shortcut a co-worker told me about.

Just browse in windows explorer to the deep folder you need.  Then in the address bar type cmd.  Then boom a command prompt opens with the path set to your current path (versus C:\)

Makes is so much easier to run an executable.

0

Simple way to check if an AD account is locked

I use a lot of service accounts when I develop an application (for DB access, web service access, external api access).

Sometimes (with multiple hands in a project) a service account can become locked.  So a co-worker pointed me to a command to run on the command line to check if an account is locked.  It’s much easier then getting the AD tools installed or even waiting for them to startup (like if you have a big forest).

Just type this command and you will get a bunch of info about the account (like what groups they are in).  The account active will let you know if it locked or not.
Be aware that domain admins can lock this info down so you may not find out if the account is locked out or now.

net user /domain <username>

 

0

Quick way to transfer schema ownership of tables

For some reason my when I create new tables in my development environment all the tables have my username as the schema owner (instead of dbo).

So I came up with a quick script to reset all the tables to dbo.  I do this because it can cause problems if tables have different schema owners and someone doesn’t have access to see what’s in your schema (like another developer).

exec sp_MSforeachtable “ALTER SCHEMA dbo TRANSFER ?”

0

How to tell what processes are running on your SQL Server database

Sometimes operations (like drop, etc) will fail on your DB if someone running a command and locking a resource.

So I came up (or found somewhere) a query to tell me what’s currently running.

SELECT P.spid as SPID, P.kpid as KPID, P.dbid as DBID, D.name, convert(varchar, P.last_batch, 5) as StartDate, convert(varchar, P.last_batch, 8) as StartTime,
P.hostname as HostName, P.nt_domain as Domain, P.nt_username as Username,P.cmd as Command, P.waitresource, P.status as [Status],P.cpu as CPU,P.memusage as MEM,P.physical_io as IO
FROM sys.sysprocesses P
JOIN sys.databases D
ON P.dbid = D.database_id
ORDER BY D.name, P.cpu

This will return all the processes, sometimes you just want the processes for a specific user.  Then you just need to add a where clause.

WHERE nt_username = ‘<username>’

0

What’s the difference between maxRequestLength and maxAllowedContentLength

I don’t pay too much attention to what’s in the web.config so I always learn something when someone asks me how/why something works in the web.config.

When you see maxRequestLength (in system.web) and maxAllowedContentLength (in system.webServer) you ask why are 2 different attributes on length allowed.

They do serve different purposes.

maxRequestLength sets the max file upload size supported by ASP.NET.  The default size is 4096 KILOBYTES (4 MB).

maxAllowedContentLength sets the max length of content in a request supported by IIS. The default size is 30000000 BYTES (~28.6 MB).

NOTICE ONE IS IN KILOBYTES AND ONE IS IN BYTES!

Now why do you care about these settings.  If you are trying to upload large files (like images or documents) you need to be aware that you may need to adjust your maxRequestLength.  Then if files are really big you may need to adjust the maxAllowedContentLength.

What setting is the smallest takes precedence.

 

 

 

0

Script to check index fragmentation

Here is a script to check index fragmentation.  This is helpful if you need to determine if you need to rebuild indexes.

SELECT dbtables.[name] as ‘Table’,
dbindexes.[name] as ‘Index’,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

0

Using C# to interface with SQLite

If you need to interface with SQLite there are a couple of steps.

1) You need to get the .net provider for SQLite from sourceforge.net
2) Then add a reference to System.Data.SQLite to your project.
3) You need to make sure the reference is marked to be copied locally.

Here is a C# class to for doing select, insert, update, and delete.

public class SqlLiteHelper
    {
        String dbConnection;

        /// <summary>
        ///     Single Param Constructor for specifying the DB file.
        /// </summary>
        /// <param name="inputFile">The File containing the DB</param>
        public SqlLiteHelper(String inputFile)
        {
            dbConnection = String.Format("Data Source={0}", inputFile);
        }

        /// <summary>
        ///     Single Param Constructor for specifying advanced connection options.
        /// </summary>
        /// <param name="connectionOpts">A dictionary containing all desired options and their values</param>
        public SqlLiteHelper(Dictionary<String, String> connectionOpts)
        {
            String str = "";
            foreach (KeyValuePair<String, String> row in connectionOpts)
            {
                str += String.Format("{0}={1}; ", row.Key, row.Value);
            }
            str = str.Trim().Substring(0, str.Length - 1);
            dbConnection = str;
        }

        /// <summary>
        ///     Allows the programmer to run a query against the Database.
        /// </summary>
        /// <param name="sql">The SQL to run</param>
        /// <returns>A DataTable containing the result set.</returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();
                    using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        using (SQLiteDataReader reader = mycommand.ExecuteReader())
                        {
                            dt.Load(reader);
                            reader.Close();
                        }
                    }
                    cnn.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

        /// <summary>
        ///     Allows the programmer to interact with the database for purposes other than a query.
        /// </summary>
        /// <param name="sql">The SQL to be run.</param>
        /// <returns>An Integer containing the number of rows updated.</returns>
        public int ExecuteNonQuery(string sql)
        {
            int rowsUpdated = 0;

            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    rowsUpdated = mycommand.ExecuteNonQuery();
                }
                cnn.Close();
            }
            return rowsUpdated;
        }

        /// <summary>
        ///     Allows the programmer to retrieve single items from the DB.
        /// </summary>
        /// <param name="sql">The query to run.</param>
        /// <returns>A string.</returns>
        public string ExecuteScalar(string sql)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
            {
                cnn.Open();
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    mycommand.CommandText = sql;
                    object value = mycommand.ExecuteScalar();
                    cnn.Close();
                    if (value != null)
                    {
                        return value.ToString();
                    }
                }
            }
            return "";
        }

        /// <summary>
        ///     Allows the programmer to easily update rows in the DB.
        /// </summary>
        /// <param name="tableName">The table to update.</param>
        /// <param name="data">A dictionary containing Column names and their new values.</param>
        /// <param name="where">The where clause for the update statement.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Update(String tableName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete rows from the DB.
        /// </summary>
        /// <param name="tableName">The table from which to delete.</param>
        /// <param name="where">The where clause for the delete.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Delete(String tableName, String where)
        {
            Boolean returnCode = true;
            try
            {
                this.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily insert into the DB
        /// </summary>
        /// <param name="tableName">The table into which we insert the data.</param>
        /// <param name="data">A dictionary containing the column names and data for the insert.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception ex)
            {
                returnCode = false;
            }
            return returnCode;
        }

        /// <summary>
        ///     Allows the programmer to easily delete all data from the DB.
        /// </summary>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearDB()
        {
            DataTable tables;
            try
            {
                tables = this.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;");
                foreach (DataRow table in tables.Rows)
                {
                    this.ClearTable(table["NAME"].ToString());
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        ///     Allows the user to easily clear all data from a specific table.
        /// </summary>
        /// <param name="table">The name of the table to clear.</param>
        /// <returns>A boolean true or false to signify success or failure.</returns>
        public bool ClearTable(String table)
        {
            try
            {

                this.ExecuteNonQuery(String.Format("delete from {0};", table));
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
[tweetmeme only_single=”false”]
0

How to get a list of all identity columns in a database

Here is some code to get a list of identity columns in database.

select  so.name as TableName,  + o.list as IdentityColumnName 
from    sysobjects so
cross apply
    (SELECT 
        column_name
         + 
           case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        ''
        end + ' '
     from information_schema.columns where table_name = so.name
    ) o (list)
where   xtype = 'U'
AND o.list is not null
AND name    NOT IN ('dtproperties')
[tweetmeme only_single=”false”]