Automation Error when called from VB 6

Hi Friend,

I have an application in VB 6 with Crystal Reports 7. Once I deploy the application using Package & Deployment Wizard, I get the error 'Automation Error' when I call the report from VB. I have deployed the .rpt file and not the complied file. I get this error occationally and not always. I also use Selection Formula, and Replace Selection Formula, both in VB code and also in the Report. Other reports function normally but just this one report gives me problems.

Have you come across this problem or do you have any idea of solving this ?

Please let me know if you do.

Bye.You might be missing a required dll.|||omalley might be right...............but it can be about a thousand things in CR - the error traps are so generic...:-(

I've seen this type of error when referencing objects in VB that are nor correctly type, for example.
eg dim myObj as Object
dim myObj as CRAXDRT.Report (which is the correct way to expose a report object and its properties and avoid the iUnknown interface on the COM layer, as I recall.)

It can also occur when automation servers used by CR are not available for some reason. I use some of my own UFLs (user function libraries that I developed in VB), which end up being exposed as "Additional Functions" in the formula editor. Any dll's with prefix UFL MUST be in same location as crpeaut32.dll or they will operate unreliably, even though properly registered using regsvr32.

Check also that your installation doesn't have some distributables on a network drive.

Once you've done that, make sure that you have properly passed your login info to the report in your code, and that you have also passed it to any subreports. This is a trap that bites many CR users - your database security may require you to supply login info to each subreport. It will also vary from OS to OS - standard XP security is much fussier than ME, for example.

As you can isolate it to a particular report that fails, are you doing anything differently to other reports, ie passing a different type of parameter, or maybe a range of parameters instead of a single one, and so on? Or maybe a different user is experiencing the problem.

Failing that, check the event log on the PC and see what information it is returning regarding the automation failure, and post that here.

Dave|||Thank You, omalley and springsoft for your comments. The code I use is given below;

'*****

CR.ReportFileName = App.Path & "\RouteCard.Rpt"

CR.DiscardSavedData = True
CR.Reset
CR.DiscardSavedData = False
CR.WindowShowRefreshBtn = True
CR.WindowShowSearchBtn = True
CR.WindowShowPrintSetupBtn = True

If CmbWoNoFrom.ListCount = 0 Then
MsgBox "No Records", vbInformation
Exit Sub
End If
ab = False
st1 = "({TblSchedulinghdr.Cancelled} = 0 and {TblPartNoWorkOrderNo.Cancelled}=0) and {TblOrderAcceptanceDetail.Move}='MOVE' "



If Trim(CmbWoNoFrom) <> "" Then
st1 = st1 & " AND {TblSchedulinghdr.SequenceWONo} >= " & Val(CmbWoNoFrom)
End If
If Trim(CmbWoNoTo) <> "" Then
st1 = st1 & " and {TblSchedulinghdr.SequenceWONo} <= " & Val(CmbWoNoTo)
End If
If Trim(CmbFinancialYear) <> "" Then
st1 = st1 & " and {TblSchedulinghdr.FinancialYear} = '" & UCase(Trim(CmbFinancialYear)) & "'"

End If
CR.SelectionFormula = st1





CR.Action = 1

'****

I use a database on the network which connects through ODBC and there is no database passwords set. There are 6 sub reports on this report and only one user gets this error at times. This package is compiled on win 2000 and deployed on win98.

Please let me know if you have any clue.

Thanx.
Bye.|||John,
Only potential problem I can see here (and it may not actually be a problem because you have given us a code snippet, not the whole lot), is that you are using combo boxes, by the look of your variable naming notations (which bears some resemblance to Charles Simonyi's Hungarian C Notation, with an indicative type prefix on variable and object names).
If CmbWoNoFrom (for example) is actually a combo box, what are you doing to trap situations where user presses Print button without actually selecting an item from each combo?
Unless you have a default value already set, and it doesn't look like you have, as you are Val'ing the combo's selection, then a failure to select the value may cause the query to fail, because it is passing a value (probably -1) that the query can't handle.

Dave|||Thnak You springsoft for your observations.

I don't think an empty combo box would produce an error as the check for an empty trimmed combo is done before the expression is built. This particular machine had some virus problems and also has a keyboard device driver alert in device manager. Does this pose any potential problem, although I feel it should'nt.

Please let me know of your views.

Thanx.

Bye.

Automating Multiple Package execution

Hi,

Can anyone tell me the process of executing multiple packages in SSIS

I have 10 packages which should be executed in an order and should be automatically executed once in a day.

Can someone suggest me any such process which will help me to achieve this

Regards,
Explorer

You can build a main package that integrates the other packages in sequence and then create a job that executes the main package when you want in a schedule.

Regards,

Raul

|||

Hi,

Can anyone tell me the process of executing multiple packages in SSIS

I have 10 packages which should be executed in an order and should be automatically executed once in a day.

Can someone suggest me any such process which will help me to achieve this

Regards,
Explorer

As Raul said, you can achieve this with one master package which in turn calls the other 10 packages in the sequence you desire.

1) Create a new package and add 10 Execute Package Tasks in your Workflow.

2) Configure each Execute Package Task to call the package you want in order to get the right sequence.

3) Schedule a job to run the master package.

sql

Automating File Deployment of SSIS Packages

Hi:

I am trying to utilize DTUtil command to automate deployment of SSIS Package from the Build Server to Dev Server. I am getting an invalid option error, when I try to run the following command:

dtutil /FILE C:\Program Files\ABC\ABC ABC CAD\ETL Packages\Load_Staging_FromWCF.dtsx /MOVE FILE;\\serverA\C$\Program Files\ABC\ABC ABC CAD\ETL Packages\Load_Staging_FromWCF.dtsx /QUIET

Currently I have a .CMD file, in which I have the above command, and I am trying to run the CMD file and it returns an error. Can someone help me the DTUtil switches. My goal is to move a DTSX file from Server A(Build Server) to Server B(Dev Server). Also is there a switch to specify the server name, if UNC path is not supported. Also I am using File System Deployment, and running the file as an Administrator. I have admin privs on both Build and Dev Server. Thanks and I appreciate some help on this.

Why not just copy the package files over there using regular COPY commands?|||

That is true, because I am not actually utilizing SQL Server Deployment switches, although DTUtil does allow moving package files, just not sure what switches to apply. Thanks.

Automating Creation of Reporting Services Reports (Excel format)

Hello,

Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?

Thanks for sharing your thoughts and ideas!

donnie100 wrote:

Hello,

Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?

Thanks for sharing your thoughts and ideas!

You mean you want to use SSIS to build the RDL? I'm not really sure why you would want to do this but heigh-ho.

Is there a .Net API for SSRS? if there is then you could call it from a SSIS script task.

-Jamie

Automatically running the SSIS package once in a day

hi frnds,

i have SSIS package, i would like to schedule the SSIS package inorder to run automatically once in a day.

can anyone suggest me the solun. pls its urgent.

thnkx in adavance..

Use SQL Server Agent|||

hi,

i connected to my DB.but it is not showing SQL Server Agent in that.

reamining all things are there.

can you tell me what will be the problem?

|||You might not have access to SQL Server Agent. Talk to your DBA.

Automatically process a cube which is on another server

Hello

I want to create a DTS package on my server #1 which will process a cube on my server #2. The problem is that when I create the DTS package, I select the "Analysis Services Processing Task" and then, the only choice I have in the left box "Select the object to process" is the local server (server#1). How could it be possible to select my server#2 in that box ?

I know I can create connexions ... could that be part of the solution ?

I'm using SQL SERVER 2000 on server#1 and SSAS2000 on server #2.

Mike

When you're editing a DTS package, I believe the Analysis Services Processing Task will display servers that you've registered in Analysis Manager with your current login profile.

Open Analysis Manager and register the server that you intend to process cubes on, then try the package design again.

Are you logged directly into server #1 or term-served into it? If not, just be aware of the classic problem of DTS package design & deployment. Since Analysis services and DTS package design use live connections, what may work during DTS design time may not work once you, for instance, schedule the package to run as a job step, due to differences in permissions between the developer account credentials versus the account credentials of the service account which SQL Server Agent starts up as.

I hope this helps. I remember suffering through my first DTS package design sessions all too well.

CJB

|||

Enterprise Manager only allows me to register a SQL Server , but the database is on my server#1. My server #2 just has Analysis Services installed, so it is not a SQL Server.

I tried to register my server#1 with the Analysis Manager, and it worked ( I think it's because I have a sample cube on my srever#1), but that didn't change anything.

I still can't do what i need to.

Mike

|||

I think you're looking for the TreeKey setting. It's been so long since I've done DTS and AS2000 that I'm a little rusty. But have a look at:

http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx

Search for "TreeKey" then look at the image above that section. I believe if you set it to "YourServerName\YourCubeName" you should be able to process a cube on another server. I think you'll need a dynamic properties task to accomplish that.

And you might look at:

http://blogs.msdn.com/bi_systems/articles/141632.aspx

|||

thanks for your help, furmangg, but I am not familiar with dynamic properties tasks... What are they ?

And I never used ActiveX scripts, like it is suggested there http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx

Could you give me more detailed explanations, please ?

|||

You need a dynamic properties task to set the TreeKey property of your Analysis Services Processing Task. Here's more on dynamic properties tasks:

http://msdn2.microsoft.com/en-us/library/aa933528(sql.80).aspx

(If there's a UI way to hardcode the TreeKey for the AS Processing Task without a dynamic properties task, then you won't need one.)

I think you'll only need ActiveX script if you don't want to hardcode your TreeKey and want it to be more dynamic like that article suggests.

|||

Ok, I think I understand the problem now.

You can change the treekey setting by entering "Disconnected Edit" mode in the DTS designer. The drawback here is that you won't be able to double click your Analysis Services Processing Task to edit it. But that's okay, since it's not working anyway.

Right click anywhere in the background while designing the DTS package.

A dialog should appear.

Select "Disconnected Edit..."

Expand "Tasks"

Select the Task which is processing the cubes.

A collection of Task properties will show up in the right hand panel. The bottom one is "TreeKey", and its syntax is

servername\databasename\CubeFolder\cubename

So you might edit this to read (note that spaces here are fine, the package will "wrap" them properly at runtime)

mike8srv\FoodMart 2000\CubeFolder\Sales

Inspect the other property values, particularly DataSource (should match the name in Analysis Manager "Data Sources"), Fact table, and ProcessingOption (enumerated list: 0 = full, 1 = refresh, 2 = incremental).

|||

Maybe I was getting close of a solution with furmangg, but with your last post, you really solved my problem John !

I wouldn't have imagined a simpler solution !

thanks for your help to both of you - i really appreciate

Automated DTS package will not run

I suspect this is a user-rights/security issue:
I have a DTS package that needs to run weekly. This DTS package runs fine
when you open it and select "execute". I have done the following to set
this up as an automated task:
1)Go to DTS/Local Packages and right click the package.
2)Select "Schedule" from the pop-up menu.
3)Change settings to "weekly" and set the time to the current time + 5
minutes
4)Go to Management/SQL Server Agent/Jobs and refresh
5)Find the job that you just created, right-click and choose 'properties'
6)Confirm the settings (Owner: <my login name chosen from list> ) and let it
run.
I set up the "Notifications" tab to send me an email on completion which it
does:
========================================
================
JOB RUN: 'PhoneImportFromHR' was run on 2/12/2004 at 2:30:00 PM
DURATION: 0 hours, 0 minutes, 11 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 32
(PhoneImportFromHR). The last step to run was step 1 (PhoneImportFromHR).
========================================
================
Due to the fact that the DTS package runs fine when I run it, but won't run
automatically, I am thinking that this is a security issue (ie. The
automated process does not think I have rights to run this package, but
recognizes me when I try to run it manually). I have tried other login IDs
in the "Owner" box (see step #6 above), but I always get the same result.
Can anyone tell me what I can do to get DTS jobs to run automatically?
Schoo
PS: Windows 2000 Server, running SQL 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided. since you got the email notification,
the job runs and you the notification is working fine. For the job to run
automatically, could you check the job schedule. You could right-click the
job, then choose 'properties'; In the 'Schedule' tab, you will notice the
schedule of how you job runs. If you want to change the schedule, you could
press the 'Edit' button. You could arrange a new schedule of the job, Then
the job will run automatically. You could confirm by right-click the job
and choose the 'View job history' and check the record of how the job runs.
For the 'Notification' button, you could check the 'Email operator', choose
one operator and select when to send the email notification.
Hope this helps. If you still have questions, please feel free to post your
message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for your response... perhaps I was not clear enough on what is
happening. All of the items you mention in regards to changing a schedule,
I am aware of. I am saying that the actual job starts, fails and I get an
email notifying me that the job fails (see original email in this string).
The issue is not how to run the job automatically or how to get it to email
me. The issue is that the DTS package will run fine if I open it up and
execute it, but not if I create a job and try to run it automatically. My
questions is: how can I get the DTS package (that runs manually without
errors) to run without errors automatically.
I hope this is more clear and also that you might shine some light on this
issue for us.
Scott
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Scott,
> Thank you for using the newsgroup and it is my pleasure to help you with
> your issue.
> From the information you provided. since you got the email notification,
> the job runs and you the notification is working fine. For the job to run
> automatically, could you check the job schedule. You could right-click the
> job, then choose 'properties'; In the 'Schedule' tab, you will notice the
> schedule of how you job runs. If you want to change the schedule, you
could
> press the 'Edit' button. You could arrange a new schedule of the job, Then
> the job will run automatically. You could confirm by right-click the job
> and choose the 'View job history' and check the record of how the job
runs.
> For the 'Notification' button, you could check the 'Email operator',
choose
> one operator and select when to send the email notification.
> Hope this helps. If you still have questions, please feel free to post
your
> message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||questions are
1) who is the owner of the job
if owner of job is member of sysadmins fixed serverrole then job will run
dts in job owner's context
if owner of the job is not member of sysadmins then job will attempt to run
in sqlproxyaccount's context(if sql proxy account is configured) if not you
will get a failure
Olu Adedeji
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OEcdtEm8DHA.4044@.tk2msftngp13.phx.gbl...
> Thank you for your response... perhaps I was not clear enough on what is
> happening. All of the items you mention in regards to changing a
schedule,
> I am aware of. I am saying that the actual job starts, fails and I get an
> email notifying me that the job fails (see original email in this string).
> The issue is not how to run the job automatically or how to get it to
email
> me. The issue is that the DTS package will run fine if I open it up and
> execute it, but not if I create a job and try to run it automatically. My
> questions is: how can I get the DTS package (that runs manually without
> errors) to run without errors automatically.
> I hope this is more clear and also that you might shine some light on this
> issue for us.
> Scott
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
run
the
the
> could
Then
> runs.
> choose
> your
rights.
>|||Hi Scott,
Thanks for your update and Olu's reply.
Besides Olu's question, could you run the DTS package in the design windows
of the package? Is it successful or any information? When the mail is send
to you, what is the job history? You could also run the following code in
you Query Analyzer and collect the information of this job.
select * from msdb..sysjobs
select * from msdb..sysjobsteps
select * from msdb..sysjobhistory
select * from msdb..sysnotifications
select * from msdb..sysjobservers
select * from msdb..sysjobschedules
I am waiting on your reply. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott:
How about everything going? Now I would add some more information on this
issue:
There is one good article for your reference:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
Also, you could add the error file to capture the detailed error happened
when run the DTS package:
In the DTS package design window, choose from menu, 'Package'->'Properties'
and set the options in the 'Logging Tab'. You could refer to the DTS
Package Properties (Logging Tab) in the SQL Server Books Online.
Hope this helps. If you still have questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Automated DTS Package Problem

I need to create an Automated DTS package. At present I am running it manually each morning to import our phone switch data to an SQL table. The reason I had to go this route is this.

Because the Access tables are locked, I have a System DSN that connects to the System.mda file, this in turn gives me access to the locked tables to extract the data I need into Excel. I then drop the existing SQL table and run the DTS package recreating the SQL table with the data from the Excel file. I do not want to use OLEDB to connect to the Access table from within my application because it changes each month and of course the traffic load, I have as many 150 clients hitting the DB at one time.

I actually need the DTS package to update the SQL table with the latest data.

I'm no DTS guru, so how can I do this automagically?I managed to create the DTS package using my DSN, but I still have a few problems.

1. This is the Query that I need to use, it works every where else except insde the DTS Query Pane:

SELECT * FROM CallLog WHERE
(TTExtDest = '2002') AND (TTAnswered = '1')
AND (TTDateTimeIn >= DATEDIFF(dd, 1, GETDATE()))

It throws an OLEDB error telling me that GETDATE is an Undefined function.

2. I need to insert the above results into another table, this should work right?:

INSERT INTO SD_2004
SELECT * FROM CallLog
WHERE (TTExtDest = '2002') AND (TTAnswered = '1')
AND (TTDateTimeIn >= DATEDIFF(dd, 1, GETDATE()))

If I can overcome this Undefined Function bit, I got it whipped.