Creating a linked server

Example specifying a specific name for the linked server:

/****** Object:  LinkedServer [MYLINKEDSERVER]    Script Date: 04/30/2012 09:24:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYLINKEDSERVER'

                                 , @srvproduct=N'KINDEL-G-W7'

                                 , @provider=N'SQLNCLI'

                                 , @datasrc=N'10.245.8.103'
 /* For security reasons the linked server 

             remote logins password is changed with ######## 
    @server - The name assigned to the linked server definition.

    @srvproduct - Name or IP Address of server sql instance where the linked server                  is being added to.    

    @datasrc - Name or IP Address of remote server sql instance
Sample query:
    SELECT * FROM MYLINKEDSERVER.<database name>.dbo.<table Name>
    
*/
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYLINKEDSERVER'

                                    ,@useself=N'False'

                                    ,@locallogin=NULL

                                    ,@rmtuser=N'sa'

                                    ,@rmtpassword='########'

GO

SQL Server 2008/2008R2 Intellisense

I've noticed that SQL server 2008 intellisense frequently stops working/refreshing the database tree nodes immediately after I have just added a new new table.  

In MS SQL Server Management Studio:

Edit menu -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)

Convert a DateTime to a BIGINT or 64bit Integer

Okay, so I have a stored procedure that is creating an Timestamp from a date field using: CONVERT(BIGINT,[UpdatedDate])

So I'm using Linq, lambda expressions, IList to parse and filter the resultset from SQL server.
I need all of the records affected within the last 90 days but the UpdatedDate is transformed into a BIGINT
from 20.12-04-04 05:37:28.717 to 41001.  Now I needed a way to convert DateTime.Now into a 64bit integer.  Convert.ToInt64(DateTime.Now) throws a typeException error.

The Answer: DateTime.Now.ToOADate():

 DateTime.Now {4/24/2012 8:49:19 AM}

DateTime.Now.ToOADate() 41023.367304120373

Source: .Net247 discussion thread.

Using Merge to Update or Insert single row of data

Example of recent procedure I wrote to either update an existing row or insert a new row.
Table has the same structure as MembershipEmailFormat the table variable.

Found it easiest to create a table variable to be the source in the Using clause.  I realize that the main purpose of Merge statements really are merging muliple rows between two tables.  My use case is that I need to insert a new email address for a user or modify and existing email address.

CREATE PROCEDURE [dbo].[usp_user_merge_emailformat]
        @UserID UNIQUEIDENTIFIER,
        @Email varchar(256),
        @UseHTML bit
AS
BEGIN
    --SELECT @UserID='04EFF187-AEAC-408E-9FA8-284B31890FBD',
    --       @Email='gkindel@merge.com',
    --       @UseHTML=0
       
    DECLARE @temp TABLE
    (
            UserID UNIQUEIDENTIFIER,
            Email varchar(256),
            HtmlFormat bit
    )       

    INSERT INTO @temp(UserID,Email, HtmlFormat)
    Values(@UserID,@Email,@UseHTML)
           
    SELECT * FROM @temp    

    MERGE dbo.MembershipEmailFormat as t
    USING @temp AS s
    ON (t.UserID = s.UserID and t.Email = s.Email)
    WHEN MATCHED THEN UPDATE SET t.HtmlFormat = s.HtmlFormat
    WHEN NOT MATCHED THEN INSERT VALUES(s.UserID,s.Email,s.HtmlFormat);
END    

PayPal Store Front

Last time I edited my StoreFront was 2008!

http://storefront.paypallabs.com/
This is the Store Front gadget configuration pages.
Create / edit gadget with list of products.
Update and republish, copy html and add as a HTML element in Blogger template.

Create a new Guid in T-SQL

Use NewID() function.

1	SELECT newid()

Result:

1	1CA040D5-53D6-4E7C-8D36-1000C8B03A91