Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts


How To Determine The Generated SQL Queries In Entity Framework 4

With the help of Entity Framework, you may never write a single SQL Query code in your programming projects. Some of the benefits of Entity framework include:

  1. Visual Studio intellisense
  2. Compile checking
  3. Faster development
  4. Auto generated domain objects
  5. Lazy loading, etc.

However, sometimes you need to know how Entity Framework translates your LINQ Query into a SQL Query. You can't just trust the framework to correctly formulate the Query. If you are concerned about scalability and performance, you need to check and analyze the underlying query and if possible modify or refine it to increase querying speed and performance.

The easiest way to view the generated SQL is to use a profiler tool that's included in database management tools (for instance, SQL Server Manangement Studio). Using this tool, you can monitor all SQL statements executed against the database. But using this tool requires that you have access to the database. Fortunately, with Entity Framework 4, you can use ObjectSet class's ToTraceString method. 

Here's the example code on how to do it in C# and VB.NET: 


C#:

var result = ctx.Orders.Where(o => o.Date.Year == DateTime.Now.Year);
var SQL = (result as ObjectQuery).ToTraceString();


VB:
Dim result = ctx.Orders.Where(Function(o) o.Date.Year = DateTime.Now.Year)
Dim SQL = TryCast(result, ObjectQuery).ToTraceString() 





Sending Automated SQL Server Notification Emails

One of our clients asked today to be automatically notified through email if a failure in the regularly scheduled jobs in SQL Server occurs. This seems like a pizza cake (yummy!) considering that sending email is common and I'm sure I can find lots of information on the internet on how to achieve this in SQL Server.



The task was simple but unfortunately it took me the whole day to configure the Server. Information was available on the internet and I seem to be following the instructions correctly to the letter. But somehow, the feeling of success and being able to shout Yes! did not occur immediately and the simple task became a nightmare of trial and errors which left me wondering whether working in the office instead of working at home is better.

 
Now to the main course, in order for SQL Server to be able to send emails, the Database Mail must be enabled and configured. The Database Mail is available only for versions higher than the SQL Server Express Editions. First, you need to open SQL Server Management Studio and connect to the SQL Server Database. Once connected, go to the Object Explorer window and under the Management folder, right click Database Mail and select "Configure Database Mail".

When the initial screen shows, click Next.

In the "Select Configuation Task" Window, select "Setup Database Mail" option.


In the "New Profile" window, specify the Profile Name and Description in the designated text boxes and then click "Add".




In the "Add Account To Profile", select an account to add to the Profile you've created earlier or you can click "New Account" to create a new account.



When you create a new account, the "New Database Mail Account" window opens. specify the account name and description, then specify the the Outgoing Mail Server (SMTP) configuration and its authentication. Here, you need to be able to know the SMTP settings that your company uses. Then click OK. You will be taken back to the "New Profile" window earlier but with a new SMTP account specified. Click next.


In the "Manage Profile Security" window, click the Option to make your profile public and available. Click Next.



In the "Configure System Parameters" window, you can leave the default settings and click Next.



Then click Finish.



The Database Mail is now configured. You can check if you've configured it correctly by right-clicking on the Database Mail in Object Explorer and select "Send Test Email". Provide the email of the recipient and click Send. If the email is received, then pat yourself in the back because you're already halfway passed the work to be made.




We need to be able to use Notification Email in SQL Agent. To do that, you need to specify the "Operator" or the list of emails that can be the recipient of the email Notifications.
In the Object Explorer again, under the SQL Server Agent node, right click the folder Operators and select "New Operator".


In the "New Operator" window, specify the name of the Operator. Put the email recipients separated by semicolon in E-mail name text box. Click OK.



Then you need to enable the mail profile in SQL Server Agent properties. After enabling, restart the SQL Server Agent.



And finally, you can use the settings you've configured above in your SQL Server Jobs so that it can send email automatically if a job fails to execute. To do that, in the Jobs properties, under Notifications Tab,check email option and specify the operator you want to receive the email and select the event that can trigger the sending of the notification email.



With the above information in hand, I'm sure configuring another email notifications in SQL Server will be a breeze!

Hope this helps,
Noli











Import Data From CSV File To SQL Server With Import And Export Wizard

There are many ways to import data from csv files to SQL Server but the easiest is using SQL Server's Import And Export Wizard. The wizard is very very versatile as it can do both import and export from various different sources and destinations such Microsoft Access, Micorosft Excel, Flat File (csv,text files), and SQL Server among others.
Here's the steps to follow to use the Wizard for importing a csv file to SQL Server:


  1. Open SQL Server Management Studio and connect to the destination database. Right Click the database, select Tasks and click  Import Data...

  2.  On the opening screen, click Next.

  3.  On the "Choose A Data Source Screen", select a Data Source in the combobox. In our case, we're going to choose Flat File Source because we're importing from a csv file. Click "Browse" and find the csv file to be imported. Check the checkbox if the csv data contain columns names in the first row.


  4. Click Columns. Here you can specify the row delimiter and column delimiter. Since we're using a csv file, column delimiter is a comma and row delimiter is a new line.

  5. Click Advanced. Here, you can change the data type for each columns of the csv file. The Wizard detects the type of data and automatically set it for you. But if you think the Wizard is incorrect, you can set the data type manually.


  6. Click Preview to see the preview and check if the settings you have made are correct. Then click Next.


  7. In the "Choose A Destination" window, select a destination in the combobox. In our case, since we are importing to a SQL Server database, we're selecting SQL Server Native Client. Enter the Server Name and Authentication credentials needed for accessing the database. Then click Next.


  8. In the "Select Source Tables and Views" window, check the source you want to import and on the right column select a destination table. For the destination table, you can either select an existing table or a new table.

  9. Click "Edit Mapping". In the "Column Mappings" window, there are 3 options available. "Create destination table" option is selected if you're creating a new table as the destination. If you're importing to an existing table, you can choose either to "Delete rows in existing table" or "Append rows to the destination table". You can also enable to insert identity data - this should be enabled if your source csv file contains Identity data that you want inserted as the row ID in the table. Then you need to check and modify accordingly the Column Mappings between the csv file and destination table. Then click OK and click Next.


  10. In the "Review Data Type Mapping", you can check the mapping results and determine if there are problems and inconsistencies so you can always click Back and modify your settings accordingly. When all is OK, click Next.

  11. In the Run Package, check Run immediately. This will execute Import immediately when you click Finished. If you're using SQL Server version that is higher than the Express edition, you can have the other option of saving the Import steps so you can have the luxury of running it again in the future without the need to perform the previous steps again.

  12. Click Next. In the "Complete the Wizard" steps, the import summary steps are displayed. If these steps are correct, then you can proceed to click Finish to begin importing your data



    If you want to be able to save this Import steps, you need to upgrade to a higher version of SQL Server. The saved step is called SSIS package and you can choose to run this package periodically using SQL Server Agent. But that topic is worth another blog page. I'll write about it next time.