I am not sure if this is a correct place to post this question. i am making a simple pay bill system, require people set a schedule that pays bill, then save it into database, when the time come, it auto transfers the money, i am thinking if i can do this in a store procedure. here is the interface:
From Account:
To Payee:
Amount:
ScheduleDate:
Save the schedule task View scheduled task
Ok, I thought this one would be easy.I have a stored proc: master.dbo.restore_database_fooThis is on database server B.Database server A backs up database foo on a daily basis as a scheduledtask.What I wanted to do was, at the end of the scheduled task is then call thestored proc on B and restore the database.If I go into Query Analyzer and log into database A, then execb.master.dbo.restore_database_foo works.But if I take the same command and make it part of the scheduled task itfails.Error is:OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)[SQLSTATE 01000] (Error 7312). The step failed.To me this seems like a permissions issue, but nothing I've tried seems tohave helped.Suggestions?----Greg D. MoorePresident Green Mountain SoftwarePersonal: http://stratton.greenms.com
View 3 Replies View RelatedHi. I have a stored procedure that I'm interacting with through vb.net... the stored procedure logic, by itself, runs in query analyzer, but when I run it from the code side using the stored procedure, it dies. Now, that said, I'm not a pro at writing a stored procedure, so you might look at this and gasp in horror.
I'm executing a three-staged query, all of which are using temp tables, the final of which is what I'm using for my datagrid import. The temp table name I'm using doesn't work... it's not accepted in the SP and stops the execution of the query stating "invalid table name."
Not being a guru at this, I dont know why it runs in Query Analyzer but not w/i the SP. Can someone look at this and help?
(If its grossly written and there's a better way, I wouldn't be offended to be told that either. I"m looking for ways to write cleaner, faster code)
Here is the SP, any suggestions are very very appreciated.
Thanks so much!
CREATE PROCEDURE sp_PCPPanel_Summary
(@strWHEREMale as varchar(500), @strWHEREFemale as varchar(500))
AS
EXEC('
SELECT
Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, Patcity, PatState , PatZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age
INTO #tblTempPCP
FROM(
SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, 0 as MWeight, dbo.tblPanelWeights.Female as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREFEMALE + '
UNION SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, dbo.tblPanelWeights.Male as MWeight, 0 as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREMALE + '
) unionWEIGHTS
GROUP BY Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, patcity, patState , patZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age
ORDER BY realage
')
SELECT isnull(cast(RealAge as varchar), 'Unknown') as AgeRange, RealAge as AgeSort, gender, mweight, fweight, [Male] = sum(case when gender = 'M' then 1 else 0 end), [Female] = sum(case when gender = 'F' then 1 else 0 end), count(*) as Totalinto #tblTempPCP2
FROM #tblTempPCPgroup by realage, gender, mweight, fweight
Select AgeRange, AgeSort, [MALE], [FEMALE], [Male Weighted] = case when gender = 'M' then Sum(MWeight * [MALE]) end, [Female Weighted] = case when gender = 'F' then Sum(FWeight * [FEMALE]) endfrom #tblTempPCP2group by AgeRange, AgeSort, [MALE], [FEMALE], gender, totalorder by AgeSortGO
Hi,Sorry, this is a very easy question. I have to create a storedprocedure in a SQL SERVER Database.I have read lots on MSDN about the syntax of this stored procedure :ie.CREATE PROCEDURE spGetContctDetailsAS-- get everything out of contact tableSELECT * FROM tblContactbut I cannot find anywhere how to actually add this procedure, so thatit is accessable in my VB.NET program.What I am asking is : what screen do I type this into? Is it QueryAnalyzer, if so where?Its got me oh so confused. When I've got time i'll read all about SQLServer, but for this morning I need to know how to simply add thatStored Procedure to my database.Thanks LOADS for any help.
View 1 Replies View RelatedHi,I have a requirement to update a table in SQL Server through a storedprocedure,the stored procedure should run periodically, say once in a month, andupdate the table.I should not use any external programs to call thestored procedure, is there any other way to do it without using anexternal programs.Thanks in advance,Rajan
View 2 Replies View RelatedCan someone help me find a source of information or give me an example of a scheduled strored procedure ... is it even possible?
Here's the scenario:
I have a field in one of my tables "date_due," once a day I need to check this field and send an email to the owner of any record (thier email address is also stored in this table) where "date_due" is equal to today.
Any help would be greatly appreciated ... thanks!
Hi,
I am new to service broker but I heard that maybe can be useful for my needs.
I have to install my DB to many different machines with SQL Server 2005 express edition and I need a scheduled execution of a procedure (each night ay 4 AM). Due to the "express" I don't have the server agent and due to the kind of the application I can't use task scheduler in order to execute a command by sqlcmd.
So, service broker (included inside the DB instance, correct?!?) can helps me?
Thank you.
Luca
Hi All,I have a stored procedure.I need to create a scheduled job using that stored procedure.I went to Enterprise Manager -->Management--> Jobs-->New JobPropertiesIn the step tab, I can select db and put the codes.Instead of writing the code there, I want to call the stored procedurein the command box.How can I do that?System: MS SQL Server 2000I would highly appreciate your help.Thanks a million in advance.Best regards,mamun
View 1 Replies View RelatedHi there,
SQL newbie here, and thanks for any help you may able to provide.
My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday.
I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it.
Thanks again.
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
View 3 Replies View RelatedI have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View 7 Replies View RelatedHave you used the stored procedure task? What is the difference in calling a stored procedure using the stored procedure task in SSIS Than calling a stored procedure using T-SQL?
View 1 Replies View RelatedIf I want to download a file, but I don't know if it's available yet (actually positive it won't be available for some time), how do I make FTP Task retry/wait until file shows up in the ftp folder?
View 1 Replies View RelatedI have a stored procedure thats transferring/processing data from onetable to two different tables. The destination tables have a uniquevalue constraint as the source tables at times has duplicate recordsand this will prevent the duplicates from being reported. When thestored procedure (which includes a cursor) is executed through queryanalyzer, it runs fine, and reports an error everytime it sees aduplicate value (as expected). It moves all the unique values from thesource to the destination tables.However, if the same stored procedure is run as a task/job in SQLServer Agent, the behaviour is different. The job fails when it see'sthe error and ends up skipping records or terminating the procedureall together. Eg. if there are 100 records in the source table with 10duplicates, the stored procedure when run through Query Analyzer willcopy the 90 unique records to the destination tables but when run fromSQL-Agent, it copies just 10-15 records.Any idea why this happens?
View 9 Replies View RelatedHi
Is it possible in my DTS Package to check if a stored procedure which I'm executing from the Execute sql task icon can be tested for failure?
Hi everybody
I'm still trying to learn the advantage of having stored procedures. I have a DTS that uses a Transform Data Task to append the result of a view into a table. All operations are done locally in the server.
Do I have any advantage if I write a stored procedure to insert the view into the table, and then call the stored procedure in the DTS, in stead of using the Transform Data Task ?
Thanks in advance for your thoughts
ds9
I'm attempting to call a storedprocedure from within an ActiveX task ina DTS and am getting a "Command Text was not set for the commandobject" error. I have no problem if I replace the stored procedure callwith the actual SQL.Is it possible to directly call a SP via an ActiveX task? If yes, couldsomebody help me with the syntax please?This is what I have currently haveSet Conn = CreateObject("ADODB.Connection")SQL = "exec (MySP)"set RLoop=conn.Execute(SQL)do while (NOT RLoop.EOF)msgbox RLoop(1)RLoop.MoveNextLoopTIAKarthik
View 1 Replies View RelatedDear all!
I have a SSIS packet. I declare a parameter that have scope of packet. I want to pass this parameter into a stored procedure and call this stored procedure from "Execute SQL Task".
Please help me how to call stored procedure with parameter from "Execute SQL Task".
Thank you very much
Dear all
Please tell me how to call Stored Procedure in a Script Task?
My Stored Procedure have an input parameter is @xmlDoc
Thanks & Rdgds
Hello,
I'm new to SQL so this should be an easy question. All i want to do is create a recuring task. I want to copy data from one SQL server to another. I've created a package to accomplish this and when i execute it manually it works. What doesn't work is scheduling to execute a regular intervals. As a test i set it up to execute every minute every day (This however will change to once a day when i prove it works) and for some reason it never executes. Any ideas? By the way i created the package using the DTS export wizzard.
I created a scheduled task on SQL server 6.5 which is actually dump system DB.
The problem I have is the scheduled task did not run with no error messsage returned. I have tried to force it run in different schedule modes. Nothing happened. However I can dump system DB through SEM Backup/Restore which runs OK!
Any body has idea why scheduled task does not run ?
Thanks
Hi ,
I am trying to exectue a stored procedure through execute sql task which takes two input parameters table name and filegroup to create a table and it also returns a value.
i am using the oledb connection
but i am getting the following error
[Execute SQL Task] Error: Executing the query "declare @rtn_value int exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? select @rtn_value as rtn_value " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
i tried with ado.net connection by directly giving the parameter names.
it will create a table but the the table name is something different from what i specified
Please do help as soon as possible
Thanks
Manish
I need to use FTP task in DTS ( or any other way), but the problem is, the source file name is not constant. It changes every day with a date extension appended to the file name, like PO_2002_6_12_PM.zip. How do I create a task task to grab this file daily as a scheduled task? Any help is appreciated.
thanks.
Di.
I think I have a basic question. Can someone tell me
the command I can use to have a stored procedure run
and generate output? This is for SQL 6.5.
I would like to schedule the sp_help_revdatabase
stored procedure to run periodically and output it
to a file. I think this is possible, but I haven't
been able to come up with the syntax...
Thanks for any suggestions!
Toni
How do you specifiy a log file for scheduled tasks. I have consistency checks done at night and when it fails, I cannot find out why because no log file is created. I checked for a log file in /MSSQL/LOGS/*.log. The maintenance wizard creates a log file, but how does a regular TSQL scheduled task create a log file?
Thanks in advance for the help.
I've created an SSIS package that retrieves a file from an FTP
View 13 Replies View RelatedHi!
I've written an scheduled job for importing an excel file into a sql table.When I try to run the job from Sql server agent , I get the status 'Not running',and in the last run status,it's written 'failed'.I've tested the command before in the query analyzer,and it works.Can someone tell me why isn't this working as a scheduled job?
Thanks in advance!
I have a job that runs a SQL script and creates a flat file. I use an FTP task to send the file to a specific FTP site. The job work beautifully when is is completed in the development environment.
After I build the package and deploy on the SQL server and set it up to run as a job it will not work on the SQL server. The error appears to be in the FTP connection. I get two separate errors:
'Unable to connect to FTP Server using FTP Connection Manager' and
'An Error Occured in the requested FTP operation. Detailed error description: The Password was not allowed'
I am sure something very simple is wrong.
Any thoughts.
Steve
Dear All!
I use a "Execute SQL Task" to call a Stored Procedure. My Stored Procedure have an input parameter with type: ntext. And in "Execute SQL Task", I set variable in Parameter Mapping as following:
Variable Name: User::xmlDocument (this variable is a String to store xml data)
Direction: Input
DataType: NVARCHAR
ParameterName: 0
When length of "User::xmlDocument" is too large then error is happen but on the contrary, "Execute SQL Task" run successfully.
So, Can you show me how to pass large data into stored procedure using "Execute SQL Task"?
I am looking forward to hearing from you
Thanks
Hi All,
I have One package that it contains one Execute SQL task in that i have placed a Stored procedure .
Now i want to pass values to Stored procedure parameters from a databse table by dynamically .For this i am trying to use " Script task "
How can i pass that table column values to that stores procedure thru using Script Task?
Regards,
Maruthi..
I have SQLServer 7, and have used Enterprise Manager to schedule local packages. The package that I'm trying to do is run a SQL script on a nightly basis. The problem that I'm having is that I need to be able to install the scheduled script customer box using an installer. I have access to execute command line programs in the installer. Do anyone know if you can schedule local packages (using SQL Scripts) from the command line? Or if a 3rd party application can do this. Any help or direction would be greatly appreciated. I've tried to use sp_add_job and sp_add_jobschedule, but haven't been able to get them to work.
Thanks
Brian
Hi,
On production server, I have two tasks in status "performing completion action" for last couple hours. Ususally these tasks take seconds to finish.
Do you know how can I kill or cancel these tasks? I tried to stop both the jobs couple times but nothing is happening.
There are no locks/blocks on current activity window.
Please give your suggestion.
Thanks.
Thanks, it worked on one server, but not the other. So I'm going to recreate that job again. Do you know where it would keep the results of the dbcc checkdb when running it in a scheduled task?
------------
Ray Miao at 9/23/99 8:07:13 AM
Recreate job.
------------
Laura Cappon at 9/22/99 5:22:43 PM
I have setup a DBCC checkdb scheduled task for Sunday nights at 11:00pm. Monday morning I checked it and the job never started running, nor did it fail. I looked in the error log and found no errors around that time. I set this job up the same way on 6 different servers, 4 ran fine and the other 2 didn't run at all.
So I set it up again to run Monday night and the 2 both ran for over 24 hours (on a 2 Gig DB). I killed it by stopping SQLExecutive. Does anyone know why this would happen and how to get it to work correctly?
Thanks for your help,
Laura