DTS.DLL

          Well, who would have thought. I mean really Micr*soft. Really? What the crap!?  Here is what I am referring to. I recently updated our servers with Sql Server 2005 SP3. We updated our development server. It went without a hitch. We updated our test server. Again, went without a hitch. Our test server more resembles our production server. So I update our production server. Does it work like it’s supposed to? NO! After updating to SP3 for SQL Server 2005, the jobs broke. I first looked at permissions for a while. Nothing changed as far a permissions go, but I know where I work things are changed on the intranet without our knowledge. Kind of a communication breakdown in my opinion. Just sayin. Anyway, it turns out that there was a .dll that was (unregistered?). Is there such a thing? Apparently so. After looking into the issue for quit some time I found that the DTS.DLL needed to be re-registered. Go figure. Thanks Micr*soft! Great going! Make my life easy why don’t you. You need to run the below command at the c:\ prompt:

regsvr32 dts.dll.

I ran that command and it fixed my issue. Why it didnt break on development or test environment is beyond me.

Have fun and as always, if there are any questions or suggestions they are welcome. Thank you.

Grabbing RDL from Server to Edit

I had a report that was working on on server but not inside my studio environment.  I had changed it too much.  I wanted to go back and start with the report that worked.  This is what I did:

In Report Manager, I clicked Edit in the Properties>General tab for the
given report.  You should get a File Download window; select Save and
put the rdl in the folder where you want it.  I over wrote the file I was working on.  You should probably close Visual Studio before you do this so there are no issues.  Another way to have done it is to save the RDL file someplace else and then  import it by right clicking on Reports then select Add>Add new item then pick your RDL.  You should then be able to open it in Designer.

Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are always welcome. Thank you.

SQL Server 2005 Reporting Service (SSRS) EnableClientPrinting

This post was originally on a friend of mine blog: C# Conspiracy. I wanted to move it here and make sure he got his due recognition. The link to his site is over to the right under Blogroll.  Now for the verbaige.

So I received a new requirement to disable the client-side printing of the reports in my application. The reports are generated in SSRS which manages the client printing control via the toolbar displayed at the top of the report page. No problem I declared.

Unfortunately I did not realize that the SSRS server I inherited did not have an account with administrator privileges. Apparently the builtin\adminstrators account has been removed and an admin account wasn’t added to replace it. (I’m still looking for the means of adding a new system administrator to the service.) In the meantime I was able to disable the client-side printing by setting the value column of the EnableClientPrinting row of the configurationinfo table in the reportserver database to false from true. The configurationInfo table maintains a copy of several of the parameters for the report service that you can manually change if you have access to the db. Glad I found this workaround! Still looking to get back admin…

Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are always welcome. Thank you.

Excel to Access to SQL Server

    I recently found that it is not easy to import external data from Excel to Access.  The version I am referring to is Office 2003.  I was trying to import some data into an access database to make it easier to import into SQL Server.  What I found was its not easy either way (that is until I figured out a pretty easy way to do it).  What happens is Microsoft tries to figure out what you want to do with the data. Well the way things are figured out from Excel to Access is the first field that is not a header, determines the data type of the field.  That is all find and dandy if all the columns are the same type. What happens if they are not. What happens if I have say row 1 contains a number, row 2 contains a number, and row 3 contained a character or even something like a 001? I’ll tell you.  Your import fails.  Just fails.  An (what I think is easy) way to import such data is to make the first row that is not the header a character (I use ‘A’). Then no matter what is in the row, it will come in fine. The only thing you have to do is remember to delete that row of ‘A’s when the import is finished. Works like a charm. I have used it more than twice.

        Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are always welcome. Thank you.

Comma Delimited List From SQL Server

Have you ever needed a list of values from a database based on a join? I have and this is what I have found. What I was trying to do was to return two rows from a database, ID and values. I had a table that housed an ID. This ID could be singular or multiple (i.e. 1 or 1,2,3). I needed to find the values for each of those ID’s. Here is what I came up with:

SELECT ID,
(select stuff((SELECT distinct ',' + value as [text()]
FROM tablename
WHERE ID in (SELECT * FROM ss.ID))
ORDER BY ',' + value
FOR xml path('')),1,1,'')) as 'MultiValue'
FROM tablename1 ss
WHERE ss.Value …

This will return results that start like this:
ID VALUE
1 value1
2 value2
3,4,5 value3,value4,value5

All you have to do is make sure you replace the values I put in there with your tables and column names. Make sure your consistent as the result will not be what you expect if not. With that you can fill in a grid view or a spreadsheet with the comma delimited results. Works like a charm.

Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are always welcome. Thank you.

Data Access Layer

 I work on a team. We have standards I have put in place.  One aspect of those standards includes the data access layer (DAL).   What this does for someone is separate the database connection from the rest of the code. Here is how we set it up and use it. First we have the web.config. In there we have our connection string:

<add connectionString="Data Source=55.55.55.55;
Initial Catalog=Databasename;User; Pwd=password;
Connect Timeout=1000"/>

From there we want to create the class file to hold all our database calls. I have a file called DAL.cs in my App_Code folder. Inside the class file I have the connection string acquisition:

#region Connection string aquistition
    public class Database
    {
        static public String ConnectionString
        {
            get
            {    // get connection string with name database from  web.config
                return ConfigurationManager.ConnectionStrings["dsn"].ConnectionString;
            }
        }
    }
#endregion

On a side note you can see the use of #region above.  That makes the code more vision friendly in my opinion. Instead of having all lines of code showing on the whole page where you have to scroll all over the place, you can wrap each code segment with the #region and minimize the segment. Works very cool. Looks good also.

Now, back to what we were talking about. We created our connection string acquisition and now we need to put it to use:

#region Dynamic StoredProc
    public DataTable get_sProc(string qryStr)
    {
        try
        {
            //create command Object
            SqlCommand sqlCommand = new SqlCommand("dbo.sProc_MyQuery");
            sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@MyQuery";
            param.Value = qryStr;
            sqlCommand.Parameters.Add(param);
            // create Sql Connection (using above ConnectionString class)
            using (SqlConnection myConnection = new SqlConnection(Database.ConnectionString))
            {
                sqlCommand.Connection = myConnection;
                SqlDataAdapter da_regions = new SqlDataAdapter(sqlCommand);
                //Instantiate and populate the DataTable
                DataTable DataTable = new DataTable();
                da_regions.Fill(DataTable);
                return DataTable;
            }
        }
 // something went wrong
        catch (Exception ex)
        {
            return null;
        }
    }
#endregion

Notice the use of #region. Anyway, that’s how you would call it. Now if you need to change the database server you can with ease. If you need to change the connection string, you can with ease without effecting the application. I am all for standards. They make the work place a better place to work. Next I will go into how to consume the class to display the queried information. Hope this helped someone.  Have fun coding and as always, if there are any questions or suggestions, they are welcome.  Thank you.

C# Using

I know it has been a while. I am here to start up again and keep up this time. Now onto the topic.
I found that coding in ASP.NET C# IS AWESOME! It is way better than VB. One thing I found by trial and error was the use of using. let me give you an example of what I am talking about. You create your data access layer, you update your web.config and you get ready to all your stored procedures. What you need to do is use this:
//create command Object
SqlCommand sqlCommand = new SqlCommand("dbo.sProc_GetSomething");
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter param_variable = new SqlParameter();
param_variable.ParameterName = "@Variable";
param_variable.Value = udo_id;
sqlCommand.Parameters.Add(param_variable);

// create Sql Connection
using (SqlConnection myConnection = new SqlConnection(Database.ConnectionString))
{
sqlCommand.Connection = myConnection;
SqlDataAdapter da_regions = new SqlDataAdapter(sqlCommand);
//Instantiate and populate the DataTable
DataTable DataTable = new DataTable();
da_regions.Fill(DataTable);
return DataTable;
}

By using this code you automatically open and close the SQL connection. It’s done automatically. The way/reason this was found is that I was explicitly using conn.open and conn.close. Well I missed some closing and wreaked havoc on our database. Shame on me. BUT, this turned out to be a better way of doing it anyhow in my opinion. Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are welcome. Thank you.

Working with a Database in ASP.NET VB

I just realized I added code on the blog for working with databases in C# but not for VB.  This will be the missing post of one way in which you can use Visual Basic. Enjoy!

Imports System.Data

Imports System.Data.SqlClient

Dim cs As String = “Data Source=.\SQLEXPRESS;” + _

Dim cs As String = “Initial Catalog=NamesDB;” + _

Dim cs As String = “Integrated Security=True;”

Using con As New SqlConnection(cs)

con.Open()

‘ insert a record

sql = “INSERT INTO Names(Name) VALUES(@Name)”

Dim cmd1 As New SqlCommand(sql, con)

cmd1.Parameters.Add(“@Name”, SqlDbType.NVarChar, 100)

cmd1.Parameters(“@Name”).Value = “Bob”

cmd1.ExecuteNonQuery()

‘ insert a second record

cmd1.Parameters(“@Name”).Value = “David”

cmd1.ExecuteNonQuery()

‘ read records

sql = “SELECT * FROM Names”

Dim cmd2 As New SqlCommand(sql, con)

Using r As SqlDataReader = cmd2.ExecuteReader()

Dim iName As Integer = r.GetOrdinal(“Name”)

Do While r.Read()

If r.IsDbNull(iName) Then

Console.WriteLine(“Null”)

Else

Console.WriteLine(r.GetString(iName))

End If

Loop

End Using

‘ read a single value

sql = “SELECT TOP 1 Name FROM Names”

Dim cmd3 As New SqlCommand(sql, con)

Console.WriteLine(cmd3.ExecuteScalar())

End Using

 

(This post was done with the help of LearnVisualStudio.net. Thanks!)

In a future post I will be sure to add a post about how to use stored procedures! Hope this helps you.  Have fun coding and as always, if there are any questions or suggestions, they are welcome.  Thank you.