Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedure
Didn't want it to fall to far - anyone have ideas?
|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?
|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:

>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure
sql

Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedureDidn't want it to fall to far - anyone have ideas?|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:

>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure

Calling SP on linked server inside job

I'm trying to call a SP that is an object on a linked server (server B)
from within a job step on a job on server A. The job appears to start
running, but then neither fails nor succeeds. The procedure never
executes b/c when I check the results, they aren't there. When I run
the same statement from isqlw, it succeeds. When I try to view any
errors on the job history there are none - There is no job history to
view from the job. My syntax from inside the job is below....any
ideas?
EXEC [linkedserver].[database].[dbo].usp_storedprocedureDidn't want it to fall to far - anyone have ideas?|||Anyone?
unc27932@.yahoo.com wrote:
> Didn't want it to fall to far - anyone have ideas?|||Try running a trace when you execute the job to help narrow
down what the problems may be.
-Sue
On 8 Aug 2005 06:34:41 -0700, unc27932@.yahoo.com wrote:
>I'm trying to call a SP that is an object on a linked server (server B)
>from within a job step on a job on server A. The job appears to start
>running, but then neither fails nor succeeds. The procedure never
>executes b/c when I check the results, they aren't there. When I run
>the same statement from isqlw, it succeeds. When I try to view any
>errors on the job history there are none - There is no job history to
>view from the job. My syntax from inside the job is below....any
>ideas?
>EXEC [linkedserver].[database].[dbo].usp_storedprocedure

Calling DTS package with Send Mail object from VB

I have several processes that call DTS packages (SQL Sever 7) from VB6, and they work fine. The problem is that if there is a "Send mail" task in the package, it won't send the email if called from VB.

If I run the package directly through enterprise manager, the email gets sent, but if run through VB it does not.

Any ideas?

The vb code is below.

Thanks.

Lionel

Private Sub Command1_Click()

Dim objPackage As DTS.Package
Const DTSReposFlag_UseTrustedConnection = 256
Dim tServer As String

Set objPackage = New DTS.Package
tServer = "SQLBOX"
objPackage.LoadFromSQLServer tServer, "", "", DTSReposFlag_UseTrustedConnection, "", "", "", "dts_test"
objPackage.Execute
Set objPackage = Nothing

End SubPlease, post your DTS task that includes a call to xp_sendmail.|||I'm not actually sure how to post the DTS Package. When I script it through enterprise manager (Open the package and Save AS) then it saves in a unreadable format

(e.g. DZ b% o?`;\ D|Jkjìg).

Please could you let me know.

Thanks,

lwakersql

Calling Com dll object from SQL Server

Hello All,

I wonder how this can be accomplished.

We have created a class lib wrapper that imports the com dll. Upon placing the assembly in sql 2005, both the wrapper Dll and the com dll (probably a converted version) gets placed inside the sql 2005 assembly store. Next a CLR sp references the 2 assemblies above, but within the call to the CLR sp things fail with exception:

System.UriFormatException: Invalid URI: The URI is empty

The works fine from a windows forms app.
What is the methodology to invoke a COM Dll? How far are we from implementing this in SQL CLR

Thank you,

LubomirJust an addition. The full exception is:

"System.UriFormatException: Invalid URI: The URI is empty.\r\n at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)\r\n at System.ComponentModel.Design.RuntimeLicenseContext.GetLocalPath(String fileName)\r\n at System.ComponentModel.Design.RuntimeLicenseContext.GetSavedLicenseKey(Type type, Assembly resourceAssembly)\r\n at System.ComponentModel.LicenseManager.LicenseInteropHelper.GetCurrentContextInfo(Int32& fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)\r\n at HexillionCom.Hexillion..ctor()\r\n at TestHexillionCLR.TestHexillionClass.Test()"

Lubomir

Calling an Oracle Procedure via a DTS

Is there a way to call an Oracle Procedure using the MS OLD DB Provider
for Oracle object in a SQL Server 2000 DTS package? If it can't be done
this way, is there another way to retrieve data from an Oracle database
using an Oracle procedure to a SQL Server table? Also, can parameters
be passed into Oracle from SQL Server via a procedure? Our Oracle DBA
does NOT want to create views to enable SQL Server to access the data.

Examples would be great if possible. Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Jason Leiser" <jasonl22@.yahoo.com> wrote in message
news:403fa15f$0$202$75868355@.news.frii.net...
> Is there a way to call an Oracle Procedure using the MS OLD DB Provider
> for Oracle object in a SQL Server 2000 DTS package? If it can't be done
> this way, is there another way to retrieve data from an Oracle database
> using an Oracle procedure to a SQL Server table? Also, can parameters
> be passed into Oracle from SQL Server via a procedure? Our Oracle DBA
> does NOT want to create views to enable SQL Server to access the data.
> Examples would be great if possible. Thanks.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You should be able to use an ExecuteSQL task using the Oracle connection,
and pass the parameter values from global variables, although I must admit
that I haven't tried this myself.

As for calling a procedure directly from Oracle, you can create a linked
server and use OPENQUERY() to pass the stored procedure call to Oracle.
Unfortunately, OPENQUERY() doesn't support variables, so you would need to
build your query dynamically:

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Simon

Called Web Services from a stored prcedure

Hello,
Does anyone know if it is possible to call a web service from a stored
procedure? Failing that is it possible to use exterbal object? I'm thinking
along the lines of:
@.obj = CreateObject("xyzzy.f")
@.obj.j = 1
call @.obj
Is this wishful thinking ?
Many thanks...
MartinI'm certain there are multiple methods of doing exactly what you ask, and
each method will impact the performance and reliability of your database to
one degree or another. The best option would be to make the procedure call
and web service call in tandem from the application or a DTS package.
"Martin Waller" <martinwaller@.beeb.net> wrote in message
news:eDWb629MGHA.1716@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Does anyone know if it is possible to call a web service from a stored
> procedure? Failing that is it possible to use exterbal object? I'm
> thinking along the lines of:
> @.obj = CreateObject("xyzzy.f")
> @.obj.j = 1
> call @.obj
> Is this wishful thinking ?
> Many thanks...
> Martin
>|||Thanks for the reply. Performance is not an issue for me. I need to unit
test some web services that directly effect database tables and I was
thinking of a simple .SQL file that does something like:
set-up precondition
call web service
test data is in the expected state
exit
Then I could log the output to a file and, once happy, use it to compare
against in further tests as and when the web serices are tested.
Martin
"JT" <someone@.microsoft.com> wrote in message
news:O$O3O$9MGHA.3932@.TK2MSFTNGP09.phx.gbl...
> I'm certain there are multiple methods of doing exactly what you ask, and
> each method will impact the performance and reliability of your database
> to one degree or another. The best option would be to make the procedure
> call and web service call in tandem from the application or a DTS package.
> "Martin Waller" <martinwaller@.beeb.net> wrote in message
> news:eDWb629MGHA.1716@.TK2MSFTNGP10.phx.gbl...
>|||i recommend you use a sql statement to call a dts, (plenty of examples how t
o
do this). Let the dts call the web service and log whatever it needs to log

Call SQL Stored Procedure

Greetings,

Even though this may be not right place with this issue I would like to try!
I facing with the problem Object Variable or With Block variable not set while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.

Many thanks in Advancehowzabout

Dim oConn, oComm, oRS
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")
Set oRS = CreateObject("ADODB.RecordSet")

' Open connection to database
oConn.ConnectionString = Application("ConnectionString")
oconn.Open

' Use the stored proc
oComm.CommandType = 4 ' adStoredProc
oComm.ActiveConnection = oConn
oComm.CommandText = "spProcName"
oComm.Parameters.Refresh
oComm.Parameters("@.ParameterName") = sParameterName

oRS.CursorLocation = 3 ' adUseClient
oRS.Open oComm, , 3 ' adOpenStatic

Dunno if that will help with the access form or not. This is from a VBScript snippet that I use.

Regards,

hmscott

Call SP from VB using optional parameters

Evenyone,
I am using VB6 ADO command object to call Stored procedure. the SP have
lots of paramters, some of parameters have already been given default
values. To that parameters that have defalut values, I don't want to
create these parameter objects when calling SP from VB side. But it
seems doesn't work. It seems you must give each parameters. It pass
parameter by position, not by name. I know in SQL Server, SP can call
another SP by passing parameter by name.
Some idea about this? Thanks for your help.
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
For VB6 there is a stored procedure add-in that you can download for
free from msdn (search for "ADO Stored Procedure Add-In"). Follow the
instructions for installing it as an add-in, and it will take care of
generating the parameters and all the rest of the ADO code you need
for calling your stored procedures.
--Mary
On Wed, 29 Sep 2004 11:31:40 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Evenyone,
>I am using VB6 ADO command object to call Stored procedure. the SP have
>lots of paramters, some of parameters have already been given default
>values. To that parameters that have defalut values, I don't want to
>create these parameter objects when calling SP from VB side. But it
>seems doesn't work. It seems you must give each parameters. It pass
>parameter by position, not by name. I know in SQL Server, SP can call
>another SP by passing parameter by name.
>Some idea about this? Thanks for your help.
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Hi Mary,
I can't find that add-in download link, Can you pls give me that?
Thanks
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I wasn't able to find the msdn link to download the official version,
but googling "Microsoft ADO Stored Procedure Add-In" gave several
promising hits from developers elaborating on the original.
--Mary
On Thu, 30 Sep 2004 14:53:03 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Hi Mary,
>I can't find that add-in download link, Can you pls give me that?
>Thanks
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Mary,
Thank you for your reply.
I already got the solution even if i didn't install the add-in. By
setting commmandObj.NamedParameters = True, I can pass named
parameters,not by postions of parameters.
By the way, I am always trying to find a way to debuging the Stored
procedure during the VB6 run time for long time. I am meaning
continusely debugging VB code and SP code. Any suggest about that?
Thanks
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Better google that one too--there's kb articles and advice on that,
but getting it to actually work is another story. Attempting to debug
stored procedures from VB is a real PITA that most people have just
given up on. I always recommend using Query Analyzer rather than
wasting the time and energy on VB6 configuration for debugging sprocs.
--Mary
On Fri, 01 Oct 2004 08:52:00 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Mary,
>Thank you for your reply.
>I already got the solution even if i didn't install the add-in. By
>setting commmandObj.NamedParameters = True, I can pass named
>parameters,not by postions of parameters.
>By the way, I am always trying to find a way to debuging the Stored
>procedure during the VB6 run time for long time. I am meaning
>continusely debugging VB code and SP code. Any suggest about that?
>Thanks
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!

Calendar object for parameters

Hi,
I've some searching around this group but haven't found any good
answers on my question. There isn't an out of the box way of
attaching a calendar object to let's say a startdate parameter on a
report. I was reading you could accomplish this through the use of an
active x object. Has anyone attempted this? If not does anyone have
any idea of how it would be accomplished?You'd have to write a custom ASP.NET app with the calendar control and the
report in it, and pass the dates as parameters to the report.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"cordata5" <cordata5@.yahoo.com> wrote in message
news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> Hi,
> I've some searching around this group but haven't found any good
> answers on my question. There isn't an out of the box way of
> attaching a calendar object to let's say a startdate parameter on a
> report. I was reading you could accomplish this through the use of an
> active x object. Has anyone attempted this? If not does anyone have
> any idea of how it would be accomplished?|||I can't seem to get this to work.
In Report Manager I go to the folder I want and upload my aspx parameter
page with the calendars on it. The calendars do not show. Is the bin folder
needed? Where would I put it?
"Ravi Mumulla (Microsoft)" wrote:
> You'd have to write a custom ASP.NET app with the calendar control and the
> report in it, and pass the dates as parameters to the report.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "cordata5" <cordata5@.yahoo.com> wrote in message
> news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> > Hi,
> > I've some searching around this group but haven't found any good
> > answers on my question. There isn't an out of the box way of
> > attaching a calendar object to let's say a startdate parameter on a
> > report. I was reading you could accomplish this through the use of an
> > active x object. Has anyone attempted this? If not does anyone have
> > any idea of how it would be accomplished?
>
>|||You should create your own web site. Do not mix your in with Reporting
Services, that is asking for trouble. Note that you also then need to
integrate with RS using either URL integration or webservices.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:D4AA33F6-E6CB-4806-8286-DD2C876B4F31@.microsoft.com...
> I can't seem to get this to work.
> In Report Manager I go to the folder I want and upload my aspx parameter
> page with the calendars on it. The calendars do not show. Is the bin
folder
> needed? Where would I put it?
> "Ravi Mumulla (Microsoft)" wrote:
> > You'd have to write a custom ASP.NET app with the calendar control and
the
> > report in it, and pass the dates as parameters to the report.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "cordata5" <cordata5@.yahoo.com> wrote in message
> > news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> > > Hi,
> > > I've some searching around this group but haven't found any good
> > > answers on my question. There isn't an out of the box way of
> > > attaching a calendar object to let's say a startdate parameter on a
> > > report. I was reading you could accomplish this through the use of an
> > > active x object. Has anyone attempted this? If not does anyone have
> > > any idea of how it would be accomplished?
> >
> >
> >