udiMagic | SQL Server to Tally Prime

SQL Server to Tally

Professional Edition

udiMagic Professional edition supports import of data from SQL Server database to Tally Prime. It supports :

All versions of SQL Server
Tally 6.3 and higher versions

Getting Started

How do I transfer data from SQL Server database into Tally ?
Before we proceed, let's have look at the steps involved in Excel to Tally vs SQL Server to Tally.

Steps	Import from Excel to Tally	Import from SQL Server to Tally	Remarks
Step 1	Start udiMagic Software	Start udiMagic Software
Step 2	Select option `Excel to Tally`	Select option `Advanced Usage`
Step 3	Select option `Import data into Tally`	Select option `MS-SQL Server`	[SQL Server to Tally] TODO : Specify the Configuration string to connect to the SQL Server database
Step 4	Select the Excel file	Select the Query file	[Excel to tally] Select any Standard Excel template provided with udiMagic Software. Example: Vouchers-Sales-Purchase-Basic.xls [SQL Server to Tally] TODO : Write SQL SELECT statement to retrieve data from your database tables. Your SELECT query should return the same columns as given in the Standard Excel template.
Step 5	Select the XML file [Optional - XML file is automatically selected]	Select the XML file [Mandatory]	Each Standard Excel template provided with udiMagic has a corresponding XML file. Here's an example: Excel file: Vouchers-Sales-Purchase-Basic.xls XML file : Vouchers-Sales-Purchase-Basic-xml-tags.xml
Step 6	Click the Start button	Click the Start button

As it can be seen, the above task to import from SQL Server to Tally involves two main things:-

To write Connection String to connect to SQL Server
To write SQL-SELECT statements to fetch data from the SQL Server database

CAUTION

Though use of udiMagic does not require any programming knowledge, you need to have understanding of SQL (Structured query Language). And Of-course, you must have the rights / privileges to access the SQL Server database.

Writing Connection String

Follow these steps :-

Start udiMagic Software
Next, select Advanced Usage option.
Next, select the Database-type as SQL Server
Next, click on the Settings button and specify the "connection string" for your SQL Server.
Next, click on the TEST SERVER CONNECTION button to test-check the connection.

Notes:
A Connection string is a string that contains information required to connect to a database or a data-file. Because there are several providers and each provider may have multiple ways to make a connection, you can write a connection-string in multiple ways.

A connection-string consists of series of keyword-value pairs separated by semicolons (;).
The equal-to sign (=) connects each keyword and its value.
Example: Key1=Value1; Key2=Value2; key3=Value3;

SAMPLE Connection strings for connecting to SQL-Server

Connecting to a SQL Server Instance
Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

Standard Security
Provider=sqloledb;Data Source=myServerAddress; Initial Catalog=myDataBase;UserId=myUsername;Password=myPassword;

Trusted Connection
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Remarks:-
a) You need to replace the highlighted text with actual values.
b) For more information on connection-strings, please refer this link http://www.connectionstrings.com

Writing SQL-SELECT statements

As database structures vary from case to case, you must write SQL statements (queries) to fetch data from SQL Server.

Sample SQL SELECT query

<SQL.LIST>
<SQL>SELECT vchno as id,basevtype,vchtype,vchno,vchdate,refno,refdate,partyname,partygstin,statename,itemname,hsncode,uom,qty,rate,discount,amount,igstrate,igstamt,cgstrate,cgstamt,sgstrate,sgstamt,salepurcledger,igstledger,cgstledger,sgstledger,narration FROM gstdata</SQL>
</SQL.LIST>

Remarks:
a) You must write SQL SELECT to select appropriate fields from your database tables.
b) Save the SQL Query in a file with extension qry.
c) If required, please consult your system administrator to write SQL SELECT statement.

Additional notes:
SQL-queries are generally written in a Text file (having .qry extension). You can use any TEXT-editor (like Notepad) to view/edit the SQL-query files. udiMagic allows you to write multiple SQL-SELECT statements in a Query (qry) file. As you can see, each SQL-SELECT statement is to be enclosed in <SQL>....</SQL> tag. This allows you to have multiple queries in a single Query (txt) file. udiMagic processes these queries one-by-one in the order in which they appear in the query-file. It contructs a result data-set which can be visualized as Columnar format comprising of Rows and Columns. The first field in the result data-set can be accessed using COLUMNREFERNCE A, the second field in the result data-set can be accessed using COLUMNREFERENCE B and so on.

Importing data from SQL Server to Tally

Start udiMagic Software
Select the option Advanced Usage
Select the option SQL Server
Click the Browse button and select the "SQL Query file"
Click the Browse button and select the "XML file"
Click the Start button

Using Named Parameters in SQL-Select query

You can use named parameters :FromDate and :ToDate in your SQL Query to provide input for the fields "From Date" and "To Date" in udiMagic for uploading data from SQL Server to Tally Prime.

How to use parameters in SQL query?

Here's a sample SQL SELECT query that shows how to use the :FromDate and :ToDate named parameters

SELECT field1,field2,fldDate FROM table WHERE table.fldDate>=:FromDate AND table.fldDate<=:ToDate

Instead of hard-coding the period (from and to dates) in SQL-query, we have used 2 named parameters viz :FromDate and :ToDate. The values for these parameters are assigned at runtime based on the period you enter in the udiMagic screen as shown here.