Custom Procedure Replication Error with Oracle Subscriber

The custom procedure was created successfully on Oracle.
The following is the error message that is recieved. What is really odd is
why SQL Server is sending a select of a stored procedure name? Any ideas
as to what is going on here?
SQL> select * from sp_upd_crms_repl where 0 = 1;
select * from sp_upd_crms_repl where 0 = 1
*
ERROR at line 1:
ORA-04044: procedure, function, package, or type is not allowed here
Oracle publishers only replicate tables. It looks like here Oracle is
treating the stored procedure as a table, and is trying to return all the
data as opposed to only the columns (where 1=1).
Oracle publications do add the following objects on the Oracle server:
http://msdn2.microsoft.com/en-us/library/ms152557.aspx
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
news:uoQ%232bjwHHA.4800@.TK2MSFTNGP05.phx.gbl...
> The custom procedure was created successfully on Oracle.
> The following is the error message that is recieved. What is really odd
> is why SQL Server is sending a select of a stored procedure name? Any
> ideas as to what is going on here?
> SQL> select * from sp_upd_crms_repl where 0 = 1;
> select * from sp_upd_crms_repl where 0 = 1
> *
> ERROR at line 1:
> ORA-04044: procedure, function, package, or type is not allowed here
>
>
|||The Oracle side is actually the subscriber. It does appear that it is
treating this as a table instead of a stored procedure. I can't see
anything wrong with the article. The article has the insert and update
commands defined as CALL stored procedures and the actual command names are
the correct stored procedure names.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIyD82kwHHA.600@.TK2MSFTNGP05.phx.gbl...
> Oracle publishers only replicate tables. It looks like here Oracle is
> treating the stored procedure as a table, and is trying to return all the
> data as opposed to only the columns (where 1=1).
> Oracle publications do add the following objects on the Oracle server:
> http://msdn2.microsoft.com/en-us/library/ms152557.aspx
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
> news:uoQ%232bjwHHA.4800@.TK2MSFTNGP05.phx.gbl...
>
|||Configure the publication to use SQL Statements instead of the stored
procedures. In the Publication Properties, Articles tab, Select the
properties of the article and Insert, Update and delete deliver formats
select insert statements.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
news:OSxakFlwHHA.484@.TK2MSFTNGP06.phx.gbl...
> The Oracle side is actually the subscriber. It does appear that it is
> treating this as a table instead of a stored procedure. I can't see
> anything wrong with the article. The article has the insert and update
> commands defined as CALL stored procedures and the actual command names
> are the correct stored procedure names.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIyD82kwHHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||The reason we were drawn to the custom procedure is that we have long column
names > 30 characters in SQL Server and Oracle can only handle up to 30.
We are trying to handle the mapping in the custom stored procedure in Oracle
since there is no way to do this in SQL Server that we could come up with.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O$MTbRmwHHA.4076@.TK2MSFTNGP06.phx.gbl...
> Configure the publication to use SQL Statements instead of the stored
> procedures. In the Publication Properties, Articles tab, Select the
> properties of the article and Insert, Update and delete deliver formats
> select insert statements.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Michael Meyer" <michael_meyer@.csgsystems.com> wrote in message
> news:OSxakFlwHHA.484@.TK2MSFTNGP06.phx.gbl...
>

Custom Delivery Extension

Hi,
I have successfully created a custom data renderer(report to form) and a
data extension.
I need to create a custom delivery extension that will check if the reports
is returning data before delivering it.
Any pointers.....
Thanks
MandarUnfortunately the delivery extension API doesn't expose any mechanism for
knowing if the report contained data or not. The only work around is to use
data driven subscriptions such that the query for the subscription returns
no rows if the report will have no data.
If the delivery extension does not have to be generic, it could be written
to have knowledge of the reports and do some checking on its own.
This feature has been requested a lot and is on the list for consideration
for a future release.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IB Ghost" <ibghost@.hotmail.com> wrote in message
news:uD2hhfwSFHA.3312@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have successfully created a custom data renderer(report to form) and a
> data extension.
> I need to create a custom delivery extension that will check if the
> reports
> is returning data before delivering it.
> Any pointers.....
> Thanks
> Mandar
>
>
>|||Hi Daniel,
Thanks for your response.
I have installed the PrinterDeliveryExtension sample.
The Webserver/ReportServer & SQL Server are installed on the same machine.
I am using RS SP2 and am able to use the print functionality in the
ReportViewer.
I tried adding the printer name in the RSWebApplication.config file &
RSReportServer.config under the Delivery tag.
The printer is a network printer installed in another machine & I had given
the path as "\\anothermachinename\printername".
This was giving me an error while setting up the subscription itself, saying
printer not found.
I have modified the code in the PrinterDeliveryProvider.vb file,
function-SetConfiguration().
Here instead of reading the printer names from the config files, I am using
the PrintDocument object to pick up all the printers on the network
programmatically.
I can see the list of all the printers on the network on the subscription
page in the "Select Printer" dropdown, and can now create the subscription.
But when the subscription runs the status says "The printer
\\anothermachinename\printername is not currently
installed on the server."
Any ideas on this.
Regards
Mandar Pai
mandarp@.infobahnlimited.com; mandarpai@.hotmail.com
"Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
news:ud5tZ55SFHA.2172@.tk2msftngp13.phx.gbl...
> Unfortunately the delivery extension API doesn't expose any mechanism for
> knowing if the report contained data or not. The only work around is to
use
> data driven subscriptions such that the query for the subscription returns
> no rows if the report will have no data.
> If the delivery extension does not have to be generic, it could be written
> to have knowledge of the reports and do some checking on its own.
> This feature has been requested a lot and is on the list for consideration
> for a future release.
>
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "IB Ghost" <ibghost@.hotmail.com> wrote in message
> news:uD2hhfwSFHA.3312@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I have successfully created a custom data renderer(report to form) and a
> > data extension.
> >
> > I need to create a custom delivery extension that will check if the
> > reports
> > is returning data before delivering it.
> >
> > Any pointers.....
> >
> > Thanks
> >
> > Mandar
> >
> >
> >
> >
> >
> >
>|||This is just a guess, but when the subscription runs it runs as the service
user. Perhaps this user does not have permission to see the network
printer. Try giving them permission.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IB Ghost" <ibghost@.hotmail.com> wrote in message
news:%23QU7%23F8SFHA.2560@.TK2MSFTNGP10.phx.gbl...
> Hi Daniel,
> Thanks for your response.
> I have installed the PrinterDeliveryExtension sample.
> The Webserver/ReportServer & SQL Server are installed on the same machine.
> I am using RS SP2 and am able to use the print functionality in the
> ReportViewer.
> I tried adding the printer name in the RSWebApplication.config file &
> RSReportServer.config under the Delivery tag.
> The printer is a network printer installed in another machine & I had
> given
> the path as "\\anothermachinename\printername".
> This was giving me an error while setting up the subscription itself,
> saying
> printer not found.
> I have modified the code in the PrinterDeliveryProvider.vb file,
> function-SetConfiguration().
> Here instead of reading the printer names from the config files, I am
> using
> the PrintDocument object to pick up all the printers on the network
> programmatically.
> I can see the list of all the printers on the network on the subscription
> page in the "Select Printer" dropdown, and can now create the
> subscription.
> But when the subscription runs the status says "The printer
> \\anothermachinename\printername is not currently
> installed on the server."
>
> Any ideas on this.
> Regards
> Mandar Pai
> mandarp@.infobahnlimited.com; mandarpai@.hotmail.com
> "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
> news:ud5tZ55SFHA.2172@.tk2msftngp13.phx.gbl...
>> Unfortunately the delivery extension API doesn't expose any mechanism for
>> knowing if the report contained data or not. The only work around is to
> use
>> data driven subscriptions such that the query for the subscription
>> returns
>> no rows if the report will have no data.
>> If the delivery extension does not have to be generic, it could be
>> written
>> to have knowledge of the reports and do some checking on its own.
>> This feature has been requested a lot and is on the list for
>> consideration
>> for a future release.
>>
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "IB Ghost" <ibghost@.hotmail.com> wrote in message
>> news:uD2hhfwSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>> > Hi,
>> >
>> > I have successfully created a custom data renderer(report to form) and
>> > a
>> > data extension.
>> >
>> > I need to create a custom delivery extension that will check if the
>> > reports
>> > is returning data before delivering it.
>> >
>> > Any pointers.....
>> >
>> > Thanks
>> >
>> > Mandar
>> >
>> >
>> >
>> >
>> >
>> >
>>
>

Custom Control Flow Item Issue

I've been using Konesan's FileWatcher control-flow item successfully in design-mode on my PC, which runs the package on a remote server.

I have installed the Konesan's FileWatcher on the remote SQLServer machine. I then imported the package to the server (Files System folder). I then select the package, right-click 'Run Package', then Execute, and receive the error:

"Error: The task 'File Watcher Task' cannot run on this edition of Integration Services. It requires a higher level edition"

..in the 'Package Execution Progeress' dialog. All other validation seem to be ok.

(Note, I'm executing the above steps using SQLServer Mgt Studio from my PC ; I'm not doing it from the SQLServer machine itself...not sure if this matters or not.)

The SSIS version installed on the server is 9.0.3054. It shouldn't be an "SSIS version issue", as it is the same SQLServer that I used (successfully) from my PC in design mode...

Thanks,

Allen

SSIS is not likely installed on the server. Grab the installation media and verify that SSIS is installed. Better yet, look at the services on the server and see if there's a SQL Server Integration Service.|||

When you execute from SQL Server Mgt Studio, actually it runs from your system and not on server. Hence you will need SSIS installed on your box to run it locally.

This should give you some more insight

http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

Thanks

|||Via MSSMS, I logged directly into "Integration Services", and then successfully "Import(ed) Package", so it must be installed; ObjectExpolorer shows "DEVSQL06 (Integration Services 9.0.3054...). I don't have physical access to the machine, but am having somebody checking anyway.

|||Yep, see the post above. Sorry, I missed the part about you executing the package via SSMS. *You* don't have SSIS installed on your local machine.

If you were to create an Agent job on the server that pointed to the package, it would likely work fine.|||Karunakaran - Thanks..that definitely shed some light on the topic! I thought it was being run on the server via RPC, with status messages being returned back...

I do not have any SSIS Service on my machine, so will install it. (I do have SQLServer express, but am not using it).

However, just as a test, I removed the 'FileWatcher' control, then re-imported the package to the server, and it is now running (validation's all completed, and it has "Start"ed), and I see my recordcount increasing, so it is working. How is this possible if I don't have SSIS installed?

|||Because some components need to have a full version of SSIS installed. Just try it!

It's kind of a mess the way it's setup.|||

Only some components? I was under the impression to run a SSIS package you need to have full SSIS installed.

Time to dig up and see what all component will run without full SSIS installation. Is there any document on this?

Thanks

|||
Phil

Installation of SSIS on my PC did the trick; the Konesan FileWatcher task now works!

Karunakaran

Prior to installation of SSIS (when running my package with the Konesan FileWatcher removed), the package seems to have worked just fine with the following ControlFlow items:

For Loop Container
Execute T-SQL Task
Data Flow Task
File System Task

Your help is MUCH appreciated!!!

Rgds,
Allen

Custom Control Flow Item Issue

I've been using Konesan's FileWatcher control-flow item successfully in design-mode on my PC, which runs the package on a remote server.

I have installed the Konesan's FileWatcher on the remote SQLServer machine. I then imported the package to the server (Files System folder). I then select the package, right-click 'Run Package', then Execute, and receive the error:

"Error: The task 'File Watcher Task' cannot run on this edition of Integration Services. It requires a higher level edition"

..in the 'Package Execution Progeress' dialog. All other validation seem to be ok.

(Note, I'm executing the above steps using SQLServer Mgt Studio from my PC ; I'm not doing it from the SQLServer machine itself...not sure if this matters or not.)

The SSIS version installed on the server is 9.0.3054. It shouldn't be an "SSIS version issue", as it is the same SQLServer that I used (successfully) from my PC in design mode...

Thanks,

Allen

SSIS is not likely installed on the server. Grab the installation media and verify that SSIS is installed. Better yet, look at the services on the server and see if there's a SQL Server Integration Service.|||

When you execute from SQL Server Mgt Studio, actually it runs from your system and not on server. Hence you will need SSIS installed on your box to run it locally.

This should give you some more insight

http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

Thanks

|||Via MSSMS, I logged directly into "Integration Services", and then successfully "Import(ed) Package", so it must be installed; ObjectExpolorer shows "DEVSQL06 (Integration Services 9.0.3054...). I don't have physical access to the machine, but am having somebody checking anyway.

|||Yep, see the post above. Sorry, I missed the part about you executing the package via SSMS. *You* don't have SSIS installed on your local machine.

If you were to create an Agent job on the server that pointed to the package, it would likely work fine.|||Karunakaran - Thanks..that definitely shed some light on the topic! I thought it was being run on the server via RPC, with status messages being returned back...

I do not have any SSIS Service on my machine, so will install it. (I do have SQLServer express, but am not using it).

However, just as a test, I removed the 'FileWatcher' control, then re-imported the package to the server, and it is now running (validation's all completed, and it has "Start"ed), and I see my recordcount increasing, so it is working. How is this possible if I don't have SSIS installed?

|||Because some components need to have a full version of SSIS installed. Just try it!

It's kind of a mess the way it's setup.|||

Only some components? I was under the impression to run a SSIS package you need to have full SSIS installed.

Time to dig up and see what all component will run without full SSIS installation. Is there any document on this?

Thanks

|||
Phil

Installation of SSIS on my PC did the trick; the Konesan FileWatcher task now works!

Karunakaran

Prior to installation of SSIS (when running my package with the Konesan FileWatcher removed), the package seems to have worked just fine with the following ControlFlow items:

For Loop Container
Execute T-SQL Task
Data Flow Task
File System Task

Your help is MUCH appreciated!!!

Rgds,
Allen

Custom Assembly not Executing

I have both a custom assembly and a data processing extension. The DPE I
have successfully integrated into RS. But, I have written a custom assembly
that I use for creating a barcode image. I run it fine in ReportDesigner and
believe I have given it and the Barcode.dll (3rd party) "FullTrust" in the
rssrvpolicy.config file.
The problem is when I run I do not even get an error. I integrated the
EventLog to try to trace the problem but it never gets called by Reporting
Services and just renders the image empty.
If anyone has any suggestions on this I would greatly appreciate it. I have
a custom assembly that uses a 3rd party (Pegasus SmartExpress Barcode) dll.
I can execute perfectly in VS.Net design mode. But, cannot in ReportManager.
Thanks
TomTom,
Possibly the registered Assembly has the incorrect Assembly version in
Report Designer (see report Properties ... References)
I avoid having to delete and re-define the refernece to the newly compiled
assembly by stopping version incrementation in the AssemblyInfo.cs file ie:
[assembly: AssemblyVersion("1.0.0.0")]
regards,|||Simon
Thanks for the idea it makes sense...but it didn't work either. Not sure
what's going on here.
Tom
"Simon" wrote:
> Tom,
> Possibly the registered Assembly has the incorrect Assembly version in
> Report Designer (see report Properties ... References)
> I avoid having to delete and re-define the refernece to the newly compiled
> assembly by stopping version incrementation in the AssemblyInfo.cs file ie:
> [assembly: AssemblyVersion("1.0.0.0")]
> regards,
>