OSQL Utility

This article will explain and define the OSQL utilities ability to run scripts for administration and batch work when using Microsoft SQL Server.

Introduction

If you have repetitive tasks to run, multiple SQL servers to administer, or a very large sequence of commands to execute, then the OSQL utility may be the tool to use. OSQL is capable of running both scripts and interactive commands. It is started from the command line and can be executed manually or by a scheduled task. With over twenty-five switch commands, OSQL can usually be configured to execute, as your application requires. OSQL does not have a user interface. So many times, scripts are created in Query Analyzer, saved and then run by OSQL.

OSQL vs. ISQL and Query Analyzer

There is a great deal of overlap between ISQL and OSQL. Both support input scripts, output scripts, and most of the same switch arguments. OSQL has no interface. It will only accept a typed command line, or a saved script. In spite of this disadvantage, sometimes ISQL and Query Analyzer cannot accomplish the required task. Working with MSDE is one example. Query Analyzer is not included with the Microsoft Desktop Engine. When developing an application on MSDE, or needing to do MSDE administration, the OSQL utility is the only tool included. Another key difference between ISQL and OSQL is the base library each tool was built on. ISQL is developed on the DB Library, as opposed to OSQL being developed on ODBC. The DB Library works at the SQL 6.5 standard. This difference means ISQL, or any application developed on the DB Library, dose not support some of the new SQL 2000 features. The entire list of unsupported features can found in Books on Line under the title "Connecting Early Version Clients to SQL Server 2000." Some of the main limitations of ISQL include char and varchars defined greater than 255 bytes will be non accessible, big ints will be converted to decimals, sql_variants will be converted to nvarchars, XML results may not be retrieved, and bit fields that are null will be reported as not null with a value of 0. OSQL and Query Analyzer will support all of the SQL 2000 features.

OSQL ad hoc query examples

In these beginning tests, we will execute ad hoc queries from the command line. This first command assumes SQL server is local on your machine and you have Windows trusted connection, rather than a SQL uid and password. Open the command prompt and enter:

OSQL -E -Q "SELECT * FROM sysloings"

The contents of the syslogins table should scroll down the command window. In the above statement, -E tells OSQL to used a trusted connection rather than a SQL uid. The -Q is the query statement. Because no database was specified, master was used. To specify a database, change the statement to:

OSQL -E -d pubs -Q "SELECT * FROM authors"

The switches are case sensitive. Moreover, many times a lower case letter has no relation to an upper case letter. Lower case p is used for print performance statistics while an upper case P is used to specify a SQL password.
To use SQL security rather than Windows security, remove the -E and change the statement to:

OSQL -U sa -P secret  -d pubs -Q "SELECT * FROM authors"

OSQL scripts

In these next examples, we will create and save TSQL scripts, and then run them from OSQL. Query Analyzer is a standard choice for script creation because of the color coding. Open Query Analyzer and enter:

USE pubs GO SELECT * FROM authors GO 

Save this script to your hard drive, and then from the command line, enter an OSQL statement using the -i switch to specify an input file. The authors table should be returned.

OSQL -E -i c:\temp\q1.sql

The results of the query can be captured to an output file, rather than appearing on the screen. Change the command line to include the -o parameter, for output.

OSQL -E -i c:\temp\q1.sql -o c:\temp\resutls.txt

OSQL should create a text output file. The -u switch can be used to control the output file being either Unicode or OEM.

System Commands

Operating system commands can also be executed from inside the TSQL script. The key !! is used to specify this. Change the Query Analyzer script and save it as:

!! dir c:\temp GO USE pubs GO SELECT * FROM authors GO 

Now our output file will include the directory listing of the temp folder in addition to the authors' results. Also, note this script will not run in Query Analyzer. The !! directive is not supported. Query Analyzer color coding is helpful in code layout, but the script testing will need to be done from OSQL.

Error Handling

OSQL supports the RAISERROR command for returning custom error messages. To use raise error, the database name, id, error severity and state should be included. Using RAISERROR will cause the script to terminate. Modify the Query Analyzer script to:

!! dir c:\temp GO DECLARE @DBID int SET @DBID = DB_ID() DELCARE @DBNAME nvarchar(128) SET @DBNAME = DB_NAME() RAISERROR('my error', 18, 127, @DBID, @DBNAME) USE pubs GO SELECT * FROM authors GO 

Running the script will now output our directory listing, from the "!! dir c:\temp" command, followed by the raise error. The remaining script, changing to pubs and selecting from authors will not occur. RAISERROR will terminate the script.
Leaving a script can also be done by calling QUIT or EXIT from inside OSQL. Neither of these will return an error code, but EXIT can execute a statement prior to quitting. For example: EXIT(SELECT @@ROWCOUNT).

Conclusion

When administering the Microsoft Desktop Engine, OSQL is a free way to run statements. For standard SQL Server environments, OSQL can be used to help automate long or repetitive tasks by reusing scripts. OSQL is also a good choice to run database setup scripts during application install procedures.

Reference:-http://www.databasejournal.com/features/mssql/article.php/3403331/OSQL-Utility.htm by Don Schlichting 

LINQ TO TEXT AND LINQ TO CSV

Reference:-http://blogs.msdn.com/ericwhite/archive/2008/09/30/linq-to-text-and-linq-to-csv.aspx

LINQ is a great tool for writing ad-hoc queries and transforms, and occasionally I need to write queries or transforms on text files.  And sometimes I receive CSV files, and need to do something with them.  I wrote a blog post on LINQ to Text files over two years ago.  My coding practices today differ from what I presented in that blog post.  This post presents my current approach for dealing with text files using LINQ, and includes a function for splitting CSV lines.

In that post, I detailed an approach for writing lazy queries on text files.  Well, to tell you the truth, I never write lazy queries on text files – I simply use File.ReadAllLines and write queries over the returned string array.  After all, my computer has a lot of RAM (and probably yours does too), and the CSV files I receive are maybe in the 50K size, and are rarely greater than a few hundred K.  It simply doesn’t matter that I read the entire text file into memory.  If I were to write the exact same two queries today, here is how I would write them:

string[] lines = File.ReadAllLines("TextFile.txt");

var t1 = lines

    .Where(l => !l.StartsWith("#"))

    .Select(l => l.Split(','))

    .Select(items => String.Format("{0}{1}{2}",

        items[1].PadRight(16),

        items[2].PadRight(16),

        items[3].PadRight(16)));

var t2 = t1

    .Select(l => l.ToUpper());

foreach (var t in t2)

    Console.WriteLine(t);

 

I have a small extension method (CsvSplit) that I use to split lines that are in CSV format.  I’ve had this method around for a while – it’s not written in the functional style.  Instead, it’s a state machine.  I’ve thought about what it would take to rewrite this method in the functional style, and as far as I know, the only way to do it would be to define a grammar, and maybe write a recursive descent parser.  (Actually, there is another approach, but it would be very inefficient, and the code would be longer and less readable.)  Well the job of parsing CSV files simply isn’t worth the effort!  So a state machine it is.  In any case, the ‘functional impurity’ is local to the function.

The semantics of CsvSplit are:

• CsvSplit is an extension method on the String class.
• Only a comma is valid for the separator character.
• Values can be quoted.  The quotes are trimmed.
• Quoted values can have, of course, internal commas.  Quoted values can also have internal escape sequences: backslash followed by any character, including quote (\”), backslash (\\) or any other character (\a).
• CsvSplit will throw an exception for incorrectly formatted strings.

If the CSV file that I receive isn’t in this format, then I just load it into Excel and save so that it is in this format.

If you have this for a source file:

Bob,"Bob said to go to the store."

Mary,"Mary said, \"Whatever.\""

Jim,Jim's quote doesn't contain quotes or commas.

 

Then you can query the CSV file like this:

var data = File.ReadAllLines("TextFile.txt")

    .Select(

        l => {

            var split = l.CsvSplit();

            return new {

                Person = split[0],

                Quote = split[1]

            };

        }

    );

 

foreach (var item in data)

    Console.WriteLine("{0}:{1}", item.Person, item.Quote);

 

The function is composable.  If you want to convert the CSV file to an XML file, you can do so like this:

XElement xmlDoc = new XElement("Root",

    File.ReadAllLines("TextFile.txt")

        .Select

        (

            line => {

                var split = line.CsvSplit();

                return new XElement("Quote",

                    new XElement("Person", split[0]),

                    new XElement("Text", split[1])

                );

            }

        )

);

Console.WriteLine(xmlDoc);

 

Here is the listing for CsvSplit (also attached):

public class CsvParseException : Exception

{

    public CsvParseException(string message)

        : base(message)

    {

    }

}

 

public static class MyExtensions

{

    private enum State

    {

        AtBeginningOfToken,

        InNonQuotedToken,

        InQuotedToken,

        ExpectingComma,

        InEscapedCharacter

    };

 

    public static string[] CsvSplit(this String source)

    {

        List<string> splitString = new List<string>();

        List<int> slashesToRemove = null;

        State state = State.AtBeginningOfToken;

        char[] sourceCharArray = source.ToCharArray();

        int tokenStart = 0;

        int len = sourceCharArray.Length;

        for (int i = 0; i <>

        {

            switch (state)

            {

                case State.AtBeginningOfToken:

                    if (sourceCharArray[i] == '"')

                    {

                        state = State.InQuotedToken;

                        slashesToRemove = new List<int>();

                        continue;

                    }

                    if (sourceCharArray[i] == ',')

                    {

                        splitString.Add("");

                        tokenStart = i + 1;

                        continue;

                    }

                    state = State.InNonQuotedToken;

                    continue;

                case State.InNonQuotedToken:

                    if (sourceCharArray[i] == ',')

                    {

                        splitString.Add(

                            source.Substring(tokenStart, i - tokenStart));

                        state = State.AtBeginningOfToken;

                        tokenStart = i + 1;

                    }

                    continue;

                case State.InQuotedToken:

                    if (sourceCharArray[i] == '"')

                    {

                        state = State.ExpectingComma;

                        continue;

                    }

                    if (sourceCharArray[i] == '\\')

                    {

                        state = State.InEscapedCharacter;

                        slashesToRemove.Add(i - tokenStart);

                        continue;

                    }

                    continue;

                case State.ExpectingComma:

                    if (sourceCharArray[i] != ',')

                        throw new CsvParseException("Expecting comma");

                    string stringWithSlashes =

                        source.Substring(tokenStart, i - tokenStart);

                    foreach (int item in slashesToRemove.Reverse<int>())

                        stringWithSlashes =

                            stringWithSlashes.Remove(item, 1);

                    splitString.Add(

                        stringWithSlashes.Substring(1,

                            stringWithSlashes.Length - 2));

                    state = State.AtBeginningOfToken;

                    tokenStart = i + 1;

                    continue;

                case State.InEscapedCharacter:

                    state = State.InQuotedToken;

                    continue;

            }

        }

        switch (state)

        {

            case State.AtBeginningOfToken:

                splitString.Add("");

                return splitString.ToArray();

            case State.InNonQuotedToken:

                splitString.Add(

                    source.Substring(tokenStart,

                        source.Length - tokenStart));

                return splitString.ToArray();

            case State.InQuotedToken:

                throw new CsvParseException("Expecting ending quote");

            case State.ExpectingComma:

                string stringWithSlashes =

                    source.Substring(tokenStart, source.Length - tokenStart);

                foreach (int item in slashesToRemove.Reverse<int>())

                    stringWithSlashes = stringWithSlashes.Remove(item, 1);

                splitString.Add(

                    stringWithSlashes.Substring(1,

                        stringWithSlashes.Length - 2));

                return splitString.ToArray();

            case State.InEscapedCharacter:

                throw new CsvParseException("Expecting escaped character");

        }

        throw new CsvParseException("Unexpected error");

    }

}

The whole program is as follows:- LinqToCsv.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Xml.Linq;

 

namespace LinqToCsv

{

    public class CsvParseException : Exception

    {

        public CsvParseException(string message)

            : base(message)

        {

        }

    }

 

    public static class MyExtensions

    {

        private enum State

        {

            AtBeginningOfToken,

            InNonQuotedToken,

            InQuotedToken,

            ExpectingComma,

            InEscapedCharacter

        };

 

        public static string[] CsvSplit(this String source)

        {

            List<string> splitString = new List<string>();

            List<int> slashesToRemove = null;

            State state = State.AtBeginningOfToken;

            char[] sourceCharArray = source.ToCharArray();

            int tokenStart = 0;

            int len = sourceCharArray.Length;

            for (int i = 0; i <>

            {

                switch (state)

                {

                    case State.AtBeginningOfToken:

                        if (sourceCharArray[i] == '"')

                        {

                            state = State.InQuotedToken;

                            slashesToRemove = new List<int>();

                            continue;

                        }

                        if (sourceCharArray[i] == ',')

                        {

                            splitString.Add("");

                            tokenStart = i + 1;

                            continue;

                        }

                        state = State.InNonQuotedToken;

                        continue;

                    case State.InNonQuotedToken:

                        if (sourceCharArray[i] == ',')

                        {

                            splitString.Add(

                                source.Substring(tokenStart, i - tokenStart));

                            state = State.AtBeginningOfToken;

                            tokenStart = i + 1;

                        }

                        continue;

                    case State.InQuotedToken:

                        if (sourceCharArray[i] == '"')

                        {

                            state = State.ExpectingComma;

                            continue;

                        }

                        if (sourceCharArray[i] == '\\')

                        {

                            state = State.InEscapedCharacter;

                            slashesToRemove.Add(i - tokenStart);

                            continue;

                        }

                        continue;

                    case State.ExpectingComma:

                        if (sourceCharArray[i] != ',')

                            throw new CsvParseException("Expecting comma");

                        string stringWithSlashes =

                            source.Substring(tokenStart, i - tokenStart);

                        foreach (int item in slashesToRemove.Reverse<int>())

                            stringWithSlashes =

                                stringWithSlashes.Remove(item, 1);

                        splitString.Add(

                            stringWithSlashes.Substring(1,

                                stringWithSlashes.Length - 2));

                        state = State.AtBeginningOfToken;

                        tokenStart = i + 1;

                        continue;

                    case State.InEscapedCharacter:

                        state = State.InQuotedToken;

                        continue;

                }

            }

            switch (state)

            {

                case State.AtBeginningOfToken:

                    splitString.Add("");

                    return splitString.ToArray();

                case State.InNonQuotedToken:

                    splitString.Add(

                        source.Substring(tokenStart,

                            source.Length - tokenStart));

                    return splitString.ToArray();

                case State.InQuotedToken:

                    throw new CsvParseException("Expecting ending quote");

                case State.ExpectingComma:

                    string stringWithSlashes =

                        source.Substring(tokenStart, source.Length - tokenStart);

                    foreach (int item in slashesToRemove.Reverse<int>())

                        stringWithSlashes = stringWithSlashes.Remove(item, 1);

                    splitString.Add(

                        stringWithSlashes.Substring(1,

                            stringWithSlashes.Length - 2));

                    return splitString.ToArray();

                case State.InEscapedCharacter:

                    throw new CsvParseException("Expecting escaped character");

            }

            throw new CsvParseException("Unexpected error");

        }

    }

 

 

    class Program

    {

        static bool Validate(string[] results, string[] expectedResults)

        {

            if (results.Length != expectedResults.Length)

            {

                Console.WriteLine("  Validation error");

                return false;

            }

            for (int i = 0; i <>

            {

                if (results[i] != expectedResults[i])

                {

                    Console.WriteLine("  Validation error");

                    return false;

                }

            }

            Console.WriteLine("  Validated");

            return true;

        }

 

 

        static void ValidateAll()

        {

            string[] split;

 

            Console.WriteLine("Test1");

            split = "\"12\\\"3\",\"456\",\"789\"".CsvSplit();

            Validate(split, new[] { "12\"3", "456", "789" });

 

            Console.WriteLine("Test2");

            split = "\"123\",\"456\",\"789\"".CsvSplit();

            Validate(split, new[] { "123", "456", "789" });

 

            Console.WriteLine("Test3");

            split = "\"aaa,bbb\",\"ccc,ddd\",ghi".CsvSplit();

            Validate(split, new[] { "aaa,bbb", "ccc,ddd", "ghi" });

 

            Console.WriteLine("Test4");

            split = "aaa,,bbb".CsvSplit();

            Validate(split, new[] { "aaa", "", "bbb" });

 

            Console.WriteLine("Test5");

            try

            {

                split = "\"aaa\\bbb\",ccc,ddd".CsvSplit();

                Console.WriteLine("  Validation error");

            }

            catch (CsvParseException)

            {

                Console.WriteLine("  Validated");

            }

 

            Console.WriteLine("Test6");

            try

            {

                split = "\"aaabbb\"bbb,ccc,ddd".CsvSplit();

                Console.WriteLine("  Validation error");

            }

            catch (CsvParseException)

            {

                Console.WriteLine("  Validated");

            }

 

            Console.WriteLine("Test7");

            split = "aaa,,bbb,".CsvSplit();

            Validate(split, new[] { "aaa", "", "bbb", "" });

 

            Console.WriteLine("Test8");

            try

            {

                split = "\"aaabbb\",ccc,\"ddd".CsvSplit();

                Console.WriteLine("  Validation error");

            }

            catch (CsvParseException)

            {

                Console.WriteLine("  Validated");

            }

 

            Console.WriteLine("Test9");

            try

            {

                split = "aaa,ccc,\"ddd\\".CsvSplit();

                Console.WriteLine("  Validation error");

            }

            catch (CsvParseException)

            {

                Console.WriteLine("  Validated");

            }

 

            Console.WriteLine("Test10");

            split = "\"a\\\\aa\",,bbb,".CsvSplit();

            Validate(split, new[] { "a\\aa", "", "bbb", "" });

 

            Console.WriteLine("Test11");

            split = "\"a\\aa\",,bbb,".CsvSplit();

            Validate(split, new[] { "aaa", "", "bbb", "" });

        }

 

        ///

        /// Main Function

        ///

        /// Command Line arguments

        static void Main(string[] args)

        {

            XElement xmlDoc = new XElement("Root",

                File.ReadAllLines("TextFile.txt")

                    .Select

                    (

                        line =>

                        {

                            var split = line.CsvSplit();

                            return new XElement("Quote",

                                new XElement("Person", split[0]),

                                new XElement("Text", split[1])

                            );

                        }

                    )

            );

            Console.WriteLine(xmlDoc);

            ValidateAll();

        }

    }

}

Reference :- http://blogs.msdn.com/ericwhite/archive/2008/09/30/linq-to-text-and-linq-to-csv.aspx

Posted By :- EricWhite