08/31/2010

Renaming a SQL 2005 host prevents replication wizard starting

I had a SQL Server 2005 box have as part of it's initial configuration it's "computer name" changed by our IT team, AFTER SQL Server was installed.  The box subsequently began to be loaded with applications and ran fine for a year.

Today however, I wanted to add a replication job and kept getting an error popping up with:-

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERNAME-OLD'. (Replication.Utilities)

But I'd never heard of SERVERNAME-OLD\INSTANCE.  A bit of digging around and finding that the machine used to go by that name.  It seems the machine name is stored in a system table in the database server, at point of SQL Server installation.

To fix this, execute against the master database:-

spdropserver @server='SERVERNAME-OLD\INSTANCE'
spaddserver @server='SERVERNAME-NEW\INSTANCE'

And restart the SQL service instance.

Hey presto, replication works once more!

Posted at 02:07 PM | | Comments (0)

|

06/10/2010

ASP.net FileUpload control giving error: Internet Explorer cannot display the webpage

More a mental reminder to myself... but it's due to the default 4mb upload limit.

Override by placing this in your web.config's system.web block:-

<httpRuntime maxRequestLength="100000"/>

Giving a lovely 100mb limit (hey, I'm writing a LAN app, I'm allowed large uploads!)

Posted at 02:19 PM | | Comments (0)

|

04/19/2010

Copying a SQL 2005 database diagram to another database

Simple enough... run this query:-

INSERT INTO DESTINATION_DB.dbo.sysdiagrams

SELECT [name], diagram_id, version, definition

FROM SOURCE_DB.dbo.sysdiagrams

WHERE [name] = 'DIAGRAM NAME'

Posted at 11:22 AM | | Comments (1)

|

03/19/2010

Animated GIF after clicking submit button not working?

You know the situation... you have a webpage that when clicking a button, has the server churning away for a few seconds.  Wouldn't an animated GIF be fantastic?  Then implementing it... the animation doesn't work.

The solution is to add the "Please Wait" animation immediately after your <BODY> tag:-

<div id="divLoading" class="loading">

  <img id="imgLoading" src="../images/loading_arrows.gif">

</div>

Then chuck this into your <HEAD> tag:-

<!-- "please wait / loading" message -->

<style>

  .loading

  {

    z-index:1000;

    position:absolute;

    left:50%;

    margin-left:-200px;

    top:197px;

    visibility:hidden;

  }

</style>

 

<script language="javascript" type="text/javascript">

<!--

  function showLoading() {

    divLoading.style.visibility = 'visible';

    setTimeout("document.getElementById('imgLoading').src =

      '../images/loading_arrows.gif'", 200);

  }

-->

</script>

Then finally add the client-side script to your asp.net button:-

OnClientClick="javascript:showLoading();"

Rick Strahl also discusses this issue in his weblog.

Posted at 01:13 PM | | Comments (0)

|

03/12/2010

CSS vs Tables

There's a chap here who sounds rather sick of the argument and makes for some fun reading.  My personal opinion is to sit on the fence.  Chose the aproach as the task seems fit; horses for courses, etc.

Tables vs CSS: PROS and CONS - 15 points to consider...

Posted at 10:00 AM | | Comments (0)

|

03/02/2010

ASP.net GridView - Empty CommandArgument in RowCommand?

Once a databound item in a grid is changed over to a TemplateField, it will automatically lose the CommandArgument property in your GridViewCommandEventArgs parameter in the _RowCommand event.

To get it back, specify the CommandArgument, such as:

<ItemTemplate>

  <asp:ImageButton runat="server" ID="btnExpandRow"

    CausesValidation="false"

    CommandName="Select"

    CommandArgument='<%# Container.DataItemIndex %>'

    ImageUrl="~/images/bullet_arrow_right.gif"

    Text="Click here to view details"

    PostBackUrl='<%# GetAppRelativePageUrl() %>' />

</ItemTemplate>

Take note of the property being passed:-

CommandArgument='<%# Container.DataItemIndex %>'

With this, you can now retrieve the row index, as such:-

protected void gvw_RowCommand(object sender, GridViewCommandEventArgs e)

{

  GridView gvw = (GridView)sender;

  Int32 rowIndex = Convert.ToInt32(e.CommandArgument.ToString()) % gvw.PageSize;

 

  switch (e.CommandName)

  {

    case "Select":

      {

        if (gvw.Rows[rowIndex].FindControl("trChild") != null)

          gvw.Rows[rowIndex].FindControl("trChild").Visible = true;

      }

      break;

  }

}

Note that we need to take into account the GridView's PageSize, as the CommandArgument parameter is actually the index of the row from within the data source.

Posted at 01:46 PM | | Comments (0)

|

01/29/2010

ASP.net - Disabling a button when clicked

Such a simple thing to do, but there are no server-side properties to enable this behaviour.

Instead, given a button named "btnApprove", add this to your Page_Load...

// disable the approve button when it gets clicked

btnApprove.Attributes.Add(

  "onclick",

  "this.disabled=true;" + GetPostBackEventReference(btnApprove).ToString());

And there you go, a user can only click the button once!

Edit:-

It seems the Page.GetPostBackEventReference is now deprecated, the future being ClientScript.GetPostbackEventReference instead.  So try this instead:-

btnApprove.Attributes.Add(

  "onclick",

  "this.disabled=true;this.value='Please Wait...';" +

    ClientScript.GetPostBackEventReference(btnApprove, String.Empty));

Posted at 11:08 AM | | Comments (0)

|

12/08/2009

Find duplicate entries in a row with T-SQL

Here's some handy SQL to give a list of duplicate items in table rows, and how many instances there are.  The example below shows lists of people who have the same FirstName as others, and how many matches there are for each name with one or more match...

SELECT

       FirstName,

       COUNT(FirstName) AS NumOccurrences

FROM

       tblUserData

GROUP BY

       FirstName

HAVING

       ( COUNT( FirstName ) > 1 )

The output looks like...

Sql_count
Sql_count

 

Posted at 09:57 AM | | Comments (0)

|

11/02/2009

Dynamic SQL Search Queries

Got a lot of parameters to search some data by?  Across multiple tables?  With some values that may be empty / not specified?  No problem!

First specify your Stored Procedure parameters as nullable, like such:-

@SUPPLIER_ID int         = null,

@REF_NO      varchar(50) = null

Then set up your WHERE clause to look like this:-

WHERE 

  (SomeTable.SupplierId = @SUPPLIER_ID OR @SUPPLIER_ID IS NULL) AND

  (SomeTable.RefNo      = @REF_NO      OR @REF_NO      IS NULL)

The effect for each line is to set field = @parameter... or if field is null, then the whole AND becomes true. Therefore only the conditions where the @parameter has a non-null value have an effect!

Posted at 11:29 AM | | Comments (0)

|

10/23/2009

Looping through database records in T-SQL using cursors

From time to time, you may need to loop through a set of records one by one, and perform conditional operations dependent on the values of each row.

One way of doing this is using cursors.

Below is an example of using cursors.  It looks through a table named aspnet_Users (which you may be familiar with), and for any UserName's beginning with "EU", modifes another table...

declare @UserName nvarchar(256)

declare @UserId uniqueidentifier

 

declare @RowNum int

declare UserList cursor for

    Select

        UserName, UserId

    from aspnet_Users

       Where UserName LIKE 'EU%'

 

open UserList

    fetch next from UserList

        into @UserName, @UserId

 

    while @@FETCH_STATUS = 0

    BEGIN

       

        print 'Looking at: ' + @UserName + ' ' + CONVERT(VarChar(255), @UserId )

 

          -- Now we have the UserName and UserId, we can perform any SQL

          -- operations we need to for each of them here...

          UPDATE tblCheese

          SET eaten=1

          WHERE UserId=@UserId

              

        FETCH NEXT FROM UserList

            into @UserName, @UserId

    END

 

CLOSE UserList

deallocate UserList

Posted at 11:16 AM | | Comments (1)

|

Next »