Transact SQL :: How To Create Stored Procedure Using Agent Job
May 8, 2015
I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.
I 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?
I have created a stored procedure for a routine task to be performed periodically in my application. Say, i want to execute my stored procedure at 12:00 AM daily.
How can I add my stored procedure to the SQL server agent jobs??
One of my clients has a stored procedure on their secondary server thatcopies a bunch of data from the production server. (Replication willbreak the accounting software, according to its authors. The productionserver generates a nightly full backup, so if the secondary can bescripted to do a nightly restore from that same file, then that wouldprobably be a Big Win.)Anyway, if I execute the stored procedure from Query Analyzer, itfinishes (after nearly 24 hours) - tested once recently, and I'm sureat least a few times at some point in the past. If I run a SQL ServerAgent job that executes the stored procedure, then it gets cut off afterabout 15-20 minutes - tested once recently with a manual run, and forseveral weeks of scheduled runs before that. (This being a secondaryserver, it took a while for the problem to be noticed.) What are thelikely causes of this?Both servers are running SQL 2K with SP3, and limited to TCP/IP andnamed pipes. RPC is allowed, with a 600-second timeout, but thatdoesn't seem relevant, since both the successful and unsuccessfulmethods go well past that length. The production server is a recentpurchase, and works well for their daily operations; the secondaryserver and/or its network connection might be flaky for all I know,but that doesn't seem relevant either, since success appears todepend consistently on method of execution.
I have a table (currently with no constraints or relationships) with two columns:
Employee_CodeEarned_Leave_Balance
The leave balance should be updated automatically on a daily basis using a formula. I understand this can be done using stored scheduled procedures. To give you a full and clear picture, this is what I need:
I have a stored procedure which loops thru about 180 tables and inserts the data into one table used for reporting purposes. One field is a ntext field. If I execute the stored procedure from query analyzer the entire contents of the ntext field is available. When I run as scheduled task (every 1/2 hour) from sql server agent, the data in the ntext field is truncated.
To set the scene I am using SQL 2012, in project deployment mode (SSIS Catalog rather than file system).I have setup an SSIS package to run a stored procedure which exports data for the last hour to a .tsv file and then FTP's the file to some other location via a sql agent job - This all works fine.However, I can see there may be a requirement to run the package with dates that need to be set i.e. in the event of a lost file of some other reason the package has not run and missed some of its hourly slots and the customer requires the files to be resent.
The stored procedure I am using has parameters for "DateOverride" - boolean), "start" and "end" dates (datetime) with defaults set "0" for "DateOverride" and null for the "Start" and "End" dates, I have built logic into the procedure which sets the dates if the parameters are null (as in the above to an hour before now). What I would like to be able to do (and this is to make it user friendly for support staff) is to be able to set parameters/variables in SQL agent with "DateOverride" set to "1" and the the dates I would like to be sent to the stored procedure "Start" and "End" parameters.
I did try using the parameters in SSIS which worked well when the values were true or false (0,1) but didn't work at all for the dates. If I left the dates as I had set them is SSIS it worked, but if I changed them (even if it was just changing the hour) the job errored/crashed and corrupted the job step leaving me the ability to only delete it.
How we can get to know what DML action(INSERT,UPDATE,DELETE) is present inside a Stored procedure, and i want to know if we have any specific columns are present in system tables or system related dynamic views which gives this info. I am aware of the SQL profiler, but in my scenario i want to use it through system tables or system related dynamic views.
I have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:
create proc spTheNameOfTheProcedure as select table_name from information_schema.columns where column_name = XXXXXX
Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007
If i use the SQL command SELECT * FROM paychexdb.dbo.sysobjects WHERE name like 'users_backup_%' AND xtype = 'U' AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects WHERE name like 'users_backup_%' AND xtype = 'U' ORDER BY Name DESC)
results would be 10_12_2007,10_13_2007.
So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3
I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):
First Name Last Name Address Mike Mik Jr 141552 South Charlie D 1422141
Lets say my table name whichthat has all these data is called dbo.TestTable I spent so much time in google and I have not found one simple good example. Can you help me please
I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?
In DB2 stored procedures are calling external cobol code/program, I want convert DB2 stored procedure to MS SQL stored procedure, on that case, How to call call cobol from MS SQL stored procedure.
If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer? Is that simply the success flag?
Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,
SELECT a.col1,a.col2,b.col3,b.col4,c.col5 FROM a left outer join b ON a.col1 = b.col1 left outer join c ON b.col1 = c.col1.
Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination table.
I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.
I have a table which has data like shown in below image , I want to get count of case numbers which has responses 123 AND 125 ( case should have both responses), like this I have requirement for other responses, how can I do it in Stored procedure .
I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.
-- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[spMyRequests] -- Add the parameters for the stored procedure here
We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:
Declare @FromDate as date Declare @ToDate as date Set @FromDate=Convert(date,'09-01-2015',110) Set @ToDate= Convert(Date,'10-13-2015',110) Select @FromDate
[code]...
How can I keep the date as a date and still pass it to stored procedure.
I am trying to use the Transact-SQL debugger to step through a stored procedure on a server to determine where it is failing. I right-click the stored procedure I want to debug in the Query Analyzer's Object Browser and choose Debug... In the Debug Procedure dialog I fill in the values for the parameters, choose the Auto roll back checkbox, and press Execute.
The T-SQL Debugger screen comes up with my stored procedure and a message on the bottom of Waiting for user input. But I can input nothing, neither click on the icons on top, set breakpoints, or doing anything else, as the stored procedure runs to the end and gives me the return code.
If I subsequently set some breakpoints in my stored procedure and try the Go button. Once again the stored procedure runs to the end without stopping at any breakpoint.
In both cases, the stored procedure shows the waiting icon as it proceeds to the end, so that I can not click on anything to stop it.
I have been told this is may be a permissions issue between myself as a client on my machine and the stored procedure on the server. If so, does anybody know what I the things that have to be done on the permissions side to be able to debug the stored procedure using the Transact SQL debugger.
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table. I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user. If exists view 'custom_fields" drop view 'custom_fields' Create view custom_fields Select * From tblCustomFields And make this a view in the db named custom_fields. And I want to call it from a button click in my UI.
I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure. This is what I have so far, but it does not allow me to run it.
alter procedure dbo.createNewBUtable ( @BU as varchar(50) ) as set nocount on; create table @BU ( BUid varchar(50) primary key, BUinfo varchar(50) )