This blog covers the step by step process on sending an SQL query output to HTML Formatted Emails from SQL Server.
Please find the output of the query.
Please follow the below steps to send an SQL query output to HTML Formatted Emails from SQL Server
Create a query based on the user requirement and save the query has view.
Then go to SQL server Agent and create new jobs.
In General tab choose Name,Description,owner
Then in steps tab choose New and enter the Step name and choose the database and in command type please design the HTML table structure as required. Syntax is given below
[ @profile_name = ] ‘profile_name’ Is the name of the profile to send the message from.The profile_name must be the name of an existing Database Mail profile. @profile_name must be specified.
[ @recipients = ] ‘recipients’ Is a semicolon-delimited list of e-mail addresses to send the message to.
[ @copy_recipients = ] ‘copy_recipients’ Is a semicolon-delimited list of e-mail addresses to carbon copy the message to.
[ @subject = ] ‘subject’ Is the subject of the e-mail message.
[ @body = ] ‘body’ Is the body of the e-mail message.
[ @body_format = ] ‘body_format’ Is the format of the message body. The headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:
TEXT
HTML
Next, choose schedules and pick the time for the mail and then click OK.
SAP B1 version 9.2 Introduced the new inbuilt license free user, It is equalling to professional user name called Support User. The SAP B1 customers may find this extra user in the user list. This user can used by support consultants when needed without any additional SAP B1 licenses.
All the activity of support user is recorded in SAP Business One and kept in a dedicated support log menu, where you can find the a list of each log on details, including time stamp and the reason for connecting the company database.
For Activating free support user, you should activate Remote Support Platform feature on one of your client server
Requesting T-user in SAP Launchpad:
As of version 3.2, remote support platform for SAP Business One (RSP) supports a new authorization concept. This is to allow a machine-to-machine communication protocol, which minimizes the maintenance of passwords by introducing a Technical user. To connect to the new SAP backend, you require a Technical S-user account with Technical Data Transfer authorization for remote support platform for SAP Business One (RSP). To configure Remote Services (including Go To Assist bookings) via RSP or to enable RSP’s software download manager, a classic personal S-user is required.
Reproducing the issue
SAP Business One Customers
Log on to https://launchpad.support.sap.com/#/importantcontacts in order to identify your Super administrator(s). Note: In some cases, it might happen that no Super administrator is displayed. To resolve this, log an incident under component SBO-CRO-SMP and provide further information as to who (preferably an existing S-user) should be the Super administrator.
When the page User for Support Hub Communication opens, choose Request User.
Fill out the form and submit it. – From the dropdown list, select your own company. – Enter a description which will help you to remember that the user is a Technical User, and not a personal S-user. – Enter your own email address. It might be used by SAP to contact you in case there are questions in regards to the Technical User. – Choose your preferred communication language. – Optionally enter the department which will use this Technical User, once activated.
Note: The creation should take up to 4 hours; however, in exceptional cases it might take up to 24 hours.
Once created, activate the Technical User by marking it and choosing Activate.
Enter a password twice. The password must fulfil certain policies, which are shown while you type in the first password field.
Note: The activation might take anywhere from 1 minute up to 24 hours. Memorize the password. If you should forget the password, choose “Change Password”.
Once activated, upgrade to / install the latest RSP version and PL. Enter the Technical User into the Technical S-user field in order to establish a connection to SAP’s backend (menu Configuration → Channels → SAP Channel) DO NOT test the Technical User credentials by trying to log on to any SAP portal.
Test the connection. If it fails, refer to SAP Note 2414424.
Optional:If you want RSP to download SAP Business One upgrade and patch packages, have the Super administrator log on to https://support.sap.com/users-authorizations.html and request a new personal user.
Once your requested user is listed there, mark the User ID S00xxxxxxxx and click “>” at the end of the row. Switch to Edit mode.
11.2 Select the following authorization objects: Software download, Edit System Data, Edit all Login Data, Edit my Login Data, and Open Remote Connections.
11.3 Save the settings.
Return to the RSP Console and open the Initialization Wizard; enter the S-user credentials into the Software Updates window and test the connection.
On RSP Menu, you’ll find this screen after logon, open the “Tasks”.
Click system stats report and click run button.
On RSP Menu, you’ll find this screen after logon, open the “Tasks Results”.
Under the “Awaiting Approval” section, click on the System Status Report task until the task changed color to yellow. Click Approve button for that task.
The System Status Report task will be moved to ‘Awaiting Upload’ section. Please select the task and then click Upload
After that, System Task Wizard screen will be showing up.
Click Next button
Please click Next until execution process finished.
On the picture above, that screen will give information to us about the task execution on the server is successfully uploaded or not.
Click Finish .
After uploaded task on Remote Support Platform, on SAP B1 application, please follow this step when you need to access your client (customer) SAP’s database without interrupt any valid user license.
Login to your SAP Business One application with User ID ‘Support’.
You’ll need to input your real name, login reason, and about the purpose why you need to logon to the database.
Choose one of the login reason from the drop down menu.
SAP system will automatically showing up a notification about a recording session when you connect with User ID ‘Support’ and idle for a couple minutes and trigger the main screen locked. Just click OK.
SAP Business One also provide a Report Support User Log. The activity of the support user is recorded in SAP Business One and kept in a dedicated log, where each log on is documented, including time stamp and the reason for connecting the company database.
Notes: Only available on SAP Business One 9.2, version for SAP HANA and SQL SAP Business One 9.2
Are you looking for the right ERP for SMEs? But, are you also in the fear of entertaining a new technology & set the new norms of practice into your company?
Fear of technology is common for even new & young entrepreneurs out there, so we do understand how it affects the traditional companies who have a set company culture in place for decades. In these cases, change management becomes a challenge in itself and here we will list you the points to eliminate your fear of technology:
Being an entrepreneur, sets a tone for the way your business operates and these are clear signs that needs to be a ringing alarm for caution:
Saying no just because it costs: Do you say NO to things/changes, just for the sake it costs money without evaluating how it can bring a positive note to your organization?
Contentment: Are you content with the way things are and not striving to change positively every day?
Own way of doing things: Has your company set in its own style of operating and not willing to change to any other way of working?
Lack of experience makes you reluctant: The reason you are afraid of implementing new technology is your lack of experience in decision-making?
If you have answered yes to any one of the three questions, it is time to re-visit your ideals for the growth of your company.
It is important how as an entrepreneur, you overcome this fear and place the safest best at the minimum of risk. Here we will give you a few tips to handle the same:
Research is the key – Keep researching until you acquire all the required information and also look out for the right partners to associate with for a long-lasting business relationship.
One at a time – Most effective and efficient way to test the technology or your company’s capability to adapting is to take one technology at a time. You can always add new things at a later stage ?
Move out of the comfort zone – Decision should never be based on what you are comfortable with, rather the decisions should challenge and be based upon the benefits, ROI, effectiveness of the system for your company.
Knowledge of ERP – Learn about new technology to overcome this fear. The only way of knowing what is best for your organization, is acquiring a thorough knowledge of ERP & use of technology.
The most successful companies are the those who learnt, unlearnt & challenged themselves with unconventional decisions!
A key requirement of most manufacturing organisations is the ability to plan production for looking at demand and supply of finished goods, raw materials and considering capacity constraints for labour, machine andresource availability.
Planning production runs requires an assessment of labour, machine time and other resource bottle necks for optimized production scheduling.
Resource planning has the ability to manage capacity for labour, machinery and “other” capacity constraints.
Go to the Resources module and click on the Resource Master Data menu item.
In resource master data we can capture master data associated with our machines and labour or “other” capacity constrained items for manufacture
Resource allocation is worth a special mention as the different resource allocation methods will determine the timing of the resource allocation to the production schedule.
The automatic schedule will try and schedule the production order on available timeslots (available resource) prior to the required date
If “on due date” is selected the system will try and schedule the production order on the due date.
Other information to be completed on the resource master data screen in SAP Business One includes:
Capacity data by warehouse
Planning data – daily capacity for machines, labour and other equipment. In the example below the capacity that has been configured for a packaging machine based on uptime available for the machine Monday through Friday (2 shifts on Monday through Thursday) with some downtime on Friday for machine maintenance.
Properties, attachments and remarks can be added to the resource master data.
To review resource capacity go to the resource capacity menu item
We can also narrow down your capacity constraints by warehouse, resource code, and resource group and resource type.
This planning tool will allow you to review the factory capacity constraints.
SAP Business One – Resource Capacity Planning:
If we do a brief analysis of the data above we can see that there is a capacity constraint issue for the packaging machine on the 3rd of February
We have 16 hours of machine time available and 19 hours committed , this leaves the factory with -3 hours available on the 2nd February
Our production planner now has a clear view of the fact that there is a capacity constraint on the packaging machine and can start to plan accordingly
By clicking on the “Committed – 19 hours” section of the report SAP Business One will drill down to the production order/s which are committed
The production manager can then re-schedule this production or can increase the capacity on that day
In SAP BUSINESS ONE screen each and every part utilized for unique purpose .You may noticed the four squares boxes in the bottom of the SAP B1 Screen nearby SAP B1 logo. It can be used to view information based on the requirements.
For example if you want to see the customer debit balance while creating sales order Simply drag and drop the business partner account balances in the four squares boxes nearby SAP B1 Logo as shown in the picture -1 below including other balance like Open Order / Open Delivery/ Open Opportunity from BP Master.
The system will automatically fetch the all those data’s while you create any kind of market documents like purchase A/p and Sales A/R as shown in the picture-2 below.
1.Two square box left to the date & time will show you the fields details & system information while you move the cursor nearby any field in the SAP B1 Screen
2.Four Squares used to see the BP ( Supplier & Customer) Account Balances / Open Orders/ Open Delivery/ Open opportunities’
Note: This functionality is available in all the version of SAP B1 Versions.
This blog covers the step by step process on linking an SQL query with a excel spreadsheet. By which we can pass parameters to the query via the excel spreadsheet and made our report an easier and flexible output.
First Open a Microsoft Office Spreadsheet, and choose ‘DATA’ tab.
On Data Tab, Select From Microsoft Query (as marked on Fig 1.1)
Choose Data Source Dialog Box Pop ups on selecting the above mentioned path,
Select an existing database or if you need to enroll a new data source, select the new data source and click ok, there you will be prompted for the database file path.
After selecting the database, when you click on OK button, the login screen pops out, prompting for the SQL credentials.
On successful login, the query columns wizard prompts for the tables on DB as selection criteria (choose any small table as we will not use based on the selected table).
Click on the next button after the columns of the tables are selected
Click on the next button until you reach the screen.
To set parameter for the query select the ‘View data or edit query’ radio button and click finish.
To set parameter for the query select the ‘View data or edit query’ radio button and click finish.
On Finish, the below mentioned screen appears, then on Criteria menu, select add criteria.
Select the number of criteria’s based on your parameter requirement.
Select the number of criteria’s based on your parameter requirement.
Add criteria as shown below.
The mandatory condition is provide [] on the value field if parameter to be obtained.
Enter a dummy parameter for the query.
The criteria will be created as shown.
The data will be imported as shown below.
Select the connections.
Select Properties Tab.
Change the command text as executing a stored procedure.
For Parameter prompting use a ‘?’ at the end.
Select the cell for the parameter.
Thus the output gets displayed on the screen.
The report gets refreshed on the changing the input parameter.
Web portals are the new wave of operations in any business sector – Be it retail, manufacturing, distribution, QSR etc. Find the reasons here on why we say it is the new wave!
Why web portals?
They are designed websites which not only gives information but also help take inputs from the user in a more simplified manner as such a person who has very little knowledge of computes can also be trained to use web portal. Further to add to this simplicity of use, portals are accessible via web browser on desktops, laptops, smartphones etc.
Too many tabs? Too complex for your employees?
While using an ERP system, most companies feel that the system is complex for usage by their employees as their requirements are to handle only a small section of the system wherein, they are bound to go across several menus, screens and inputs, a couple of dozen times in a day. To eliminate this time-consuming process, we have developed several portals for ease of inputs which are directly integrated to the SAP Business One on the back-end.
Personalization is the key!
These portals can be customized to the company’s choice of fields & requisites, so it becomes easier for vendors, customers, employees to provide input data for the ERP system. One such POS for a sales employee at a home appliance retail store is given below:
The basic customer details are entered into this POS (Point of Sale) screen so the data can be captured accurately within less time for the back-end SAP Business One.
Web portals also restrict access and allow only certain information viewble by the user as defined by the company norms.
Integration issues?
Not every ERP vendor can seamlessly integrate the web portals to the ERP system, this is where the real problem begins. However efficient and easy the portal might function, if it fails to integrate to the core system – all the data is lost & the purpose of simplication turns in to an extreme chaos.
Types of portals
We’ve designed several portals such as vendor portal, customer portal & employee portal for ease of operational activites. We have also built expense claim, time sheet login portals for our customers’ specific needs & integrated the portals with SAP Business One.
To conclude, web portals can make life easier for users working around the ERP system & also is a cost-effective alternate to buying several licenses. Web portals are here to stay and the faster we adapt to it, the more succesful ERP implementations where the end-users comprise majority of the ERP usage.
If you are an entrepreneur and you are looking for an ERP system for your small or mid-sized company, you’ve reached the right page and here we are to guide you through the choosing the right ERP vendor for your organization.
Unfortunately, we understand that most ERP decisions in small or mid-sized companies are based on the cost or within the budget of the organization. Keeping this on mind, we wrote this article to help you understand the right ways to choose the ERP partner
The below are the few basic mandatory checks that are required to be understood to choose the right ERP partner:
After-Implementation Cost which coves all the upgrades, support, innovations, advanced technological solutions, maintenance, cloud services etc. Make sure the quote you get is inclusive of end to end implementation of your organization and not just a few parts of the implementation. The figure quoted might be low initially to get an entry, and then increased with time when there is no way out of the system or partner.
Experience of the partner/vendor in successful implementations. Read out their success stories & ask for a few references to call. The success stories will speak about their experience & client relationship.
Number of third-party vendors involved is crucial to identify if the ERP partner provides complete solution or just a part of your requirement wherein you would need additional vendors to integrate in to the ERP system. Look out for an ERP partner who given end to end solution rather than just the core-ERP.
Breakdown of cost involved & complete understanding of the offering is essential part of evaluation of ERP vendor. The right partner will break down license cost, implementation
SAP Business One is a SME offering of SAP products which is sold and implemented by a large network of SAP Partners across the globe. Even if you are unhappy with one vendor, you can switch to another without the hustle of changing the entire ERP system.
We can say, SAP Business One is the most affordable ERP solution for any small or mid-sized company, given its flexibility and also being a future-ready platform for your ever-growing business needs. Evaluate this system before you finalize the ERP decision and we are sure it will be worth your time and money!
There are lot of stories when it comes to ERP implementations which an entrepreneur comes across from either known sources or online reviews. We will list out here, the facts of failures for your understanding & also as a reference when you decide to finalize on an ERP system:
Low-Priced / Local-ERP Alternate
The first mistake most entrepreneurs commit is, falling for the low-priced ERP systems which makes the implementations failure even before the start as they are not equipped to professionally nor digitally enhance your ERP experience.
It might seem cheaper for now but in the long run, it costs you both money & time – more than expected if you would have chosen the right system at the beginning.
ERP Implementation Strategy
They say that the success or failure of an implementation lies in the methodology adapted by the partner before the initiation of project. A well begun job is half-implementation done ?
A standard implementation should follow the phases or approach given below & monitored closely for deviations & change management of the company to ensure successful implementations:
Bitter Experiences
We have come across several companies who were overly cautious and sceptical with us due to past experiences with other partners or vendors for gone-wrong implementations.
The only advise to entrepreneurs is to consider the experience & project successes of the partner to eliminate this fear. Do not hesitate to ask 15/20 references or pick any customer from their list for a feedback call. Best source of success in ERP implementations lies in choosing the right partner with an absolutely stunning customer track record.
The answer to the right partner not only lies in the stories of entrepreneurs who have failed implementations but also in the reference check phase. Check thrice before signing up!!
Online Presence
Be it a simple google/glasdoor review or even a vision statement of the company – it can say a lot about who they are as a company & the content they have put out including the values they follow!
Watch out for fake marketing posts or no-brainer content to stay out of, do not fall for the word free and be cautious if it is mentioned on the official website. At the end of the day, values & content put out on social media platforms can decide the fate of even brands we buy, why not look the same way for the software that helps you run the entire business??
In short, RESEARCH, REVIEW, REACH OUT to always know what system is best for your company & who is the best partner out there to collaborate with for a long-standing business relationship.
Because for companies who value their customers, they will always be there to answer every question before you decide to invest your time & money! ?
