Today we faced the following problem with Atomic CMS (content management system): when creating page in admin section it was not possible to save text > 4000 characters. SQL Server had this limit in old versions, but SQL Server 2008 does not have this problem if you declare column as nvarchar(max), in this case the length of the data can be 2^31-1 bytes

We double checked if the column in database has correct type, and yes, it was nvarchar(max), but we still have 4000 limit.

The source of the problem was found in about an hour, it was wrong NHibernate configuration (Atomic CMS use NHibernate to access database). By default NHibernate does not allow write in database and read back strings with length greater than 4000.

The fix was quick, we have to set length attribute for the PageContent property

<property name="PageContent" column="PageContent" type="String" length="2147483647"/>

Often you have to create a simple pages for your website, for example admin pages which allow you edit tables through web. When working with Ms Sql server it’s a very easy task which you can solve with SqlDataSource and DataGrid. But using Oracle it’s difficult to make your page work without exceptions and you have to know some tricks for using Oracle with SqlDataSource.

To develop webpage you can start with user interface. Put SqlDataSource and DataGrid on the page, and add css class if needed.

Than you need to add settings to SqlDataSource to select data from database using SelectCommand. And now you can have data inside of DataGrid.

To make Grid editable you have to define UpdateCommand which in this case contains the name of stored procedure which I am using to update table:

CREATE OR REPLACE PACKAGE HEATMAP.orm_web_support
AS
   PROCEDURE update_stream_lookup (
      stream_id_     public_stream_lookup.stream_id%TYPE,
      stream_name_   public_stream_lookup.stream_name%TYPE,
      is_visible_    public_stream_lookup.is_visible%TYPE
   );
END orm_web_support;
/ 

CREATE OR REPLACE PACKAGE BODY HEATMAP.orm_web_support
AS
   PROCEDURE update_stream_lookup (
      stream_id_     public_stream_lookup.stream_id%TYPE,
      stream_name_   public_stream_lookup.stream_name%TYPE,
      is_visible_    public_stream_lookup.is_visible%TYPE
   )
   AS
   BEGIN
      UPDATE public_stream_lookup
         SET stream_name = stream_name_,
             is_visible = is_visible_,
             last_modified_date = SYSDATE
       WHERE stream_id = stream_id_;
   END;
END orm_web_support;
/ 

It’s a simple Sql to update table, but I was not able to make page work without stored procedure.

You need to provide parameters for UpdateCommand inside of SqlDataSource:

<UpdateParameters>
    <asp:Parameter Name="STREAM_ID" Type="Int32" />
    <asp:Parameter Name="STREAM_NAME" Type="String" />
    <asp:Parameter Name="IS_VISIBLE" Type="String" />
</UpdateParameters> 

Now if you try to run application you will get Exception:

PLS-00306: wrong number or types of arguments in call to ‘UPDATE_STREAM_LOOKUP’

To make UpdateCommand work in OnUpdating event for DataSource you have to initialize parameters if you need, or add some parameters, usually it’s username who make changes or something like this. The number and the names of parameters must be the same as in Oracle stored procedure, so you have to add underscore at the and of the parameters.

Why do you need to add underscore? The thing is, in UpdateParameter you need to have the same names as in stored procedure, but you can’t change parameter names inside of your SqlDataSource, because they will not be initialized, so you have to change names inside of OnUpdating event. And the easiest way is use underscore for new parameter names.

protected void SqlStreams_OnUpdating(object sender, SqlDataSourceCommandEventArgs e)
{
    foreach (DbParameter param in e.Command.Parameters)
    {
        param.ParameterName = param.ParameterName + "_";
    }
} 

And now you can change data in table

A couple of days ago I bought a virtual server from Godaddy and the first application than I have to install was MS SQL Server 2008 R2 Express. But something was wrong, every time I tried to install SQL Server 2008 express I had the same error message is not a valid login or you do not have permission.

TITLE: Microsoft SQL Server 2008 R2 Setup
------------------------------
The following error has occurred:
'' is not a valid login or you do not have permission.
------------------------------

This error message was meaningful, because I installed it using my account with has administrator access.

I tried to remove and reinstall SQL Server, I tried different versions of SQL Server, but nothing helped.

But finally I found a solution how to avoid this error message is not a valid login or you do not have permission

By default godaddy virtual server has the same name as user account and this was the reason of error message.

I renamed my server and error disappeared and I was able to install SQL Server Express without errors.

For some simple pages in my application I prefer to use declarative manner of programming. To display some data on the page you can use this lines of code.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ProviderName="System.Data.OracleClient"
    SelectCommand="pac_dwh.Revew_item_info.GetAppPermissionInfoExt"
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:QueryStringParameter Name="p_line_id"
                  QueryStringField="lineId" />
        <asp:Parameter Name="p_res" Direction="Output" />
    </SelectParameters>
</asp:SqlDataSource>
<asp:FormView ID="FormViewContent" runat="server"
          DataSourceID="SqlDataSource1" >
        <ItemTemplate>
            <%# Eval("profile") %>
            <%# Eval("application_description") %>
        </ItemTemplate>
</asp:FormView>

But if you are using Oracle database you will get the error

System.Exception: Parameter ‘p_res’: No size set for variable length data type: String.

It’s because you can’t define type of parameter in DataSource, because it’s a Cursor, and Direction has no this type of data.

The solution is very simple, you just need to set datatype for the output parameter in OnSelecting event, like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ProviderName="System.Data.OracleClient"
SelectCommand="pac_dwh.Revew_item_info.GetAppPermissionInfoExt"
SelectCommandType="StoredProcedure"
OnSelecting="FixOracleRefCursorProblem_SqlDataSource1_Selecting">
    <SelectParameters>
        <asp:QueryStringParameter Name="p_line_id"
                  QueryStringField="lineId" />
        <asp:Parameter Name="p_res" Direction="Output" />
    </SelectParameters>
</asp:SqlDataSource>

and in cs file:

protected void FixOracleRefCursorProblem_SqlDataSource1_Selecting(object sender,
   SqlDataSourceSelectingEventArgs e)
{
    ((System.Data.OracleClient.OracleParameter)
    e.Command.Parameters[1]).OracleType = OracleType.Cursor;
}

And now it works

I started a new contract last week, and the first task for me was to change column datatype from int to bigint in Microsoft SQL Server.

Also I have to provide database scripts for this changes, so this changes can be implemented in all databases than we have in our project.

Change column datatype is very easy task, you have to run T-SQL script like this:

ALTER TABLE your_table_name ALTER COLUMN your_column_name new_data_type

For example for my table it looks like:

ALTER TABLE dbo.Account ALTER COLUMN AccountNumber bigint;

The first problem with changing datatype was because of indexes. If you have an index on your column you can’t change column type because it has references. I get this error message:

Msg 5074, Level 16, State 1, Line 1
The index ‘SK_Account_AccountNumber’ is dependent on column ‘AccountNumber’.
ALTER TABLE ALTER COLUMN AccountNumber failed because one or more objects access this column.

So at first I have to remove index, change column type and then create index again.

How to remove index in MS SQL Server

To remove index I can use Drop index statement. The best way is to click on index with the right mouse button, select “Script index as” and “Drop to”

But do not forget to create script for creating index first.

In my first script I forget to add NOT NULL at the new datatype, and column was changed so it was allow nulls, so I have to change script.

ALTER TABLE dbo.Account ALTER COLUMN AccountNumber bigint NOT NULL;

Final T-SQL code for changing column datatype in MS SQL Server 2000 – 2008

use Accounts_Database;
GO
/* ------------------------------------------------------
Changing type of AccountNumber from int to bigint for DBO.Accounts
---------------------------------------------------------*/

/* Drop existing indexes */
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[Accounts]')
 AND name = N'SK_Accounts_AccountNumber')
DROP INDEX [SK_Accounts_AccountNumber] ON [dbo].[Accounts]
WITH ( ONLINE = OFF )
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[Accounts]')
 AND name = N'Accounts0')
DROP INDEX [Accounts0] ON [dbo].[Accounts] WITH
( ONLINE = OFF )
GO

/* Alter table Accounts*/
alter table dbo.Accounts
alter column AccountNumber bigint not null;
go

/* Recreate indexes for Accounts table*/
/* SK_Accounts_AccountNumber */
CREATE UNIQUE NONCLUSTERED INDEX [SK_Accounts_AccountNumber]
ON [dbo].[Accounts]
(
    [AccountNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON [PRIMARY]
GO
/* Accounts0 */
CREATE NONCLUSTERED INDEX [Accounts0] ON [dbo].[Accounts]
(
    [Account_ID] ASC,
    [AccountNumber] ASC,
    [StatusCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON [PRIMARY]
GO