Bulk insert SQL Batchfile 

Monday, February 21, 2011 12:36:00 PM

CODE

sqlcmd -U [user id] -P [password] -S [server] -d [database] -i [full path to script] -o [full path to log file]

example
 

CODE

sqlcmd -U foo -P bar -S .\SqlExpress -d MyDB -i "c:\scripts\restore.sql" -o "c:\db logs\restore.log"

-o is good for logging errors and print statments. without it errors are not logged (i think.)

Sql Work 

Thursday, February 10, 2011 12:16:00 AM

select * from dbo.tblChecklist3Submitted

where inputdate< getdate()-7

select * from dbo.tblChecklist3Detail

where Q3submittedid in (

select Q3submittedid from dbo.tblChecklist3Submitted

where inputdate< getdate()-7)

Oracle database 

Tuesday, January 25, 2011 5:37:00 PM

Oracle

Triboro_Ops

Eastern instance

Pwd:Postal1

1. Show current day and datetime 

Tuesday, January 25, 2011 12:48:00 AM

Type: =Today() same as getdate() in sql

type: =TEXT(WEEKDAY(A2), "ddd")  =Sat

 

Marquee 

Wednesday, January 19, 2011 9:25:00 AM

<marquee behavior="slide" direction="left">Your text goes here</marquee>
<marquee behavior="alternate">Your text goes here</marquee>

My Pivote  

Friday, January 7, 2011 12:14:00 AM

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + period + ']',

'[' + period + ']')

FROM vw_12wk

 

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT office,[L C],description, '+

@cols +'

FROM

(SELECT office, [L C],description,period, actual

FROM vw_12wk ) p

PIVOT

(

sum(actual)

FOR period IN

( '+

@cols +' )

) AS pvt

'

EXECUTE(@query)

 

---------------------

office L C description 14B 13B 12B 11B 10B 09B 08B 07B 06B 05B 04B 03

0 FN4 TOTAL C/S HRS 14630 23164 23692 23257 11527 17333 21899 18658 19449 21763 21609 17653

(1 row(s) affected)

 -------------------

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + period + ']',

'[' + period + ']')

FROM vw_12wk

 

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT office,[L C],description, '+

@cols +'

FROM

(SELECT office, [L C],description,period, actual

FROM tblTrend ) p

PIVOT

(

sum(actual)

FOR period IN

( '+

@cols +' )

) AS pvt

'

EXECUTE(@query)

---------

DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + period + ']',

'[' + period + ']')

FROM vw_12wk

 

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT office,[L C],description, '+

@cols +'

FROM

(SELECT office, [L C],description,period, actual

FROM tblTrend ) p

PIVOT

(

sum(actual)

FOR period IN

( '+

@cols +' )

) AS pvt

'

EXECUTE(@query)

 

Creating RESTful Mashups using SPD 2007 

Thursday, January 6, 2011 12:34:00 AM

The word "mashup" has become one of the most repeated buzzwords in the industry over the past few years. Promising ease of data integration and rapid development, mashups have become an integral part of the Web 2.0 lexicon. Many people are now familiar with the stereotypical mashup example of some data rendered on a geographical map.

Most initial applications of mashup technologies happened in the consumer space. For example, in 2007 Microsoft set out to create a tool integrated with a social network targeting the typical MySpace user. Popfly™ was the result of those efforts and has a very large user base already. It was even voted as one of PC World’s Top 25 Most Innovative Products for 2007.

Information Technology (IT) organizations have now begun to consider the applicability of mashup technologies to business use, attracted not only by the potential benefits derived from giving end users the ability to get things done on their own, but also to maximize the return on investment on SOA and data integration initiatives.

This article focuses on building mashups using SharePoint. Specifically, we will show you how to connect to external REST services using SharePoint Designer to bring data into a page in the form of web parts, and connect these web parts to create the mashup. Finally, we will show you how to render data on a geographical map using XSLT.

Scenario

The business scenario we will implement is about recruiting. Imagine a recruiter who has to stay in touch with a group of potential recruits and provide each one of them with personalized conversations as part of the recruiting process. This scenario is applicable to HR recruiting, college and professional sports recruiting, etc. In our example, the recruiter would like a way to have some insight as to what is going on in the recruit’s life before placing a phone call to follow up. In order to accomplish this, the recruiter would like to create a mashup using Twitter, MSN Weather, and the recruit’s personal information stored in a SharePoint list.

End Result

Our solution will consist of two pages. The main page (default.aspx, shown in Figure 1) will show the recruits information in a list in addition to a geographical map that displays their location. When the user clicks on one of the pins or markers on the map, a bubble pops up showing basic information about the recruit. This bubble also includes a link to our second page (mashing.aspx, shown in Figure 2). This page shows the recruit’s information, his or her Twitter image and latest status, and local weather information from MSN Weather.

Figure 1
Figure 1. Final result of the default.aspx page.

Figure 2
Figure 2. Final result of the mashing.aspx page.

Figure 3 below shows the high level conceptual flow of our application. When a user clicks on the recruit’s name on default.aspx, they navigate to mashing.aspx. Mashing.aspx receives a parameter in the query string called “name” to determine which recruit to render. You can choose another parameter such as email address if you like. From a user perspective, it is easy to use mashing.aspx by knowing only the name of the recruit. It also makes it easy for us to test our page. We will discuss default.aspx in Part 2 of this blog entry. Today, we will focus on the creation of mashing.aspx, the page that mashes Twitter and MSN Weather data with the SharePoint list.

Figure 3
Figure 3. Conceptual flow between two pages.

Building the mashing.aspx Page

As we see in Figure 3, mashing.aspx has essentially three web parts: the individual’s information, the Twitter web part, and the MSN Weather web part. The following is a concise outline of how mashing.aspx is created:

  1. Create a blank page in the desired location
  2. Add the three web parts with the desired information from each data source
  3. Connect the different web parts
  4. Format as desired

Step 1. Create a page in the desired location

We will assume the SharePoint contact list is already created, and it is named “Recruits” (see Figure 4 below). This SharePoint list should contain basic recruit contact information. The two key pieces of information we want in this list are Email Address (to connect to Twitter) and Zip Code (to connect to MSN Weather).

Figure 4
Figure 4. The Recruits SharePoint list with contact information.

The first step is to create an .aspx page. Open SharePoint Designer to the site where you want to put the mashup, and click File > New > ASPX. This is shown in Figure 5:

Figure 5
Figure 5. Create a new .aspx page and save it with the desired name.

Save the page with the desired name and location (Ctrl+S or click the Save button). We are going to call the page mashing.aspx and locate it in the global site folder.

Hint: One helpful thing to do immediately after creating a new page, is to press enter a few times. This enlarges the form area, making it easier navigate the page editor. After entering data, it is easier to click back into the form to add more data separated by a space or two. We find this super helpful when trying to format the page later.

Figure 6
Figure 6. The saved page appears in the Folder List on the left.

Step 2. Add the desired information

After creating a new page, the next step is to place the desired information on the page. First, we will add contact information from the Recruits list onto the page. We’ll use a single item view. If the Data Source Library task pane is not already open, click Task Panes in the toolbar to open it. Display the available list information by clicking into the Data Source Library > Recruits > Show Data (see Figure 7).

Figure 7
Figure 7. To insert the web part containing the recruit's information, click on the Recruits list in the Data Source

Use Ctrl-click to highlight all of the desired fields that you want to display about the recruit (we will select Full Name, EmailAddress, Phone, Address, City, State, ZIP, and Country). Then click Insert Selected Fields as… > Single Item View (see Figure 8 and Figure 9).

Figure 8
Figure 8. Using Ctrl+click, choose the list fields that you want to display on the page. Then enter them as a Single Item View.

Figure 9
Figure 9. The list data after being placed on the page.

twitter.com

The next step will be to configure Server-side Scripts to pull in external data. We are going to use data from twitter.com and MSN Weather, both of which are RESTful services. Again, using the Data Source Library, click Server-side Scripts > Connect to a script or RSS Feed… Use the dialog to name the source (General tab) and enter the URL (Source tab). For twitter.com, we use the following URL service.

http://www.twitter.com/users/show.xml?email=youremail@domain.com

This URL passes one parameter, an email address. If the email address is associated with a twitter.com account, the service will pass back all of the account information including their current status and the URL for their profile image. Figures 10-12 show these steps…

Figure 10
Figure 10. Click "Connect to a script or RSS Feed..." to add an external REST data source.

Figure 11
Figure 11. Configure the source by following the dialog instructions. Here, the name of the source is added.

Figure 12
Figure 12. The URL is entered for the source. Here, the specific URL for the Twitter service is added.

After the service is configured, we can add the data to the page similar to the contact information. Click twitter.com > Show Data. Ctrl-click the status text and the profile_image_url >Insert Selected Fields as… > Single Item View (see Figure 13).

Figure 13
Figure 13. Pick the new Twitter data source, show its data, select the desired fields, and insert them as a Single Item View.

To show the profile picture as an image and not a URL you need to set the Format as Picture. This can be done via the On-Object User Interface (OOUI) dropdown menu. When you have focus on something, like a web part, you often see a ‘>’ or chevron on the top right of the object. Clicking the chevron opens a dropdown menu (the OOUI). To set change the picture to an image click the URL text > OOUI > Format as: > Picture. The image should then show (see Figures 14 and 15).

Figure 14
Figure 14. The external Twitter data has been added to the page. To show a picture instead of a URL, adjust its format.

Figure 15
Figure 15. The Twitter profile picture now appears as an image.

MSN Weather

Configuring and the MSN Weather data source follows the same procedures as the twitter example. The URL used to create the data connection is as follows.

http://weather.msn.com/RSS.aspx?wealocations=98052&weadegreetype=F

This URL passes two parameters: the location in Zip code form and the units for the temperature (F = Fahrenheit and C = Celsius). Use a valid Zip Code to test the data connection. In our case, we will use the zip code 98052, Redmond, WA.

Figure 16
Figure 16. Insert the MSN Weather data as a Multiple Item View. This will show both the current weather and the forecast.

To display the weather feed, we will insert the data as a Multiple Item View. This will show us both the Current conditions and the Forecast. In the Data Source Library, click on the weather source > Show Data and then choose the item/description field > Insert Selected Fields as… > Multiple Item View (see Figure 16). Again, the “Format as:” selection will need to be adjusted. Set the format to Rich Text (using the OOUI).

Figure 17
Figure 17. Again, the inserted data needs to be formatted differently to appear correctly. Choosing Rich Text format will show the weather feed correctly.

We now have all of the data sources configured and displayed in three different Data Form Web Parts (DFWP) on our .aspx page (see Figure 18 below).

Figure 18
Figure 18. The page now contains all of the data, both external and internal.

Step 3. Connect the different parts of information

The goal for our mashup is to show data unique to each recruit (and be able to select which recruit’s data to display). Right now, we have static data for one recruit, and external data from whatever example we typed into the server-side script URLs upon configuration. To solve our problem, we will use a filter with a query string to pick an individual recruit along with Web Part Connections to link the three Web Parts.

Start with the contact list data from Recruits. Click on the list Web Part > OOUI > Filter:. A dialog titled “Filter Criteria” appears (Figure 19). Follow these steps:

  1. Click “Click here to add a new clause”
  2. Field name = Full Name
  3. Comparison = Equals
  4. Value: choose Create a new parameter (opens another dialog called “Data View Parameters”)
  5. Parameter Source = Query String
  6. Query String Variable = recruit
  7. Default Value: pick a name within the Recruits list (if you don’t pick one, the web part will be blank and difficult to work with)
  8. Click OK
  9. Click OK

Figure 19
Figure 19. Filter the Recruits list web part to show the data for the recruit passed in the URL query string.

Figure 20
Figure 20. Create the query string parameter to use in the filter.

At this point, we could preview the page and see contact information, along with the twitter.com and MSN Weather data for the examples that were used to configure them. Changing the URL query parameter to different names would show us the unique contact info for those names (the URL is the page URL with the following attached to the end: “?recruit = [name]”). But, even though the recruit data changes, the twitter and weather info would not. Next, we need to connect the recruit with their twitter info and the weather for their location.

To connect the twitter source to the email address column of the Recruit list, we will use Web Part Connections. Click on the twitter Web Part > OOUI > Web Part Connections… (Figure 21). A dialog with title “Web Part Connections Wizard” will appear. Follow these steps (Figure 22).

  1. Choose the action… = Get Parameters From
  2. Click Next
  3. Click Next (for Connect to a Web Part on this page)
  4. Target Web Part = Recruits
  5. Target action = Send Row of Data To
  6. Under “Columns in Recruits” click <none> on the same row as email under “Inputs to Twitter” and select E-mail Address.
  7. Click Next
  8. Click Next
  9. Click Finish

Figure 21
Figure 21. Use Web Part Connections to send the recruit's email address to Twitter.

Figure 22a Figure 22b Figure 22c Figure 22d Figure 22e Figure 22f
Figure 22. Follow the wizard, connecting the REST sources to the Recruits list.

The Twitter Web Part is now linked with the Recruits Web Part. A box should appear at the top of the page labeled “SPWebPartManager – WebPartManager”.

The MSN Weather Web Part can be connected in the same way. Click on the MSN Weather Web Part > OOUI > Web Part Connections… to open the Web Part Connections Wizard.

  1. Choose the action… = Get Parameters From
  2. Click Next
  3. Click Next (for Connect to a Web Part on this page)
  4. Target Web Part = Recruits
  5. Target action = Send Row of Data To
  6. Under “Columns in Recruits” click <none> on the same row as wealocations and select ZIP/Postal Code.
  7. Click Next
  8. Click Next
  9. Click Finish

Ta Da! All of the Web Parts are now connected to each other. When the page URL is submitted, the server draws the Recruit name from the URL, displays that recruit’s information, sends the recruit’s email address to Twitter and their zip code to MSN Weather, receives the data from both services, and displays all of the unique information on the page! Figure 23 displays what the page looks like in the browser.

Figure 23
Figure 23. With all of the data sources inserted and connected, this is what the page looks like before formatting. Changing the name in the query string of the URL displays unique information for that recruit.

Step 4. Format as desired

The page can now be formatted to any degree, making the page easy to use and look good. The first thing that we would do would be to attach a Master page to help the page fit in with the rest of the site. This can be done easily by clicking Format > Masterpage > Attach Masterpage… > OK. Next, text can be formatted, colored, or moved around. Figure 24 shows an example of what the final result could look like.

Figure 24
Figure 24. The final mashing.aspx page with a master page and some formatting.

Page 1 of 2 1 2 > >>