You cannot ALTER SYNONYM in SQL Server 2005: DROP and RECREATE it

Posted: February 25, 2010 | Author: | Filed under: SQL Server 2005 | Leave a comment

You can create SYNONYM in SQL Server 2005 with this syntax:

CREATE SYNONYM yourSynonym
FOR  [[[server.][database].][schema_name].]object_name

If you want to change a synonym, there is no ALTER command to do it. You need to DROP and CREATE it again with new context.

Here is a sample:

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'mySynonym') 
  

DROP SYNONYM [dbo].[mySynonym] 

CREATE SYNONYM [dbo].[mySynonym] FOR [myDB].[dbo].[myTable]  

Change parameter name with datacontrol event without using ParameterPrefix

Posted: February 24, 2010 | Author: | Filed under: ASP.NET 2 | Leave a comment

You can pass the original parameter value to your new parameter in your -ING event (here is DELETING) of your SqlDataSource.

You can use the same logic to do update.  

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnDeleting="SqlDataSource1_Deleting" ….

Code:

 protected void SqlDataSource1_Deleting(object sender, SqlDataSourceCommandEventArgs e)
    {
        e.Command.Parameters["@pPartNumberID"].Value = e.Command.Parameters["@PartNumberID"].Value;
        //Remove Parameter @PartNumberID
        e.Command.Parameters.Remove(e.Command.Parameters["@PartNumberID"]);
    }

UDF for horizontal aggregate to get the minimum

Posted: February 11, 2010 | Author: | Filed under: SQL Server | Leave a comment
SELECT [dbo].[fn__horizantal_min](Col1,
           [dbo].[fn__horizantal_min](Col2,
           [dbo].[fn__horizantal_min](Col3,
                                                 Col4)  as HorizontalMin 
 FROM testTable
 
The function is as below:

CREATE FUNCTION [dbo].[fn__horizantal_min]( @int1 int, @int2 int)

returns

int

as

begin

if @int1 is null

return @int2

if @int2 is null

return @int1

if @int1 < @int2

return @int1

else

return @int2

return null

end