Understanding Sequences and their use – SQL Server 2012

As per MSDN ,

A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

A sequence can be created using the CREATE SEQUENCE  statement.

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [  ] } | { NO MINVALUE } ]
    [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

An example could be found on these statements here in an older post.

When to use sequences :

• The application requires a number before the insert into the table is made.
• The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
• The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
• The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
• An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
• You need to change the specification of the sequence, such as the increment value.

Sequences in SQL Server 2012

Sequences where for Oracle until now

Now, SQL Server 2012 comes with a nice and simple to use Sequence Feature like Oracle.

An example sequence ‘countToten’ which would have sequence from 1 to 10 could be created like this :

CREATE SEQUENCE countToten
   AS tinyint
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 10
    CYCLE ;
GO

To reset the sequence to 1 you can use the following query:

ALTER SEQUENCE countToten
 RESTART WITH 1 ;

Finally, if it is necessary to drop the sequence, you can use the DROP SEQUENCE

drop sequence countToten

Winding up with an example :

Create a table with one int column, and insert data from the sequence.

CREATE TABLE seqTest
(column1 INT)
GO
INSERT seqTest(column1) values
(NEXT Value for countToten),
(NEXT Value for countToten),
(NEXT Value for countToten),
(NEXT Value for countToten),
(NEXT Value for countToten)

Happy Sql Coding !

Firefox Download File Feature Not Working

I just got stuck with a weird issue on Firefox Browser.

Any file I try to download, it just gives me nothing but a blank downloads list !

Tried all these options initially , which is what any one does as the primary troubleshooting steps.

1. Cleared Cache ( everything! )

2. Got latest updates

3. Repaired

4. Checked for virus attacks

5. Cleared all download history etc

All these attempts failed

Finally got it solved by doing these steps

1. Open Firefox

2. Go to Help -> Troubleshooting Information

3. On the applications basics section, click , ‘Open Containing Folder’.

4. Close Firefox

5. In the containing folder , delete the file  ‘ downloads.sqlite’.

6. Restart Firefox .

Bingo !! Issue solved !

 

Email Router -Configure the MS CRM deployment

Once you have successfully done the previous step 1 , step 2 and step 3 , then this post would be a continuation .

1. Click the Deployments tab.

2. Click New to create a new deployment.Make sure that the Deployment option is set to Microsoft Dynamics CRM Online.

3. For Microsoft Dynamics CRM Server, type the URL address of Microsoft Dynamics CRM Online. To do this, enter https://dev.crm.dynamics.com/OrganizationUniqueName where OrganizationUniqueName is a placeholder for the actual ID of your organization. Make sure you punch in the correct crm, crm5, crm4 accordingly.

6. For User Name and Password, type a Windows Live ID for a user in your Microsoft Dynamics CRM Online organization who has the System Administrator role.

7. For Incoming configuration profile, select the incoming profile that you created.

8.  For Outgoing configuration profile, select the outgoing profile that you created.

9. Click on Load Users and if everything is fine,all the users would be loaded.

10. Test the settings.

11. All is well, PUBLISH !

*If you want to change the Incoming or Outgoing configuration profile that will be used for a particular user, double-click the user record to open it, change the selection for the Incoming Configuration Profile or Outgoing Configuration Profile, and then click OK

Configure Email Router for Online MS CRM 2011 Hotmail Accounts

Once , Microsoft Dynamics CRM 2011 E-mail Router installation is done successfully as per the post , it is time to have the router configurations done.

** Here the post talks about an online CRM instance where the users are the having windows live id which are Hotmail emails. Other mails work as well, provided you provide the basic pop and SMTP information correctly.

The steps to follow are :

1. Have the MS CRM Users configured correctly.

- Make sure their primary email is right.

- Email Access Configuration needs to be set to Email Router. Default is usually Outlook.

- Approve the user’s email.

3. Start the E-mail Router Configuration Manager.

4. Click Configuration Profiles and then click New.

5.Create a new Outgoing Profile with details as shown in the pic below. This one is for Hotmail emails.

Outgoing Server (SMTP Server): smtp.live.com
Outgoing Server SMTP Port: 25
Outgoing Server Authentication: Yes
Outgoing Server TLS or SSL : Yes

6. Now create a new profile for incoming. As in pic below for a Hotmail SMTP relay.

Incoming Server (POP3 Server): pop3.live.com
Incoming Server POP Port: 995
Incoming Server POP SSL Encryption: Yes (On or Required)

7. Once the incoming and outgoing profiles are set. The next step is to Configure the Microsoft Dynamics CRM deployment.

8. Once Deployed, publish and you are ready !

Install Email Router for MS CRM Online 2011

As per the earlier post on deciding what type of E-mail capabilities your MS CRM 2011 Online needs to have, if you have finalized on the Router implementation, then these are the steps to follow:

1. Download the Microsoft Dynamics CRM 2011 for E-mail Router Installing Guide for use with Microsoft Dynamics CRM Online. Here is the link.

2. Download the Microsoft Dynamics CRM 2011 E-mail Router. The exe file is here.

3. Decide on the Server you would want to install. This has to be a proper exposed online server.

4. During installation , there would be some pre requisites that needs to be met. Many a times, there would be an error during this step, wherein , Microsoft Online Services Sign-in assistant  7.2 download will fail. How to recover from this error is illustrated in the post here.

Once installed, we need to configure Email Router using the configuration manager.

 

 

Send and Receive emails from MS CRM Online 2011

If you are implementing a CRM Online solution, it would be complete system when emails start flowing in and out of the system. In CRM Online there does not exist an e-mail router service implementation. That is there is no outbound (SMTP, Exchange) or inbound (exchange, POP3, etc) email service hosted in the cloud.

There are 3 aspects of this, out of which 2 are described here. The forwarded mailbox option is exempted. We need to have the online CRM understand that emails needs to be sent out using one of the aspect we choose.

The OUTLOOK client integration.

Here we use the native emails ending capabilities of the outlook email client.
Outlook will send and receive emails the normal way. As per your options in MS CRM, inbound or outbound emails will be tracked into MSCRM Online.

The drawback here is that only emails send/received with a CRM User linked to an Outlook client account will be processed.
So, let’s say you have a workflow sending an email in response to a certain condition that email will ONLY be sent if the following conditions are met:

• The sender has outlook with a MSCRM add-in installed
• The sender is configured in MSCRM User Settings with an E-mail access type Outgoing of type “Microsoft Dynamics CRM for Outlook”
• The sender’s outlook is connected (both to MSCRM and the SMTP).

In short, while not impossible, as you could always dedicate outlook clients as email routers, it is very difficult to manage emails from/to queue or for users not always connected.
All emails not going to an outlook instance will stay flagged as ‘Pending Sent’ in CRM Online.

Implementation of an e-mail router service

The Microsoft Dynamics CRM Online E-mail Router is a software component that creates an interface between Microsoft Dynamics CRM Online and an e-mail server. When the E-mail Router is installed, it can transfer e-mail messages to and from the Microsoft Dynamics CRM Online system.

- Outbound emails will be read by your e-mail router and routed through your SMTP (exchange or plain SMTP)
- Inbound emails will be read from your POP3, Exchange or Live accounts and send via the e-mail router to CRM Online.

You keep the option for each User or Queue to specify which method is used for email connectivity inbound and outbound.