Create custom SQL commands

Create custom SQL commands

When you click the Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures option and then click Finish in the Configure Database Connection dialog box, the Edit Custom SQL Commands dialog box opens. In this dialog box, you can create custom SQL statements to retrieve or modify the data in a database.

There are four tabs in the Edit Custom SQL Commands dialog box, each corresponding to a common SQL command. They are:

• Select  The SELECT command is used to retrieve data from a table.
• Update  The UPDATE command is used to modify data in a table.
• Insert  The INSERT INTO command is used to insert new rows into a table.
• Delete  The DELETE command is used to delete rows from a table.

The Stored Procedure list displays all of the stored procedures that are available in the database that you are connecting to.

When the Edit Custom SQL Commands dialog box opens, any statement already applied to the data appears in the SQL Statement box. For example, if you create a database connection by clicking Select a table or view to select a table, and later you decide to edit the SELECT statement, you see the following statement in the SQL Statement box on the Select tab:

SELECT * FROM dbo.[your_table_name]

The asterisk in this statement is a wild-card character that means all. This SQL statement means select all of the fields for all of the records from this specific table. To edit the statement, click Edit Command to open the Command and Parameter Editor.

Edit the SQL query by typing directly in the SELECT command box.

Add SQL parameters

You can also create parameters for your SQL queries by clicking Add Parameter.

SQL parameters should be used anywhere that you might use a query string, meaning text enclosed in single quotation marks. Because using query strings in SQL statements creates a security vulnerability, SQL parameters are required in Office SharePoint Designer 2007 whenever you create an SQL statement that retrieves filtered data from a data source.

For example, Northwind Traders is a gourmet food distribution company. They store all of their business data in an SQL-based database. When they retrieve their entire product list from the products table, the resulting SQL query looks like this:

SELECT * FROM dbo.[Products]

This SQL statement retrieves all of the records in the product table. Northwind wants to retrieve a filtered list that includes only the items that are out of stock. The table includes a UnitsInStock field. Using traditional SQL, the statement that would retrieve this list looks like this:

SELECT * FROM dbo.[Products] WHERE UnitsInStock = '0'

The

'0'

in this statement is a query string. This query string should be replaced by a variable. For this example, you want to create a variable called OutOfStock. The new SQL statement looks like this:

SELECT * FROM dbo.[Products] WHERE UnitsInStock = @OutOfStock

To create the SQL statement by using the OutOfStock variable in this example, perform the following procedure:

1. In the Command and Parameter Editor dialog box, in the SELECT command box, type the following string:
   

   SELECT * FROM dbo.[Products] WHERE UnitsInStock = @OutOfStock

2. Under Parameters, click Add Parameter.
3. The variable that you want to define is OutOfStock, so in the Name column, type OutOfStock.
4. In the Parameter Source list, click None.
5. Because the value that you want to match is 0, in the DefaultValue box, type0.
   
   
6. Click OK.
   
   You can now see the new SQL statement in the Edit Custom SQL Commands dialog box.
   
7. Click OK again.
   
   In the Data Source Properties dialog box, the Edit Custom Query button appears on the Source tab.
   

Modify the data source properties of an existing database connection

After you configure a database connection, you can later modify the connection at any time. For example, you might want to create a new query, modify an existing one, or create a custom SQL statement.

1. On the Task Panes menu, click Data Source Library.
2. In the Data Source Library, click the database connection that you want to modify, and then click Properties.
   
   If the database connection uses a custom SQL query, the Data Source Properties dialog box looks like the one in the following illustration.
   
   If the database connection does not use a custom SQL query, the Data Source Properties dialog box looks like the one in the following illustration.
   
3. In the Data Source Properties dialog box, do any of the following:
   
   • If there is a Query area with buttons, click those buttons to add or remove fields or to create or modify a filter or sort order.
   • If there is a Query area with buttons, click Configure Database Connection to apply a custom SQL query to the connection. After you click Configure Database Connection, click Next.
     
      IMPORTANT   Because SQL authentication saves the user name and password as text in the data connection, any user with permission to open the site in Office SharePoint Designer 2007 can view these credentials. A security warning informs you that the user name and password are saved as text in the data connection and that other site members can access this data source. If you want other site members to access the database by using the same user name and password, click OK. If you do not want other site members to access the database by using the same user name and password, click Cancel. If your site is located on a server running Office SharePoint Server 2007, you can talk to your server administrator about configuring Single Sign-On, and then see the later section Connect to a database by using Single Sign-On authentication.
     In the Configure Database Connection dialog box, click Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures, and then click Finish.
     
     The Edit Custom SQL Commands dialog box opens. In this dialog box, you can create your custom SQL statement. For more information, seeCreate custom SQL commands earlier in this article.
   • If there is an Edit Custom Query button, click it to open the Edit Custom SQL Commands dialog box, where you can add, modify, or delete custom SQL statements.

Connect to a database by using a custom connection string

A connection string provides the information that an application or provider requires to communicate with a data source. A connection string usually supplies the location of the database server, the particular database to use, and the authentication information. With a custom connection string, you can specify the connection string that is used to connect to the database.

1. If the Data Source Library task pane is not visible, on the Task Panes menu, click Data Source Library.
2. In the Data Source Library task pane, click Connect to a database.
    NOTE   If the Database Connections heading is collapsed, click the plus sign (+) to expand it.
3. In the Data Source Properties dialog box, on the Source tab, click Configure Database Connection.
4. In the Configure Database Connection dialog box, select the Use Custom connection string check box, and then click Edit.
5. In the Edit Connection String dialog box, in the Provider Name list, click the data provider that you want to use.
6. In the Connection String text box, type your connection string.
   An example of an OLE DB connection string:

   Provider=myProvider;Data Source=myPath;Initial Catalog=catalogName;
   User Id=username;Password=password;

   An example of an ODBC connection string:

   Driver={myProvider};Server=myPath;Database=myDatabase;
   Uid=username;Pwd=password;

   An example of an Oracle connection string:

   Data Source=myDatabase;User Id=username;
   Password=password;Integrated Security=no;

7. Click Next.
8. In the Database list, click the database that you want to use as a data source, and then do one of the following:
• Click Select a table or view, click the table or saved view that you want from the list, and then click Finish.
  By using this option, you create a default connection to the table or view. However, after you click Finish, you can specify a query by clicking Fields,Filter, and Sort in the Data Source Properties dialog box. For more information, see the article Create a data source query.
• Click Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures, and then click Finish.
   NOTE   This option is available only if your administrator has turned on theEnable Update Query Support option.
  By using this option, you can create or edit custom SQL commands. When you click Finish, the Edit Custom SQL Commands dialog box opens. In this dialog box, you can create commands and add parameters. After you create a custom SQL command, you can edit the query by clicking Edit Custom Query in the Data Source Properties dialog box. For more information, seeCreate custom SQL commands later in this article.
9. In the Data Source Properties dialog box, click the General tab, type a name for the data source, and then click OK.
   The new database connection now appears in the Data Source Library.

  NOTES  

• If you are trying to connect to an external server running SQL Server, make sure that SQL Server authentication is enabled on that server.
• If you cannot use SQL Server authentication on the server that is running SQL Server, make sure that Single Sign-On authentication is enabled for the server where your SharePoint site resides.

Add a database as a data source for SHAREPOINT

With Microsoft Office SharePoint Designer 2007, you can connect to a variety of databases, including Microsoft SQL Server, Oracle, and other databases that are accessible by using OLE DB or ODBC protocols. By default, your Microsoft SharePoint site does not contain any database connections. To access a database, someone who has sufficient permissions must create the connection.

This article shows you how to connect to a database by using the Data Source Library. It also shows you how to create custom Structured Query Language (SQL) commands and how to modify the data source properties of an existing database connection in the Data Source Library.

Connect to a database by using Single Sign-On authentication

If your site is located on a server running Office SharePoint Server 2007, this is the preferred option for generating a connection string. The Use Single Sign-On authentication option instructs the server to supply the user name and password for accessing the database. This option can be used when your site is part of a portal site that was created by using Office SharePoint Server 2007 and the administrator has enabled and configured Single Sign-On. With Single Sign-On, you can connect to an external database, even if it doesn't reside on the same server as your SharePoint site.

1. If the Data Source Library task pane is not visible, on the Task Panesmenu, click Data Source Library.
2. In the Data Source Library task pane, under Database Connections, clickConnect to a database.
   
    NOTE   If the Database Connections heading is collapsed, click the plus sign (+) to expand it.
3. In the Data Source Properties dialog box, on the Source tab, clickConfigure Database Connection.
4. In the Configure Database Connection dialog box, under Server information, in the Server Name box, type the name of the server where your database resides.
5. In the Provider Name box, do one of the following:
   
   • If you are connecting to a database stored on a computer running Microsoft SQL Server, such as Microsoft SQL Server 2000 and Microsoft SQL Server 2005, click Microsoft .NET Framework Data Provider for SQL Server.
   • If you are connecting to a database that uses OLE DB, including versions of SQL other than Microsoft SQL such as MySQL, clickMicrosoft .NET Framework Data Provider for OLE DB.
6. Under Authentication, click Use Single Sign-On authentication, and then click Settings.
7. In the Single Sign-On Settings dialog box, do the following:
   
   • In the Application Name box, type the application name for your database.
   • In the Application field to use as the user name box, type the field in which your user name is stored.
   • In the Application field to use as the password box, type the field in which your password is stored.
    NOTE   If you do not have this information, ask your server administrator.
8. Click Next.
9. In the Database list, click the database that you want to use as a data source, and then do one of the following:
• Click Select a table or view, click the table or saved view that you want from the list, and then click Finish.
  
  By using this option, you create a default query that selects all records in the table or view. However, after you click Finish, you can modify the default query by clicking Fields, Filter, and Sort in the Data Source Propertiesdialog box. For more information, see the article Create a data source query.
• Click Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures, and then click Finish.
  
   NOTE   This option is available only if your administrator has turned on theEnable Update Query Support option.
  By using this option, you can create custom SQL commands. When you click Finish, the Edit Custom SQL Commands dialog box opens. In this dialog box, you can create commands and add parameters. After you create a custom SQL command, you can edit the query by clicking Edit Custom Query in the Data Source Properties dialog box. For more information, seeCreate custom SQL commands later in this article.
10. In the Data Source Properties dialog box, click the General tab, type a name for the data source, and then click OK.
    
    The new database connection now appears in the Data Source Library.

 NOTE   If you cannot use SQL Server authentication on the server that is running SQL Server, make sure that Single Sign-On authentication is enabled for the server where your SharePoint site resides.

If I am using SharePoint for an Internet facing website do I still need to purchase Client Access Licenses (“CALs”)?

If I am using SharePoint for an Internet facing website do I still need to purchase Client Access Licenses (“CALs”)?

If you are creating an Internet- or Extranet-facing website, it is recommended that you use Microsoft Office SharePoint Server 2007 for Internet sites. This license does not require the purchase of Client Access Licenses. However, all content, information, and applications on the “Internet Sites” edition must be accessible to non-employees. Websites hosted using an “Internet Sites” edition cannot be accessed by employees creating, sharing, or collaborating on content which is solely for internal use only, such as an intranet portal scenario. Here are some usage scenarios to help you determine when to use which edition:

Usage Scenario	Licensing Option
Internet facing web site: where content, information, and applications are meant to be used by external, non-employees. For example as a company’s web presence, informational website, community usage (wikis/blogs/discussion lists) , or an e-commerce website.	Microsoft Office SharePoint Server 2007 for Internet sites1
Extranet web site: where internal and external, employees and non-employees, can collaborate on content, information, and applications that are not being used exclusively for internal organizational use.	Microsoft Office SharePoint Server 2007 for Internet sites OR Microsoft Office SharePoint Server 2007 along with Client Access Licenses 1
Internal Intranet Portal or team site: where internal users, or employees, can collaborate on content, information, and applications that are meant for internal organizational use.	Microsoft Office SharePoint Server 2007 along with Client Access Licenses2

¹ If you are only interested in hosting forms for external users, then you may use Microsoft Office Forms Server 2007 for Internet Sites.
² If you are only interested in hosting forms for internal users, then you may use Microsoft Office Forms Server 2007.

Great Fast Facts about Haiti

Fast facts – Haiti Earthquake

feel free to help them by anyway you can, we have lot of organizations partcipating in their help. so please go head and do your part . thanks

• USGS: USGS called it the strongest earthquake since 1770 in what is now Haiti

• The quake struck on January 12, 2010 at 4:53 p.m.

• The 7.0 magnitude quake's epicenter hit just 10 miles west of Porte-au-Prince and its 2 million inhabitants

• 3 million people in need of emergency aid after major earthquake

• The major quake sent 33 aftershocks ranging in magnitude from 4.2 to 5.9

• The Red Cross dispatched a relief team from Geneva and the UN's World Food Program is flying in two planes with emergency food aid.

• The Inter-American Development Bank said it was immediately approving a $200,000 grant for emergency aid.

• Some 9,000 peacekeepers have been in Haiti since 2004, including 1,266 Brazilians.

• Haiti has no real construction standards.

• November 2008: Following the collapse of a school in Petionville, the mayor of Port-au-Prince estimated about 60% of buildings were shoddily built and unsafe in normal circumstances.




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

Cognos implentation on varchar data??

it looks tricky on title but thats what happening , ibm congos 8.3 report studio from past five years struggling to place a varchar2 data item in fact cellss in cros tab report , that looks ammazing right .. do ypou believe it or not ?? better you believe it if not prove it wrong ...!
take it easy ...

Cognos 8.3

Why? SharePoint (WSS/ SPS) is the place for end users to access corporate information.

Why? SharePoint (WSS/ SPS) is the place for end users to access corporate information.

--> Customers need to expose this corporate information from many data sources into a single coherent view. SPS and Office Server provide the single coherent view via the BI Portal. Reporting Services is the means to exposing this critical corporate information in the BI Portal.

Report Center – Central Storage for business reports.

SQL Server 2005 Reporting Services SP2 is integrated with Windows SharePoint Services to enable seamless publishing, viewing, and management of reports

-Report server namespace and security is hosted in WSS content database

-Report Server database used for additional metadata (schedules, caching, subscriptions)

-WSS DB used for storing Report Server items – reports, data sources, models, resources etc.

SharePoint Solutions: Smart Investments in a Recession

If collaboration solutions were a smart investment in 2008, they are even smarter in the recession of 2009. Well architected SharePoint solutions are likely to be one of the smartest investments companies can make to survive and thrive in this challenging economy. Here are a few ideas to help justify or accelerate your SharePoint solution investment and ensure that your investment shows a quick and sustainable return.
Why SharePoint now? [in the time of recession]
Solutions that automate business processes, especially those processes that are designed to replace lost workers, can help organizations deliver value with fewer resources. If an organization already owns SharePoint (and we all know that many do), building an automation solution on the SharePoint platform may be easier to justify than an expensive investment in business process automation software.
Effective collaboration solutions help to minimize travel expenses. Pretty much every organization I know is severely cutting back on travel expenses this year. In a geographically dispersed organization, cutting back on travel doesn’t mean project teams aren’t expected to work together; it must means that they have to work more effectively at a distance. SharePoint team sites, wikis, and blogs are a key enabler for effective distance collaboration.
Now, more than ever, companies can’t afford the productivity drain resulting from not being able to find information. I’ve read some estimates that the infamous “knowledge worker” spends close to 50% of his time searching for information. Can organizations really afford not to invest in a solution that helps increase information transparency and improves “findability?” Should we really be paying our scarce resources to navigate through endless LAN folders or should we be investing in solutions that promote asset re-use and facilitate knowledge transfer?
When you get the commitment to move ahead, here are some implementation considerations that are even more important in a recession than they are in a growing economy:
Don’t just throw SharePoint “over the fence” and expect users to jump on board. Create sample solutions and model sites so that teams can quickly begin to take advantage of the improved ability to find information in a well-architected SharePoint solution.
Don’t skip the information architecture step – it’s just as easy to design an “unfindable” SharePoint solution as it is to create a nested folder hierarchy that is 14 layers deep. A small investment in information architecture (from an expert consultant either inside or outside the organization) can help ensure the success of your SharePoint investment.
Be sure to include a governance plan to ensure things don’t get out of control and that users have a plan to maintain content over time. The only way your strategic collaboration investments will have a chance of being a valuable investment to ride out the recession and provide a platform for growth in a turn-around is if the content is reliable and current (in addition to being “findable”). A strong governance plan is critical to ensure that your investment delivers value in both the short and long term.