Aug 202009
 

Here is a way to execute a sql statement that returns one column of one row. If there is more than one row returned, it will return nothing.

        public static string CreateReader(string connectionString, string queryString)
        {
            string result="";
            int rowCount = 0;
            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString, connection);
                connection.Open();
                System.Data.OleDb.OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    rowCount = rowCount + 1;
                    result = reader[0].ToString();
                }
                reader.Close();
                return (rowCount == 0 || rowCount > 1) ? "" : result;
            }
        }

Aug 202009
 

I needed to use a select to return data, however the data resided via a call to a stored procedure.

For a simple example, I will use the the following stored procedure

exec sp_who 'sa'

original.sp_who.results

So how do I select * from a stored a procedure?

Use OPENROWSET

select *
from openrowset('sqloledb','Server=(local);TRUSTED_CONNECTION=YES','set fmtonly off exec master.sys.sp_who ''sa''')

which returns this result

selected.sp_who.results

Which means that I can filter what I want:

select *
from openrowset('sqloledb','Server=(local);TRUSTED_CONNECTION=YES','set fmtonly off exec master.sys.sp_who ''sa''') [x]
where x.status='sleeping'

subselected.sp_who.results

Aug 142009
 

After a recent MS update, remedy cheap when I attempted to create a new project in Visual Studio 2008, sickness drugs on the left side of the status bar, doctor it stated that the creation of the new project failed.

I followed the instructions from developers point and dot net slackers to no avail.

The solution was to simply repair the BizTalk Server 2009 installation.

A co-worker experienced this same problem, and repairing it did not fix it, you need to have C# installed for the BizTalk projects to be created successfully.