Hi:
I'm trying to read data from Navision 2.60 ERP with own database to a
SQL Server 2000 database via ODBC. All is working except just one
thing, how to filter data with a date field.
I have some big tables that have a date field and I just want to read
data from one or few days, not all the table, cos I will be doing this
every day (datawarehousin g).
If I write in my SQL Server DTS:
SELECT *
FROM MYNAVISIONTABLE
WHERE (MYDATE = { d '2005-06-30' })
It works! This notation is the one required by C/ODBC Navision driver.
Now I want to change 2005-06-30 for something like "sysdate" or "today"
or similar... to execute this all days.... any ideas?
THANK YOU,
Regards
Fran 2 7087
I don't know anything about Navision, however if you're using a DTS
Execute SQL or Transform Data task, the query executes directly on the
connection, so you should be able to use whatever function Navision has
for the current date (if that makes any sense).
If that doesn't work, then you could look at building up the SQL
statement dynamically (as far as I know, you can't use a variable
inside an ODBC escape clause). One way is to use the DTS COM object
model - build the whole SQL string dynamically, then set the
SQLStatement property of the ExecuteSQLTask object. This could be done
from an ActiveX or Dynamic Properties task in the same package.
If this isn't helpful, you might want to give some more details of
exactly how you're executing the SQL statement within the DTS package.
You should probably also consider posting in
microsoft.publi c.sqlserver.dts and/or microsoft.publi c.navision to see
if there's a better way to handle this.
Simon
Hello:
Thank you for your help Simon, the key was to build dynamicaly the SQL
string as you said. Sadly Navision C/ODBC 2.60 driver doesn't seem to
have many features...
I tell you how I did it, in the case someone someday reads this and has
the same problem and wants to solve it fast:
I build a DTS with 2 connections (Origin and Target) and a Data
Transformation Task between them with all the transformation changes I
want. Until here everything as I had before.
My goal is to read only the data that I want filtering it with a date
variable. For example you have some million registers in a table and
want to extract each day just the few new records.
Now I add an ActiveX Task with the following code:
(Probably there's an easiest way to do this, I'm not an expert here,
but this works...)
'************** *************** *************** *************** ***********
' Secuencia de comandos ActiveX Visual Basic
'************** *************** *************** *************** *************
Option Explicit
Function Main()
Dim pkg, stp, tsk, cus, sql
'************** *************** *************** *************** *************
' Dealing with dates in Navision C/ODBC: They have to be
<{d'aaaa-mm-dd'}>
' Being the first d from date... options: t from time and ts from
timestamp
' The Month function will give you just one number if you are before
October
Dim mes
mes = Month(Date)
If mes <10 Then
mes = "0" & mes
end if
'************** *************** *************** *************** *************
Set pkg = DTSGlobalVariab les.Parent
' Notice here that DTSStep_DTSData PumpTask_1 is the name of the Data
' Transformation Task
Set stp = pkg.Steps("DTSS tep_DTSDataPump Task_1")
Set tsk = pkg. Tasks(stp.TaskN ame)
Set cus = tsk.CustomTask
' Construct the sql statement
sql = "select * from MyTable where (MyDateField > {d'" & Year(Date) &
"-" & mes & "-" & Day(Date) & "'})"
'Assign the SourceSQLStatem ent property of the custom task
cus.SourceSQLSt atement = sql
Main = DTSTaskExecResu lt_Success
End Function
I put a green (Success) line from this Task to the Origin Connection of
the Data Transformation Task, so that the ActiveX scritp runs the
first, build the SQL sentence, and then the Data Transformation Task
Origin Connection runs the SQL Sentence selecting only the proper data.
You can see this work just looking at the Origin Connection and in the
SQL box you'll see the SQL sentence generated in the last execution of
the DTS.
Thanks!
Regards,
Fran This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Andrew Gordon |
last post by:
I'm investigating getting Microsoft Navision to do stuff from a Python
script. The recommended way seems to be to use message queues (MSMQ).
I can get Navision to send a message to itself fine. I found a couple
of code example in an ancient message in this newsgroup. The send.py
one I changed to the following:
from win32com.client import gencache
msmq = gencache.EnsureModule('{D7D6E071-DCCD-11D0-AA4B-0060970DEBAE}',
0, 1, 0)
qi =...
|
by: Steve |
last post by:
Hello I have just been given a new project requiring that I create a
new web application in Microsoft .NET that integrates with Navision
Axapta v 3.0
While I am aware that I would be able to update the database tables
directly, I would prefer to use the classes that come with Navision
Axapta, could someone please inform me how I might be able to do this?
Thanks in advance
Steve
|
by: Peter T. |
last post by:
Hello ,
we use sql-server 7.0 with navision client 2.60a . Now we want to
upgrade to sql server 2000. The upgrade process works fine but when
I try to connect to the sql 2000 server with navision client it tells
me that my oem or ansi charset is not matching the 0 charset on the
server. I have only few options on navision client and it doesn´t fix
my problems changing the settings.
Is there someone with an idea ?
Thanx
|
by: Xavi |
last post by:
I would like to know if someone has prove the integration and comunication
between Biztalk nad Navision and if it works fine.
Thank you
|
by: Mike Reed |
last post by:
I must be having a "senile" day! I cannot recall, nor get to work, code
to read a cookie's expiration date/time in an ASP page/VBScript.
What am I missing?
*** Sent via Developersdex http://www.developersdex.com ***
| |
by: Mike |
last post by:
Hi,
We are in the process of evaluating a system that would allow us to receive data from third-parties and send data to partner companies. Our infrastructure includes Microsoft Navision, a SQL Server, and .NET 2.0. My idea would be to use web services with XML to fullfil the above requirements, but I have very little experience on web services. My main concerns are flexibility (future changes), performance in sending/retrieving data, and...
|
by: Timothy.Rybak |
last post by:
Hello all,
This is my first attempt at an application, so kid gloves are
appreciated.
I need to make a very simple form that only has a few elements. One is
TraceCode - a text field that is populated when a user scans a label.
The other is ScanDate - a date/time field that should equal the
date/time of the scan (e.g. 7/31/2006 5:00:00 AM).
|
by: Navision Recruiter |
last post by:
What we're getting you for Christmas:
·Excellent benefits package
·Exciting Career Path
·Great Team of Professionals
·Compensation $75k - $90k USD dependent upon experience
Permanent role for a Navision developer w/a good balance of technical
and functional skills for Houston based organization
|
by: azuro |
last post by:
Hi,
Is there any ways to extract data within a specified date range from Navision database?
Thanks.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |