Tag: sql

SQL Saturday #28: Baton Rouge

This Saturday (August 14th) LSU will be hosting SQL Saturday #28. The all day event will feature a number of great sessions and speakers. If you’re a technology professional in the Baton Rouge area, this event is a must. Along with the speakers and sessions, there will be plenty of local technology professionals attending (close to 600 registered attendees) which should make it a great event for networking.

The local user groups will also have a table setup to help spread the word. If you’re interested in getting involved with the .NET, SQL, or SharePoint user groups, please stop in and say hello. I’ll probably spend most of the day at the table talking up the New Orleans SharePoint User Group.

Advertisements

InfoPath Data into SharePoint List and SQL

A client wished to push data into their SQL databases from an InfoPath form. Usually quite a simple task but the form was published to a SharePoint library and had fields published so they could be displayed in the library. We looked into a few different mechanisms but ultimately decided on a workflow set to start on creation or update of a list item. The workflow consisted only of a code block that produced the contents of the XML file stored in the SharePoint library and passed it into a stored procedure. The following code block was used produced the entire contents of the XML file as well as the file name.

/*
The variable workflowProperties is an instance of SPWorkflowActivationProperties:
SPWorkflowActivationProperties workflowProperties = new SPWorkflowActivationProperties();
*/
if (workflowProperties.Item.File.Exists)
{
    // get file contents
    string contents;
    using (StreamReader reader = new StreamReader(workflowProperties.Item.File.OpenBinaryStream()))
    {
        contents = reader.ReadToEnd();
    }
    string fileName = Path.GetFileNameWithoutExtension(workflowProperties.Item.File.Name);
}

At this point, you can use code to get specific values or pass the entire XML string to SQL depending on your requirements. I would recommend using SharePoint’s unique ID for the list item to identify the record in SQL. This will allow you to synchronize any CRUD operation on the SharePoint record to the SQL record.

Free SQL 2008 R2 Ebook

Microsoft Press has released a free ebook entitled Introducing Microsoft SQL Server 2008 R2.

PART I Database Administration

CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2 Multi-Server Administration 21
CHAPTER 3 Data-Tier Applications 41
CHAPTER 4 High Availability and Virtualization Enhancements 63
CHAPTER 5 Consolidation and Monitoring 85

PART II Business Intelligence Development

CHAPTER 6 Scalable Data Warehousing 109
CHAPTER 7 Master Data Services 125
CHAPTER 8 Complex Event Processing with StreamInsight 145
CHAPTER 9 Reporting Services Enhancements 165
CHAPTER 10 Self-Service Analysis with PowerPivot 189

More information:
http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

T-SQL String Padding Functions

Some time ago I found myself needing to format the data within a dataset. I thought about writing the code for it but then realized that a SQL implementation would probably be more efficient. I wrote two functions fPadLeft and fPadRight to allow a varchar(MAX) to be padded with any character (in my case it was zeros).

Here’s the fPadRight function:

CREATE FUNCTION fPadRight 
(
    @OrigString VARCHAR(MAX) = NULL,
    @PadLength INT = 0,
    @PadChar CHAR(1) = ''
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Result VARCHAR(MAX); 
    DECLARE @OrigLength INT;

    SET @OrigLength = LEN(@OrigString);
    
    IF (@OrigLength >= @PadLength)
    BEGIN
        SET @Result = @OrigString
    END
    ELSE
    BEGIN
        SET @Result =  @OrigString + REPLICATE(@PadChar, @PadLength - @OrigLength);
    END

    RETURN @Result
END
GO

The entire scripts, including if exists logic, are included in the following links:

Pad Right: https://gist.github.com/4488425

Pad Left: https://gist.github.com/4488465