Mystic Slayer's Bloggie

Timestamp in SQL Server:

1) The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

2) It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

For a customer we made some changes. And the situation is as follows. We exported the data to excel. Multiple tabels are exported. We changed the data in the excel file and the customer validated the data. We also did export the timetamp with the data. Why? Because we had to import it again to the database. If a Timestamp changed, we should not update the data from the excel file. That's why.

Problem situation:

Because a timestamp isn't a varchar, or numeric value, but a binary(8) value we had to convert it. Because we import the data by SSIS back into the database. But the data we imported is from a flat text file. So every column was read as a DT_STR by SSIS, and timestamp in the stored procedure was a datatype of Timestamp. So we did a comparison of a varchar against a timestamp. Well that situation didn't work out that well.

So what we did was to change the parameter of the stored procedure to varchar. Well SQL Server can't handle a comparison of varchar against timestamp inside the stored procedure. Of course, so we did try it to convert it to a varbinary(8) or binary(8) and then validate it against the timestamp. Well that didn't work out that well.

declare @timestamp varbinary(8)
set @timestamp = CONVERT(varbinary(8), '0x0000000001F4AD88')
print @timestamp

returned: 0x3078303030303030

declare @timestamp varbinary(8)
set @timestamp = CONVERT(varbinary(8), 0x0000000001F4AD88)

print @timestamp

returned: 0x0000000001F4AD88

That comparison didn't work well. So I had to figure an other way to validate on timestamp. When I almost lost my hope I wrote a function in SQL Server.

create function [dbo].[sp_hexadecimal] ( @var varbinary(255) )
returns varchar(255)
as

begin
      declare @charval varchar(255)
      declare @i int
      declare @length int
      declare @hex char(16)

      select @charval = '0x'
      select @i = 1
      select @length = datalength(@var)
      select @hex = '0123456789abcdef'

      while (@i <= @length)
      begin
            declare @tempint int
            declare @firstint int
            declare @secondint int

            select @tempint = convert(int, substring(@var,@i,1))
            select @firstint = floor(@tempint/16)
            select @secondint = @tempint - (@firstint*16)
            select @charval = @charval +
            substring(@hex, @firstint+1, 1) +
            substring(@hex, @secondint+1, 1)

            select @i = @i + 1
      end

return ( @charval )
end

So what I did was the other. I managed to validate the data on the other way.

it did the trick...

I've been working for the first time with a DataFlow of updating data in a SQL Database with SSIS. The only problem is that a colleaque did made it, but he forget something with it. After reading some documentation about the DataFlow in SSIS and the using of a OLE DB command to execute a Stored Procedure I came to a certain problem.

The problem is that a OLE DB Command can execute a Stored Procedure, but the error handling is really a issue. Why, you can give a output parameter with it, but for some reason it doesn't work well. The question is why they didn't use a SQL Task to execute a Stored Procedure. Now I'm searching why my stored procedure isn't fired well.

Well the problem is that the stored procedure contains a error handling not supported by the OLE DB Command. And when you have error handling handled by you SSIS you get a Error Number 0 returned. So your searching for a problem with Error number 0? That's really crap. So I get rid of the Error handling in the stored procedure, and well the funny thing is that the stored procedure really is fired well and with filled parameters. But another problem is now that there are not enough parameters filled to execute the Stored Procedure properly.

So I have to search for that issue to solve the problem....

Well if you use third party web controls like Telerik or Infragistics you can have performance issues. It's running like an old crappy site.

To overcome this and you use Javascript or it will be rendered by Javascript you can enhance it by writing your Javascript in one single line.

Check this site for Bookmarklets

Well I was searching for a Stored Procedure that you can use for reindexing and updating stats, etc.

I didn't find any nice procedures that gave me the solution I needed, so I've read some posts by google, and made the following procedure

This procedure will loop through all the tables and all indexes and shall Reindex them. When every index on every table is reindexed it will update all the stats on the database.

Of course you can choose to change the fill index from 80 to any other valid percentage.

 
 
CREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC SP_UPDATESTATS
GO
END
 

Well I know that for VS 2005 or VS 2008 can maintain your web or app configuration file with alot of easy. Well adding shouldn’t be a harsh, but updating still is a somekind of problem. Allright, normally you have a static connectionstring for your (web)application. Well the company that I work for has an application that isn’t static at all.

Everything is dynamic, even the connectionstring. You know why? Well the application that someone made was used for multiple databases. And it is used for importing data. You can change the connectionstring by editing the web or app config. But that wasn’t an option. The people that use this application should change the connectionstring by the application itself.

Allright, the fellow that created this nice application wasn’t aware of the connectionstring configsection. So he didn’t used that part, but I said, well if you change a connectionstring, you can maintain it by ease with code. So I had to write two simple functions. Adding and updating of connectionstrings.

The first part of the code is adding the connectionstring. Well I used this code the Windows Application so I didn’t include any for web applications.

Well opening of the configuration file is used by the configurationmanager. The ConfigurationManager.OpenExeConfiguration is used for opening the app.config, with ConfigurationUserLevel none. So no user level is needed.

Then you take out the ConnectionString section.

/// <summary>
        /// Add a connection string to the connection
        /// strings section and store it in the
        /// configuration file.
        /// </summary>
        /// <param name="csName">The name of the property.</param>
        /// <param name="connectionString">The connectionstring as specified.</param>
        public static void AddConnectionStrings(string csName, string connectionString)
        {
 
            // Get the configuration file.
            System.Configuration.Configuration config =
                ConfigurationManager.OpenExeConfiguration(
                ConfigurationUserLevel.None);
 
            // Add the connection string.
            ConnectionStringsSection csSection = config.ConnectionStrings;
            csSection.ConnectionStrings.Add(
                new ConnectionStringSettings(csName,
                    connectionString, "System.Data.SqlClient"));
 
            // Save the configuration file.
            config.Save(ConfigurationSaveMode.Full);
        }

Well for updating it was hard to find any usefull code to update a connectionstring. It wasn’t possible to update a connectionstring, so I had the remove the connectionstring, and add a new one. Well isn’t that odd?? Microsoft left something undone… Well maybe it isn’t usefull to change your app.config, but well, I had to make it anyhow. So dynamic app.config or web.config files is little bit strange, because you can’t dynamically update it. No you have to remove it first by the configurationmanager.

<pre lang="csharp">

        /// <summary>
        /// First remove the old connectionstring and after that
        /// add a connection string to the connectionstrings
        /// section and store it in the configuration file.
        /// </summary>
        /// <param name="csName">The name of the property.</param>
        /// <param name="connectionString">The connectionstring as specified.</param>
        public static void UpdateConnectionStrings(string csName, string connectionString)
        {
            // Get the configuration file
            System.Configuration.Configuration config =
                ConfigurationManager.OpenExeConfiguration(
                ConfigurationUserLevel.None);
 
            // Remove the existing connectionstring.
            config.ConnectionStrings.ConnectionStrings.Remove(csName);
            // Add the connectionstring
            ConnectionStringsSection csSection = config.ConnectionStrings;
            csSection.ConnectionStrings.Add(
                new ConnectionStringSettings(csName,
                connectionString, "System.Data.SqlClient"));
 
            // Save the configuration file
            config.Save(ConfigurationSaveMode.Full);
        }
</pre>

In part 1 we started to explain a couple of things in our Excel xml spreadsheet. So let’s start how to explain it in code.

First of all we need a StringBuilder. With a StringBuilder we can append all the data we want. Let’s build a couple if simple functions. Everything is written in C#, because it’s a common written language in .NET. So if you would like to write in VB.NET you have to convert it to VB.Net.

First we start with the header of our Xml Spreadsheet:

So we have written our first part of our spreadsheet header. Let’s see what we’ve got now. Ah yes. Now our DocumentProperties we can write in our stringbuilder.

So we have written now our DocumentProperties. Now we can write our ExcelWorkbook properties. There are a couple of things important. Don’t forget to put ProtectStructure and ProtectWindows in your ExcelWorkbook properties. These are important things we need to use.

We have done our first part of our document. Now we can see allready that we have done alot of work to write our header for our document. Let’s start with our Styles. You can add styles in a couple of manners to your document. You can write an external document that we can add to our project. Or you can add this by code. Well like you write a stylesheet for your webpage, I wrote a styles.config to add to our project.

You can add this in by the following code part.

This was the part for reading our styles.config into a StreamReader and return it as string. Well we have done the most of things now. Let’s start with the main part. The main part is making the ExcelWorksheet and the table with data. In Html you can write a table like this:

Well the xml spreadsheet is almost the same. Let’s see how to make a simple template of it.

There are not many differences between html and xml spreadsheet. But it’s important to know that you don’t forget any closing tags. In particular big documents it’s a problem to find the problem. Well let’s start with some simple data to put it in our document.

On part 3 we’re going further. Cause the fact is that Wordpress doesn’t allow me to write alot of text in one article…

If you want to export data with VB.NET of C# you can do it with the Office API’s delivered with your Office API’s, but I wasn’t to happy with the Excel Object. After doing some research on the internet I discovered alot of ways to write your own manner to Generate an Excel document with the XmlSpreadsheet. Well after doing some tryouts with Excel I discovered that Excel has alot of options how to save your data.

• Excel Workbook (*.xlsx) Office 2007 Format
• Excel Macro-Enabled Workbook (*.xlsm) Office 2007 Format
• Excel Binary Workbook (*.xlsb) Office 2007 Format
• Excel 97-2003 Workbook (*.xls)
• XML Data (*.xml)
• Single File Web Page (*.mht; *.mhtml)
• Web Page (*.htm; *.html)
• Excel Template (*.xltx) Office 2007 Format
• Excel Macro-Enabled Template (*.xltm) Office 2007 Format
• Excel 97-2003 Template (*.xlt)
• Text (Tab Delimited) (*.txt)
• Unicode Text (*.txt)
• XML Spreadsheet (*.xml)
• Microsoft Excel 5.0/95 Workbook (*.xls)
• CSV (Comma Delimited) (*.csv)
• Formatted Text (Space Delimited) (*.prn)
• Text (Macintosh) (*.txt)
• Text (MS-DOS) (*.txt)
• CSV (Macintosh) (*.csv)
• CSV (MS-DOS) (*.csv)
• DIF (Data Interchange Format) (*.dif)
• SYLK (Symbolic Link) (*.slk)
• Excel Add-In (*.xlam) Office 2007 Format
• Excel 97-2003 Add-In (*.xla)

You’ll see that the XML Spreadsheet normally would be saved in the Extension of *.xml, however you can save this also as *.xls. Why is that? You would ask? Well Excel has alot of manners how to open or save data. If you would have a CSV file delimited in Comma’s or Tab delimited files you can also open this with Excel. And you can see that the data is perfectly rendered in the Spreadsheet.

Let’s see where a XML SpreadSheet starts with:

After this section you will include you styles. When writing your styles you can build a Excel Workbook manually and make some different styles init. After that you can save this Workbook to a XML Spreadsheet. When you finally saved a Workbook it will generate for example the following styles:

What you can do is giving alot of extra options to your excel sheet. Every above style has a particular function. You can specify a column and give it a specific style. So that the column will be formatted by for example Scientific functions. After the styles there will be the data for your spreadsheet.

First we start with a new Worksheet. Every worksheet has a name . Let’s start with that:

When we have done that we can add our table with data in our Worksheet. It will look like this.