Search This Blog

Monday, April 30, 2012

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

Tuesday, April 24, 2012

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

Thursday, April 19, 2012

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    

Tuesday, April 10, 2012

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.


Friday, April 6, 2012

Create a new Guid in T-SQL

Use NewID() function.


1SELECT newid()
Result:
11CA040D5-53D6-4E7C-8D36-1000C8B03A91