HELP! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:

EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @.hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @.oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.

GunnyHi

Check out

http://www.databasejournal.com/feat...cle.php/1459181

A good resource for everything DTS are books online and
http://www.sqldts.com

John

"Cox News Server" <cfgunny@.cox.net> wrote in message
news:RJClb.97602$AH4.96936@.lakeread06...
> I'm a rookie with MSSQL. I need to run a DTS package to export a result
set
> to an MX Excel spread sheet. I need to call the DTS from a stored
procedure
> and pass it three values, depending on the input parameters to the stored
> proc.
> DTS package is no problem. Pretty easy with the DTS wizard. My problem is
> that I can't figure out how to instansiate the DTS package object from a
> stored proc and pass the three values as parameters to the DTS package so
> they can populate the parameters I created in it.
> I found an article related to it, but I'm too much of a rookie to grasp
it.
> It showed how to do this from a stored procedure:
> EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
> 'MyGVValue'
> IF @.hr <> 0
> BEGIN
> PRINT '*** GlobalVariable Assignment Failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> END
> I have three values and tree global variables to populate. Do I need to do
> the above 3 times?
> How do I instantiate the package object? I've read up some on sp_OACreate,
> but I don't get it, yet.
> How do I initiate the variable @.oPKG?
> It contains the name of the sp_OACreate string, right? How do I address a
> DTS package in the sp_OACreate string?
> I would really appreciate just writing out the sp_OACreate string and how
I
> pass values for three existing global variables to a DTS package named
> "DTS_1".
> I'm under some real pressure to get this done.
> Thanks for any help I can get.
>
> Gunny

HELP! Err whn changing Stored Proc used

We had to make a change to a stored procedure and wanted to test out the
change. So made a test version of the sp (all done correctly and verified by
dba). I changed the name of the stored proc used in my report to be the test
version. I could run the sp fine from the data tab and it picked up the
change and displayed the correct results. When I wanted to run it from the
Preview tab ... different story. I tried EVERYTHING and couldnt get it to
not throw an error (SQL0444N Routine "*S_UPTIME" (specification name
"SQL050707202207020") is implemented with code in library or path"
...RES_UPTIME", function "DANTEST.SP_RPT_RES_UPTIME" which cannot be
accessed. Reason code: "4". SQLSTATE=42724. Permissions on the sp are
correct, grant was run, everything is correct from DBA standpoint. IN FACT
... if I create a NEW report and use that stored proc then I dont get an
error. SO ... where to look for the problem. I need to use the new stored
procedure in an existing report and I CANNOT REWRITE that just to use a new
stored procedure. Something is being remembered in the project I need to
make the change in and I dont know where to look. Thanks for any help.I had an issue similar to this and it seems to be that even though you can
run the dataset on the data tab after changing the stored procedure using
the ! it doesn't update the field list. If you look in the config for the
dataset after running it you still see the field list from the old SP, I
tried using the refresh fields button, rerunning the new SP several times
but the field list wouldn't update.
As a work around I just nuked the dataset and recreated a new one with the
same name and the new SP and then the new field list was available.
Hope this helps.
Peace,
Dan
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:63BCFB5C-8196-4622-8D3F-5A336CE07A96@.microsoft.com...
> We had to make a change to a stored procedure and wanted to test out the
> change. So made a test version of the sp (all done correctly and verified
> by
> dba). I changed the name of the stored proc used in my report to be the
> test
> version. I could run the sp fine from the data tab and it picked up the
> change and displayed the correct results. When I wanted to run it from
> the
> Preview tab ... different story. I tried EVERYTHING and couldnt get it to
> not throw an error (SQL0444N Routine "*S_UPTIME" (specification name
> "SQL050707202207020") is implemented with code in library or path"
> ...RES_UPTIME", function "DANTEST.SP_RPT_RES_UPTIME" which cannot be
> accessed. Reason code: "4". SQLSTATE=42724. Permissions on the sp are
> correct, grant was run, everything is correct from DBA standpoint. IN
> FACT
> ... if I create a NEW report and use that stored proc then I dont get an
> error. SO ... where to look for the problem. I need to use the new
> stored
> procedure in an existing report and I CANNOT REWRITE that just to use a
> new
> stored procedure. Something is being remembered in the project I need to
> make the change in and I dont know where to look. Thanks for any help.|||Dan,
I have had that issue before and dealt with it as you mention here. This
issue is different. It doesnt have to do with the fields list as the fields
as there - that isnt the problem. I dont understand the different behavior
between running the stored proc using the ! and running the report against it
using the preview. The report didnt change from before ...just the stored
proc ... shouldnt be such a problem!
"Dan Christjohn" wrote:
> I had an issue similar to this and it seems to be that even though you can
> run the dataset on the data tab after changing the stored procedure using
> the ! it doesn't update the field list. If you look in the config for the
> dataset after running it you still see the field list from the old SP, I
> tried using the refresh fields button, rerunning the new SP several times
> but the field list wouldn't update.
> As a work around I just nuked the dataset and recreated a new one with the
> same name and the new SP and then the new field list was available.
> Hope this helps.
> Peace,
> Dan
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:63BCFB5C-8196-4622-8D3F-5A336CE07A96@.microsoft.com...
> > We had to make a change to a stored procedure and wanted to test out the
> > change. So made a test version of the sp (all done correctly and verified
> > by
> > dba). I changed the name of the stored proc used in my report to be the
> > test
> > version. I could run the sp fine from the data tab and it picked up the
> > change and displayed the correct results. When I wanted to run it from
> > the
> > Preview tab ... different story. I tried EVERYTHING and couldnt get it to
> > not throw an error (SQL0444N Routine "*S_UPTIME" (specification name
> > "SQL050707202207020") is implemented with code in library or path"
> > ...RES_UPTIME", function "DANTEST.SP_RPT_RES_UPTIME" which cannot be
> > accessed. Reason code: "4". SQLSTATE=42724. Permissions on the sp are
> > correct, grant was run, everything is correct from DBA standpoint. IN
> > FACT
> > ... if I create a NEW report and use that stored proc then I dont get an
> > error. SO ... where to look for the problem. I need to use the new
> > stored
> > procedure in an existing report and I CANNOT REWRITE that just to use a
> > new
> > stored procedure. Something is being remembered in the project I need to
> > make the change in and I dont know where to look. Thanks for any help.
>
>

Help! Can't pass names with apostrophe from ASP to SQL7 proc

I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.
When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.
I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error. What should I try; can
anyone help?
BTW, here's an example of the proc.
========================================
=============
CREATE proc getName
@.CUSTNAME varchar(15)
as
declare @.SQL varchar(4000)
set @.SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @.CUSTNAME + '%'''
exec (@.SQL)
========================================
==============
I've passed the name as below and still get errors.
getName O'Neil
getName 'O'Neil'
getName ''O'Neil'' - All Single quotes chr (39)
getName "O'Neil" - Quotes on the outside chr(34)
getName 'O''Neil' - All Single quotes chr (39)
getName "O''Neil" - Quotes on the outside chr(34), and double
singles in the name chr(39)
Nothing works due to the syntex. What can I do, please HELP!!
Thank you,
DaeI don't understand why you are using dynamic sql for this..
I guess you simplified the actual query for the post :)
But anyways.. try this.
getName 'O''''Neil' -- 4 single quotes|||this would work
getname 'o''''niel'
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> I don't understand why you are using dynamic sql for this..
> I guess you simplified the actual query for the post :)
> But anyways.. try this.
> getName 'O''''Neil' -- 4 single quotes|||Or this should work:
SET QUOTED_IDENTIFIER OFF
EXEC getName "O'Anders"
"Vadivel" wrote:
> this would work
> getname 'o''''niel'
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "Omnibuzz" wrote:
>

Help with Update Query command Problem

Hi all,

I have this store procedure as follows:

Create Proc UpdateProblem
@.ProblemID int,
@.CompanyName varchar (50),
@.Firstname varchar (50),
@.Lastname Varchar (50),
@.Address varchar (50),
@.Postcode varchar (50),
@.City varchar (50),
@.Phone varchar (50),
@.Cutype varchar (50),
@.ProDescript varchar (50),
@.Sol varchar (50),
@.Email varchar (50)

as Update Problem
set CompanyName = @.CompanyName,
Firstname = @.Firstname,
Lastname = @.Lastname,
Address = @.Address,
PostCode = @.Postcode,
City = @.City,
Phone = @.Phone,
Cutype = @.Cutype,
ProDescript = @.ProDescript,
Sol = @.Sol,
Email = @.Email

where ProblemID = @.ProblemID

when I test the querry

exec UpdateProblem
10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@.ht.com'

what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.

Please help. I have set the ProblemID as the Primary key.

When you do a SELECT * FROM Problem WHERE PRoblemID = 10004 , do you get 1 row or multiple rows?|||Only 1 row when I run : SELECT * FROM Problem WHERE PRoblemID = 10004|||

Find out the error. where ProblemID = @.ProblemID --> wrong

where @.ProblemID = ProblemID -- Right

cheers

|||I dont think so. They are both same. There's something else that happened. ...WHERE ProblemID = @.ProblemID should work just fine. Thats the way most queries are written.|||Hi Justnew, is the ProblemID column defined as int type? Can you post some sample data so that we can repro your issue?